Using Ruby & SQL-DMO to Automate SQL Server Tasks

If you work with Microsoft SQL Server, you might like to know that you can automate many of your administrative tasks by leveraging Distributed Management Objects (SQL-DMO). SQL Server's Enterprise Manager is based on DMO, so most (if not all) of what you do through the Enterprise Manager interface can be automated through SQL-DMO -- and Ruby. Create, manage, and backup databases, tables, views, jobs, stored procedures, and more. Let's take a look at some of the available objects and Ruby code that uses them.

We start by creating an instance of the SQLServer object:

require 'win32ole'
server = WIN32OLE.new('SQLDMO.SQLServer')

To connect to your server using SQL Authentication, call the Connect method providing the server IP address or name, the login, and password:

server.Connect('127.0.0.1', 'login', 'password')

To connect using Windows Authentication, set the LoginSecure property to true, then call the Connect method with just the server IP address or name:

server.LoginSecure = true
server.Connect('127.0.0.1')

When you're done, be sure to close your connection by calling the Disconnect method:

server.Disconnect

The Databases method returns a collection of Database objects. You can access a specific Database object by passing the database name to the Databases method:

database = server.Databases('my database')

The Database object includes dozens of methods and several collections, including Tables, Views, and StoredProcedures. This code, for example, prints the names of all tables:

for table in database.Tables
    puts table.Name
end

Furthermore, many database objects expose a Script method that returns the SQL code for creating that object. This little block of code saves the SQL for creating all your Table objects:

for table in database.Tables
    File.open("#{table.Name}.sql", "w") do |f|
        f.puts table.Script
    end
end

The following block of code writes the text of each stored procedure to a text file:

database.StoredProcedures.each do |sp|
    File.open("#{sp.Name}.txt", "w") do |f|
        f.puts sp.Text
    end
end

The SQL Server Agent service, which runs and manages jobs, is accessed via the JobServer object. The following code starts and stops this service:

server.JobServer.Start
server.JobServer.Stop

The JobServer object's Jobs method returns a collection of all SQLServer Agent jobs:

for job in server.JobServer.Jobs
    puts "Name: #{job.Name}\nDescription: #{job.Description}\n\n"
end

I have a script that iterates over the Jobs collection and creates a "jobs inventory", an Excel workbook listing each job's name, description, frequency (daily, weekly, etc.) and start time.

Further details on SQL-DMO can be found in this MSDN article, and in this SQL Team article.

That's all for today, but if you use SQL Server, perhaps this has given you some ideas on how to use your Ruby skills to make SQL Server admin tasks a little easier.

UPDATE: You may also be interested in this earlier article about using Ruby and ADO for your SQL Server queries.

Feel free to post a comment here or send me an email with questions, comments, or suggestions.

Thanks for stopping by!