There are a lot of products and product features available for creating a (more or less) high available environment. If you look at Microsoft SQL Server you have the option to create a Microsoft Cluster, you can use Marathon everRun, VMware Fault Tolerance if you have a 1 vCPU SQL server, etc.
When I was researching the possibility to create a redundant Microsoft SQL Server environment without the use of shared storage I noticed that a lot of the options needed shared storage: clustering, FT, etc. Some products, like everRun, can be used without shared storage, but are very costly.
An alternative to the options above is Database Mirroring (DBM), which is a part of Microsoft SQL Server. Database Mirroring (DBM) is in essence the possibility to replicate/mirror all database content to a second database server. With DBM you can achieve high availability for your databases without the hassle of Microsoft Clustering Services (MSCS) and without the need for shared storage.
Using database mirroring is one of my favourite solutions, because:
- it can be used in environments without shared storage;
- the setup can be active/active configuration, where both nodes are used for holding databases;
- you can enable mirroring per database;
- you give control back to the DBA’s for choosing whether or not they want their database to be protected or not;
- it can be used on shared storage, for example when you use shared storage based on iSCSI in a virtual environment (which isn’t supported at the moment);
- you can mirror databases on servers with more than 1 vCPU (the limit for FT in vSphere at this time);
- management of the Windows Server gets easier.
You can argue about the management of the SQL environment. Since you don’t use Microsoft Clustering Services the management of Windows is somewhat easier, but the management shifts to the database administrator.
From the Microsoft MSDN:
Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record.
Without witness:
In the scenario without a witness server/instance, all transactions are transported to the secondary database. If the principal (srv1) database server fails, the DBA has to perform some manual actions to bring up the database on the secondary server (srv2).
with witness:
If you use a witness server or instance the fail over can be transparent. Users will not notice the fail over most of the time IF the application is capable of using database mirroring. When your application uses the native SQL client or ADO.NET the fail over will be transparent.
Database side
Here are the steps to create a mirrored database:
Principal (Primary) server:
- Make sure you have a database that has it’s recovery mode set to Full. When you’re migrating from SQL Express to SQL Standard the databases are in Simple recovery mode;
- Set the database option “AUTO CLOSE” to Off. Again, if you’re migrating from SQL Express this value is set to ON;
- Create a full backup of the database;
- Create a transaction log of the database.
Mirror server:
- Restore the full backup onto a second server with the NO RECOVERY option;
- Restore the transaction logs onto a second server with the NO RECOVERY option;
After both restores the database is still in ‘restoring’ mode. This is the intended mode.
Principal server:
Now that you prepared the primary database and the mirror database it’s time to set up the mirroring:
- Create mirror of the database (right click->tasks->mirror);
- Choose whether or not you are going to use a witness server;
- Choose the server with the primary database as principal server;
- choose your 2nd server as standby/secondary server;
- Choose your witness server, if you have one. This can be a SQL Express edition;
- Enter the appropriate login credentials;
- Start mirror.
Voilà. You’re done.. If you aren’t using database login accounts.
If you are using database login accounts you have to make sure that the login accounts on both servers are synchronized, since they are not replicated. Check this Microsoft KB article for more information on synchronizing user accounts between the two servers.
As with all fail over solutions make sure that the servers don’t end up on the same virtualization host.
Client Side
On the client side some small changes have to be made to be able to use the automatic fail over. Changing the connection string from
“Server=srv1;database=db1”
to
“Server=srv1; Failover_Partner=srv2; Database=db1”
is enough to use the newly creating high(er) available solution.