By Ron Barrett
We have reached a point in business where recovery from failures needs to happen in seconds not minutes. When I was an IT Director for a Financial Services firm in NYC, partners would remind me daily that every time the databases for our CRM, Financial Applications or Time and Billing system where down that we would be losing tens of thousands of dollars an hour. I mention this because it has been my experience that the idea of highly available systems only now seems to be taking hold in the SMB market.
Many enterprises know and acknowledge the need for availability. Sadly even some of these networks run with little or no thought of what happens if the system simply stops working. The nature of today’s business makes it vital to ensure that you can not only continue business, but continue with little to no interruption to the end users.
Although SQL Server 2008 provides several methods of meeting this goal, Database Mirroring, provides a solution that is easily managed and responds fast to a failover, with zero loss of committed data.
The Microsoft SQL Server TechNet Virtual Lab: Database Mirroring takes us through the process in two steps:
1. Establishing the Mirror
2. Simulating a Failover
Unlike some labcasts this lab does not give an overview of the tools you will work with, rather it allows the user to jump right in and begin working. By default it opens the “Database Engine” for “SQL08DEMO”. However, it is important to be sure you’re working on the correct Server Type and Server Name before clicking Connect.
Once the SQL Server Management Studio is launched, users are faced with three panes: the Object Explorer, Object Explorer Details pane and the Solution Explorer. Resizing the Object Explorer allows for better views of the scripts to be run in the Object Explorer Details pane.
The lab next takes you through the process of opening and executing the 5 preset scripts. These scripts will setup the Primary, Secondary, and a third “Witness” server to act as a mediator in case of failure to ensure a quick failover response.
TIP: Script number 4 and 5 will show an error after they are executed. This is because the Adventureworks database has not been refreshed yet.
Next you’ll run through setting up the mirror server instance and adding it to the object explorer.
TIP: The lab tells you to go to the registered servers view to find SQL08DEMO\MIRROR. You will first need to add the registered server before it appears in the registered servers.
Finally you need to check that the Secondary database has been created in the mirror database before moving on to the SQL Server Failover Test.
In the failover test the lab simulates both the failure and failback. The performance monitor shows the simulated load on the systems.
This lab, Database Mirroring, was a quick and fun way to get a grasp on how to prepare your database for high availability. As I said before SQL Server 2008 has several high availability options but as you will learn in this lab, Database Mirroring can be set if needed by a newcomer to SQL Server. There you go; no more excuses for database downtime.