Support Center

Getting Started [Legacy Projects]

Last Updated: May 02, 2013 11:27PM AEST

This tutorial covers an end-to-end database development and deployment scenario using the legacy DataDude-based projects.

By the end of this tutorial you will have:

 

  • Created a new database containing a table object (and/or imported an existing database)
  • Learned how to edit your deployment scripts to gain control over your database's evolution
  • Source-controlled your database project
  • Deployed your database to a shared test server

 

1. Create a new ReadyRoll project


After installing ReadyRoll, open Visual Studio 2010 and click File... New.... Project. From the New Project dialog, click the Database node from the list of installed templates, then select "ReadyRoll SQL Server Database Project" and click OK.

Note that there is no need to select a specific version of SQL Server to target; this is detected automatically by ReadyRoll in the next step.




 

2. Configure a Sandbox Connection


When the project is created, the ReadyRoll DBSync tool window should automatically appear in the lower pane within Visual Studio (parallel to the Errors List tool window). If it doesn't show, click View... ReadyRoll DBSync.

Within the DBSync window, click Specify Sandbox Database Connection. This opens a dialog that will allow you to configure the  connection to a SQL server instance that will contain your new database. If you want to import an existing schema, specify the Server Name and Database Name for your existing database.

Ideally, the server you connect to should be an isolated test environment (i.e. a Sandbox database server such as localhost\SQLEXPRESS), rather than a shared development environment. While ReadyRoll supports either type of configuration, the chances of your work colliding with someone else's changes increases significantly in a shared environment (see this great article from Troy Hunt for more information on the benefits of using a Sandbox environment).

Before clicking OK, click "Test Connection" to verify your settings.

Note: If your database does not yet exist, you will receive a "This database cannot be tested because the specified database does not exist" message. This is normal and can be safely ignored.




 

3. Setup your New/Existing Database


Each time you create a project, ReadyRoll will need to perform a minor amount of setup to get it ready for use. This involves adding a special log table to your database (called "[dbo].[__MigrationLog]") which ReadyRoll relies on to keep track of deployments to your database.

Additionally, when you first set up your database, ReadyRoll will create a separate copy of your database called the Shadow database. By default, this database will be contained within the same server as specified in the previous step and named [ProjectName]_[UserName]_SHADOW. This database is purely a design-time artifact and is automatically maintained by ReadyRoll as part of project build and synchronization operations only. However, unlike the log table, the Shadow database is not deployed to your Production server.

To proceed with your database's initial setup, click the Deploy button in the DBSync tool window (or click Buid... Deploy [ProjectName]).



Once your project has deployed successfully, the tool window will display a Start Synchronization button, which will allow you to start either developing or importing your database (if your project fails to Build/Deploy, check the Output tool window for details).



If you're working with a blank new database you'll get a list of objects like this:

 

Jump to step 5 if this is the case: your new database is now ready for development!


 

4. Import an Existing Schema


This step applies if you are working with a pre-existing database.

To begin importing objects from your configured Sandbox database, click Start Synchronization within the DBSync tool window. For an existing database, all supported objects will then be listed within the tool window:



The columns displayed are:
  • Checkbox: Selects the item(s) to be included in the synchronization
  • Object Type: The type of SQL Server object to be imported
  • Object Owner: The owner of the object in the source database
  • Object Name: The name of the object in the source database
  • Action: What will occur when the Synchronize button is clicked (Initially, all objects will appear as "Add to Project", as each object will be added to the Visual Studio project as a discrete file, e.g. Schema Objects\Tables\dbo.Customer.sql)
  • Change Summary: Descriptive pseudo-code for each of the SQL commands to be included in the generated deployment script

Un-select any objects you do not want to import, and when ready, click Synchronize to add all the objects to your project and generate a deployment script. The generated script is also included in your project, and can be further edited to your liking once the synchronization is complete.



If you click "Edit Script" you'll notice that each statement is preceded with an existence conditional:

 

This statement is added by ReadyRoll to prevent collision with existing objects during your database deployment. We'll cover more about collision protection in the next step.


 

5. Add or Modify Objects in your Database Project

When you want to add or modify schema objects within your database project, you have two options for doing so:

A) Edit your database schema Online using the tool of your choice (Recommended)
B) Edit your database schema Offline within Visual Studio

Method A) is the recommended approach in most scenarios because you get to see results of your changes immediately within your database, and you are not limited to the database authoring toolset within Visual Studio. Method B) is covered in the next step of the tutorial.

To try the Online method, follow these steps to use SQL Management Studio to add some objects to your database:

