Replacement of SQL servers that are attached to a SAN

Question

Replacement of SQL servers that are attached to a SAN

Dazza

SSC Enthusiast

Points: 171
More actions
July 21, 2006 at 6:58 am

#60939

We have 2 Win2000 Enterprise edition servers running SQL2000 Enterprise edition running an active/passive cluster and are attached to a SAN, all user and system databases sit on the SAN.

We plan to replace these 2 servers with 2 new Win2003 enterprise edition builds, server names(nodes) will change but the virtual SQL cluster name will remain the same.

Overview Plan is as follows;

Disconnect users from SQL.

Backup User and System databases.

Take Cluster service offline

Detach all User databases.

Shutdown SQL server.

Remove servers from SAN.

Replace and attach 2 new SQL servers.

Install SQL Server and install choose SAN for the data file location.(I’m assuming the existing System DB’s MDF & LDF files get overwritten)

Stop SQL server

Start SQL server in single user mode from command prompt.

In SQL Analyzer restore Master db.

Start SQL server. (assuming may get some error messages as cannot find the user databases, but ignore these)

Restore the MSDB & Model databases from backups.

Re- attach user databases.

I'm sure there are many people who have done the above many times, I would really appreciate some feedback as to whether my approach is correct or missing anything.

Thanks in advance.
Darren

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

SQLZ SSChampion Points: 12872 More actions · Answer 1

Hi Darren,

In the past, when doing this kind of thing I skip the part where you detach the databases. When I shutdown the SQL Servers I copy the system database MDFs, LDFs and NDFs. Although it's not necessary I also copy the user database files just in case.

After building the new server, all you need to do is copy the above system database files (the ones you copied somewhere safe) into the directory where the system database files are located after the install.

And that's it.

Start SQL Server and it will pick up the master db, which points to all of the other system and user databases.

This assumes that, when you install SQL Server, you specify the same directory for the system databases.

Essentially though there's absolutely nothing wrong with your approach. My approach skips a couple of steps, meaning you don't need to mess around with re-attaching database or restoring master. But that's about it.

And as long as you have backups you should be ok.

One thing worth mentioning. When you take the backups I'd at least verify that the backups are valid by restoring them on a seperate server. The last thing you want happening is for things to go wrong only to find out that the backups are no good.

It pays to be paranoid .

Good luck,

Karl
source control for SQL Server

Dazza SSC Enthusiast Points: 171 More actions · Answer 2

Thanks Carl for your suggestion, I carried out this work at the weekend and followed your suggestion which made complete sense and was all plain sailing. No problems at all.

Many Thanks

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

One thing that may fail in this approach is if you have local accounts as logins to SQL Server then when you replace the server and re-create this new local accounts they will have different SIDs. These accounts have to be re-added to SQL Server. Both Domain and SQL Server Standard accounts should be OK, it is only Windows Local Accounts that may have this problem.

Regards,Yelena Varsha