The user creates specific scripts to create or update each database. Source of errors and waste of time… in short, it's not the recommended solution.
Migration version by version
You define versions of the database (version 1, version 2, …) and the scripts allowing to go from version N to N+1. If the database is in v2 and you want to migrate it to v4, you have to apply the script v2->v3 then v3->v4. This is for example the solution chosen by Entity Framework Migrations.
In the rest of the post, we will see how the solution provided by SQL Server, namely the DAC packages (DAC package or DACPAC).
A DacPac file is a zip archive containing mainly an XML file. This file contains all the information of the database schema: the list of tables, columns, stored procedures, triggers, etc. Almost all kinds of objects of SQL Server are supported. More details on MSDN: DAC Support For SQL Server Objects and Versions
So you have a file that alone can describe the contents of the database. This can be very useful when you have to exchange the DB schema with someone else (for example, between a developer and a DBA).
Finally, this file can be deployed on a SQL server to create a new database or update an existing database. This procedure can be done through SQL Server Management Studio or the command line via the SqlPackage.exe . In the case of a migration, a migration script is generated and executed. You can validate that the script corresponds to what is expected. For some migrations, there are warnings to indicate for example that there can potentially be a loss of data.
Using SQL Server Management Studio
SqlPackage is the command-line utility for manipulating dacpacs. To create a dacpac from an existing database you can use the extract command:
Shell copyVisual Studio database project
This kind of project is integrated with Visual Studio and contains creation scripts for objects (tables, triggers, etc.). When compiling this type of project a dacpac file is produced. I will explain this kind of projects in more details in the next article.
For the deployment of a dacpac file you can the same tools, but also WebDeploy. In the case of the latter, this makes it possible to deploy a website and its database in a single operation. You can find all the information on the IIS website: https://learn.microsoft.com/en-us/iis/publish/using-web-deploy/dbdacfx-provider-for-incremental-database-publishing
Using SQL Server Management Studio
There are many options to customize the operations performed when creating the deployment script (useful mainly for updates):
Here is an excerpt from a migration script. In this script the Product table is created, the Customer table schema has changed (changing the identity column increment) and is updated without loss of data using a temporary table, and the stored procedures associated with the modified tables are refreshed. We can see that the migration is capable of advanced modifications.
; GO 50) ; GO ; GO 50) 50) 1, 2) 1 1 The SqlPackage tool provides functionality to compare the schema of a database with a dacpac and it can generate a drift report.
Shell copyThe generated report is an XML file. For example, following the modification of a column of a table, the file is the following:
DACPACs make it much easier to manage the database's lifecycle by allowing you to create or update a database using a single file that describes the desired schema. I strongly advise you to take a closer look at dacpac, unless you already have another tool that meets this need.
Note for developers: SqlPackage.exe (and associated DLLs) is a .NET application. So you can use it in your applications if you need it. The files are probably already on your disk C:\Program Files (x86)\Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB .
Do you have a question or a suggestion about this post? Contact me!
