SQL Server Express LocalDB

about the SQL Server Express LocalDB Mode of Operation

The minimum file required for a SQL Server Express instance is copied when LocalDB is installed, basically LocalDB is almost identical to the full SQL Server Express Database engine, and SQL Server D is started LocalDB way Atabase Engine instances have the same limitations as SQL Server Express (because they share the same files as SQL Server Express), but LocalDB has more than SQL Server Express because it simplifies administration Some limitations, but these limits usually do not affect development. Compared to the older version of SQL Server Express, there are some differences between the two:

• SQL Server Express
• The process of installing SQL Server Express takes a long time
• through SQL Server Express installation Only one SQL instance (Instance) can be installed at a time, and to install more instances you must run SQL Server Express setup   once;
  Note: The default instance name is   sqlexpress
• is required through   SQL server  Configuration Manager   or   Windows   Service Manager   Start this instance
• SQL Server Express LocalDB
• Install SQL Server Ex Press localdb  process is very fast (installation time is very short)
• after you install SQL Server Express LocalDB, the default is to have an auto instance of  < strong>v11.0 , but later to establish another instance no longer needs to run the Setup program, just through   SqlLocalDB.exe   Tool program can create a new instance arbitrarily, Build an instance for no more than 3 seconds! The
• can start the instance automatically with a special connection string, or it can be started using the   SqlLocalDB.exe   Tool launcher, and the application can begin to use the database without complex or time-consuming component work!
• in the same host, each user can establish their own LocalDB instance, each instance is the different process that runs as a different consumer , so different users can have instances with the same name .

SQL Server Express LocalDB distinguishes between two instance types, namely:

• LocalDB Automatic Instance (Automatic Instances)

LocalDB automatic instances are common.

• After installing LocalDB v11.0 is automatic instance , although feel only one instance, but because in the same host, each user can establish their own LocalDB instance, all users have the same name v11.0 , but each of them is separate processes (process). These instances are automatically created and managed for the consumer and are available to any application.
• Each LocalDB version installed on the user's computer has a LocalDB automatic instance. Future if the next version of SQL Server Express LocalDB appears, there will be a new instance name available, and the default auto instance name is a LocalDB release number followed by a V character followed by xx.x format. For example, v11.0 represents SQL Server 2012.
• LocalDB named instance (Named Instances)
• LocalDB named instances are private.
• These instances are owned by the consumer or a specific single application that is responsible for establishing and managing the instance.
• By default, different users cannot save the LocalDB named instance from the definition, unless you manually establish the share function of the named instance, enabling the sharing function to allow other users to access the named instance's database.

Understand SQL Server Express LocalDB the path to the database file where the instance

After installing SQL Server Express LocalDB, there will be an instance named v11.0by default, and the directory of the relevant files for that instance is usually located in the following directory (please replace <user> with your login account)

C:\users\<user>\appdata\local\microsoft\microsoft SQL Server Local db\instances

Or it is convenient to enter this directory with %LOCALAPPDATA% environment variable:

%localappdata%\microsoft\microsoft SQL Server Local db\instances

The following is an icon showing the directory, listing all instances of LocalDB:

The files you see under this directory (v11.0) are the relevant files for the instance, system database files, error records, record tracking, encryption keys 、... Wait, but if you create a user database in a LocalDB instance, you must explicitly specify the path to the data file and the record file, otherwise all established databases will be located in the %USERPROFILE% directory (c:\users\< username>)

Establish SQL Server Express LocalDB named instance

As you can imagine, a SQL database instance is like a full SQL Server database server, with its own system database (master, msdb, model, temp) in one instance, as we typically do with SQL Server, when If you create an additional LocalDB instance, these system databases will be completely independent of one another named instance, completely unrelated to each other.

In terms of development, you can even build a database environment that is exactly the same as the client's official host in the development test environment, which is very different from the previous SQL Server Express, and the new LocalDB is really a great place!

Before you can create a new LocalDB instance, you must understand the path to the SqlLocalDB.exe tool program, refer to the following path:

C:\Program Files\Microsoft SQL Server\110\tools\binn\sqllocaldb.exe

The following is an instruction to establish an LocalDB instance where "localdbtest" is the name of the instance you are able to name yourself:

SqlLocalDB.exe Create localdbtest

We then use the command to list all the instances that LocalDB has established and to view information about the specific instance:

SqlLocalDB.exe Info
SqlLocalDB.exe Info Projects

Then we look at the path to the entity file described earlier in the article, and you'll find one more directory, and we'll use the %LOCALAPPDATA% environment variable to enter the directory to see:

%localappdata%\microsoft\microsoft SQL Server Local db\instances

Entering the directory will see these system database files, error records, record tracking, encryption keys as well as the full version of SQL Server 、... And so on, as shown:

SQL Server Express LocalDB with other SQL Server Comparison of versions

Prior to the advent of SQL Server 2012, SQL Server had different versions depending on the operating environment, with different restrictions on running, such as:

• SQL Server Enterprise for large data or high availability environments
• SQL Server Standards (Standard Edition, suitable for use in general database processing environments)
• SQL Server Express (lightweight, suitable for use in single-or small-scale database environments)
• SQL Server Compact (lite, also known as SQL CE, for mobile or embedded system environments)

And what kind of a location is SQL Server Express LocalDB? The complexity of the database system has been reordered as follows:

• SQL Server Enterprise > Standard > Express > LocalDB > Compact

Because SQL Server Express LocalDB is similar to SQL Server R2 Express in front edition, the limitations are:

• The LocalDB instance collation defaults to SQL_Latin1_General_CP1_CI_AS and cannot be changed.

However, database hierarchy , data row level , and expression level sequencing are supported.

In other words, you can simply specify its collation when you build the database , or mount a database file that is moved by another database system, or restore a backup file from another database.

Autonomous databases follow the metadata defined by autonomous database sequencing and the tempdb sequencing rules.

• LocalDB must not be a consolidated replication Subscriber.
• LocalDB does not support FILESTREAM.
• LocalDB only native queues are allowed for Service Broker.

RELATED LINKS

• Download Microsoft SQL Server Express
• SQL Server Express LocalDB (MSDN)
• SqlClient support for LocalDB (MSDN)
• Sqllocaldb Utility
• Introducing LocalDB, an improved SQL Express
• SQL Server LocalDB Management Tour

SQL Server Express LocalDB