Replication to the Non Default File Group

Hi,

I have a snapshot replication set up on a SQL 2K Server publishing to other SQL 2K servers. The publisher database has mutliple file groups - which is structured the same as the subscriber databases. The snapshot has been set to delete and recreate the tables on the subscribers - this is a performance decision as the tables are large and this is the better approach. The tables reside in their own file groups (but not the default). I want the replication agents to recreate these tables in the original file groups and not the default.

Is there a method within Replication to specify which file group to recreate the table in so it doesn't fill up the default file group?

Thanks in advance,

Pete

Hi Pete,

The tables on the subscriber will only be created on the default filegroup. The options are either to not drop tables and use delete instead or modify the default filegroup of the destination databases.

You can create a job that does the following:

1. sets the default filegroup of the subscriber database to secondary filegroup.

2. Agent starts and does the job.

3. Once agent job has finished and call a new job that will set the filegroup backup to primary.

In SQL 2005 you have the option to specify the scriptts that you want to execute before and after the snapshot is applied.

Jag

|||

There is a third option. After the snapshot has been created, edit the script files on the distributor which hold the CREATE TABLE commands and correct them to use the correct file groups.

You can also setup a T/SQL script which can be run after the snapshot has been delivered. Write a script which rebuilds the clustered indexes of the tables on the correct file group. By rebuilding the clustered index and moving it to another file group you will move the tables to that file group as well.