Migrations Using Visual Studio SQL Server Database Project

Setting Up the Migration Project

Add a new project to the Visual Studio solution. This project will initially be empty, without assembly references or scaffold files - it appears everything is native to Visual Studio. To this we need to add the connection to the database the schema is to be imported from. There will be a menu option for ‘Import’ -> ‘Database’. After the connection is made, the project will be populated by a SQL script for each table, and, by default, a few other scripts to apply or update security policies for the database.

Making Database Schema Changes

If we double-click on a given script, the main window will display the script and designer views. Either one could be used for modifying the schema and/or fields. Another view of a given database table can be displayed in the SQL Server Object Explorer window, if we select ‘View in Object Explorer’.

After changes are made, Visual Studio can display a comparison between the local schema and that on the target database, using ‘Schema Compare…’. This might take a few moments, if it’s running through a large number of tables and .sql files.

Updating the Target Database

There are three ways I can see of using this project to update the database:

• The script(s) can be run manually on the database server.
• We can build/rebuild the project, which will generate a DACPAC file in /bin/Debug - this doesn’t appear to work in SQL Server Management Studio v18, but I’m guessing the aim here is to use the file in some automated Azure process.
• The Publish feature will attempt to run the scripts on a target database server to push the schema update.

While the scripts listed in Solution Explorer are CREATE TABLE scripts, the ones generated in the Schema Compare run the ALTER TABLE command.

Other Features: The ‘Snapshot Project’ feature will also generate a DACPAC file.

Testing

I have done some cursory testing, by attempting various updates to a couple of the tables on the target database. For one of the tables I changed the data type of the id field to int, and this failed because the new data type was incompatible with the existing data, and a forced update would have most likely caused data loss. It is possible to override these checks in the Schema Compare Options, if the current settings are blocking the desired changes. Next, I added a column called ‘ExtraTest’ to both tables, and used the Update option in the Schema Compare window to push the update. The columns were added successfully without any loss of data.

#VisualStudio #Migrations #SQL #Database