How to: Specify the Local Instance of SQL Server to use for Design-time Validation

  • Article

To perform design-time validation, Microsoft Visual Studio Team Edition for Database Professionals requires an instance of Microsoft SQL Server 2005. By default, Team Edition for Database Professionals uses the local instance of SQL Server 2005. You can specify an instance of SQL Server 2005 Developer Edition, Express Edition, or Enterprise Edition (including the Enterprise Evaluation Edition). To use the default instance for design-time validation, you leave the instance name blank.

If you specify an instance of SQL Server 2005 Express Edition, the following features will be unavailable:

  • Full-text indexing

  • Table partitioning

  • Database mirroring (if you use it in a stored procedure)

  • Database snapshot

  • Parallel index creation (MAXDOP option on CREATE INDEX)

  • Indexed Views

  • Partitioned Views

  • Online index operations (ONLINE option on CREATE INDEX)

If you use one or more of these features in your database and you specify an instance of SQL Server 2005 Express Edition for design-time validation, an error will appear.

Note

If you require features such as full-text search in your database projects, you must also install support for them in the design-time validation database before you can use them in your database projects.

To Change the Instance of SQL Server that is used for Design-time Validation

To change the instance of SQL Server that is used for design-time validation

  1. On the Tools menu, click Options.

    The Options dialog box appears.

  2. Expand the Database Tools node, and click Design-time Validation Database.

  3. In the SQL Server Instance Name (blank for default), type the name of the instance of SQL Server 2005 that you want to use for design-time validation. You must provide only the instance name. If you specify **./**InstanceName, an error appears.

    Important

    The specified instance must be running in order to create and modify database projects and the objects that are contained in the database projects.

  4. Click OK.

Important

You must now unload and reload your project for the change to take effect. You can do this using the Project menu or by closing and reopening the solution.

Security

You must have permissions to create, modify, and delete databases in the design-time validation database. If you must allow users who are not administrators to create design-time validation databases, you must use the following procedure:

To configure your local SQL Server instance to support non-administrative users

  1. Create a local Microsoft Windows security group.

    The recommended name is VSTEDPUsers. If you do not know how to create a security group, see the documentation for your operating system.

    See the next procedure for the steps to create a security group if you are using Windows XP.

  2. Add all users who will use Team Edition for Database Professionals as members of the security group that you just created.

  3. Run the Transact-SQL editor from Team Edition for Database Professionals or a tool such as SQL Server Management Studio and connect to the server as a system administrator.

  4. In a query window, execute the following:

    CREATE LOGIN [ComputerName\VSTEDPUsers] FROM WINDOWS
Exec sp_addsrvrolemember 'ComputerName\VSTEDPUsers','dbcreator'
Exec sp_addsrvrolemember 'ComputerName\VSTEDPUsers',' securityadmin'

    You must substitute the name of the computer that hosts the SQL Server 2005 database in place of ComputerName.

  5. If you need SQLCLR integration support for your SQL Server 2005 database projects, you must also execute the following:

    exec sp_configure 'clr enabled', 1
reconfigure

    Your users should able to connect to your local instance of SQL Server.

To create a security group using Windows XP

  1. Click Start, and then click Control Panel.

  2. Double-click Administrative Tools, and then double-click Computer Management.

  3. In the console tree, expand Local Users and Groups, and then click Groups.

  4. On the Action menu, click New Group.

  5. In Group Name, type a name for the new group.

  6. In Description, type a description of the new group.

  7. To add one or more users to the new group, click Add.

  8. Click Create, and then click Close.

    Your security group has been created with the users whom you specified.

See Also

Tasks

How to: Create Empty Database Projects

Concepts

Troubleshooting Data Generation Issues
An Overview of Database Creation and Deployment in a Team Environment