SQL Server 2008 Database Mirroring in Action

I have been pretty busy with a server consolidation and move project at work. We had four Dell PowerEdge 6800 and four Dell PowerEdge 1950 servers that were running a number of busy, large SQL Server 2008 databases. We had previously done some vertical partitioning (splitting different application functionality across different databases and servers) and horizontal partitioning (with data dependant routing in the middle tier) to allow us to scale out in the data tier, using commodity hardware.

All of these databases were mirrored, with the Principal running on our 3PAR S400 SAN, and the Mirrors running on an EMC CX-500 and some Dell PowerVault MD1120 SAS arrays. As the 3PAR went over 3 years in service, and the PowerEdge 6800s started going out of warranty, we made a combination business/technical decision to consolidate and simplify this environment.

We are using a new Dell PowerEdge R710 and the best of the existing Dell PowerEdge 1950 servers to replace the eight old servers. We bought several additional PowerVault MD1120 arrays to replace the 3PAR.  We are replacing about $650K worth of hardware with approximately $50K worth of hardware. Part of this move involves moving from Windows Server 2008 to Windows Server 2008 R2. We are also moving to a different area in our hosting facility while we are at it!

Of course, with our SLAs with external customers, we cannot just take an extended service outage. I have been able to use SQL Server 2008 database mirroring to do “rolling upgrades and moves”, with a series of 15-30 second outages during planned mirror failovers. How have we done this?

First, you need at least one new or previously unused database server. You build that server out with Windows Server 2008 R2 and SQL Server 2008, then mirror one or more databases to that instance. Using direct attached storage (DAS) instead of a SAN makes this a little more challenging, since you don’t have the flexibility of a SAN. This forces you to be more careful in calculating your I/O requirements, and deciding where to locate your data and log files in order to not exceed your I/O capacity on any drive array.

One new feature in SQL Server 2008 Enterprise Edition that has been extremely helpful during this is compression (data compression, backup compression, and log stream compression for mirroring). Once the mirror is up, it is just a matter of modifying some connection strings in the middle tier, and then failing over databases to move data from server to server and from place to place. Of course, doing all this smoothly takes thorough planning to make sure you don’t miss anything (such as a login or a SQL Agent job), So far, all of this has gone very well. There is no other way that I can think of that I could have done all of this with my available resources without SQL Server 2008 and database mirroring.

Technorati Tags:

This entry was posted in SQL Server 2008. Bookmark the permalink.

33 Responses to SQL Server 2008 Database Mirroring in Action

  1. Unknown says:

    http://www.salesid.com and http://www.saletoo.com Are Top Wholesale: Belts Caps Clothing Glasses Handbags Shoes Wallets Watches.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s