sql server clustering: SQL Server 2000 Virtual Server Renaming -- sql server clustering

Re: SQL Server 2000 Virtual Server Renaming

Allan Hirt
7/28/2004 4:00:43 PM

Let's be clear: to rename you have to uninstall the SQL
Server instance ONLY. That's what I've been saying all
along. You don't have to evict the nodes first; the SQL
Server uninstall process will remove all traces of SQL
Server except any data files you created.

Then you reinstall your virtual server with your new name.

You do not have to uncluster the nodes or re-confugure the
server cluster unless you want to rename that as well.

Keep in mind that you can still only have one default
instance; all others you install will need to be named
instances.
[quoted text, click to view]
to uninstall virtual SQL Server 2000 to rename the Virtual
server name. Sure, it has to be uninstalled. That is the
[quoted text, click to view]
clustered SQL Server 2000 Virtual Server is to uninstall
and reinstall SQL Server 2000 with the new Virtual Server
[quoted text, click to view]
to but you don't have to do). I am referring to the steps
in one of the previous post
[quoted text, click to view]
not. Why do we need to evict all nodes ? which basically
means uninstall Windows Cluster and then reinstall it.
[quoted text, click to view]
and IP, the qs is how do you get the dbs. You can make use
of the dbs (system and user dbs) of SQL Server that
[quoted text, click to view]
can replace the new mdf and ldf files with the old ones. I
have done this many times on standlone SQL Servers
[quoted text, click to view]
recently. The only thing I believe we will need to do is
run sp_dropserver and sp_addserver to have the new server
[quoted text, click to view]
Technology Protection Program and to order your FREE
Security Tool Kit, please visit
[quoted text, click to view]
access update their Microsoft software to better protect
against viruses and security vulnerabilities. The easiest
[quoted text, click to view]

Re: SQL Server 2000 Virtual Server Renaming

Barry Lynch
7/28/2004 7:59:26 PM

Alan
Forgive me for drawing this out but you seem to be strong in this area and
I'd like to get this out of my system and tease it out a bit - I take it
then that you think that the KB article is indeed wrong so we are in
improvisation territory. Here's the scenario in brief...

1. uninstall sql server
2. Evict all nodes (node names, IP Addresses, dsn servers etc remain the
same)
3. Create new cluster with new name and ip
4. Reinstall SQL Server with a new name for the VS with a new IP

This now contains a master database SQLB that is perfectly "synchronised" to
the new cluster

If theoretically we did a field by field compare between SQLA master and
SQLB master where would we find the differences relating to the Cluster

What is the relationship between SQLA master and the registry, can you point
to any particular keys?

Have you ever tried the drop_server, add_server approach? Anecdotally...I
had to do it a maybe 2 years ago on a 2-node when the owners had renamed the
server after they'd installed the cluster - this was breaking replication
because of the inconsistent @@server name value and they were not in a
position to uninstall (manufacturing plant). There were no problems with the
server although I concede the scenario is not the same. My point is that
conventional wisdom is uninstall but it was not necessary
in that case and I'm now trying to understand why a restore master won't
work in this one

I'm not disputing your expertise but I just don't understand where the
issues are

Actually when I have time I'm going to to this in our lab and report back on
my hacking

Regards

Barry Lynch

[quoted text, click to view]

Re: SQL Server 2000 Virtual Server Renaming

uttamkp NO[at]SPAM online.microsoft.com
7/28/2004 9:23:46 PM

I don't know where it was assumed that we will not have to uninstall virtual SQL Server 2000 to rename the Virtual server name. Sure, it has to be uninstalled. That is the
only supported way and that is what is documented in

INF: How to Change a Clustered SQL Server Network Name
http://support.microsoft.com/?kbid=307336

Here it says clearly that " The proper way to rename a clustered SQL Server 2000 Virtual Server is to uninstall and reinstall SQL Server 2000 with the new Virtual Server
name."

