Maintenance Plans and DR

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720983

  • Ola Hallengren

    Hall of Fame

    Points: 3960

    SQL Server Central thread:

    "Under SQL Server 2005, we have found that we are having a problem with our maintenance plans. Since most of our servers are on VMWare, when we installed a new server, our system admins cloned this server and created a DR server at our DR facility. However, when we tested the DR server, the maintenance plans were running on the original server. The connections for the maintenance plans were still point to the original server, and not the DR server."

    Microsoft Connect:

    "After renaming a SQL Server, one must go in an edit all of the connections for a Maintenance Plan to coincide with the new SQL Server name."

    Experts Exchange thread:

    "I have production server 'A' and virtual server 'B'. I took the master, msdb, model, and db1 backups from server 'A' and restored them to server 'B'. I then ran the sp_drop server 'a' and then sp_addserver 'B' on server 'B'. The maint plans were created on server 'A' to use local connection, however when they are on server 'B' SQL Agent, the maint plan jobs execute against server 'A'."

    Isn't all these three scenarios related to how the connection is stored in the Maintenance Plans? It seems like it is the orginal sql server name that is stored and then when you do things like restoring system databases on another server, renaming the server or cloning the server, things doesn work as expected.

    This is just my analysis. It could be that there is something that I don't understand.

    Ola Hallengren

    http://ola.hallengren.com

  • Brian Brown-204626

    Hall of Fame

    Points: 3211

    Sorry I have not replied to anyone, I was out of the office last week. I agree that all of these issues sound like mine. However, if I use the SMS Maintenace plan screen to try and change the connection, it will allow me to add a connection, but not to delete nor change the original connection. Also the only way I know to view the maintenance plan data is to use the Dynamic View sysmaintplan_plans. I assume this means the maintenance plan data is stored in the resource database, and therefore cannot be updated.

  • Ola Hallengren

    Hall of Fame

    Points: 3960

    My understanding is that the SQL Server 2005 Maintenance Plans are stored as SSIS packages in the system table msdb.sysdtspackages90.

    I have a set of maintenance stored procedures that you could use if you like.

    http://ola.hallengren.com

    Ola Hallengren

    http://ola.hallengren.com

  • Brian Brown-204626

    Hall of Fame

    Points: 3211

    Yes, there is one row for each Maintenance Plan, however the packagedata column is in a hex format.

  • Ola Hallengren

    Hall of Fame

    Points: 3960

    It is stored as a SSIS (SQL Server Integration Services) package.

    I think that you have to use SQL Server Business Intelligence Development Studio to change it. This is however not my field, so I will not be able to help you with this.

    Ola Hallengren

    http://ola.hallengren.com

  • Brian Brown-204626

    Hall of Fame

    Points: 3211

    Unfotunately, we do not have BI installed. For tht matter, we do not have SSIS installed. The only person who uses DTS\SSIS to transfer data on a regular basis is using Analysis Services and he is not a major application.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720983

    Not sure if this is still true, but once I had edited maintenance plans in pre-SP2 servers with SSIS, I couldn't open them in the maintenance plan designed and had to use SSIS for all changes.

  • Key DBA

    SSCertifiable

    Points: 6029

    Brian,

    I didn't see any specific versions of SQL Server mentioned. What version are your Production and DR servers running for SQL Server 2005? SQL Server 2005 Service Pack 2 (9.00.3042) seems to be the minimum standard for patching SQL Server 2005 systems. Also note that if you are remotely connecting to a SQL Server Instance, you need to also patch the client machine. (This reared it's ugly head in my new environment).

    Hope This Helps,

    "Key"
    MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

  • Brian Brown-204626

    Hall of Fame

    Points: 3211

    We are on SQL Server 2005 SP2 Cumulative Update Package 3

  • Mike King-194037

    Grasshopper

    Points: 22

    We have the exact same issue. There does not seem to be a way to resolve the maintenance plan

    running against the 'source' server and scripting the maintenance plan out does not seem to be

    viable.

  • Mike King-194037

    Grasshopper

    Points: 22

    We did find an excellent posting with a tsql script that may script out the maintenance plans.

    We are modifying it and going to try to 'script in' the maintenance plans instead of restoring msdb.

    http://social.msdn.microsoft.com/forums/en-US/sqldisasterrecovery/thread/466caa44-65fe-4118-a36c-972d2a8d4d16/

  • Thiago Santos Leite

    Newbie

    Points: 9

    Tip:

    You can create a alias on "sqlserver configuration manager" tool that point to old server name 😉

    regards,

    Thiago

    thiago.leite@ilegra.com

Viewing 13 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply