Solved

Duplicate Default Instances of SQL on same network

Posted on 2007-04-03
5
362 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
  • 2
  • 2
5 Comments
 
LVL 16

Accepted Solution

by:
rboyd56 earned 500 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Date from a string 4 70
Delete from table 6 48
SQL Query with Sum and Detail rows 2 56
Sql Server group by 10 45
There are some very powerful Data 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 discu…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

820 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