SQL Server Data Tools (SSDT)

Does database comparisons, deployments, automation

In Visual Studio SQL Server Project

Being in Visual Studio � can do smart refactoring. It propagates all the changes (like a rename) to all associated files.

Schema Compare and Updates

Changes made on the SQL server can be brought back into Visual Studio through the Database Compare tool. This means everything done on the SQL server can be stored into source control.

Working with Data

SSDT can help with tracking and managing certain data � such as lookup data (static data). Such data can be configuration data (name-value pairs).

In the SQL Project � add new item under SQL Server for User Scripts. These scripts can be pre or post deployment scripts:

Scripts.NotInBuild are scripts that are not built � these can contain the static data.

DacPac (.dacpac)

These are binary files of the database. Used to deploy the database. Its actually a package file (can be viewed with zip file vierwer) that is based on XML and has all the sql data. Also has a refactor.xml which keeps track of all the refactoring changes.

Deploy and Automation

When deploying, the Project Setttings must have the correct Target Platform. For example, if server is 2016, must define that version in the Target Platform.

SqlPackage.exe deploys the *.dacpac

Create a solution item for a bat file which will call the SqlPackage.exe for publishing.

This script can now be used to auto deploy the database changes.