Managing Database Change

Visual Studio

As a SQL Server Database Administrator, you need to understand enterprise development. Your responsibilities probably include coordinating schedules and changes with software-development teams to create unified database applications. You can significantly reduce the effort that this coordination requires by adopting tools and processes that are as similar as possible to those of the software developers who are also in your organization.

As many teams move to more iterative, rapid-development paradigms for writing code (such as the Agile method), both software and database developers have an even greater need for tools such as version control. These tools can reduce the risks that are associated with team-based development. By using similar tools and processes, you can gain significant benefits not only for the databases that you manage but also for the integration points between those databases and the software that interacts with them.

Using Visual Studio

Visual Studio helps you coordinate with your software developer counterparts by encouraging team members to take a more holistic view and focus on the application behavior, instead of the implementation details. This approach diverges from the model where the production database (and its data) are viewed as the center of the application universe. Visual Studio provides a solution set that is targeted at the complete application — both data and logic. To build an application that performs well, is easy to maintain, and can be delivered as quickly as possible, development organizations at the enterprise level must take this inclusive view of the application.

Visual Studio provides features that are targeted at the database developer and database administrator roles of today. For example, Visual Studio supports the concept of an isolated development environment for each database professional. Team members can work on schema-related activities without causing hardship to other team members or the production environment. The long-term vision adds features for the database architect role and expands features for database administrators.

High-level Tasks

To support high-level developer goals, Visual Studio helps you perform the following high-level tasks:

  • Create and deploy a new database under version control.
  • Put an existing database schema under version control by using the database project. The database project schema is created by importing the schema from a database or existing scripts.
  • Modify an offline representation of a database and then deploy the changes.
  • Compare schemas or data between databases.
  • Develop and run unit tests against database objects.
  • Generate predictable, representative test data without compromising sensitive production data.
  • Rename database tables, columns, views, or other objects and propagate those changes to the rest of the database project, which includes associated unit tests, views, stored procedures, triggers, and so on.
  • Create, edit, validate, execute, and analyze scripts and queries.

The Database Development Lifecycle

Visual Studio supports a process that works together with the new tools. You can think of this process as the database development lifecycle, which provides each database professional with an isolated development environment in which to work and test changes. After changes have been tested, team members share changes through the version control system. The database development lifecycle contains the following phases:
  • Establish the project environment
  • Perform isolated, iterative development
  • Build the project
  • Deploy from the project environment

Establish the Project Environment

The following illustration shows how the team member who performs the role of the database administrator (DBA) establishes the project environment:

DBA establishes the database project environment

The DBA starts by creating a database project and importing the database schema from the production database. The DBA then can create a data generation plan to create test data for the isolated development environments. Finally, the DBA checks the database project into version control, which makes it available to the team.

Perform Isolated, Iterative Development

The following diagram shows how team members perform iterative development work in an isolated environment:

Iterative development in  an isolated environment

Each database professional synchronizes their development environment to version control. They can check out files as they are changed and develop and test those changes in isolation. Changes made to their copy of the database project are deployed only to their isolated development environment. After a team member generates realistic test data and runs unit tests against a private copy of the database, the team member checks the changes into version control. Other team members obtain tested changes from version control.

Generate Daily Builds of the Project

The following illustration shows how the build cycle works for database projects:

Build cycle for database projects

The daily build is generated by synchronizing to the most recent tested version of the database project in the version control system. That build can be deployed to a test database where unit tests are run against generated test data.

Deployment from the Project Environment

The following diagram shows how the DBA can deploy a version of the database project into production:

DBA builds and deploys into production

When it is time to deploy a version of the database to production, the DBA synchronizes to a label from the version control system. The DBA retrieves matching files for the database project, related scripts, and tests. Next, the DBA builds the .dbschema file. From the .dbschema file, the DBA generates the deployment script, makes manual modifications as needed, and does a test deployment to the staging server. The DBA iterates this process until the script is ready to be deployed to the production server. If the DBA made changes to the schema or to the pre-deployment or post-deployment scripts, those changes will be checked back in to version control.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s