Step-by-Step SQL Mirroring the Database

Introduction

In this article, I'm going to cover:

• Setting up the ASPState database in a mirrored environment
• Configuring an ASP.NET web-application to use the mirrored ASPState database
• How to do a manual failover (for maintenance scenarios)
• How to do a forced failover (when the database server unexpectedly goes boom)

Background

It's fairly common for businesses to want to provide some high availability for their SQL Server databases, and one option is to have two SQL Server databases on separate machines with a SQL Server database mirrored. Microsoft provides mirroring out of the box in SQL Server 2005 and SQL Server 2008, and is a much cheaper alternative than going down the clustering/failover route, but does provide some protection. In mirroring, there is always one Principal database which serves the requests, and a standby Mirror that is always synchronizing. If the Principal database goes down, then the Mirror can be forced to become the Principal, and will then serve the requests. Once the original Principal is available again, it will become the new Mirror.

Setting up a mirrored database is not straightforward as it should be. Although there is a wizard, there are a number of steps that must be performed before a database can be mirrored, and also a number of "gotchas" which prevent it from working. This I found, to my own pain and frustration, over a number of days. Although Microsoft provides a large amount of documentation on the subject, a lot of it may not be relevant, and it's not easy to work out exactly what needs to be done. I needed a step-by-step guide to setting up a basic mirrored database, and couldn't find one, so here's my attempt at providing this to help other people in the future.

I'm going to use the ASP Session database as the example, since this is a common requirement, but this guide is good for any database you may have. I've used the same steps for other company databases. This is also good for SQL Server 2005, although I performed these steps on a SQL Server 2008 Standard Edition.

As soon as a mirrored database server is introduced, the ASP State can no longer use the State Server or the In-Memory Model, but must be configured to use the ASPState SQL Server state database. Since this database is mirrored, the user can move from one web server to another during a session, and the session state will be maintained between the servers.

Step 1- Installing the ASP Session Database

This step is omitted for a normal database.

First things first, we need to install the ASPState database on the Principal server.

Navigate to the C:\Windows\Microsoft.NET\Framework\v2.0.50727 folder and type the following command:

Collapse

aspnet_regsql.exe -ssadd -sstype:p -S [myserver] -U [mylogin] -P [mypassword]

The parameters are case sensitive. This will create the ASPState database on the specified server. You could use the parameter -E-U and -P parameters to use the current credentials. instead of the

If the -sstype:p parameter is not specified, then by default, the ASP.NET sessions will be put into the TempDB database and not the ASPState database. This confused me for a while. This is fine for a normal non-mirrored environment because the ASP sessions will be cleared if the server is restarted. But, this is not fine for the mirrored environment, because we want to mirror the ASP session data itself, not just the Stored Procedures! Also, mirroring is not possible on the TempDB database. The -sstype:p parameter makes ASP.NET install the session tables into the ASPState database, and they'll be persisted if the server is restarted. This is exactly the behaviour we want for a mirrored environment.

Step 2 - Installing the Database on the Mirrored Server

Start at this step for a normal database.

In order to get the database onto the mirrored server, we do a full backup of the ASPState (or the database you are mirroring) on the Principal server, followed by a backup of the Transaction Log.

