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