Solved

Duplicate Default Instances of SQL on same network

Posted on 2007-04-03
5
356 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
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now