As you may have realised from much of my blogging over the past year or so I’m an advocate of using SSDT database projects for building database solutions on SQL Server. I have been using SSDT database projects a lot in that time and have come up with a checklist of things to consider when starting a new SSDT database project and I’ll be detailing that checklist below. I strongly advise you to consider making decisions about these items before you even write a scrap of code as invariably it will be more difficult to change later, especially if you have already deployed your database.
In no particular order here is my checklist:
Folder Structure
By default SSDT will not provide a folder structure for new projects in which to store all your script files so you might want to consider setting one up yourself. I recommend not trying to create a large hierarchy of folders to start with as this will evolve as you go about building your database. Here’s a simple starter for ten:
Filegroups
If you don’t understand the importance of filegroups then read Thomas Larock’s recent blog post DATABASE FILEGROUPS: JUST LIKE SEATBELTS BUT WITH LESS CHAFING. Quite often your organisation will stipulate policies for what filegroups you should be using and I highly recommend that you set up filegroups in your database projects to match those policies sooner rather than later. If your organisation does not have any such policies then perhaps think about defining them yourself, Thomas lists a few considerations that may influence your decisions:
- separation of system data from user data
- larger indexes may benefit from their own filegroup
- archival of data can benefit from using dedicated filegroups as this will reduce backup maintenance tasks
If you follow my suggested folder structure above then put your filegroups into the “Storage” folder:
Above all, specify your default filegroup especially as many DBAs won’t be happy about you putting objects into the PRIMARY filegroup (which is the default). This is done by right-clicking on the project and selecting “Properties..” When there hit “Database Settings…” and set your default filegroup on the “Operational” tab:
If you intend to use the filestream feature of SQL Server then you can set the default filestream filegroup here too.
Collation
If you take only one piece of advice from this blog post make it this: set your collation before you write a single line of code (just trust me on this, OK). You’ll find this in Project Properties->Project Settings->Database Settings->Common
Again, find out if your organisation has any guidance on database collation.
Specify Target platform
SSDT allows you to deploy a project to SQL Server 2005, 2008, 2012 or Azure so it stands to reason that you should specify which you are intending to deploy to. You’ll find this in Project Properties->Project Settings:
Default Schema
If you follow the premise that “anything that SQL Server picks as the default is probably wrong” then you should probably think about changing the default schema which, by default, will be [dbo]. SSDT will create all new objects in the default schema (unless otherwise specified).
Generally I’m of the opinion that not accepting SQL Server’s defaults is a good idea if only because it forces you to think about these things and be aware of them; in the case of schemas it forces you to think about security and who should have GRANT or DENY permissions and on what.
Specify the default schema in Project properties->Project Settings:
Review other database settings
I’ve mentioned the most important database settings that you should look to change however you should also glance over the rest of the defaults that SSDT chooses for you to verify that they are valid for your project. Again, these are in Project Properties->Project Settings->Database Settings. a few I’d call out as being especially worthy of your attention are:
- Auto update stats (on by default)
- Recovery model (FULL by default)
- Transaction Isolation (RCSI off by default)
- Service broker (disabled by default)
Code Analysis
SSDT always checks the syntax of your database code, that’s one of the main justifications for using SSDT database projects. However what it does not do (not by default anyway) is check whether the code you write might be considered good code. That’s what Code Analysis is for, your code gets checked to see if it adheres to well-understood good practices for SQL Server database development. For example, Code Analysis will check for use of “SELECT*” which is generally regarded as a bad thing.
Code analysis is turned off by default. I recommend that you head into Project Properties->Code Analysis and turn it on. Moreover I recommend that you check all the boxes under “Treat Warning as Error” – this forces developers to address issues that get raised by Code Analysis.
Big Labels
Add a banner to your Pre-Deployment script as I describe in Big label generator. This may seem rather unnecessary but it takes about 30 seconds and believe me, at some point you’ll be glad you did!
The demo project that I took some of the above screenshots from is downloadable from http://sdrv.ms/ZW0gNt and might provide a useful template for your own SSDT database projects.
If you have any more suggestions for my checklist please add them to the comments below!
@Jamiet