Database mirroring as alternative to clustering and FT
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.
Tags In
Related Posts
9 Comments
Leave a Reply Cancel reply
You must be logged in to post a comment.
Hi Anne,
I came across the same thoughts 5 years ago when there wasn’t a single line of code for FT yet.
There was (is) MSCS but it is such an overhead compared to DBM.
We setup it up across two datacenters connected through a dark fiber, two separate storage devices, we used SSL certificates for authentication because the DB servers were not sharing the same AD forest.
We picked up async method instead of sync because of the way sync works where DB at principal must wait for ACK from mirroring at secondary before committing into the DB at principal. Even thought the TTL between the two sites was below 2ms, still it was too much delays for our production databases at principal.
N.B. the same issue exists with some SAN mirroring tools such EMC’s S/SRDF, so it is not a MS DBM issue…
Anyway, I would also recommend this path for redundancy/fault tolerant scenarios but you need two license of the pricy MS SQL Enterprise version…
Rgds,
Didier
Didier,
on a side note, when is full failover ever cheap? The way I always explain this to my clients: what will it cost you if the service is down? What is it worth to you to make sure it will keep on running without downtime and dataloss.. if it really is a core service, this will be a very short conversation :)
Personally I think DBM still is a relatively cheap way of making sure your service is dependable.
Cheers,
Alex
Is it with database mirroring possible to patch the pricipal WIndows 2003/ 2008 server with MS WSUS with out downtime?What about the third party tools like neverfail and double take ? Wouldn’t this third party tool eleminate any downtimefor sql?
Meistermn
You could, but WSUS doesn’t do an automatic fail-over for the SQL server.
I can imagine that you schedule a SQL query to do the fail-over before patching the principal server on your patchday. The secondary server then becomes the principal server. I haven’t tried it though.
Neverfail and double take will also reduce your downtime, but they also need extra licenses. Which option you choose depends on how much downtime you are prepared to take and how large your budget is.
I’ve deployed DBM on a couple of scenarios and I do like it, though there are a couple of things to bear in mind.
For a large DB you are doubling your storage requirements as well as your licencing requirements. There is also a bit of CPU overhead with DBM , possibly more so than with MSCS ( thoguh of course DBM is much more suitable for deployment within a Virtualised Environment. With older versions of SQL there were limits on the number of databases you can mirror. Also for applications that create their own databases , they will need to be mirroring aware as the mirror is set up at the db level , not the server level.
According to the SQL server licensing guide, at the least the 2008 version,
“Does not require separate SQL Server license as long as it is not serving data to clients or running active SQL Server workloads”. If the first server fails you can run on the secondary server for 30 days before needing an extra license.
As far as I know (and could find) you can use both standard and enterprise for database mirroring.
You’re totally right on the delays. This can be a serious problem.
sorry for my ignorance but where did you setup that on the 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.
thanks
This depends on the client. If your application uses the Microsoft Datasources you can check it under Data Sources in the Windows Control panel on your client. Otherwise the client might use a configuration file where you can change it.
If neither of these places is used you’re out of luck. The connection string is then probably hardcoded in the application. This is often the case with inhouse developed applications in my experience. Your best bet then is to contact the application developer to change it.