SQL Change Automation 3

This page helps you get started with developing and deploying databases with SQL Change Automation. It takes you through different methods of working with migrations in SQL Change Automation projects.

Method 1: create new objects by using the Visual Studio designers to generate migration scripts in the project.
This is a useful method when you're refactoring objects, because changes are captured in the generated script. The trade-off is that you may end up with many scripts in your project, increasing your team's maintenance and code-review overheads.
Method 2: make online changes to your database, and import the new/modified object(s) into the project when ready.
This is a useful method if you want to make multiple changes to your database without scripting a migration for each change. The trade-off is that, given the process used to generate scripts is limited to comparing the before-and-after state of the schema, some fidelity in the changes may be lost. For example, if you add a nullable column to a table, update it with data and then make the column NOT NULL, the generated script will fail to execute as it will lack the intermediate step of adding the nullable column (in such cases, an import should be performed after performing the first step).

In this tutorial we'll:

Create a SQL Change Automation project in Visual Studio
Design a table and generate your first migration (method 1)
Create a view by editing the database online, and post-scripting a migration (method 2)

If you want to get started with an existing database, see Working with an existing database.

Create a SQL Change Automation project

In Visual Studio, from the File menu, select New > Project.
The New Project page is displayed:
From the Installed > Other Languages > SQL Server menu, select SQL Change Automation Project.
Enter the project name AdventureWorks.
This will also be the default name of your database.
You will be presented with a setup wizard. Follow the instructions to set your development database.
Note
You only need to specify a database in the area marked Development. Do not specify anything under the area marked as Deployment Target. This is used for specifying an existing database you would like to create a baseline schema from. For more information see Working with an existing database.
Once the new project is initialized, from the View menu, select SQL Server Object Explorer.

This will allow you to view objects in the development database you specified in the wizard.

Design a table and generate your first migration

In this step, we'll use Visual Studio designers to deploy a table in the newly-created schema.

Within the SQL Server Object Explorer window, expand the SQL Server node and expand the appropriate SqlLocalDb instance for your version of Visual Studio.
LocalDb Instance
An instance of SqlLocalDb is created automatically when you install Visual Studio. The name of the instance varies depending on which version of Visual Studio you have installed:
Visual Studio 2015 prior to Update 5: (localdb)\ProjectsV12
Visual Studio 2015 Update 5 onwards: (localdb)\ProjectsV13
Visual Studio 2017: (localdb)\ProjectsV13
Expand the Databases node and then expand the AdventureWorks database.
Right-click on Tables and select Add New Table.

The table designer is displayed. It contains two panes:
- an upper pane that you can use to add new columns, specify data types, default values, constraints, etc
- a lower pane that displays the object's declarative T-SQL, and lets you set the name of the table
In the lower pane, set the name of the table to Customer by editing the CREATE TABLE statement:
In the upper pane, add columns to the table and set a primary key, as follows:

You can also right-click on properties to set table metadata, such as foreign keys, constraints and indexes:

Even though this contains the DDL to deploy the object, the T-SQL will always display a CREATE statement, even if you modify the object after deployment.
We'll cover how SSDT generates an ALTER statement to sync table modifications later in the tutorial.
To deploy the table, on the designer's toolbar, click Update.

The Preview Database Updates dialog displays the changes that'll be made to the database:
Click Generate Script.
The new script is displayed so you can preview it prior to deployment:

The script is also added to your project:
Once you're ready to apply the changes to your database, from the Build menu, select Deploy Solution:

View the deployment results in the Output window:

Once deployed, the SQL Server Object Explorer window displays the new table in the database schema:

The Migration Status bar above the T-SQL script editor confirms the deployment:
You can also deploy by starting the solution [F5]. However, if you happen to have a different project set as the Start-up Project in your solution, you'll need to add a reference from that project to the SQL Change Automation project to make sure that the database is also built and deployed.
Once your object has been successfully deployed, close any open designers.The designers in SQL Server Data Tools keep an in-memory copy of your schema, so closing them refreshes the cached copy of your database.

If you're prompted to save changes, click No. Changes have been applied already.

You've now created new objects by deploying changes via the project as migration scripts. If you want to modify existing objects, the process is the same.

Create a view using the online-editing method

Instead of scripting a database object before deployment, we're now going to save changes directly in the database, and then create a migration script by importing the changes into the project at the end.

To add a view to the database:

Within the SQL Server Object Explorer window, expand the SQL Server node and expand the appropriate SqlLocalDb instance for your version of Visual Studio (see the list above).
Expand the Databases node and then expand the AdventureWorks database node.
Right-click on Views and select Add New View.
Enter a query to create the view. The code editor provides IntelliSense to help you pick column names.
When you're ready to deploy your view to the schema, click Update.
Click Update Database to deploy your object to the schema:

Changes are applied directly to the target database, skipping the 'generate script' step. You can make additional edits if necessary, before you generate a delta script of pending changes.
If you prefer, you can use SQL Server Management Studio (instead of Visual Studio) to make online changes to your database.
Once you've made changes in SSMS, switch back to Visual Studio and use the SQL Change Automation tool-window to import them.
To script your changes, from the View menu, select SQL Change Automation:

The SQL Change Automation tool-window is displayed. You can use it to import schema objects from your sandbox (i.e. target) database, and revert changes to a previous known "good" state.
In the SQL Change Automation tool-window, click Refresh:

SQL Change Automation starts to compare the sandbox database to your project. The list of pending objects is displayed.
To display the object's contents in the Object Diff window, double-click on the view:
To import the changes into the project, click Import and generate script.
The generated migration script is added to your project.

And also opened within Visual Studio in 'preview' mode:

The script isn't executed against the sandbox database: its purpose is to allow your changes to be deployed to another server. I.e., it would only be executed on your sandbox if you were to drop and redeploy your sandbox database. For more information about the target database, see Development and shadow databases.
Although any edits to the script will not be applied to your Sandbox database, you can make changes to the T-SQL if the generated script doesn't do what you expected. For example, if you rename a Table, SQL Change Automation will actually script a DROP/CREATE instead of an EXEC sp_rename.
To test the script, click Refresh (Verify Script) within the tool-window.
The script is executed against the Shadow database in order to test the integrity of the migration. If the verification of your script is successful, the tool-window will update, indicating that all objects have now been imported.
You've now made online changes to your database, and imported the new/modified object(s) into the project.

Summary

You've now tried two different ways of making database changes and creating migration scripts in your SQL Change Automation projects.
Depending on how you like to work, you may choose one approach over the other. You may even combine the two methods: edit online when you want to create new tables or modify programmable objects, and generate migrations directly when you want to refactor table objects.

What's Next

Now you've created a project, learn how to set up automated validation and testing. See Automating database changes.