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
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:
sp_configure 'show advanced options',1 reconfigure with override sp_configure 'backup compression default',1 reconfigure with override sp_configure 'max server memory (MB)',32768 reconfigure with override
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.