SQL SERVER – Fix : Error : 8501 MSDTC on server is unavailable. Changed database context to publisherdatabase

During configuring replication on one of the server, I received following error. This is very common error and the solution of the same is even simpler.

MSDTC on server is unavailable. Changed database context to publisherdatabase. (Microsoft SQL Server, Error: 8501)

Solution:

Enable “Distributed Transaction Coordinator” in SQL Server.

Method 1:

  1. Click on Start–>Control Panel->Administrative Tools->Services
  2. Select the service “Distributed Transaction Coordinator”
  3. Right on the service and choose “Start”

Method 2:

  1. Type services.msc in the run command box
  2. Select “Services” manager; Hit Enter
  3. Select the service “Distributed Transaction Coordinator”
  4. Right on the service and choose “Start”

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

SQL SERVER – Initializing a Merge Subscription Without a Snapshot

During recent course of Disaster Recovery and Performance Tuning, I had very interesting conversation with students regarding Initializing a Merge Subscription Without a Snapshot and Initializing a Transactional Subscription Without a Snapshot. After the discussion when we were looking at MSDN pages one thing caught my notice was the note on the top of the MSDN page regarding future support of the feature for Initializing a Merge Subscription Without a Snapshot.

In the book on line on the subject Initializing a Merge Subscription Without a Snapshot it suggests that this feature will be deprecated in future, whereas there is no such note on the topic for Transactional Subscription. Well, I want to share this with everybody suggesting that there may be issue with future version if you are not initiating your replication with snapshot in the case of Merge Replication.

I would like to know if you are currently using Merge Replication and initializing it without snapshot. If you are – I would like to know why?

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – ERROR: FIX: Cannot drop server because it is used as a Distributor in replication

Replication has been my favorite subject when it comes to resolving errors. I have found that many DBAs are stuck with the solving of the problem of replication for hours; however, the solution is very easy. One of the very common errors in replication occurs when replication is removed from any server. I have seen the following error as one attempts to remove replication from the same server when the publisher and distributor are on the same server.

Cannot drop server ‘repl_distributor’ because it is used as a Distributor in replication.

Cannot drop the distribution database ‘distribution’ because it is currently in use.

Fix/Workaround/Solution:

Try any of the following option. Either of them should work for you.

EXEC master.dbo.sp_serveroption @server=N'XXXX', @optname=N'dist', @optvalue=N'true'
GO

EXEC sp_dropdistributor @no_checks = 1, @ignore_distributor = 1
GO

Please do let me know your feedback regarding which one of the above options worked for you or if any of the other solution helped to resolve this issue.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – FIX – ERROR : Cannot drop the database because it is being used for replication. (Microsoft SQL Server, Error: 3724)

I have set up replication at many different organization. One error I quite commonly face is after I have removed replication I can not remove database. When I try to remove the database it gives me following error.

Cannot drop the database because it is being used for replication. (Microsoft SQL Server, Error: 3724)

Fix/Workaround/Solution:

The solution is very simple. Create the empty database with the same name on another server/instance first. Take full back of the same and forced restore over this database.

Do let me know if you have any better idea or suggestion.

Reference : Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – Book Review – Pro SQL Server 2005 Replication (Definitive Guide)

Pro SQL Server 2005 Replication (Definitive Guide) (Hardcover)
by Sujoy Paul (Author)

Link to Amazon

Quick Review:
This is good book for any novice developer to start in the world of database replication implementation and maintenance. Replication is important part of highly availability and one book covers all the concept and methodology at one place.

Detail Review:
Replication is the process of sharing information so as to ensure consistency between redundant resources, such as software or hardware components, to improve reliability, fault-tolerance, or accessibility. Database replication can be used on many database management systems, usually with a master/slave relationship between the original and the copies. The master logs the updates, which then ripple through to the slaves. The slave outputs a message stating that it has received the update successfully, thus allowing the sending of subsequent updates.

This books talks about different replication types and methods. This book explains different scenarios where types of replication can be used. Sujoy Paul (SQL Server – MCP) explains the concept with many screenshots and simple examples. This book is not limited to replication types only but it also covers replication between heterogeneous databases and other objects.

Pros :

  • Lots of screen shots
  • Lots of explanation of how different procedures works
  • Good coverage of different kind of replications
  • Step by Step guidance
  • Complete coverage of any methodology
  • Truly useful to novice programmers

Cons:

  • No discussion about potential errors can come across during replication process
  • Some of the screen shots are hard to read (Apress should look into this matter)
  • Some of the chapters are very blurry

Overall, this is great book for any novice developer who is beginning in the world of replication. Though this book covers many screenshots and material directly from Book On Line this is great collection of all the concept at any place. I have personally used this book when I was new DBA and have learned replication basics from this book.

Stars: 4 Stars

Summary: This a comprehensive single source of information will save lots of time as there is no need to go through multiple books to implement and maintain replication solutions.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Replication Keywords Explanation and Basic Terms

While discussing replication with Jr. DBAs at work, I realize some of them have not experienced replication feature of SQL SERVER. Following is quick reference of replication keywords I created for easy conversation.

Publisher

The publisher is the source database where replication begins. It makes data available for replication.

Subscriber
The subscriber is the destination database where replication ends. It either receives a snapshot of all the published data or applies transactions that have been replicated to itself.

Distributor
The distributor is the intermediary between the publisher and subscriber. It receives published transactions or snapshots and then stores and forwards these publications to the subscribers.

Publication
The publication is the storage container for different articles. A subscriber can subscribe to an individual article or an entire publication.

Article
An article is the data, transactions, or stored procedures that are stored within a publication. This is the actual information that is going to be replicated.

Two-phase commit
Two-phase commit is a form of replication in which modifications made to the publishing database are made at the subscription database at the same time.

MSDN : Replication

Reference : Pinal Dave (http://blog.SQLAuthority.com)