Splitting a SQL Server 2012 database into multiple files
-
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?
-
Answer:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/61c08bb2-e443-4ccd-89d8-34611b050522/split-one-mdf-file-into-several-files?forum=sqldatabaseengine 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 http://msdn.microsoft.com/en-us/library/ms175905(v=sql.110).aspx 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.
DWRoelands at Ask.Metafilter.Com Visit the source
Other answers
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.
Salvor Hardin
How screwed are you if you start losing data? I'd at least ask around at https://dba.stackexchange.com/, 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.
Aleyn
> 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.
ostranenie
Related Q & A:
- Can I create a second filestream container on an existing SQL Server 2008 database without going offline?Best solution by Database Administrators
- What is the best practice for free space for a SQL server database drive?Best solution by Database Administrators
- How to restrict access to a sql server database from Azure?Best solution by Stack Overflow
- How to change column data's as a separate column wise format in a SQL Server?Best solution by stackoverflow.com
- How do I connect to a SQL Server database?Best solution by Stack Overflow
Just Added Q & A:
- How many active mobile subscribers are there in China?Best solution by Quora
- How to find the right vacation?Best solution by bookit.com
- How To Make Your Own Primer?Best solution by thekrazycouponlady.com
- How do you get the domain & range?Best solution by ChaCha
- How do you open pop up blockers?Best solution by Yahoo! Answers
For every problem there is a solution! Proved by Solucija.
-
Got an issue and looking for advice?
-
Ask Solucija to search every corner of the Web for help.
-
Get workable solutions and helpful tips in a moment.
Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.