Transcript Database Mirroring: Maximizing Availability Through Automation

Database Mirroring:
Maximizing Availability
Through Automation
Robert L Davis
Who am I?
 DBA on the CAP Operations Team
 Worked as a database developer/administrator
since 2000
 Been at Microsoft for 2 ½ years as a vendor and
now as an FTE
 Co-author of Pro SQL Server 2008 Database
Mirroring (Oct. 2009)
 Blog:
http://www.sqlservercentral.com/blogs/robert_davis
Automation
 How can automation help me maximize availability of
my mirrored databases?
 When a controlled failover occurs, a short outage occurs.
 The longer it takes for the failover to occur, the longer your
outage is.
 Manual intervention is prone to errors and missed steps.
 T-SQL never feels stress when others are panicking.
 You don’t need to be a Subject Matter Expert to run a
script or procedure.
 Common pitfalls that you may potentially have to deal with
can be built in to the scripts or procedures.
Demo Scenario
Your principal server is on the verge of failing and needs a Windows hotfix
installed immediately. Your supervisor wants you to fail over the mirrored
databases. Since this is an unplanned, unauthorized downtime, you must
do it as quickly as possible.
Demo 1: There is no automation in place. You are not the SME for database
mirroring. Your knowledge of how to fail over a database is limited to
using the SSMS GUI.
Demo 2: There is no automation in place. You are not the SME for database
mirroring, but you are well read on the subject and eager to put what you
know to work.
Demo 3: You are the SME for database mirroring. You knew something like
this would happen. You have planned for this, and you have put
automation procedures in to place and have tested them during monthly
patching, Service Pack upgrades, and other events.
Demo Scenario ……
To make matters worse, your supervisor is
standing in front of your desk repeatedly
emphasizing how important it is to do it quickly
and staring at you like he expects you to mess
things up.
And Tom, the new Jr. DBA they just hired, is
standing just outside the doorway listening for
you to mess things up so he can “save the day”
and get the promotion that you are expecting to
get.
Conclusion
Without a doubt, the automated failover process is faster and
accounted for potential problems after failover that the manual
processes did not.
In Demo 1, Tom “saved the day”. Your supervisor is happy … that
Tom just happened to be in the right place at the right time.
In Demo 2, Tom didn’t get to save the day during the failover, but he
later identified why the application was failing and ended up the
hero anyway.
In Demo 3, your supervisor was delighted with how fast things
went. He looked at Tom lurking in the doorway and barked at him
to get back to work. You were the hero.
Database Mirroring:
Maximizing Availability
Through Automation
Q&A
Database Mirroring:
Maximizing Availability
Through Automation
Thank You!
The PowerPoint slide-deck and the stored
procedures will be posted on my blog this
weekend.
http://www.sqlservercentral.com/blogs/robert_davis