0

Say I have a large production mirrored 1TB DB that resides on a single MDF device and I would like to split that up into say 5 200 Gig devices. I want to do this without interruption to Production.

I thought I could break the mirror and use the RESTORE process for creating a mirror to achieve the split to multiple devices quickly and without interruption to Production. Doing this twice would allow me to get this done in a few hours.

Has anyone done this? Is it the preferred method seeing as we are mirroring anyways?

What are other my alternatives, Pros and Cons? And gotchas?

Also, I recall another more organic process where one would create the 5 new New Devices and somehow, over time get the objects to move over to the new devices. Not sure of the process for this but I seem to recall it being discussed. Sounds like this could take a long time and possibly cause some clocking at times?

Thanks

...Ray

RayG
  • 1
  • 2

1 Answers1

0

This isn't quite as simple a process as it first looks, the reason being is that just adding the files to SQL server isn't enough as even if you were to add 4 new files, they would all be empty space, you would have one file with 1Tb of data in it and 4 empty ones, which would eventually fill up as SQL server uses a proportional fill method for the files, but most of your queries would still be hitting the single file.

I take it you are doing this to improve performance? If so, you will need to move data around into different files in order to actually split the data up. Whether you can do this online or not depends on whether you are running Enterprise Edition or not (as this allows you to rebuild indexes online).

An easy way to move a table (or more accurately a clustered index, which is pretty much the same thing as the table for all intents and purposes) is to add a new filegroup with a new data file and then rebuild the clustered index specifying the new filegroup, you can use the following to do this:

CREATE CLUSTERED INDEX Existing_Index_Name ON schema_name.table_name(column_name) 
WITH(DROP_EXISTING=ON,Online=ON) on [new_filegroup_name]
GO

This code will create the new index on the new filegroup, get rid of the old one and if you are running enterprise edition, it will do it all without blocking the users.

See the following link for more methods of moving the data between filegroups:

Move data between SQL Server database filegroups

You should also look into partitioning your tables to help improve performance too:

Partitioning Tables and Indexes

With regards to your mirroring setup, you should break the mirror, then on the primary add all your files/filegroups, then move the data between the filegroups, then backup the modified database on the primary, restore on the mirror (so all the files are set up the same on the mirror) and then re-set up your mirroring.

steoleary
  • 8,968
  • 2
  • 33
  • 47