Walkthrough: Create and Deploy a New Version-Controlled Database

Article
02/04/2013

In this walkthrough, you create a simple database that contains two tables and a stored procedure. This process requires you to create a database project, create database objects in that project, and then build and deploy your changes to a database server. By creating a database project, you can put your database schema under version control by putting the database project under version control.

This topic is not intended to demonstrate how to deploy into a production or preproduction environment. To deploy into those types of environments, you would typically use either the VSDBCMD.EXE utility, or you might manually deploy by using the deployment script. For more information, see the following topics:

The major steps for this walkthrough are as follows:

Create a database project.
Create the database tables.
Create the indexes, keys, and constraints for those tables.
Create two stored procedures.
Configure database project properties.
Build the database project.
Deploy the database project.
Put the database project under version control.

Prerequisites

To perform this walkthrough, you must log on with an account that has permissions to create a database on a database server that is running SQL Server 2008.

To create the database project

On the File menu, point to New, and click Project.

The New Project dialog box appears.
Under Installed Templates, expand the Database node, and then click SQL Server.

Note

If you are using Visual Studio Professional, instead look under Installed Templates, expand the Database node, expand the SQL Server node, and click Advanced.
In the list of templates, click SQL Server 2008 Database Project.
In Name, type ProductsDBProductsDB.
Select the Create directory for solution check box.
Accept the default values for Location, Solution Name, and Add to Source Control, and click OK.

Note

You could, at this point, add your solution to version control. In this walkthrough, you add the solution to version control in the final procedure.

The new database project, ProductsDBProductsDB, appears in Solution Explorer.
On the View menu, click Database Schema View.

Schema View appears if it was not already visible.

Next you add a schema to the database project.

To add the Sales schema to your database project

In Schema View, expand the ProductsDBProductsDB node, and click the Schemas node.
On the Project menu, click Add New Item.

The Add New Item dialog box appears.

Note

You can also right-click the ProductsDBProductsDB project in Schema View, point to Add, and click Schema.
In Templates, click Schema.

Note

In the Categories list, you can click Tables and Views to more easily find the template for a table.
In Name, type Sales as the name that you want to give the new schema.
Click Add to add the schema to your database project.

Solution Explorer shows the new file for the schema in your database project. Schema View shows the new schema object. The Transact-SQL editor appears, displaying the definition for your new schema.

Next you add the tables to the database project.

To add the Customer table to your database project

In Schema View, expand the Sales node, right-click the Tables node, point to Add, and click Table.
In Name, type Customer as the name that you want to give the new table.
Click Add to add the table to your database project.

Solution Explorer shows the new file for the table in your database project. Schema View shows the new table object. The Transact-SQL editor appears, displaying the definition for your new table.

In the Transact-SQL editor, modify the table definition to match the following example:

CREATE TABLE [Sales].[Customer] (
    [CustomerID] INT IDENTITY (1, 1) NOT NULL,
    [CustomerName] NVARCHAR (40) NOT NULL,
    [YTDOrders] INT NOT NULL,
    [YTDSales] INT NOT NULL
);

On the File menu, click Save Sales.Customer.table.sql.
In Schema View, expand the Sales.Customer node.
In the Sales.Customer table, expand the Columns node.

The four columns that you defined in the Transact-SQL editor appear.

Next you add a primary key to the Customer table.

To add an index to the Customer table

In Schema View, right-click Sales.Customer, point to Add, and click Index.

The Add New Item dialog box appears.
In Templates, click Index.
In Name, type IX_CustomerCustomerName as the name that you want to give the new index.
Click Add to add the index to the Categories table.

Solution Explorer shows the new file for the index in your database project. Schema View shows the new index object. The Transact-SQL editor appears, displaying the definition for your new index.

Note

In Schema View, the icon for the index appears with the red circle that contains a white "x," which indicates that the default definition contains an error. This behavior is expected because the default definition references the "column_1" column, which does not exist.
In the Transact-SQL editor, modify the index definition to match the following example:
```
CREATE INDEX [IX_CustomerCustomerName]
    ON [Sales].[Customer]
(CustomerName)
```
On the File menu, click Save Sales.Customer.IX_CustomerCustomerName.index.sql.

The error indicator disappears from the icon, which indicates that the index definition is now valid.

Next you add a primary key to the Customer table.

To add a primary key to the Customer table

In Schema View, right-click Sales.Customer, point to Add, and click Primary Key.

The Add New Item dialog box appears.
In Templates, click Primary Key.
In Name, type PK_CustomerCustomerID as the name that you want to give the new primary key.
Click Add to add the primary key to the Customer table.

Solution Explorer shows the new file for the primary key in your database project. Schema View shows the new primary key object. The Transact-SQL editor appears, displaying the definition for your new key.

Note

The icon for the primary key appears with the red circle that contains a white "x," which indicates that the default definition contains an error. This behavior is expected because the default definition references the "column_1" column, which does not exist.

In the Transact-SQL editor, modify the primary key definition to match the following example:

