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…

«
»
  1. Warren says:

    I’m curious – how long did it take to validate the SE apps against 2012 vs 2008?

  2. Nick Craver says:

    We ran our dev environments and part of chat against it for about a week before moving forward.  It’s really a matter of how deep you’re using the DB, and since most of our interaction is basic CRUD operations.  That being said, we had an outage last night due to a combination of the network itself going crazy and SQL Server replicas shutting down completely…we’re investigating that now.

  3. Warren says:

    Thanks, Nick – I deal with the certification process with some apps, and it’s good to hear how others do it, too.

    When you get the network craziness and replica shutdowns RCA’d, we’d love to hear about those on a blog.SF post, too

  4. Bronson Tubb says:

    Nick, Why did you choose Intel 710 SSDs to replace the 320 series, and did you consider Intel 520 SSDs?  Thanks for the informative and enjoyable read.

  5. Erik Paulsen Skålerud says:

    I really really hope that you will post an update on how this turned out for you. There are many, many SQL installations out there where admins are dying to get away from regular mirroring, log shipping and SAN replication. Documenting your steps – especially caveats.

  6. Kyle Brandt says:

    Our DBA wrote a follow up at: http://www.brentozar.com/archive/2012/09/microsoft-sql-server-alwayson-ags-at-stackoverflow/

Leave a Reply