Walkthrough: Comparing the Schemas of a Database and Database Project

Artykuł
02/04/2013

This topic applies to:

Visual Studio Ultimate	Visual Studio Premium	Visual Studio Professional	Visual Studio Express

In this walkthrough, you compare the schema of a database project with the schema of a database by using Visual Studio. Depending on how your team is using the database project and the database, you might then want to copy schema changes in one direction or the other. You might encounter the following typical scenarios as your team progresses through the life cycle of database development:

Project is Source, and Database is Target. You can use a database project to develop or maintain a database. After you change in the project's schema, you copy the changes to the database, which is hosted on a staging server. Later, your team could deploy the database to a production server.

The comparison that you perform in this walkthrough generates a Data Definition Language (DDL) script from the schema differences. You can then use this script to apply all or part of the database project to the database. For more information, see Propagate changes from the project to the database.
Database is Source, and Project is Target. An error might be found in the schema of a production database, or the schema might become outdated and require an update. This discovery might require your team to apply an urgent update to the database. To keep the project synchronized with the database, you can then import the update into a database project. For more information, see How to: Import Updates from a Database into the Database Project.

This walkthrough illustrates the following tasks:

Setup Phase

Create a database project. The new project starts out empty.
Import a database schema from a script.
Configure, build, and deploy the database project to your database server. The database and the project have identical schemas.

Production Phase

Add a table to the database project. You add a three-column table that is called InternationalShippers.
Compare the two schemas. In this comparison, you designate the database project as the source and the database as the target. Therefore, the results show the InternationalShippers table as a new table in the database project.
Propagate changes from the project to the database. You can propagate the new InternationalShippers table from the database project to the deployed database. If you followed this procedure, you would overwrite parts of the example database.

Note

You can also propagate changes from a database to a database project. For more information, see How to: Import Updates from a Database into the Database Project.

Prerequisites

To complete this walkthrough, you need the following applications and permissions:

SQL Server 2008
Permissions to create and update databases on the target database server
Visual Studio Premium or Visual Studio Ultimate

Setup Phase

Create a Database Project

To create a database project

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

The New Project dialog box opens.
Under Installed Templates, expand the Database node, and then click SQL Server.
In the list of templates, click SQL Server 2008 Database Project.
In Name, type CompareProject, and then click OK.

The CompareProject project, which is empty, opens and appears in Solution Explorer.

Import a database schema from a script

To create a script from which you can import a schema

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

The New File dialog box opens.
In the Categories list, click General if it is not already highlighted.
In the Templates list, click Sql File, and then click Open.

The Transact-SQL editor opens.

Copy the following code, and paste it into the Transact-SQL editor.

PRINT N'Creating Sales...';
GO
CREATE SCHEMA [Sales]
    AUTHORIZATION [dbo];
GO
PRINT N'Creating Sales.Customer...';
GO
CREATE TABLE [Sales].[Customer] (
    [CustomerID]   INT IDENTITY (1, 1) NOT NULL,
    [CustomerName] NVARCHAR (40) NOT NULL,
    [YTDOrders] INT NOT NULL,
    [YTDSales] INT NOT NULL
);
GO
PRINT N'Creating Sales.Orders...';
GO
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
);
GO
PRINT N'Creating Sales.Def_Customer_YTDOrders...';
GO
ALTER TABLE [Sales].[Customer]
    ADD CONSTRAINT [Def_Customer_YTDOrders] DEFAULT 0 FOR [YTDOrders];
GO
PRINT N'Creating Sales.Def_Customer_YTDSales...';
GO
ALTER TABLE [Sales].[Customer]
    ADD CONSTRAINT [Def_Customer_YTDSales] DEFAULT 0 FOR [YTDSales];
GO
PRINT N'Creating Sales.Def_Orders_OrderDate...';
GO
ALTER TABLE [Sales].[Orders]
    ADD CONSTRAINT [Def_Orders_OrderDate] DEFAULT GetDate() FOR [OrderDate];
GO
PRINT N'Creating Sales.Def_Orders_Status...';
GO
ALTER TABLE [Sales].[Orders]
    ADD CONSTRAINT [Def_Orders_Status] DEFAULT 'O' FOR [Status];
