Dedicated
Administrator Connection (DAC)
SQL Server provides a special diagnostic connection for administrators when standard
connections to the server are not possible. This diagnostic connection allows
an administrator to access SQL Server to execute diagnostic queries and troubleshoot
problems even when SQL Server is not responding to standard connection
requests. The DAC is available through the sqlcmd utility and SQL Server
Management Studio.
To connect
to a server using the DAC
1. In SQL
Server Management Studio, with no other DACs open, on the toolbar, click Database
Engine Query.
2. In the Connect
to Database Engine dialog box, in the Server name box, type ADMIN:
followed by the name of the server instance. For example, to connect to a
server instance named HRSrvr\HRProd, type ADMIN: HRSrvr\HRProd.
3. Complete
the Authentication section, providing credentials for a member of the sysadmin
group, and then click Connect.
http://www.mybasicknowledge.com/2012/06/dedicated-administrator-connection-dac.html
http://www.mybasicknowledge.com/2012/06/dedicated-administrator-connection-dac.html
I/O Optimizations
Instant
File Initialization
For large
PeopleSoft installations, the newly introduced SQL Server feature, instant
file initialization, can be leveraged to avoid long waits when the data file
expands.
Data and
log files are first initialized by filling the files with zeros when you
perform one of the following operations:
• Create a
database.
• Add
files, log or data, to an existing database.
• Increase
the size of an existing file (including autogrow operations).
• Restore a
database or filegroup.
The
initialization of the data or log file with zeros usually leads to wait time
and blackouts, if a large expansion is required.
In SQL
Server, data files can be initialized instantaneously for fast execution
of the previously mentioned file operations. Instant file initialization
reclaims used disk space without filling that space with zeros. Instead, disk
content is overwritten as new data is written to the files. Log files cannot be
initialized instantaneously.
Instant
file initialization is available only if the SQL Server (MSSQLSERVER) service
account has been granted SE_MANAGE_VOLUME_NAME. Members of the Windows
Administrator group have this right and can grant it to other users by adding
them to the Perform Volume Maintenance Tasks security policy.
The
SE_MANAGE_VOLUME_NAME privilege is granted by default and no specific action is
required to use this feature.
Security
Considerations
Because the
deleted disk content is overwritten only as new data is written to the files,
the deleted content might be accessed by an unauthorized principal. While the
database file is attached to the instance of SQL Server, this information
disclosure threat is reduced by the discretionary access control list (DACL) on
the file. This DACL allows file access only to the SQL Server service account
and the local administrator. However, when the file is detached, it may be
accessed by a user or service that does not have SE_MANAGE_VOLUME_NAME. A
similar threat exists when the database is backed up. The deleted content can become
available to an unauthorized user or service if the backup file is not
protected with an appropriate DACL.
If the
potential for disclosing deleted content is a concern, you should do one or
both of the following:
• Always
make sure that any detached data files and backup files have restrictive DACLs.
• Disable
instant file initialization for the instance of SQL Server by revoking
SE_MANAGE_VOLUME_NAME from the SQL Server service account.
Note. Disabling instant file
initialization only affects files that are created or increased in size after
the user right is revoked.
For
PeopleSoft applications, instant file initialization is recommended from a
performance perspective. However, evaluate the performance gain against the
possible security risk. If the security policy does not allow for this possible
risk, do not use instant file initialization. You can disable it by revoking
SE_MANAGE_VOLUME_NAME from the SQL Server service account.
Long I/O
Requests
In SQL
Server, the buffer manager reports on any I/O request that has been
outstanding for at least 15 seconds. This helps the system administrator
distinguish between SQL Server problems and I/O subsystem problems. Error
message 833 is reported and appears in the SQL Server error log as follows:
SQL
Server has encountered %d occurrence(s) of I/O requests taking longer than %d
seconds
to complete on file [%ls] in database [%ls] (%d). The OS file handle is 0x%p.
The
offset of the latest long I/O is: %#016I64x.
A long I/O
may be either a read or a write; it is not currently indicated in the message.
Long I/O messages are warnings, not errors.
Note. Long I/O warning messages are
related to functional disk errors only. For performance tuning of I/O issues,
use the I/O related dynamic management views and System Monitor counters.
For
PeopleSoft applications, I/O error message 833 can be very useful from a
reactive I/O maintenance and monitoring perspective. It is recommended that you
monitor the SQL Server error log for these messages. However, for I/O
performance tuning, it is recommended that you use the I/O-related dynamic
management views and System Monitor counters.
