SSIS problems with cloned machine

Question

Post reply

SSIS problems with cloned machine

mwalbert

Old Hand

Points: 335
More actions
February 3, 2010 at 9:59 am

#138572

I have an interesting (see: frustrating) problem and was wondering if anyone had any ideas on how to straighten it out without having to reinstall SQL.
My coworkers received a request for 2 VMs (we'll call them CloneA and CloneB) that were setup exactly like another server (call it ParentServer). ParentServer is W2K3 EE, and SQL 2K5 EE. So, instead of building them from scratch, they cloned ParentServer. Sounds all well and fine, except for one problem. Whenever I try to run a maintenance plan or any SQL job for that matter, they fail, stating "Description: Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection." Examination shows that the local server connection referenced in the maint plans is ParentServer rather than CloneA or CloneB. I tried adding a new connection to the maint plan, but the job still fails. I am unable to change the values for Local server connection in the maintenance plan.
Anyone have a clue on how i can get the jobs to point to CloneA and CloneB and run successfully?
----

Viewing 5 posts - 1 through 4 (of 4 total)

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

DNA_DBA SSCrazy Eights Points: 9116 More actions · Answer 1

Sounds like you've renamed the server - you need to check the system tables to see what is configured there.

http://msdn.microsoft.com/en-us/library/ms143799(SQL.90).aspx

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 2

I agree with DNA_DBA. From the sounds of the clone process, serverA and serverB were EXACT copies of the parent server with no consideration to issues this can bring up. Other than the SQL server being named incorrectly it sounds, there could be other isseus if the vms and the parent server are all on the same domain, as they will propably have the same GUID.

Joie Andrew
"Since 1982"

mwalbert Old Hand Points: 335 More actions · Answer 3

mwalbert

Old Hand

Points: 335

February 4, 2010 at 6:39 am

#1113701

worked like a charm. thanks!

----

mwalbert Old Hand Points: 335 More actions · Answer 4

aside from frowning at my coworkers, i did the following to get the servers behaving again:

Running SELECT @@SERVERNAME verified the problem - it returned the old servername.

The solution to this part of the problem was to run the following commands (obtained from the BOL refrenced above):

sp_dropserver (oldname)

GO

sp_addserver (newname), local

GO

I then restarted SQL - afterwards, running SELECT @@SERVERNAME returned the correct (new) name of the server.

I still, however, had no luck deleting the old maintenance plans. After some more digging, I found a workaround. Running this command returned some useful information:

USE msdb;

SELECT * FROM sysmaintplan_plans;

So, for each maintenance plan I wished to delete, I copied the ID returned above, and substituted it into the following three queries. After that, I deleted the associated SQL Agent jobs.

DELETE FROM sysmaintplan_log WHERE plan_id = '<planid>';

DELETE FROM sysmaintplan_subplans WHERE plan_id = '<planid>';

DELETE FROM sysmaintplan_plans WHERE id = '<planid>';

(substituting the plans from above)

I then, for simplicity, just re-created the maintenance plans.

all seems well. of course, it would be been much simpler just for them to not have cloned the darn things in the first place 😀 but, such is life.

----