It is pretty hard to get a bunch of system administrators together for any period of time and not have a conversation about command line for managing things. In my experience, command line always wins the debate for any medium to large installation. Microsoft caught on to this and has introduced powershell. From my own experience and people I have talked to, powershell adoption has been slow. Like powershell or dislike it, it is an interesting take on command line with its object oriented versus text-based approach. It is also quite powerful, so why the slow adoption?
In my mind it isn’t a problem with powershell itself, but rather a result of human nature, culture, and the Windows ecosystem. The problem lies in workflow, and I think the best way to illustrate this is to contrast Windows versus Linux administration. When managing Linux systems, scripting something flows naturally from the experience of trying things out in the first place. For example, lets say someone with some Linux experience (but not an expert) is building a piece of software and installing it on Linux for the very first time. The steps usually involve something like:
- Run the traditional, configure, make, make install from the command line. See what libraries or binaries are missing that are required to build, from the errors. Install them (often via yum or apt), keep doing this until it works.
- Possibly add some users to the system, adjust permissions etc with commands like useradd, chown, chmod.
- Edit some text files
- Set it to start at boot with commands like chkconfig or update-rc.d
As you do this more and more, you learn the patterns of what fails, and get the urge to automate it. Although things like puppet are the end goal these days, the next normal step is to write shell scripts to do this. This is the lynchpin of the whole experience, a shell script is just a little flow control, error handling, and a few variables on top of what you were already doing. With the Linux experience, scripting flows naturally as a next step in your experience. It is a gradual learning experience, and your previous experience of doing things manually taught you much of what you need to know to script it — what goes wrong, what to check, how to do everything you need to do. Even the first time tutorials on the web are almost always command line. From the second you start learning how to administrate a Linux box, you are learning how to script.
Unless you are extremely disciplined or have been taught Windows administration recently by a “benevolent” dictator, your first steps in Windows administration are via the GUI and Wizards. Don’t get me wrong, for many things this gets the job done faster and with far more ease. The generally accepted problem with the GUI though is that it doesn’t scale. When you want to scale in management you often turn to scripts (GPOs are very useful, but you are generally out of luck if what you want isn’t predefined.) With Windows, scripting these tasks is generally a complete departure from your previous experience. Scripting doesn’t flow naturally from previous experience, and scripting something is scary because of this. This means the windows ecosystem, due to human nature, is far less likely to produce administrators with scripting experience. Therefore, powershell adoption is logically slower. This also means administrators are less likely to be testing powershell and pressuring each other and Microsoft to improve it.
The cure to this workflow to me seems to be Windows Core since changing human nature isn’t easy. With Windows core, even though you can use the Management Console, you really have to start doing many things via the command line and powershell. With use of these tools, just as with the shell in Linux, scripting will flow naturally. The Catch-22 however though is that since adoption is slow, everything you need might not be available via command line options. At my company we haven’t used Windows core, but I’m hoping with Server 2012 it is time for Core, does anyone with more experience know if it is time for this shift?
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:
- Primary: Sites DB, Stack Overflow
- Backups: Sites DB & StackOverflow Full and Transaction Logs -> NY
- Replica: Sites DB, Stack Overflow, Chat DBs
- Backups: Chat DBs Full -> NY
- 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:
- Primary: All Stack Exchange 2.0 Sites other than SO
- Backups: SE 2.0s Full + [diff of trans] -> NY
- Primary: SE 2.0 & SO Dev DBs
- Replica: All Stack Exchange 2.0 Sites other than SO
- 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…