Connecting SQL Server Management Studio and SQL Server Profiler to LocalDB

I find the Visual Studio 2015 SQL Server Object Explorer to be quite slow. Also, as anybody working with an ORM, I want to monitor the SQL-commands being fired against the database during development to make sure I didn't screw up too much. 

Wouldn't it be nice to use good ol' SQL Server Management Studio and the SQL Server Profiler for that? I think so, but the Internet says NO! I've accepted that as a truth for some time, but yesterday I decided to revisit the topic, and actually found out that it works just fine.

Now I can enjoy both the speed of SSMS and the power of the Profiler without replacing LocalDB. 

1. Install SQL Server Management Studio, mr Hanselman has created a nice download overview here:
   http://www.hanselman.com/blog/DownloadSQLServerExpress.aspx
2.  Open a Command Prompt and run this command
   
3. Now find the instance pipe name for the instance you want to connect to
   
4. Use the instance pipe name as server name in the connect dialog for SSMS, and you're ready to rumble.
   

EF4 CTP5, CodeFirst, connection strings and SQL Server Express/SQL Server 2008

I’ve been spending some time looking at EF4 CTP5 and the CodeFirst support. Looks really promising and along with the MvcScaffolding it’s really a breeze to work on the domain model and have the changes reflected in the repositories and DbContext.

In my sample app (a regular empty ASP.NET MVC3 Web Application) I added a custom Model, and used scaffolding to generate the CRUD views, controller, repository and the DbContext. Things went smooth and I could add/edit/delete my model items using the generated code without any problems. But where is the data actually stored?

Default connection string

If you study the MusicStore example (based on EFCodeFirst.0.8) you’ll see that the connection string in web.config points to a dedicated .mdf file (SQL Server Express) under the App_Data folder. In my sample app the web.config did not include any connection string at all, and the App_Data folder remained empty even though data obviously was created somewhere.

That somewhere is specified by the default connection string used by EF4, and points to:
C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\[Fully.Qualified.Name.Of.Your.DbContextClass].mdf

Using an explicit connection string

It feels better to have all things gathered at one place. So how do you change that to an explicit .mdf file under App_Data? Easy – just add a new connection string to the web.config like the one found in the MusicStore example right?

Well no, I kept getting various SQL exceptions:

• Exception Details: System.InvalidOperationException: Unable to complete operation. The supplied SqlConnection does not specify an initial catalog.
• Exception Details: System.Data.SqlClient.SqlException: Cannot open database "JobnetDb" requested by the login. The login failed.
• Exception Details: System.Data.SqlClient.SqlException: Database 'C:\FRER\Cybercom Projects\2011 - AMS Jobnet Codecamp\src\JobnetCC\App_Data\JobnetDb.mdf' already exists. Choose a different database name.

After some testing back and fourth I finally found a connection string that worked.

What’s important to highlight is

• The name attribute should match the name of your DbContext class.
• The Initial Catalog part of the connectionString must be specified.
• The Initial Catalog and the AttachDbFilename values must match.
• The .mdf filename specified must NOT match a previously created database. Does not matter if the previous .mdf file has been deleted from C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\ and/or App_Data.
  For some reason SQL Server Express seems to remember that and you get the login failed exception (restarting the SQL Server Express service does not help neither).

Want to use a SQL Server 2008 database instead of SQL Server Express?

Easy enough – follow these steps:

1. Create a new empty SQL Server 2008 database (using SQL Server Management Studio)
2. Diff the SQL Server Express database schema against the newly created SQL Server 2008 database.
   Use your favorite database schema comparison for that. I use Open DBDiff.
3. Copy the data on table EdmMetadata from the SQL Server Express database to the SQL Server 2008 database.
   I did that manually (good cashflow? RedGate’s SQL Data Compare)