Starting Team Development of Databases that Reference SQLCLR Objects

  • Article

To manage changes to database objects and settings, you create a database project, and then you import objects and settings into it from the database that you want to manage. If the database contains any references to one or more SQL common language runtime (CLR) assemblies, they must be declared in the database project. Because the database project is an offline representation of the objects and settings in a database, you can put it under version control and then deploy iterative changes to an isolated development environment. Team members can test their changes independently, and then share those changes with the team only after they have been fully tested. By taking this approach, you help improve the quality of the database code before you deploy it to a production environment.

Specifying a Type of Database Project

When you create a database project, you specify the type that corresponds to the type and version of database that you want to manage. For example, you specify either SQL Server 2005 Database Project or SQL Server 2005 Wizard if the database that you want to manage is based on SQL Server 2005. If you use the wizard, you can not only create the project, but also configure some build and deployment settings and import database objects and settings at the same time.

Importing Database Objects and Settings

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.

When you import objects that reference SQLCLR objects, you can find imported SQLCLR assemblies in the Schema Objects\Database Level Objects\Assemblies folder of the database project. The References folder also contains a reference to each assembly that you imported. If you need to update the assembly, you might want to remove the existing assembly reference, add the SQLCLR project to your solution, and add a reference to the SQLCLR project. If you do not need to update the assembly, you can retain the existing reference. That assembly will be deployed to any target database.

Common Tasks

In the following table, you can find descriptions of common tasks that support this scenario and links to more information about how you can successfully complete those tasks. In addition, you should determine how these tasks best fit into the development methodology of your team.

Common Tasks

Supporting Content

Learn more about database projects: You can read about the basic concepts of how to manage changes to database objects and settings by using database projects.

Get hands-on practice: You can follow the walkthrough to become familiar with how to create an isolated development environment to manage changes to a database that uses a SQLCLR assembly. If you deploy your changes to such an environment, they will not conflict with changes from other team members.

Add a reference to a SQLCLR assembly: You can add a reference to a SQLCLR assembly if you want to reference user-defined functions, procedures, aggregates, triggers, or user-defined types that are defined in a SQLCLR assembly.

Put objects and settings from an existing database under version control: You can use the database project wizard to create a project, configure project settings, and import database objects and settings. You can also create an empty database project if you want to import the objects later or if you do not have permission to access the database from which you want to import the objects. After you import the objects and settings, you can add the project to version control.

Starting Team Development of Databases

Starting Team Development of Databases that Reference Shared Server Objects

Starting Team Development of Databases that Reference SQLCLR Objects