Database Properties in Visual Studio Database Projects

Do you work in Visual Studio for development of SQL Server databases? At Pragmatic Works, we’re big fans using SQL Server data tools within Visual Studio for doing this kind of development, especially development of Azure SQL Databases.

Today I’d like to share some tips about using Visual Studio and SSDT for Azure SQL Database, mainly with database properties. I’ll start with a mistake I recently made on a project (see screenshot below).

• I had created a new database project in Visual Studio. When I used the dropdown to set the target platform, I chose Microsoft Azure SQL Database.
• It seemed clear to me that’s what I wanted so I chose that platform and created a couple table scripts in the project.
• But when I went to build the project, I received an error message that something I had written was not supported in this version of SQL Server.
• What I needed to use was the Azure SQL Database V12 option. And once I chose that, my problem was solved.

In this next screenshot, you’ll see a simple query that you can run against any of your Azure SQL Databases.

• Looking at the ‘@@’ variable, I see that I am running V12. This is something you can’t change as the Version 12 of the Azure SQL Database platform has been generally available since 2015.
• The version of Visual Studio I was using still had the old version as an option.

Now, I started thinking, what other things should I know about my database and the things I may be able to control from Visual Studio? Check out my next slide:

• Let me point out that the version of the database is not the service tier or service objective; this is not what tells me whether I’m running standard or premium.
• To see what service tier you’re running you can do this quick query against this view called ‘sys database objectives’. By doing this simple query, I can see that my database is a standard S0.
• This is something you cannot change in Visual Studio; you can only change that through a SQL script or through the portal.

This next slide shows a query of compatibility level, this is something we can change in Visual Studio.

• A compatibility level is for your SQL Database (Azure or others) and this tells me what version of on-premises SQL the database is compatible with.
• In my example it shows 150 which corresponds to the upcoming release of SQL Server 2019.
• By default, if you create a new database in Azure SQL DB, as of this writing, the compatibility level will be set at 140 which corresponds to SQL Server 2017.

My last slide shows how I can set the compatibility level of my database in Visual Studio.

Again, not the version of the service or the service tier, but only the compatibility level I can do here from the database projects in Visual Studio. Then I can include this in my deployment scripts if I wish.

Hopefully you found this helpful on managing database properties in Visual Studio. If you have question on this, the Azure SQL Data Platform or anything Azure related, we’d love to help. Click the link below or contact us – we’re here to help you take your business from good to great with Azure.