The Great Breakup

1

September 22, 2009 by Mike Hillwig

In a recent post, I said that database servers  and web servers can be friends, but they shouldn’t live together. Truth be told, I inherited an environment with tons of servers that run both the web and database servers. When you’re a small shop, it’s not such a big deal. But as the company has grown, we’re outgrowing that. We’re starting to look at consolidating our databases and database servers.

In the meantime, we’re dealing with an application that has really outgrown that model. Historically, this particular application has been owned by the business unit. They have people who manage the application and the data. We manage the infrastructure and make sure the database is backed up. I think we’ve outgrown that model, too.

This particular application basically lives on a single server that runs both the web server and the database server.  Lets call that server CS1. We have the application installed on a second server in our secondary data center (CS2) and restore the production database to this server daily. CS1 and CS2 were purchased at the same time and are practically identical.

As the user base for this application has grown and as the company has grown, the server has been getting a bit sluggish. My user community was clamoring for new hardware. I explained to my boss and the VP who owns this particular application that throwing more hardware would only buy us time without actually fixing the problem. CS1 has four drives in a RAID 5 configuration all on a single partition. This is hardly optimal for a database server. Again, this may have been fine when we were a small startup, but not anymore.

We had two problems that I want to fix before buying new hardware. First, I want to break apart the app and db servers. Second, I want to implement some change management processes for the application.

SQL and IIS are going to remain friends, but they’re no longer going to live together. Here is what we’re doing:

  • CS2 was virtualized. That’s keeps our DR strategy in place while we do this migration. This will be on a VMWare ESXi server in our secondary data center.
  • CS2’s hardware is being moved to our primary data center.  It got more memory, an additional drive and is being rebuilt as CSDB. This will be the application’s database server. It’s being built with two mirrored drives (split into two partitions, one for the OS and one for the transaction logs) and drives configured for RAID 5.
  • Two new virtual servers will be deployed in our secondary data center. They will be CSDR and CSDBDR. CSDR will continue to mirror CS1 and CSDBDR will stand by for CSDB. I’m still thinking that log shipping or database mirroring will be our method for keeping the DR database current.
  • Two more new virtual servers will be deployed in an internal lab network. These will be CSDev and CSDBDev. This is where the business will be able to test customizations prior to implementing in production.
  • Before we cut over the database server from CS1 to CSDB, we will be revoking permissions to our business users.
  • Finally, we’ll remove SQL Server from CS1 and retire the CS2 virtual machine.

This is quite a bit of work and it should be well worth the effort. My business users are not very happy about losing their access to production. I’ll spare the jokes about the Crymea River. Their VP agreed to it. In return, we had to agree to an SLA. I’m perfectly okay with this.

One thought on “The Great Breakup

  1. SQLJoe says:

    I share your cry me a river situation. At our organization we have a transactional replicated database which I recently found out the business analysts don’t use. They connect their queries directly to the Production database server. I recently gave them the ultimatum that their access to the Production db server will be taken away and they should now link their queries to the replicated db to offload our Production db. I got a lot of resistance as you might have experienced yourself.

    I have a couple of questions about your recent changes:

    1) Is there any reason why you decided to use VMWare ESXi instead of Microsoft Hyper-v, other than it being free?

    2) What raid level did you use for the database data? RAID 1+0 or RAID 5 as well.

Leave a Reply

Your email address will not be published. Required fields are marked *