I have about 10 SQL Clusters/ 20 SQL Instances.
Each currently has a Maintenance Plan to do backups/index maintance etc, and a set of SQL Jobs to do DBA style stuff like Block Monitoring, and checking Job consistency between High Availability Replicas etc.
I want to move away from manually changing these plans and jobs on all 20 instances so I've started looking at Multi Server Administration.
Now I understand that I nominate a Master server, set up my jobs and plans once, and these are then pushed out to nominated target servers.
What I can't find much information on is how instance level specific settings are changed. So my maintenance plan is identical for all 20 servers except the connection string to connect to SQL and the file paths to where backups are stored. I know that multi-server administration takes care of the connection part, but how do you parameterize file paths in jobs or plans?
All backups for all servers go to the same UNC root path but then I have a 2 letter foldername that matches the instance name so I need these 2 letters to change for each server when deployed.
\\fileserver\sqlbackups\auto backups\XX\Full Backups
I need the XX to be the same value as the value returned from "SELECT @@SERVICENAME"
Is this possible or does multi server administration assume that all servers back up to the same folder on a dedicated location and that instances do not share backup folders?
Same question for SQL Jobs. I have a Powershell script that exports all my SQL Jobs. The Jobstep takes the backup target path as a parameter. Can Multiserver Administration pass a dynamic value to this for each target server?