Refactoring Database Objects in a Team Environment

  • Article

With Visual Studio Premium, you manage change by putting the offline representation of the database schema, which is called the database project, under a version-control system. You or a member of your team can change the database project, but those changes must then be built and deployed to a live database. In this situation, up to four representations of the database can exist:

  • The database server, which contains the live database and the data.

  • The database project, which is the offline representation of the database schema.

  • The compiled .dbschema file, which contains information that is required to deploy that database schema to any target database and server. This file is created whenever you build the database project.

  • The version-control repository, which tracks all changes that any member of the team makes to the database project.

For more information, see Starting Team Database Development.

Refactoring in a Team Environment

If you use refactoring to rename a database object or move it to a different schema, other objects that reference the renamed or moved object are automatically updated with the new name or schema. For example, you can rename a column in a table, and any stored procedures that reference that column are automatically updated with the new name. For more information, see Rename All References to a Database Object or Move a Database Object to a Different Schema.

Before you can update a database object, you must check out from source control the files for all objects that must be updated. If you have set your source control option to automatically check out files, the check-out attempt is made silently. Otherwise, you are prompted to check out the files.

The refactoring operation fails in the following situations:

  • If someone else has locked one or more files, a message appears, and the refactoring operation is canceled. You must wait until the files are unlocked before you try the operation again.

  • If the versions that you check out are newer than the versions that are in your project, a message appears, and the refactoring operation is canceled. You should reconcile the file versions before you retry the operation.

  • If you are prompted to check out the files and you click Cancel, the refactoring operation is canceled.

    Note

    When you rename a database object, you do not rename the .sql file in which that object is defined. You can rename the file manually in Solution Explorer.

Refactoring Logs and Preservation of Intent

When you use refactoring to rename or move a database object, the ProjectName.refactorlog file is updated with the details of that operation. When you deploy your changes, the log helps preserve the intent of your changes because the deployment script contains operations that preserve that intent. For example, deployment can issue an sp_rename statement for a column instead of drop and create statements.

If two or more developers make changes that update the refactoring log, the changes to the log file must be merged. The .refactoring file is an XML file that has a simple schema, so merging the updates should not be difficult. Each operation includes a date and time so that you can make sure that refactoring operations are applied in the correct order.

Warning

If you automatically merge the changes in the refactoring log, errors might occur. You should always review the results of an automatic merge or merge the changes manually before you try to deploy the database project.

Your refactoring log will resemble the following example:

<?xml version="1.0" encoding="utf-16"?>
<Operations>
  <Operation Name="Move Schema" Key="677a0ee6-1707-413a-985f-b392b1a2d68b" ChangeDateTime="04/07/2008 21:59:00">
    <Property Name="ElementName" Value="[Person].[AbsenceHistory]" />
    <Property Name="ElementType" Value="ISql90Table" />
    <Property Name="NewSchema" Value="HumanResources" />
    <Property Name="IsNewSchemaExternal" Value="True" />
  </Operation>
  <Operation Name="Rename Refactor" Key="fb88992c-cd6e-43d0-aa54-ed80f155d202" ChangeDateTime="04/07/2008 21:59:26">
    <Property Name="ElementName" Value="[HumanResources].[AbsenceHistory].[column_1]" />
    <Property Name="ElementType" Value="ISqlSimpleColumn" />
    <Property Name="ParentElementName" Value="[HumanResources].[AbsenceHistory]" />
    <Property Name="ParentElementType" Value="ISql90Table" />
    <Property Name="NewName" Value="EmployeeID" />
  </Operation>
</Operations>

In this example log, two refactoring operations were recorded. In the first operation, the [AbsenceHistory] table was moved from the [Person] schema to the [HumanResources] schema. In the second operation, the [column_1] column in the [AbsenceHistory] table was renamed to [EmployeeID].

See Also

Concepts

Creating and Managing Databases and Data-tier Applications in Visual Studio

Rename All References to a Database Object

Move a Database Object to a Different Schema

Refactor Database Code and Data