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.

SQL Configuration Manager

SQL Configuration Manager

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.

SQL Mirroring Configuration Manager

SQL Mirroring Configuration Manager

SQL Mirroring Configuration Manager

SQL Mirroring Configuration Manager

Enter the password. When you click OK, the service will restart. You will then see the service account Logon in Configuration Manager.

SQL Mirroring Configuration Manager

SQL Mirroring 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.

SQL Mirroring Configuration Manager

SQL Mirroring Configuration Manager

Click the drop down to change the Start Mode.

SQL Mirroring Configuration Manager

Services set to start automatically

Navigate to the Protocols section and insure that TCP/IP is enabled and the others are disabled.

SQL Mirroring Configuration Manager

TCP/IP protocol enabled

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?

SQL Mirroring AdventureWorks

AdventureWorks License

SQL Mirroring AdventureWorks

AdventureWorks Setup

Accept the default to install the suite. It takes about 10 minutes to install.

SQL Mirroring AdventureWorks

AdventureWorks installation completed

In SQL Server Management Studio, (SSMS), expand the Databases folder. Click the AdventureWorks Properties and note the settings.

SQL Mirroring AdventureWorks

AdventureWorks General Properties

SQL Mirroring AdventureWorks5

AdventureWorks data file locations

Mirroring only supports databases using the Full Recovery model. Under Options, change the Recovery Model from Simple to Full.

SQL Mirroring AdventureWorks6

AdventureWorks Full Recovery mode

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.

SQL Mirroring Setup1

SQL Mirroring Setup

 

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.

SQL Mirroring Setup

SQL Mirroring Setup

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:

SQL Mirroring Setup3

SQL Mirroring Setup

Set up Mirroring

Open properties of the AdventureWorks database and go to Mirroring.

SQL Mirroring Setup4

SQL Mirroring Setup

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.

SQL Mirroring Setup

SQL Mirroring Setup Wizard

Click Next.

SQL Mirroring Setup6

SQL Mirroring Setup

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.

SQL Mirroring Setup7

Configure a Witness server

Click Next.

Configure the Principal (L7TGAPPCRM11).

SQL Mirroring Setup

SQL Mirroring Setup

Click Next.

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

SQL Mirroring Setup9

SQL Mirroring Setup

Click Next.

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

SQL Mirroring Setup10

SQL Mirroring Setup

Click Next.

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

SQL Mirroring Setup11

SQL Mirroring Setup

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

Click Next.

The Wizard will complete the setup as shown below.

SQL Mirroring Setup12

SQL Mirroring Setup

The following summary appears.

SQL Mirroring Setup13

SQL Mirroring Setup

You can start Mirroring from the summary window or start it from the database properties as shown below.

SQL Mirroring Setup14

SQL Mirroring Setup

Click Start Mirroring.

Upon successful startup, the Status changes as shown below.

SQL Mirroring Setup15

SQL Mirroring Setup

 You can check the status and confirm the Principal in SQL Server Management Studio as shown below.

SQL Mirroring Setup16

SQL Mirroring Setup

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.

SQL Mirroring Testing

SQL Query – Updated field

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.

 

 

This entry was posted in Lab Projects, Reviews, Tips and tagged , , , , , , , . Bookmark the permalink.

6 Responses to SQL Server 2008 Database Mirroring

  1. Pingback: SQL Failover Cluster – Part 1 | Level 7 TechnoBlog

  2. Jimmy says:

    Great step by step article. How would I disable automatic failover?

  3. AlvarezG says:

    What kinds of things causes the failover to occur? Some examples would be helpful? Nice article.

    • John says:

      Thanks for the question. It seems simple at first glance. One reason could be the OS of the principle server hangs for some obscure reason, maybe someone pulls out a dvd that was in use. I decided to research this a little further and will send you off to an article on MSDN that discusses some possible failures that can occur and what actions can be taken. It is a good article that can be used as a springboard for further research. http://msdn.microsoft.com/en-us/library/ms190913.aspx

  4. JeremyZ says:

    Awsome post. I ran into a problem installing SQL Server 2008 R2 on Windows Server 2008 R2 SP1. It seems as though you need the setup program that comes with SQL 2008 R2 SP1 in order to install SQL on Windows 2008 R2 or R2 SP1.
    This post explains the entire procedure: http://wadingthrough.com/2009/09/14/problem-installing-sql-server-2008-on-windows-2008-r2/
    In a nutshell download SQL 2008 R2 SP1 and start the setup program. The setup files will install but it won’t update because there is nothing to update. Close the program and go back to the SQL 2008 R2 media and run setup. It will use the newer setup files. After installing go back and run the SQL 2008 R2 Sp1.

Leave a Reply

Your email address will not be published. Required fields are marked *