Splitting a SQL Server 2012 database into multiple files

February 7, 2014 5:46 AM

I have an application whose database will soon grow beyond the 2TB limit of its disk drive.

I have additional drives available to me, each with 2TB of space. What I would like to do is split this database across 2 drives, with some of the tables on drive D and the remainder on drive E. This is not being done for performance reasons, but simply to allow the database to whold what it needs.

Can you point me to a decent HOWTO or article that describes how this can be done?

posted by DWRoelands to Computers & Internet (5 answers total)

Best answer: Here's a discussion on MSDN with appropriate links on how to add files and use DBCC SHRINKFILE to force SQL to split tables between files. If you want more control over what goes where, there's this walkthrough on moving indexes; moving a clustered index will move its associated table to the same filegroup.

I'd try some things on a sandbox before doing anything live, of course. Also - if your disks aren't the same speed as each other, you may want to figure out whether some of your tables are heavier on I/O (i.e. lots of writes) and make sure they are on the faster drive.
posted by graymouser at 6:37 AM on February 7, 2014 [1 favorite]

I've never done this, but you could create a spanned volume out of your multiple disks, thus effectively creating a single large drive made up of the individual smaller drives that would be big enough for your whole database file.

I know it does have the downside that if one drive dies, your whole database is dead, and there's a higher chance of that happening with multiple drives.
posted by Salvor Hardin at 7:52 AM on February 7, 2014

How screwed are you if you start losing data? I'd at least ask around at the DBA StackExchange, since presumably you'll have a more knowledgeable audience there that can better steer you around potential pitfalls with whatever solution you go with. If it's really important, it might be a sign that you should contract out a DBA to help with this.
posted by Aleyn at 2:00 PM on February 7, 2014

Best answer: Increase the Size of a Database, Microsoft TechNet

This topic describes how to increase the size of a database in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL. The database is expanded by either increasing the size of an existing data or log file or by adding a new file to the database.

posted by ob1quixote at 3:38 PM on February 7, 2014

> This is not being done for performance reasons, but simply to allow the database to whold what it needs.

Have you considered migrating it to a RAID 5 or RAID 6 array instead of storing it across volumes? That might cause you less headaches in the long run and you'd have a shot at a semi-quick recovery if a drive fails, incremental backups (which you should be doing anyway) notwithstanding.
posted by ostranenie at 8:08 PM on February 7, 2014