I work both with SSDT database projects and .NET projects based on Entity Framework Code First models. Now I’ll say some words about how these two types of projects do the deployment.
1. SSDT database project
- You write the code (it is Transact-SQL code) to create tables, stored procedures, functions and other SQL Server database objects in declarative style. It means that you write it in a way of how it should be in the final stage. Don’t worry about changes, no ALTER commands – Visual Studio do it during deployment.
- In SSDT database project terminology deployment is called “publishing”. To deploy your database you should find Publish command 🙂
- During publishing Visual Studio compare the actual state of the database with the state that should be according to your project. Then it generate the ALTER script.
2. Code First Migration
- You write your model in declarative style too but as a C# class.
- To create the database you use NuGet Package Manager to run two commands: Add-Migration and Update-Database.
- During migration Visual Studio looks into previous migration classes and generates the difference. Then it creates a new class with changes.
- Update-Database generates T-SQL commands according to the code in the migration class. The info about applied migrations is saved in the database in the dbo.__MigrationHistory table.
The main difference between these two deployments is that SSDT database project produces change script based on actual database, whereas Code First Migration does not. So if you use migrations, change your tables only through migrations. Direct changes to database would not be taken into account by migration process.