3

I have a SQL Server 2008 database in production that we are moving onto a new server. The current database has a single ~400GB .MDF file. The new server will be running SQL Server 2012, and we are running mirrored Intel 910 SSDs. These drives will present us with 4x 200GB partitions.

To make this work, we will need to split the single .MDF into 4 smaller ones using DBCC SHIRNKFILE with EMPTYFILE. We have done this in test, and it still takes ~ 3.5 hours to do which is too long. The existing database is OLTP, and 365/24/7 and I know blocking will occur during this process, so we can't do it on production first.

My question, is there a way to backup and restore the database to the new server in a temp location, create the new files, EMPTY the temp .MDF into the new locations, then apply transaction logs after? That way we can move the data while current old production is up and running, then do a short shutdown, apply logs, and bring up the new DB?

Or are there any other options to get from Server A with one file and Server B with 4 files on different drives with minimal downtime?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Lauren
  • 39
  • 2

1 Answers1

1

One think you can do, if you have the disk space, is:

  • Restore the database on the new server
  • Create a new file group
  • Create all the indexes on the new file group with DROP_EXISTING=ON (and specify the new filegroup)
  • Drop the old file group

This won't work for Large Object Data, you would need to move that manually to a new table.

Syntax for this would be something like:

CREATE CLUSTERED INDEX [index]  
ON [schema].[table]([columns)
WITH (DROP_EXISTING = ON, ONLINE = ON) 
ON [filegroup]

It is possible that the index can stay online while the new one is created. This will cause the tempdb to use more space though.

JodyT
  • 4,324
  • 2
  • 19
  • 31
  • Can't he drop and recreate the clustered index on the new FileGroup? I do that move tables – Preet Sangha Mar 15 '13 at 00:36
  • That is what happens when you use `DROP_EXISTING`. It drops the index then rebuilds it on the file group that you specify. – JodyT Mar 15 '13 at 00:40
  • Space is not an issue for temp storage. I will play around with this on the new servers and see how long it takes. Our indexing is a little crazy at the moment, which is another task I have, so I have a feeling that this will be slower the shrinking, but will look into it. Cheers – Lauren Mar 15 '13 at 01:01
  • Using the option `DROP_EXISTING` is more efficient then a regular rebuild index. A regular rebuild causes the `NONCLUSTERED` indexes to be rebuild twice, while using this option it will only rebuild them once. – JodyT Mar 15 '13 at 01:04