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.
Tom Thomson
I’m worried about some of these “debunks”.
Number 4 is correct but it seems clear that the myth being debunked is that MS doesn’t have a big idiocy/usability problem in the UI here – it’s more a subject for a connect item than for a “myth” debunking – presenting it as a myth suggests that we should put up with this appalling bit or UI design.
Number 3 is debunking something that just is not a myth. “Committed” does not mean anything like “written to the database pages so that the log entries are now redundant” it means “committed to the log”. There may be a myth that says recovering committed transactions is low cost – if so debunk that (easily done) instead of pretending that there is a different myth.
Number 2 is corret but there are other work-arounds than the one described. I don’t think the other work-arounds should be used, but if you don’t mention them someone will notice them and use them. For example (at least in WIn2003, I haven’t looked at later versions) one can change the permissions/privileges of the Local System built-in account, so the debunk should point this out and also point out that doing so would be absolutely stupid.
SQLSoldier
Thanks for the feedback Tom!!
I agree that #4 would make a good Connect item. I consider it a bug. In fact, I consider that MSFT recommends starting in high performance mode and the default mode is high safety to be a problem. The default mode should be the one they recommend you start in unless a Witness is included.
For #3, I don’t agree that it not a valid myth. The myth is that many people do not understand what is meant by “committed” in this context. They think that committed means that the transaction is hardened to the database and not just the log file. When I tell people that SQL Server may have to roll transactions forward when they fail over, I usually get a response that the session was in sync and therefore all of the transactions were already committed.
I agree that there are different ways this could have been approached, but as is, it is a valid myth.
For #2, how would you change the permissions of the Local System account so that it can connect to a different SQL Server? If that is possible, I don’t know how to do it.
Again, thanks for the feedback. It was some very good comments!!
eastlandgrl
interesting, thanks
T-SQL Tuesday #11 Round up, Misconceptions in SQL Server | Sankar Reddy, SQL Server Developer/DBA
[…] Robert, the lead author of “Pro Database Mirroring”, shares some very good information around Database Mirroring myths. Its a fun technologu that was introduced in SQL Server 2005 and optimized even more in SQL Server 2008. […]
Rama
Hi Soldier,
Good explaination….
Could you have any steps for How to change the SQL server service account on the
DB mirroring server.
Thanks,
Rama
SQLSoldier
Thanks Rama!! The SQL Server service account is external to the database. You can change it without worry as long as it has CONNECT permissions to the endpoint of all other servers involved with mirroring sessions on the server.
Why Log Shipping is Better than Database Mirroring for Migrations – SQLSoldier
[…] blogged about this issue back in 2010: Top 5 Myths of Database Mirroring. When you mirror a database to a higher level version, at the point of failover, the mirroring […]
Why Log Shipping is Better than Database Mirroring for Migrations – SQL Server Best Practices
[…] blogged about this issue back in 2010: Top 5 Myths of Database Mirroring. When you mirror a database to a higher level version, at the point of failover, the mirroring […]