TOP 10 SQL Server Requests That Should Be Automated

March 15, 2011

Microsoft is capturing the market using simple and elegant graphical user interfaces. However, when it comes to managing many computers, scripting/automation is the key. Microsoft also provides automation features in almost all of its products, including SQL Server.

There are mundane tasks that SQL Server Database administrators get as requests. This article discusses the various repetitive and boring tasks that should be automated.

Some of the many advantages for automating tasks are:

Reduce the amount of time you dedicate on repetitive activities

Reduce the money spent on these mundane works.

Use the time saved to solve quality issues.

Improve your ability to handle a larger volume of requests

The following are common repetitive tasks that should be automated.

1. Install SQL Server

The first and foremost request that a DBA usually gets is Install SQL Server. Installation of Microsoft SQL Server or Microsoft SQL Server Client is usually a long process and requires a lot of clicks, and both need to be automated. You don't need to click n number of times to install software. SQL Server installation can be done using unattended install. Check these articles on unattended installation, and also check books online.

2. SQL Server Configurations

Installation of SQL Server also requires configuration changes. Any configurations like memory settings, enabling features, processor settings, etc., should be automated. Most of the settings can be updated using the sp_configure command and using the combination of Windows PowerShell and registry object. All these can be automated. Example:

3. Install Service pack, Hotfix / cumulative updates

Like any product, SQL Server also comes with service pack updates and cumulative patches. SQL Server installation is always followed by installation of service packs and hotfix, etc. All the service pack installs, hotfix and cumulative updates can also be installed using the unattended installation feature. Example:

These service pack, hotfix and cumulative update requests either could become a standard to be implemented on all servers, or sometimes it could be ad hoc requests.

4. Server Maintenance

There are certain tasks that need to be done on the host or server level. For example, weekly reboot of the host or shrinking database files or archiving/purging or Change Service account password and so on can be automated using SQLAgent or windows scheduler job with Windows PowerShell or VBScript, etc., or a combination of all.

Though these can be ad hoc real requests, if everything is scripted and ready to run, you don't have to manually do these tasks.

This link talks about setting up the reboot cycle for active/passive cluster server.

5. Database Maintenance

There are database maintenance tasks that are very repetitive and need to be automated. You cannot log on to SQL Server everyday and do these maintenance tasks manually. These maintenance jobs are:

FULL Backups

Differential Backups

Transaction LOG Backups

Index maintenance

Check database consistency

Backup cleanups, etc.

Processing cube

All the above listed jobs could be ad hoc real requests. So if you have everything scripted and ready to run like SQLAgent jobs, you don't have to manually do these tasks. You can always kick off the SQL Agent jobs.

6. Alerts and Notifications

Once you establish a SQL Server standard on which alerts and notifications need to be enabled on every SQL server, you can go ahead and automate them. Just implement them on one server and then script it out. Once scripted, it can be executed on all the servers using command line utilities like SQLCMD or OSQL.

7. Monitoring

Monitoring Blocking more than 15 mins

Monitoring Open Transactions

Deadlock Monitoring

Monitoring Database Mirroring status

Monitoring Log shipping status

When all these tasks come as ad hoc requests, automated monitoring comes in handy. All you have to do is to send the results from these jobs to the users.

8. High Availability

There are many high availability features available in SQL Server. When these features are requested by your user, you could simply set all these up by automated using simple scripting. Examples include failover clustering and database mirroring.

There are other features related to database high availability like Log Shipping, Replication and so on that can also be automated using scripting. If you use SQL Server management studio, you would have noticed that there is a SCRIPT option in almost every objects that are displayed:

9. Synchronize QA databases with PROD database

There are requests like refresh database from Production to QA / UAT / Test servers and so on. Even though they are not consistently repetitive, it is always efficient to keep a readymade script that would automatically go to production server and take a backup or get the recent backup and restore it on the QA or test server.

See this link for a high-level process on how to synchronize a TEST database using Production backups.

10. Transfer Logins and users

When you do log shipping or database mirroring or even do a QA/TEST database refresh, the logins are not carried over to the target server. In order to do that, you need the stored procedure "sp_hexadecimal" and a script to generate those logins using SQL server security information from system tables. It is better to generate these logins before implementing log shipping or database mirroring or even doing QA/TEST database refresh. It is also necessary to generate these logins and copy the generated login script to the destination server on daily basis. For more on regenerating logins, read Re-generating SQL Server Logins.