June 25, 2006 at 9:48 am
Our main SQL 2000 server is AISSQL1. We have about 100 different computers with their ODBC connection pointing to that server. I have a new, more powerful server that I need to build, test, then copy the live databases and make this new server the live server, AISSQL1. I need both servers active for a period of time. I would like to build it as AISSQL2, then when I want to make the cutover and make it live, either have all ODBC connections suddenly point to AISSQL2, without having to scramble and change them all manually at that time, or find a reliable way to rename the server from AISSQL2 to AISSQL1 and everyone suddenly points to the new server.
I have been told that building the server as AISSQL2 and then changing the name to AISSQL1 is not really recommended, as there is a lot that needs to be changed. I would like to leave it AISSQL2, but it is not practical to try to hit all 100 or so computers (in 3 different locations) at the same time to change their ODBC to point to AISSQL2.
It has been suggested that possibly there is a way to make an ODBC point to a file to get the name of the server? If so, that would be great, as we could change all ODBC's over a period of time to have them all point to the file, and the file points to AISSQL1. The day we cut over, we just change the file to AISSQL2 and suddenyl everyone is looking at the new server. It can all be set up in advance, which is what I am looking for.
If this can be done, I don't know how to do it and would appreciate any guidance or recommendations on trying to accomplish my overall goal. Thank you!
June 28, 2006 at 8:00 am
This was removed by the editor as SPAM
June 28, 2006 at 12:46 pm
Depending on what kind of clients you have you may need to do this either in Wins or DNS or most likely BOTH. Why not setup an alias that points to the production data? Example if you have a 10.10.10.0/24 network why not Make an DNS/Wins entry called productionodbc and give it the IP of AISSQL1. when you are ready to make the switch just change the IP to the IP of AISSQL2 Ten just wait for replication to do it's bit and you shoudl be all set.
June 28, 2006 at 2:00 pm
I agree with the previous post on using an alias to avoid this problem in the future. For example we have a DNS alias called SQL1 however this points to EQXSQL01. We can therefore swap out EQXSQL01 just by changing the DNS alias. The client machines will never need to be touched. We do a similar thing for DR where we will change SQL1 to point ot EQXSQLDR01.
However changing a servername is not really a big deal. We do this alot when we upgrade hardware.
The steps are as follows:
1. change old server to temporary name and ip
2. rename new server to old servers name and ip
3. Run this script on the new server to change the name in SQL:
sp_dropserver 'old_name'
go
sp_addserver 'new_name', 'local'
go
4. update the originating_server column in msdb.sysjobs
June 28, 2006 at 2:01 pm
Luke - thanks, let me play with this one, it sounds like it is heading in the right direction.
June 28, 2006 at 2:02 pm
Elliot - thanks to you as well, sounds like this is what I am looking for. Thanks to both you and Luke!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply