Reviewer #17 Database Replication in SQL Server 2012

Wow, my first article for the year 2014! Here’s to a blessed and fruitful year ahead for all of us <3

Database Replication 

This topic is part 3 and the last topic of lesson in High Availability which covers 12% of the certification exam.

Replication enables the SQL Server 2012 to distribute and synchronise data and database objects from one database to another.

Replication Architecture

SQL Server 2012 replication uses specific terminology to describe how components interact and work together in the replication topology. Very much similar to a news print or magazine publication i might say.

Publisher – An instance that makes data available in through publication

Article – the published object. This are the tables, schemas, stored procedure, views, indexed view or a UDF that is published in the replication topology.

Publication – the collection of articles published.

Distributor – An instance that manages the transmission from publisher to subscriber. A distributor on the same LAN as the publisher is termed as a local distributor while a distributor on a remote  location from the publisher is a remote distributor.

Subscriber – the instance that receives the published articles.

Agent – the services that enables the publisher, subscriber and distributor to perform the replication tasks.

Replication Types

Snapshot Replication – Enables the complete refreshes of data rather than updating the database on an incremental basis. This replication type is most suitable for infrequent data changes or only a small volume of data needs to be replicated.

Transactional Replication – Suited for the instance-to-instance situations that require subscriber databases to stay up to date with changes from the publisher.

Peer-to-Peer Replication – Enables peer nodes to read and write changes and still have those changes replicated to other nodes in the replication topology.

Merge Replication – Suited for mobile and distributed server applications when data conflict is possible and any node may publish changes that is synchronised across all nodes.

Configuring Snapshot Replication

When configuring for a snapshot replication, you need to determine the schedule for the snapshot agent to complete a snapshot. The snapshot agent uses the BCP utility to generate a snapshot. You can set the snapshots to be compressed. However, compressing snapshots takes additional time and resources and due to the limitations of CAB file format, you cannot compress snapshot files over 2GB. The following are steps to configure snapshot replication.

1. On the node that hosts the database that will act as your publisher, expand the Replication, right click Local Publications, select New Publication to launch the New Publication Wizard.

2. When you come to the Distributor page, choose the local instance as the distributor.

3. On the SQL Server Agent start page, configure the SQL Server agent service to start automatically.

4. On the snapshot folder page, specify the network share for storing the snapshots. You must ensure that the replication a gents have the appropriate permissions to this shared folder. The publisher must be able to write to this shared folder and also the subscriber must be able to read from this shared folder.

5. On the publication database page, select the database you wish to publish.

6. On the publication type page, select snapshot replication.

7. On the articles page, select the objects you wish to be included in the publication.

8. Use the filtered rows page if you have the need to filter the content of the tables in the publication.

9. On the snapshot agent page, choose to create a snapshot immediately and then configure a snapshot creation schedule.

10. On the agent security page, click security settings to choose the snapshot agent security account. You can choose to run under a windows domain account or use the SQL Server Agent service account. You can also configure the account that connects to the publisher instance.

11. On the Wizard actions page, you can choose to create the publication and generate a script file with steps to create the publication. You can save this script for reference later.

Configuring a Subscription

When configuring a subscription to a snapshot replication, connect to the subscriber instance and do the following steps;

1. Right click on Replication\Local Subscriptions on your subscriber instance and click on New Subscription

2. On the publication page, use the drop down list to select your publisher. Click Connect. Select the database and the publication.

3. On the Distribution Agent Location page, choose to run each agent at its subscriber to configure pulls publication.

4. On the Subscriber page, select <New Database>, provide the name of subscriber database and click OK.

5. On the Distributor Agent Security page, click (…) and enter the credentials the Distribution Agent process will use.

6. On the Synchronization Schedule page, choose Run Continously for the Agent schedule.

7. On the Intialize Subscription page, choose Immediately under Initialize When.

