This post was inspired by the following tweet from the Robert Davis.
I have seen it all. "I imported SQL Server's registry from another machine but it won't start." <- No s#!+
— Robert L Davis (@SQLSoldier) February 3, 2016
A little bit of a story—I once had a client who was insanely worried about renaming SQL Servers for a migration. Note: It’s no big deal to rename an offline SQL Server—the ONLY thing you need to change is the internal server name by running sp_dropserver and sp_addserver. There is a single registry key called “ORIGINAL_SERVER_NAME” it stays the same across multiple server renames. Every other SQL registry key changes when you do a rename of the server. So there is nothing to worry about in the registry if you do a name change. This name change process was part of our automation strategy for cloud at Comcast, and if I had to guess, it’s also part of what Microsoft is doing in Azure.
Anyway, on to my original topic—I know most shops aren’t Comcast where we were deploying multiple servers a week. However, automating your installation process has a lot of other benefits—you don’t have to worry about misconfigurations, unless you put in into your script. It also forces you into adopting a standard drive letter approach to all of your servers. So when something breaks, you know exactly where to go. And more importantly you save 15 minutes of clicking next.
I love this comic from XCKD. It doesn’t take a lot of time to automate this process. So how to you do it?
It’s easy—run through the installer, and use all of your best practices, service accounts like you would for any new install. When you get to the end, before you click install. You’ll notice this:
See that file—save it somewhere handy and then cancel out of the install. There are a couple of edits you’ll need to do to make it automation ready, the biggest is you want your install to either run in quiet or quiet simple mode (I prefer quiet simple—it shows the GUI, but doesn’t allow any interaction with it). From there it’s as simple as running the following command (assumes you have the SQL media mounted under D: )