ALTER TABLE [Sales].[Customer]
ADD CONSTRAINT [PK_CustomerCustomerID]
PRIMARY KEY (CustomerID)

On the File menu, click Save Sales.Customer.PK_CustomerCustomerID.pkey.sql.

The error indicator disappears from the icon, which indicates that the primary key definition is now valid.

Next you add the Orders table.

To add the Orders table

In Schema View, click ProductsDBProductsDB.
On the Project menu, click Add New Item.

The Add New Item dialog box appears.

Note

You can also right-click the ProductsDB project in Schema View, point to Add, and click Table.
In Templates, click Table.
In Name, type Orders as the name that you want to give the new table.
Click Add to add the table to your database project.

Solution Explorer shows the new file for the table in your database project. Schema View shows the new table object. The Transact-SQL editor appears, displaying the definition for your new table.

In the Transact-SQL editor, modify the table definition to match the following example:

CREATE TABLE [Sales].[Orders] (
    [CustomerID] INT NOT NULL,
    [OrderID] INT IDENTITY (1, 1) NOT NULL,
    [OrderDate] DATETIME NOT NULL,
    [FilledDate] DATETIME NULL,
    [Status] CHAR (1) NOT NULL,
    [Amount] INT NOT NULL
);

On the File menu, click Save Sales.Orders.table.sql.
Expand the Sales.Orders node in Schema View.
Expand the Columns node in the Sales.Orders table.

The columns that you defined in the Transact-SQL editor appear.

Next you add an index to the Products table.

To add a primary key to the Orders table

In Schema View, right-click Sales.Orders, point to Add, and click Primary Key.

The Add New Item dialog box appears.
In Templates, click Primary Key.
In Name, type PK_OrdersOrderID as the name that you want to give the new primary key.
Click Add to add the primary key to the Orders table.

Solution Explorer shows the file for the new primary key in your database project. Schema View shows the new primary key object. The Transact-SQL editor appears, displaying the definition for your new primary key.

Note

The icon for the primary key appears with the red circle that contains a white "x," which indicates that the default definition contains an error. This behavior is expected because the default definition references the "column_1" column, which does not exist.

In the Transact-SQL editor, modify the primary key definition to match the following example:

