If you want to learn the SQL Server Data Tools (SSDT) I definitely recommend watching the Pluralsight course Visual Studio Data Tools for Developers. It is a 3 hours long course from Deborah Kurata that provides a great introduction to it.
SSDT is a collection of tools for developers that helps you to work with SQL Server inside Visual Studio.
SQL Server Object Explorer is basically a subset of SQL Server Management Studio (SSMS) inside Visual Studio that allows you create, delete, edit objects among other things. A designer helps you to work on your object and the query editor provides Intellisense and T-SQL syntax error checking.
The Update button apply the changes.
SQL Server Database Project is a Visual Studio project that allows you to define an entire database. You can create a project directly from a database in the SQL Server Object Explorer (using Create New Project) or you can right-click on the project and select Import Database.
Each database project has many settings like the Target Platform (the version of SQL Server you are targeting) and many database settings (like collation). Target Platform helps to avoid using features not supported in your target SQL Server.
You can run Code Analysis on your project to get recommendations on your T-SQL.
To deploy your database, you right-click the project and click Publish. From the same dialog you can save a publish profile and have different deployment settings based on target environments. For example, you can have a publishing profile for development and one for staging. This profile is a xml file that is added to your project.
What happens when you build a database project?
A DACPAC file gets created in the bin/debug folder. A DACPAC is a deployment unit, a package that contains all the SQL Server objects that represent the end state of the database you want to deploy. More specifically (it is a zip file at the end), it contains a single sql file with all the scripts combined in it plus additional files that contains various metadata.
You can create a DACPACK file directly from a database (both in SQL Server Object Explorer or in SSMS) without creating a database project (right-click on the database and select Extract Data-Tier Application).
You can also publish a DACPACK file in a similar way using the Import Data-Tier Application option.
Under the covers, during a deployment SSDT compares the database defined in the DACPACK file with the target database in order to automatically generate a deployment script. You have the ability to look at the deployment script before running it.
SqlPackage.exe is a command line tool that allows you to automate the deployment. You can provide the action (like publish), the dacpac file you want to deploy and a publishing profile.
There is also a programming API called DACFx that you can use to automate the deployment using C#. The namespace to do it is Microsoft.SqlServer.Dac.
And what about data?
SSDT gives you the ability to add pre-deployment and post deployment scripts in order to preserve or add your data. In order to add data you need to add a post deployment script. In oder to preserve data you need to add a pre-deployment script (to copy the data in a temporary location) and a post deployment script (to restore the data in the new location).
For columns renames Visual Studio create a refactoring file in the database project that contains some metadata so that it can automatically do the data preserving operations for you.
This way of managing data can works in simple scenarios but it is not very flexible. You can only run a custom script before or after the deployment. If you need to do more complicated refactoring that requires running scripts in the middle of your deployment and you seriously care about preventing data loss, it is probably worth looking at SQL Source Control 5 and in particular the new Migrations feature.
Inside the pre-deployment and post-deployment scripts you can use the SQLCMD syntax to load and run scripts. This can help to make your deployment scripts more modular.
You can use the T-SQL merge statement for merging data instead of deleting and re-adding it. You can basically run what you want inside pre-deployment and post-deployment scripts.
Some extra tools
In Visual Studio, under Tools -> SQL Server you can find few additional tools.
The Schema Comparison allows you to compare two databases or a database and a database project to see the differences. You can also deploy the changes if you want.
The Data Comparison does the same thing but instead of comparing the schema it compares the data inside your tables.
The Query tool helps writing queries. It gives you the ability to visualize the query plan directly inside Visual Studio.
Finally you can also create a Database test project that contains SQL Server Unit Tests. There is a limited set of assertions (called Test Conditions) but you can add your own Test Conditions if you want using C#. You can use the classic Visual Studio Test Explorer to run the tests and see the results.
Conclusions
SSDT use state driven deployment that means that the deployment script is created automatically at deployment time. It can change at every deployment based on the target database. If you prefer using a migrations-driven deployment to have full control of the deployment script and still use Visual Studio you should have a look at ReadyRoll.
If state driven deployment is right for you but you live inside SSMS you should check SQL Source Control. You can also use SSDT tooling during development but use SQL Source Control as your versioning and migrations tool to get the best of the two worlds.
Both ReadyRoll and SQL Source Control use the trusted industry-standard SQL Compare engine under the covers.
SSDT offers a lot of tools to deal with SQL Server inside Visual Studio. You can stay up to date with what’s next in SSDT following the SSDT Team blog.