Configuring database files within Microsoft SQL Server Development Tools

Over the years SQL Server database development has evolved from non-structured tools (Query Analyser/Management Studio) to fully structured development tools (Visual Studio Database Professionals/SQL Server Data Tools). However there many important aspects of database development which hasn't changed over the years. One of which I would like to cover within this blog post is the database files configuration especially within Microsoft development tools: SSDTS and VSDB-Pro.

Getting your database files size and growth incorrectly configured can hurt your database performance. As on a project we had our first test release into production of our data warehouse and run our ETL process. At the time the databases had to take on the default file settings of the model database. After the process completed within three hours our data base files all grew in excess of 40GB. We did another test release, after tearing down the environment, with database file settings worked into the deployment and re-run our ETL process. This rerun completed in almost half the time of the first test release.

I have been using the Microsoft database development tools ever since they have been release. I have found that they do improve the development and deployment process of the database life-cycle. I also believe that all objects involved with your database should be included within the project of these tools so that there is one consistent method of deployment of the databases.

Unfortunately there are some restrictions which make putting all the objects within the project impossible to achieve. One of these are that you cannot apply parameters within the file definitions. This is unsuitable as this enforces the database files to be the same size for all environments which don’t always have same disk space assigned.

I would like to share a method, which works for both VSDB-Pro & SSDTS, which I've come up with to get around the restriction of not being able to parameterise the file sizes:

1. Create some variables for the size of the file(s) and how much the file(s) growth should be e.g. <filegroup>FileSize , <filegroup>Filegrowth
2. Create your files with an initial size and growth configuration e.g.
   

      1:  ALTER DATABASE [$(DatabaseName)] 

      2:  ADD FILE ( NAME = <logicalfilename>

      3:  , FILENAME = '$(SQLDatabaseDataPath)\$(DatabaseName)_<logicalfilename>.'

      4:  , SIZE = 3072 KB

      5:  , MAXSIZE = UNLIMITED

      6:  , FILEGROWTH = 1024 KB ) 

      7:  TO FILEGROUP[<FileGroup>];

   
   
3. Then within the post-deployment script add the following SQL statement , for each file, which checks that current size of the file is under the configured variable file size to update file configuration:
   
   

      1:  IF (SELECT ([size] * 8 / 1024) FROM sys.database_files WHERE name = '<logicalfilename>') < $(<filegroup>FileSize) 

      2:  BEGIN 

      3:  ALTER DATABASE [$(DatabaseName)] 

      4:  MODIFY FILE ( NAME = N'<logicalfilename>'

      5:  , SIZE = $(<filegroup>FileSize)MB

      6:  , FILEGROWTH = $(<filegroup>Filegrowth)MB) 

      7:  END 

   The above example the unit of measure is MB

I would like to share some recommendations using this approach:

• I would recommend creating one set of variables, <filegroup>FileSize and <filegroup>Filegrowth, per file group. As it is recommended to have the same file configuration within a file group due to way that SQL Server handles multiple files within the same file group.
  
• Having instant file initialisation configured within your SQL Server boost the speed of the data files creation. To enable instant file initialisation do the following steps:
  
  
  
  • Open Local Security Policy on the SQL Server
    
  • Select Local Policies \ User Rights Assignment \ Perform volume maintenance tasks.
    
  • When adding a user; change the location to the local computer and the follow user NT SERVICE\MSSQLSERVER (If your SQL Server is the default instance otherwise use the following pattern: NT SERVICE\MSSQL$)