Introduction
As DBA’s we usually have a lot of utility scripts sitting around that we use in our daily work. Examples might include things like common administrative duties (setting up users for an application for example), installing standardized maintenance routines, or even something as complex as a home grown utility database. Often times these scripts languish on some network share or (worse) our own computers, with previous versions lost for all time when changes are made. For those of us that come from a development background, this approach makes us shiver. Keeping code that is not in some kind of version control system is inexcusable for any level of software professional, and DBA’s should be no exception. Used for quite some time by software developers, version control is a wonderful tool for administrators as well, for reasons we will discuss. But first, I want to talk a little bit about my version control system of choice, Mercurial.
The Basics of Mercurial
Why Mercurial?
While the choice of version control system is a very subjective and personal one, there are a number of reasons why I think Mercurial is an excellent choice, especially for someone just venturing into the world of version control. Here are a number of those reasons.
No Server Required
With many version control systems a requirement is to have some kind of central server which serves as the actual repository. But with Mercurial, the repository is always local to the user’s machine. It is true that in some setups you can have a central copy of the repository, and that is certainly a useful pattern once you get into multiple user scenarios, but it is by no means a requirement. In fact, the very first tutorial given in Mercurial’s guide is a very simple scenario where you simply want to track changes made to some files. Because of this, the overhead and effort required to set up your first repository is extremely small.
The Distributed Model
Mercurial is a distributed version control system, which, according to Wikipedia, is characterized by “[taking] a peer-to-peer approach to version control, as opposed to the client-server approach of centralized systems”. Since each person has a complete, independent copy of the code, it gives much more freedom to do things like more frequent commits without fear of impacting others of breaking things. I for one commit as soon as I’ve accomplished some small unit of work; this means at any given time I’m able to deliver a working version of the code with some kind of incremental improvement included, while still feeling free to experiment and continue working. It’s also very nice to not depend on having a network connection available to commit my work or compare versions (taking some time to disconnect when working is a good thing, but that’s another essay). In addition, the distributed model means that at any one time there are many copies of the code in different places, thus somewhat mitigating the risk of a single failure causing the entire repository to be lost.
Note: this is not an excuse to avoid backing up your work, only a bit of an insurance policy. Version control is not the same thing as a backup!
It’s Free!
Mercurial is one hundred percent open source and free (as in beer) to use. There are no costs associated with licensing or infrastructure, so here again, the investment needed to try it out is extremely small.
When to Use Source Control
Now that we’ve gone over the advantages of a tool like Mercurial, let’s talk in more general terms about specific scenarios in which version control is valuable for DBA’s. It’s true that we don’t have mountains of code to maintain like developers do, but there are still plenty of times when the tracking of changes is a useful exercise. But first, why use source control with our scripts? First, it allows us to track changes to them over time, and easily pull a previous version if something we change turns out to break things. Second, as we’ll see once we get in to some more advanced operations, we can easily create customized versions of scripts for certain scenarios while still allowing changes / enhancements to the parent script to be “pushed” down to the custom versions. This gives us the most flexibility in maintaining things. For example, perhaps we have a standard set of configurations that need to be applied to servers, but for some reason, it needs to be changed slightly for a particular server. By utilizing version control, when we make changes to the original version, we can easily apply them to the custom one.
But now, let’s talk about a few specific examples of what we should be putting in source control.
Server Configuration
When configuring the SQL Server installations we support, it’s paramount that they be kept as standardized as possible. There are two ways we can do this: 1) create a lengthy document describing how to click through the Management Studio GUI and make appropriate selections, or 2) create a script that allows us to easily and repeatedly apply standard configurations. If it’s not obvious from the description, my choice is the latter. Because really, who want to spend more time than necessary in Microsoft Word, only to subsequently spend time playing MATM (Monkey At The Monitor) clicking the mouse?
Here are a few best practices to use when creating these configuration scripts:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT @MaxServerMemory = (physical_memory_kb/1024) FROM sys.dm_os_sys_info; IF @MaxServerMemory * .2 > 2048 SET @MaxServerMemory = @MaxServerMemory - 2048; ELSE SET @MaxServerMemory = @MaxServerMemory - (@MaxServerMemory * .2); exec sp_configure 'max server memory (MB)', @MaxServerMemory; |
This is just one example of many kinds of configurations that can and should be scripted and tracked.
SQL Agent Jobs
One of a DBAs’ tasks is to create, maintain, and otherwise administrate scheduled operations on database servers via the SQL Agent system. Just like any other kind of code, SQL Agent jobs will need changes from time to time, be it in their steps, their scheduling, or various other aspects. There are also likely to be standards that need to be applied to all jobs, such as adding a notification for failures. For these reasons, scripts are again the way to go.
Here are some things to think about when writing scripts for SQL Agent jobs:
- How will changes be applied? The easiest method would be to drop and recreate the job from scratch every time, however unless you do some trickery behind the scenes you’ll lose valuable history data. We may get into some ways around this in a future article, but, for now, consider creating shells of the jobs and updating them rather than blowing them away and starting from scratch each time.
- Are there any standards that need to be applied, such as notifications on failure or capturing output in log files?
- Are there dependencies that need to be handled either as part of the script or as a separate one, such as operators, proxies, etc?
Utility Scripts
Every DBA needs to have a set of utility scripts and code to help them in their daily work. This has numerous advantages over going the route of treating everything as one-offs, such as:
- It will be easy to find scripts for various common situations, thereby letting you get your work done faster and easing troubleshooting.
- By using scripts to complete your work you can ensure it is done in a repeatable and consistent way.
Just as developers need to use version control to track changes to their work, so should we.
Here are a couple ways to find things that you should be using scripts for:
- Do you have checklists? If so, these are a great candidate for scripting, since you already have done the hard work of defining what needs to be done.
- What do you spend most of your time doing throughout the day? Where there’s work, there’s an opportunity for scripting and automation.
- What do people constantly ask you about? If you’re constantly getting questions about how to set up Service Broker, maybe it’s time to create a script that does it all and simply hand that off to interested parties.
Conclusion
In this first installment of the series, we’ve laid out a case for why DBA’s should use version control software, with some specific advantages of the Mercurial product. In the coming installment, we’ll get a repository created (which as you’ll see is far easier than it sounds), commit our first file, and show how you can view changes made to a file and commit them safely. Finally, we’ll see how we can easily remove a change we accidentally made. Stay tuned!
In what was originally a temporary job creating reports for clients of a financial company, he decided we would rather write some VBA macro code than repeatedly push the same buttons on a keyboard .
After working in low level development and support roles, he transitioned to the role of a full time SQL Server DBA, supporting both production and development systems, and has never looked back.
He currently works as a senior DBA for Gateway Ticketing Systems, advising customers on maximizing SQL Server performance and availability. He also recently co-founded the independent software startup Do It Simply Software. He loves learning about what makes SQL Server tick, and how to make it go faster. For more information, follow him at sqljosh.com
View all posts by Joshua Feierman
- A DBAs Introduction to Mercurial – Branching and merging - February 19, 2016
- A DBAs introduction to Mercurial – Working with files and changes - January 28, 2016
- A DBA’s introduction to Mercurial – When and why we should use version control - December 18, 2015