Tags: VisualStudio SQLServer
When building an application I also want to manage changes to the database from source control. One of the ways of doing this is with a Visual Studio SQL Server Database project. It integrates well with source control and can be integrated into automatic deployments.
All screenshots and used techniques that are written in this post are available within Visual Studio 2017 community edition and SQL Server 2017 express edition that are both free downloads.
The SQL Server Database project type can be found in the New Project window under de SQL Server templates as shown below:
This project will just create an empty project without any objects.
You can now start adding new items like tables, views, stored procedures and all kinds of other SQL Server database objects. A good practice is to put these objects into folders representing the different database objects for better maintainability. You can also import objects from an existing database by right-clicking on the project and select Import from the available options ( the import database and dacpac options are only available when the database project is still empty).
When creating an object like a table, Visual Studio will show an editor as shown in the image below. You can leverage the designer combined with the properties window to define fields and column settings or just type in the SQL statements in the T-SQL window on the lower left side.
Below you can see an example of how I created a simple Customer table.
After creating or changing objects in your project you can publish these changes to a database with the Schema Compare option in the project context menu.
Use the select target drop down to go through the dialogs to select the database that you want to publish to.
After selecting the database the Compare button on the upper left will become enabled and you can click it to kick off the compare between your database and the project. You can click on an object to see the changes between the specific objects.
If you want to update the database to reflect the changes made, click the update button. You can deselect unwanted changes with the checkboxes in the Action column of the SchemaCompare tab. Visual Studio will show if the changes were successful and give you some options to view details.
When the update fails, for example when you add a new non-nullable column without a default value or deleting a column with data, the results will show like below:
By clicking View Results you see the script that was run but also a tab that shows the message of what went wrong.
You can either force the data loss by changing the schema compare options via the gear icon of the schema compare tab as shown below or run an update script to mitigate the data loss.
In my case, I had a faulty existing Countr column that did not match a newly created Country column. To get Schema Compare to update the database I had to create a data migration pre-upgrade script as shown below.
The update is a dynamic statement because the script will not compile with a column name that does not exist before the upgrade script has been run. Notice that the IF statement is very specific to this change in the database. This is because we can add this script as a Pre-Deployment Script to the database project.
Even if we have to run the script manually when using a schema compare, Pre- and Post-Deployment scripts will be run automatically when running the database project (F5) on a database, or when deploying the bacpac file that is the output of this project (found in the bin folder after building the project). The connection string that is used when running/debugging the project can be found and changed in the properties pages of the project in the Debug tab. There is also a SQL Script in the output folder that can be used to upgrade a database. The Pre- and Post-Deployment scripts are contained within the SQL script that is the output of the project.
Once all known instances of the database are updated with the specific Pre-Deployment script. It can be removed to avoid any clutter of the project. The specific IF statement will ensure that the statements are only run on a database that can do the specific data migration and skips it when creating a completely new instance.
These projects are really only for managing schema information, not data. If you want to secure your data, you need to create regular (scheduled) backups of your database. We can use Post-Deployment scripts to add seed data to tables when they are empty. An example is shown below:
So now we have project that can be plugged into source control. All the objects are stored as SQL files and work well in source control comparing and merging. If you have any manual scripts, that you might want to run periodicly, store them with the solution in a seperate solution items folder so they will not be compiled into the project output.
The dacpac files that are the output of the project can be used by an automated or manual deployment process to change the databases in your environments. Once you update the database, it will remember the version of the dacpac used to update the database. The project has a refactor log file reflecting all changes made in a sequential form. Once a dacpac version has been applied with the changes and you want to run the dacpac again after making manual modifications to the database, it will not be able to restore properly to the intended database state. This is why during development you will usually be using the Schema Compare functionality and running Pre- and Post-Deployment scripts manually until all changes are ready and the dacpac reflecting the next sequential update will be pushed out to your testing and eventually production environments.
If you have a dacpac file you can deploy it in manually in SQL Server Management Studio with the option below for a new database:
Or use the option below on an existing database to upgrade it:
If you are using VSTS for automatic deployments, you can add a step to deploy dacpac files.