With every fresh SQL Server installation, there are several default configuration changes you probably consider. For instance, you should probably set a maximum value on Max Server memory to avoid memory pressure on vital OS resources. You also should probably raise the Cost Threshold of Parallelism above 5. And you should probably enable Optimize for Ad Hoc Workloads as well.
But then again, you may have reasons why you don’t touch those defaults. Those are performance related, and maybe it’s your policy to simply leave the configurations as they are until you determine you need to change them. Different strokes, different folks, and all that.
That’s fair, but I would like to suggest that you absolutely must change one configuration setting, and it has nothing to do with improving performance. To prepare for a situation where SQL Server is online but not allowing connections, you want to enable the remote access to the Dedicated Administrator Connection (DAC).
What is this DAC?
(I know “DAC” is also used as an acronym for Data-Tier Applications. This is an unfortunate coincidence.)
If you’ve never heard of SQL Server’s DAC, know that it’s basically a special connection with reserved resource so you can make to your instance when things start to look really bad. By bad, I mean you are unable to make a connection through typical means because of performance problems.
Now, by default you can connect using the DAC connection LOCALLY. This is good news if you have access to the physical hardware, or you can use a remote desktop connection. However, many organizations now place security restrictions that prevent users from doing such things, so connections must be made REMOTELY.
Short story even shorter: If your connections are all remote and your responsibilities include database administration, then you want this configuration enabled.
How do you enable remote DAC connections?
Enabling remote access to the DAC is fairly simple. Just change the configuration and RECONFIGURE.
EXEC sp_configure 'Remote Admin Connections', 1
GO
RECONFIGURE
GO
However, you may have to have a port in a firewall opened as well. By default SQL Server will listen for the DAC using Port 1434, but if you are using non-standard ports then you probably want to read more about that.
Once you’ve got that all squared away, you’re now able to use the DAC. But…you shouldn’t. I mean, it’s OK to test it once, but quickly disconnect, but the DAC is like the Highlander: there can be only one (connection, that is). This is an “in case of emergency break glass” tool, not something you want to use outside of a test or an emergency.
That’s just one of many considerations here.
- Only members of the “sysadmin” role can use the DAC
- If the user’s default database is offline, the DAC will fail to connect
- There’s no parallelism for queries using the DAC
- Other resources allocated to the DAC minimal, so don’t try running something like DBCC CHECKDB
When you do have to connect, you have a couple of options.
How do you connect using remote DAC connections?
1. Using SQL Server Management Studio.
Connecting through SSMS is done using an ADMIN: when entering the “Server name:”
However, if you’re just firing up SSMS and attempting to connect, you may get an error that you can’t connect. That’s because SSMS tries to connect to both a query window and Object Browser, which as any mathematician will tell you is two connections. And as previously stated, the DAC can only support one connection.
The solution here is to connect to some other instance when first starting SSMS, then in a query window connect to using the DAC option.
2. Using SQLCMD.
You can also connect using the SQLCMD utility, which is a good option in case using SSMS is not available The DAC is option is enacted using the -A parameter.
So there you go! The DAC is a handy tool for helping resolve seemingly unresolvable issues, so make sure it is enabled and working as expected on all the SQL Server instances that matter to you.