GO
PRINT N'Creating Sales.PK_Customer_CustID...';
GO
ALTER TABLE [Sales].[Customer]
    ADD CONSTRAINT [PK_Customer_CustID] PRIMARY KEY CLUSTERED ([CustomerID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);
GO
PRINT N'Creating Sales.PK_Orders_OrderID...';
GO
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);
GO
PRINT N'Creating Sales.FK_Orders_Customer_CustID...';
GO
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;
GO
PRINT N'Creating Sales.CK_Orders_FilledDate...';
GO
ALTER TABLE [Sales].[Orders]
    ADD CONSTRAINT [CK_Orders_FilledDate] CHECK ((FilledDate >= OrderDate) AND (FilledDate < '01/01/2010'));
GO
PRINT N'Creating Sales.CK_Orders_OrderDate...';
GO
ALTER TABLE [Sales].[Orders]
    ADD CONSTRAINT [CK_Orders_OrderDate] CHECK ((OrderDate > '01/01/2005') and (OrderDate < '01/01/2020'));
GO
PRINT N'Creating Sales.uspCancelOrder...';
GO
CREATE PROCEDURE [Sales].[uspCancelOrder]
@OrderID INT
AS
BEGIN
DECLARE @Delta INT, @CustomerID INT
BEGIN TRANSACTION
    SELECT @Delta = [Amount], @CustomerID = [CustomerID]
     FROM [Sales].[Orders] WHERE [OrderID] = @OrderID;

UPDATE [Sales].[Orders]
   SET [Status] = 'X'
WHERE [OrderID] = @OrderID;

UPDATE [Sales].[Customer]
   SET
   YTDOrders = YTDOrders - @Delta
    WHERE [CustomerID] = @CustomerID
COMMIT TRANSACTION
END
GO
PRINT N'Creating Sales.uspFillOrder...';
GO
CREATE PROCEDURE [Sales].[uspFillOrder]
@OrderID INT, @FilledDate DATETIME
AS
BEGIN
DECLARE @Delta INT, @CustomerID INT
BEGIN TRANSACTION
    SELECT @Delta = [Amount], @CustomerID = [CustomerID]
     FROM [Sales].[Orders] WHERE [OrderID] = @OrderID;

UPDATE [Sales].[Orders]
   SET [Status] = 'F',
       [FilledDate] = @FilledDate
WHERE [OrderID] = @OrderID;

UPDATE [Sales].[Customer]
   SET
   YTDSales = YTDSales - @Delta
    WHERE [CustomerID] = @CustomerID
COMMIT TRANSACTION
END
GO
PRINT N'Creating Sales.uspNewCustomer...';
GO
CREATE PROCEDURE [Sales].[uspNewCustomer]
@CustomerName NVARCHAR (40)
AS
BEGIN
INSERT INTO [Sales].[Customer] (CustomerName) VALUES (@CustomerName);
SELECT SCOPE_IDENTITY()
END
GO
PRINT N'Creating Sales.uspPlaceNewOrder...';
GO
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
GO

On the File menu, click Save SqlQuery_1.sql As.

The Save File As dialog box opens.
In Object name, type SampleImportScript.sql.

You can save the file to any location on your computer. Make note of the location because you must use it in the next procedure.
Click Save.
On the File menu, click Close Solution.

Next you create a database project and import the schema from the script that you have created.

To import a database schema from a script

On the Project menu, click Import Database Script.
After you read the Welcome page, click Next.
Click Browse, and browse to the location in which you saved the SampleImportScript.sql file.
Double-click the SampleImportScript.sql file, and then click Finish.

The script is imported, and the objects that are defined in that script are added to your database project.
Review the summary, and then click Finish.

Configure, Build, and Deploy the Database Project

These procedures create a database that has the imported schema in an isolated development environment (or sandbox) in which you can develop and test the database.

To configure and build the database project

In Solution Explorer, click the CompareProject project.
On the Project menu, click CompareProject Properties.

