Moving SQL server 2005 cluster to new hardware


Moving cluster SQL server 2005 to new hardware:

The hardware of the cluster SQL server 2005 two servers out of warranty and I developed a plan to move to the new hardware with minimum downtime for the end user.

The old cluster were running on windows 2003 64bit, I decided to move it to the new hardware on windows 2008 R2 64bit instead of window 2003 to advantage windows new features and install SQL 2005 so we can utilize the old license. It would have been good idea to upgrade to SQL 2008 R2 but it would cost more money to buy license for SQL 2008.

The plan to move the SQL server to the new hardware is build Windows 2008 R2 cluster and install SQL 2005 as cluster in the new hardware (two nodes) then install the latest SQL service pack (Sp4) then use SQL script to move the databases to the new SQL cluster. After the database moved successfully rename the new cluster name and with the old SQL cluster name (virtual ip name) and all the applications will connect to the new hardware without any problem.

Steps to move SQL server cluster to the new hardware: 

  1. Install windows 2008 enterprise edition on Node 1& 2
  2. Install/configure Windows 2008 cluster
  3. Add web server role (IIS)
  4. Install SQL 2005 on top of the windows 2008 cluster 
  5. Install SQL 2005 SP4
  6. Move user accounts from the old cluster company-db2 to the new  database cluster
  7. Perform full backup of old cluster company-db databases
  8. Restore databases in the new cluster with no recovery so additional log file or differential backup can be restored using SQL script:

--*********************************************************************************
-- Written by : Tarek Mohamed Date: July 14, 2011
-- Updated by: Date:
-- Updated by: Date:
-- Updated by: Date:
-- Version:1.0
--*********************************************************************************
--
-- This script will restore databases in folder which each database has folder and the folder name will be
-- the database name so you can use this script to restore one database or more
-- The functnality of the script:
-- 1-Locate the database folder in the source folder
-- 2-Sort the files in in ecach database folder
-- 3-Locate the latest full backup and restore it to the new location

-- 4-Locate the Transaction log files and restore them
-- Please note this script desgined to work with backups form SQL maintenance plans
--
--*********************************************************************************
-- User inputs
-- 1-@SourceDirBackupFiles, this parameter with the path of the backup files
-- 2-@DetDirDbfiles, this paramter will have the path will be used to restore the data and log file
-- for each database
--*********************************************************************************
SET NOCOUNT ON
Declare @SourceDirBackupFiles varchar(500)
Declare @DestDirDbFiles varchar(500)
-- set this paramater with the path of the backups folder
Set @SourceDirBackupFiles='E:\DBsbackup'
-- set this paramater with the destnation of the data and log files
Set @DestDirDbFiles='E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data'
Declare @SourceDirBackupfolder varchar(500)
Declare @dbname Varchar(200)
declare @NoOfRestorefiles int
Declare @dirfile varchar(900)
DECLARE @fname varchar(200)
DECLARE @LogicalName nvarchar(128)
DECLARE @PhysicalName nvarchar(260)
Declare @BaseRestoredone int
DECLARE @sql nvarchar(2000)
DECLARE @type char(1)
--*********************************************************************************
-- Initliazation
--*********************************************************************************
-- create temp table to hold all the the folders has the database files to restore
CREATE TABLE ##folders(fname varchar(200),depth int, file_ int)
-- create temp table to hold all the backup files for each database
CREATE TABLE #files(fname varchar(200),depth int, file_ int)
-- create temp table will save backup file info
CREATE TABLE #dbfiles(
LogicalName nvarchar(500)
,PhysicalName nvarchar(500)
,Type char(1)
,FileGroupName nvarchar(500)
,Size numeric(20,0)
,MaxSize numeric(20,0),
filed sql_variant,
createlsn sql_variant,
droplsn sql_variant,
uniqueld sql_variant,
readonlylsn sql_variant,
readwritelsn sql_variant,
backupsizeinbytes sql_variant,
sourceblocksize sql_variant,
filedgroup sql_variant,
loggroupguid sql_variant,
diff sql_variant,
diff1 sql_variant,
isreadonly sql_variant,
ispresent sql_variant)
--*********************************************************************************
-- script body
--********************************************************************************* --get all the folders from the source backup folder and each folder will be the database name
INSERT ##folders
EXECUTE master.dbo.xp_dirtree @SourceDirBackupFiles, 1, 1
--Cursor to process each folder(database backup files)
DECLARE folders CURSOR FOR SELECT fname FROM ##folders where fname not in ('master', 'tempdb','model','msdb')
open folders
fetch next from folders into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
--get all the files from the Directory contains the SQL backup
set @SourceDirBackupfolder =@SourceDirBackupFiles+'\'+@dbname
-- get the backup file to restore
INSERT #files
EXECUTE master.dbo.xp_dirtree @SourceDirBackupfolder, 1, 1
-- Select the full backuo file
SELECT @fname=fname from #files where substring(fname,len(fname)-2,3)='bak'
print @fname
--Construct the beginning for the RESTORE DATABASE command
set @dirfile=@SourceDirBackupFiles+'\'+@dbname+'\'+@fname
print @dirfile
SET @sql = 'RESTORE DATABASE [' + @dbname + '] FROM DISK = ''' + @dirfile + ''' WITH MOVE '
TRUNCATE TABLE #dbfiles -- delete the records in this temp table
Insert #dbfiles
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @dirfile + '''')
-- Declare Cursor for the files for each database
DECLARE dbfiles CURSOR FOR
SELECT LogicalName, PhysicalName, Type FROM #dbfiles
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type
--For each database file that the database uses
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + '''' + @LogicalName + ''' TO ''' + @DestDirDbFiles + '\' + @dbname
IF @type = 'D'
SET @sql = @sql + '.mdf'', MOVE '
ELSE IF @type = 'L'
SET @sql = @sql + '_log.ldf'''
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type
END
CLOSE dbfiles
DEALLOCATE dbfiles
set @sql=@sql+', NORECOVERY, REPLACE '
print @sql
exec (@sql)
truncate table #files
fetch next from folders into @dbname
End
--**************************************************
close folders
deallocate folders
drop table ##folder

  1. restore Analysis services cubes on the new cluster
  2. Force the databases in the old cluster company-db to be readonly using SQL script:

