Must Known Things about Replication in SQL 2012

New features in SQL Server 2012 Replication

Updatable subscriptions including immediate updating and queued updating with snapshot and transactional publications are discontinued.

Four new stored procedures provide replication support for AlwaysOn.

  • sp_redirect_publisher
  • sp_get_redirected_publisher 
  • sp_validate_redirected_publisher 
  • sp_validate_replica_hosts_as_publishers

Replication supports the following features on Availability groups:

  • A publication database can be part of an availability group. The publisher instances must share a common distributor. Transaction, merge, and snapshot replication are supported.

In an AlwaysOn Availability Group an AlwaysOn secondary cannot be a publisher. Republishing is not supported when replication is combined with AlwaysOn.

Peer-To-Peer (P2P), bi-directional, reciprocal transactional publications, and Oracle Publishing are not supported.

  • A database that is enabled for Change Data Capture (CDC) can be part of an availability group.
  • A database enabled for Change Tracking (CT) can be part of an availability group.

Deprecated Features of SQL Server 2012 Replication

  • Heterogeneous replication to non-SQL Server subscribers is deprecated. To move data, create solutions using change data capture and SSIS.
  • Oracle Publishing is deprecated.
  • The RMO API is deprecated.

Upgrading Replicated Databases

SQL Server 2012 supports upgrading replicated databases from previous versions of SQL Server; it is not required to stop activity at other nodes while a node is being upgraded. Ensure that you adhere to the rules regarding which versions are supported in a topology:

  • A Distributor can be any version as long as it is greater than or equal to the Publisher version (in many cases the Distributor is the same instance as the Publisher).
  • A Publisher can be any version as long as it less than or equal to the Distributor version.
  • Subscriber version depends on the type of publication:

o    A Subscriber to a transactional publication can be any version within two versions of the Publisher version. For example: a SQL Server 2005 Publisher can have Subscribers running SQL Server 2005, SQL Server 2008 (including SQL Server 2008 R2), or SQL Server 2012; and a SQL Server 2012 Publisher can have Subscribers running SQL Server 2005, SQL Server 2008 (including SQL Server 2008 R2), or SQL Server 2012.

o    A Subscriber to a merge publication can be any version less than or equal to the Publisher version.

Run the Log Reader Agent for Transactional Replication Before Upgrade

Before you upgrade to SQL Server 2012, you must make sure that all committed transactions from published tables have been processed by the Log Reader Agent. To make sure that all transactions have been processed, perform the following steps for each database that contains transactional publications:

  1. Make sure that the Log Reader Agent is running for the database. By default, the agent runs continuously.
  2. Stop user activity on published tables.
  3. Allow time for the Log Reader Agent to copy transactions to the distribution database, and then stop the agent.
  4. Execute sp_replcmdsto verify that all transactions have been processed. The result set from this procedure should be empty.
  5. Execute sp_replflushto close the connection from sp_replcmds.
  6. Perform the server upgrade to SQL Server 2012.
  7. Restart SQL Server Agent and the Log Reader Agent if they do not start automatically after the upgrade.

Run Agents for Merge Replication After Upgrade

After upgrade, run the Snapshot Agent for each merge publication and the Merge Agent for each subscription to update replication metadata. You do not have to apply the new snapshot, because it is not necessary to reinitialize subscriptions. Subscription metadata is updated the first time the Merge Agent is run after upgrade. This means that the subscription database can remain online and active during the Publisher upgrade.

Merge replication stores publication and subscription metadata in a number of system tables in the publication and subscription databases. Running the Snapshot Agent updates publication metadata and running the Merge Agent updates subscription metadata. It is only required to generate a publication snapshot. If a merge publication uses parameterized filters, each partition also has a snapshot. It is not necessary to update these partitioned snapshots.

Run the agents from SQL Server Management Studio, Replication Monitor, or from the command line.

After upgrading SQL Server in a topology that uses merge replication, change the publication compatibility level of any publications if you want to use new features.

Restoring a Replicated Database from an Earlier Version

To ensure replication settings are retained when restoring a backup of a replicated database from a previous version: restore to a server and database with the same names as the server and database at which the backup was taken.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s