ALTER TABLE [Sales].[Orders]
    ADD CONSTRAINT [PK_Orders_OrderID] PRIMARY KEY CLUSTERED ([OrderID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);

On the File menu, click Save Sales.Orders.PK_Orders_OrderID.pkey.sql.

The error indicator disappears from the icon, which indicates that the primary key definition is now valid.

Next you add a foreign key to the Orders table.

To add a foreign key between the Orders table and the Customer table

In Schema View, right-click Sales.Orders, point to Add, and click Foreign Key.

The Add New Item dialog box appears.
In Templates, click Foreign Key.
In Name, type FK_OrdersCustomer as the name that you want to give the new foreign key.
Click Add to add the foreign key to the Orders table.

Solution Explorer shows the file for the new foreign key in your database project. Schema View shows the new foreign key object. The Transact-SQL editor appears, displaying the definition for your new foreign key.

Note

The icon for the foreign key appears with the red circle that contains a white "x," which indicates that the default definition contains an error. This behavior is expected because the default definition references the "column_1" column, which does not exist.

In the Transact-SQL editor, modify the foreign key definition to match the following example:

ALTER TABLE [Sales].[Orders]
    ADD CONSTRAINT [FK_Orders_Customer_CustID] 
    FOREIGN KEY ([CustomerID]) REFERENCES [Sales].[Customer] ([CustomerID]) 
    ON DELETE NO ACTION ON UPDATE NO ACTION;

On the File menu, click Save Sales.Orders.FK_OrdersCustomer.fkey.sql.

The error indicator disappears from the icon, which indicates that the foreign key definition is now valid.

Next you add a check constraint to the Products table.

To add a check constraint to the Orders table

In Schema View, click Sales.Orders, point to Add, and click Check Constraint.

The Add New Item dialog box appears.
In Templates, click Check Constraint.
In Name, type CK_OrderStatus as the name that you want to give the new check constraint.
Click Add to add the constraint to the Products table.

Solution Explorer shows the file for the new constraint in your database project. Schema View shows the new constraint object. The Transact-SQL editor appears, displaying the definition for your new constraint.

Note

The icon for the constraint appears with the red circle that contains a white "x," which indicates that the default definition contains an error. This behavior is expected because the default definition references the "column_1" column, which does not exist.

In the Transact-SQL editor, modify the constraint definition to match the following example:

ALTER TABLE [Sales].[Orders]
ADD CONSTRAINT [CK_OrderStatus] 
CHECK  ([Status] IN ('O','X','F','B'))

On the File menu, click Save Sales.Orders.CK_OrderStatus.chkconst.sql.

The error indicator disappears from the icon, which indicates that the constraint definition is now valid.

Next you add two stored procedures to the project.

To create a stored procedure that adds a customer

In Schema View, under Schemas, right-click Sales, point to Add, and click Stored Procedure.
In Name, type uspNewCustomer as the name that you want to give the new stored procedure.
Click Add to add the stored procedure to your database project.

Solution Explorer shows the file for the new stored procedure in your database project. Schema View shows the new stored procedure object. The Transact-SQL editor appears, displaying the definition for your new stored procedure.

In the Transact-SQL editor, modify the stored procedure definition to match the following example:

CREATE PROCEDURE [Sales].[uspNewCustomer]
@CustomerName NVARCHAR (40)
AS
BEGIN
INSERT INTO [Sales].[Customer] (CustomerName) VALUES (@CustomerName);
SELECT SCOPE_IDENTITY()
END

On the File menu, click Save Sales.uspNewCustomer.proc.sql.
In Schema View, expand the Programmability node, and then expand the Stored Procedures node.

The procedure that you defined in the Transact-SQL editor appears.

Next you add a second stored procedure that places a order for a customer.

To create a stored procedure that places an order for a customer

In Schema View, click ProductsDB.
On the Project menu, click Add New Item.

The Add New Item dialog box appears.

Note

You can also right-click the ProductsDB project in Schema View, point to Add, and click Stored Procedure.
In Categories, expand Database Project, and click Programmability.
In Templates, click Stored Procedure.
In Name, type uspPlaceNewOrder as the name that you want to give the new stored procedure.
Click Add to add the stored procedure to your database project.

Solution Explorer shows the file for the new stored procedure in your database project. Schema View shows the new stored procedure object. The Transact-SQL editor appears, displaying the definition for your new stored procedure.

In the Transact-SQL editor, modify the stored procedure definition to match the following example:

CREATE PROCEDURE [Sales].[uspPlaceNewOrder]
@CustomerID INT, @Amount INT, @OrderDate DATETIME, @Status CHAR (1)='O'
AS
BEGIN
DECLARE @RC INT
BEGIN TRANSACTION
INSERT INTO [Sales].[Orders] (CustomerID, OrderDate, FilledDate, Status, Amount) 
     VALUES (@CustomerID, @OrderDate, NULL, @Status, @Amount)
SELECT @RC = SCOPE_IDENTITY();
UPDATE [Sales].[Customer]
   SET
   YTDOrders = YTDOrders + @Amount
    WHERE [CustomerID] = @CustomerID
COMMIT TRANSACTION
RETURN @RC
END

On the File menu, click Save Sales.uspPlaceNewOrder.proc.sql.
In Schema View, expand the Programmability node, and then expand the Stored Procedures node.

The procedure that you defined in the Transact-SQL editor appears.

Next you configure your project settings before you build and deploy the project.

To configure settings for deploying the project

In Solution Explorer, click ProductsDB (the project, not the solution).
On the Project menu, click ProductsDB Properties.

The project properties window appears.

Note

You can also right-click ProductsDB in Solution Explorer and click Properties.
Click the Deploy tab.
In the Deploy action list, click Create a deployment script (.sql) and deploy to the database.
Click Edit to specify the target connection.
Specify the information to connect to the database server to which you want to deploy the ProductsDB database.
In Select or enter a database name, type ProductsDB, and click OK.

Target Connection is populated with the connection string. Note that the Target Database Name is set to ProductsDB.
Accept the default values for the other options.
On the File menu, click Save Selected Items.

Your project build settings are saved.

Next you build your database project.

To build your database project

On the Build menu, click Build Solution.

Your database project builds. If successful, Build Succeeded appears in the status bar and the build results are displayed in the Output window.

Finally, you deploy your database project.

To deploy your database project to your database server

In Solution Explorer, click ProductsDB (the project, not the solution).
On the Build menu, click Deploy ProductsDB.

Your database project is deployed by using the connection that you specified in the build configuration. The message "Deployment succeeded" appears in the Output window and in the status bar.

To check your database project into version control

In Solution Explorer, click ProductsDB (the solution).
On the File menu, point to Source Control, and click Add Solution to Source Control.

At this point, you interact with the installed version control software. This walkthrough provides steps to add your project to Team Foundation Server. If you want to use different version control software, substitute the equivalent steps. If you are using Team Foundation Server, the Connect to Team Foundation Server dialog box appears.
In Connect to a Team Foundation Server, click the server where the team project to which you want to add your solution is found.

Note

If you do not have a team project to which you can add the database project, see Planning and Tracking Projects.
In Team projects, click the team project to which you want to add the database project, and click OK.

The Add Solution ProductsDB to Source Control dialog box appears.
Click OK to accept the default values.

Your database project and the files that it contains are put under version control. Initially, they are still checked out. You must check them in before other team members can access them.
On the View menu, point to Other Windows, and click Pending Changes.

The Pending Changes window appears.
In the Comment field, type Initial database project creation.
In the Pending Changes window, click Check In on the toolbar.

The Check In Progress dialog box appears as the database project and the files that it contains are checked in. The icons in Solution Explorer update to show that the files are checked in to version control.

Next Steps

You can use additional walkthroughs to find out how to work with existing deployed databases.