12 High Availability
(Part of a series of SQL Server 2012 training course notes. Previous | Contents | Next)
SQL Server does not offer anything like Oracle RAC (Real Application Cluster). If RAC-like functionality is a requirement for your system, then you must drop SQL Server from consideration.
Failover Clustering – active-passive cluster with shared disk storage. Differences from Oracle RAC are:
- (crucially) Oracle RAC is active-active, while in Failover Clustering, the DR server sits idle and unused until disaster strikes.
- The Clustering is controlled and configured at operating system level as a Windows Cluster. That means Windows system administrators create and control the system, unlike RAC which is mostly done at Oracle level rather than by OS system administrators.
Synchronous Database Mirroring – again an active-passive confugration, but not part of a Windows Cluster, instead uses two unrelated servers with different disk storage to keep a passive copy of the active database. This is the equivalent of Oracle Dataguard with Synchronous Redo Transport. An advantage over RAC is that separate storage is used, not shared.
Asynchronous Database Mirroring – Waits for write to both systems before acknowledging back to user process. Requires a fast network connection between both servers, This is the equivalent of Oracle Dataguard with Asynchronous Redo Transport.
Database Log Shipping – the old version of Asynchronous Database Mirroring. Used in the past (Database Mirroring only became available from SQL Server 2005 with Service Pack 1). The log shipping can be suspended during the day to allow the second server to be available as a read only reporting system. It can then be resumed at night to allow second server to catch up with live changes.
Replication – Is not strictly high availability, but can be used as a high availability solution if all main tables are replicated. This solution (only) would allow for active-active configuration with updateable replication.
12.1 Failover Clustering
A Failover Cluster can only be installed on a previously built Windows cluster running Microsoft Clustering Services. That is normally a Windows Sys Admin task rather than for the DBAs.
The first node is installed with SQL Server by choosing the “New SQL Server failover cluster installation” option on the first screen of the normal SQL Server Installation Centre.
The subsequent nodes are installed by choosing the “Add node to a SQL Server failover Cluster” option from that same first screen of the normal SQL Server Installation Centre.
The installation is similar to standalone with a few cluster-specific screens.
In the Cluster Resource Group dialog box, check the resources available on your Windows Server 2012 cluster. This will tell you that a new Resource Group will be created on your cluster for SQL Server. To specify the SQL Server cluster resource group name, you can either use the drop-down box to specify an existing group to use or type the name of a new group to create it.
In the Cluster Disk Selection dialog box, select the available disk groups that are on the cluster for SQL Server 2012 to use. In this example, two clustered disk groups – APPS and APPS2 – have been selected to be used by SQL Server 2012.
In the Cluster Network Configuration dialog box, enter the IP address and subnet mask that your SQL Server 2012 cluster will use.
To manage the cluster, open the Failover Cluster Management console, and click on SQL Server (MSSQLSERVER) under Services and Applications. Make sure that all dependencies are online. There is an option to move the service to another node.
12.2 Client Failover Connection String
Ideally you want clients to automatically connect to the new live server in the event of a failover. This requires the DR server name to be specified in each client connection string, like this:
Data Source=myServer; Failover Partner=myMirrorServer; Initial Catalog=myDataBase; Integrated Security=True;
The client will then first try to connect to “myServer”, if that does not succeed will automatically try to connect instead to “myMirrorServer”.
Floating I.P. Addresses are also used, so there may be a hostname which will resolve to either physical host depending on which is specified as active. Both those methods could be combined.
12.3 Database Mirroring Procedure
- Backup database on primary
- Copy backup file to mirror server
- Restore database on mirror server – with NORECOVER option
- On primary – database > options > mirror
To failover: on primary database > options > mirror > click “failover” button
To force failover, used only if primary server is dead: on mirror database
New Query > “ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS”
Note that mirroring is database level, not server/instance level, so logins and jobs are not replicated over and must either be added to both sides manually or “SQL Server Integration Services” used to create a produced that will be scheduled into a nightly job to copy both logins and jobs from live to DR server. SSIS includes these in its integration services toolbox.
(Part of a series of SQL Server 2012 training course notes. Previous | Contents | Next)
Leave a Reply