THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Considerations when starting a new SSDT database project

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:

image

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:

image

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:

SNAGHTML1ebb40b8

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

SNAGHTML1ebc6e3e

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:

image

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:

image

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)

image
image
image

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.

image

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

Published Thursday, March 21, 2013 12:58 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

CPU_BUSY said:

Great starting point - i preferred the folder structure used in the previous version - not sure why they did away with this,- creating a separate script folder definately makes life easier

Have you had to deal much with linked servers? - I have had issues where we had existing databases with linked servers to DB2 or Oracle that would not build because of this.

March 21, 2013 9:44 AM
 

jamiet said:

Hi,

I haven't had to deal with Linked Servers at all I'm afraid (lucky me). However, I do know that SSDT supports Linked Servers via the provision of 4-part names - I suggest you check out Database References at this article: http://blogs.msdn.com/b/gertd/archive/2007/07/26/database-references.aspx which does talk about Linked Servers.

Regards

Jamie

March 21, 2013 10:26 AM
 

Gonçalo Ferreira said:

I suggest adding "Page verify" to the list of options to double check -- NONE is a really, really bad default option!

March 21, 2013 4:18 PM
 

Keith Ellis said:

Do you have any recommendations on how to manage users and logins such that (a) they use SqlCmd variables and (b) the scripts are part of the build process so validation occurs?

I'd like to create a "PortalAccount.sql" file with the below contents but it doesn't work. My workaround is a post-deployment script that creates all security objects (server logins, db users, roles, role memberships, and permissions). What I have works but I would love if these files were part of the build process.

CREATE USER [$(PortalAccount)] FOR LOGIN [$(PortalAccount)] WITH DEFAULT_SCHEMA=[dbo]

Thanks!

April 9, 2013 9:11 PM
 

jamiet said:

Hi Keith,

Thanks for the comment.

I did blog about similar issues before at:

A strategy for managing security for different environments using the Database Development Tools in Visual Studio 2010

(http://sqlblog.com/blogs/jamie_thomson/archive/2010/07/21/a-strategy-for-managing-security-for-different-environments-using-the-database-development-tools-in-visual-studio-2010.aspx)

which may be of some use. Take a read and feel free to post comments up there if you have any questions or points to make.

regards

Jamie

April 10, 2013 3:43 AM
 

Wes Boyd said:

Hi Jamie,

Can you point me in the right direction in order to make this a custom template?

September 6, 2013 8:55 AM
 

jamiet said:

Hi Wes,

I'm afraid I don't know how to do that, now if its even possible.

regards

Jamie

September 6, 2013 8:58 AM
 

Wes Boyd said:

I found a one liner!

http://technet.microsoft.com/en-us/library/ms141178.aspx

You simple do that and it works! err No it doesn't!

September 6, 2013 10:02 AM
 

Wes Boyd said:

Hold the phone!

http://technet.microsoft.com/en-us/library/ms345191.aspx

It works -- Add New Item NOT Add New SSIS Package

September 6, 2013 10:07 AM
 

Alexander Batishchev said:

Thanks for the article!

Just a side comment: sometimes you give a pretty controversial recommendations justifying.

December 18, 2013 1:10 AM
 

jamiet said:

Hi Alexander. What have i said that you consider controversial?

December 18, 2013 2:04 AM
 

jamiet said:

Hi Wes,

Sorry, I'm confused. This blog post is regarding SSDT but the article you link to there concerns SSIS package templates. That's something else entirely.

JT

December 27, 2013 3:45 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement