With the introduction of SQL Express LocalDB (part of SQL Server 2012 a.k.a. "Denali"), the available Microsoft SQL database options available now are:
- Regular SQL Server SKUs
- SQL Server Express
- SQL Server Compact Edition (CE)
- SQL Express LocalDB
LocalDB is a lightweight version of SQL Express with zero administration and low installation foot-print. Strictly speaking, LocalDB is not a new SQL Server edition by itself; rather, it is a database engine activation mode in which the SQL Server process (sqlservr.exe) runs as a child of the calling process but without exposing any connectivity surface. The engine runs as long as the parent process runs but as a private named instance. The name of the instance can be anything given at the time of creation. Once started, other applications cannot connect to it unless the instance is explicitly shared by its owner. MSDN says user instance feature of Express editions is deprecated and developers should switch to LocalDB going forward. LocalDB also has an automatic instance which is created & started automatically and public. Any application can connect to it and perform database operations. Technically, this automatic instance is also a named instance with a special name that reflects the engine version; for SQL Server 2012, it is v11.0. Hence, the automatic LocalDB instance is identified as (localdb)\v11.0. Any number of private instances can be spun off from single installed LocalDB bits.
From a programming perspective, the API surface remains the same, be it managed or native. In order to connect to a LocalDB instance, the Data Source keyword of the connection string should start with (localdb) followed by the instance name. Example:
SqlConnection con = new SqlConnection (@"Data Source=(localdb)\v11.0;Integrated Security=true;Initial Catalog=master");
Here is a n00b code!
As with user instances, AttachDbFileName keyword is also supported to connect to a database directly. Output from a SQL Server 2012 RC0 instance for the above code is shown below:
Note the following about LocalDB:
- SQL Express edition is upgradable to higher SKUs, but LocalDB is not. LocalDB is intended to be for developers without the overhead of regular SQL Server installations.
- All the database size limitations of Express apply to LocalDB as well.
- LocalDB instances do not use Win32 services; the engine is loaded on demand by the parent process.
- sqllocaldb.exe is the simple command line admin companion for LocalDB. It supports, creating, starting, stopping & deleting LocalDB instances (you cannot delete an automatic instance).
- LocalDB instances are named, user specific and private (except the auto instance).
- LocalDB stores system database files in user’s AppData folder (see below). This means that when LocalDB is used from an application running as a non-interactive user account (including IIS), that user account should be configured to load user profiles, Otherwise, the instance starting process will fail.
- There may be a delay when a local DB instance is created or started for the first time, causing the parent application to timeout.
- Multiple users on a computer can create and start database instances simultaneously using the same installed LocalDB bits. Unlike other SQL Server editions, LocalDB does not require multiple installations for multiple database engine instances.
- Current (pre-SQL Server 2012) versions of SQL Server Management Studio cannot connect to LocalDB instances using server name prefix "(localdb)". Instead they should use the raw named pipe path of the LocalDB instance (this can be retrieved using sqllocaldb utility):
Remember that the part of the named pipe path after # will change with every instance start and hence it cannot be hardcoded in the code. You can also use this named pipe path in the connection string from code. The following is a perfectly valid connection string & connects to the specified local DB instance:
SqlConnection con = new SqlConnection (@"Data Source=np:\\.\pipe\LOCALDB#B7BB9C55\tsql\query;Integrated Security=true;Initial Catalog=NorthWind");
category: SQL Server