Ok, fifth time I’ve had to Google this (I keep forgetting the exact steps), and unfortunately not enough people have actually written about this error message for the first 2 to 3 pages in the Google search to be of any use. So let me make this one short and sweet.

You get the following error dialog

The Web project ‘YourProjectNameHere’ requires SQL Server Express, which is not installed on this computer. The recommended database engine for Visual Studio 2012 is SQL Server LocalDB Express.

To upgrade the project database to use SQL Server LocalDB Express, double click the database file and follow the instructions. Note: After this upgrade, the project database cant be modified using earlier versions of Visual Studio. To continue using SQL Server Express for this project, install it from the Microsoft Download Center.

Ok, this sure seems like it is a straight forward thing to fix, but it isn’t (doh).  So why isn’t it an easy thing to fix? because with EF and Code First and Design First more and more people are pushing up examples that don’t actually have a database in them.  They depend on EF to JIT build the database for the project (and for you) the first time you run their sample.

That of course means you can’t double click on the project database as instructed by the helpful Microsoft dialog, because there IS no project database to double click on. But wait a sec, if there is no project database how is Visual Studio 2012 determining that you aren’t using the latest greatest from Microsoft.  Well because despite what the dialog says, VS 2012 is actually NOT looking at the database, it is looking in the project’s active .config file and the connection strings therein.

So for the example of the project above in the web.config here is what comes from the author for the Connection strings entries

  <connectionStrings>

    <add name="ApplicationServices" 

         connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" 

         providerName="System.Data.SqlClient" />

  </connectionStrings>

So the problem here is the section that says “data source=.\SQLEXPRESS”. VS2012 is reading that in the .config file and warning you.  Now if there was a database file in the project you could double click it and a wizard would run that would make the necessary changes for you.  BUT if the project author left out the database, then you have a chicken / egg problem, you can’t create the database to upgrade it until you have already upgraded the database.

So how do you get around this problem? Easy, just need to make a quick change in the .config file and you are ready to rock

What we need to do is change the .\SQLEXPRESS to indicate instead the LocalDB engine and then EF Code First / Model First will work just fine and your database will be created without requiring you to install SQLExpress.

Change

connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|DatabaseName.mdf;User Instance=true"

To

connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\DatabaseName.mdf;Integrated Security=True"

And you are done.

Thursday 12 May, I presented at the Hawke's Bay Dot Net User Group (a group within the NZ MS Communities) on SQL Server Indexing. The idea for this presentation was born after a discussion we had at Red Jungle on a particular application we have created a while ago. After that discussion I thought that it would be a good idea to share some of the knowledge we have built up with working on SQL Server for close to 10 years now and share it with the local community. I had forgotten that it actually takes quite a bit of time collecting all the details of your presentation, making sure you have some good demos and then creating the presentation. But in the end it worked out quite well, and I believe it was helpful for at least a good part of the audience.

The presentation is aimed at the developer audience we have here in the DNUG. I don't expect DBAs to find a lot of new stuff in what I presented. Although you never know of course. But, as we do, modern day developers of smaller software development companies are also the database designers and maintainers, and I believe that every developer who is in that position should at least know what I presented. That will make him a lot better developer for as far as SQL Server is concerned.

I have put most of the content I presented available for download for you below. Feel free to use it in any way that is beneficial to you.

• The PowerPoint slides itself
• The tool used to generate test data for the database and running the speed test at the start. Check out the "ReadMe.txt" file for how to use it.
• The SQL scripts for the five demos referred to in the PowerPoint slides

There are a whole lot of additional resources available on this topic. I have listed some of them here. Most of these were used as inspiration for this presentation. I can highly recommend the videos from SQL Skills. They are very useful.

On the physical structure of Indexes:

• SQL Skills video on Index basics
• The Clustered Index debate (how to choose your Clustered Index keys, SQL Skills video)
• MSDN Tables and Index Data Structures

On Optimizing indexes and finding the right indexes

• General tips on optimising indexes
• How can you tell if an index is being used?
• Find Missing and Unused Indexes

General sites:

• SQL Server Central
• SQL in the Wild