Solved

Duplicate Default Instances of SQL on same network

Posted on 2007-04-03
5
366 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 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
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…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
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…

617 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