This tutorial covers an end-to-end database development and deployment scenario.
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
A Getting Started video (3:16) is also available.
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 (Online method)
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,
[MyDescription] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED ([MyTableID] ASC)
) ON [PRIMARY]
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
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.
A few notes about Deploy-Once scripts:
-
Deploy-Once scripts are a first-class artifact of your project, responsible for the entire deployment of your database. This type of deployment is referred to as imperative deployment, because you have statement-by-statement control over how your database evolves. This is opposed to traditional database projects which use declarative deployment; a method that involves comparing the project to the target database at deploy time in order to generate a migration script. More information on this topic is available in the ReadyRoll Infosheet.
-
The Xml fragment in the header comment contains a unique identifier for the migration. ReadyRoll uses this GUID to ensure that the script is executed a single time against the target database.
-
The SQL that follows contains an "IF EXISTS" object existence check, preceding the actual table object change (in the above example, the [DateCreated] column will be added only if it doesn't already exist). This is just an added precaution to avoid collision between your changes and those of a fellow team member (e.g. if a colleague also adds the [DateCreated] column to [MyTable]). For stricter deployment, remove the existence checks from the Deploy-Once script.
-
You can add or remove any statement from a Deploy-Once script, as long as the state of the database matches that of your Schema Object declarations at the completion of deployment. For example, if you renamed an object in your database but ReadyRoll generated a script that performed a DROP/CREATE, you can replace that script with a "EXECUTE sp_rename" statement because the end result would yield the same schema. You can also add DML (SELECT, UPDATE, INSERT) statements to your script in order to perform migrations like populating a new column with a data from another table.
- If you make a change to a Deploy-Once script, performing a Build within Visual Studio will test that the change is not only syntactically correct, but that it is also imperatively correct, i.e. that the script will have the intended effect when deployed to the target database. ReadyRoll does this by executing your script against the Shadow database; a separate copy of your database built solely from your Deploy-Once scripts. It then validates that your Schema Object declarations are in-sync with your Shadow database. Note that the Sandbox database is left intact during a build (only the Deploy action modifies your Sandbox).
In the next step of the tutorial we'll modify our new column's default constraint in offline mode.
6. Add or Modify Objects in your Database Project (Offline method)
In the previous step we used SQL Management Studio to modify the Sandbox database and import those changes into the project. An alternative approach to this is to prepare your schema changes directly within Visual Studio, prior to deploying to your Sandbox database.
This technique is useful when you want to perform a number of minor changes to an existing schema. For example, reformatting some stored procedures.
In this example, we'll work in Offline mode to change the default constraint we added in the previous step.
1. Within the Solution Explorer In Visual Studio, expand the Schema Objects\Tables subfolder of your ReadyRoll project and open "dbo.MyTable.sql" for editing.
2. Replace the default value for the [DateCreated] column with "getdate()" (excluding quotes) so it looks like below and Save.
3. Now click the Refresh button in the ReadyRoll DBSync tool window. The [MyTable] object should appear as a change originating from the Project Model:
4. Ensure the [MyTable] object is checked and click Synchronize. You will be presented with a confirmation dialog, asking you whether you'd like to deploy the change immediately to your Sandbox database, or edit the file prior to deployment.
5. In this case the default constraint change is quite straightforward, so click Deploy Now if you are happy to proceed with the Sandbox deployment.
6. Once the Deployment Complete notification appears, switch to SQL Management Studio and navigate to your Sandbox database. Right-click the [MyTable] table and click Design. If you then select the [DateCreated], you should now see that the Default Value column has changed to "getdate()".
While working Offline, you may like to try more advanced operations like renaming Table or View objects. However depending on your comfort level with the TSQL-based Schema Object declarations in Visual Studio, you may prefer to always follow the Online approach detailed earlier in this tutorial.
7. 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.
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.