Sql-server – How to make snapshot for new tables only

replicationsnapshotsql server

If there is a replication running for a large set of data, and I want to add one or more new tables, I normally add the articles to the replication and start the snapshot agent.

Since there's a lot of data, this takes a while, interrupting users that do the normal operations on the database.

Is there a way to have the snapshot agent create the snapshot data only for these new (empty) tables?

Best Answer

There sure is. There are two settings on the publication that need to be set correctly to get the behavior that you want. allow_anonymous and immediate_sync must be set to false. You can change both with a call to sp_changepublication. Note that the tables needn't be empty for this to work.

Related Solutions

Sql-server – Should snapshot agent continue to run in transactional replication

I have noticed that the snapshot agent job runs hourly and it looks like it does a refresh of the publications (literally, a new snapshot?) Should this be doing a refresh this often if no new articles are being added or changed?

NO. Snapshot should not be run frequently, unless you are adding/dropping articles.

Also, Transactional replication uses Concurrent Snapshot processing; meaning it does not hold share locks in place during the entire snapshot generation, thereby allowing users to continue working uninterrupted while replication creates initial snapshot files.

The sync_method parameter governs which mode is used - native, character, concurrent or concurrent_c.

Sql-server – Snapshot for transactional replication taking too long for large database

Yes, initializing a large database through a snapshot can be very slow and time consuming. If it is appropriate to your case, you should start by restoring a copy of the database to the target machine.

In setting up the subscription choose "allow initialization from backup files".

Because of limitations in the user interface for setting up replication, you will need to create a script instead of relying on the UI tools. There is a blog post on this at:

http://blogs.msdn.com/b/repltalk/archive/2010/03/16/deep-dive-on-initialize-from-backup-for-transactional-replication.aspx

The post from msdn includes the code sample:

exec sp_addsubscription @publication = N'Repl2000', …..
    @sync_type = N'initialize with backup', @backupdevicetype='Disk',
    @backupdevicename='C:\Repl2000_RestoreThis.bak'--the last backup used to restore on the subscriber 
go
exec sp_addpushsubscription_agent …….
go

The article also includes some troubleshooting tips.

Best Answer

Related Solutions

Sql-server – Should snapshot agent continue to run in transactional replication

Sql-server – Snapshot for transactional replication taking too long for large database

Related Question