One of my clients uses MS SQL Server 2000 Enterprise, and a few nights ago we migrated their existing databases to a newly built DB server. There were the usual gotchas that you can’t test for unless you have a duplicate of your production environment, but what can you do when you don’t have an unlimited budget?
There was an unexpected gotcha that eventually brought a sneer to my lips, and once again convinced me that I’ll take my on-the-job experiences over a MicroSoft Certification any day of the week.
I discovered, when I went to change the schedule on a backup procedure, that MS SQL Server’s Enterprise Manager won’t let you change a SQL Server Agent job if it was created on a different database server. Which means, it freezes you out of changing jobs migrated to a new server as well. (It will continue to run the Server Agent jobs… Go figure.) The specific error it gives is:
Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server. The job was not saved.
A quick search with Google yields the official Microsoft support page for this issue: http://support.microsoft.com/kb/281642
It’s not a bad article, per se. It explains the cause, and it gives a fix; but, in typical Microsoft style the fix is overly complicated:
WORKAROUND
The best way to handle this problem after the rename process is to follow these steps:
1. Rename the server back to the original name.
2. Script out all of the jobs and then delete them.
3. Rename the server to the new name.
4. Add back the jobs by running the script generated from step 2.
For additional information, see the “Multiserver Administration” article in SQL Server Books Online.
Personally, the person who wrote “best way to handle this problem” doesn’t understand MS SQL server, and has very little understanding of how the system works.
Delete and re-create jobs? This is the best they could come up with?
Here is my solution, free of charge:
SOLUTION
The best way to handle this problem after the rename process is to follow these steps:
1) From Query Analyzer, logged in as ‘sa’ or ‘administrator’, issue this command:
UPDATE msdb..sysjobs SET originating_server='[NEW NAME]’
You tell me. Which solution is more elegant?
-Chris
[ad#adsense-horizontal]