Skip to end of metadata
Go to start of metadata

This page helps you get started with developing and deploying databases with ReadyRoll. It takes you through different methods of working with migrations in ReadyRoll database projects:

  • Method 1: create new objects by deploying changes via the project as migration scripts
    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.
    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 changes may be misinterpreted by ReadyRoll's DBSync tool, and you may need to modify the script to define changes correctly.

In this tutorial we'll:

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

Create a ReadyRoll database project

  1. In Visual Studio, from the File menu, select New > Project. 
    The New Project page is displayed:

  2. From the Installed > Templates > SQL Server menu, select ReadyRoll SQL Server Database Project.
  3. Enter the project name ContractManagement.
    This will also be the default name of your database. 
  4. Click OK.
  5. Once the new project is initialized, from the View menu, select SQL Server Object Explorer.

    The database has been created automatically by the project system. You can start editing it using SQL Server Data Tools (SSDT).

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.

  1. 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 2010: (localdb)\Projects
    Visual Studio 2012. (localdb)\Projects or (localdb)\ProjectsV11
    Visual Studio 2013: (localdb)\Projects or (localdb)\ProjectsV12
    Visual Studio 2015 prior to Update 5: (localdb)\ProjectsV12
    Visual Studio 2015 Update 5 onwards: (localdb)\ProjectsV13 

  2. Expand the Databases node and then expand the ContractManagement database.

  3. 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
  4. In the lower pane, set the name of the table to Customer by editing the CREATE TABLE statement:
     

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

  6. 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:

  7. Click Generate Script.
    The new script is displayed so you can preview it prior to deployment:

    The script is also added to your project:

  8. 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 widget in 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 database 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:

  1. 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). 
     

  2. Expand the Databases node and then expand the ContractManagement database node.
     

  3. Right-click on Views and select Add New View.
  4. Enter a query to create the view. The code editor provides IntelliSense to help you pick column names.
  5. When you're ready to deploy your view to the schema, click Update.
  6. 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 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 ReadyRoll DBSync tool to import them.

  7. To script your changes, from the View menu, select ReadyRoll DBSync:

    The ReadyRoll DBSync 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.

  8. In the ReadyRoll DBSync tool, click Refresh:  

    ReadyRoll starts to compare the sandbox database to your project. The list of pending objects is displayed.  

  9. To display the object's contents in the Object Diff window, double-click on the view:

  10. To import the changes into the project, click Import (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 Target 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, ReadyRoll will actually script a DROP/CREATE instead of an EXEC sp-rename.
  11. To test the script, click Refresh (Verify Script) within the DbSync tool. 
    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 DbSync tool will update, indicating that all objects have now been imported.
     

  12. 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 ReadyRoll database 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.