Tuesday, March 8, 2011

SQL Server could not create a subscription for Subscriber: MSDTC on server is unavailable.

When I configured a subscription for Transactional replication with updatable subscription, the following error occurred.

SQL Server could not create a subscription for Subscriber 'DINESH-PC\SQL2008Instance2'. MSDTC on server 'DINESH-PC\SQL2008INSTANCE2' is unavailable.

I did not want to set the subscription for Immediate Updating mode, so set to Queue changes and commit when possible.


I am aware that MSDTC is used for Immediate Updating (Simultaneously commit changes). Then why I am getting such error? I did not select that option.

The reason is the wizard. When the subscription is created through the wizard, whatever the selection you make, it makes both Immediate Updating and Queued Updating available for subscription. If you do not want both, do not use the wizard, create the subscription manually using stored procedures. Note that you can use the wizard for generating scripts for the whole task without creating the subscription. The generated script contains four stored procedures;

  • sp_addsubscription: This adds a subscription to publication and it has to be run in publisher. You need to make sure that @update_mode is set as ‘queued tran’.
  • sp_addpullsubscription: This adds a pull subscription to subscriber, hence needs to run in subscriber. Just like previous one, make sure that @update_mode is set as ‘queued tran’.
  • sp_link_publication: This is to use with Immediate Updating, hence you do not need to execute this.
  • sp_addpullsubscription_agent: This adds a scheduled agent job to synchronize pull subscription with transactional publication. Run it in subscriber.

Once you run everything, you have a subscription with queued updating mode.

Monday, March 7, 2011

SQL Server Updatable Subscriptions: What is the best replication type for this?

Replication is used for copying data and database objects from one location to another on a recurring basis. The topology of replication is vary, it can be a simple implementation with two servers or a complex implementation with many local and remote servers and mobile devices. Servers involved are designated with server roles such as Publisher, Distributor and Subscriber and their responsibilities are publishing, distributing, and finally subscribing published data. In some scenarios, subscribers use the subscription only for reading data. In other scenarios subscribers make modification to subscription (change data) and send back to publisher. Such subscriptions are called as Updatable Subscription. SQL Server provides many ways to implement a replication with updatable subscription. This post discusses some considerations and guide lines for selecting the best replication type with updatable subscription suit for your requirement.

The general overview of Replication can be illustrated as below.


  • Publisher: Publishes data with publication.
  • Distributor: Distributes data. This server holds metadata, historical data and replicated data for some replication types.
  • Subscriber: Receives data from publications. They can make changes to subscribed data too.

Updatable Subscription
SQL Server provides three different types of Replications. They are:

  • Snapshot replication
  • Transactional replication
  • Merge replication

Changing data at subscriber and sending changes back to publisher facility is not available with all types. Transactional replication and Merge provides facilities for it.

Transactional Replication is mainly used in Server-to-Server scenario. Subscribers are initiated with a snapshot of publication and then transactions occurred are sent to them in incremental manner. This replication type allows subscribers to update data and send back to publisher. If it is required, Transactional Replication with updatable subscription option is available at creation of publication. This works in two ways:

  • Immediate Updating: Uses MSDTC for sending data from subscribers to publisher and applying. It is done through triggers added in published tables in subscription.
  • Queued Updating: Changes are sent to a queue. The Queue reader agent pull changes and apply to publisher. It is done with triggers too.

Read more on these two at: http://msdn.microsoft.com/en-us/library/ms151794.aspx

Now, where we can use Transactional replication with updatable subscription? Here are some guidelines for selecting this as your replication type:

  • Scenario is Server-to-Server.
  • Number of subscribers are less than 10.
  • Subscribers update data infrequently.
  • Subscribers, distributor and publisher are connected most of the time (For Immediate updating, regular connection is required as update happens synchronously).
  • Subscribers need all changes, not the last change (Eg. if there are 100 transactions for a record, all are required by the subscriber, not the final state of the record)
  • Expect less update conflicts with queued updating. This type does not have rich facilities for handling conflicts.

**Note that MSDN says that this feature will be removed in a future version of SQL Server, so it is not advisable to use this if you plan to upgrade your system with future versions.

Merge replication is mainly for Server-to-Client environment. This allows subscribers to make modification on replicated data and send them back to publisher. Use following guidelines when selecting Merge as your replication type for updatable subscription:

  • Scenario is Server-to-Client.
  • Many number of updating subscribers (Merge is designed for this).
  • Expect conflicts (Good support on conflict detection and resolving)
  • Subscribers need the final state of the change, not all transactions.
  • Subscribers, distributors and publisher are not always connected.
  • Subscribers need a portion of publication, not the entire one (This is done with dynamic filters in publication).

Okay, you can consider given guidelines and select the best type for your requirement. How about Peer-to-Peer replication? Can we use it too for handling this?

Peer-to-peer replication is scale out and high availability solution. It maintains copies of data across multiple servers, referred as nodes. Its foundation is Transactional replication and works in similar manner. The main difference is, nodes are not designated as publisher or subscribers, all are publishers and subscribers. Here are some guidelines for selecting it:

  • Requires high availability (with other types, if publisher goes down, replication topology is down too).
  • Less number of nodes (would be better to have 10 or less).
  • Filtering of publication is not required.
  • Do not expect much conflicts.

Friday, March 4, 2011

Self-Service Business Intelligence with SQL Server 2008 R2 – Speaking engagement in SQL Server Sri Lanka User Group

The February meeting of SQL Server Sri Lanka User Group meeting was held on last Wednesday and sessions were done by Dinesh Asanka and me. My session was on Self-Service Business Intelligence with SQL Server 2008 R2, focusing on PowerPivot. The agenda for the sessions was:

  • What’s new in SQL Server 2008 R2?
  • What is Business Intelligence?
  • Introduction to New Paradigm
  • Introduction SQL Server PowerPivot
  • Future Releases
  • Q & A

The presentation is available for downloading: http://www.sqlserveruniverse.com/v2/SSSLUG/Downloads.aspx