You DO NOT have to evict the nodes ( you can if you want to but you don't have to do). I am referring to the steps in one of the previous post

" 1. uninstall sql server
2. Evict all nodes (node names, IP Addresses, dsn servers etc remain the
same)
3. Create new cluster with new name and ip
4. Reinstall SQL Server with a new name for the VS with a new IP".

--- In this steps 1 and 4 are required. Steps 2 and 3 are not. Why do we need to evict all nodes ? which basically means uninstall Windows Cluster and then reinstall it.
Not required at all.

-- After installing SQL Server with a new virtual name and IP, the qs is how do you get the dbs. You can make use of the dbs (system and user dbs) of SQL Server that
you uninstalled. If the paths are the same then YES we can replace the new mdf and ldf files with the old ones. I have done this many times on standlone SQL Servers
and few times on clustered SQL Server but not very recently. The only thing I believe we will need to do is run sp_dropserver and sp_addserver to have the new server
name. I will do some research to see if anything else is required and post the information.

-- Another option is to transfer the logins (a search will give you the KB) and then attach the user dbs.

Uttam Parui
Microsoft Corporation

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest
way to do this is to visit the following websites: http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

Re: SQL Server 2000 Virtual Server Renaming

allspammustdie NO[at]SPAM yahoo.com
7/29/2004 1:47:21 AM

Hi Uttam et al
I think we are more or less of agreement but I just need to clarify a
litle ona number of fronts

Front1: KBID 307336

1. Quote from KBID: 307336

"The proper way to rename a clustered SQL Server 2000 Virtual Server
is to uninstall and reinstall SQL Server 2000 with the new Virtual
Server name.

NOTE: Before uninstalling SQL Server 2000, make sure that you have
backed up the databases, including the master database. You can then
restore them after SQL Server has been reinstalled."

So the quote "you can then restore them" referring I take it to the
system databases including Master.

Objections
Conventional wisdom including a number of posts on this thread say
that restoring the master will not work because the cluster name and
or the SQL VS name has changed

Specific Question
Is the KB article correct?

Front 2. Evicting Nodes
I know I don't have to evict the nodes. The reason I put forward this
scenario is to form a framework for discussion of the issues of the
Registry and restoring the master - the pointof putting this in the
scenario was indeed as you say to effectively remove windows
clustering and reinstall so as to try and remove as many legacy
registry settings as spossible

Specific Question
What are these registry settings which effect the Master database?

Front 3. Sp_DropServer, Sp_AddServer
I aggree with you in that personally I think this would work and as I
mentioned in a previous post I have done it before on a cluster with
no ill effects
What I was hoping to achieve to with the initial posting in this
thread was to tease out any other issues

Specific Questions
Is dropserver, addserver enough to put SQLA master back in synch with
SQLB?
If not, what else is required

BTW I think this is an interesting thread and if I have offended with
any of my posts to date please forgive as its not intended - the devil
is in the detail no?

[quoted text, click to view]

Re: SQL Server 2000 Virtual Server Renaming

allspammustdie NO[at]SPAM yahoo.com
7/29/2004 1:58:47 AM

Sorry one other thing re KBID
If it is right but what is subsequently required is to dropserver,
addserver in order to resynch the server name then it sould be in the
KB article no?
After all just restoring the master with no further actions will cause
problems
so while it meets the requirements of it titles its note is not
complete

B

[quoted text, click to view]

Re: SQL Server 2000 Virtual Server Renaming

uttamkp NO[at]SPAM online.microsoft.com
7/30/2004 12:54:24 AM

Barry,

I feel the KB is correct as it was written to give steps to rename the virtual sql server name and the steps for that is correctly documented in the KB. But the additional info in the KB i.e. the note on how to restore the dbs
--- has a step missing. I will send a comment to the right team and ask them to add sp_dropserver and sp_addserver to the KB.

I did some more research and did not find any other step that needs to be done other than running sp_dropserver and sp_addserver (this one would also need to do for a standalone sql, right ?). Some other options
for moving databases is documented in KB 314546 (though I would restore the master db and run sp_dropserver and sp_addserver)

Best of luck on the renaming.

Best Regards,

Uttam Parui
Microsoft Corporation

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.

Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

Re: SQL Server 2000 Virtual Server Renaming

allspammustdie NO[at]SPAM yahoo.com
7/30/2004 1:42:35 AM

Hi Uttam et al
Uttam, well done for putting the work in. We are agreed then!!
Another clustering old wives tale finally put to rest maybe.

I am going to do this for real in about an hour so I will report back.
Many thanks

Barry Lynch

[quoted text, click to view]

Re: OK then - the outcome

allspammustdie NO[at]SPAM yahoo.com
7/30/2004 2:58:45 AM

Hi All

Ok then I reinstalled SQL Server yesterday evening and decided to go
for it this morning.
In the end I didn't even restore master I just stopped sql Server
service renamed the SQLB Data folder to xData and copied in the SQLA
data folder containing all the original systems databases and log
files from the SQLA install which I had copied before uninstalling
(just need to stop SQL first)

I then restarted the SQL server service and did a select @@Servername
It returned, as expected SQLA
then it was...
sp_dropserver 'SQLA'
sp_addserver 'SQLB', local
Stop the SQL service
Restart SQL Service
select @@servername now equals 'SQLB' as planned

Failed over to Node 2 sucessfully
select @@servername on node 2 equals 'SQLB' as expected
Failed back to Node 1 successfully
All databases on line and tested logins functional.

QED ;-)

