Support Center

Getting Started [Tutorial]

Last Updated: May 01, 2014 09:23PM AEST
This article has moved to a new home. For up-to-date documentation please continue reading at http://doc.ready-roll.com/display/RRSQLDOC/Getting+started




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.

In this case, we're going to call the project "ContractManagement".


Once the new project is initialised, click View... SQL Server Object Explorer.

The new database will have been created automatically by the project system.
 
The database is now ready for editing using the SQL Server Data Tools (SSDT)!
 
 

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.

 

Expand the new database in SQL Server Object Explorer, right-click the Tables node select Add New Table...


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.

 
When you want to deploy your new table, click the Update button on the designer's toolbar to display the change preview dialog. 

 
The list of changes to be made to your database will be displayed in the Preview Database Updates dialog:
In this case, we want to capture the actual T-SQL for the change, so continue by clicking Generate Script.


This will cause a new script to be opened for preview prior to deployment:


The script is also added to your project:

 
When ready to deploy, click Build... Deploy Solution.

 
You can view the results of the deployment in the Output window:

 
Once deployment has completed, the SSOX window will refresh and reveal the new table within the database schema:


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.
 
 
Once your object has been successfully deployed, it's highly recommended that you close any open designers. This is because the designers in SQL Server Data Tools keep an in-memory copy of your schema, so by closing the designers, you refresh the cached copy of your database. If prompted to save changes you may click "No" because the changes will have already been applied.


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.
 
 
When ready to deploy your view to the schema, click Update.


This time instead of clicking Generate Script, click Update Database to deploy your object to the schema.
Note that the object appears immediately within the SSOX window. You can continue to make Online changes to the view, using the view object editor or directly within a query editor window if you prefer.
 

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:
The script itself it not actually executed against the Sandbox database: its purpose is to allow your changes to be deployed onto another server (i.e. it would only be executed on your Sandbox if you were to drop & redeploy your Sandbox database).

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.


 

Summary

This 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.


 

Further reading

readyroll.support@red-gate.com
http://assets2.desk.com/
false
readyrollsql
Loading
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
about
false
Invalid characters found
/customer/en/portal/articles/autocomplete