I use a Mac (Intel, you can only run SQL Server on Edge on Apple chips) as my primary workstation. While I have a Windows VM locally, and several Azure VMs runnining Windows, I can do most of my demo, testing, and development SQL Server work locally using Azure Data Studio, sqlcmd, and SQL Server on Docker. Docker allows me to quickly run any version or edition of SQL Server from 2017-2022 natively on my Mac, and has been nearly 100% compatible with anything I’ve needed Windows for core database functionality. And then this #sqlhelp query came up this morning.
#sqlhelp Came across a message about a ForkID when restoring a backup. Can’t find a decent article that explains and demos such things. Any pointers to a good article on this?
— BrainDonor (@The_BrainDonor) September 26, 2022
The one reference I found to “ForkID” on the internet was in this DBATools issue, given that and the fact that the tweet also referenced backup and restore, my first thought was to query sys.columns in MSDB. So, I did and there were a couple of tables:
Because as shown in the image above, the table in question is a system_table, in order to query it directly, you need to use the dedicated administrator connection (DAC) in SQL Server. The DAC is a piece of SQL Server that dedicates a CPU scheduler, and some memory for a single admin session. This isn’t designed for ordinary use–you should only use it when your server is hosed, and you are trying to kill a process, or when you need to query a system table to answer a twitter post. The DAC is on by default, with a caveat–it can only be accessed locally on the server by default. This would be connected to a server console or RDP session on Windows, or in the case of a container, by shelling into the container itself. However, Microsoft gives you the ability to turn it on for remote access (and you should, DCAC recommends this as a best practice), by using the following T-SQL.
exec sp_configure 'remote admin connections', 1
GO
RECONFIGURE
GOThis change does not require a restart. However, when I tried this on my Mac, I got the following error:
Basically–that’s a network error. In my container definition, I had only defined port 1433 as being open, and the DAC uses port 1434. If I were using Kubernetes for this container, I could open another port on a running container, however in Docker, I can only do this by killing and redeploying the container.
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=P@ssw0rd!' -e'MSSQL_PID=Developer' -p 1433:1433 -p 1434:1434 -v /Users/joey/mssql:/mssql -d mcr.microsoft.com/mssql/server:2022-latest
I simply expose port 1434 (by the second -p switch in the deployment script) and now I can connect using the DAC. Sadly, there was nothing interesting in sysbrickfiles.
