Share via


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

You can use database projects to create new databases, new data-tier applications (DACs), and to update existing databases and data-tier applications. Both database projects and DAC projects enable you to apply version control and project management techniques to your database development efforts in much the same way that you apply those techniques to managed or native code. You can help your development team manage changes to databases and database servers by creating a DAC project, database project, or a server project and putting it under version control. Members of your team can then check out files to make, build, and test changes in an isolated development environment, or sandbox, before sharing them with the team. To help ensure code quality, your team can finish and test all changes for a particular release of the database in a staging environment before you deploy the changes into production.

For a list of the database features that are supported by Data-tier Applications, see Features Supported in Data-tier Applications on the Microsoft Web site. If you use features in your database that are not supported by Data-tier Applications, you should instead use a database project to manage changes to your database.

Using Database Features in Visual Studio

If you have installed Visual Studio Premium or Visual Studio Ultimate, you have access to all the database features that are described here and in the Developing the Application section of the Visual Studio documentation. If you do not have Visual Studio Premium or Visual Studio Ultimate, but instead have Visual Studio Professional, only a subset of the features are available.

The following table describes the limitations in versions of Visual Studio other than Visual Studio Premium and Visual Studio Ultimate:

Feature Area

Limitations

Database Projects (including Data-tier Applications)

You can create, modify, build, and deploy your database projects in the same way as in Visual Studio Premium and Visual Studio Ultimate.

Import Objects and Import Script

You can import objects and settings from a database or from scripts in the same way as in Visual Studio Premium and Visual Studio Ultimate.

Database Unit Testing

You can run database unit tests but you cannot create new tests, view tests, or modify tests.

Data Generation

You can generate data only as part of an existing database unit test. You cannot create, view, modify, or manually run data generation plans.

Schema Compare

You cannot compare schemas.

Data Compare

You cannot compare data.

Transact-SQL editor

You can use all features of the editor in the same way as in Visual Studio Premium and Visual Studio Ultimate.

Database Refactoring

You cannot use database refactoring.

Database Code Analysis

You cannot use database code analysis.

Common High-Level Tasks

High-Level Task

Supporting Content

Getting Started: You want to understand how you can use the database features of Visual Studio to manage changes to your databases.

Start development of a data-tier application: A DAC is a new concept introduced with SQL Server 2008 R2 that contains the definition for a SQL Server database and the supporting instance objects that are used by a client-server or 3-tier application. A DAC includes database objects, such as tables and views, together with instance entities such as logins. You can use Visual Studio to create a DAC project, build a DAC package file, and send that DAC package file to a database administrator for deployment onto an instance of the SQL Server database engine.

Start development of a database or server project: To manage changes in Visual Studio Premium, you first create a database project or a server project. In most cases, your team is updating an existing production database or server that already contains some objects and settings. You can import those objects and settings into the project and configure it with the settings that you want the team to use. You might also establish a plan that developers and testers use to generate test data. When you are ready to share the project and its settings with the rest of the team, you put the files that are associated with the project under version control.

Performing iterative database development: If you are a developer or a tester, you check out parts of the project and then update them in an isolated development environment. By using this type of environment, you can test those changes without affecting other members of the team. After the changes are complete, you check the files back into version control, where other team members can obtain your changes and build and deploy them to a test server.

Avoid breaking existing database code: If you run existing database unit tests before sharing your changes with the team or deploying them into a production environment, you can reduce the risk of breaking application behavior. As part of your unit tests, you might generate test data by running an existing data generation plan.

Building and deploying databases to staging or production environments: If you act as the database administrator for your team, you can deploy a version of the database project or the server project into staging or production environments.

Prototyping, verifying test results, and modifying database scripts and objects: You can use the Transact-SQL editor to perform any one of these common tasks.

See Also

Concepts

Managing Database Change