Transactional replication is a commonly used method for implementing disaster recovery (DR) in SQL Server. However, it can be problematic and complex to set up, especially in older versions like SQL Server 2000. In this article, we will explore how transactional replication has improved in SQL Server 2005 and discuss the steps to implement it as a DR solution.
Background and SQL Server 2000 Methodology
In SQL Server 2000, transactional replication assumes that the subscriber is read-only. This means that certain constraints and automatically populated columns are not implemented on the subscriber. While this is fine for normal replication, it becomes a problem when using the subscriber as a failover server.
To make the schemas more or less identical on the publisher and subscriber, a manual process is required. This involves setting constraints and identities to NOT FOR REPLICATION (NFR) on the publisher, scripting out the publisher’s tables, and applying the script on the subscriber. Additionally, replication stored procedures that apply to timestamp columns need to be modified.
Setting up transactional replication in SQL Server 2000 can be lengthy and prone to errors. This may explain why some implementations fail after failover.
Setting up Transactional Replication in SQL Server 2005
In SQL Server 2005, transactional replication for a failover solution has become simpler and more straightforward. The default behavior of replication has been improved to converge the schemas of the publisher and subscriber without manual changes.
When replicating tables with primary keys, foreign keys, defaults, check constraints, and indexes, the defaults, check constraints, and foreign keys are left behind on the subscriber. However, two interesting changes occur:
- The Identity attribute is modified to NFR on the publisher if not already NFR, and is implemented as an identity column (NFR) on the subscriber.
- The primary keys remain as primary keys on the subscriber, rather than being changed to unique indexes.
To further converge the schemas without editing scripts, the remaining configuration can be set on the article properties dialog box. In SQL Server 2005, the advantage is that properties can be set collectively for a set of tables, rather than one by one as in SQL Server 2000.
By appropriately setting the properties, such as reversing the defaults values, the subscriber schema can be made identical to the publisher. The properties also take care of issues like timestamps automatically.
Conclusion
Using transactional replication as a disaster recovery option in SQL Server has become much simpler and less prone to errors in SQL Server 2005. By setting the properties of the table articles appropriately, the schemas of the publisher and subscriber can converge without the need for manual scripting and stored procedure modifications.
Overall, transactional replication provides a reliable and efficient method for implementing disaster recovery in SQL Server. With the improvements in SQL Server 2005, it is now easier than ever to set up and maintain a failover solution using transactional replication.
Article by Paul Ibison, www.replicationanswers.com