LocalDb Dev on Windows11 ARM

Background

In the past year I'd started using a macbook for development, though I still have a need for doing some development that would keep me on windows for a while. Some of that is .NET Framework application development, and some of that is SQL Server related development used in conjunction with a LocalDb instance.

I'd found that in many cases the SQL Server code I was developing against would work just fine against a macOS docker container using a mssql server 2022 image. I could deploy to this instance via azure data tools or the vs code database projects extension. But, some advanced features were not supported there, such as CLR and Service Broker. My applications and or application testing depended on these features in some way. This took me to an ARM version of Windows 11 running in parallels, where I had a new set of problems to solve.

1. SQL Server localdb deployment would not succeed
2. Application development could not access localdb instances

For a while an answer to problem #1 for me was to use VS22 LTSC 17.2, which ran using x64 emulation. This was terribly slow obviously and frequently would hang but could successfully deploy to the localdb instance as if I was running on x86 windows. This did require an MSDN license however, which I was fortunately able to accommodate.

The answer on #2 was to utilize the named pipes connection with sql client for the localdb instance. For some reason this would work, but resolving the (localdb)\ProjectModels host would not. For this a few command line commands lent me a hand:

This command would retrieve information on the ProjectModels localdb instance for me, including the state and the instance pipe name that I needed:
SqlLocalDB i ProjectModels

This would start the instance, which was unfortunately needed quite a bit since it would occasionally stop running:
SqlLocalDB start ProjectModels

Interestingly SSMS and Azure Data Studio would be able to connect to (localdb)\Project models just fine.

Recently the preview versions of studio have been making some improvements to SSDT and other crucial areas with ARM but unfortunately localdb development still has not come around.

Solution

So here is the idea:

1. Use the named pipe connection since it works
2. Set up a SQL Server Alias to allow clients to use the alias host name rather than the named pipes in the connection strings
3. Set up a scheduled task to run a script once a minute (good enough) to ensure the alias is kept up to date with the latest named pipes value for the localdb instance, since it changes every time it is restarted.

I will list instructions assuming you're going after the ProjectModels localdb instance, you can update it to another instance name if needed.

Requirements

• The latest VS 22 preview version (I'm not sure if all of these options are available previously)
• Admin rights on the win11 ARM machine
• Powershell
• sqllocaldb (if not already installed - choco install sqllocaldb)

Start by opening your sql project in visual studio and testing that you can deploy using the named pipes address. To do so:

1. Get the named pipes address by running SqlLocalDb i ProjectModels in the terminal and grabbing the "Instance pipe name" value. It will look like this: np:\\.\pipe\LOCALDB#C514698C\tsql\query
2. Right click a database project in the solution, go to properties, Debug, Target Connection String, edit. Place the pipe name value in the "Server Name" field, use windows authentication, set the database name to the database name for the one you're editing, and set Encrypt to false.
3. Click test connection, it should say that it has succeeded.

Next, run the following powershell script as administrator to create an alias by the name of "LocalDBAlias" for the named pipe value for the instance as it is right now.

[CmdletBinding()]
$ErrorActionPreference = "Stop"


$Name = "LocalDBAlias"
$SQLServerName = "ProjectModels"

sqllocaldb start $SQLServerName

$ThePipe = ((sqllocaldb info $SQLServerName | Select-String -Pattern "np") -split ":")[2]

# create keys if they don't exist, which they do not for me at least initially on win11 ARM
if (-not (Test-Path "HKLM:SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo")) {
    New-Item HKLM:SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo
    New-ItemProperty HKLM:SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo –name $Name -propertytype String -value "DBMSSOCN,$SQLServerName,1433" # we'll update this with the np down below
}
if (-not (Test-Path "HKLM:SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo")) {
    New-Item HKLM:SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo
    New-ItemProperty HKLM:SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo –name $Name -propertytype String -value "DBMSSOCN,$SQLServerName,1433" # we'll update this with the np down below
}


$hive = "localmachine"

$key = "SOFTWARE\\Microsoft\\MSSQLServer\\Client\\ConnectTo"
$key32 = "SOFTWARE\\WOW6432Node\\Microsoft\\MSSQLServer\\Client\\ConnectTo"

$reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey([Microsoft.Win32.RegistryHive]$hive, $env:COMPUTERNAME)


$subkey = $reg.OpenSubKey($key, $true)
$subkey32 = $reg.OpenSubKey($key32, $true)


$res = $subkey.GetValue($Name)
$res32 = $subkey32.GetValue($Name)

$CurrentPipe = ($res -split ",")[1]


if ($CurrentPipe -eq $ThePipe)
{
    Write-Output "The $Name alias' named pipes match the current instance of mssqllocaldb."
}
else 
{
    $subkey.SetValue($Name,"DBNMPNTW,$ThePipe")
    $subkey32.SetValue($Name,"DBNMPNTW,$ThePipe")
    Write-Output "The $Name alias for $SQLServerName has been set for $ThePipe."
}


$reg.Close()

If you want to test this without running the script, you can use cliconfg.exe to add the alias via that GUI:

Repeat the above three steps in visual studio for a database, but use "LocalDBAlias" for the server name value. It should succeed. Close the properties, set the database project as the startup project and hit F5 to build and deploy it. Watch the output window, make sure that the deployment succeeds. Use SSMS/AzureDataStudio/VS SQL Server Object Explorer to validate that it did indeed deploy to the target.

Great, now we know that we can use our alias. The only problem is that when your localdb instance stops and starts again, the named pipe value has changed and we need to run the script again to keep the alias up to date. For this, I've created a scheduled task, using the following steps:

1. Save the above powershell script in C:\TaskScripts\ named "create_win11arm_localdb_alias.ps1"
2. Open "Task Scheduler"
3. Click on Task Scheduler Library in the left pane
4. Click Create Task on the right pane. Note: For anything not listed, the defaults were used for me.
5. General tab: a. Name: "Refresh LocalDbAlias" b. Security Options: run this as someone with admin permissions, the script will edit some registry options. Run whether user is logged on or not, do not store password. Run with highest privileges.
6. Triggers: a. Begin the task: on a schedule b. Daily c. Start today at 12:00 AM d. Recur every 1 day e. Advanced settings: repeat task every 5 minutes for a duration of 1 day. Stop task if it runs longer than 1 minute.
7. Actions: a. Action: Start a program b. Program/Script: powershell c. Add arguments: -ExecutionPolicy Bypass -File "C:\TaskScripts\create_win11arm_localdb_alias.ps1" -ErrorAction Stop
8. Settings: a. Allow task to be run on demand b. Run task as soon as possible after a scheduled start is missed
9. Hit ok to create the task

Run the task manually by right clicking on it and clicking "Run". This should succeed. To test if it works, SqlLocalDB stop ProjectModels in the terminal, followed by SqlLocalDB start ProjectModels then SqlLocalDB i ProjectModels. Note the named pipes value. Open cliconfg.exe, go to aliases and validate the value for the alias is the current value.

You'll need to update each database with the aliased host. You'll also need to use this host in your connection strings for any applications in local development that you use localdb for, if applicable.

Hopefully that helps, let me know what you think.