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 |
Copy Codeaspnet_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.
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:
- it runs automated backup jobs and
- 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 |
Copy Code
USE master
GO
ALTER DATABASE dbName SET SAFETY FULL
GO
ALTER DATABASE dbName SET PARTNER FAILOVER
GO
USE master
GO
ALTER DATABASE dbName SET SAFETY OFF
GO
Doing a Forced Fail over
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 |
Copy Code --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 U
SQL 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:
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 |
Copy Code <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 T
SQL, you get the following error:
Collapse |
Copy Code "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 |
Copy Code "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 2nd 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
- Right-click, Mirror > Remove Mirroring
- Refresh the database view (Principal should now show as normal database).
The db is now a normal database.
- Delete the database
On the Mirror Itself
- Will currently be saying "Restoring..."
- 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!
7:46 PM
windowslinuxservers
Choose the 
and configure the security for the snapshot agent. 
Then specify the Merge Agent security as mentioned above on "Agent Snapshot". And specify the synchronization schedule for each agent.
