Creating new server like production

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Creating new server like production

Author

Topic

CanadaDBA

583 Posts

Posted - 2004-10-19 : 10:00:30

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’ properties I have my production’s 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’s your idea about my approach to implement this?

Canada DBA

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-10-19 : 10:38:13

You could try to use a dts package to export the production databases to the new server. I'm pretty sure it will transfer the jobs.

Dustin Michaels

CanadaDBA

583 Posts

Posted - 2004-10-19 : 12:28:48

I haven't done this but I will try. Do you have any idea about how to do it?

quote:
Originally posted by DustinMichaels

You could try to use a dts package to export the production databases to the new server. I'm pretty sure it will transfer the jobs.

Dustin Michaels

Canada DBA

MuadDBA

628 Posts

Posted - 2004-10-19 : 13:03:01

or try just scripting the jobs on the production server and then running the SQL Script through QA.

CanadaDBA

583 Posts

Posted - 2004-10-19 : 15:57:21

I generated the job script but I got error message when I ran it on destination server. It couldn't drop the old job on the destination server. I tried to delete the 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 crazyjoe

or try just scripting the jobs on the production server and then running the SQL Script through QA.

Canada DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-19 : 16:17:27

If the servers are identical, then you can just backup the msdb database on the old server and restore it on the new server. This will give you MSX server configured, jobs, DTS packages, and everything else. You also could have done this with master. I've done this a few times now on identical systems. If drive letters are different, then you can't do this.

http://support.microsoft.com/default.aspx?scid=kb;en-us;224071&Product=sql2k

Tara

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-20 : 04:05:30

quote:
Originally posted by farhadr

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’ properties I have my production’s 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’s your idea about my approach to implement this?

Canada DBA

you need to change the servername, if both machines are on the same domain, you'll get into a lot of trouble with dns resolution.

on QA:
1. check for servername, this should match your machine name (network name)
-- select @@servername
2. if not the same
--sp_dropserver
--sp_addserver

if they're not in the same domain, meaning standalone, just check the machine name and servername, they should match the production server.

--------------------
keeping it simple...

CanadaDBA

583 Posts

Posted - 2004-10-20 : 10:40:47

Yes, The second server is identically partitioned. I will try to RESTORE an MSDB backup on new server. Do you think this will change the references to old server?

Regarding restoring Master, I haven't done before. Is it possible to restore it?

quote:
Originally posted by tduggan

If the servers are identical, then you can just backup the msdb database on the old server and restore it on the new server. This will give you MSX server configured, jobs, DTS packages, and everything else. You also could have done this with master. I've done this a few times now on identical systems. If drive letters are different, then you can't do this.

http://support.microsoft.com/default.aspx?scid=kb;en-us;224071&Product=sql2k

Tara

Canada DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-20 : 12:59:11

Did you look at the link that I posted?

Tara

CanadaDBA

583 Posts

Posted - 2004-10-20 : 13:09:00

Yes, actually I used that web page to do SQL relocation.

quote:
Originally posted by tduggan

Did you look at the link that I posted?

Tara

Canada DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-20 : 14:43:20

Then I'm confused by your question:

quote:

Regarding restoring Master, I haven't done before. Is it possible to restore it?

Instructions on how to do this are located in that link that I posted.

Tara

CanadaDBA

583 Posts

Posted - 2004-10-26 : 14:10:51

Tara,

I restored the MSDB but still the problem exists and I get the same MSX error.

In the link that you posted, is talking about detach and re-attach new MASTER. This is what I did and using this procedure I have moved the DBs. The problem is that the new server's name is not same as the production one. Therefore, there are the production server's name in the system DBs. I thought by restoring MSDB, it would automatically be set with new server info. But seems it's not.

Canada DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-26 : 14:14:32

I'm so confused. The link that I posted has information about restoring/moving/attaching master and msdb databases. Which database(s) did you do?

On one system where we had MSX setup and wanted to move to beefier servers, we used that link to move over all of the databases (except tempdb of course). We didn't just restore master or just msdb. We did it for all of them and it worked like a charm.

Tara

CanadaDBA

583 Posts

Posted - 2004-10-26 : 14:54:30

What is MSX? Probably it is my problem. I know that network people have installed something and I see a BKUPEXEC server registered under SQL Server Group in new machine. Is it related?

quote:
Originally posted by tduggan

...
On one system where we had MSX setup and wanted to move to beefier servers, we used that link to move over all of the databases (except tempdb of course). We didn't just restore master or just msdb. We did it for all of them and it worked like a charm.

Tara

Canada DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-26 : 14:56:33

MSX is multiserver administration. It is a SQL Server thing.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_m_m_8pic.asp

Tara

CanadaDBA

583 Posts

Posted - 2004-10-26 : 14:57:29

I have moved all user DBs and system DBs (Master, msdb, model). Everything was smooth and well done but as I wrote before, production's server name is in some places in Master or MSDB (like originating_server field).

quote:
Originally posted by tduggan

I'm so confused. The link that I posted has information about restoring/moving/attaching master and msdb databases. Which database(s) did you do?

...
Tara

Canada DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-26 : 14:59:36

So after the move of the databases, did you tell SQL Server about the rename?

From BOL:



Renaming a Server
When you change the name of the computer that is running Microsoft® SQL Server™ 2000, the new name is recognized during SQL Server startup. You do not have to run Setup again to reset the computer name. 

You can connect to SQL Server using the new computer name after you have restarted the server. However, to correct the sysservers system table, you should manually run these procedures:

sp_dropserver <old_name>
go
sp_addserver <new_name>
go

Issues with Remote Logins and Replication
If the computer has any remote logins, for example, if it is a replication Publisher or Distributor, sp_dropserver may generate an error similar to this:

Server: Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 44
There are still remote logins for the server 'SERVER1'.

To resolve the error, you may need to drop remote logins for this server. If replication is installed, disable replication on the server before running the sp_dropserver stored procedure.

To disable replication using the SQL Server Enterprise Manager 

Expand a server group, and then expand the Distributor (the server that contains the distribution database).


Right-click the Replication folder, and then click Disable Publishing.


Complete the steps in the Disable Publishing and Distribution Wizard. 
©1988-2000 Microsoft Corporation. All Rights Reserved.

Tara

CanadaDBA

583 Posts

Posted - 2004-10-27 : 09:13:09

I ran the following with my servers name:


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.

Canada DBA

CanadaDBA

583 Posts

Posted - 2004-10-27 : 09:22:39

I restarted the server and the result is as previous posting.

Canada DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-27 : 12:40:38

http://support.microsoft.com/default.aspx?kbid=281642

Tara

Kevster
Starting Member

1 Post

Posted - 2004-11-04 : 07:30:20

I've encountered this problem before. I simply cheated and directly modified the 'originating_server' column in sysjobs (msdb) to reflect the new server name.

Note : Not the recommened way, but it requires no job re-scripting.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources