SQL11UPD03-TSCRIPT-07
This wiki is a transcript of a previously recorded video.
Related content assets:
Hi folks, I’m Greg Low. I’m a SQL Server MVP and part of the Microsoft Regional Director Program. In this session I want to have a look at a new addition of SQL Server Express called SQL Server Local DB. It’s a new attached process version of SQL Server Express rather than a service based process.
First up, if you look at how SQL Server Express has been used, it’s been used in a number of ways. First up, it’s a free SQL Server edition and in fact it’s a very, very capable edition. In fact, I see some people almost think of it as a hobbyist thing or something, but it’s not, it’s a very professional edition. It’s commonly used as things like frontends – perhaps I’ve got point of sales systems with lots of SQL Express systems all hooked up with a SQL Standard Edition. It’s been filling that sort of role and one of the advantages of it is that it’s been a very high level of compatibility with the remainder of the SQL Server product line. So when it comes time maybe to move from Express and go to upgrade to SQL Server to a Standard edition or above then this has been a very painless process when you’re coming from Express.
The second thing is that developers have often been using it as a basic sort of test environment and it’s also been installed when people install things like Visual Studio. It’s been installed to provide a database engine alongside that where you want to do some testing. It’s really hard to fill both of those needs with a single product, but developers do want to have complete compatibility. They don’t want to be developing against one target and then when they go to move to a bigger database engine having to rewrite their code.
It’s important to understand that LocalDB doesn’t replace Express. SQL Server Express continues on and that’s a standard edition. Sorry, I shouldn’t use the word standard, but it is a common edition of SQL Server that continues on into the future. Just like we have Standard edition, BI edition and Enterprise edition and so on.
LocalDB is a special version of the Express edition. The aim for this is to have a really tiny footprint and also be really easy to deploy. The aim here is to be able to have something simple to work with, to be able to deploy it simply, but then also to have that easy path to upgrade in the future.
The LocalDB runtime is constructed different to Express in that Express is installed as a service inside your machine. If you go looking at SQL Server Configuration Manager or if you look at the list of services running inside the services applet in the Administrative Tools you’ll see the services running for SQL Server Express. By comparison, SQL Server LocalDB, or Express LocalDB, gets launched in association with an existing program. I run my program, it causes LocalDB to be fired up. The same thing applies with Visual Studio. Visual Studio can be running and then it can launch an instance of LocalDB. This is a child instance that’s launched as a separate process but it’s launched as an attached process to the program that started it in the first place. You won’t see it in the list of services – it’s a completely separate thing. It is using the same executable. If you look at something like Task Manager you’ll now find another SQL Server.exe running inside of that. So it’s the same code base with a high level of compatibility but very different in terms of how this is deployed.
There’s no configuration required on installation, a simple MSI is provided. This becomes a really interesting database option for you to deploy along with your own applications. There are minimal prerequisites and it copies as few files as possible while it’s being deployed. Because this is able to be used in multiple environments it is a really good test platform. For example if I build a database project in Visual Studio using SQL Server data tools and then I go to test that, LocalDB is the easiest option to spin up and run most tests against. I might, though, want to exert some additional control over it. There’s a SQL LocalDB.exe utility supplied alongside of it. That would allow me, for example, to improve the startup time for my application by launching that process before my application went to connect to it, or perhaps I could decide to shut something down in the background. I might want to list the instances of LocalDB that are in use. The utility lets you do all of those things.
This directly replaces the idea of User Instance Support that we had in earlier version of SQL Express. User Instance Support was deprecated in SQL Server 2008 and this supplies a good alternative to it.
In terms of where it creates its database files there are three basic options. One is that I can specify when I create a database where I want it created, and that could just be a simple file path. If I don’t specify where that is and I just say give me a new database, it will create it in the root of my user profile. The third option if I have a database already in existence it supports the attached DB filename parameter in a connection stream that allows me to connect to an existing database.
The basic idea here as I said is that it uses that same SQL Server.exe executable. If you look in Task Manager you’ll see another copy of that running when it’s spun up. All of the clients are providers of the same. The T-SQL is the same. You only need to install this once for each major version of SQL Server on a machine. No services are installed, everything is started and stopped automatically as required. When an application fires up like we have in SQL Server with the local memory provider, we have local in brackets that we can use to connect to a local SQL instance using shared memory. We now have a LocalDB option that says I want to talk to LocalDB and then backslash and the particular instance I’m after.
The generic one for SQL Server 2012 is just V11.0. If I connect my data source as LocalDB\V11.0 in my app, as long as the LocalDB has been installed in the first place, that’s all I need to do and that will fire up an instance and will attach to my application. A few minutes after I finish connecting to it, it will automatically be shut down again.
If you compare this with Compact edition the footprint is somewhat bigger but that makes sense because it is a real version of SQL Server. Compact edition sits around 4MG. LocalDB is up around 140MG at the current time in terms of deployment.
In terms of T-SQL compatibility though, Compact edition is somewhat like a subset but it’s really more like an intersecting set where it has some commands that are not standard T-SQL and it doesn’t support many of the standard T-SQL commands or data types. By comparison, LocalDB is a fully compatible type of version. It basically supports everything that would be supported on Express largely, so we have this really high level of compatibility. That means as we go to do upgrades and so on, it’s painless. Compact edition only supported the very core data types. LocalDB has really rich data type support. For example things like spatial data types are supported directly inside of LocalDB. LocalDB also supports stored procedures so we can build all of that type of code directly and then test it before we go on to another type of server or maybe that is how we deploy our application using LocalDB as an embedded database engine.
As I said, all you need to do is make sure that for each major version of SQL Server that LocalDB is already installed. There is a simple MSI supplied to help you do that installation. It can be installed silently, completely without any sort of prompts coming up. The other thing is that it doesn’t throw any sort of error if it finds that it’s already installed. It allows you as part of your application deployment to just deploy it along (whether or not it’s already there without a concern) then deploy your application that goes on and uses it. It’s a very good solution for these types of applications moving forward.
Return to SQL Server 2012 Developer Training Kit BOM (en-US)