x

controlling the location of subscriber tables and indexes

I'm doing transactional replication in SQL Server 2000. I'm setting up a new publication and subscriber. I've created my subscriber database with the files and filegroups that are similar to my publication database (PRIMARY, DATA1, INDEX1, ARCHIVE). I want my objects on the subscriber to be placed in the same filegroups as they were in the publisher, but when I create the snapshot, it doesn't specify filegroups. The result is, everything goes into the PRIMARY filegroup.

The hard way to fix this would be to edit the snapshot schema files before they are applied or run a script after the snapshot is applied to move everything to the correct filegroup or initiate with a backup and restore instead of a snapshot.

Is there an easier way?
more ▼

asked Sep 28 '11 at 10:41 PM in Default

Jim Orten gravatar image

Jim Orten
646 8 8 10

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first
One option is to restore a copy of backup from your publisher to the subscriber, then work on your file groups as the way you want at the subscriber, at the publisher article property, Destination Object, "Action If name in use". Choose object unchanged.
more ▼

answered Sep 28 '11 at 11:05 PM

Cyborg gravatar image

Cyborg
10.6k 36 39 45

I think I figured this out with a hint from Cyborg.

Seems the easiest way is to script out the database (except data) and build the empty objects. Then in the Article properties on Destination Object, Action if name in use - use "Truncate...."

"Keep existing object unchanged" would be OK until I have to do another snapshot (for some reason) because it wouldn't remove any data.
Sep 29 '11 at 12:09 AM Jim Orten
(comments are locked)
10|1200 characters needed characters left
I dont have a SQL 2000 instance to hand to check, but from 2005 and above, you can set options regarding filegroup association for each article in a publication. Have you looked at the properties for the articles in your publication to see if you can do that?
more ▼

answered Sep 29 '11 at 02:46 AM

WilliamD gravatar image

WilliamD
25.8k 17 19 41

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x293
x6

asked: Sep 28 '11 at 10:41 PM

Seen: 635 times

Last Updated: Sep 28 '11 at 10:41 PM