mwrye
asked on
Duplicate Default Instances of SQL on same network
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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.
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.
ASKER
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
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
>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