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, 2011 at 10:41 PM in Default

avatar image

Jim Orten
646 8 10 13

(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, 2011 at 11:05 PM

avatar image

10.8k 37 57 51

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, 2011 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, 2011 at 02:46 AM

avatar image

26.2k 18 37 48

(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Sep 28, 2011 at 10:41 PM

Seen: 1343 times

Last Updated: Sep 28, 2011 at 10:41 PM

Copyright 2018 Redgate Software. Privacy Policy