11

I have a 1.4TB SQL Server Database that's struggling massively with disk I/O. We've installed a new SSD array into the server that will solve all of our problems, we're just debating the best way to move the database across. Ideally if we can do it without downtime, that's best. But where the choice is between two days of poor performance (e.g. while copying data) versus two hours of downtime, the latter might be preferable.

So far, the solutions we've come up with are:

  • Simple copy. Take the DB offline, copy the files across, change the locations in SQL Server and bring it back online. Rough figures estimate this will take up to five hours, which is not really acceptable, but it is the easiest solution.

  • Block-level copy. Using an rsync-like utility, we copy the files across in the background while the DB is up. When we're ready to migrate, we take the DB offline, do a differential copy across using this utility, then point SQL server at the new files and bring it online. Timing here is unknown. We don't know how long it will take to do a differential analysis of 1.4TB and copy that across. Our other concern is that the block-level copy will leave the files in some state unreadable by SQL Server and we'll have waste our time.

  • SQL migration. Create a new 1.4TB SQL data file on the new disk and disable autogrowth on all of the other files. Then run DBBC SHRINKFILE(-file_name-, EMPTYFILE) on all of the other data files in turn. Once all of the data is across, I'll take a scheduled window at some point to move the MDF file over to the SSD and remove the other unused files. I like this because it minimises downtime. But I have no idea how long this will take and whether it will cause degradation in performance while it's happening.

We don't have any kind of load & performance environment to test this. I can verify that the strategies will work on our staging environment, but not the impact and not the performance.

migrated from stackoverflow.com Dec 15 '15 at 11:26

This question came from our site for professional and enthusiast programmers.

  • Your datafiles are stored on an LVM partition? – Marco Dec 15 '15 at 11:44
  • 1
    don't know how long it will take to do a differential analysis of 1.4TB at least as long as it takes to read that data. I don't think the rsync idea saves much if anything. rsync is made to cope with slow networks. – usr Dec 15 '15 at 12:26
  • 2
    Instead of using EMPTYFILE I would rebuild all indexes over to a new filegroup that lies on the SSD. That way the indexes appear nice and defragmented. EMPTYFILE might fragment them, not sure. – usr Dec 15 '15 at 12:27
14

One method to move the entire database is with BACKUP and RESTORE. The database will be unavailable during the final switch but downtime should be minimal with planning. This procedure assumes the FULL or BULK_LOGGED recovery model:

1) Perform a FULL backup (or use your existing one).

2) Restore latest full backup to a different database name, specifying the WITH MOVE option to relocate files on SSD storage as desired and the NORECOVERY option so that subsequent differential and log backups can be restored.

3) Apply incremental changes to the new database until the time of the final cut-over with transaction log backups and RESTORE...WITH NORECOVERY. This will minimize downtime for the final switch to the new database.

4) To switch to the new database, take the application offline, perform a final transaction log backup, and apply to the new database WITH RECOVERY. Finally, rename the original database to a different name and rename the relocated database to the original name. Drop the old database at your convenience.

In the SIMPLE recovery model, you could use a similar process but without the step 3 transaction log backup/restores. Instead, use a differential database backup/restore in the final step. That may require more downtime, depending on the amount of changes since the initial FULL backup.

  • Yup, nothing comes to mind as simplest and fastest for same server db movement. – Marian Dec 15 '15 at 22:17
-6
Good approach is to use SQL REPLICATION between two server
once all data replicated on SSD server then take current server offline 
and switch to SSD server.
  • 2
    You're getting downvotes because you're not answering the question. There's only one server in the situation under discussion. – AakashM Dec 15 '15 at 13:55
  • Also a good approach to move dbs are mirroring, log shipping, availability groups, backups and.. others. This does not solves the problems, unfortunately. – Marian Dec 15 '15 at 22:18
  • On one server we can create two instances and make replication between two instances. – Avinash Mendse Dec 16 '15 at 6:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.