Creating new server like production | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Creating new server like production

I am creating another server exactly like our production. I did the following:
  • Stopped the production server, copied all DBs including master, msdb, model and then restarted the server.
  • Installed SQL-Server on new server and used the same path for system DBs as my production.
  • Stopped new server and copied all DBs from production to the same path in new server.
  • Started the server.
The new server seems exactly like my production server but I noticed that in ‘jobs#%92 properties I have my production#%92s server name not the new one. Therefore, the jobs cannot be run. How can I fix this? Do you think there may be some other parts having problem? What#%92s your idea about my approach to implement this?
CanadaDBA
Look in the MSDB database at a table called sysjobs. Update the OrginatingServer column to your new server name. Everything should then be fine. To update the table you may need to go into server properties and check the box that says Allow Updates to System Tables (or something similar). Providing all your paths are the same this is a veryu quick/effective way of doing it. The only other option is to backup databases and restore them, or possibly just attach the user databases. restoring teh master database to get your settings back is a real pain though – I avoid it. If it’s working as it is then don’t worry. You may want to run Select @@Servername. Check it returns the correct name. If not run: sp_addserver @server = ‘[servername]’, @local = ‘local’ Yes the @local = ‘local’ should be exactly as written, a bizarre bit of code I know! Simon
Have you restored the MSDB from the source server to destination server? You can generate scripts for all jobs, alerts and operators on the source server, and then run the script on the destination server. To script jobs, alerts and operators: 1. Open the SQL Server Enterprise Manager, and then expand the Management folder.
2. Expand SQL Server Agent, and then either right-click Alerts, Jobs, or Operators.
3. Click All Tasks, and then click Generate SQL Script. For SQL Server 7.0, click Script All Jobs, or Alerts, or Operators.
You will have the option to generate scripts for All Alerts, All Jobs or All Operators based on the item you right-click. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Thanks Simon;
I had a problem when adding the server because SQL-server knew the production name as "local". Using sp_dropserver, I removed that one and added the new server. Then the jobs were fine and working but whenever I right click on SQLSERVER Agent, after a few seconds the EM is closed. I added the production server’s name but the problem exists. I am trying to redo my work from scratch. What was wrong?
quote:Originally posted by simondm
sp_addserver @server = ‘[servername]’, @local = ‘local’

Simon

CanadaDBA
Hi Satya,
As you see, I have copied all system DBs including MSDB. I had a problem with adding the destination server’s name.
I am going to generate the scripts for Alerts, Operators and jobs from production. Also, I am going to replace existing DBs and system DBs in destination server with production ones. Then I will run the scripts agianst the destination server. The scripts will delete existing Alerts, Operators, and jobs and recreate them. Am I right?
Thanks,
quote:Originally posted by satya Have you restored the MSDB from the source server to destination server?
Satya SKJ

CanadaDBA
Firstly have you installed Service Pack 3a – EM crashes are usually due to unpatched versions. Next, EM automatically adds your server as local – don’t worry about this it’s not a problem. Connect to your server using Query Analyser. Then run SELECT @@ServerName, if the name returned is you server name don’t worry everything is ok. If it’s the old server’s name then run sp_addserver ‘yournewservername’, @local=’local’. You also need to restart SQL after running this proc. Hopefully the crash is because you haven’t installed the service pack. If not, it’s something I haven’t seen before. Someone else may know of a reason….
Yes, I have installed the SP3a. My SQL-Server version is 8.00.818. I ran SELECT @@ServerName and it returned Production’s name. I tried to run sp_addserver ‘yournewservername’, @local=’local’ but it said that there is already existing a server name as local. That’s why I had to drop the server’s name and…
quote:Originally posted by simondm Firstly have you installed Service Pack 3a – EM crashes are usually due to unpatched versions. Next, EM automatically adds your server as local – don’t worry about this it’s not a problem. Connect to your server using Query Analyser. Then run SELECT @@ServerName, if the name returned is you server name don’t worry everything is ok. If it’s the old server’s name then run sp_addserver ‘yournewservername’, @local=’local’. You also need to restart SQL after running this proc. Hopefully the crash is because you haven’t installed the service pack. If not, it’s something I haven’t seen before. Someone else may know of a reason….

CanadaDBA
I generated the job script but I got error message when I ran it. It couldn’t drop the previous job. I tried to delete the previous jobs directly from within EM but got the following: Cannot add, update, or delete a job (or its step or schedules) that originated from an MSX server.
quote:Originally posted by satya …You will have the option to generate scripts for All Alerts, All Jobs or All Operators based on the item you right-click. Satya SKJ
Moderator

CanadaDBA
This issue is caused because SQL Server 2000 supports multi-instances, the originating_server field contains the instance name in the format ‘serverinstance’. Even for the default instance of the server, the actual server name is used instead of ‘(local)’.
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. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
I ran the following with my servers name and then restart the server.
sp_dropserver <old_name>
go
sp_addserver <new_name>
go
SELECT @@ServerName returned the old_name before I run the above commands but it returns null after that. Also I have the old server’s name in job properties. And if try to change anything in any job, I get the error: Cannot add, update, or delete a job (or its step or schedules) that originated from an MSX server.
I compaired the result of SELECT * FROM master.dbo.SysServers for both old and new servers. The differences are in srvid and srvname and datasource. srvname and datasource show my new server name and srvid has the value 2 while in old one it has 0.
CanadaDBA
Try to add the server using LOCAL variable and then follow the procedure defined above. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>