Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 SHRINKFILE 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?

share|improve this question
1  
I see in the Intel SSD's spec that they come in two capacities: 400 and 800. What's the reason for 200 GB partitions for a production db server? –  Marian Mar 15 '13 at 14:24
    
In a similar vain to @Marian's question, where does this 4 file issue come into play? Why is it key to this move? –  Mark Storey-Smith Mar 18 '13 at 0:14
1  

2 Answers 2

In a word, no. You have to do it live.

What you'll want to do it add 3 new files. Then simply start doing index rebuild operations. As long as you are doing rebuilds not defrags SQL will start spreading the data across all the data files.

I'm assuming that you are running on SQL Enterprise Edition and can do online index rebuilds.

share|improve this answer
    
Yes, running enterprise on the new setup. I can do some testing, do you know if there is a speed difference between rebuilding all the indexes vs just emptying the primary file? I have a feeling that may be slower since our indexing is a little out of control. That is an issue I will tackle after we get off the old servers. –  Lauren Mar 15 '13 at 0:57
    
You'll have to do all of this on the old server. Is the old server standard or Enterprise? –  mrdenny Mar 15 '13 at 16:18
    
old is only standard, so no online rebuilds –  Lauren Mar 17 '13 at 19:07
1  
How is an index rebuild supposed to help move from server A to server B? The question describes a server switch, not a storage switch. –  Mark Storey-Smith Mar 18 '13 at 0:13
    
I am pretty sure for the index rebuild to work I would need to move the data to the new server first, either through detach and copy or via backup and restore. I would then need to either shrink the old primary file via DBCC to the new files, or move the data via a index rebuild. –  Lauren Mar 18 '13 at 3:50

I would set up striping in the operating system or hardware. That will be easier to manage than multiple files in SQL Server.

Can you create a hardware RAID 1+0 across all 8 SSDs? Can you do it in software, using Windows storage?

Otherwise you could create a new database using 4 files then use either the Database Object Copy Wizard or Transactional Replication to move the objects from old database to new database. I'm not sure how the duration of this will compare to the 3½ hours for splitting. Since these methods will lead to split-brain you will have to have some down-time while the objects copy across (the strategy is, disable updates to the old server, allow enough time for all the new data to have replicated to the new server, test data integrity, enable updates on the new server).

share|improve this answer
    
Sadly, that is the downfall of the Intel SSD, no hardware RAID. Some/most of the FusionIO cards are the same from what I have read. To make them fast, they basically connect the SSD controller directly to the PCIe bus, then you have to deal with volumes at the OS level. Each card presents us with 4x 200gb drives, so we are using windows software mirroring across cards to have 4x mirrored volumes we will be storing our data on. I still have not bench-marked the other options as some other fires have started I need to put out first. –  Lauren Mar 18 '13 at 3:47
1  
Using the database copy wizard or replication are horrible approaches as data will be missing on the destination database if the database copy wizard is used, and with replication you'll be missing users, permissions, roles, etc that don't get copied across. –  mrdenny Mar 18 '13 at 14:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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