question

harveyhonda75 avatar image
harveyhonda75 asked

Multi-Server Maintenance Plans in SQL Server 2019

Hello all! I hope you're all staying safe and well! So I've set up Multi-Server Administration in my environment so that I can just quickly add new target servers to my already created Maintenance Plans. I've created a Multi-Server Maintenance Backup plan to backup user databases nightly. However, it doesn't seem to be intelligent enough to identify and use the target server databases when running the plan on the target server. Does that make sense? It basically sends out the the job to all of the target servers, but they try to backup the Master server databases. What am I missing?

maintenance-plansmulti-server
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered

I don't have any experience with Maintenance Plans in MSX/TSX setup. But generally, I wouldn't schedule them as MSX/TSX jobs.

A Maintenance plan is nothing else than an SSIS-package running. The SSIS package has a connection manager, which is most probably setup for your MSX-server, where you created the maintenance plan. If you really need to run maintenance plans with MSX/TSX-setup, you have to make sure the connectionstring setup in the connection manager points to the instance that SQL Server Agent is running against. If you're not using named instances anywhere, you could just use "." as server-name in the connection-string. But with named instances it gets a little trickier. Tbh I don't know how it would be done, I haven't looked into the details of how you can parametrize connection string in Maintenance plan.

Alternatives:

1) Run the jobs from the MSX-server only, with subplans for each server with its own connection string.

2) Use T-SQL instead of Maintenance Plans. You could for example use Ola Hallengren's excellent scripts for index maintenance and database integrity checks. https://ola.hallengren.com. Use one job to deploy the scripts to the target servers and then setup jobs to run the maintenance tasks.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.