SQL Server’s LocalDB Database Engine

As one who is currently concentrating on developing desktop applications since I retired from corporate development in 2014, I have spent an enormous amount of time researching the best database engines to support such applications. And with current push for all things to be stored in the Cloud or large data-centers, little time has been towards developing high quality and affordable database engines.

To be sure, since the advent of the Internet, increasingly desktop database engines have always been given a second-class citizenship status. Nonetheless, the only third-party database engine to survive the milieu of evolving events in the Information Technology has been the indomitable SQLite engine, which is still being actively supported and developed. In addition, the SQLite Foundation has in the past year taken on the additional responsibility for supporting the comparable ADO.NET library that allows .NET developers to seamlessly use this excellent database engine in their own applications.

Firebird is yet another quality database engine that emerged after Borland International\Inprise finally closed its doors, leaving its own formidable database engine, Interbase, to the vagaries of the marketplace. When Firebird reached a level of maturity that rivaled that of Interbase, Embarcadero picked up Interbase and implemented it in their own line of products replacing development of their own Blackfish SQL database engine with it.

For the work I am doing now, I decided against using SQLite but tried implementing the more powerful Firebird engine in its embedded model only to find the database’s SQL syntax so arcane along with such terribly, poor error reporting that I could not resolve even what would be considered by most developers as simplistic issues, with the more complex SQL code I wanted to implement. This is not a slight against Firebird itself as it is a relatively good engine but unfortunately, there is little real community support for this engine and even the project developers refuse to allow themselves to be queried in a support forum.

The PostgreSQL engine has a similar level of SQL ambiguity but not nearly to the level of Firebird’s and I was always able to resolve such issues as a result of the far greater community support.

As a result, my favorite database engine has always been and still is Microsoft’s SQL Server. It simply cannot be beat for its community support, documentation, and of course its incredible ease-of-use. Up to 2014, Microsoft had supported its excellent desktop database engine, SQL Server Compact Edition (CE) and then suddenly abandoned it with no real reason other than it did not fit into their overall plans for future support. Not surprisingly, Microsoft did not allow the source code of this engine to be outsourced so a third-party could pick it up and refine it as needed as it probably included some proprietary algorithms from SQL Server’s core engine.

In its place, Microsoft introduced SQL Server LocalDB, with the understanding that this was the new engine for desktop application development as well as development against SQL Server in which the final products would run against full scale SQL Server implementations.

LocalDB is a rather odd duck from the descriptions provided for it. As a database engine for desktop applications, it can take up to 160 megabytes of storage, which is a far cry from SQLite’s approximate 256k requirements or even Firebird’s embedded model, which requires around 2.5 megabytes of storage. As a result, the description as an excellent choice for desktop application development seems a bit of stretch.

The other promotion for LocalDB is that it makes an excellent platform for those who do not either have the capability of always connecting to a remote SQL Server during development or simply want to work with a local implementation throughout the development of their projects. This is a very strange way to promote this engine given that Microsoft already offers its SQL Server Express editions, which are fairly easy to install on workstations and provide everything any developer would need in order to create database intensive applications. And if for some reason enterprise level capabilities were required against a local SQL Server implementation, Microsoft freely provides their SQL Server Developer Edition for developers needing such capabilities.

Both SQL Server Express and LocalDB support database files up to 10 gigabytes of storage; six more than SQL Server CE offered, which is a definite advantage.

However, LocalDB is also promoted as not requiring any extensive administration for it. In other words, you simply connect to it and go…

Really???

If we are talking about the type of administration a DBA would normally perform, then such a claim is completely accurate. However, anyone who is going to take the time to build an application, any application, that requires a database engine of any capability is going to have basically the same set of tasks to perform no matter the engine used. At the very minimum, a developer will have to install the engine and ensure that it is running properly so that it can be connected to. This applies to SQL Server, SQL Server Express, and its sibling SQL Server LocalDB.

As to the latter, there is no need to concern oneself with roles, users, and permissions. However, if a developer is perfectly happy to develop with the “sa” role\user on their local implementation, than there is really nothing of concern here.

Again, the reasoning behind the concept of LocalDB is rather thin and from the research I have done on this oddity, it appears that many developers simply do not get it either.

However, what Microsoft does not note about LocalDB is its ability to be handled by a desktop application more easily than that of SQL Server Express, whereby to do so with this somewhat more extensive implementation requires permissions, which can be a bit of a task to surmount from an individual application. I have attempted this in my current work and simply put, it is not worth the effort.

Such an obstacle makes SQL Server Express makes a rather poor choice as a desktop application database engine, though Microsoft casually suggests it as such.

Unlike SQL Server Express, which runs as a standard SQL Server service on the machine it is implemented on, LocalDB runs as a process to the application making a call to it, making handling the database engine from within the application much easier since it only requires the same level of permissions as the application accessing it. As a result, the small but significant API that LocalDB supports can be run seamlessly from within an application using it.

This API provides the following functionality that any .NET application can make use of through the Process method…

LocalDB API

Usage: SqlLocalDB operation [parameters...]
Operations:
-?
Prints this information
create|c ["instance name" [version-number] [-s]]
Creates a new LocalDB instance with a specified name and version
If the [version-number] parameter is omitted, it defaults to the
latest LocalDB version installed in the system.
-s starts the new LocalDB instance after it's created

delete|d ["instance name"]
Deletes the LocalDB instance with the specified name

start|s ["instance name"]
Starts the LocalDB instance with the specified name

