|
DAC - Dedicated Admin Connection in SQL Server
2005
In mids of loads of new features introduced in SQL Server 2005, this is one
feature that interests the DBA's the most. If you work with large number of
servers and large number of databases you might want to have immense control
the way you monitor and maintain SQL Server instances. In the past I've seen
times when people say the server has frozen and I am not able to connect. To
eliminate such problems "Welcome SQL Server 2005 - DAC"
What
is DAC ?
Dedicated Admin Connection allows
user to connect to SQL server when normal connection attempts fail, for
example, when server is hanging, out of memory or other bad states that it's
not responding to connection requests. DAC is achieved by pre-allocating
dedicated resources during server startup, including memory and scheduler etc.
Only available in SQL Server 2005.
Ways
to use DAC ?
There
are two modes of usage. You can use the sqlcmd which is the command promt
version and the osql version in SQL Server 2005. We have a new option -A
that enables the connection to be as Admin connection.
More
often than not, I am sure you would like to have this with the Enterprise
Manager way i.e. the SQL Server Management interface for SQL Server 2005. To
enable the admin connection from SSMS you need to use the ADMIN:
before your server's name. Hence the connection property would look like:
I am sure this is a nifty
feature as DBA's we would like to capitalize on. This proves at any point in
time the DBA has a window of control ALWAYS available to him. I think we need
to use it, test it and experience the difference that this feature brings to
the table.
|