T-SQL Tuesday #015 – Automation in SQL Server

February’s T-SQL Tuesday is brought to us by Pat Wright (Blot|Twitter).  The topic is “Automation with T-SQL or with Powershell or a mix of both.”

As I mentioned in a prior post, increasing my Powershell skills is on my list of techie resolutions for this year.  I haven’t done much with it yet, so that leaves me with writing about T-SQL.  That’s fine because I love working with T-SQL.  I’ve been fortunate to have worked on some really fun automation projects with T-SQL and SQL Server Agent over the years.  My first thought for this post was to describe one of my favorites, but I decided to take a different route.

So let me ask this… Have you used T-SQL?  Yes?  Then you’ve done automation with T-SQL!  Here’s why I would say such a thing.

Several years ago I was brought in to meet with a development team that was dealing with performance issues in some stored procedures.  They had decided that the solution was to do processing outside of SQL Server (for context I should mention that this team included the type of Oracle fans who think that SQL Server isn’t a serious database management system).  They began describing their plan, and how it would be fast because it would be written in C++, all processing would take place in memory, etc.  I asked how big the data set would be during processing.  They replied that it would be larger than the amount of RAM on the server.  However, the developers had come up with a paging scheme involving a file on disk (the operating system already has disk paging, but they wanted to control when this would happen), access methods, etc.

At some level you have to admire this amount of ambition.  However, in some ways they were proposing building a database management system, but without the good and important things like ACID properties.

So as you might imagine, I eventually let this development team know that everything they were proposing was already in SQL Server, and no complex C++ application needed to be developed and maintained for years to come.  Using features in the Standard Edition of SQL Server, T-SQL and existing hardware, we were able to solve the performance problems while still using stored procedures and the existing process flow.

I started down this path by saying that just using T-SQL is enough to be considered “automation”.  To those of us who use it every day, that might not seem appropriate.  However, consider all that’s going on under-the-hood each time you submit a query.  Or from another angle, if you don’t know what’s available and waiting to be utilized on your server, then you might set out to build a similar automated solution from scratch.  Is “automation” in the eye of the beholder?

I’m looking forward to reading the contributions to this month’s T-SQL Tuesday and seeing all the ideas for better living through automation.  Thank you very much to Pat Wright for hosting T-SQL Tuesday #15, and thanks again to Adam Machanic (Blog|Twitter) for creating this monthly blog event!

About Noel McKinney

Noel McKinney is the administrator of noelmckinney.com
This entry was posted in T-SQL Tuesday and tagged . Bookmark the permalink.

3 Responses to T-SQL Tuesday #015 – Automation in SQL Server

  1. Claire says:

    A) That C++ solution sounds like a nightmare.
    B) Nice work convincing them that T-SQL was the way to go!
    C) Great post!

  2. Sal Young says:

    It seems like new technologies come out every day to do the same things we have done for years and vendors are bombarding us with marketing campaigns to use them.

    It’s not uncommon to find ourselves wanting to implement these new technologies and techniques whilst the solution has been available to us all along. I would like to praise you for the simplicity and pointing out the obvious.

    Your blog entry for T-SQL Tuesday #15 is refreshing and points out that many of the database objects in RDBMS are forms of automation. I’m now a subscriber to your blog.

  3. Pingback: T-SQL Tuesday #15 Summary | Sql Server Insane Asylum

Comments are closed.