The configuration of SQL Database project is important. It this article we will learn how to create a basic configuration for the project, which would work in 80% of cases.
Configuring Filegroups
Using multiple filegroups allows for a lot of benefits:
- Separating out internal system data from user data
- Placing larger tables/indexes on their own filegroup and/or dedicated set of disks for performance
- Placing archive (or even read-only) data onto their own filegroups and dedicated set of disks to reduce maintenance overhead
- Ability to bring parts of the database online quickly, no need to wait for a full restore
Having your data separated to several filegroups is important. If you interested into this topic, please read DATABASE FILEGROUPS: JUST LIKE SEATBELTS BUT WITH LESS CHAFING or some other materials, which you quickly find using search engines.
In our test project, we will create two filegroups. One filegroup we will use for users' data, another will we use to store indexes. If you forgot how to add new items in solution, please use Creating stored procedures and additional objects in SQL Server Database Project article for more information.
DataFileGroup.sql code
/* Do not change the database path or name variables. Any sqlcmd variables will be properly substituted during build and deployment. */ ALTER DATABASE [$(DatabaseName)] ADD FILEGROUP [DATA]
IndexesFileGroup.sql code
/* Do not change the database path or name variables. Any sqlcmd variables will be properly substituted during build and deployment. */ ALTER DATABASE [$(DatabaseName)] ADD FILEGROUP [INDEXES]
I've put files in the root of the project. So the structure of the project looks like on the picture below.
Database project has default filegroup with name 'PRIMARY'. We have to change that and set 'DATA' as default filegroup. To change default file group, please use Set default filegroup for Microsoft Database project article.
Configuring Target platform
Target platform specifies the version of SQL Server that you are targeting with this database project. In Visual Studio 2017 there are few possible options for the target platform:
- SQL Server 2005
- SQL Server 2008
- SQL Server 2012
- SQL Server 2014
- SQL Server 2016
- Microsoft Azure SQL Database
- etc.
Choose what suits you best. I will keep target platform as SQL Server 2016.
Configuring Default schema
Default schema property specifies the default schema in which both SQLCLR and Transact-SQL objects are created. You can override this setting by specifying schema directly on objects.” In our scenario, we are not going to change default schema.
Configuring Build options (optional)
From my perspective, there is one option among build option which can bring some value to the project. This option is 'Treat Transact-SQL warnings as errors'. This option specifies whether a Transact-SQL warning should cause the build and deployment process to be canceled. If this check box is cleared, warnings appear, but the build and deployment process continues. This setting is specific to the project, not the user, and is stored in the .sqlproj file. Personally, I prefer to set this option to true, because strict rules can reduce potential issues in future. More information about build option you can find using link https://msdn.microsoft.com/en-us/library/hh272681(v=vs.103).aspx#SQLCLR and SQLCLR Build.
Database settings
To change database settings you have to navigate to Project Setting → Database Settings.
Configuring database collation
Collations in SQL Server provide sorting rules, case, and accent sensitivity properties for your data. Collations that are used with character data types such as char and varchar dictate the code page and corresponding characters that can be represented for that data type. Whether you are installing a new instance of SQL Server, restoring a database backup, or connecting server to client databases, it is important that you understand the locale requirements, sorting order, and case and accent sensitivity of the data that you will be working with.
A collation specifies the bit patterns that represent each character in a data set. Collations also determine the rules that sort and compare data. SQL Server supports storing objects that have different collations in a single database. For non-Unicode columns, the collation setting specifies the code page for the data and which characters can be represented. Data that is moved between non-Unicode columns must be converted from the source code page to the destination code page. Read more about collation https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support.
To configure database collation navigate to Project Settings → Database Settings → Common → Database Collation.
Configuring compatibility level
A lot of enterprise solutions have a long history, so many of them started on old versions of SQL server. It might be required to setup your project in compatibility level. To do that you have to navigate to Project Settings → Database Settings → Miscellaneous → Compatibility level.
Code analysis
Code analysis is turned off by default. I recommend to activate it and check all 'Treat Warning as Error' checkboxes. To do that navigate to Properties → Code Analysis. (see picture below)
Conclusion
In this article, I tried to provide vision about the configuration of SQL Server Database project. I highlighted the most common and important settings, which should be configured for each project. You can find source code examples using following link https://github.com/aliakseimaniuk/blog-examples/tree/master/WideWorldImporters/04-Configuring.
