This tutorial will introduce you to two different methods of working with migrations in ReadyRoll database projects.
In this tutorial we will:
- Create a new ReadyRoll database project in Visual Studio
- Design a Table and generate your first migration
- Create a View by editing the database Online & post-scripting a migration
Create a new ReadyRoll database project
Within Visual Studio, click File... New Project, and provide a name for the Solution & Project, which will also become the default name for your database.
Once the new project is initialised, click View... SQL Server Object Explorer.
The new database will have been created automatically by the project system.
Design a Table and generate your first Migration
In this step, we'll use the designers within Visual Studio to deploy a table within the newly-created schema.
This will display the table designer, which contains two panes: an upper pane for adding new columns, specifying of data types, default values, constraints, etc, and a lower pane which provides the declarative T-SQL underlying the object (and also allows you to set the name of the table):
Start by adding some columns to the table and setting of a primary key within the upper pane. Notice that the lower pane updates with the T-SQL behind the object as you update items in the upper pane. This also works in reverse, however, to avoid undesired behaviour, it is recommended that you use the upper pane to edit the table whenever possible (for example, changing a column name in the lower pane results in a DROP COLUMN instead of a sp_rename).
NOTE: Despite the fact 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.
The properties window allows for specifying of other table metadata, such as foreign keys, constraints and indexes.
This will cause a new script to be opened for preview prior to deployment:
The script is also added to your project:
The Migration Status widget in the T-SQL script editor will also update, indicating that the script has been applied:
NOTE: You can also deploy by starting the solution [F5]. However, If you happen to have a different project that is set as the Startup Project in your solution, you'll need to add a reference from that project to the database project to ensure that the database is also built & deployed.
This section demonstrated how you can create new objects by deploying changes via the project as migration scripts.
Note that the process is the same for modifying existing objects. In fact, the biggest advantage of using this approach is when refactoring objects, because the intention of change is captured precisely in the generated script. The trade-off is that you may end up with many scripts in your project, which can add overhead to your team's maintenance and code-review activities.
Create a View using the Online-editing method
In this step we'll take a different approach: instead of scripting a database object prior to deployment, we'll save our changes directly in the database, and then create a migration script by importing the changes into the project at the end.
Start by adding a view to the database: in the SSOX window, right-click the Views node and select Add New View.
Note as you type your query, the code editor will provide IntelliSense to help you pick the column names.
This time instead of clicking Generate Script, click Update Database to deploy your object to the schema.
When ready to script your changes, click View... ReadyRoll DBSync.
This will display the ReadyRoll DBSync tool, which you can use to Import schema objects from your Sandbox (i.e. target) database as well as revert changes to a previous known "good" state.
Click Refresh within the ReadyRoll DBSync tool begin the process of comparing the Sandbox database to your project:
The list of pending objects will be displayed. Double-click the view to display the object's contents in the Object Diff window:
Click Import (Generate Script) to synchronise the differences between the Sandbox and project, or alternatively, right-click the project in the Solution Explorer and select Import... Database...
The generated migration script is added to your project and opened in preview mode:
NOTE: 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, ReadyRoll will actually script a DROP/CREATE instead of an EXEC sp_rename.
To test the script click Refresh (Verify Script) in the DBSync tool, build the solution or simply execute the script directly within the T-SQL editor. The action performed in each case will be the same: the script will be executed against the Shadow database to test apply the imported changes. Click Refresh within the DBSync tool to confirm that the Sandbox database is now in-sync with your project.
If you click Refresh in the DBSync tool, the change list should update as follows:
Re-opening the generated script in the T-SQL editor will reveal a change in the migration status:
This section demonstrated how to make Online changes to your database and then import the new/modified object(s) into the project.
The advantage of this approach is that you can make multiple edits within your database without scripting a migration for each individual change, instead importing your changes after the fact. The trade-off is that changes may be misinterpreted by ReadyRoll's DBSync tool, requiring you to modify the script to define the change correctly.
SummaryThis tutorial covered two distinct ways of authoring new database changes and creating migration scripts in your ReadyRoll database projects.
Depending on how you like to work, you may choose one approach over the other. You may even like to try a combination of the two methods: edit Online using the latter approach when creating new tables or modifying programmable objects, and generate migrations directly using the former approach when refactoring table objects.
- Importing an existing database & setting a deployment baseline
- Importing and editing static/domain data
- Enhanced source control support for code objects (stored procs/view/functions etc)
- Publishing/Deploying to a SQL Server instance (via command line)
- Deploying to your databases environments with Octopus Deploy