SQL Server 2008 Database Mirroring in Action

, 2009-11-13

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:





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads