May 28, 2019 at 2:17 pm
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.
Example:
\\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?
Thanks
May 29, 2019 at 3:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
May 29, 2019 at 4:52 pm
Example: \\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? Thanks
I'm not clear on what you are doing with the maintenance as you mention having maintenance plans. I'm not aware of a way to dynamically set the backup paths in maintenance plans. Using SQL, you can dynamically build the path. In your example, you could use something like to build the path:
DECLARE @BackupPath varchar(100)
SET @Backuppath = '\\fileserver\sqlbackups\auto backups\' + @@ServerName + '\Full Backups'
Sue
May 29, 2019 at 9:03 pm
Another option that works if your backup path in your sql server settings is correct
DECLARE @BackupDir nvarhcar(255)
EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE'
, N'Software\Microsoft\MSSQLServer\MSSQLServer'
, N'BackupDirectory'
, @BackUpDir OUTPUT
May 30, 2019 at 9:58 am
Thanks guys.
I don't have a problem dynamically creating a back up location. What I'm attempting to understand is how you then take that dynamic path and add it to a Maintenance Plan stored on a Master server in a Multi Server Administration set up.
When I push that single plan out from the Master to the Target servers, I need the Target Servers to use that dynamic path and not the hardcoded static path stored in the Master Maintenance Plan.
Is this possible?
I'm new to Multi-Server Administration and trying understand what it can and can't do.
May 30, 2019 at 10:56 am
This was removed by the editor as SPAM
May 30, 2019 at 1:16 pm
I did think that was a bit simple for what you were asking .. I've never used maintenance plans before tbh hopefully somebody here is more familiar with them
May 30, 2019 at 2:28 pm
Thanks guys. I don't have a problem dynamically creating a back up location. What I'm attempting to understand is how you then take that dynamic path and add it to a Maintenance Plan stored on a Master server in a Multi Server Administration set up. When I push that single plan out from the Master to the Target servers, I need the Target Servers to use that dynamic path and not the hardcoded static path stored in the Master Maintenance Plan. Is this possible? I'm new to Multi-Server Administration and trying understand what it can and can't do.
Part of the issue is that with multiserver administration you push out jobs, not maintenance plans. And then a job may execute a maintenance plan. Nonetheless, I don't think Maintenance Plans themselves support dynamic configurations.
Sue
May 30, 2019 at 2:39 pm
Part of the issue is that with multiserver administration you push out jobs, not maintenance plans. And then a job may execute a maintenance plan. Nonetheless, I don't think Maintenance Plans themselves support dynamic configurations. Sue
Hi Sue, this article specifically says that Multiserver Administration pushes out maintenance plans.
https://www.sqlservercentral.com/articles/multi-server-administration
I'm at the point where I'm probably going to bin the plans and go to a fully scripted Job/SPROC maintenance solution that I can batch deploy with Registered Servers from SSMS, but before I do that, I'd just like to exhaust any and all official first party options to streamline deployments of Maintenance Plans over multiple servers.
Normal Jobs seem easy to deploy with Multiserver Admin as I can use the tips suggested earlier in this thread to write jobs that dynamically populate instance specific configs. It does seem an oversight if Maintenance Plans cannot have their backup folders changed dynamically as it makes Multi-server Administered Maintenance Plans essentially worthless in any scenario where a database with the same name exists on multiple instances which would result in those databases sharing the same backup folder.
May 30, 2019 at 2:50 pm
And it also notes how it isn't reliable (but doesn't use that wording) and mentioned how not all changes get propagated and you need to open up the maintenance plans on the servers to see if a change really took on the other servers. And if you read the comments about the article, you can see where people gave up on trying to use maintenance plans with multiserver administration. There are quite a few caveats that aren't addressed.
Sue
June 5, 2019 at 2:34 pm
Never used multi server admin (MSX) for maintenance plans,
however, did use it for managing +300 instances in several maintenance jobs.
Best way is to script a backup procedure (with dynamic location code) and push that in the MSX job towards the targets.
I have never seen the targets using "parameter" stuff from the master, all configuration is gathered from the target instance itself.
Also when using stored procedures: for example : exec master.dbo.myproc , you have to make sure that "myproc" exist on every target when launchin the job.
When respecting these rules, i found MSX quit good and easy to work with and follow up job results (those come back to the master).
But there is idd little documentation at hand on the web concerning MSX.
Wkr,
Eddy
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply