My environment has over 400 servers and we use MSX/TSX to manage those servers but we want to be able to have different parameters on different servers. Plus we have the need to run Ola’s scripts on Linux with sqlcmd not available in SQL Agent we will need T-SQL jobs. So I’ve created configuration tables for each of his procedures with the defaults pre-populated with this defaults and a script that will create a T-SQL job. This tables and jobs can be found in my GitHub repository here and all you need to do is create the table and the jobs the schedule the jobs. Anytime you need a server to be different from the default you just change the values in the table on that server. Also, if you happen to have all your jobs running on the same storage you may not want them to run them all at the same time so here is a little trick to stagger the jobs across your maintenance window that a coworker of mine came up with. This will allow setting a schedule for to run every hour from midnight to 6:00 AM and based on the server name it will pick an hour to run the backup so the load will be spread out.
IF (CONVERT(INT,ROUND((DATEPART(HOUR,GETDATE())%6)/2.0,0))=ABS(CHECKSUM(@@SERVERNAME))%6+1)
BEGIN
EXECUTE dbo.DatabaseBackup ....
END
If you are using Ola’s scripts out of the box you would first install Ola’s scripts as is on your existing servers. Then you can run my scripts to set up the configuration tables on each server. And the job files to create drop the jobs you had before and recreate them using T-SQL steps. If you need to do anything custom, change the values in the table or add records to the table and set up a job that queries only that record from the table. If you are using MSX/TSX. you will need to run the setup the jobs and specify the targets.
