Working with Database Projects
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.
If you have the older type of database projects with the .dbp extension, you must upgrade them to the new type of database project. .dbp projects are no longer supported in Visual Studio.
Getting Started: You want to understand how you can use the database features of Visual Studio to manage changes to your databases.
Convert a .dbp project to a database project (.dbproj): You can follow a fairly simple procedure to migrate the database objects that you have defined in a .dbp project into a .dbproj database project or a Data-tier Application project..
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, 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.
You must have Visual Studio Premium or Visual Studio Ultimate to create or modify database unit tests.
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.