Today, let us focus on how we can configure the Localdb SQLserver management studio by our Support Techs. As part of our Server Management Services, we assist our customers with several SQL connections. 

Localdb SQLserver management studio

LocalDB SQL Server Express installation copies a minimum set of files that is necessary to start the SQL Server Database Engine. An instance of LocalDB SQLserver management studio has organized by using the SqlLocalDB.exe utility.

Initially, when the LocalDB is installed, you can directly initiate a connection using a special connection string. Eventually while connecting the necessary SQL Server infrastructure it automatically create or gets started, also enabling the application to use the database without complex configuration tasks.

Install LocalDB

The LocalDB installation can be done through the installation wizard or by using the SqlLocalDB.msi program. 

You can select LocalDB on the Feature Selection/Shared Features page during the installation process. Only one installation of the LocalDB binary files is managed for each major SQL Server Database Engine version. Multiple Database Engine processes will start and use the same binaries.

Description

The LocalDB setup program uses the SqlLocalDB.msi to install only the necessary files on the computer. Once installed, LocalDB will be an instance of SQL Server Express that will create and open SQL Server databases.

For example :

C:\Users\<user>\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\LocalDBApp1\

User database files will be stored where the user designates, basically in the C:\Users\<user>\Documents\ folder.

Automatic and named instances

LocalDB supports two kinds of instances i.e Automatic instances and named instances.

• Automatic instances of LocalDB are public. Created and managed automatically by the user and can used by any application.  This feature manages easy application installation and migration. Automatic instances prevent name conflicts with named instances of LocalDB. The name for the automatic instance is known as “MSSQLLocalDB“.

• Named instances of LocalDB are private. In other words, this is verified in a single application that is responsible for creating and managing the instance. Named instances provide isolation and can improve performance by reducing resource contention with other database users. The sysname data type and can hold up to 128 characters.  This can contain any Unicode characters that are legal within a filename.

Microsoft SQL Server LocalDB

Step 1. Check and create a LocalDB instance

Before connecting to your LocalDB instance with DataGrip, just make sure if your LocalDB instance is ready for a connection. To do this, you can simply locate SqllocalDB.exe and run the following command in a command prompt:

SqllocalDB.exe i

Next, create a LocalDB instance. To follow up on this, run the following command:

SqlLocalDB create "DEVELOPMENT" 15.0 -s

This creates an instance of LocalDB named DEVELOPMENT by using SQL Server binaries and starting the instance.

Now check the instance state by running the command:

SqllocalDB.exe i MSSQLLocalDB

Currently, the instance is running you can see it in the State field. Instead, if you have got the state as Stopped , then start the instance by running the following command in the terminal:

SqllocalDB.exe s MSSQLLocalDB

Step 2. Create the LocalDB connection

First Open data source properties, you can open these properties by using one of the following options:

• Navigate to File | Data Sources.

• Press Ctrl+Alt+Shift+S.

• In the Database Explorer (View | Tool Windows | Database Explorer), click the Data Source Properties icon.

Second From the Driver list, select Microsoft SQL Server LocalDB, and at the bottom of the data source settings area, click the Download missing driver files link.

Meanwhile, as you click the link DataGrip downloads drivers that will interact with the database. You can also specify your drivers for the data source if you do not want to download the provided drivers.

• Now from the Instance list, select the instance to connect (for example: DEVELOPMENT).

• From the Authentication list, select the authentication type:

• User & Password – Login credentials
• Windows credentials: connect by using your Windows domain credentials. Requires to run DataGrip on Windows in the same domain as the Microsoft SQL Server database.

• Kerberos: connecting by using Kerberos authentication. This specifically requires a Kerberos server and authentication with kinit.

• No authentication: connect without authentication.

Finally, to ensure that the connection has the data source successfully created, click the Test Connection link. You will have Succeeded pop-up message, then click apply.

Conclusion

Microsoft Localdb SQLserver management studio installer is available in the installation media for all editions except for Express Core. You can also further install LocalDB through the Visual Studio Installer, as part of the Data Storage and Processing workload.