Replication and Database Mirroring - things to take care and best practices to administer the environment
Replication and Database Mirroring are the ones which can provide high availability option without an extra cost what it implies on a Clustering setup.
So in this scenario Database mirroring can be used in conjunction with replication to provide availability for the publication database. Database mirroring involves two copies of a single database that typically reside on different computers. At any given time, only one copy of the database is currently available to clients, this is known as the principal database. Updates made by clients to the principal database are applied on the other copy of the database, known as the mirror database. Mirroring involves applying the transaction log from every insertion, update, or deletion made on the principal database onto the mirror database.
Say if you need to setup the Disaste Recovery environment using Replication on top of Database Mirroring between the sites, you need to be ware of redirection of Publisher whenever the database mirroring failver ocurrs. As it doesn't happen that easily that you cannot "Redirect" replication subscriber server and you have to perform the setup of replication on the new mirror server with no requirement of snapshot of data. To give a clear direction what are the considerations you need to setup when using replication with database mirroring (here is the extract from BOL):
- The principal and mirror must share a Distributor. We recommend that this be a remote Distributor, which provides greater fault tolerance if the Publisher has an unplanned failover.
- The Publisher and Distributor must be SQL Server 2005 or a later version. Subscribers can be any version, but merge replication pull subscriptions from a version prior to SQL Server 2005 do not support failover; the agent in this case runs at the Subscriber and previous versions of the agent are not mirror aware. Replication to such Subscribers resumes if the database fails back from the mirror to the principal.
- Replication supports mirroring the publication database for merge replication and for transactional replication with read-only Subscribers or queued updating Subscribers. Immediate updating Subscribers, Oracle Publishers, Publishers in a peer-to-peer topology, and republishing are not supported.
- Metadata and objects that exist outside the database are not copied to the mirror, including logins, jobs, linked servers, and so on. If you require the metadata and objects at the mirror, you must copy them manually.
The last bit of copying the objects such as scheduled jobs and linked servers setup on the mirror server must be performed before the failover is tested or happens, this is one of the best practice to ensure that the complete setup of that SQL Server environment is like to like on the standby server to ensure that the high availability is not compromised.
It is useful to divide the best practice guidance into two areas:
- The following information covers best practices that should be implemented for all replication topologies:
- Develop and test a backup and restore strategy.
- Script the replication topology.
- Create thresholds and alerts.
- Monitor the replication topology.
- Establish performance baselines and tune replication if necessary.
- The following information covers best practices that should be considered, but might not be required for your topology:
- Validate data periodically.
- Adjust agent parameters through profiles.
- Adjust publication and distribution retention periods.
- Understand how to change article and publication properties if application requirements change.
- Understand how to make schema changes if application requirements change
Don't forget to perform the baseline the replication setup to ensure the performance is optimized with less number of tasks, as there are number of factors such as hardware, configuration, design of database and maintenance of replication environment is most important to continue the high-availability provision. Replication agents can take advantage of additional processors on the server. If you are running at high CPU usage, consider installing a faster CPU or multiple CPUs. The network can be a significant performance bottleneck, particularly for transactional replication. The propagation of changes to Subscribers can be significantly enhanced by using a fast network of 100 megabits per second (Mbps) or faster. If the network is slow, specify appropriate network settings and agent parameters. Business logic in user-defined triggers at the Subscriber can slow down the replication of changes to the Subscriber and because replication is easy to set up, there is a tendency to publish more data than is actually required. This can consume additional resources within the distribution databases and snapshot files, and can lower the throughput for required data. Avoid publishing unnecessary tables and consider updating publications less frequently.