Controlling Database Compatibility Level with SSDT

We’re currently look into upgrading from SQL Server 2008 R2 to SQL Server 2012.  I think the plan is to detach the existing databases, install 2012 and attach to the new instance.  Once we’ve attached we will set the compatibility level in the databases properties to be SQL Server 2012.

SSDT Compatibility Level 1

Our databases are under source control in TFS using SSDT for VS2012.  We have several enivornments that we will need to upgrade and to make sure we don’t forget to update the compatibility level we will update it in our SSDT projects and then it will get updated when we publish.  To set the compatibility level in SSDT click on the “Database Settings…” button in the “Project Settings” tab of the database properties.  Then under “Miscellaneous” select the required value from the “Compatibility Level” drop down.

SSDT Compatibility Level 2

However, by default SSDT doesn’t check for differences in the compatibility level when it publishes a database project.  You can enable it in the “Advanced” settings of the “Publish Wizard” and then save the publish profile for later use.

SSDT Compatibility Level 3

Then if there is a difference between the compatibility level in your database and project a statement similar to the following will be included in the publish script.

SSDT Compatibility Level 4

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s