Skip to content

Instantly share code, notes, and snippets.

@lenards
Last active July 24, 2020 20:35
Show Gist options
  • Save lenards/6b6e238f8cd85ce1b9aa590c398cd63e to your computer and use it in GitHub Desktop.
Save lenards/6b6e238f8cd85ce1b9aa590c398cd63e to your computer and use it in GitHub Desktop.
Making Azure Storage Emulator Happy (Maybe) ... I hate to add the uncertainty there, but I wrote this up before I had full confirmation of the system working locally and I ended up ripping out all of `sql-server-2017` and getting `sql-server-2019` in place ...

Installing (localdb) on Windows 10

Why? "Sql Local", aka LocalDB is used by the Azure Storage Emulator to mimic the storage interfaces for Azure.

For some reason, installing Sql Server 2017 Developer edition does not offer in "Feature Selection" the ability to include (or add) SqlLocalDB (e.g. LocalDB, or (localdb)).

What to do?

Get the Installer & Install

You can get the msi for the SqlLocalDB.exe here:

https://download.microsoft.com/download/E/F/2/EF23C21D-7860-4F05-88CE-39AA114B014B/SqlLocalDB.msi

Then, you can run that installer and you'll have SqlLocalDB.exe available on your shell path.

Create Instance

You can check on available instances using info. With a new install, we won't have any yet.

If you omit the name, it will create the default (see Appendix B - Output for an example).

But, hey - let's be explicit

> SqlLocalDB.exe create "MSSQLLocalDB"
LocalDB instance "MSSQLLocalDB" created with version 14.0.1000.169.

Just to confirm if you're being super skeptical:

> SqlLocalDB.exe info
MSSQLLocalDB

Start Instance

SqlLocalDB.exe start "MSSQLLocalDB"
LocalDB instance "MSSQLLocalDB" started.

Previous LocalDB Instances

If you see an error like this, you might have data files hanging around.

Windows Azure Storage Emulator 5.10.0.0 command line tool
Found SQL Instance (localdb)\MSSQLLocalDB.
Creating database AzureStorageEmulatorDb510 on SQL instance '(localdb)\MSSQLLocalDB'.
Cannot create database 'AzureStorageEmulatorDb510' : The database 'AzureStorageEmulatorDb510' does not exist. Supply a valid database name. To see available databases, use sys.databases..
One or more initialization actions have failed. Resolve these errors before attempting to run the storage emulator again.
Error: Cannot create database 'AzureStorageEmulatorDb510' : The database 'AzureStorageEmulatorDb510' does not exist. Supply a valid database name. To see available databases, use sys.databases..

You might have a old data files hanging around. Depending on the previous installed configuration, you'll potentiall find them under C:\Users\{username}.

Something like:

  • AzureStorageEmulatorDb510.mdf
  • AzureStorageEmulatorDb510_log.mdf

This can block the init command from the Azure Storage Emulator. So you can nuke them.

Forcing the Matter

To avoid playing around, use Sql Server Management Studio to connect to (localdb)\MSSQLLocalDB.

Then, right click on the "Databases" folder icon in the Object Explorer, and select "New Database"

You'll find to create the database based on the version of Azure Storage Emulator you have installed. The error messages will tell you what name the tool is trying to create.

For our example, it was AzureStorageEmulatorDb510. Use this name as the input to for Database name: field.

Start Azure Storage Emulator

 . "C:\Program Files (x86)\Microsoft SDKs\Azure\Storage Emulator\AzureStorageEmulator.exe" start
Windows Azure Storage Emulator 5.10.0.0 command line tool
The storage emulator was successfully started.

Move On

Now, you can move on with your developer life and pretend that Windows is a viable platform for building software.

Appendices

Appendix A - AzureStorageEmulator.exe

Failure Output for init Command

. "C:\Program Files (x86)\Microsoft SDKs\Azure\Storage Emulator\AzureStorageEmulator.exe" init
Windows Azure Storage Emulator 5.10.0.0 command line tool
Empty SQL Instance. Autodetecting SQL Instance to use.
Looking for a LocalDB Installation.
Probing SQL Instance: '(localdb)\MSSQLLocalDB'.
Caught exception while probing for SQL endpoint. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 50 - Local Database Runtime error occurred. Cannot create an automatic instance. See the Windows Application event log for error details.
)
Number of SqlErrors Reported: 1
SqlError: System.Data.SqlClient.SqlError: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 50 - Local Database Runtime error occurred. Cannot create an automatic instance. See the Windows Application event log for error details.
)
Could not find a LocalDB Installation.
Probing SQL Instance: 'localhost\SQLExpress'.
Caught exception while probing for SQL endpoint. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
Number of SqlErrors Reported: 1
SqlError: System.Data.SqlClient.SqlError: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
No available SQL Instance was found.
One or more initialization actions have failed. Resolve these errors before attempting to run the storage emulator again.
Error: No available SQL Instance was found.

Appendix B - SqlLocalDB Information

Help Output from SqlLocalDB.exe

 SqlLocalDB.exe
Microsoft (R) SQL Server Express LocalDB Command Line Tool
Version 14.0.1000.169
Copyright (c) Microsoft Corporation.  All rights reserved.

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".
@lenards
Copy link
Author

lenards commented Jul 24, 2020

Helpful stuff here ... https://docs.microsoft.com/en-us/azure/storage/common/storage-use-emulator#initialize-the-storage-emulator-to-use-a-different-sql-database

Like if you want to avoid SqlLocalDB.exe altogether, you should be able to init to the full instance of SQL Server using AzureStorageEmulator.exe init /server . (as mentioned in the link above at the time I'm writing this ... the web is really an example of impermanence).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment