How to Configure MS SQL Server for Testing with Visual Studio

When setting up MS SQL Server for test projects with Visual Studio I generally  stumble across the following problems only:

• Use SQL Server Express or Developer Edition?
• Enabling remote access
• What are the default database instance names

With Oracle I have a little more trouble. See How to Configure Oracle for Testing with Visual Studio

Use SQL Server Express or Developer Edition

For testing it is OK to install SQL Server on a development Workstation – in case you don’t want to test things like real-world performance. I hope the service pack install problems with Express edition and Developer Edition installed on one machine are solved. I recommend always installing Express Edition, because it is cumbersome to tell AppFabric to use the Developer Edition instead of Express. Install

• Visual Studio 2010 without SQL Server Express
• Install SQL Server Express 2008 R2 with Management Tools
• Install SQL Server 2008 R2 if you need feature not included in Express edition or favor the full Management Studio

Enable SQL Server Remote Connections

To enable remotely connection to an SQL Server:

• Computers should be in same Workgroup or Domain
• On SQL Server machine:
  • Enable Remote Connections using SQL Server Management Studio (Properties, Connections)
  • Enable TCP using SQL Server Configuration Manager (Client Protocols)
  • Open Port 1433 for TCP in Firewall http://msdn.microsoft.com/en-us/library/dd857537(VS.85).aspx

Default Database Instance Names

Because it is cumbersome to wait for the connect timeout when entering the wrong name or browsing, it is good to know how to connect to the SQL Sever default instances.

To connect to the default database instance on the local machine use:

• Developer Edition: (local)
• Express Edition: localhost\SQLExpress or .\SQLExpress