July 15, 2008 at 12:49 pm
Our DR servers are kept in a cold state, SQL Server installed but no user databases, no jobs, etc. For disaster recovery on SQL Server 2000, we would restore all databases, except temp and model. After making a few small adjustments(ie. running sp_dropserver, sp_addserver to change the server name to the recovered server, etc.), we could use the DR server with no problems.
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. We found we could not delete or modify the 'local' connection and had to delete and recreate the maintenance plans to resolve this problem. We are afraid we will have this same problem if we restore the system databases on another DR server.
Can we change the 'local' connection in the maintenance plan or must we drop and recreate them anytime we need to DR a server?
Does anyone know where the maintenance plans are stored? Are they in a system database?
Can we update a column with a new server name to change the connection?
July 15, 2008 at 2:20 pm
The maintenance plans in 2005 are stored as SSIS packages, and I think the connections are buried in there. They should be in msdb, so you can get them there.
I'd think (local) works, but it might be resolved at design time and saved. You could build packages as 127.0.0.1 instead (loopback), but you'd have to watch ports.
July 15, 2008 at 2:39 pm
We are moving maintenance plans to a DR server with a restore and I need to change the (local) connection to the new server. The maintenance plan screens in SMS won't let me change or delete the (local) connection. I found the view(sysmaintplan_plans) that lets me see a maintenance plan, but I cannot find the tables where the data is stored.
If the maintenance plans are stored in the msdb database, then restoring msdb to a DR server will result in a connection pointed back to the original server. This means our choices are to precreate maintenance plans on all our 2005 DR servers and leave them, or to delete and recreate all maintenance plans after the restore of the msdb database.
July 15, 2008 at 2:45 pm
Question: did yoyu specify the @local = 'local' while using the sp_addserver.
Maninder
www.dbanation.com
July 15, 2008 at 2:50 pm
yes, but addserver does not affect the connect set up in the maintenance plans.
July 15, 2008 at 4:50 pm
Two options that I know of:
1) Export the maintenance plan to a file, create a new SSIS project in BIDS - add the maintenance plan to the project, modify the connection information and save the maintenance plan to the new server. Note: you have to open the maintenance plan on the new server and verify/validate all of the sub-plans and make sure everything was loaded correctly. This does not work all the time.
2) Modify the connection information in the Agent job and override the original connection information. Note: if anyone modifies the maintenance plan in SSMS - the agent job is recreated and the connection information would have to be modified again.
One of the things you can do is modify the connection information (option 1 above) and set the connection to LocalHost instead of a specific server. That should work and allow you to restore the msdb database without having to modify anything.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 16, 2008 at 9:01 am
1) We do not have SSIS installed, therefore we cannot export a package.
2) When a maintenance plan is created with the SMS wizard, the connection is created as 'local server connection' with the server that the maintenance plan was created on. This cannot be changed or deleted. In the SQL Agent job, the datasource for the package execution is set to the server that is in the local server connection. This too cannot be changed.
July 16, 2008 at 9:14 am
Very strange. I'll try to test this a little later if I have time, but I wonder what happened. All I can think is that the package got resolved to the original server name. I can't imagine IP, but it's possible.
If you changed the name of the DR server to the original, I'm really stumped as to how the plans are running on the original server.
July 16, 2008 at 1:55 pm
Brian Brown (7/16/2008)
1) We do not have SSIS installed, therefore we cannot export a package.
You don't need SSIS installed, but you do need the client tools. If you have installed the client tools then you have BIDS (SQL Server Business Intelligence Development Studio). Create an SSIS package using that tool, follow the directions above and you can change the connection information.
2) When a maintenance plan is created with the SMS wizard, the connection is created as 'local server connection' with the server that the maintenance plan was created on. This cannot be changed or deleted. In the SQL Agent job, the datasource for the package execution is set to the server that is in the local server connection. This too cannot be changed.
To change the connection in the SQL Agent job, you have to check the box for that connection manager. Edit the step for the job, select the data sources tab, check the box for the connection manager and you can now change the connection information.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 16, 2008 at 2:12 pm
This was one of the reasons I gave up on using SSIS for maintenance plans and rolled my own using basic T-SQL. I found SSIS just added an unnecessary level of complexity to what should be a simple solution (and by complexity I don't mean using SSMS to setup the plans - that's easy).
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
July 16, 2008 at 2:23 pm
Steve Jones - Editor (7/16/2008)
If you changed the name of the DR server to the original, I'm really stumped as to how the plans are running on the original server.
We don't change the DR server name, after restoring the master database we use the dropserver and addserver to change the network name in the master database to the DR server name.
July 17, 2008 at 3:48 pm
I read about a similar problem on another forum recently.
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23452193.html
In case you are thinking about using a T-SQL solution, I have a set of maintenance stored procedures that you are welcome to use.
Ola Hallengren
July 18, 2008 at 9:05 am
Ola Hallengren (7/17/2008)
I read about a similar problem on another forum recently.http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23452193.html
Unfortunately, I am not a Premium Service Member on the expert-exchange site. Therefore, I cannot see the responses to this issue.
July 25, 2008 at 9:10 am
Here's a short summary. The person in this thread is describing an issue that is very similar to yours. I think that it is the same issue. He has been in dialog with Microsoft and got a confirmation that it's a bug. The bug number is 642195.
Ola Hallengren
July 27, 2008 at 6:50 am
I think that the issue that you're experiencing is also the same issue that was reported on Microsoft Connect recently.
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=357953
Ola Hallengren
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply