SQL Server has a variety of options when it comes to automatically creating "copies" of databases on other SQL Servers. This may be done for a variety of reasons: a) high availability - to ensure a database never goes down b) for reporting - to offload to another SQL Server a copy of the data c) to handle remote locations - if 20 locations all need to be able to view and/or update data in a shared database d) for performance - to offload to another SQL Server a copy of the data to spread the workload SQL Server has a variety of options:
It is sometimes hard to keep track of which of these approaches is best for the situation. SUMMARY OF THESE APPROACHES Server Clustering Key thoughts:
Failback policies: By default, groups are set not to failback. Unless you manually configure your group to failback after failover, it continues to run on the alternate node after the failed node comes back online. When you configure a group to automatically failback to the preferred node, you specify whether you want the group to failback as soon as the preferred node is available or to failback only during specific hours that you define. This option is useful if you want the failback to occur after peak business hours, or if you want to make sure the preferred node is able to support the group when it does come back online. When setting the "Allow failback", you have two options, Immediate and Failback Between. When setting it to Immediately, ailback will occur as soon as the Cluster Service detects that the preferred owner is now online. When setting the Failback Between, it is hours on a 24 hour clock (i.e. 1 is 1:00am, 15 is 3:00pm, etc). So if you were to set this to Failback between 1 and 2, it means between 1:00am and 2:00am. This is used to prevent interruption of the availability of the cluster resources in that group during peak hours.If you were to set this to between 1 and 1, it means the current 24-hours and is the same setting as Immediate. Database Mirroring 3 configuration options:
Key thoughts:
To apply patches in a mirroring setup: 1. Patch the witness first. 2. Pause the mirroring. 3. Patch the mirror and re-boot if necessary. 4. Resume mirroring. 5. Let synchronization on the mirror catch up so the mirror is current. 6. Fail over to the mirror. 7. Pause mirroring. 8. Patch the principal and re-boot if necessary. 9. Resume mirroring. 10. Let synchronization on the principal catch up so the principal is current. 11. Fail over to the principal. Log Shipping Key thoughts:
REPLICATION Replication basics: 3 databases are involved: Publisher, Distributor and Subscriber(s). The Distributor can be on the same database server as the Publisher, or it can be remote. Publications consist of multiple articles. Subscriber servers subscribe to a publication not an article. Transactional Replication Key thoughts:
Merge Replication Key thoughts:
|