Posts Automatically deploy Database Changes with SSDT
Automatically deploy Database Changes with SSDT
Cancel

Automatically deploy Database Changes with SSDT

In my last post, I talked about deploying database changes automatically. Today, I will show how to use SSDT (SQS Server Data Tools) to generate a Dacpac package and how to deploy it to your SQL server.

Prepare your Environment

Before you can get started, you have to download the custom SSDT Tools from Github. These custom tools are an extension of the MSBuild.Sdk.SqlProj project and are necessary to be able to open the SSDT project file (*.sqlproj) with Visual Studio.

Getting to know the SSDT Project Structure

You can find the code of the demo on Github.

Open the .sqlproj file with Visual Studio and you will see the project structure. There are two relevant folders inside the project, Scripts, and Tables.

The Scripts folders

The Scripts folder contains the PostScripts, PreScripts, and ReferenceDataScripts subfolders.

The Scripts folder contains SQL scripts

The Scripts folder contains SQL scripts

Scripts in the PreScripts folder are executed before the deployment whereas scripts in the PostScripts and ReferenceDataScripts folders are executed after the deployment. There are two folders after the deployment for a better separation of concern but it would also be fine if you put all your scripts into the PostScripts folder. You can execute any SQL script you want, except schema changes. Schema changes are defined in the Tables folder.

The Tables folder

The Tables folder contains the definition of your tables.

The definitions of all tables

The definitions of all tables

The MigrationScriptsHistory table is needed to store all executed migrations. All other tables are customer-defined and you can add whatever tables you need, for example, the Product table:

1
2
3
4
5
CREATE TABLE [dbo].[Product]
(
	[Id] INT NOT NULL PRIMARY KEY, 
    [Name] NVARCHAR(50) NOT NULL 
)

If you double-click on the SQL file in Visual Studio, you can see the script file and the designer to edit the file.

The SQL Designer in Visual Studio

The SQL Designer in Visual Studio

Generate the Database using SSDT

I have added two tables, Customer and Product, and want to deploy this new database to my database server. Since Docker is awesome, I use a Docker container for my SQL Server. You can start an SQL Server 2019 with the following command:

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=yourStrong(!)Password' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest

If you connect to your server, using tools like the SQL Server Management Tool, you will see that there is no database yet.

No database is on the server

No database is on the server

To deploy the new database, right-click the .sqlproj file in Visual Studio and select Publish.

Publish the database

Publish the database

This opens the Publish Database window.

Configure the database deployment

Configure the database deployment

As you can see, the connection string is empty. Click on edit and enter your database server information.

Provide the connection settings

Provide the connection settings

This creates the connection string and now you can click on Publish to deploy your database.

Deploy the database

Deploy the database

The publish process should only take a couple of seconds and Visual Studio will tell you when it is finished.

The publish succeeded

The publish succeeded

That’s already it. Refresh your SQL server and you will see the new database with its three tables.

The database and tables got created

The database and tables got created

Applying changes to an existing Database

If you already have an existing database and want to apply changes, for example, adding a new column, you can simply add it to the existing table. Open the Product table and add a price column as a decimal. The code looks as follows:

1
2
3
4
5
6
CREATE TABLE [dbo].[Product]
(
	[Id] INT NOT NULL PRIMARY KEY, 
    [Name] NVARCHAR(50) NOT NULL,
    [Price] DECIMAL(8,2) NOT NULL    
)

That’s already all you have to do. Publish the project again and update your SQL server to see the new column in the Product table.

The Product table got a new column

The Product table got a new column

Configure the Target Platform

If you don’t use an SQL Server 2019 then you have to configure your target platform before you can publish the project. Right-click the .sqlproj file and select Properties. Select the desired target platform in the Project Settings tab.

Configure the target platform

Configure the target platform

Conclusion

SSDT offers a simple solution to automating your database deployments. It automatically checks the schema on the deployed database and compares it with the schema in your SSDT project. If there is a change, these changes are applied. This allows for fast changes and even allows for setting up environments for new developers fast and easily. The demo was very simple but should give you enough knowledge to get started.

In my next post, I will show how to build the SSDT project in your CI/CD pipeline and how to deploy it to an Azure SQL Database.

You can find the code of the demo on Github.

This post is part of “Microservice Series - From Zero to Hero”.

This post is licensed under CC BY 4.0 by the author.