Sunday, October 12, 2008

Database Mirroring with SQL Server 2005

    Database mirroring is a software solution, provided by SQL Server 2005, that gives you the ability to replicate your data in real-time.  Mirroring allows for increased database availability and data protection and works at the database level on databases that are set to full recovery mode.  You need two instances ofSQL Server to set up mirroring because it maintains separate copies of data of a single database across both servers.

    Let's look at a common scenario for database mirroring. Take a company that has their customer database in a data center in New York City.  They also have a backup data center in Houston. The database in NY is mirroring the data to the database in Houston.  Let's say the data center in NY loses power and they need to shut the server down.  With a "flip of a switch" they can move the database to Houston and continue as if nothing happened.  When the data center gets power again they can switch to the NY city database as soon as it's resynchronized.

Benefits

    One benefit of database mirroring is the increased data protection of your data.  As each transaction is being written to your principle database it is sending that exact transaction to the mirror database.  This provides complete or almost complete (depending on which mode you set up; I'll get into this later) data redundancy in real time.

    High-availability, this is a huge benefit for mission critical data.  Hardware failures happen and network issues arise, with a "flip of a switch' you can send the responsibility to the mirrored server to become the principled server at any time.  You can also set up automatic failover with the addition of a witness server to monitor the servers.

    Another benefit is that you have the flexibility to install updates on your server without data disruption.  Because you can manually failover to the mirror server at any time you can failover, install the updates and reboot if necessary. When you are done you can then have the new principle server failover again to restore the principle on to the primary server without any data loss or downtime and upgrade the mirror server.

    Finally, you can set up mirroring to sync in synchronous or asynchronous modes.  Synchronous or high-safety mode will write a transaction to the principle database and send that exact transaction to the mirror and then wait for it to finish committing that transaction before moving on. With high-safety you are assured that the data that is on the principle is on the mirror at the exact same time. In that you trade some latency waiting for the transaction for the true redundancy of the data.  Automatic and manualfailovers, explained more below, require the use of high-safety mode.

    Asynchronous or high-performance mode will send the stream of transactions at the mirror server and the mirror will try to keep up.  With high-performance there can be a gap where the mirror has a back log of transactions to complete that the principle already has completed.  This gap can become pretty big with heavy loads on the principle. 

Requirements

    Database mirroring requires two servers. With SQL Server 2005 you can set up mirroring with Standard or Enterprise editions.  It's very important that both servers are running the same edition.  It is also recommended that the hardware for both servers be near identical and have the ability to handle the same load.

Setting up a Database Mirror

    Let's start with some simple preparation:

  1. Make sure that you have the proper logins created on the mirror server.
  2. Create the mirror database by restoring with NORECOVERY a recent full backup of the principle database. The principle database must have been set-up with full recovery when the backup was created.
  3. You must also restore all log backups since the full backup was made. Create a backup of the transaction logs and restore on the mirror server.  You'll want to start the mirror session as soon as you can after taking the log backup.

    Configuring Database mirroring:

  1. Connect to the principle database. Select the database you wish to mirror.
  2. Right-click > Tasks > Mirror; this will bring you to the Mirror tab on the database properties pop-up.
  3. Select the Configure Security button to configure the mirror session through the Configure Database Mirroring Security Wizard (only in this wizard you can add or change the witness server instance).
  4. Upon completion of the security wizard and you are still connected to the principle server click on Start Mirroring.

Client Connections (Connection Strings)

    I am going to assume that you as the reader already know how to connect to a database in your application code in order to access it.  What I'll cover here are the required key strings needed in the connection string to handle connecting to both database servers in a mirror session depending on which one is acting as the principle at the moment.

    Just as you would with a normal connection string you need to provide a initial partner name.  This will be the principle server.  If you are usingTCP/IP then enter in the IP, if you are using named pipes then enter in the name of your server. Additionally, if your SQL Server is using a Instance Name then you will need to add that as well (example: 10.10.2.1\InstanceName.  Now we need to provide the failover partner.  This will be the mirror server.  Add this information similarly as you would for the initial partner name.

    The other item you will need to provide in your connection string is the network attribute.  This will specify the network protocol to be used and ensures that the proper network protocol persists between connections to different partners. ForTCP/IP use the following:

Network=dbmssocn;

    Named Pipes use:

Network=dbnmpntw;

    Putting it all together in a sample connection string, you can get more info on connection string keywords here:
Data Source = 10.10.2.10/InstanceName; Failover partner = 10.10.2.11\InstanceName; Initial Catalog = databaseName; User ID = sa; Password = 12345; Network = dbmssocn;

Failover

    There are three types of failover: automatic, manual and forced.  Automatic requires a witness server and the mirror set-up in high-safety mode.  Manual failover does not use a witness server and also requires the mirror set-up to be in high-safety mode. Forced is usually used because the principle server has been disconnected somehow.  Using forced service may result in data lose as all transactions may not have made it to the mirrored server.  Forced service is supported on high-availability and high-safety mirrored set-up modes.

Automatic Failover

    For automatic failover to occur the following conditions are required:

  • Mirroring sessions must be running high-safety mode and posses a witness server.
  • The mirror database must be synchronised.
  • The principle server has lost communication with the witness and mirror servers but the witness and mirror server are still online.  Note: If all servers lose connectivity but then the witness and mirror server comes online automatic failover does not occur.
  • The mirror server detects the loss of the principle server.

    How it works

  • If the principle server is still online and changes it's state to DISCONNECTED and disconnects all clients.
  • The mirror and witness server both register that the principle server is unavailable.
  • The mirror server waits for all logs to be written from the redo queue before rolling forward the mirror database.
  • The former mirror server now moves online to be the principle. Recovery will roll back any uncommitted transactions, locks isolate those transactions.
  • When the former principle server comes back online and sees that the mirror has moved to principle the former principle server will become the new mirror and start synchronising. Once synchronising is complete failover is possible again.

Manual Failover

    With manual failover it's possible to failover to the mirror server so that updates and upgrades can be performed on the principle server. Manual failover requires the mirror set-up to be in high-safety mode (transaction safety set to FULL). The partners need to be connected and synchronised.

    How it works:

  • Principle server disconnects, sends the last log message to the mirror and switches to be the mirror server.
  • The mirror server records the last log message as the failover log.
  • The mirror server waits for all logs to be written from the redo queue before rolling forward the mirror database.
  • The mirror server becomes the principle server and the principle becomes the new mirror.
  • The new mirror server quickly resynchronizes with the new principle server. Once complete failover is possible again.

    Initiate Manual failover:

  1. Connect to the principle server. Choose your database.
  2. Right-click > Tasks > Mirror; this will bring you to the Mirror tab on the database properties pop-up.
  3. Click Failover.  Confirm that you want to failover to the mirror server.

Note: The client will need to see that the connection to the primary has failed before it will try to connect to the failover partner.

Forced Service

    If the Principle server goes down due to hardware issues or is unreachable you can use forced service to bring the mirror to principle state.  Doing this may cause data loss as the mirror may not have received all of the transaction logs from the principle. Read this Microsoft TechNet article for more information on forced service.

    Initiate Forced Service

  1. Connect to the mirror server.
  2. Run the following T-SQL:
    ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

    The mirror will take over as principle and mirroring will be suspended.

Summary and Online Resources

    Database mirroring has many options and ways to be effective in maintaining a high-availability database.  This article is my collected research as I was learning how to set up mirroring. If you would like more information on setting up database mirroring please look at the following resources:


# Sunday, October 12 2008 at 10:15 PM | Comments [1]
Tags:  |  | 

Wednesday, October 08, 2008

Gaining Exclusive Access to database in SQL Server 2005 via T-SQL

Usually, whenever I restore a backup of my database in SQL Server I am presented with the following error:

Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Usually, to get around this I just restart the server. This was fine when we were developing on our local server instance on our development machines. But we have a few programmers that need to make changes to the database and the logistics of having everyone script their changes and drop them into Subversion was becoming a nightmare. Regardless our simple solution was to put it on a shared server in the office and backup the server occasionally, in case someone screwed up the data.

Well I screwed up some data and needed to restore. Unfortunately, I have another co-worker in the office who is working on another project and is using the same database server (different database) for development. To be nice I'd want to restore without restarting the SQL Server and possibly disrupting his work.

What I need to do is find all the connection processes to the database and kill them:

Use Master
Go

Declare @dbname sysname

Set @dbname = 'databaseName'

Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
        Execute ('Kill ' + @spid)
        Select @spid = min(spid) from master.dbo.sysprocesses
        where dbid = db_id(@dbname) and spid > @spid
End

Now I can immediately restore:

USE Master
GO
RESTORE DATABASE [databaseName]
FROM  DISK = N'physical disk path to the backup file.bak' --example path: c:\program files\microsoft sql server\mssql\backup\databaseName.bak
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

This method works best in a development environment with minimum developer connections. In production/staging environments refer to your database administrator for best practices.


Related Links

How to Gain Exclusive Access to SQL Server 2005 DB to restore?

# Wednesday, October 8 2008 at 1:01 PM | Comments [1]
Tags:  |  | 

Blog Posts by:

The Official jQuery Podcast

with Ralph Whitbeck & Rey Bango

You can subscribe to the show in iTunes or via the raw RSS feed

My Twitter Updates

View Twitter Page