SQL Server 2008 Database Mirroring

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.

Click Next.

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.

Click Next.

Configure the Principal (L7TGAPPCRM11).

Click Next.

Click “Connect…” button to logon to the Mirroring Partner (L7TGAPPSP10).

Click Next.

Connect to the Witness Server using the Connect… button to logon to validate the connection.

Click Next.

Specify the service accounts that will be used on all participants.

Make sure the Accounts are in the form <domain>\<service id>.

Click Next.

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.

Conclusion

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;

References:

High Performance / High Availability

http://technet.microsoft.com/en-us/library/ms188712.aspx

Introduction

http://www.extremeexperts.com/sql/articles/DBMirroring1.aspx

http://www.databasejournal.com/features/mssql/article.php/3828341/Database-Mirroring-in-SQL-Server-2008.htm

Things to consider

http://support.microsoft.com/kb/2001270

The experts at SQL Server Magazine

http://sqlmag.com/

Paul and Kimberly Randal’s Blog

http://www.sqlmag.com/author/5613129/PaulRandal.aspx

Michael Otey

http://www.sqlmag.com/author/5030568/MichaelOtey.aspx

MSDN – Database Mirroring

http://msdn.microsoft.com/en-us/library/bb934127.aspx

MSDN – Database Mirroring Administration

http://msdn.microsoft.com/en-us/library/ms177412.aspx

Technet – Database Mirroring and Failover Clustering

http://technet.microsoft.com/en-us/library/ms191309.aspx

How to: Force Service in a Database Mirroring Session (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms189270.aspx

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
http://www.go4answers.com/Example/different-mirroring-states-within-163564.aspx

How to determine principal
http://msdn.microsoft.com/en-us/library/ms365781(SQL.90).aspx

SQL 2008 R2 T-SQL reference
http://msdn.microsoft.com/en-us/library/bb510741.aspx

This posting is provided “as is” with no warranties, guaranties or any rights whatsoever. All content is based on the author’s experiences and opinions and is not intended to influence the actions of the reader.