The properties for the CompareProject project appear.
Click the Deploy tab.
In the Deploy Action list, click Create a deployment script (.sql) and deploy to database.
In Target Database Settings, click Edit.
In the Connection Properties dialog box, set the connection properties for the database where you want to work, and then click OK.

The Target Connection box displays the correct connection string.

Warning

You should create the database on a test server, a development server, or your local computer. You should not use a production server.
In the Target database name box, type CompareProjectDB.
On the File menu, click Save All.
On the Build menu, click Build Solution.

The deployment script is built based on the project properties that you have just set. The status of the build appears in the Output window, and Build: 1 succeeded or up-to-date appears as the last line.

To deploy the database project

In Solution Explorer, click the CompareProject project.
On the Build menu, click Deploy CompareProject.

As an alternative, you can also right-click the project in Solution Explorer and click Deploy.

Warning

You should run this deployment against a test server, a development server, or your local computer. You should not use your production server.

The database project is deployed to a new database. The status of the deployment appears in the Output window, and Deployment succeeded appears as the last line.

Production Phase

Add a Table to the Database Project

To add a table to the project

On the View menu, click Database Schema View.

Schema View opens and displays the schema of the CompareProject project.
In Schema View, expand the CompareProject node, and expand the Schemas node.
Right-click Sales, point to Add, and click Table.

The Add New Item - CompareProject dialog box opens.
Under Templates, click Table.
In Name, type InternationalShippers, and then click Add.

A table that is named InternationalShippers is added to the CompareProject project. The table definition appears in the Transact-SQL editor.

Change the SQL script in InternationalShippers.table.sql to the following statements:

CREATE TABLE [Sales].[InternationalShippers]
(
[ShipperID] [int] NOT NULL IDENTITY(1,1),
[CompanyName] [nvarchar] (40) NOT NULL,
[Region] [nvarchar] (40) NOT NULL,
[Phone] [nvarchar] (24) NULL
) ON [PRIMARY]

Click Save InternationalShippers.table.sql.
In Schema View, right-click the InternationalShippers table, and click View File in Solution Explorer.

In Solution Explorer, the InternationalShippers.sql file is highlighted.
Press F4.

The Properties window appears and displays the properties of the InternationalShippers.table.sql file. The Build Action property is set to Build, which indicates that the file contains the definition for a database object and should be parsed and validated.

Compare the Two Schemas

To compare the two schemas

On the Data menu, point to Schema Compare, and click New Schema Comparison.
The New Schema Comparison dialog box opens.
In Target Schema, click Database and specify a connection to the database that you deployed earlier in this walkthrough, and click OK.

The schema of the database is compared with the schema of your changed project, and the results appear in the Schema Compare window. In the CompareProject column, the [Sales].[InternationalShippers] table appears. The status of the table is New, and its update action is Create. If you propagated changes now, the table would be created in the target database. For more information, see the following procedure.

To review and ignore expected differences

In the list of comparison results, scroll to the SQL files node.

For the target database, two rows that have a status of Missing appear in the list: one row for the filegroup file and one row for the log file.
Click in the Update Action column for each row to change the action to Skip.

When you update a database schema, you typically do not want to change the filegroup file or the log file associated with the target database. By changing the action to Skip, the target database will continue to use its current files.

Next, you can optionally update the target database to match the source database.

Propagate Changes from the Project to the Database

To propagate changes to the target database

In the Schema Compare window, click Write Updates.

Note

The Write Updates button might be disabled if the project contains one or more errors. If this occurs, a message appears in the Schema Compare status bar.

The actions that were listed in the Schema Compare window, which include the action of Create for the InternationalShippers table, are executed. This synchronization changes the schema of the database to match that of the database project. When the update operation is complete, the schemas are compared again, and the results are refreshed.

While the target schema is being updated, you can cancel the operation by opening the Data menu, pointing to Schema Compare, and then clicking Stop Writing to Target.

Warning

Because some schema changes cannot be performed within the scope of a transaction, you might lose data if you cancel an update operation. For example, a table in the target database may have been dropped in preparation for its re-creation. If you cancel the update at that moment, you could lose the table. If this kind of data loss occurs, you should compare the schemas again.

Udostępnij za pośrednictwem