The move to SQL 2012

Nick Craver

Sorry this blog has been a bit quiet lately, we’ve been very busy making some big changes behind the scenes.  So what are we up to?  Let’s start with just the SQL infrastructure moves, here’s a list of servers in play as they started out:

  • NY-DB01 – SQL2008 R2 Hosts all sites except Stack Overflow and the Sites DB
  • NY-DB02 – SQL2008 R2 Daily backups restored from NY-DB01, and the Dev DBs
  • NY-DB03 – SQL2008 R2 StackOverflow’s DB
  • NY-DB04 – SQL 2008 R2 StackOverflow 5 minute behind hot spare in restore mode
  • OR-DB01 – SQL2008 R2 Chat’s DBs
  • OR-DB02 – SQL2008 R2 SEDE, Internal SEDE and Chat Dev DBs
  • New Dell R710 w/ 2x OS Drives + 6x Data 300GB Intel 320 SSDs in RAID10 and 96GB RAM
  • 3xNew Dell R720 w/ 2x OS Drives +  12x Data 200GB Intel 710 SSDs in RAID10 and 384GB RAM

First we set up the first SQL2012 Cluster with those new R720 machines. The new R720s are identical; they became NY-SQL01, NY-SQL02 and OR-SQL01:

  • NY-SQL01
    • Primary: Sites DB, Stack Overflow
    • Backups: Sites DB & StackOverflow Full and Transaction Logs -> NY
  • NY-SQL02
    • Replica: Sites DB, Stack Overflow, Chat DBs
    • Backups: Chat DBs Full -> NY
  • OR-SQL01
    • Primary: Chat DBs
    • Replica: Sites DB, Stack Overflow
    • Backups: Sites SB, StackOverflow & Chat DBs Full -> OR

For this we have 2 new availability groups, StackOverflow_AG and Chat_AG. The primary server for StackOverflow_AG is NY-SQL01 replicated to a secondary in the same data center (NY-SQL02) and across the country to Oregon (OR-SQL01).  The Chat_AG is only on 2 servers: the OR-SQL01 primary (chat is hosted in Oregon) and the replica NY-SQL02. The reason chat is only on 2 servers is because SQL2012 availability groups do not have the ability to distinguish between sites and replicate that way…so it would send the same transaction stream across the country twice to replicate to the NY servers, rather than echoing the transactions through one to the other…this is an unnecessary use of bandwidth we feel.

The StackOverflow and Sites DB portion of the first cluster was completed on the 2012-08-11 maintenance window; chat will be completed on 2012-08-18 (part of chat has moved, we want to give it a week to observe any problems).  Now what happens in the following week? We need to shuffle some hardware around.

With the StackOverflow DB moved off of the NY-DB03 and NY-DB04 pair, they’re ready to be re-tasked.  Currently these servers are identical Dell R710s with 288GB of RAM, 2x OS Drives in a RAID 1 and 6x 200GB Intel 710 SSDs in RAID10.  These boxes get re-tasked to be NY-SQL03 and NY-SQL04. Joining them in this second SQl2012 cluster is OR-SQL02, that new Dell R710 above.  Here’s a breakdown:

  • NY-SQL03
    • Primary: All Stack Exchange 2.0 Sites other than SO
    • Backups: SE 2.0s Full + [diff of trans] -> NY
  • NY-SQL04
    • Primary: SE 2.0 & SO Dev DBs
    • Replica: All Stack Exchange 2.0 Sites other than SO
  • OR-SQL02
    • Primary: Chat Dev DBs
    • Replica: All Stack Exchange 2.0 Sites other than SO
    • SE 2.0s Full + [diff of trans] -> OR

Now we’ve freed up the NY-DB01 and NY-DB02 boxes, they’ll also be nuked, get some new drives and be re-tasked for some other purposes (for example, one goes to Oregon to be the HAProxy traffic log out there).

For the miscellaneous bits, OR-DB01 will be freed up after the move to OR-SQL01 and OR-SQL02 of the chat DBs.   We’ll then take OR-DB01 and install 2012 re-tasking it to host the data.stackexchange.com databases.  It has double the memory of the current server and should provide a nice boost to performance there.

 

