SQL SERVER MAINTENANCE SCRIPTS MULTI-SERVER DEPLOY PACKAGE
OVERVIEW
The SQL Server Maintenance Scripts Multi-Server Deploy Package is a simple SSIS package that allows for easier deployment and management of a large number of SQL instances backups and maintenance. It consists of a SSIS package and a few configuration tables. Configuration tables hold servernames, backup times, backup folders, exceptions and script details, the SSIS package is what does the actual work.
Below is a basic outline of how to Install and configure the package, if you are unsure or run into any issues please use the codeplex site to get help. http://sqlmaintscriptdeploy.codeplex.com
INSTALLATION
1. Download all of the required components:
a. SSIS Package & Create sql tables script - http://sqlmaintscriptdeploy.codeplex.com/releases
b. Ola Hallengren’s Server Maintenance Solution - http://ola.hallengren.com
2. Create a Folder that is accessible to the server running the SSIS package (preferably a network share) e.g. \\Servername\DBA\MaintScripts and make sure permissions are correct so user running the SSIS package has read/write permissions to the folder
3. In this folder create a folder for the version of Ola’s script you downloaded and put the script you downloaded from Ola’s site in this folder. I use the date of the version formatted like this 04092012 The package supports multiple versions so each time you download a new version create a new folder and put the new script in the folder.
4. Run the CreateTables.sql script you downloaded in step 1 against the instance that will house the SSIS package
5. Edit the MaintScript table adding in the details of the script you downloaded the ScriptDate column is the foldername you used in step 2 ie 04092012
6. Edit the InputServers table adding in the instances you would like to manage with the solution. Using the below as a guide
a. ServerName – This is the name of the server including the instance ie NameOfServer or NameOfServer\InstanceName
b. Comment – Not used in the solution just for your reference
c. UseInput – either YES or NO. If set to NO the server will be ignored by the solution
d. BackupDir – The local path to the backup folder for that server ie c:\backup (Where c: is the c: of the server)
e. Environment – Currently Supported values are PROD, DEV, SIT, UAT, TRN these values define the type of server it is as it uses different schedules for different environments.
f. BackupTime – Time of the Full backup job in the format of 21:30:00 (HH:MM:SS 24hour)
g. OutputFileDirectory – Not yet implemented but will be able to set the job outputs to a custom folder in a later version.
h. MaintScriptID – The ID from the table MaintScript of the version of the script you want to use for this server. I use this to test a new version against dev before pushing it upwards.
7. Edit the AMS_Exceptions table adding in any individual changes to backup jobs for a particular server.
a. Current ExceptionTypes Supported:
i. DatabaseAddRemove – Allows you to modify the databases that are backed up in a backup job ie removing a single database or using wilcards to remove many. Available changes can be found here: http://ola.hallengren.com/sql-server-backup.html An example row is something like this:
ExceptionType ExceptionServer ExceptionValue ExceptionValue2
DatabaseAddRemove NameOfServer USERDATABASES, -NameOfDBToRemove DatabaseBackup - USERDATABASES - FULL
8. Copy the dtsx package to the root folder created in step 2 and open it in BIDS
9. Modify the parameters for you environment using this guide:
a. DefaultPath – The root folder path you created in step 2
b. EmailRecips – emails you want to get notifications from the jobs failing
c. InputServersDB – Name of the DB you created the tables in
d. InputServersServerName – Name of the server you created the tables in
The rest of the variables can be left as is and are used in the executing of the package
10. If you want to modify the default schedules that are set for each environment open the two sql tasks “Edit existing schedules” and “For New instances setup the schedules” and modify the configuration section of the script
11. Now save and build the package and either setup a scheduled task to run it or just run it manually when you make changes
The next few versions will get a simpler Installation process using a config file and additional Exceptions added as needed. Any issues please ask at the codeplex site linked at the top of this doco.

Last edited Dec 20, 2012 at 5:02 AM by slimrichard, version 4

Comments

No comments yet.