SQL11UPD03-TSCRIPT-08

This wiki is a transcript of a previously recorded video.

Related content assets:

  • Presentation: Using SQL Server 2012 Express Local Database Runtime as an Embedded Database (SQL11UPD03-DEMO-04)
  • Video: Using SQL Server 2012 Express Local Database Runtime as an Embedded Database (SQL11UPD03-REC-08)


Using SQL Server 2012 Express Local Database Runtime as an Embedded Database

Hi folks, I’m Greg Low. I’m a SQL Server MVP and part of the Microsoft Regional Director Program. In this video I want to just quickly show you how easy it is to work with SQL Server LocalDB or Express LocalDB as an embedded database for your application.

 

In a folder on this system I’ll just change to ListEntryApp. In this folder I’ve got the executable for my application and also the install file SqlLocalDB.msi. The MSI file is all that we need for installing SQL Server LocalDB. Now the only time it needs to be installed is once for each major version of SQL Server. At present all of them would use the same instance or the same installation, not necessarily the same instance but for SQL Server 2012. If it is already installed on the machine that’s not a problem, you can install it on the basis that even if it is already there it won’t raise an error. So as you’re installing your own application you could simply shell out and cause SQL Server LocalDB to be installed.

 

Let me quickly show you what’s involved in this. Let’s clear up a little more space because we need the whole command line. Msiexec and then saying /i SQLLocalDB.msi is the installer file I want to install. I can say /qn – in this case is saying do this silently. The other thing I need to do is accept the license terms, so if I say IACCEPTSQLLOCALDBLICENSETERMS=YES that will simply avoid that ever popping up in the middle of the installation. So if I run this notice that even though this is already installed I got no warning, no error or anything like that.  This could be very silently done as part of your application install.

 

This ships with a utility so if I say SQLLocalDB and then said info, what this will show me is instances of SQLLocalDB that are already installed on the machine. Let’s fire up and run this app. So if I just say ListEntry, this is firing up an application. What the app is doing is connecting to the instance of SQL LocalDB, then it’s going off and creating the database if it’s not already present and so on. Notice that what its connection to is LocalDB in brackets \v11.0, which is the instance it’s connecting to. Notice that was one of the instances that we had coming up here before. If I now again say SQLLocalDB, I could say show me the details about that specific instance. Notice that this is now setup and running automatically. With this utility I could even avoid the startup delay on the application, which is short anyway, but I could use the utility to start and stop the instance rather than having the application be the only thing that controls it. Generally you do not have to manage at all. You start the application, it will start up the instance and start it running. Sometime after the application stops, usually within a couple of minutes, you’ll find that it will shut down again.

 

Importantly, this is running as an attached process, a separate process, but an attached process to the application itself. If we go and look in SQL Server Configuration Manager – so let’s grab SQL Server config tools and Configuration Manager and bring up and look at the list of services – you’ll notice that while there are SQL Server services running here, this is not one of them. What we have in place, you’ll notice even with the log ons here, none of these are currently running as an administrator. However, let’s go down here on taskbar and start Task Manager and then start looking at the processes that are running. In amongst here notice that we’ve got two copies of the SQLServer.exe running. One is the service that is the standard service running in the background, but notice there is another one here now running as the administrator because it’s been launched within our current session.

 

The other thing about this is you might wonder, well, where are the databases? If I go in and look at that folder, notice the folder where we had it, the ListEntry application, I’ve now got a .mdf and a .ldf file sitting in here. They weren’t there a moment ago. That’s because the application when it created the database specified that’s where it wanted the databases to be. You don’t have to do that. If I hadn’t just said create database it would have in fact created them for me but in the root of my profile, because it wouldn’t know where else to put them. A good practice is usually to indicate where you want the databases. The other thing you could have done is if we already had a pre-created database and we simply wanted to attach it, LocalDB also supports the attached DB filename option that you can place in your connection stream to work with an existing database.

 

So LocalDB really makes a wonderful addition with a high degree of compatibility with the rest of SQL Server as an embedded database for your app.


Return to SQL Server 2012 Developer Training Kit BOM (en-US)