Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Duplicate Default Instances of SQL on same network

Posted on 2007-04-03
5
Medium Priority
?
372 Views
Last Modified: 2012-08-13
Creating a SQL Server with the same default instance on the same network.

We are using VMWare on an ESX SAN.  I have a Server running SQL 2005 on Windows 2003 R2.  I want to make a clone of this Server.  The physical Server name will of course be different as the original.  However, cloning will also clone my SQL Server with the specified default instance as the original Server name.  What kind of problems will this create.  I know you shouldn't have 2 SQL Servers with the same default instance name.  I plan to un-install and re-install SQL so that I can give it another name.  From past experience with SQL 2005 even the un-install and re-install will not get rid of everything linked to the default instance name.  Before I had to do some registry hacking to rename the default instance in some places.  

Thanks for any help you can give on this topic.  The second machine is to be a test server to the original production server.
0
Comment
Question by:mwrye
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 16

Accepted Solution

by:
rboyd56 earned 2000 total points
ID: 18844129
After cloning the machine all you should have to do as far as SQL Server is concerned is run the following commands in management studio:

sp_dropserver <old server name>
go
sp_addserver <new server name>, local

That will change the internal SQL server name. This should sufice and is the same thing you would do if you restored the master database from an instance of SQL Server to a new machine.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18844144
no problems.
>I know you shouldn't have 2 SQL Servers with the same default instance name
where did you learn that?
note that on 1 server, you CANNOT have 2 instances with the same name (see below)

the name of the instance is either ...
for a default instance:
SERVERNAME

for a named instance:
SERVERNAME\INSTANCENAME

hence, no confusion possible

0
 

Author Comment

by:mwrye
ID: 18844954
rboyd56:  That sounds like what I was looking for.  My only concern with dropping the Server name is that the Server does exist and I want to keep that one production server that.  Would I need to specify the IP to the server that I do want to drop and rename?

Example:  CBF2K14 is the Server Name (and therefore my default instance name).  Each will have their own IP.  And the clone would be named something like "Clone".  

AngelII:  Yes, the name of the Server is the Default instance.  But when we renamed a VMWare Server the SQL Server's default instance remained the old server name.  Also, when we tried once to Clone a SQL server with VMWare I wound up with registry keys that still read the Old Default Instance name (the name of the Original Server).  SQL then still kept trying to read it as the original server until I changed all the registry keys to define the Default Instance registered server as the new name.


0
 
LVL 16

Expert Comment

by:rboyd56
ID: 18845635
The change that I suggested will only affect the SQL Server that you are connected to when you run the commands. It will not affect the other server at all. So to answer your question, you cannot specify the ip address, you have to specify the SQL Server name.

so you woud run these command in Management Studio while connect to the clone instance:

sp_dropserver CBF2K14
go
sp_addserver Clone, local

Not sure about the registry change. The default instance of SQL Server is MSSQLSERVER for all machines. The machine name is not referenced anywhere. Now this may be different on a VM machine but on a hardware based server, the actual machine name is not referenced, as far as SQL Server is concerned.
0
 

Author Comment

by:mwrye
ID: 18846279
Looking at all the notes I had from trying this before (and on the rename of the machine) I see that it was actually the Report Services, VSS Writer, Native Client, and a few of the subsidiary serives that still have the name in them.  The Actual default instance is saying MSSQLServer.  

There were also some keys for SQL Server under the profile (Server Name) which was still referencing the old server name.
Either way your solution should work.

Thanks
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question