SQL Server Dedicated Admin Connection (DAC)

The Dedicated Admin Connection (or DAC for short - not to be confused with DAC packages) is exactly that - a dedicated connection that's available to SQL Server sysadmins, for use when a SQL Server might have become unresponsive. This backdoor for the sysadmins has reserved resources at all times, and allows a means for connecting to SQL Server when other ways of connecting may not be working due to performance (or other) issues.

By default, connection to SQL Server via the DAC is only available locally from the server - i.e. you'd need to remote onto the server then use SQLCMD or SSMS to use the DAC. If the host is unresponsive due to performance issues, this could prove problematic. The solution is to enable remote admin connections - this allows connecting via the DAC remotely, and can be switched on by using the following SQL:

EXEC sp_configure 'remote admin connections', 1;

GO

RECONFIGURE

GO

Connecting to the DAC remotely via SSMS is then as simple as prefixing your server\instance name with "admin:"

Kendra Little has an excellent blog post and video explaining more, including how to tell who is using the DAC (if you're unable to use it), how to enable on clusters, and more:

https://www.brentozar.com/archive/2011/08/dedicated-admin-connection-why-want-when-need-how-tell-whos-using/

Troubleshooting Microsoft Distributed Transaction Coordinator (MSDTC / DTC)

Microsoft Distributed Transaction Coordinator (abbreviated to MSDTC, or DTC) is usually very quick and simple to set up. However, it isn't the most intuitive thing to troubleshoot if you run into issues. This page outlines various tools that can be used to assist in troubleshooting MSDTC, and how to use them.

What is MSDTC?

MSDTC is the software that allows "distributed transactions" to run across multiple servers. A distributed transaction is something that updates data on two or more servers. MSDTC ensures that updates on all servers succeeed, or if there is a problem with ANY of the updates, then all of the updates are rolled back.

For example, if you wanted to move data from Server A to Server B, you could do this in a distributed transaction. The data would be written to Server B, and deleted from Server A simeltaneously. If there was a problem with the write to Server B, or the deletion from Server A, the transaction would fail, rolling back both the write and the deletion, leaving both servers exactly as they were. In this example, this ensures there is no loss of data (if only the deletion of data from Server A succeeded), and no duplication of data (if only the write to Server B succeeded).

DTC uses Port 135, and the DCOM port range, which is 1024 - 65535. As this is such a large range of ports to open, the DCOM port range can be limited in the registry if required

How to Enable and Configure MSDTC

1. Navigate to Control Panel > Administrative Tools > Component Services
2. Within Component Services, expand Component Services > Computers > My Computer > Distributed Transaction Coordinator. Here you'll see Local DTC, or, if you're on a cluster, you'll also see Clustered DTC
3. Right click the Local DTC (or Clustered DTC if required), and click Properties
4. Navigate to the Security tab
5. Select the following options as a default:
   
   Network DTC Access
   Allow Inbound
   Allow Outbound
   No Authentication Required (set by default)
   Enable SNA LU 6.2 Transactions (set by default)
   Account NT Authority\NetworkService (set by default)
   
   Note, Enable XA Transactions may also be required for SQL Server

It really is that simple enabling DTC, and typically, this is all that needs to be done to allow two servers to run distributed transactions between them.

There is more information about each DTC setting here: https://technet.microsoft.com/en-us/library/cc753620(v=ws.10).aspx

Test DTC is Working Between Two SQL Servers

It is relatively simple to test whether or not DTC is working between two SQL servers.

1. On Server A, set up a linked server to Server B. On Server B, create a test table. Ensure the linked server account on Server A has permission to write to the test table on Server B
2. On Server A, run the following SQL, replacing the table name etc with a test table:

set xact_abort on

begin distributed transaction

insert into ServerB.DBName.dbo.testTable (ID) values (1)

commit transaction

If it has committed successfully, SQL Server Management Studio will return (1 row(s) affected). If it fails, there may be DTC connectivity issues. The following tools may prove helpful troubleshooting.

DTCPing

DTCPing is a tool that allows testing DTC connectivity between two servers.

How to Use DTCPing

DTCPing must be installed on both servers you wish to test connectivity issues between. By default, the DTCPing installation installs to the C:\Windows\Temp folder (or subfolder buried in the temp directory somewhere). It's usually helpful to move the files to C:\DTCPing for ease of use.

1. Once DTCPing has been installed on both servers, open the application on both servers
2. On SOURCE server, enter DESTINATION server NETBIOS name into the Remote Server Name field
3. On DESTINATION server, enter SOURCE server NETBIOS name into the Remote Server Name field
4. Click PING on SOURCE server
5. Click PING on DESTINATION server

If a successful test has happened, the output will read similar to the following:

++++++++++++++++++++++++++++++++++++++++++++++
DTCping 1.9 Report for SOURCE
++++++++++++++++++++++++++++++++++++++++++++++
RPC server is ready
++++++++++++Validating Remote Computer Name++++++++++++
11-21, 04:31:01.455–>Start DTC connection test
Name Resolution:
DESTINATION–>65.52.22.254–>DESTINATION.contoso.com11-21, 04:31:01.470–>Start RPC test (SOURCE–>DESTINATION)
RPC test is successful
Partner’s CID:084B708C-F0C5-4E65-95F2-8E2DEF73FFF3
++++++++++++RPC test completed+++++++++++++++
++++++++++++Start DTC Binding Test +++++++++++++
Trying Bind to DESTINATION
11-21, 04:31:01.830–>SOURCE Initiating DTC Binding Test….
Test Guid:B5544E05-D64B-40AC-B283-71947914DED3
Received reverse bind call from DESTINATION
Network Name: SOURCE
Source Port: 1116
Hosting Machine:SOURCE
Binding success: SOURCE–>DESTINATION
++++++++++++DTC Binding Test END+++++++++++++

Should any errors be reported, the following link describes common errors and their resolutions: https://blogs.msdn.microsoft.com/puneetgupta/2008/11/12/troubleshooting-msdtc-issues-with-the-dtcping-tool/

DTCPing outputs logs to the folder the DTCPing.exe has been installed to, and also includes various .txt files explaining the DTCPing log format.

DTCTester

Next up is DTCTester, which only tests DTC transactions one way, from a source server to a destination server. It is command line only.

How to Use DTCTester

1. Install DTCTester on the server you want to test distributed transactions from. Install to C:\DTCTester or somewhere helpful!
2. Create an ODBC connection on the source server, to the destination server
3. Navigate to Administrative Tools > Data Sources (ODBC)
4. On the User DSN tab, click Add to add a new ODBC connection
5. Select SQL Server
6. Enter details for the destination SQL Server you wish to test DTC for
7. Run cmd.exe as an administrator
8. Navigate to the folder where you installed dtctester.exe (type cd c:\DTCTester)
9. Next, run dtctester <ODBC Name> <Username> <Password> where
   <ODBC Name> is the name of the ODBC connection you set up in step 1
   <Username> is the name of a sysadmin user
   <Password> is the corresponding sysadmin password

DTCTester will then attempt to create, and write to, a temporary table on the destination server using a distributed transaction. A successful output looks as follows:

Command Line: dtctester test sa
Executed: dtctester
DSN: test
User Name: sa
Password is assumed to be NULL.
Connecting to the database
tablename= #dtc7488
Creating Temp Table for Testing: #dtc7488
Warning: No Columns in Result Set From Executing: 'create table #dtc7488 (ival int)'
Initializing DTC
Beginning DTC Transaction
Enlisting Connection in Transaction
Executing SQL Statement in DTC Transaction
Inserting into Temp...insert into #dtc7488 values (1)
Warning: No Columns in Result Set From Executing: 'insert into #dtc7488 values (1) '
Verifying Insert into Temp...select * from #dtc7488 (should be 1): 1
Press enter to commit transaction.

Committing DTC Transaction
Releasing DTC Interface Pointers
Successfully Released pTransaction Pointer.
Disconnecting from Database and Cleaning up Handles

More information on DTCTester here, including typical errors: https://support.microsoft.com/en-gb/kb/293799

Tracing DTC Output

It is possible to stop and start logging of DTC output to help understand what is actually happening when enlisting distributed transactions.

Setting up DTC Tracing

DTC tracing can be configured from the Tracing tab of the DTC Properties (see How to Read DTC Trace Files below)

1. Navigate to Control Panel > Administrative Tools > Component Services
2. Within Component Services, expand Component Services > Computers > My Computer > Distributed Transaction Coordinator. Here you'll see Local DTC, or, if you're on a cluster, you'll also see Clustered DTC
3. Right click the Local DTC (or Clustered DTC if required), and click Properties
4. Navigate to the Logging tab to see the Location value where DTC trace data will be saved to (by default, it is C:\Windows\system32\Msdtc\Trace)
5. Navigate to the Tracing tab. Ensure Trace Output and Trace Transactions is selected. Also ensure types of transactions you wish to trace are selected
6. Click Stop Session, then New Session to begin a new DTC trace. You can now test your DTC connectivity. Once finished, click Stop Session to end tracing of DTC
7. Navigate to the folder selected in step 4 to find the trace files (see How to Read DTC Trace Files below)

How to Read DTC Trace Files

Frustratingly, DTC trace files are written in binary, and are indeciperable to the average human eyeball. Microsoft do not make it easy to read these files. The software required to open the files is not installed with Windows. Again, this is command line only.

1. Copy tracefmt.exe to the directory that DTC trace files are output to (by default it is C:\Windows\system32\Msdtc\Trace). Note, you cannot paste this path into explorer to get to this folder! But you can navigate to it manually
2. Run cmd.exe and navigate to the trace output folder (by default it is C:\Windows\system32\Msdtc\Trace) by running the command cd C:\Windows\system32\Msdtc\Trace
3. Next, run the command msdtcvtr.bat -tracelog <tracefile name> which will output the trace file contents to trace.csv in the same folder

There is more info on reading trace files, and where to get tracefmt.exe here: http://stackoverflow.com/questions/1329583/where-do-i-get-tracefmt-exe-and-how-do-i-read-my-msdtc-traces

Creating Unique 10 Character Strings

A nice way to make a unique 10 character string is to generate a GUID, strip out the hypehens, and take only the left / right 10 characters as follows:

select right(replace(CONVERT(varchar(255), NEWID()), '-',''),10)

 This produces no duplicates over 1 million rows, when tested as follows:

create table #tmpID(ID varchar(10))

declare @i int

set @i = 1

while @i <1000000

begin

       insert into #tmpID

       select right(replace(CONVERT(varchar(255), NEWID()), '-',''),10)

       set @i = @i+1

end

select ID, count(*) [No of IDs]

from #tmpID

group by ID

having count(*) >1

Profiler - Querying Trace Files From SSMS

If you have trace data that has been saved to a file, it is possible to query this data via SQL Server Management Studio, and run SQL commands against the file. Example SQL:

SELECT *

FROM ::fn_trace_gettable('c:\TraceFile.trc', default)

This makes it much easier to work with the trace file comapred to opening the file within SQL Server Profiler. It means it is also possible to sort, filter, apply aggregations (sums, counts, etc) against the data. Of course, you could always save the trace data to a table from within SQL Server Profiler, and then perform these sorts of SQL queries against that table, but using fn_trace_gettable, you don't need to.

SQL Server Start Up Switches

Some useful SQL Server start up switches:

Switch	Action
-d	Default startup option – specifies the fully qualified path for the master database file. If this is not provided, the current registry value will be used
-e	Default startup option – specifies the fully qualified path for the master database error log file. If this is not provided, the current registry value will be used
-l	Default startup option – specifies the fully qualified path for the master database log file If this is not provided, the current registry value will be used
-m	Start SQL Server in single user mode. Allows any member of the Local Administrator group to connect as sysadmin onto the SQL instance. CHECKPOINT process is not started
-m”client app name”	Specify which application can take the single user connection (e.g. –m”SQL Server Management Studio – Query”). Use when an unknown application keeps grabbing the single user connection when SQL Server starts
-f	Starts SQL Server in minimal configuration mode (this is also single user mode as well). Useful if a configuration setting is stopping SQL Server from starting
-n	Doesn’t use Windows Application Log to record events. Use –e in conjunction with this to ensure events are logged to SQL error log
-s	Start a named instance. Without this, a default instance is started
-Ttrace#	Start SQL with a specified trace flag (note the uppercase T)
-x	Disables several monitoring features
-c	Shortens the time taken to start SQL Server from command line

The following T-SQL code will tell you which start up switches have currently been applied to SQL Server:

SELECT

    DSR.registry_key,

    DSR.value_name,

    DSR.value_data

FROM sys.dm_server_registry AS DSR

WHERE

    DSR.registry_key LIKE N'%MSSQLServer\Parameters';

...and querying the sys.dm_server_registry dynamic view in it's entirety will give you details about the registry entries for SQL Server.

Resources:
Info on trace flags: http://www.sqlservercentral.com/articles/trace+flags/70131/

What's new in SQL Server 2014

Some information on the new features / enhancements  introduced in SQL Server 2014:

In memory OLTP engine:
Allows tables and stored procedures to move to memory to drastically reduce IO and improve performance many times over. Microsoft provide the Analysis, Migrate and Report (AMR) tool that identifies objects that would benefit from being moved into memory.

AlwaysOn Availability Groups:
SQL Server now supports up to 8 secondary replicas (as opposed to 4 in SQL 2012), and secondary replicas are available for read only workloads even when the primary replica is unavailable. Now also supports using Azure for asynchronous replicas for high availability / disaster recovery scenarios.

http://sqlmag.com/sql-server-2014/sql-server-2014-important-new-features

Permissions - Server & Database Roles

When assigning permissions, it is important to always grant the least privileged role - minimum permission to perform the function that is required.

Server Roles:
More info from MSDN

Fixed server-level role	Description
sysadmin	Members of this role can perform any action on the SQL Server instance. Initial membership of this role happens at installation
serveradmin	Members can perform instance wide administration tasks, including shutting down the instance
securityadmin	Members can manage instance level permissions. Permissions for this role are equivalent to sysadmin - as members of this role can elevate their own permissions to sysadmin
processadmin	Members can view and terminate processes on the database engine
setupadmin	Members can add / remove linked servers to the database instance, and execute some system stored procedures
bulkadmin	Members can run the BULK INSERT statement
diskadmin	Members can administer database file configuration
dbcreator	Ability to create databases (database will be owned by the creator)
public	Every login is automatically a member of the public role; you cannot change membership for public. You can change the permissions for public however. If a user has no permissions granted, they will revert to the permissions assigned to public

Database Roles:

More info from MSDN

Database-level role name	Description
db_owner	Has permissions to do anything within the database
db_securityadmin	Members can administer security of roles (note: can elevate their own permissions to db_owner)
db_accessadmin	Can administer who has access to database for Windows Logins, Windows Groups and SQL Server Logins
db_backupoperator	Members have ability to backup the database. Cannot restore the database - this is reserved for dbcreator / sysadmin fixed server roles
db_ddladmin	Can perform any Data Definition Language (DDL) operations (those that create / modify structure of database), e.g. CREATE, ALTER, DROP, TRUNCATE
db_datawriter	Allows users to add / delete / update data on all user tables
db_datareader	Allows users to read data from all user tables
db_denydatawriter	Members cannot add / delete / update any data in any user tables
db_denydatareader	Members cannot read data from any user tables

: