General Distribution Release (GDR) for Visual Studio
Team System 2008 Database Edition

Readme

Contents

System Requirements. 2

Prerequisites. 2

Supported Architectures. 2

Supported Operating Systems. 2

Hardware Requirements. 3

Installation Instructions. 3

Multi-Language Installation Instructions. 3

Uninstall Instructions. 4

How to Submit Bug Reports and Suggestions. 4

VSDBCMD.EXE. 4

Prerequisites. 4

Instructions. 4

Defining Permissions. 5

Import Schema Scenario. 5

Import Script Scenario. 5

User-Defined Permissions Scenario. 5

Defining Permissions. 6

Common Language Run-Time (CLR) Assemblies. 7

CLR Assembly Names. 7

Deployment of Dependent Assemblies. 8

Minimum Permissions to Import from a Database. 8

Using Three- and Four-Part Names in Project. 9

Referencing Server Objects. 9

Using a Three-Part Name in a Database Project. 10

Using a Four-Part Name in a Database Project. 10

Using master.dbschema and msdb.dbschema. 11

Linked Server Support. 12

Deploying Encryption Keys. 13

Disabling a Trigger. 13

Known Issues and Limitations. 13

Offline Database Files. 13

Could Not Load File or Assembly
System.Data.SqlServerCe. 14

Deploy. 14

Files and Filegroups. 14

Schema Compare. 15

Importing Language Settings for Schema Objects. 15

Related Links. 15

System Requirements

Prerequisites

Before you install this GDR, you must
install the following software on your computer:

1.       Microsoft Visual Studio Team System
2008 Database Edition or Microsoft Visual Studio Team System 2008 Suite

2.       Service Pack 1 for Microsoft
Visual Studio 2008 Service Pack 1

3.       Microsoft SQL Server Compact 3.5 Service
Pack 1 (typically installed as part of Service Pack 1 for Microsoft
Visual Studio 2008)

Supported Architectures

·
x86

·
x64 (WOW)

Supported Operating Systems

·
Microsoft
Windows XP SP2 or later

·
Microsoft
Windows Server 2003 SP2 or later

·
Windows
Vista SP1

·
Windows
Server 2008

Hardware Requirements

Minimum: 2.4 GHz CPU, 768 MB RAM, 1024x768
display, 5400 RPM hard disk

Recommended: 2.2 GHz or higher CPU, 1024 MB
or more RAM, 1280x1024 display, 7200 RPM or faster hard disk

For Windows Vista:
2.4 GHz CPU, 768 MB RAM

Installation Instructions

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:

<PermissionStatement
Action="GRANT">

<Permission>CONNECT</Permission>

<Grantee>User1</Grantee>

</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:

<PermissionStatement
Action="GRANT">

<Permission>EXECUTE</Permission>

<Grantee>User1</Grantee>

<Object
Type="OBJECT" Schema="User1" Name="StoredProcedure1"/>

</PermissionStatement>

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

<PermissionStatement
Action="GRANT">

<Permission>SELECT</Permission>

<Grantee>User1</Grantee>

<Object
Type="OBJECT" Schema="User1" Name="Table1">

<Columns Treatment="INCLUDE">

<Column Name=”Id”/>

<Column Name=”Name”/>

<Column Name=”Updated”/>

</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:

<PermissionStatement
Action="GRANT">

<Permission>UPDATE</Permission>

<Grantee>User1</Grantee>

<Object
Type="OBJECT" Schema="User1" Name="Table1">

<Columns Treatment="EXCLUDE">

<Column Name=”Created”/>

<Column Name=”Updated”/>

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

<PermissionStatement
Action="GRANT">

<Permission>SELECT</Permission>

<Grantee>User1</Grantee>

<Object
Type="OBJECT" Schema="User1" Name="Table1"/>

</PermissionStatement>

<PermissionStatement
Action="GRANT">

<Permission>SELECT</Permission>

<Grantee>User1</Grantee>

<Object
Type="OBJECT" Schema="User1" Name="Table1">

<Columns Treatment="INCLUDE">

<Column Name=”Id”/>

<Column Name=”Name”/>

<Column Name=”Updated”/>

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

<PermissionStatement
Action="GRANT">

<Permission>SELECT</Permission>

<Grantee>User1</Grantee>

<Object
Type="OBJECT" Schema="User1" Name="Table1">

<Columns Treatment="EXCLUDE">

<Column Name=”Created”/>

<Column Name=”Updated”/>

</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];

GO

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];

Or

--
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];

GO

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'

GO

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

GO

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

GO

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

GO

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

GO

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

GO

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

GO

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

GO

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

GO

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

GO

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

GO

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

GO

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

GO

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 target’s 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.

Related Links

Microsoft Visual Studio 2008 Service Pack
1: http://www.microsoft.com/downloads/details.aspx?FamilyId=FBEE1648-7106-44A7-9649-6D9F6D58056E