I just completed a chapter for another book where I spoke about the Recovery Point Objective (how much data you are prepared to lose) and Recovery Time Objective (how long you have to bring your environment up again) after a disaster, and while I never get tired of repeating myself, that’s SQL Server. What happens if your development environment — or workstation — experiences a catastrophic failure?
Or what if, say, you’re on a cruise ship in the middle of the ocean with Internet access and a phone (but no laptop) and your on-call person just died? (I’ll leave this as an exercise for the reader to decide if this really happened.)
The answer is, if we do a careful bit of planning using the same disaster recovery principles we already know, the impact could be minimal. Note that this post assumes that you have Internet access and are using Microsoft Windows as your environment.
Get a VM in the cloud
Azure VMs are a dime a dozen these days. You can get really inexpensive VMs that are powerful enough to work on in bursts, with enough RAM and CPU to get by. So this first tip is to set one up for yourself. If you’re a Windows person (many SQL Server data professionals use Windows), you can either use a (licensed) Windows 10 image of your own, or just use Windows Server 2019. It works almost the same as Windows 10, and you can get up and running on a VM in about 5 minutes. You don’t need a SQL Server VM, and I’ll explain why in a short while.
Keep in mind that you will be keeping this machine turned off most of the time. When you’re not using it, you can stop the VM and your compute costs will be zero, but your storage costs will continue to be billed. That means picking the most cost-effective option for storage, even if it’s slow. After all, this machine is an insurance policy, not your main box. Size the storage accordingly.
Keep your operating system up to date
While this may seem obvious, the Windows release cadence has increased since Windows 10 came out. We are seeing major updates at least every six months, so if you have an emergency VM in the cloud, you’ll need to spin it up at least once a month — preferably after Patch Tuesday — and just let it update using the built in Windows Update. Once it’s done you would spin it down again.
Keep your software up to date
This is the easy part. Get Chocolatey. It works with PowerShell, which is installed on Windows by default, and it can keep your third-party software up to date with very little effort. Some of the applications I personally use Chocolatey for include: SQL Server Management Studio, Visual Studio Code, Azure Data Studio, Notepad++, Mozilla Firefox, Microsoft Edge (the new Chromium one), Visual Studio 2019, Dropbox, Slack, GitHub Desktop, Sysinternals (including Process Explorer, Process Monitor, and so on), Total Commander, SentryOne Plan Explorer, Wireshark, 7-Zip, AutoHotKey, Paint.net, Python, Red Gate’s SQL Toolbelt, and last but not least, Choco Cleaner.
Oh, and remember what I said about SQL Server Developer edition previously? Chocolatey has you covered.
I didn’t mention Microsoft Office tools in this list (including OneDrive and Teams) because I pay for Office 365, and that is maintained using its own update mechanism.
Chocolatey gives me the ability to have a common set of tools that I install on any Windows machine I own, using nothing more than a plain text file when installing the first time, that I can update as often as I need. That text file can be stored in Dropbox or OneDrive and referred to by Chocolatey when you first install it.
Keep your code up to date
Do you write code? Do you keep it in a source control system? Is that source control system Git? That’s excellent news, because you can use a private remote repository provider like GitHub, GitLab, Azure DevOps, or BitBucket, and it can be accessed from anywhere in the world with an Internet connection. If you use Chocolatey (and you should), then the desktop tools are often installed for you already, including GitHub Desktop. If instead of “source code” you think of “plain text files,” you can imagine a great deal of uses for Git, so get your PowerShell and Transact-SQL scripts in there too. There’s no excuse.
Keep the other stuff in sync
Let’s say you have documents that aren’t code but you need to have them around just in case. Maybe you have password-protected compressed files with customer information in them, or use a password manager. Maybe there are some configuration files that shouldn’t be in the Git repository but you need close at hand. This stuff needs to be kept in sync as well, and sometimes you’ve just got to get OneDrive or Dropbox on the case to make sure these are available for you.
Deallocate the VM
Once you’ve set up and configured the machine the first time, and gone through a complete set of updates followed by running the Disk Cleanup and Choco Cleaner tools to remove temporary files, shut down the VM until next month, where you’ll spin it up again to get it up to date.
Conclusion
This insurance policy gives you a relatively inexpensive way to get up and running in an emergency. Because you keep it up to date monthly, there is less of a risk with running into forced updates when you need it to let you do your job. As long as the tools you use are licensed correctly (and there are free developer and community editions, or open source tools as well), you’ll be in a good position when disaster strikes. In fact, the Chocolatey file and a good sync process with Dropbox or OneDrive would even help to rebuild your workstation after a failure.
Share your tips in the comments below.
Featured image copyright Randolph West. All rights reserved.