• Perform a full backup of the database on the Principal server.
• Perform a Transaction Log backup on the Principal server.
• Copy the backup file to the Mirror.
• Important: Do a restore of the full backup into a new step, but before doing the restore, go to Options, then ensure you check the No Recovery option! This is vital!
• Perform another restore of the Transaction Log, also with the No Recovery option. (This is important, otherwise you'll get an error when starting the mirror - See Gotchas section for explanation).

You'll notice that the database on the Mirror server now is marked as "Restoring..." and can't be accessed. This is normal and expected! This confused me for quite some time, thinking that it was incorrect.

The Mirror is always in a permanent Restoring state to prevent users accessing the database, but will be receiving synchronization data. If the database fails over to the Mirror, then it will become an active database and the old Principal will go into the Recovering state.

Step 3 - Setting the SQL Server Service Impersonation

By default, and in most installations, the SQL Server Service in the Services applet runs as the Local System account. However, for mirroring to work, this needs to be changed to a local user. The Local System account does not have access to the network resources, so is unable to communicate with the mirrored server through the endpoint. It's vital that this step is completed, since I spent many an hour wondering why the mirroring wasn't working.

• Create a local user on both the Principal and the Mirror server with the same username and password. For example, "sqluser".
• Edit the SQL Server Service and change the Logon to this user.
• Do the same for the SQL Server Agent service.
• Change the SQL Server Agent service to be Automatic.
• Re-start the SQL Server Service and then the SQL Agent service.
• Do this on both the Principal and the Mirror!

It's important that the SQL Agent is also running. Because:

1. it runs automated backup jobs and
2. it expires the sessions in ASP

If you find that ASP.NET sessions are not being expired in the ASPState database, then it's because the SQL Agent service is not running.

Sometimes, you may find that the SQL Agent does not start. This can be resolved by re-starting the SQL Server Service and then the SQL Agent again.

Create a SQL Login on both SQL Servers for this user you created.

Step 4 - Setting Up the Mirror

Now, it's time to actually setup the mirror! Go to the Database Properties on the ASPState database (or your database), and choose the Mirroring tab.

If the Mirror tab does not appear in SQL Server 2008, then re-run the setup and ensure you've ticked the Complete SQL Tools options.

• Click "Configure Security"
• Click Next on the wizard
• Choose whether you want a Witness server or not, (this article does not cover Witness servers) and click Next
• In the Principal Server Instance stage, leave everything as its default (you can't change anything anyway)

In the Mirror Server Instance stage, choose your Mirror server from the dropdown and click Connect to provide the credentials. Click Next.

• In the next dialog about Service Accounts, leave these blank (you only need to fill them in if the servers are in a domain or in trusted domains)
• Click Next and Finish
• Click "Do not start mirroring"
• Enter in the FQDN of the servers if you want, but this is not necessary (as long as it will resolve)
• Click Start Mirroring (if you do not have a FQDN entered, then a warning will appear, but you can ignore it)
• The mirror should then start, and within moments, the Status should be "synchronized: the databases are fully synchronized"

So, you should now have a working mirror! Perform a manual failover to test it. Follow the instructions below in "Doing a manual failover".

Here's what a working mirror setup looks like on the Principal:

And, here's what it looks like on the Mirror:

Doing a Manual Failover

If you need to take a box down for maintenance, then you can perform a manual failover. There are two methods - using the SQL Enterprise Manager, and using T-SQL. I'll explain both here, but you can choose depending on your situation.

Using the SQL Enterprise Manager

On the Principal server, right-click on the database and choose Mirror. Then, you'll see a Failover button. Click this, and you'll get a message about the failover swapping the roles. Click Yes, and within about 10 seconds, the roles will be swapped. If you do a refresh of the databases on both servers, you'll see the Principal is now marked as Restoring... and the old mirror has become the new Principal.

Using T-SQL

Here is the T-SQL to do the same as above. You can ignore the SET SAFETY lines if your mirror is using Synchronous mode. You can check the mode being used in the Mirror Properties (right-click Database, Mirror).

Collapse code snippet

--Run on principal
USE master
GO
 
ALTER DATABASE dbName SET SAFETY FULL
GO
 ALTER DATABASE dbName SET PARTNER FAILOVER
GO
--Run on new principal
 USE master
GO

ALTER DATABASE dbName SET SAFETY OFF
 GO

Doing a Forced Failover

If your Principal goes Boom! and you have an unexpected outage, then you'll need to do a forced failover. This means the Mirror server is forced to become the Principal. There is a slight risk of data loss when this takes place, but if the Principal server is down, then what choice have you got? Obviously, you only want to do this forced failover when the principal is unexpectedly unavailable. In all other situations, you should do a normal manual failover.

Doing a Forced Failover using T-SQL

Collapse

--Run on mirror if principal isn't available
 USE
master
GO

ALTER DATABASE dbName SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
GO

For example, in a production environment, you'll have a monitoring system which checks for the availability of the Principal at regular intervals. If it detects that the database is unavailable, then it could run a VBScript or a USQL command line to execute this T-SQL on the mirror database. If you have a witness server, then this will be done automatically.

Step 5 - Configuring the ASP.NET Application to Use the Mirrored Database

The final step is to configure your ASP.NET application to use the mirrored ASPState SQL Server database (or your database). If you are using .NET 2.0 or higher, use the following steps:

• In the web.config file, change any existing <sessionState key to the following (or if it doesn't exist, add it):
  
  
  Collapse

  <sessionState mode="SQLServer" allowCustomSqlDatabase="true"
      sqlConnectionString="data source=[PRINCIPALSERVER];
     failover partner=[MIRRORSERVER];initial catalog=ASPState;user id=[DBUSER];
     password=[DBPWD];network=dbmssocn;" cookieless="false" timeout="180" />

• data source = [PRINCIPALSERVER] - For data source, add the IP address or name of the Principal server. This is a required parameter.
• failover partner = [MIRRORPARTNER] - Specify the name of the Mirror server. This is a required parameter.
• initial catalog = ASPState - or specify the name of your database if doing a custom database. This is a required parameter.
• allowCustomSqlDatabase = "true" - This is a required parameter.

By configuring your application to use these settings, when the application makes a request to the database and the Principal server is not available, .NET will automatically send the request to the mirrored server. This should happen transparently so your website user should not notice any outage.

If you're using .NET 1.1, this Failover Partner is not present, so you'll need to roll your own code to transfer the client. This could be a simple try...catch around the database connection, and in the catch, it retries using the mirrored server.

In a unexpected outage scenario, the following happens:

• The Principal server goes down.
• The Witness server or your monitoring service detects the Principal database in unavailable.
• The Witness server or your monitoring service forces the failover to your mirrored server, which becomes the new Principal.
• ADO.NET detects that the principal server is down, and retries automatically with the mirrored server.

Important: If you have two or more web servers, you'll need to copy the above SessionState entry on to each of the web servers.

You'll also need to ensure you have the same validation key / decryption key on each of the web servers. If not, then the session data will not be able to be read if created on a different server. This is necessary if mirroring the ASPState database.

Collapse

<machineKey
 validationKey="D581FCFD1xxxxxxxxxxxxxx16FEEBB4C56Axxxxxxxxxxxxxxxxxxxxxxxxxx"
  decryptionKey="55B44F83Cxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
 validation="SHA1" decryption="AES"
  />

Gotchas

You may get the following error messages and gotchas when playing with your mirror:

When attempting to do a FORCED FAILOVER with the SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS TSQL, you get the following error:

Collapse

"Cannot alter database <db>because the database is not in the correct state 
to become the principal" 

You'll be able to perform a manual failover fine, but forced failovers will cause the error. The mirror will NOT become the principal if it detects that the principal is already running. This may happen if you are testing a forced failover with code or a script and don't stop the SQL instance on the principal. If you stop the service on the principal, you can then perform a forced failover.

When setting up the mirror, you get the following error message when attempting to start the mirror:

Collapse

"Remote copy of <db>has not been rolled forward to a point in time 
that is encompassed in the local copy of the database. Error: 1402" 

This will occur if you have not done a transaction log backup on the Principal and restored it (no recovery) on the Mirror. You MUST do this step, or this error will occur.

Mirroring a 2^nd Database

If you want to mirror more than one database on the same servers, this is possible. You need to repeat Step 2 for the next database, and then Step 4 (just click Next, Next, Finish, etc). The databases will all use the same endpoint port.

Stopping the Mirror

If you want to stop and remove the mirror for a database, follow these steps:

On the Principal

1. Right-click, Mirror > Remove Mirroring
2. Refresh the database view (Principal should now show as normal database).
   The db is now a normal database.
3. Delete the database

On the Mirror Itself

1. Will currently be saying "Restoring..."
2. Right, click and Delete the database or Take Offline and then delete.
   If the Delete is not showing, or you don't have control, Refresh the database view or reopen SQL Enterprise Mangler.
   Or to turn back to a normal database without deleting: Do another Restore Database but change the Options to be With Recovery (top option)

Conclusion

If you've been following the steps in this article, you should have now successfully setup the ASPState database as a mirrored database (or your own database), providing your web users with a decent amount of availability and protection against outage.

I hope the article has been of interest and of some help to someone. Any questions, drop a comment, and I'll try to answer it, or update the article if areas are not clear. Thanks for reading!

Notes

You can have more than one database mirrored on the same set of servers. But, of course, the more you have, the worse the performance.