Firstly what is a Database in SQL Server ?
There are probably many definitions but one aspect is that a database has a transaction log and therefore the capability to coordinate many concurrent transactions, if recovery is needed completed transactions can be rolled forward and incomplete transactions can be rolled back thereby recreating a consistent database.
Applications that use multiple databases will either have to use the MSDTC – distributed transaction coordinator or risk an inconsistent recovery should it be required.
Why use multiple Files ?
Better performance on backup, restore and the ability to place files on different drives giving rise to better IO throughput and better performance.
Additionally a SQL Server database on the enterprise edition can be online once the Primary Filegroup (containing the system objects) is online.
Each filegroup can be backed up and restored separately.
Database — transaction log
– < Filegroup(s) (FG)
There is always a Primary Filegroup (contains the system objects)
There is always a Default Filegroup (contains objects which are not specifically placed, doesn’t have to be the Primary FG)
For all versions of SQL Server before SQL Server 2016
Use TraceFlag 1117 to ensure all files in a FG grow at the same time by an equal amount
Use Traceflag 1118 to disable mixed allocation on extents – reduces reliance on SGAM pages
SQL Server uses a proportional fill algorithm to determine where to write data
this depends on the amount of free space (not size !) in each file within the target filegroup.
Up to sizes of about 500Gb running with a single File causes no major problems. But unfortunately migrating a database over 50Gb from a single file to multiple files is a bit tricky
This implies it is best to address this challenge early.
One way to do this is to have a standard design and alter the model database so that all new databases not created with an explicit file layout will inherit the files from the model db.
We should have a Primary Filegroup and just hold the system objects in it – a single file is probably OK in this FG.
We should have one or more other Filegroup(s) – DATA is as good a name as any
I would recommend having 4 files in this filegroup ( up to 1Tb – rough rule of thumb) all of equal size and equal autogrow settings that way we can place all 4 files on 1 LUN, 2 files on 2 LUNS, or 1 file on 4 LUNs as requirements change.
when we might want more than 1 Filegroup for example
when we have staging tables that we frequently load and truncate
when we have tables with large datatypes (XML, Spatial, Images) etc,
when we have historical data which we wish to set read-only (& back up separately)
when using partitioned tables
when we can obtain a performance improvement by isolating large or frequently used tables to their own FG
when using In Memory tables
when using filetables
when using fulltext search – put the fulltext indexes on their own FG
when the vendor recommends it
Add new LUNs to the SQL Server, we will want GPT drives (not MBR) with 64 kb allocation unit size.
Create the new Filegroup(s) and add the new empty files.
Move the tables to the new Filegroups by using
Create Index XXX ON DATA with DROP_EXISTING
realistically we will need to be offline while this process in ongoing
but we don’t need to move all the tables at the same time.
When it comes to HEAPs (tables without any clustered index) – there are two choices
Create a new clustered index on the target filegroup in question and then drop the clustered index
Create a new empty table with matching schema and a new name
Script indexes and constraints
Use Insert Select to move the data across
Drop indexes and constraints on the original table
Drop the original table
Use sp_rename to rename the new table to the original tablename
Re-add indexes and constraints
We can run these scripts as a job overnight using the SQL Server Agent
NB The transaction log will need to be large enough to accommodate the largest table that needs to be moved
alternately one can create a new table, move the rows in batches navigating
the Clustered index and then rename the new table (this is a lot of work and needs thorough testing) – contact your local friendly SQL Server experts 😊