8. On the Wizard Actions page, choose Create The Subscription, then proceed to Finish.

To simulate the steps above, I created 2 instances on my laptop and it was fairly easy to set it up.

Transactional Replication

This is the most suitable type of replication when you want your changes from the publisher synchronised with the subscriber as they happen. The replication direction is only one-way, from the publisher to the subscriber.

To configure the transaction replication to publish your articles and to use the local instance distributor, you may do the following steps;

1.  Right click on Replication\Local Publication and choose New Publication to launch the wizard.

2. On the Publication Database page, select the database that will act as the publisher.

3. On the Publication Type page, choose Transactional Replication.

4. On the Articles page, select the objects you wish to include your publication.

5. Use the Filter Table Rows page if you have needs to filter the table row data. But for testing i usually just bypass this page.

6. On the Snapshot Agent page, choose Create a Snapshot Immediately and Keep The Snapshot Available To Initialize Subscriptions. In a way you can also configure your snapshot agent to run according to a schedule so new subscriptions are created from an updated snapshot.

7. On the Agent Security page, click Security Settings to specify credentials of the Snapshot Agent and the Log Reader Agent.

8. On the Wizard Actions page, choose Create the Publication. You may also click on Create the Script and save the script for reference.

9. On Complete the Wizard page, provide the Publication name and proceed to Finish.

After the snapshot is created, you can subscribe to the transactional publication through the Local Subscriptions node on the Subscriber instance.

After the successful configuration, you should be able to see the replication when you expand Local Publications.

AfterConfig

Peer-to-Peer Transactional Replication

Peer-to-Peer transactional replication enables transactions performed by subscribers to be synchronised with other nodes in the replication topology. When you implement peer-to-peer transactional replication, you must prevent conflicts by partitioning the data so that the same row will not be updated at separate locations. If the data is not partitioned properly and a conflict occurs, the replication will fail.

Peer-to-peer  transactional replication has the following conditions;

  • All the instances that participate in the replication topology must be running SQL Server 2012 Enterprise Edition.
  • You cannot use row and column filtering.
  • Publication names must be identical on all instances participating in the replication.
  • Each participating instance should use its own distribution database to ensure that there is no single point of failure.
  • You cannot include tables in multiple peer-to-peer publications in the same publication database.
  • You cannot reinitialise peer-to-peer subscriptions. You must restore a backup at a node if you must force an update of data.
  • If you add nodes to a peer-to-peer topology and have to perform a restoration, you need to restore only from backups created with the new nodes participating in the topology.

To configure a peer-to-peer publication that uses the local instances as the distributor, see steps below;

  1. Go to Replication, right click on Local Publications and choose New Publication to launch the New Publication Wizard.
  2. On the Distributor page, choose the local instance as the distributor.
  3. On the SQL Server Agent Start page, configure the SQL Server Agent service to start automatically.
  4. On the Snapshot Folder page, provide the location of a network share folder that the other agents participating in the replication can access.
  5. On the Publication Database page, choose the database that hosts the data or objects you will replicate.
  6. On the Publication Type page, select Peer-to-Peer Publication.
  7. On the Articles page, select the objects you want to include in the publication.
  8. On the Agent Security page, click Security Settings and then specify the credentials of the Log Reader agent.
  9. On the Wizard Actions page, select Create the Publication.
  10. Provide the Publication name and proceed to Finish.

Merge Replication

Merge Replication enables databases hosted on instances at separate locations to be updated and those changes replicated to all other database participating in the replication topology.

When configuring merge replication, the publisher and subscribers track changes made by using triggers. When a subscriber synchronises with the publisher, they exchange all rows that have changed between the publisher and subscriber since the last synchronisation.

Merge replication is most suitable for the following requirements;

  • You have the database updated at multiple locations. The publication is multi direction across all the nodes.
  • You have enabled subscribers to make offline changes to data and then to synchronise those changes back when they connect.
  • You are able to detect and resolve update conflicts.
  • Your configuration supports non-SQL Server nodes participating in the replication.
  • Applications do not require tables to be transactionally consistent.

