Deploying SQL Server Automatically | Voice of the DBA

Deploying SQL Server Automatically

I’ve had to install SQL Server many times over the years. Often it has felt that most of these installs were one-offs, a dev server, a new QA instance, a production server for a brand new application. A few times I’ve had to recover from disaster, including restoring master, but often, I just installed SQL Server manually because that was quick and guaranteed. I knew what needed to be done for most of the instances.

However, I couldn’t be sure. In the past there were relatively few settings that were ever changed outside of the master database, but today there are more, and perhaps more importantly, the tolerance for making mistakes with any security missteps, is low. I’ve spent a bit of time learning to do unattended installs, and I’ve tried systems like FineBuild for installing SQL Server. In any size organization that might want certain standards set up, using one of these systems is important. Perhaps one of the better reasons to do this is ensure that your dev and test systems are configured the same as production to prevent any silly mis-configuration problems.

This week I saw a post on using Ansible to install SQL Server on Linux, with all the various configuration. While I do think that it’s easier to install SQL Server on Linux programmatically, there are a number of items to set and configure. Following along the install, it’s interesting how much more this feels like something a developer would do, which is how many modern Operations groups approach installs and upgrades. Everything needs to be done without a human directly involved, often because of scale. There are so many systems we manage, often a mix of VMs, cloud, and local systems, which mean that a consistent, programmatic way of installing instances is needed.

That’s likely one of the most important skills for a modern system administrator. We need to learn to use tools to get our work done. Whether these are free, open-source, or purchased, using a tool to work at scale is more important today than ever before. Even if you are part of a development group, learning to manage systems with code can help ensure you can scale and grow quickly, and more importantly, hand off this job easily to someone else when you reach that point.

I think there are plenty of homemade scripts and tools that can help here, but almost every time I’ve seen one of these, it’s not very portable to other staff, especially if the author isn’t available. Too often these tools take shortcuts or are specifically tailored to the current environment and not the future one. These days, for many functions we tackle there are extremely well built tools available at no, low, or modest costs. I’d encourage anyone that is handling these tasks to learn about the tools available and pick one. I don’t know that I think any of these are necessarily easier to use, but they are all well documented and capable of handling most of your install, update, and configuration chores.

Steve Jones