In this article, I'm going to cover:
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.
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:
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 instead of the -U and -P parameters to use the current credentials.
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.
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.
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.
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.
It's important that the SQL Agent is also running. Because:
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.
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.
In the Mirror Server Instance stage, choose your Mirror server from the dropdown and click Connect to provide the credentials. Click Next.
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:
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.
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.
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).
--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
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.
--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.
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:
<sessionState key to the following (or if it doesn't exist, add it):<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:
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.
<machineKey
validationKey="D581FCFD1xxxxxxxxxxxxxx16FEEBB4C56Axxxxxxxxxxxxxxxxxxxxxxxxxx"
decryptionKey="55B44F83Cxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
validation="SHA1" decryption="AES"
/>
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:
"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:
"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.
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.
If you want to stop and remove the mirror for a database, follow these steps:
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!
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.
| You must Sign In to use this message board. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
