Updated 2011-11-11: Added a link to the post on how LocalDB handles database file locations.
Updated 2012-01-03: Clarified that only SSMS from SQL Server 2012 (or newer) will work with the .NET 4 Update 4.0.2, since earlier SSMS versions are using .NET 3.5.
.NET team has just released Update 4.0.2 for .NET Framework 4. This update enables connecting to SQL Server Express LocalDB via System.Data.SqlClient provider.
.NET Framework 4 Update 4.0.2 can be downloaded from http://support.microsoft.com/kb/2544514
Well, let's give it a try!
We start by installing SQL Server 2012 Management Studio RC0 (SSMS) and LocalDB together with .NET 4. The version of SSMS is important as earlier versions are using .NET 3.5 and currently only .NET 4 can be updated to understand LocalDB connection strings. Next we update .NET 4 to 4.0.2 (hey, no reboot in my case!). Then fire up SSMS. When prompted for the server address we specify "(localdb)\v11.0", like this:
Clicking Connect and … (drum roll please!) …
OK, so we confirmed SSMS connects to LocalDB. Which is great, but how about doing something more useful for a developer? The next logical step would be to create a database. We know the drill -- right-click the Databases node in Object Explorer and select New Database. So far, so good. After typing in the name of the new database (in my case LocalDBTest) we are ready to hit OK...
Clicking OK and ... we hit an error!
This is obviously a bug, but there are at least two reasons we shouldn't panic. First, the bug has an easy workaround that we're going to use very, very soon. Second, it points to an important characteristic of the way LocalDB handles database files. I will explain it in more detail in the next post, but for now let's get back to LocalDB and creating our database.
To work around this problem we need to explicitly specify the folder for the database files. For the lack of a better location let's just put them into My Documents, like this:
This time clicking OK creates the database without any problems.
We are now ready for some serious database development, but this too will have to wait for another post.
- Krzysztof Kozielczyk
Please share your feedback with us! Feel free to start a thread on SQL Express Forum, hit the "Email Author" button on this post or file a Connect item!