If you’re a full time production database administrator, you need a staging environment.
This is where you test infrastructure tasks like failing over mirroring, doing a disaster recovery role swap, cluster quorum changes, log shipping configuration, and patching. You need to test this stuff repeatedly so that when you’re under pressure in the production environment, you feel comfortable and confident. Bonus points if you use this environment to build checklists and screenshots for rarely-done tasks like recovering from corruption.
You can’t use the development or QA environment because many of these tasks will take the entire environment down – sometimes by design, and sometimes by accident. If you’re in the middle of testing a patch at 9:30AM, and something breaks, and you’ve got a meeting starting at 10AM, you don’t want to have the entire developer team sitting idle because their SQL Server is down. You want to be able to leave this environment in a broken state for hours or days without feeling clock pressure.
Staging is the DBA’s development environment.
You’re not going to use it for load testing or performance tuning, just rehearsing and testing infrastructure changes you might make in production. As such, the hardware quality and quantity doesn’t have to matter, but it needs to be as logically similar as possible.
For example, if your production environment consists of a 3-node production failover cluster and a 2-node cluster in DR, with log shipping across the two, then you’re going to want at least one cluster, plus a separate server to log ship to.
The individual nodes can be low-powered boxes – think single-CPU desktops with 16GB RAM and a couple of $250 1TB SSDs. (You want SSDs because you’re going to be doing a lot of backup/restore on these.) In theory, they can be VMs too, but often sysadmins freak out when you start asking for several terabytes of shared storage space for a staging environment. For $250, I can just avoid that argument altogether.
Use SQL Server Developer Edition – it’s free – and this whole thing costs you less than a grand.
4 Comments. Leave new
Hmmm, but in such weak (single CPU) environment you may experience different query plans (parallelism), how should we deal with it as production DBAs?
(Thanks for great article!)
Because SQL Server Developer Edition isn’t licensed by the core, I generally wouldn’t build a single-core machine for staging.
Single-CPU does not mean single-core. Most CPUs these days are at least 4-cores which is plenty for a staging environment (even without HT).
Brent, great suggestion about the inexpensive SSDs. We currently have a nice Hyper-V dev/staging environment running on an old Dell MD1220 storage unit with 20 cheap consumer SSDs (Crucial MX200 1TB) for our storage. Compared to enterprise-level hardware, it’s so much less expensive and these SSDs have been blazing fast. And we get more use out of the end-of-life hardware that rolls out of our production environment.