This post is going to cover SQL Server Mirroring. SQL Server 2005 will support mirroring, but it’s best to have SQL Server 2008 or SQL Server 2008 R2 Enterprise installed on your servers. The Level 7 Tech Group lab is virtualized on Windows Server 2008 R2 Service Pack 1 using Hyper –V. The SQL nodes (servers) are using Windows Server 2008 R2 Service Pack 1 and SQL Server 2008 Service Pack 2. When mirroring, it is recommended to use the same hardware / software platform with the same operational configuration including disk (size, drive letters, SQL file location, memory and processor). Here’s how we do it.
SQL Operational Configuration
Use SQL Configuration Managers to set Permissions for the services to run under and the network protocols to use. Never use Windows Server Manager to set any SQL service property. It will break SQL! Also, best practices would have you stop and disable any services that are not required for your operation. We will concentrate on the Engine and the SQL Server Agent for the mirroring exercise.
Right click on the Service name, for example, the SQL Server (MSSQLSERVER) and use the familiar Select User or Group dialog to select and validate your choice.
Enter the password. When you click OK, the service will restart. You will then see the service account Logon in Configuration Manager.
Perform the same operation on the SQL Server Agent service. The default configuration for the SQL Agent service is disabled. Right-click the properties, go into the Service tab and change the Start Mode to Automatic. Change the Start Mode to disable any services that are not necessary for the application. Taking this one step further, it is a best practice to install the SQL components necessary for the role. Additional services can be installed at a later time.
Click the drop down to change the Start Mode.
Navigate to the Protocols section and insure that TCP/IP is enabled and the others are disabled.
This completes the section on the operational environment.
Install Adventure Works
SQL Server does not automatically install sample databases as in previous versions. If you do not have a production database to use, then install the AdventureWorks sample databases from the Microsoft Download, MSDN or TechNet sites. Best bet is to search Google for the latest source. I downloaded it for the SQL Community site which seems to be its current home. Launch the setup program. Accept the license terms. Do you have a choice?
Accept the default to install the suite. It takes about 10 minutes to install.
In SQL Server Management Studio, (SSMS), expand the Databases folder. Click the AdventureWorks Properties and note the settings.
Mirroring only supports databases using the Full Recovery model. Under Options, change the Recovery Model from Simple to Full.
Create database on the mirror partner.
In order to establish mirroring, the database must be installed on the partner server. Insure that the database files are in the same location on both servers. There are a number of ways to establish the database.
1. Script the database creation to a t-sql file.
2. Copy it to the partner and then execute it in a query window.
3. Backup the database, Tasks | Backup. Default location is C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup
4. Copy database backup to the partner and restore with NORECOVERY.
Click the middle Recovery State option to leave the partner database in a recovering state.
When this is complete, you’ll see the database in the partner server with a status of “Restoring” as shown below:
Set up Mirroring
Open properties of the AdventureWorks database and go to Mirroring.
Add SQL Engine to the Windows Firewall Exception. Go to Administrative Tools | Windows Firewall with Advanced Security, Create New Rule | Program C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL\Binn\slwservr.exe = Allow
Click Configure Security button to launch the Wizard.
A witness server is a third SQL server that participates in the mirroring process. It’s role is to keep a heartbeat with the mirrored partners. When the heartbeat fails with one of the partners, an automatic failover may be automatically initiated.
Configure the Principal (L7TGAPPCRM11).
Click “Connect…” button to logon to the Mirroring Partner (L7TGAPPSP10).
Connect to the Witness Server using the Connect… button to logon to validate the connection.
Specify the service accounts that will be used on all participants.
Make sure the Accounts are in the form <domain>\<service id>.
The Wizard will complete the setup as shown below.
The following summary appears.
You can start Mirroring from the summary window or start it from the database properties as shown below.
Click Start Mirroring.
Upon successful startup, the Status changes as shown below.
You can check the status and confirm the Principal in SQL Server Management Studio as shown below.
Management studio shows the two servers. The Principal identifies itself and shows the status as Synchronized while the Partner displays the “Restoring…” status.
Testing the Mirror Failover
Using SSMS, open the AdventureWorks.dbo.Person.Contact | right-click edit top 200 rows. Change Gustavo Achong Middle Name Null to Lingcod. Move off the cell click the Next arrow at the bottom of the page to commit.
Right-click the AdventureWorks database | Tasks | Mirroring. Click the Failover button. On the new Principal, open the table to the row you previously modified and check the value of Gustavo’s Middle name.
SQL Server 2008 provides a High Availability option in the form of database mirroring – the process of having two servers supporting a single database on each server. The principal server connects to the front-end application and sends database updates to the mirror partner when a transaction is completed. The partner database is in a continual recovery mode as the database is updated. A Witness server monitors each database partner and may initiate an automatic failover. The front-end program will have a connection string to both servers. If you connect with ADO.NET or the SQL Native Client to a database that is being mirrored, your application can take advantage of the drivers ability to automatically redirect connections when a database mirroring failover occurs. You must specify the initial principal server and database in the connection string and the failover partner server. See the following example:
Data Source=L7TGAPPCRM11; Failover Partner=L7TGAPPSP10; Initial Catalog=AdventureWorks; Integrated Security=True;
High Performance / High Availability
Things to consider
The experts at SQL Server Magazine
Paul and Kimberly Randal’s Blog
MSDN – Database Mirroring
MSDN – Database Mirroring Administration
Technet – Database Mirroring and Failover Clustering
How to: Force Service in a Database Mirroring Session (Transact-SQL)
T-SQL solution to servers stuck in recovery:
RESTORE DATABASE <DBNAME> SET PARTNER OFF
How to commit a transaction to the database on the principal
How to determine principal
SQL 2008 R2 T-SQL reference