July 27, 2009 at 2:47 pm
I am in the process of deciding on what to use about keeping a database on standby in case of a failure on the primary database. I read up on replication and mirroring. I understand the pros and cons of both, but there is one answer I can't find. If I use database mirroring and have the mirror database on a different host, what happens when the primary database host hardware goes down for maintenance such as windows updates? I would rather not have automatic failover so the primary database server stays the primary database server. Also, in case of a hardware failure on the primary server, how will the clients connecting to the primary database know to use the mirrored database. Do I need to repoint connection settings to the mirrored database if it is on a different host? None of the literature I've come accross talks about this. It only talks about it when using replication.
Thanks.
July 27, 2009 at 3:08 pm
You can setup database mirroring without a Witness server so it will not automatically fail over when you do maintenance on the Principal server. Using synchronous mode without a witness or asynchronous mode will allow you to manually fail over to the Mirror server whenever you want. In the event of a failure you will either need to repoint clients or you can use the Failover Partner property in the connection string described here...
July 27, 2009 at 4:04 pm
I'd go with Ken's suggestion. I would either disable automatic failover, or not set it up.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 27, 2009 at 5:11 pm
Thanks! That's the exact answer I was looking for. One more question if you don't mind me asking...Do I risk any data loss on the secondary mirrored database if the secondary mirrored database host server goes down for maintenance and comes back up in about ten minutes? Will the primary database be smart enough to know that the secondary went down and catch it up to speed when it comes back online? I plan and doing asynchronous mirroring for performance reasons so the update is done first on the primary.
Thank you.
July 27, 2009 at 7:24 pm
The transactions that haven't been applied on the mirror database will queue up on the principal until the mirror comes back online. You can see this happening in realtime if you use the Database Mirroring Monitor interface. The "unsent log" values will increment until the mirror comes back online. Just an FYI, if you plan on having your mirror database down for a considerable amount of time, the transaction log on the principal database won't get truncated even if you back it up. Just like with replication, the log on the principal holds onto marked transactions until they are applied at the mirror.
Hope this helps!
July 27, 2009 at 7:27 pm
When you take the Mirror server down, the logs will contiue to build up on the Princiapl server until the Mirror server comes online again. Then the Principal server will start sending the transactions to the Mirror server. What you have to keep in mind is that when the Mirror server is down, the transaction log continues to grow on the Principal server until the Mirror is up again. If the Mirror is down for a long time, you could fill up your transaction log drive on the Principal server. If it is just down for 10 minutes or so, that should be fine.
July 27, 2009 at 7:32 pm
I see Chris beat me to it. Here is a really good whitepaper that covers all of the failover scenarios.
July 28, 2009 at 9:41 am
Thank you all. This helps a lot!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply