There are numerous options for scripting common SQL Server tasks. Most tasks can be scripted and many times the need evolves as the environment changes. It may begin with a simple task to backup production and restore it to a lower environment. Next, you may have the need to cleanse sensitive data from the lower environments. Later you may be tasked with automating the process of destroying and rebuilding an environment as needed. All of this and more can be automated. In this post, we’ll discuss a few options and their benefits. It is not an all-inclusive list but focuses on more common options that may exist within your environment.
T-SQL
Simple T-SQL is a good start when scripting simple tasks. Since your team already works with T-SQL, it greatly reduces the learning curve compared to other options. Many tasks can be accomplished aside from data manipulation. Leveraging the DMVs or even scripts that reach out to gather performance counters or other OS information can be useful within this scope. It can be a convenient option for tasks like a backup/restore between servers or emailing a summary of failed jobs for the day on the server.
Batch Files
Batch files are useful for many tasks that may need to be removed from the SQL Server environment itself. There is still the ability to run queries via sqlcmd but it also enables tasks that either requires a resource that cannot be accessed via T-SQL or if restarting the SQL services are a necessary part of the task. They are also useful in environments where the SQL Server Agent is not available. Batch files could be called by Task Scheduler to handle maintenance tasks.
PowerShell Scripts
PowerShell greatly increases power over batch files with less effort. More advanced scripting is generally simpler in PowerShell due to things like it’s .NET and WMI integrations and ability to chain commands together. The ability to create modules as well as the readily available modules from others can make tasks much simpler.
dbatools
Dbatools adds many modules to PowerShell to make common DBA tasks trivial. It loads hundreds of commands that can handle many types of tasks for managing, configuring, and installing or patching SQL Server. Many of these commands can also be chained together so a command to check for registered servers could be piped a command to check for failed jobs which could then be emailed out with relatively little scripting necessary.
Ansible Playbooks
Ansible is very powerful when it comes to provisioning. Although it may not be the first choice for many ongoing daily tasks with SQL Server, Ansible can be great at setting up SQL Servers consistently according to your team’s practices. A playbook can be created to install and configure SQL Server to reduce setup time and keep the environment consistent. It can also be combined with other tools such as Terraform to give additional power. As an example, a test environment could be destroyed and completely recreated in a VMware cluster in minutes rather than hours.
Summary
These are just a few of the options available. There are many other methods that could be used; however, these are common options and skillsets in an enterprise environment. They can also be ran from commonly available schedulers if they need to be done on a regular basis.
By following these best practices, we hope you understand more of your options when it comes to scripting common SQL Server tasks. For any other SQL server support questions, reach out to our team of experts at House of Brick today.
