An Overview of Database and Server Projects
You can help your organization manage its database development more effectively by using Visual Studio Premium to create a database project or a server project and putting the project under version control. These offline representations contain the object definitions, settings, and deployment scripts that you could use to create a separate instance of that database (or server) or to update an existing instance.
Based on your organization's needs, you can also share parts of projects or create a composite project. For more information, see Sharing Parts of Projects later in this topic, or the "Composing Projects by Using References" section of Using References in Database Projects.
Available Project Types
By using Visual Studio 2010 Premium, you can create database projects, server projects, and Data-tier Application Component (DAC) projects.
Data-tier Application Component project
A DAC is a new concept. It was introduced with SQL Server 2008 R2 and it 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. For a list of the features supported by DAC projects, see Features Supported in Data-tier Applications.
A database project contains the definitions of all the objects for a database. In addition, it contains database settings, build settings, and deployment settings.
A server project contains the definitions of the objects that reside on the server or in the master database on the server. In addition, it contains server settings, build settings, and deployment settings.
Solution Explorer shows your project organized by file. Each item in Solution Explorer corresponds to a saved file or a folder. In contrast, Schema View shows your project organized by object or by schema, so that you can identify objects in your database whether they are defined in separate files or not.
A database or server project can contain the following types of objects:
Your database or server project includes files in the Properties folder that contain property values. You can control how your project is deployed by modifying those values. For example, you can specify database settings, server settings, SQLCMD variables, and database permissions. For more information, see Property Files in Database and Server Projects.
Data Generation Plans
Data generation plans contain information about how you want to generate realistic and representative test data for a database that you intend to deploy or update. For more information, see Generating Test Data for Databases by Using Data Generators.
A schema comparison contains information about a specific comparison between your database project and another schema. You can re-compare your project to that schema by re-opening the .scmp file and refreshing the comparison. For more information, see Compare and Synchronize Database Schemas.
Schema objects are defined in a collection of .sql files that are stored in the project folder. Most objects are defined in separate files. Exceptions include columns in a table and parameters for a stored procedure or function. Columns are stored in the definition for the table, and parameters are stored in the definition for the stored procedure or function. For more information, see Creating and Modify Database and Server Objects.
Your project contains pre-deployment and post-deployment scripts, in addition to any scripts that you might use to manage your database or server. For more information, see Creating and Modify Database Scripts.
Importing Objects and Settings
After you create a project, you can import the objects and settings from a database instance or from a script. When you import a database, its object definitions are validated, and statements that cannot be parsed are put in the ScriptsIgnoredOnImport.sql file. If you import object definitions that reference objects that no longer exist, you must resolve those errors before you can build and deploy the project. For example, you might import a stored procedure that references a table that no longer exists. To resolve the error, you could remove that stored procedure.
You might need to spend a lot of time resolving such errors after you import a large schema. However, team members cannot unknowingly introduce additional errors of this type as they update the schema in Visual Studio Premium. When they modify and save any object definition, all changes are validated so that team members can fix them immediately and avoid deploying those errors to a live database.
After you resolve warnings in object definitions, you should also consider analyzing your database code for issues around design, naming, and performance. For more information, see Analyzing Database Code to Improve Code Quality.
Sharing Parts of Projects
If you want to re-use a set of files in more than one project, you can export any part of a project as a partial project. This action creates a .files file, which you can include in one or more other projects. For example, you might have some common stored procedures that you use to audit all your databases. You can define these stored procedures in one project, export them, and include them in your other projects. By taking this approach, you avoid having to maintain the same code in more than one project. For more information, see How to: Import and Export Partial Database Projects.
Your database projects and any related .dbschema files contain information about your database assets. In much the same way that you would restrict access to the source code, you should restrict access to your database projects and .dbschema files.
You can use the following methods to control access to your database projects and .dbschema files:
Use version control for your projects and appropriate file system permissions
You can grant access to your database projects to only those developers who require access. In addition, you can restrict permissions on any file shares that contain database project files or .dbschema files to prevent access by unauthorized individuals.
Partition a database project by using partial projects
You can divide your database objects across several projects, and use version control permissions to control who can view or change those projects. For more information, see Starting Team Development of Large Databases.
Create a .dbschema file that has empty procedure bodies
You can create a copy of your stored procedures that do not contain implementation, and share the .dbschema file for that copy with developers who must call those procedures but who do not have to see their implementation. For more information about how to reference .dbschema files, see Using References in Database Projects.
Starting Team Development of Databases
You can manage changes to a database by creating an offline representation of its objects and settings, and putting that representation under version control.
Starting Team Development of Databases that Reference Other Databases
You can manage changes to a database by creating an offline representation of its objects and settings, and putting that representation under version control. In that representation, you define cross-database references to support deployment into different target environments.
Starting Team Development of Databases that Reference SQLCLR Objects
You can manage changes to a database by creating an offline representation of its objects and settings, and putting that representation under version control. In that representation, you add references to a SQLCLR assembly and then use objects that are defined in that assembly.
Starting Team Development of Databases that Reference Shared Server Objects
You can manage changes to a database by creating an offline representation of its objects and settings, and putting that representation under version control. In that representation, you add a reference to a shared server project that contains the definitions of server objects, such as logins or keys.
Starting Team Development of Large Databases
You can manage changes to a large database by separating the objects across multiple projects.
Starting Team Development of Databases that Use XML Schema Collections
You can manage changes to databases that use one or more XML Schema Collections for typed XML columns.