//
you're reading...
SQL Server

Database Migrations (Moving to a new Server)

Over the past several weeks I have had the honor of migrating several production servers to new SAN. Obviously constituting many “all nighters” and normally over the weekends. So clearly the quickest migration method is in everybody’s best interest.

I ended up utilizing two methods, Detach\Attach and Backup\Restore.  In terms of preference I would lean slightly more towards Detach\Attach as it is pretty much full proof and provides the least level of risk, assuming file copy and not file move is implemented. However from a performance perspective the backup and restore proved to be much more efficient especially since you can stripe the backup set across multiple folders and volumes.

The first two servers I used the detach\attach method and that went quit well however because of the state of the old SAN it took four hours to copy 370+ GB of files for one database to their new location on the new SAN. As for the third server, as it was participating in replication,  detach\attach was not an option. Unless I chose to break and rebuild replication and deal with the time it would take to perform a snapshot synchronization. Yes, I probably could have stopped the agents but my experience with SQL Server replication is that when it works it is absolutely fantastic but when it breaks it is a bit of a bastard.  Instead I chose to backup and restore (KEEP_REPLICATION) the databases to new file locations (Move). Under this scenario I was able to migrate (backup & restore) a 570GB database in less then 3 hours utilizing stripped backups. And this was done on a SQL Server 2005 environment, imagine how quickly it could have been done had I had backup compression available to me.

I do feel that the detach\attach method is full proof as the original files are left intact, whereas with the backup\restore probably leaves a tiny margin of risk as you are overwriting the database. Then again if you can not trust your backup and restore I guess there are bigger issues.

Steps For Detach\Attach:

–YourDB
— Set database to SINGE_USER mode to restrict access\transactions
ALTER DATABASE YourDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
— Detach Database
exec sp_detach_db @dbname = ‘YourDB’
go

—————————————-
— Move Files
—————————————-

— Attach Database to new location —–
exec sp_attach_db @dbname = ‘YourDB’,
@filename1 = ‘G:\data\dwh\YourDB.mdf’,
@filename2 = ‘D:\dwh\YourDB_log.ldf’
go
— Set database to MUTLI_USER mode to unrestricted access
ALTER DATABASE YourDB SET MULTI_USER
go 

Steps for Backup and Restore:

— Set database to SINGE_USER mode to restrict access\transactions
ALTER DATABASE YourDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
— Backup Database utilizing striping of backup set
BACKUP DATABASE [YourDB] TO  DISK = N’U:\BCKUP\YourDB.BAK’,
DISK=N’U:\BCKUP2\YourDB.BAK’,
DISK=N’U:\BCKUP3\YourDB.BAK’,
DISK=N’U:\BCKUP4\YourDB.BAK’
WITH NOFORMAT, NOINIT,  NAME = N’YourDB-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
— Restore Database utlizing the Move parameter and keep_replication parameter (if replicated)
USE MASTER
GO
RESTORE DATABASE [YourDB] FROM  DISK = N’U:\BCKUP\YourDB.BAK’,
DISK = N’U:\BCKUP2\YourDB.BAK’,
DISK = N’U:\BCKUP3\YourDB.BAK’,
DISK = N’U:\BCKUP4\YourDB.BAK’
WITH  FILE = 1,
MOVE N’YourDB’ TO N’U:\YourDB.mdf’,
MOVE N’YourDB_archives’ TO N’U:\YourDB_archives.ndf’,
MOVE N’YourDB_indexes’ TO N’W:\YourDB_Indexes.ndf’,
MOVE N’YourDB_log’ TO N’X:\YourDB_log.ldf’,
NOUNLOAD,  REPLACE,  STATS = 10,keep_replication
GO
— Set database to SINGE_USER mode to unrestricted access
ALTER DATABASE YourDB SET Multi_USER –WITH ROLLBACK IMMEDIATE
go

Other Considerations:

You may want to dbcc shrink the log file after putting into SINGLE_USER mode to reduce log file size.

If you happen to loose your session while in SINGLE_USER Mode, which happened to me as the SAN Admin restarted the services in the middle of my backup, you can do the following:

— Find active sessions for YourDB (should only be 1 (yours)
select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
from master..sysprocesses p inner join master..sysdatabases d on p.dbid = d.dbid
where d.name = ‘YourDB’
go
Kill 123 (your spid)
go
— Reset database mode
exec sp_dboption ‘YourDB’, ‘single user’, ‘FALSE’;
GO                                                                                          – Restart Steps outlined above

As with any script you get off the internet please review for appropriateness in your environment. NJOY and I hope this sample code is helpful.

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

No comments yet.

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

%d bloggers like this: