Replication: Snapshot does not generate correctly the partition scheme when you add a view to the publication
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.