Chef has really made great strides recently to enhance the support and capabilities of managing Windows hosts. I’ve recently been working on automation of SQL Server with Chef, and wanted to share details in this post on how you can run TSQL against a SQL Server instance.

There are a couple of ways to run TSQL against a server. One is to use the database cookbook that makes a few Lightweight Resource Providers (LWRP) available to manage databases, and one of those is for SQL Server. The other way is to use either the execute resource that calls sqlcmd.exe or the powershell_script resource and use the invoke-sqlcmd Powershell cmdlet. The thing to keep in mind is that the database LWRP doesn’t play well with TSQL with multiple batches (using the GO keyword), so if this is the case in your situation you’re probably better off with the execute or powershell_script resources. Just a decision point to keep in mind.

So let’s cover the execute resource method of running a TSQL script. In this case it’s a database maintenance script (based on the awesome Ola Hallengren scripts) that set up some standard maintenance activities. This is a script that I drop on the server with a Chef template resource. In this case there’s a couple variables that come in to play to set some values in the script itself.

template 'C:\DBScripts\DatabaseMaintenance.sql' do
  source 'DatabaseMaintenance.sql.erb'
  variables(
    :backup_path => 'C:\db_backups'
    :retention_hours => '168'
  )
end

Now I want to actually execute the script. First, I make sure that the sqlps module is imported. This is needed in the guard for the execute resource and ensures that the invoke-sqlcmd cmdlet is present on the system. In my specific case it was much easier to get the return values from the invoke-sqlcmd command than trying to get it through sqlcmd.exe.

powershell_script 'sqlps module' do
  code 'Import-Module "sqlps" -DisableNameChecking'
end

Next comes the actual execute resource. I wasn’t able to get the full functionality of the script working when using the powershell_script resource in my case so I had to mix the execute resource with the powershell_script guard. I’ll be circling back to see if I can get it working fully in the powershell_script resource so that it’s a bit cleaner, but this works for now. Here I run the script through sqlcmd.exe, but not_if the number of jobs that start with DBMaint (a naming standard I follow) is not equal to 11. If there are 11 jobs we know the jobs are there and we’re good. If there aren’t 11 jobs, we need to run the script. The code in the not_if guard will either return true or false depending on the results of the query. This brings a bit of idempotence to the setup of these jobs, ensuring that we only do something when something actually needs to be done. It’s not perfect, but is a good starting point.

execute 'setup db-maint jobs' do
  command "sqlcmd -S localhost -i \"C:\\DBScripts\\DatabaseMaintenance.sql\""
  guard_interpreter :powershell_script
  not_if "(invoke-sqlcmd -ServerInstance \"localhost\" -Query \"select count(*) from msdb.dbo.sysjobs where name like 'DBMaint%'\").Column1 -eq 11"
end

Putting it all together in one recipe:

template 'C:\DBScripts\DatabaseMaintenance.sql' do
  source 'DatabaseMaintenance.sql.erb'
  variables(
    :backup_path => 'C:\db_backups'
    :retention_hours => '168'
  )
end

powershell_script 'sqlps module' do
  code 'Import-Module "sqlps" -DisableNameChecking'
end

execute 'setup db-maint jobs' do
  command "sqlcmd -S localhost -i \"C:\\DBScripts\\DatabaseMaintenance.sql\""
  guard_interpreter :powershell_script
  not_if "(invoke-sqlcmd -ServerInstance \"localhost\" -Query \"select count(*) from msdb.dbo.sysjobs where name like 'DBMaint%'\").Column1 -eq 11"
end

This is a quick and easy way to execute TSQL against an instance using standard utilities (or cmdlets) that we use outside of Chef, yet still allows for idempotence so that we only do something when something needs to be done, and ensuring that that server is in the same desired state after every chef-client run.