T-SQL Tuesday #011- Top 5 Myths of Database Mirroring
This blog entry is participating in T-SQL Tuesday #011, hosted this month by Sankar “@SankarReddy13” Reddy(Blog|Twitter). You are invited to visit his blog to join the blog party. You are welcome to write your own participating blog post for the party or just to read more blogs participating in this month’s theme: Misconceptions in SQL Server.
For my part in this month’s T-SQL Tuesday, I will be discussing my top 5 myths of database mirroring. Three of these misconceptions come up time and time again whereas two of them are rarely seen but I consider them to be very critical and I think everyone should know about them.
5. SQL Server ignores expiry date for certificates
This myth is mostly true. There are a couple of instances where this is not true, and database mirroring is one of those instances. When using certificate authentication for database mirroring, the expiry date is enforced and mirroring will fail if the certificates are allowed to expire.
The good news is that you don’t have to accept the default expiration date of 1 year. If you don’t specify an expiry date, the certificate will expire in 1 year. You can provide an expiry date for many years in the future. I like to choose an expiry date at least 5 years in the future because I anticipate that the hardware will likely be replaced by the time the expiry date passes. Nonetheless, the certificate expiration date should be tracked and planned for.
4. You can switch to high performance mode in the wizard as soon as you start mirroring
The best practice for starting database mirroring on a new mirroring session is to start it out in high performance mode (asynchronous) and then switch to high safety (synchronous) only when you are sure the session performs adequately. The catch is when mirroring starts, it is in high safety and you have to change it to high performance. The bigger catch is that if you are using the wizard (GUI) to configure mirroring and change the operating mode to high performance immediately after starting mirroring and before you close the wizard, the change is not made to the mirroring session. When you click okay, the wizard closes without changing the operating mode.
To actually make the change using the wizard, you must close the wizard and then re-open it and make the change.
3. Full safety ensures that the transaction is committed on the Mirror before finalizing the commit on the Principal
This is an over-simplification of how the process works. When running in full safety mode, the transaction must be committed to the log file of the Mirror database before the commit is finalized on the Principal. The difference is a significant one. If there is a backlog of transactions hardened to the log of the Mirror, time to failover could will take longer. The end result is that a mirroring session in the synchronized state may still have a lot of work to do for a failover.
When the database is recovered, the database must go through crash-recovery. During crash-recovery, active transactions not yet committed are rolled back and transactions hardened to disk but not yet applied are rolled forward. These transactions that have been hardened to disk but not yet applied to the Mirror are in the Redo Queue.
I have seen this manifest itself significantly when creating database snapshots (which also run crash-recovery) on a Mirror of a database with a very high level of transactions. This means you can’t simply check to see if the Mirror is synchronized to know that recovery will proceed quickly. Fortunately, SQL Server 2008 added many new performance counters for database mirroring, and there is a counter for this specific issue: SQLServer:Database MirroringRedo Queue KB.
This counter can be tracked or checked via Performance Monitor or via sys.dm_os_performance_counters:
SELECT instance_name, cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:Database Mirroring' AND counter_name = 'Redo Queue KB'
2. Mirroring must use certificate authentication if SQL is using built-in system accounts
Error 1418 is so well known, that it got its own page in Books Online. In actuality, the article in Books Online only lists a subset of potential causes for this error. One of the better known causes for this error is when you are trying to use Windows Authentication and the SQL Server service of one or more machines involved is running under the Local System built-in account. Local System has no network level permissions and cannot be used for Windows Authentication on another server.
This has led many to believe that you must use certificate authentication when using a built-in system account. This is true for the built-in Local System or Local Service accounts, but it’s not true for the Network Service account. If you are not allowed to use domain accounts for the SQL Server service in a particular domain (and I have encountered this requirement) and do not want to use certificates, you can use Network Service. To grant permissions to connect to the mirroring endpoint, you need to grant access to the other server’s machine account. This account is in the format of DomainMachineName$. So if the machine is named SQLProd01 and the domain is EnterpriseCorp, then you would need to grant CONNECT permissions to EnterpriseCorpSQLProd01.
GRANT CONNECT ON endpoint::[Database Mirroring] TO [EnterpriseCorpSQLProd01];
1. Rolling upgrade immediately suspends mirroring session on failover
Books Online describes the methodology for performing a rolling upgrade of the major version (i.e, SQL 2005 to SQL 2008) of SQL Server. Based on the description of the process, it sounds like the mirroring session is suspended as soon as the session is failed over to the upgraded Mirror. It does not say immediate, but that is what most people infer. It’s what I inferred until I learned better.
Books Online states:
After failing over from a SQL Server 2005 server instance to a SQL Server 2008 server instance, the database session is suspended.
What really happens is that SQL Server attempts to upgrade the database to bring it current with the Mirror. The upgrade may be successful to some point. The mirroring session does not suspend until the database upgrade fails. If the upgrade has successfully upgraded the database version, you could end up in a state where the database cannot be brought online.
Now imagine that after failover, there is a catastrophic event on the new Principal and you are forced to bring the original Principal back online. At first, you can’t do so because it is in a suspended mirroring session. So you drop mirroring and attempt to recover the database. Then you get error message 943:
Database '[DB Name]' cannot be opened because its version ([database version]) is later than the current server version ([server version]).
This further emphasizes the need to backup your database before you fail over. Fortunately, this was just a test and we did have backups.