Merge replication makes use of the SQL Server Snapshot Agent and the Merge Agent. If the Merge Agent detects a conflict, such as the same row being updated with different values at different locations, the conflict resolvers determines which data is accepted.

When you start to implement a subscription in merge replication, you need to specify a subscription type and conflict resolution priority. A server subscription enables you to set a conflict resolution priority, which is a figure between 0 and 99.99, with servers assigned a higher priority overriding servers assigned with lower priority. When configuring the Client subscription type, the priority for conflict resolution is “whoever is first to publisher wins”. The default type is Server, and default  priority is 75. To view the conflicts, right click on Merge Publication and select View Conflicts. Conflicts are viewable only if conflicts have occurred. When a conflict occurs between a publisher and subscriber, the publisher change is kept and the subscriber change is dropped.

To configure a merge replication, you may follow the steps for configuring a Transactional Replication discussed above, but selecting Merge Publication in the Publication Type page. On the Subscriber Types page, choose SQL Server 2008 or later versions.  Only subscribers with SQL Server 2008 or later are supported. Proceed to Create and Name the Publication.

Replication Monitor

Replication Monitor is a tool built inside the SQL Server Management Studio (SSMS) that enables you to monitor replication. Only members of the sysadmin fixed server role can perform monitoring on the Distributor instance or have been assigned membership of the replmonitor database role in the distribution database.

You may add a publisher from the Replication Monitor tool. Right click on the Replication Monitor node and choose Add Publisher to bring up the Add Publisher dialog box. You can also add a SQL Server Publisher or a Oracle Publisher or add a Distributor and add its publishers. This final option enables you to view all publications associated with a specific distributor.

From the Replication Monitor Tool, you can configure alerts based on performance benchmarks. When a monitored value exceeds the benchmark, Replication Monitor displays an alert in the Status column for the subscription and the publication with which it synchronises. You can also configure an alert to send email or run SQL Server Agent jobs.

Replication Monitor also provides performance-quality information for the transactional replication and merge replication. To set a threshold, select the publication you wish to monitor, click the Warnings tab, choose the warning and set the threshold value. In the same manner, to configure an alert for the selected publication, on the Warnings tab, click Configure Alerts. Select the alert you will configure and click Configure.

Replication Monitor

The following maximum sizes and numbers apply to SQL Server 2012 replication;

  • Merge publication articles 256
  • Snapshot or transactional publication articles 32767
  • Merge publications columns in a table 246
  • Snapshot or transactional publications columns in a table 1000
  • Merge replication bytes for column used in a filter 1024
  • Snapshot or transactional publication bytes for a column used in a row filter 8000

For more information on replicating data  between SQL Server, Oracle and IBM DB2 database, visit http://msdn.microsoft.com/en-us/library/ms151835(SQL.110).aspx.

About these ads

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

unfoldthecreativity

Traveller Observer

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.

juliansherman

Just another WordPress.com site

Paul Turley's SQL Server BI Blog

from the world of Microsoft SQL Server BI and reporting

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We will improve your knowledge

SQL Studies

 Live, Learn, Share

Behind-the-Scenes of Xeno Hemlock (dot) com

#TeamAPE (author, publisher, entrepreneur)

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Stevespevack's Blog

IT Recruitment

SQL Problems and Solutions

Sharing the knowledge & experience of SQL, DBA & BI

Glenn Berry's SQL Server Performance

Semi-random musings about SQL Server performance

Algonquin Studios Blog

Ideas and opinions on software development, web design, customer service, sales and marketing...and food

SutoCom Solutions

Success & Satisfaction with the Cloud

Terli Srinivasa rao Blog

Sql server and Tableau practices

Follow

Get every new post delivered to your Inbox.

Join 48 other followers