Localized versions of the GDR for Visual Studio Team System 2008 Database Edition will be available in the same languages as Service Pack 1 for Visual Studio 2008 (SP1). Each language-specific version of Database Edition must be installed on a version of Visual Studio 2008 SP1 that is running the same language. For example, the French GDR for Database Edition must be installed on a French version of Visual Studio 2008 SP1. If you would like to install a non-English version of the GDR for Database Edition, replace English with your language choice in the following instructions.

1. Install Microsoft Visual Studio Team System 2008 Database Edition (English) or Microsoft Visual Studio Team System 2008 Suite (English).

2. Install Microsoft Visual Studio 2008 SP1.

3. Uninstall the Database Edition Power Tools if you have them installed.

a. To uninstall the Power Tools from the command line, use:
msiexec /X {EA016DAB-E08A-46FB-BBF0-ED6EB8FD4671}

4. Uninstall any previous version of the GDR for Database Edition.

a. To uninstall a previous version of the GDR from the command line, use:
msiexec /X {DDF197C6-4507-3A19-A4B5-0E17CC931370}

5. Install the GDR for Database Edition by running the self-extracting executable SETUP.EXE.

Multi-Language Installation Instructions

Visual Studio 2008 SP1 and GDR for Database Edition support the side-by-side installation of English and a non-English language. To install a non-English version of the GDR for Database Edition in addition to an English installation:

1. Follow the installation instructions listed above for the English installation.

2. Repeat the installation instructions listed above, replacing the English versions of Visual Studio 2008, Visual Studio 2008 SP1, and GDR for Database Edition with the localized versions that you would like to install.

Uninstall Instructions

1. Uninstall Visual Studio Team System 2008 Database Edition GDR.

a. To uninstall from the command line, use:
msiexec /X {DDF197C6-4507-3A19-A4B5-0E17CC931370}

2. Open a Visual Studio 2008 command prompt with elevated privileges (Start à All Programs à Microsoft Visual Studio 2008 à Visual Studio Tools à Visual Studio Command Prompt).

3. In the command prompt, run the following commands:

CD C:\Program Files\Microsoft Visual Studio 9.0\DBPro\

DBProRepair RestoreDBPro2008

How to Submit Bug Reports and Suggestions

In order to report a bug or submit a feature request, use the Microsoft Connect site.

1. Open http://connect.microsoft.com/visualstudio in your browser.

2. Click the Submit a Bug or the Submit a Suggestion button.

3. In the Version field, select Visual Studio Team System 2008 Database Edition GDR.

4. Fill out the rest of the fields as appropriate.

5. Click the Submit button.

VSDBCMD.EXE

The vsdbcmd.exe utility can be used to do command-line deployment of your database.

Prerequisites

The following must be installed on the system prior to running vsdbcmd.exe:

· Microsoft SQL Server Compact Edition 3.5 SP1 (http://go.microsoft.com/fwlink/?LinkID=123760)

· Microsoft SQL Server 2008 Management Objects (http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en)

Instructions

1. Install the prerequisites on the host system from which you will deploy.

2. On your development (or build) computer:

a. Copy the contents of the folder %VSINSTALLDIR%\VSTSDB\Deploy to a portable device such as a USB drive.

b. Copy the contents of the build output folder for your project to the portable device.

3. Take the portable device to the host system from which you will deploy.

4. Deploy your database by running the command:

vsdbcmd /Action:Deploy /Manifest:<ProjectName>.deploymanifest

5. For command-line options, type:

vsdbcmd /?

6. To view the parameter options for the Deploy action, type:

vsdbcmd /? /a:Deploy /cs:"Data Source=(local);Initial Catalog=Master" /dsp:SQL

Defining Permissions

Permissions are managed through an XML file that is a standard part of the project template. The file is named Database.sqlpermissions for database projects or Master.sqlpermissions for server projects. The file will be referred to as SQLPermissions for the remainder of this document. SQLPermissions is located in the Properties folder of each project.

There are three primary scenarios in which permissions are defined in SQLPermissions:

· Import Script

· Import Schema

· User Defined

Permission statements are populated in SQLPermissions when you import from a script or from a database. You define new permissions or change existing permissions in SQLPermissions. Permission statements created interactively in scripts within a project will cause validation errors that indicate that permissions should be defined in SQLPermissions.

Import Schema Scenario

When you import database objects and settings, you can optionally include permissions. If you select the Import Permissions check box in the import database schema dialog box, permissions in the database will be imported and added to SQLPermissions.

Import Script Scenario

When you import a script, you can optionally include permissions. If you select the Import Permissions check box in the import script dialog box, permission statements in the script will be imported and added to SQLPermissions.

User-Defined Permissions Scenario

You can define permissions or change existing permissions by opening SQLPermissions in the Visual Studio editor. The schema of SQLPermissions is defined by the Microsoft.VisualStudio.Data.Schema.Permissions XML Schema Definition (XSD), which is located in the <VSROOT>\xml\Schemas directory.

You start with an empty XML document that references the XSD when you create an empty project. Comments are provided to help you define permissions. A project in which a script or a database has been imported will contain all recognized permission statements in SQLPermissions. Visual Studio provides validation and Intellisense of SQLPermissions based on the schema.

Defining Permissions

Permissions are defined as a collection of permission statements. Each permission statement is defined as a combination of elements.

The minimum required set of elements includes the following:

<Permission>CONNECT</Permission>

</PermissionStatement>

The above statement can be used for granting or denying database- or server-level permissions.

A typical permission statement will consist of the following:

<Permission>EXECUTE</Permission>

</PermissionStatement>

Permissions for objects that have columns (tables, views, and table-value functions) can be defined as follows:

<Permission>SELECT</Permission>

</Columns>

</Object>

</PermissionStatement>

The permission statement above grants the SELECT permission only on the columns listed. The permissions for any other columns of the object will not be affected. Similarly, you can also specify columns to be excluded from a permission statement as follows:

<Permission>UPDATE</Permission>

</Columns>

</Object>

</PermissionStatement>

Column permission exclusions will only be created from imported databases and scripts when a REVOKE is present for a column-level object.

Objects that have column-level permission statements and an object-level permission statement have two permission statements in SQLPermissions.

<Permission>SELECT</Permission>

</PermissionStatement>

<Permission>SELECT</Permission>

</Columns>

</Object>

</PermissionStatement>

Objects that have column-level permission exclusion statements are specified by one permission statement. When deployed, the column-level permission exclusion statements are expressed as a parent-level object permission statement and column-level REVOKE statements.

<Permission>SELECT</Permission>

</Columns>

</Object>

</PermissionStatement>

Common Language Run-Time (CLR) Assemblies

CLR Assembly Names

When creating references in database projects to SQL CLR assemblies or SQL CLR projects, the Assembly Name property of the reference is populated only when the reference is first established. If the Assembly Name project property is updated in the SQL CLR project, you must re-create the reference to the SQL CLR assembly or project to update the Assembly Name property for the reference.

If you deploy a database project with references to SQL CLR assemblies and the project builds successfully but fails to deploy, giving en error that indicates that the SQL CLR assembly does not exist in the SQL Server catalog, the reference might have an incorrect Assembly Name property and might need to be re-created in the project.

Deployment of Dependent Assemblies

The GDR does not deploy indirectly referenced assemblies (for example, a SQL CLR assembly that references another SQL CLR assembly). You must provide a reference to the SQL CLR assembly and to each assembly it references to successfully deploy the database project.

An example of how to set up your references:

You have a database project called SQLDB that references a SQL CLR project called SQLCLR1. SQLCLR1 references another SQL CLR project called SQLCLR2. To deploy the database successfully, you must create a reference to both SQLCLR1 and SQLCLR2 in the SQLDB project.

Using Three- and Four-Part Names in Project

To resolve cross-database and server references within a project, you must create database references between projects and optionally define SQL CMD variables for these references. The following scenarios illustrate how to work with the three-part and four-part names in your project.

Referencing Server Objects

1. Create a server project named MyServer.

2. Add a LOGIN such as the following:

CREATE LOGIN [MYSRV\Login1] FROM WINDOWS

3. Configure the target database settings in project properties for your MyServer project.

4. Build the MyServer project.

5. Create a database project named MyDatabase.

6. Add a database reference in your MyDatabase project that references the MyServer project.

a. Select the Literal check box.

b. Specify master (without quotation marks) for the database name.

7. Add a USER such as the following:

CREATE USER [user1]

FOR LOGIN [MYSRV\Login1]

WITH DEFAULT_SCHEMA = dbo;

8. Add a second database reference in your MyDatabase project that references the master.dbschema that shipped as part of the GDR.

a. Select the Database project schema radio button in the Add Database Reference dialog box.

b. Browse to the appropriate folder under C:\Program Files\Microsoft Visual Studio 9.0\VSTSDB\Extensions\SqlServer\<Your SQL Version>\DBSchemas

c. Select the master.dbschema file.

d. Check the Literal check box.

e. Specify master (without quotation marks) for the database name.

9. Add a VIEW such as the following:

CREATE VIEW [dbo].[View1]

AS SELECT * FROM [master].[dbo].[syscomments];

10. Configure the target database settings in the project properties for the MyDatabase project.

11. Build and deploy your solution.

Using a Three-Part Name in a Database Project

1. Create a database project named SQLDB1.

2. Add a VIEW to the SQLDB1 project such as the following:

CREATE VIEW [dbo].[View]

AS SELECT * FROM [dbo].[Table];

3. An unresolved reference to a Table error appears in the error list.

4. Create a database project named SQLDB2 and add it to the existing solution.

5. Add a TABLE to the SQLDB2 project such as the following:

CREATE TABLE [dbo].[Table]

(Id int IDENTITY PRIMARY KEY,

Name nvarchar(50) NOT NULL)

6. Add a database reference to the SQLDB1 project that references the SQLDB2 project. If you want to use a SQLCMD variable to specify the database part of the three-part name, define a SQLCMD variable named $(DB2) and set the value of the database name appropriately. Alternately, you could specify a literal value (the actual name of the database) by selecting the Literal check box for the database reference and by specifying the actual name of the database.

7. Modify the VIEW defined in step 2 by using one of the following definitions depending on whether you are using a SQLCMD variable or a literal value for the database name:

-- SQLCMD variable for the database name

CREATE VIEW [dbo].[View]

AS SELECT * FROM [$(DB2)].[dbo].[Table];

-- Literal database name

CREATE VIEW [dbo].[View]

AS SELECT * FROM [database2].[dbo].[Table];

8. Build the solution.

9. The table is now resolved to the SQLDB2 database project. The SQLDB1 and SQLDB2 projects can now target different deployment databases.

Using a Four-Part Name in a Database Project

1. Using the solution created in the three-part name example, remove the database reference from SQL1DB.

2. An unresolved reference error appears in the error list.

3. Add a database reference to the SQLDB1 project that references the SQLDB2 project.

4. Modify the VIEW in the SQLDB1 project to match the following definition:

CREATE VIEW [dbo].[View]

AS SELECT * FROM [$(DB2Srv)].[$(DB2)].[dbo].[Table];

5. Build the solution.

6. The table object is now resolved again to the SQLDB2 database project. The SQLDB1 and SQLDB2 projects can now target different deployment servers.

Using master.dbschema and msdb.dbschema

The GDR for Database Edition provides .dbschema files that represent the contents of the master and msdb databases on your SQL Server. They are effectively pre-built database projects. If you need to reference the contents of one of these databases in your database or server project, you can define a database reference to one or both of these dbschema files.

The following scenario illustrates how you can reference the master.dbschema file:

1. Create a database project named MyDatabase.

2. Add a database reference to master.dbschema:

a. Select the Database project schema radio button in the Add Database Reference dialog box.

b. Browse to the appropriate folder under C:\Program Files\Microsoft Visual Studio 9.0\VSTSDB\Extensions\SqlServer\<Your SQL Version>\DBSchemas

c. Select the master.dbschema file.

d. Select the Literal check box.

e. Specify master (without the quotation marks) for the database name.

3. Add a VIEW:

CREATE VIEW [dbo].[View1]

AS SELECT * FROM [master].[dbo].[syscomments];

Linked Server Support

Linked server definitions are supported for SQL Server projects. A linked server definition can be imported from an existing master database or defined in a project. When you import from an existing master database, only the linked server and linked server logins are imported into the project. The other supporting statements, such as server options and provider options, are not imported into the project. You should add these options to the post-deployment script to deploy a linked server to master with all options.

The following statements are an example of what you should add to the post-deployment script:

EXEC master.dbo.sp_serveroption @server=N'MYLINKEDSERVER\MYINSTANCE, @optname=N'collation compatible', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=N'MYLINKEDSERVER\MYINSTANCE', @optname=N'data access', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=N'MYLINKEDSERVER\MYINSTANCE', @optname=N'dist', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=N'MYLINKEDSERVER\MYINSTANCE', @optname=N'pub', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=N'MYLINKEDSERVER\MYINSTANCE', @optname=N'rpc', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=N'MYLINKEDSERVER\MYINSTANCE', @optname=N'rpc out', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=N'MYLINKEDSERVER\MYINSTANCE', @optname=N'sub', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=N'MYLINKEDSERVER\MYINSTANCE', @optname=N'connect timeout', @optvalue=N'30'

EXEC master.dbo.sp_serveroption @server=N'MYLINKEDSERVER\MYINSTANCE', @optname=N'collation name', @optvalue=N'SQL_Latin1_General_CP1_CI_AI'

EXEC master.dbo.sp_serveroption @server=N'MYLINKEDSERVER\MYINSTANCE', @optname=N'lazy schema validation', @optvalue=N'false'

EXEC master.dbo.sp_serveroption @server=N'MYLINKEDSERVER\MYINSTANCE', @optname=N'query timeout', @optvalue=N'30'

EXEC master.dbo.sp_serveroption @server=N'MYLINKEDSERVER\MYINSTANCE', @optname=N'use remote collation', @optvalue=N'true'

EXEC master.dbo.sp_serveroption @server=N'MYLINKEDSERVER\MYINSTANCE', @optname=N'remote proc transaction promotion', @optvalue=N'true'

Deploying Encryption Keys

Symmetric keys can be deployed to a database. Symmetric keys that are encrypted by another symmetric key cannot be deployed automatically and will fail to deploy since the encrypting key is not first opened by the deployment engine. To update an existing key that has been encrypted by another key, you should update these keys on the target server before you deploy. For specifics of managing encryption keys, see SQL Server Books Online.

Disabling a Trigger

DISABLE TRIGGER statements are valid in projects, but are not supported when you deploy. When you deploy the project, the state of a trigger will not be changed, even if the state of the trigger in the target database does not match the state of the trigger in the project. Similarly, Schema Compare will detect a trigger as being different when the source does not match the targets trigger state, but will not update the trigger state when you write updates to a target database or project. When you deploy or use Schema Compare, a trigger will be updated if its definition does not match, but the enabled state will not be updated. You can modify the enabled state of a trigger manually, or in the pre-deployment or post-deployment script of your project.

Known Issues and Limitations

Offline Database Files

Offline database files are not supported within the project system. If a database is imported that contains files marked as OFFLINE, these statements will be ignored. OFFLINE FILES must be restored or removed by the sysadmin of the SQL Server instance. For more information, see SQL Server Books Online.

Could Not Load File or Assembly System.Data.SqlServerCe

The GDR requires that the 32-bit version of SQL Server Compact Edition (SQL CE) 3.5 SP1 be loaded on the host computer. It is installed as part of Visual Studio 2008 SP1. If you do not have the correct version of SQL CE installed, you will get the following error message:

Could not load file or assembly System.Data.SqlServerCe, Version=3.5.1.0

You can download the SQL CE redistributable from:

http://www.microsoft.com/downloads/details.aspx?FamilyID=dc614aee-7e1c-4881-9c32-3a6ce53384d9&DisplayLang=en

Deploy

· When you deploy an assembly, the supporting files, such as PDB files, are not generated and deployed during the build and deploy process.

Files and Filegroups

When you import objects and settings from a database into a database project, you import the file and filegroup definitions for that database. If the files are located on the default file path for the database, the literal file paths are replaced with the $(DefaultFilePath) variable. If the path for the files differs from the default file path, even if it differs only by case (for example C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ vs. C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\), then no variable substitution is performed. If you deploy to a target database that does not have the same file paths or to a server that already contains the files in the specified directory, deployment will fail.

To resolve this issue, you must replace the path with the $(DefaultDataPath) SQLCMD variable, or define additional SQLCMD variables and then use those variables in the file definitions. Unless you have a specific need to place files outside of their default location, it is recommended that you use the $(DefaultDataPath) SQLCMD variable, because this variable is populated during deployment and will always reflect the default data file path for the environment where the database is being deployed.

To add additional SQLCMD variables:

1. In Solution Explorer, expand the database project node, expand the Properties node, and double-click the Database.sqlcmdvars file.

2. Add one or more SQLCMD variables and values.

3. Expand the Schema Objects node, expand the Database Level Objects node, expand the Storage node, and expand the Files node.

4. Double-click the file whose definition you want to update.

5. In the Transact-SQL (T-SQL) editor, replace the explicit path with the variable that you defined in step 2.

Schema Compare

· Schema Compare will always ignore passwords for Master keys, Symmetric keys, and Asymmetric keys.

· When comparing server settings that are defined in a project, if the setting is not configured to be verified, its value will be NULL in Schema Compare. If you would like to use a non-NULL value, you must configure the setting to be verified in your Server.sqlsettings file. From Solution Explorer, expand the Properties node and open the Server.sqlsettings file.

Importing Language Settings for Schema Objects

If you specify the LANGUAGE setting for a schema object for example, in a CREATE FULLTEXT INDEX statement such as the following:

CREATE FULLTEXT INDEX ON [dbo].[Table_1]

([column_2] LANGUAGE [British English])

KEY INDEX [PK_Table_1]

ON [FullTextCatalog1]

WITH CHANGE_TRACKING AUTO;

Import Schema will convert the Language identifier into a Language Code Identifier (LCID):

CREATE FULLTEXT INDEX ON [dbo].[Table_1]

([column_2] LANGUAGE 2057)

KEY INDEX [PK_Table_1]

ON [FullTextCatalog1]

WITH CHANGE_TRACKING AUTO;

This will cause a Schema Compare difference when comparing a project to a database if you have used the string identifier [British English] in this example in your project.