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:
1. Install SQL ServerThe 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 ConfigurationsInstallation 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 updatesLike 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:
Now SQL Server allows you to slip stream service packs along with the actual installation binaries (Overview of SQL Server Servicing Installation
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 MaintenanceThere 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 MaintenanceThere 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:
Transaction LOG Backups
Check database consistency
Backup cleanups, etc.
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.
Visit this link for backup of Analysis service database using script.
6. Alerts and NotificationsOnce 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. MonitoringMonitoring Blocking more than 15 mins
Monitoring Open Transactions
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 AvailabilityThere 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 databaseThere 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 usersWhen 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.