If any problems do arise you can rest assured I will post them. Right
now though (for a little while at least) I'm feeling pleased with
myself - this saved me a lot of work.

Comments welcomed

Re: SQL Server 2000 Virtual Server Renaming

uttamkp NO[at]SPAM online.microsoft.com
7/30/2004 3:15:34 AM

Barry,

Just to confirm that the steps I mentioned works, I did a bit of testing on a test server. Here are the scenarios I tried

Scenario 1 -- Renaming default instance virtual sql server (vss) name
(For simplicity -- all mdf and ldf files were on same Data folder)
-------------------------------------------------------------------------------------------------------------------------------------------
1) Took backup of the mdf and ldf files. i.e. backed up Data folder.
2) Uninstalled default instance virtual sql server (vss). Reinstalled virtual sql server with new virtual server name. Applied SQL Server service packs and patches that were there before
3) Took vss resource offline. Replaced new Data folder with backup taken in step 1. Took vss resource online. Came online. Performed failover -- worked as expected
4) Ran select @@servername. It gave old virtual server name. Ran
exec sp_dropserver 'old virtual server name'
go
exec sp_addserver 'new virtual server name' , 'local'
go
Recylcled vss. Ran select @@servername -->returned new virtual server name (As expected)

Scenario 2 -- Renaming named instance virtual sql server (vss) name (instance name same)
(For simplicity -- all mdf and ldf files were on same Data folder)
-------------------------------------------------------------------------------------------------------------------------------------------
1) Took backup of the mdf and ldf files. i.e. backed up Data folder.
2) Uninstalled named instance virtual sql server (vss). Reinstalled virtual sql server with new virtual server name. But I kept the same instance name. Applied SQL Server service packs and patches that were there
before
3) Took vss resource offline. Replaced new Data folder with backup taken in step 1. Took vss resource online. Came online. Performed failover -- worked as expected
4) Ran select @@servername. It gave old virtual server name\instance name. Ran
exec sp_dropserver 'old virtual server name\instance name'
go
exec sp_addserver 'new virtual server name\instance name' , 'local'
go
Recylcled vss. Ran select @@servername -->returned new virtual server name\instance name (As expected)

Scenario 3 -- Renaming named instance virtual sql server (vss) name (In this case I changed the instance name also)
(For simplicity -- all mdf and ldf files were on same Data folder)
-------------------------------------------------------------------------------------------------------------------------------------------
1) Took backup of the mdf and ldf files. i.e. backed up Data folder.
2) Uninstalled named instance virtual sql server (vss). Reinstalled virtual sql server with new virtual server name and new instance name. Applied SQL Server service packs and patches that were there before
3) Took vss resource offline. Replaced new Data folder with backup taken in step 1. Took vss resource online. Resource Failed.
Did usual troubleshooting ;) . Found that SQL was not getting the mdf and ldf files for all dbs except master. It gets info for master from registry. It was looking for
G:\Program Files\Microsoft SQL Server\MSSQL$OldInstanceName\Data folder

whereas now I had put the Data folder in

G:\Program Files\Microsoft SQL Server\MSSQL$NewInstanceName

Easiest way to solve was to create the folder it was looking for and then SQL came online. They are other ways to fix this but it is outside the scope of this posting.
4) Ran select @@servername. It gave old virtual server name\old instance name. Ran
exec sp_dropserver 'old virtual server name\old instance name'
go
exec sp_addserver 'new virtual server name\new instance name' , 'local'
go
Recylcled vss. Ran select @@servername -->returned new virtual server name\new instance name (As expected)

P.S: For a production environment, I will also take SQL Server backups of ALL dbs (system and user dbs) --- just in case ;) Ofcourse, before uninstalling SQL I should know the collation, sort order of SQL Server as
I will need these for the new install. I have seen many cases where the dba did not know have any info about old sql server that was already uninstalled, all they had was sql backups. Depending upon the
complexity these cases can take a lot of time to resolve ;)

HTH,

Best Regards,

Uttam Parui
Microsoft Corporation

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.

Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

Re: SQL Server 2000 Virtual Server Renaming

allspammustdie NO[at]SPAM yahoo.com
7/30/2004 3:29:42 AM

Hi Uttam et al
I've just seen your most recent posting now having already just posted
my own (identical) approach so we are officially agreed then. :-)

A Cluster/SQL Server urban legend bites the dust.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server clustering group.