Why? What does all this moving get us?  Well it turns out SQL 2012 Always on Availability Groups give us quite a bit.  Here are the big ones for our architecture:

  • Near real-time replicas of every production database, ready to go
  • No more copying backups across to the offsite datacenter for redundancy
  • We can read from the replicas, eliminating the need for an entire server and allows us to spread the read load out (e.g. API can point at a replica)
  • A backup DR location is now doable

First, we can have very near real-time hot spares for all production databases (previously, we had up to 8 hours data loss between differentials).  Second, we don’t need to do these wasteful copies of databases across the country purely for backup purposes…we have a nearly-in-sync replica across the country we can do speedy local backups from.  That’s a huge cross-country VPN bandwidth savings as an added bonus.  Third, we can spread the read load out across multiple servers (and we can add another 2 more to either of these availability groups if needed).  Performance-wise, we don’t even have a need for any read load spreading, but it’s very nice to have as an option. Now for the last one: a DR site.

PEAK Internet in Oregon is where Stack Overflow began on a single server, and we’ve been very happy with the service provided ever since.  Chat’s been all alone out there for over a year now, it deserves some company.  In another blog post coming up I’ll detail how we’re setting up as a read-only disaster recovery location out there, as well as our intention to actively use that while we move datacenters in New York.

P.S. Make sure to RAID your PCIe SSD drives, we’ll put up a post with that story a bit later…

I’ve recently been looking back on what we have written about our architecture in the past, and came to a stunning realization. That realization is that while we have many many different articles about what we have been doing there hasn’t been a good, solid overview of our architecture in a long time. In fact, the last really comprehensive write-up was done by Jeff before this blog even existed. And, boy I do have to say there has been quite a lot of change behind the scenes since then. So, my dear readers I’m going to take some time – and my next few blog posts – to give everyone an in depth look into how we have the Stack Exchange Network setup to serve between 12 and 14 Million page views per day.

How these posts will breakdown

Since we have obviously grown, and are offering more services to our users I’m going to break these posts out by each of the 4 major services we offer to our user base:

  • Core Q&A (this includes the API)
  • Careers
  • Chat
  • Community Blogs

Each one of these systems all work towards our goal of making the internet better, but they have different requirements and different challenges.

In this first post, I’ll be focusing on our core Q&A system, since that is after all our bread and butter.

Core Q&A

First, a high level overview of how everything is put together:

The Hardware

Our core hardware setup hasn’t changed all that much. Well, I should say the chassis haven’t changed that much. We’ve done a lot of work to upgrade the internals of the servers when needed to address performance issues as they came up, as well as handle issues that resulted from Stack Overflow being so big.

Web Tier

Of these 10 Servers, 3 are dedicated to Stack Overflow with an additional 3 servers serving Stack Overflow and the Stack Exchange Network. We have one server dedicated to Dev/QA – which also hosts meta.stackoverflow.com. Our Web Tier machines normally operate between 5 and 20% utilization. We have plenty of room to grow on these boxes.

  • 10 Dell R610 IIS web servers:
    • 2x Intel Xeon Processor E5640 @ 2.66 GHz Quad Core with 8 threads
    • 16 GB RAM
    • Windows Server 2008 R2
    • 2 drives
      • RAID 1
      • 2x Intel 320 300GB SSD (RAID 1)

DB Tier

We have two database server pairs. One pair is dedicated to running Stack Overflow, and the other runs the rest of the network. We run development against the secondary server of the non-stack overflow database pair. Both of our database pairs run at about 20% utilization, so once again we have room to grow here as well.

  • 2 Dell R710 database servers:
    • 2x Intel Xeon Processor X5680 @ 3.33 GHz
    • 96 GB RAM
    • 8 spindles
      • Mirrored Pair for OS
      • 6 disk RAID10 for databases
    • SQL Server 2008 R2 SP1
  • 2 Dell R710 database servers (Stack Overflow Dedicated):
    • 2x Intel Xeon Processor X5680 @ 3.33 GHz
    • 96 GB RAM
    • 8 drives
      • Mirrored Pair for OS
      • 6 drive RAID10 of Intel X25-E SSDs for Database
    • SQL Server 2008 R2 SP1

