renaming a SQL instance

[Homerboy]

Not really sure this is "programming" per se, but there seems to be a lot of knowledge in this subforum about SQL, so figured I'd give it a shot.

I have an old server named: SQL
I have a new server named : SQL2

I have mountains of reports that query SQL.
I need to migrate the data from SQL to SQL2 (not a problem),

Once migration is done, I need to "rename" SQL2 to SQL rather than update the mountains of reports themselves.

Upon reading through some google links, it seems you can't do a rename, short of re-installing.

However, it seems that aliases may allow me to reroute the traffic properly.

from what I can tell, I've pretty much followed the directions here:
http://www.mssqltips.com/sqlservertip/1620/how-to-setup-and-use-a-sql-server-alias/
to the letter, but can not connect the new alias "SQLTEST" (the alias I was attempting to use)

Any help?
I've tried the IP address (192.168.1.7), I've tried localhost in the alias.... nothing works.

Thanks in advance.

 

[KLin]

If it's just server names then you're not really dealing with sql instance names.

http://msdn.microsoft.com/en-us/library/ms143799.aspx

 

[Homerboy]

If it's just server names then you're not really dealing with sql instance names.

http://msdn.microsoft.com/en-us/library/ms143799.aspx

Yeah. As I think about it, it's the instance names, of which this alias "trick" won't really work. Connection strings, SSMS logins etc look for instance names, not computer names.

So a reinstall of the SQL instance is the only way.

But then I guess I get confused as to how I get the data from old to new, since they both can't exist with the same name at the same time...

 

[KLin]

So SQL and SQL2 are actual instance names on the same server? you connect by using <servername>\SQL as the name of the instance?

 

[Homerboy]

I followed the directions in your link above using:

sp_dropserver 'SQL2'
GO
sp_addserver 'SQLTEST', local
GO

I still only see SQL2 when attempting to connect with SSMS (as would any existing query in reports and applications).

When I connect to SQL via SSMS and run the query: "sp_helpserver" I get the following:

name	network_name	status	id	collation_name	connect_timeout	query_timeout
SQLTEST	SQLTEST                       	rpc,rpc out,use remote collation	0	NULL	0	0

Not sure what's going on here....

 

[Homerboy]

So SQL and SQL2 are actual instance names on the same server? you connect by using <servername>\SQL as the name of the instance?

(still learning here if you haven't noticed... )

Let's take connecting via SSMS for example...
In the connection window that pops up, it would be:

Server Type: Database Engine
Server Name: SQL2
Authentication: Windows Authentication
etc... etc...

So I guess that's not an instance in this situation...


* these are two PHYSICALLY DIVERSE servers.
'SQL' is on a server named "EMAIl2-OLD' (do not get me started)
'SQL2' is an a server named "SQL2"

 

[WannaFly]

It sounds like you are confusing INSTANCE names with HOST names. One HOST can have multiple INSTANCES, and you specify the instance name when it is installed.

If you used the default instance name, you dont have to specify it when connecting and can just connect using the HOST name, if you specified an INSTANCE name, you must specify it when connecting.

select @@servername + '\' + @@servicename
will provide you the host and instance name. "MSSQLSERVER" is the default instance name.

EDIT: Changing the hostname and rebooting will work, changing an instance name requires a reinstall, i believe.

 

[Homerboy]

You are correct.
Old server -- EMAIL2-OLD\MSSQLSERVER
New server -- SQLTEST\MSSQLSERVER

So... Apparently I changed the host name on the new server from SQL2 to SQLTEST.

How is it then I am still able to connect to "SQL2" (the new server) via SMSS?
Once I migrate everything over, I want the NEW server to become "SQL" and the old server to become "SQLOLD" so all my reports and queries automatically are directed to the NEW server.

 

[Homerboy]

SSMS and my connection strings are literally connecting to the DNS name of "SQL" and/or "SQL2" are they?

sql.company.local and sql2.company.local are the internal DNS for the 2 servers.

 

[KLin]

When you say queries, are you talking about stored procs and views?

 

[Homerboy]

When you say queries, are you talking about stored procs and views?

Not exactly.
I have some (many actually) queries built into Excel sheets/reports, VB programs etc etc, that the connection string points towards 'SQL'

DRIVER=SQL Server;SERVER=SQL;UID=XXXX;PWD=YYYY;APP=2007 Microsoft Office system

or

Cn.ConnectionString = "Data Source=SQL;Initial Catalog=CLS;uid=XXXX;Pwd=YYYY"


I don't want to have to go into all these programs, reports and queries and change "SQL" to "SQL2"

If it's just, literally a DNS name, then obviously that can be easily flip-flopped from a network standpoint. but I assumed that it was the SQL instance name (I've now learned it is not an "instance") hence the thread title.

 

[KLin]

Nope, it's just the servername and making sure it resolves to wherever you want it to resolve via DNS. So to make it point to the new server, update the dns entry for SQL to point to the ip address of the new server.

 

[Homerboy]

Nope, it's just the servername and making sure it resolves to wherever you want it to resolve via DNS. So to make it point to the new server, update the dns entry for SQL to point to the ip address of the new server.

Ah hell. I made it way harder than I had to I guess.
Over thinking a simple solution.
Thanks for bearing with me.