Share via

Replication: Snapshot does not generate correctly the partition scheme when you add a view to the publication

  • Article

Problem:

SQL Server 2005:

I have created a database that contains various file groups and assigned the new data files to their respective file group.

I also created a partition function and partition scheme (using the file groups created).

I then created a transactional publication in which I place all the articles involved. The snapshot is run and the partition scheme information is correctly scripted to file.

I proceed to then add a view to the publication and regenerate the snapshot. This causes the partition scheme script to be incorrect (it does not reflect the new file groups – just “PRIMARY”).

Cause / Workaround:

The cause of this issue is due to an incorrect option on the SMO scripter when processing a partition scheme object.

The workarounds available are:

a. Upgrading to SQL Server 2008

b. Create the partition scheme outside the snapshot logic through the pre-snapshot scripting. The file groups will have to be pre-created outside of the snapshot therefore the partition scheme logic can be added to this.