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?
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.
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
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.
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)
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.
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.
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.
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
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.
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.
The publisher is the source database where replication begins. It makes data available for replication.
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.
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.
The publication is the storage container for different articles. A subscriber can subscribe to an individual article or an entire publication.
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 is a form of replication in which modifications made to the publishing database are made at the subscription database at the same time.