Sitecore CMS Blog by Alex Shyba: Sitecore on SQL 2012 “Denali” with LocalDb

SQL 2012 codename “Denali” is now RTM since March 7th, which means I can start leveraging the new LocalDb feature with Sitecore!

LocalDb is a great way to run embedded SQL on your dev box, so you don’t end up with hundreds of databases attached in your SQL Mgmt Studio.

Basically, the idea is that you will be able to have the SQL engine auto started and Sitecore databases auto attached simply by having the following in your connection strings:

   1: <add name="core"

   2:      connectionString="Server=(localdb)\v11.0; Integrated Security=true;
                             AttachDbFileName=E:\db\Sitecore.Core.MDF" />

   3: <add name="master"

   4:      connectionString="Server=(localdb)\v11.0; Integrated Security=true;
                             AttachDbFileName=E:\db\Sitecore.Master.MDF" />

   5: <add name="web"

   6:      connectionString="Server=(localdb)\v11.0; Integrated Security=true;
                             AttachDbFileName=E:\db\Sitecore.Web.MDF" />

You can find more information about LocalDb here and here.

Steps I took to get it working:

1. Grabbed that SQL 2012 distro, install it with Express options, and don’t forget to check “LocalDb” checkbox:

2. Installed .NET 4.0.2 update as it has the LocalDb features, plus according to the following note from this doc, this should help you connect to a LocalDb instance without named pipes:

If your application uses a version of .NET before 4.0.2 you must connect directly to the named pipe of the LocalDB

3. Using “SqlLocalDb.exe” utility, started the default LocalDb instance (v11.0):

4. Set my app pool to use local administrator account, otherwise I was getting the following exception:

50 - LocalDB error occurred. Cannot create an automatic instance. See the Windows Application event log for error details.

5. Adjusted my connectionStrings.config as shown above.

Started the site.

At first I was getting exceptions about LocalDb instance that cannot be started, which was pretty random as the SqlLocalDb utility was saying that it was running.

50 - LocalDB error occurred. Error occurred during LocalDB instance startup: SQL Server process failed to start.

So I had to use the pipe name in the connection string for the “Server Name”, which worked beautifully, however, the ID in the pipe changes when LocalDb instance restart, which would not work for me long term.

<add name="core"

     connectionString="Server=np:\\.\pipe\LOCALDB#B1AFD502\tsql\query;
                       Integrated Security=true;AttachDbFileName=...MDF" />

In order to troubleshoot why I cannot connect to the default LocalDb instance using (localdb)\v11.0 name, I took obvious first step to look into IIS app pool configuration, but nothing helped.

So I remembered about IIS 7.5 Express released a while back, since the main hunch was the problem was caused by some security setting within IIS. So I got IIS Express downloaded and installed, ran it from the command line:

C:\Program Files (x86)\IIS Express>iisexpress /path:C:\inetpub\wwwroot\MySite\Website /port:80 /clr:v4.0 /systray:true

and boom! http://localhost/ brings up my site:

And IIS Express shows up in sys tray, very cool!