Database Reference
In-Depth Information
■
you can still use Database Mirroring in such a scenario by implementing a routine that monitors the principal
server database location and fails over the databases if needed. One possible implementation is a sQl agent Job, which
is running every minute and querying the
State
or
State_Desc
columns in the
sys.databases
view for one of the
databases in the group. the job could fail over other databases in the group when it detects that the database is in
restOring state, which means that it was failed over to a different server.
Tip
It is extremely important to test your high availability strategy and perform failover after it is implemented
in production. The situation where everything works perfectly the first time is extremely rare. You may encounter
security issues, incorrect settings in application connection strings, missing objects on the servers, and quite a few
other issues that prevent the system from working as expected after failover. Even though testing of the failover
process can lead to system downtime, it is better to have a controlled outage with all personnel on-deck, rather than
the situation when the system does not work after an unplanned disaster.
Finally, you should regularly re-evaluate and test your high availability and disaster recovery strategies. Database
size and activity growth can invalidate your HA implementation, making it impossible to meet RPO and RTO
requirements. It is especially important when secondary (stand-by) servers are less powerful than the primary ones.
It is entirely possible that the system would not be able to keep up with the load after a failover in such cases.
Summary
Even though high availability and disaster recovery strategies are interconnected, they are not the same. A high
availability strategy increases the availability of the system by handling hardware or software malfunctions
transparently to users. A disaster recovery strategy deals with situations that the high availability strategy was unable
to handle and when the system needs to be recovered after a disaster.
A SQL Server Failover Cluster protects you from server failures by implementing a clustered model using a SQL
Server instance as the shared resource. Only one server/node can handle users' requests at any given time; however,
a Windows Server Failover Clustering Cluster can host multiple SQL Server clusters. Even though running multiple
instances of a SQL Server Failover Cluster is a common practice, which helps to reduce the cost of the solution, you
should avoid situations where the cluster does not have spare passive nodes and multiple SQL Server instances
running on the same node after failover with unacceptable performance.
A SQL Server Failover Cluster uses shared storage, which becomes the single point of failure. You should combine
the failover cluster with other high availability technologies that store the data on different storage media to minimize
the possibility of data loss due to the storage failure.
Database mirroring allows you to maintain a byte-to-byte copy of the database on another server by constantly
sending transaction log records over the network. With synchronous database mirroring, SQL Server does not commit
the transaction on the principal server until the log record is hardened on the mirror server. This approach guarantees
no data loss for committed transactions, although it adds extra latency to the transactions. With asynchronous
mirroring, log records are sent asynchronously and data loss is possible. Data loss is possible even with synchronous
database mirroring if the mirror server is offline or not fully synchronized.
Database mirroring provides automatic failover in synchronous mode with an additional SQL Server instance,
which works as a witness.
AlwaysOn Availability Groups use a similar technology to database mirroring and allow the creation of an
infrastructure with one primary server that handles read/write activity and multiple secondary servers, which allow
read-only access to the databases. AlwaysOn should be installed underneath the Windows Server Failover Clustering
Cluster, although every node uses separate storage for the databases.
Both database mirroring and AlwaysOn Availability Groups support automatic page repair. A fresh copy of the
page retrieved from a different server can replace the corrupted data pages.
