Replication Without Creating a Snapshot
In replication, the standard way of creating a publisher is to create a publication with a snapshot (which is what all the documentation recommends).
However there are a few problems with this method:
- Creating the snapshots and then applying them is time consuming.
- Snapshots will consume disk space.
There is an alternative to this, where you can restore the database into a separate database which will then become the subscription and then create the subscription with sync_type set to none as shown below:
use [MyData] exec sp_addsubscription @publication = N'myData_Subs', @subscriber = N'DINESHAWIN7', @destination_db = N'myData_Subs', @subscription_type = N'Push', @sync_type = N'none', @article = N'all', @update_mode = N'read only', @subscriber_type = 0 exec sp_addpushsubscription_agent @publication = N'myData_Subs', @subscriber = N'DINESHAWIN7', @subscriber_db = N'myData_Subs', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor' GO
However, the problem with this approach is that there will be data loss between the backup time and the time of adding the subscription (unless you take the drastic step of stopping all applications).
There is another way of doing this by syncing with the database backup, this feature is available from SQL Server 2005.
Let us assume that you have a database in production from which you need to create a subscription. In production, typically you will have full backups and transaction log backups scheduled.
Obviously, it will be better to disable those backups. This is not a requirement and if you need your backups to run continuously let them proceed, but by disabling them you will definitely make the process easier.
- Using full/diff and log backup build your subscription database. This is the standard procudure for building a database using backups. Make sure you apply the last database backup with the NORECOVERY option as shown below. This is because you will need to apply another log backup to this database. Let us assume that subscription database is myData_Subs.
- Create the publication. You can use the wizard for this but the important thing to remember is that you should not enable the snapshot. In this example the publication name is mydata_trans_repl.
- Enable the publication to allow initialization from a backup. In the
replication properties, under subscription options, set the Allow
initialization from backup files option to true as shown below:
Similarly you can do this using a TSQL script:
EXEC sp_changepublication @Publication=mydata_trans_repl, @property = N'allow_initialize_from_backup', @value = True
This has to be a separate option as you will not be given this option using the wizard.
- Take a transaction log backup and apply it to the subscriber database. This restore should be with RECOVERY so that database will be accessible.
- Add the subscription using the below TSQL. Note the sync_type, backupdevicetype and backupdevicename options which are for syncing with the database backup :
exec sp_addsubscription @publication = N'mydata_trans_repl', @subscriber = N'DINESHAWIN7', @destination_db = N'myData_Subs', @subscription_type = N'Push', @sync_type = N'initialize with backup', @article = N'all', @update_mode = N'read only', @subscriber_type = 0, @backupdevicetype ='disk', @backupdevicename ='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\myData_l.bak'
In the @backupdevicename you need to provide the last backup used to restore the subscriber that was taken after the publication was created. Once you create this, data between the time of the last backup and the latest backup will be synced as shown from the replication monitor:
So you can see that you will be able to create a replication without any down time.
If your database backup uses a password, there are parameters to set – http://msdn.microsoft.com/en-us/library/ms147834.aspx will give you all the parameters you need for this option.
The Main problem with this approach is the subscription will have all the database objects such as stored procedures, views, functions and tables etc. So you will need to manually drop those objects.
Also, if you have filtered articles where you are replicating a subset of the rows, you need to delete the unnecessary rows manually.
There may be a few errors you encounter during this operation:
The specified publication does not allow subscriptions to be initialized from a backup. To allow initialization from a backup, use sp_changepublication: set ‘allow_initialize_from_backup’ to ‘true’.
Obviously, this means that you have not enabled the publication to allow initialization from backup. You need to enable the setting as shown above.
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.
Make sure you have taken the log backup of the replicate database after you enabled the replication.