1. Launch SQL Management Studio, connect to your Sandbox database server and expand your new database in the Object Explorer.

2. Right-click the Tables node and select New Table.



3. Add a new table called "MyTable" and add an auto-seeded ID column as a Primary Key and a Description column, unchecking Allow Nulls, i.e.



(Or run this SQL against your database to create a sample table)
 
	CREATE TABLE [dbo].[MyTable](
    [MyTableID] [int] IDENTITY(10000,1) NOT NULL,
    [MyDescription] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED ([MyTableID] ASC)
) ON [PRIMARY]

4. Switch back to the DBSync tool window in Visual Studio and click Refresh to view a list of pending objects. This causes ReadyRoll to perform a 3-way comparison between your Project, your Sandbox Database and the Shadow Database.



5. The new table will be shown in the list, pre-selected for synchronization. Click Synchronize to import the Table definition and have a Deploy-Once script generated and added to your Project.



The DBSync tool-window will have updated to reflect the fact that the Project is now in-sync with your Sandbox Database.

This is a good point to check your database project into source control. How you do this depends on your source control provider, however if you don't currently have a tool integrated with your copy of Visual Studio, may we recommend VisualSVN (Subversion), VisualHG (Mercurial/Kiln) or Git Extensions for Visual Studio

6. Now try modifying the table by adding a new column. Switch back to SQL Management Studio, expand "MyTable", right-click Columns and select New Column.



9. Name the column "DateCreated", uncheck Allow Nulls and specify a Default Value of '2012-02-06' (including quotes).



(Or run this SQL against your database to add the new column)
ALTER TABLE dbo.MyTable ADD
DateCreated datetime NOT NULL 
     CONSTRAINT DF_MyTable_DateCreated DEFAULT '2012-02-06'
GO

10. Switch back to the ReadyRoll DBSync tool window in Visual Studio and click Refresh. Your database change will appear as a pending item to be synchronized with your project.



11. Click Synchronize to import the table object change.



Whenever you synchronize from your Sandbox database, ReadyRoll will update or add to your Schema Object declarations (which describe the target state of your database as a series of unordered files, one per database object) and also generate a Deploy-Once Script (which migrates your database from one state to the next as a series of alphanumerically ordered T-SQL scripts).

12. Click Edit Script to open the newly-created Deploy-Once Script for editing.



Read more about how to author Deploy-Once migration scripts.
 
6. Deploy your Database to a Test Server

Once you have reached the stage where your changes are ready to be shared with others, you may want to check your project into source control and deploy your database to a test server.

There are a few different ways you can perform a deployment onto a separate database server:

A) Add your project to a Continuous Integration server (such as TeamCity), specifying the Build & Deploy targets
B) Perform a Build within Visual Studio and execute the generated SQLCMD within SSMS manually
C) Use a separate build configuration within your solution (e.g. Release) and perform a Deploy directly within Visual Studio

Method A) is the generally considered the best approach in nearly all scenarios, as it avoids the possibility of creating snowflake builds, however in order to illustrate how the ReadyRoll deployment process works this tutorial will focus on method C).

1. Within Visual Studio, switch to the Release configuration of the Solution containing your ReadyRoll Database Project.


2. Click View... ReadyRoll DBSync to show the DBSync tool window. Notice that you are once again requested to specify a Database connection (Visual Studio stores a separate connection string for each of your Configurations i.e. Debug, Release etc).

In this case, specify the target of your database deployment, e.g. a test database server.



3. When you click OK, the DBSync tool window will update, reflecting that there is some deployment work to do.



4. Switch to the Error List tab and toggle Messages ON to see what deployment actions are pending. In the case of a new database project that has not been deployed, you'll see a message like this:



Otherwise, one Message per pending Deploy-Once Script will be shown, for each applicable project in your solution. This will be a common sight if other team members are also making changes to this database project.

5. To go ahead with the deployment, click Build... Deploy [Project Name].



Note that the deployment process outputs progress of each Deploy-Once Script as it executes, along with any errors encountered.

6. After the deployment has completed, switch to SQL Management Studio to confirm that the changes were successful. The sample table created in the earlier steps should now appear on the test server as follows:



You now have an end-to-end database deployment process!

Note: Don't forget to switch back to the Debug configuration before making further changes to your database.



In subsequent tutorials we will cover other methods of establishing a deployment process for your database, including adding your database to Continuous Integration and handing off scripts to your Database Administrator or peers for review or manual deployment.
readyroll.support@red-gate.com
http://assets0.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