Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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:

Comments

Posted by TheSQLGuru on 17 November 2009

Pretty amazing that you can replace $650K of hardware with $50K of hardware!  Hope your performance is acceptable!  Guess I need to check out those MD1120 drive arrays.

So you established synchronous mirroring with the new database server and let things stabilize, then failed over to the new box, right?

Posted by Hugo Shebbeare on 17 November 2009

Awesome one again Glenn! It's nice to know that on 64-bit there is no documented limit to the number of DBs you want to mirror also: before 32-bit had a 10 DB limit.

Posted by woods on 17 November 2009

Sounds like you guys saved a lot of money...maybe too much. You are most likely going to find scalability very challenging as your performance requirements increase. Spending 150K to 200K might have been the better long term solution. A SAN solution gives you a lot more flexibility around growth. Your comment “(DAS) instead of a SAN makes this a little more challenging” should be changed to “a lot more challenging”.

You guys did a great job if your budget was 50K.

Posted by Glenn Berry on 17 November 2009

Hi Terry,

I actually like our scaling story now. If we exceed the I/O capacity that we have available, we can either add more MD1120 SAS arrays to our existing servers, or we can add another database server with its own MD1120s for a pretty low incremental cost. A Dell R710, fully loaded is about $7-8K, while a MD1120 with 24 146GB drives is about the same.

Posted by terry.wooods on 17 November 2009

Not sure I agree on the scale capabilities… but if your application can scale out at the DB tier you are in OK shape. What is your HA solution for the DB servers?

Leave a Comment

Please register or log in to leave a comment.