Declare @dbname Varchar(200)
DECLARE @sql nvarchar(2000)
DECLARE DBs CURSOR FOR SELECT name from sys.databases where name not in ('master', 'model','msdb','tempdb')
Open DBs
fetch next from Dbs into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER DATABASE [' + @dbname + '] SET READ_ONLY WITH ROLLBACK IMMEDIATE'
exec (@sql)
SET @sql = 'ALTER DATABASE [' + @dbname + '] SET READ_ONLY'
Exec(@sql)
fetch next from Dbs into @dbname
END
close DBs
deallocate DBs

  1. Perform differential backups
  2. Restore differential backups to databases in step 8 with recovery using SQL script:

--*********************************************************************************
-- Written by : Tarek Mohamed Date: July 14, 2011
-- Updated by: Date:
-- Updated by: Date:
-- Updated by: Date:
-- Version:1.0
--*********************************************************************************
--
-- This script will restore databases in folder which each database has folder and the folder name will be
-- the database name so you can use this script to restore one database or more
-- The functnality of the script:
-- 1-Locate the database folder in the source folder
-- 2-Sort the files in in ecach database folder
-- 3-Locate the latest full backup and restore it to the new location
-- 4-Locate the Transaction log files and restore them
-- Please note this script desgined to work with backups form SQL maintenance plans
--
--*********************************************************************************
-- User inputs
-- 1-@SourceDirBackupFiles, this parameter with the path of the backup files
-- 2-@DetDirDbfiles, this paramter will have the path will be used to restore the data and log file
-- for each database
--*********************************************************************************
SET NOCOUNT ON
Declare @SourceDirBackupFiles varchar(500)
Declare @DestDirDbFiles varchar(500)
-- set this paramater with the path of the backups folder
Set @SourceDirBackupFiles='E:\DBsbackup'
-- set this paramater with the destnation of the data and log files
Set @DestDirDbFiles='E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data'
Declare @SourceDirBackupfolder varchar(500)
Declare @dbname Varchar(200)
declare @NoOfRestorefiles int
Declare @dirfile varchar(900)
DECLARE @fname varchar(200)
DECLARE @LogicalName nvarchar(128)
DECLARE @PhysicalName nvarchar(260)
Declare @BaseRestoredone int
DECLARE @sql nvarchar(2000)
DECLARE @type char(1)
--*********************************************************************************
-- Initliazation
--*********************************************************************************
-- create temp table to hold all the the folders has the database files to restore
CREATE TABLE ##folders(fname varchar(200),depth int, file_ int)
-- create temp table to hold all the backup files for each database
CREATE TABLE #files(fname varchar(200),depth int, file_ int)
-- create temp table will save backup file info
CREATE TABLE #dbfiles(
LogicalName nvarchar(500)
,PhysicalName nvarchar(500)
,Type char(1)
,FileGroupName nvarchar(500)
,Size numeric(20,0)
,MaxSize numeric(20,0),
filed sql_variant,
createlsn sql_variant,
droplsn sql_variant,
uniqueld sql_variant,
readonlylsn sql_variant,
readwritelsn sql_variant,
backupsizeinbytes sql_variant,
sourceblocksize sql_variant,
filedgroup sql_variant,
loggroupguid sql_variant,
diff sql_variant,
diff1 sql_variant,
isreadonly sql_variant,
ispresent sql_variant)
--*********************************************************************************
-- script body
--*********************************************************************************
--get all the folders from the source backup folder and each folder will be the database name
INSERT ##folders
EXECUTE master.dbo.xp_dirtree @SourceDirBackupFiles, 1, 1
--Cursor to process each folder(database backup files)
DECLARE folders CURSOR FOR SELECT fname FROM ##folders where fname not in ('master', 'tempdb','model','msdb')
open folders
fetch next from folders into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
--get all the files from the Directory contains the SQL backup
set @SourceDirBackupfolder =@SourceDirBackupFiles+'\'+@dbname
-- get the backup file to restore
INSERT #files
EXECUTE master.dbo.xp_dirtree @SourceDirBackupfolder, 1, 1
-- Select the full backuo file
SELECT @fname=fname from #files where substring(fname,len(fname)-2,3)='dif'
print @fname
--Construct the beginning for the RESTORE DATABASE command
set @dirfile=@SourceDirBackupFiles+'\'+@dbname+'\'+@fname
print @dirfile
SET @sql = 'RESTORE DATABASE [' + @dbname + '] FROM DISK = ''' + @dirfile + ''''
print @sql
exec (@sql)
truncate table #files
fetch next from folders into @dbname
End
--**************************************************
close folders
deallocate folders
drop table ##folders
drop table #files
drop table #dbfiles

  1. After restoring the differential backup the databases status will be ready and must be changed to read write using SQL script:

Declare @dbname Varchar(200)
DECLARE @sql nvarchar(2000)
DECLARE DBs CURSOR FOR SELECT name from sys.databases where name not in ('master', 'model','msdb','tempdb')
Open DBs
fetch next from Dbs into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER DATABASE [' + @dbname + '] SET READ_WRITE WITH ROLLBACK IMMEDIATE'
exec (@sql)
SET @sql = 'ALTER DATABASE [' + @dbname + '] SET READ_WRITE'
Exec(@sql)
fetch next from Dbs into @dbname
END
close DBs
deallocate DBs

  1. Shutdown the old cluster company-db( Node1 & Node2)
  2. Delete DNS entries for company-db on DNS Servers
  3. Stop database cluster resources on the new Cluster
  4. Rename Database cluster name to company-db as below:
  1. Restart the cluster( the new company-db) and make sure the SQL resources are online
  2. wait till you see machine name company-db in active directly (AD)and DNS server will have entry for company-db this might take around 15 minutes
  3. Verify connectivity for company-db from outside (PC or smoothing)
Advertisements

About Tarek

I have been working in IT for years and I thought it might be good to share my experience using real life scenarios.
This entry was posted in Microsft SQL Server and tagged , , . Bookmark the permalink.

2 Responses to Moving SQL server 2005 cluster to new hardware

  1. Jared Bowden says:

    Thanks for this post. I’m a Sys Admin and I have two SQL clusters that I need to migrate from 2003 to 2008 server, and SQL 2008, and I’ve been reading many articles and forums, but there are still a few details that I’m hung up on:
    1) Initially, when you first build the new cluster, and install SQL, the IP addresses that are assigned to each SQL instance will have to be different than the 2003 IPs which are already in use. Later, when you offline the 2003 cluster, and go to reip the SQL IP addresses, will changing the SQL IP addresses in the 2008 cluster require any reconfiguration within SQL or cause any problems that might prevent SQL from starting?
    2) Once you rename the cluster name, I believe the cluster will then attempt to attach to the preexisting Computer Object in Active Directory. If for some reason the migration doesn’t go smoothly and I need to fall back, will the 2003 cluster be able to come online and reattach to the same computer object?
    3) This may seem like a silly question, sorry I’m not a DBA… but what is the purpose of making the DB read only if the backup and restore is done offline and no new data is written to it?

    Thanks!

  2. Tarek says:

    It depend on how your application connect to the database server, if the applications doesn’t connect using the ip you don’t need to change the IPs but we want make sure the dns reflect the new IP address. That what I did in my case I didn’t want change the IP addresses of the new cluster sp I changed the cluster name as in step 17 because all my application were connecting using the DNS entry. If it doesn’t work you can start the old 2003 cluster and the reverse the dns entry to point to the old IP address and you

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s