Implementing NoSync Initializations (and variations) on SQL Server 2005

I'd like to do a brief article on doing nosync initializations for SQL Server 2005. I had previously done an article on SQL Server 2000 (see here), but the methodology is now slightly different and offers other alternatives.

How to Add the Subscription - the Wizard

When adding a subscription, the option in the wizard to declare that 'No, the Subscriber already has the schema and data' has been replaced by the screen below which is fairly self-explanatory. We still need to get the data to the subscriber as before, and there are several modifications we might need to make to the schema on the subscriber as explained in the section below, but this sets us up for a nosync initialization. For merge replication this is much the same as the previous methodology for a nosync initialization and running the merge agent will generate all the extra system tables, triggers etc on the subscriber. However for transactional replication this is not the same as the SQL Server 2000 nosync initialization - this is the new 'replication support only' option which is explained further below.

How to Add the Subscription - Scripts

Using scripts we can more easily see what all the options are and what they mean. To add a subscription in transactional replication we use the following procedures: sp_addsubscription and sp_addpushsubscription_agent / sp_addpullsubscription_agent. In sp_addsubscription there are now 4 options for @sync_type. Previously there were just 2 options: 'automatic' (the default) and 'none' which was used to make a nosync initialization. This is still the case for merge replication in SQL Server 2005 so I'll not mention that any more, but for transactional we now have several new options which aren't explicitly available in the wizard. To paraphrase my SQL Server 2000 article, the latter option ('none') had to be used in conjunction with a manual generation of replication stored procedures which are then applied to each subscriber. In SQL Server 2005 we now have alternative options which I'll explain below:

(1) none - assumes that the subscriber already has the schema and initial data for published tables - NOW DEPRECATED. (however sp_scriptpublicationcustomprocs is not marked as deprecated for manual use in BOL!!!)

(2) automatic (default) - automatic transfer of schema, data, replication stored procedures (and triggers where necessary).

(3) replication support only - what does the term "replication support" mean in this context? It is the article's stored procedures, the triggers that support updating subscriptions and the required system tables. So, this is like the SQL Server 2000 option, without the headache of doing the manual creation and implementation of the stored procedures. This option assumes that the subscriber already has the schema and all the initial data for published tables. Here we must be extremely careful - there must be NO difference in data between the publisher and subscriber. This is not ensured by the setup and as DBAs we must guarantee that this is the case. In some terminology this is referred to as "Quiescing the system" while the data is transferred. Obviously if you work on a system where there is 24 hour access, this is not going to be possible, so option (2) or (4) would be required.

(4) initialize with backup. The complete schema and initial data for published tables are obtained from a recent full backup of the publication database. So, we must ensure that the subscriber has access to a recent backup of the publication database - either the backup is transferred to the subscriber or is put on a fileshare that the subscriber can read. The location of the backup and media type for the backup are specified by 2 new parameters of sp_addsubscription: @backupdevicename and @backupdevicetype.

At this point I'll note that you'll need to do 3 things:

(a) remove redundant objects and tables. There's no point cluttering up the database on the subscriber with nothing that wouldn't be there is you had done an automatic initialization. This will speed up the backup and clarify to other administrators what the subscriber is being used for.
(b) replace timestamp columns with binary(8) columns. You'll have to use a temporary holding table to achieve this.
(c) remember that the backup includes all data, even if row or column filters are specified on a table - so some columns might need to be dropped.

How "initialize with backup" Works, and How to avoid Pitfalls

When using "initialize with backup", a transactional replication topology does not need to be quiesced during configuration. With "initialize with backup", sp_addsubscription internally calls RESTORE HEADERONLY to extract the last LSN of backup to be used as the starting point of the new subscription. A subsequent comparison is made to the xact_seqno value in distribution..MSrepl_commands, and the necessary commands transferred. To ensure that all the required commands actually exist in the MSrepl_commands table we must restore the backup before the retention period is reached and the distribution cleanup agent removes the rows. BOL recommends that we disable the distribution cleanup agent is stopped during this period to ensure it all goes smoothly. If you get this wrong, you'll receive the following message:

Msg 21397, Level 16, State 1, Procedure sp_MSsetupnosyncsubwithlsnatdist, Line 213 The transactions required for synchronizing the nosync subscription created from the specified backup are unavailable at the Distributor. Retry the operation again with a more up-to-date log, differential, or full database backup. The Subscriber was dropped.

Conclusions

Hopefully this article will help you to take advantage of the useful new options in setting up a nosync initialization. This option has always involved some necessary scripting requirements, and it could never be done entirely using the GUI. Now with SQL Server 2005 this is even more so, but the additional options present a necessary alternative to automatic initializations.

Thanks to Nigel Maneffa for pointing out some improvements on this article.