An Overview of Database and Server Projects

You can help your organization manage its database development more effectively by creating a database project or a server project and putting it 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.

Project Structure

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:

• Properties Files
  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 Generate Test Data for Databases by Using Data Generators.

• Schema Comparisons
  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
  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 Scenario: Create and Modify Database and Server Objects.

• Scripts
  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 Scenario: Create 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 Database Edition. 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 Improving Database Code with Static Analysis.

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.

Security Considerations

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.

Related Scenarios

• 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.

See Also

Concepts

Build and Deploy Databases to an Isolated Development Environment

Configuring Database Projects and Performing a Test Deployment

Performing Iterative Database Development

Terminology Overview of Database Edition

Other Resources

Walkthroughs (Creating and Updating Version-Controlled Database Schemas)