question

Jim Orten avatar image
Jim Orten asked

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?
replicationsnapshot
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cyborg avatar image
Cyborg answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
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?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.