This month’s T-SQL Tuesday is about automation and have methods stayed the same or changed. I haven’t had a chance to automate much recently, but back in the day when I was a database administrator at one company, automation was my favorite part of the job.
When would I decide to automate something? Usually, about the third time I performed a task, I would decide to automate it if I had the time. Writing a script to automate a task takes time. Generally more time is taken to write and test the script than to just perform the task. However, the benefits of writing and scheduling the script quickly take over performing a series of steps manually.
My favorite tool for automation was T-SQL. T-SQL has constructs for looping and logic just as any other language. For the rare occasion that T-SQL wasn’t suited for the job at hand, I would rely on VBScript. One of the coolest things I did was set up a linked server to Active Directory to read account names and properties to synchronize with several systems.
At the time, close to 10 years ago, I had about 40 SQL Servers to manage and came up with an idea. I set up one SQL Server instance to collect information from all the other instances via a linked server to each one. Most DBAs cringe at the thought of linked servers, but in this case, it was a great solution. I had a jobs set up to gather information from each of the servers using a cursor to loop through the server names. Ouch! Another bad word, but it served the purpose well.
I had a job to email me a report each morning before I left the house in case there was something that needed attention right away. I also had SSRS reports that I could review once I got to work with information about things like database growth and missing backups.
Overall, it was a great solution that I continued to use even when a better tool became available.
What was that better tool you may ask? Well, that tool is PowerShell. With PowerShell you can easily loop through lists of SQL Servers connecting to each one to perform a task – without setting up the linked server. PowerShell is great for performing the same function on a collection. It could be a collection of tables, databases or servers. Combine PowerShell with T-SQL and you can do just about anything!
If you are new to PowerShell, check out this series of articles from sqlservercentral.com.