Caching Tier

We run redundant Redis servers for our caching tier.

  • 2 Dell R610 Redis servers:
    • 2x Intel Xeon Processor E5640 @ 2.66 GHz
    • 16 GB RAM
    • CentOS

Network Layer

We use HAProxy for our load balancing, and Cisco Switching.

  • 2 Dell R610 HAProxy servers:
    • 1x Intel Xeon Processor E5640 @ 2.66 GHz
    • 4 GB RAM
    • Ubuntu Server
  • 6 WS-C2960S-48TS-L Gigabit switches
    • FlexStack (two stacks, 4 switches and 2 switches)

Data Integrity

As with any system, making sure that your data is backed up and the backups are good is an integral part to your service offering. We backup our databases nightly and restore them to two different locations. One local to our NY data center for our devs to work against, and one remote in our OR data center.

Conclusion

Overall I believe that we are in a good place and have plenty of room to grow given our current setup. As always we will constantly be looking at our infrastructure and tweaking it to get the best performance possible and give our users the best experience possible.

With all my talk of doing things in a scalable way comes a requirement, and that is that we actually accomplish this stuff in practice at Stack Exchange. We have been making a lot of progress in this area. George has been refining and expanding our deployment process. He improved our Windows deployment to include most of the software we use and has made it so kickstart for CentOS/Linux installs are integrated into our deployment as well.

Scaling your ability to manage your environment in my mind means doing more with less. I find I really only have to ask myself one question to quickly gauge if we are doing it right or not.

Do I have to repeat steps to do this task on multiple servers?

I like this question because it is more specific than “Is it automated?” while still implying automation. It is akin to the DRY rule in programming: “Don’t repeat yourself.”

When it comes to our environment, here is where we are at. For this I will ignore some details — for instance, we log into servers to kick off a PXE install and then just let it go — I consider that a Yes to “No repetition required” since it is only one or two steps and we don’t really deploy more than a server at a time. We don’t want to automate to the point over engineering beyond the projected size of our environment.

Task No repetition required Solution or Proposed Solution
Windows OS Deployment Yes Microsoft Deployment Toolkit with LiteTouch
Linux OS Deployment Yes WDS which redirects to PXELinux and Kickstart
Windows OS Updates Yes Windows Update Services
Linux OS Updates No Kick them off with Cron or Puppet? Spacewalk?
Windows Firmware Updates No Exploring what Dell has to offer that might tie into System Center
Linux Firmware Updates No Run binaries with Puppet? (kind of scary)
Windows Software Deployment and Updates No1 Microsoft System Center
Linux Software Deployment Updates Yes Puppet†
Windows Configuration Management No (with the exception of IIS and web related configs) Microsoft System Center
Linux Configuration Management Yes Puppet†
Automated Deployment of Monitoring and Backup Configuration No No ideas at this point

1We do have some software that can be deployed via GPO, and LiteTouch deploys a lot of stack on the web servers during deployment. But future software updates and software that doesn’t lend itself to GPO is manual.

† I am currently in the middle of rolling out puppet so it is partially deployed on some of our Linux servers

The big picture of all of this is deployment as phase 1 and maintenance as phase 2 for both Windows and Linux. Also, ideally these phases connect to each other seamlessly.

One of our main goals is to change all of the above “No” to “Yes” over the next few months and then refine each step. For the most point we have deployment taken care of for both Windows and Linux. As far as maintenance goes, I believe as I progress in rolling out Puppet that will solve the vast majority of our Linux needs. How we will manage our firmware is still an unknown. As far as Window maintenance goes we are going to start exploring System Center soon and hope that it can meet our needs.

What I really think all of this will buy us is consistency, recoverability, and most importantly — time. By having all of this centrally managed it will make our servers more consitent with each other — and make them effectively drones. By having these processes automated we will be able to recover fast and replace servers easily. Lastly, and most importantly it buys time. By making our management faster and more agile, George and I can focus on rapidly deploying improvements to our environment instead of just maintaining it. By having less friction to deploying changes to our infrastructure I believe more possibilities for improvement will start emerge.