SQLServerWiki

“The Only Thing That Is Constant Is Change”

Ways to migrate SQL Server User databases from one storage to another.

Posted by database-wiki on April 28, 2016

  1. Copy the files. This would be easy but relatively slow, also requires taking the database offline to move the files. Since uptime is paramount, this is a no-go.
  1. Backup and restore with new name onto the new SAN. This is a very strong candidate, due to its simplicity.

Restoring a backup on the new storage (with a different database name) while the current database remains online.

Create a job to restore the Transaction Log backup (incurred in the meantime) of original database to renamed database.

Cutting over would involve backing up the tail of the transaction log on the old database, restoring it on the new one, and then swapping names.

The cutover would be quick enough to fit in a maintenance window, making this a very attractive option.

  1. BACKUP LAST TRAN LOG On Primary database.

BACKUP LOG Check21DB TO DISK = ‘C:\MSSQL\BACKUP\FTRN\’ WITH norecovery

  1. RESTORE the above LAST LOGs for each database ON renamed database with recovery

RESTORE LOG DBNAME FROM DISK = ‘\\SecondaryServer\C$\MSSQL\Backup\FTRN\xxx.trn’ WITH RECOVERY

Note: This will have an overhead on the instance as we are introducing a duplicate copy of large databases on the same instance which will increase the resource consumption and may have performance impact.

  1. Add and remove files from the filegroup. This approach is quite slow and tedious. SQL Server allows you to add and remove files from filegroups at almost any time,

and this can be leveraged to move data between different storage systems. What makes it awesome is that the operation is completely online with no interruptions to the user,

all types of data (including LOB data) is moved, and it works in any edition of SQL Server.

Note: As always, there are trade-offs. It’s very slow, and LOB data makes it even slower.

Shrinking the data files may introduce fragmentation but can be solved using REBUILD or REORGANIZE, and if speed is not important, it could be the way to go.

  1. Mirroring. Much like arolling upgrade, mirroring can be used.

Steps:

  1. Setup a database mirroring on a secondary instance. Secondary database(s) to be placed on Nimble Storage.
  2. Failover the database(s) to secondary instance.
  3. Remove Mirroring and Setting Secondary Database Online.

ALTER DATABASE [database_name] SET PARTNER OFF

GO

RESTORE DATABASE [database_name] WITH RECOVERY

GO

  1. Detach the Secondary database from secondary instance followed by removing the clustered disk and adding it to primary instance.
  2. Remove or rename the primary database(s).
  3. Attach the database(s) from the Nimble storage.

Note: Unfortunately both primary database and secondary database cannot reside on the same instance so a secondary instance is needed.

  1. Log shipping: As an alternate to mirroring, we can use log shipping.

Steps:

  1. Setup a Log Shipping on a secondary instance. Secondary database(s) to be placed on Nimble Storage.
  2. Changing the secondary as primary:
  3. Ensure all scheduled backups have completed successfully.
  4. STOP/DISABLE LOG SHIPPING BACKUP JOBS.
  5. RUN (LOG SHIPPING) TRAN LOG COPY AND RESTORE JOBS ON Secondary until the last log is applied then DISABLE all copy & restore jobs.
  6. REMOVE THE LOG-SHIPPING FROM PRIMARY SERVER’S MAINTENANCE PLANS for the required Db’s.
  7. Go to maintenance jobs folder ON Primary.
  8. delete destination server information from inside the maintenance job for each DB.

iii.           remove log shipping & delete maintenance job.

  1. Kill all users IN Required database on primary server.
  2. BACKUP LAST TRAN LOG On Primary Server and place them is easy accessible folder Eg.(\\Primary Server Name\C$\MSSQL\Backup\FTRN)
  3. BACKUP LOG Check21DB TO DISK = ‘C:\MSSQL\BACKUP\FTRN\’ WITH norecovery
  4. COPY LAST LOGS TO Secondary Server to say ‘FTRN’ folder (\\Secondary Server \C$\MSSQL\Backup\FTRN)

E.g: xp_cmdshell ‘copy \\PriamryServer \C$\MSSQL\BACKUP\FTRN\*.trn \\SecondaryServer\C$\MSSQL\Backup\FTRN’

  1. RESTORE the above LAST LOGs for each database ON Secondary with recovery

RESTORE LOG DBNAME FROM DISK = ‘\\SecondaryServer\C$\MSSQL\Backup\FTRN\xxx.trn’ WITH RECOVERY

  1. Detach the Secondary database from secondary instance followed by removing the clustered disk and adding it to primary instance.
  2. Remove or rename the primary database(s).
  3. Attach the database(s) from the Nimble storage.

Note: More complicated than mirroring and both primary database and secondary database cannot reside on the same instance.

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: