Managing a large SQL Server inventory requires an efficient management processes.
Following on from DBA productivity and less is more , this post outlines the system I use to manage the DBA scripts across the database server environment
1) Develop a script library covering the essentials of DBA database management . Tasks include : Reindexing , statistics, defragementation, checkdb , kill user processes, kill all db connections and much more..
My current version of SQL_TOOLS has 345 scripts covering a wide range of DBA tasks
2) Create an installation T-SQL script. The purpose is to deploy the scripts onto the SQL Server Instance via command line .
An example of the script could be as follows . The script I actually use doesn’t allow the DROP DATABASE as the logon account doesn’t have DROP DATABASE privileges , to avoid any mistakes
This example script assumes you have the five scripts mentioned in the same directory :
/* SCRIPT: SQL_TOOLS_INSTALL.sql */ /* BUILD A THE SQL-TOOLS DATABASE */ -- This is the main caller for each script SET NOCOUNT ON GO PRINT 'CREATING DATABASE' IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = 'SQL_TOOLS') DROP DATABASE SQL_TOOLS GO CREATE DATABASE SQL_TOOLS GO USE SQL_TOOLS GO :On Error exit :r isp_ALTER_INDEX_main.sql :r isp_UPDATE_STATISTICS_main.sql :r isp_DBCC_CHECKDB.sql :r isp_KILL_ALL_DBCONNECTIONS.sql :r isp_KILL_USER_PROCESSES.sql PRINT 'SQL_TOOLS DATABASE CREATE IS COMPLETE' GO
3) Create a batch file installation script. This is the script you’ll execute from the command line – which will trigger the script in step 2.
An example is : /* SCRIPT: SQL_TOOLS_INSTALL.bat */ /* Excecute from the command line */ /*input parameter1 = ” SQLCMD -E -d master -S %1 -i "SQL_TOOLS_INSTALL.sql" PAUSE
4) Once the scripts are deployed onto the SQL Server Instance – you have a script library available to a) create scheduled or ad-hoc tasks b) deploy secure scripts for troubleshooting , analysis and reporting
The key to this system is a) maintaining a centralised script library , test and proven on lower environments. b) rapid deployment onto a sql server environment c) Securing the scripts and limiting execution rights to relevant accounts
This approach is only one part of a strategy . Further considerations are scheduling of scripts , managing installation, disaster recovery and inventory management