Transactional Changes in SQL Server 2005 (2): Your Pre-snapshot and Post-snapshot scripts may be Redundant

Introduction

This article applies to SQL Server 2005 transactional and snapshot replication. In SQL Server 2000 we would sometimes use pre- and post-snapshot scripts. The pre-snapshot scripts are TSQL scripts which run before the snapshot files are applied, and conversely the post-snapshot scripts apply once the snapshot has completed. These scripts had various uses. The pre scripts might eg create a user who owns the tables which are to be replicated, or it might create a filegroup into which the tables are to be created. The pre and post scripts might also be related to referential integrity which is more pertinent to the current article. If the articles all belonged to one publication, selecting the article property to replicate referential integrity (0x200 or 0x20000 in sp_addarticle) makes all this irrelevant - the FKs are applied automatically, and the tables dropped in the correct order to make a pre script redundant. However, what if there was more than one publication involved? We might now have a scenario where the dropping of tables at the subscriber would be invalid due to FK restrictions from articles replicated from another publication. There might also be a possibility that there are other tables on the subscriber which are related to replicated articles and which are not part of any publication. Either way there would be a problem. In these cases, the pre and post scripts were really needed - a pre-snapshot script could simply drop the FK and a post-snapshot script would then add it back in. Such scripts are not difficult to write but would each need to be manually created.

Snapshot Changes in SQL Server 2005

In SQL Server 2005 there new .pre script files. You'll see these in the snapshot folder (distribution working folder) if you look there before the files are cleaned up. You might have seen these files before if you chose the option to truncate the data in a table (@pre_creation_cmd = 'truncate' in sp_addarticle), so they are not entirely new. However, there is now a .pre file per article, and their content IS new.

In the new way of doing things, there is a call to sys.sp_MSdropfkreferencingarticle which saves the relevant info to three metadata tables:

dbo.MSsavedforeignkeys
dbo.MSsavedforeignkeycolumns
dbo.MSsavedforeignkeyextendedproperties

and once the info is safely hived away, there is a subsequent drop of the FKs. There is no corresponding script to readd the FKs because the distribution agent simply calls the new system stored procedure "sp_MSrestoresavedforeignkeys" directly to restore the dropped foreign keys once the snapshot is applied.

Conclusions

Have a look at your existing pre and post snapshot scripts. If they deal with the maintenance of FKs then there's a good chance they are simply doing work which is already done by default. In which case you'll be able to drop the scripts entirely and remove another potential maintenance issue. By the way there is no way to "tweak" this automatic behaviour, although to be honest I can't yet think of a circumstance when you might want to override it. I suppose if it was really what you didn't want then the manual post-snapshot script could be used...