stop|p ["instance name" [-i|-k]]
Stops the LocalDB instance with the specified name,
after current queries finish
-i request LocalDB instance shutdown with NOWAIT option
-k kills LocalDB instance process without contacting it

share|h ["owner SID or account"] "private name" "shared name"
Shares the specified private instance using the specified shared name.
If the user SID or account name is omitted, it defaults to current user.

unshare|u ["shared name"]
Stops the sharing of the specified shared LocalDB instance.

info|i
Lists all existing LocalDB instances owned by the current user
and all shared LocalDB instances.

info|i "instance name"
Prints the information about the specified LocalDB instance.

versions|v
Lists all LocalDB versions installed on the computer.

trace|t on|off
Turns tracing on and off

SqlLocalDB treats spaces as delimiters. It is necessary to surround
instance names that contain spaces and special characters with quotes.
For example:
SqlLocalDB create "My LocalDB Instance"

The instance name can sometimes be omitted, as indicated above, or
specified as "". In this case, the reference is to the default LocalDB
instance "MSSQLLocalDB".


SqlLocalDB
This is the utility to administrate the localdb instances.

to get help: sqllocaldb -?
to print the version: sqllocaldb versions
to create an instance: sqllocaldb create "YourInstanceName"
to delete an instance: sqllocaldb delete "YourInstanceName"
to start an instance: sqllocaldb start "YourInstanceName"
to stop an instance: sqllocaldb stop "YourInstanceName"
to share an instance: sqllocaldb share "YourInstanceName"
to unshared an instance: sqllocadbl unshare "YourInstanceName"
to list all your instances: sqllocaldb info
to list the status of an instance: sqllocaldb info "YourInstanceName"
to set the trace on and off: sqllocaldb trace on|off

The result of this API then is that developers should have enough access to the LocalDB engine to make this engine rather flexible for the needs of any desktop application.

However, there is one drawback that in fact makes LocalDB less than an ideal choice for an embedded database engine; the in-process LocalDB service does not run continuously along with the application as other such database engines do. When first connecting to a LocalDB database, it can take a noticeable amount of time for an application to complete its initial connection and run the requested database process. LocalDB will remain active for several minutes so if another database request is made, the latency in processing the request will be no longer than would be expected against an in-process service that is active. Wait too long to issue a new request, LocalDB will shut itself down forcing a new and longer wait time to process this request.

On the face of it, most developers would probably wonder why such a stupid limitation was implemented in LocalDB and they would be correct in doing so. Did Microsoft believe that professional developers and even hobbyists would want to waste time developing against a database engine that would provide somewhat erratic wait times for processing?

It could be the fact that such a design is installed within the Windows` program directories on a workstation and not locally to the application. This would be in keeping with how all SQL Server implementations are installed. But still…

A more likely possibility is that with the ascendancy of Satya Nadella to the CEO position at Microsoft may have yielded unexpected results in the development of their products. 

With his known very narrow focus on Cloud-based product development, Nadella appears to have drunk the Kool Aid of the belief that Cloud-based services are the future and that Microsoft has to be on top of this new trend. This may be quite true from a business perspective. However from a military historian-analyst point of view (this is my avocation), this direction is a complete fool’s errand as anyone who understands siege theory can attest to. In layman’s terms, the Cloud is a disaster waiting to happen. However, leave it to business leaders to rush in where even fools dare to tread…

With such an emphasis on such pie-in-the-sky thinking, it has undoubtedly left its mark on product divisions across Microsoft narrowing the focus on how products should be developed. In this case, support for desktop development is weakened to promote all development for the larger, futuristic picture that people like Nadella want to infuse in their development communities who in turn will push for such implementations in their places of employment or with their clients.

The result is a very useful database engine with a very odd drawback to its processing, which makes very little sense from a technical point of view. However, if the perceived thrust of all new Microsoft products is to encourage all new development in the Cloud (for Microsoft, this would be their cloud service Azure) than there is an underlying logic to this inherent weakness to LocalDB.

Nonetheless, LocalDB is quite a powerful option for those who do want to build desktop applications using the extended development apparatus of a SQL Server database engine without the hassles of working out permissions for accessing more than SQL processing from an Express version of this database. Like the more robust versions of this engine, LocalDB supports just about everything that the other versions of the engine support, including store procedures and triggers.

As a result, developers who choose this option for their database engine in a desktop application will feel right at home using the standard SQL code the engine allows. For those who require or want a simple to use database engine without all the hassles, SQLite would be the best solution. And for those that want a fully embedded database with similar capabilities to LocalDB and are willing to learn the inherent syntactical differences\idiosyncrasies to SQL, than Firebird would be your choice.

To make using LocalDB easy to manage, developers will find that SQL Server Management Studio can connect to an instance of this engine allowing them to work with it like any other version of SQL Server.

On a final note, there may be a way to get around the latency of connecting to the LocalDB database engine within one’s application, though I haven’t tried this approach yet. This would be to implement a secondary thread in the application that based upon an interval setting would check the current state of your LocalDB database instance and if it is stopped, simply issue a Start command. This way, the user of your application should not have to worry about any time consuming connections\requests for a stopped engine.

To help interested developers in evaluating this database engine and testing out the above threaded approach, I have created a small console application that will allow you to test out the various LocalDB API commands with the exception of the Share commands since I didn’t see any use for them in what I am working on.

You may download this project from the following link…

• https://1drv.ms/u/s!AnW5gyh0E3V-hxfaDRo42FDzh5eZ