Some time back there were some connectivity related questions thrown at me:

Q1. We know about the Dedicated Admin Connection (DAC) and how it is useful for troubleshooting service hang issues. We also know that DAC should only be used when necessary. Can we find out if someone is misusing the DAC and staying connected all the time?

A. You can use the following query from a normal query window to check:

select * from sys.dm_exec_sessions

where endpoint_id IN

(

        select endpoint_id from sys.tcp_endpoints where is_admin_endpoint = 1

)

Do note that this assumes that the service is working normally and that you can connect normally 🙂

Q2. “I have disabled the named pipes protocol on server but I still find the server ‘listening’ on named pipes. See errorlog snippet below for what I mean”:

2007-05-23 11:36:56.69 Server      Server local connection provider is ready to accept connection on [ \.pipeSQLLocalINSTANCE1 ].

2007-05-23 11:36:56.69 Server      Server local connection provider is ready to accept connection on [ \.pipeMSSQL$INSTANCE1sqlquery ].

The “local connection provider” refers to the shared memory protocol. If named pipes is genuinely enabled, you will receive an entry similar to: 

2006-05-09 07:37:12.57 Server Server named pipe provider is ready to accept connection on [ \.pipeMSSQL$INSTANCE1sqlquery ].

So, do not worry about it – named pipes is not enabled 🙂

Q3. “I have configured a fixed TCP port for SQL to listen to but I still find entries in the errorlog which seem to indicate dynamic ports being used:”

2007-05-23 12:27:42.84 Server      Server is listening on [ 127.0.0.1 <ipv4> 1115].

To disable dynamic ports, use a blank value (remove the 0) from the Dynamic Port entry. However, you would still receive such an entry for DAC like this:

2007-06-15 17:03:16.63 Server      Server is listening on [ 127.0.0.1 <ipv4> 1509].

2007-06-15 17:03:16.63 Server      Dedicated admin connection support was established for listening locally on port 1509.

Hope these answers are useful!

Previously in SQL Server 2000, the STOPAT clause was provided for RESTORE LOG wherein you can “halt” the recovery process at a particular point in time. This feature is very useful for recovering from accidental user errors and such.

Now in SQL Server 2005, there is a STOPAT clause provided in the RESTORE DATABASE command also. The first time I saw this option, I was confused and wondered how we can provide a point-in-time recovery with a full database backup – because the full database backup is essentially an image of the database and does not contain the entire log backup (only contains the portion which changed during the backup command itself.) But I never thought about it much after that.

So when a customer asked me how this feature works, I dug in a little bit and here’s what I found:

• Without log backups, there is simply no way to achieve a true point-in-time restore


• What we do when we specify STOPAT in RESTORE DATABASE is to test if the full database backup is already ahead of the point in time you want to stop at. If it is, then the command errors out.


• Why it errors out is to provide you a clear signal that even if you restore this full backup and then restore subsequent transaction log backups on top (specifying a STOPAT) then those transaction log backups would fail with errors since the database is already rolled forward to a point in time ahead of the STOPAT


• In the above case, you would have wasted a lot of time and effort first restoring the database and then the transaction log backup before finding out that you should have started with a earlier full backup.

So, STOPAT in RESTORE DATABASE can be a little disappointing if you are not aware of the real purpose of this clause. Please review http://msdn2.microsoft.com/en-us/library/ms191468(SQL.90).aspx which explains this in detail.

One of my customers recently asked me how we can enforce the CHECK_POLICY option for all logins created on the SQL 2005 instance. Since by default this is enforced on a per-login basis, we cannot by default have it at a server level. But by using DDL triggers in SQL Server 2005, here is one way of achieving it:

—- BEGIN DDL Trigger Code —  

CREATE TRIGGER trgNewLogin on all server
for create_login
as
 declare @loginbeingcreated sysname
 select @loginbeingcreated = eventdata().value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘sysname’)

 If EXISTS
 (
  select 1 from sys.sql_logins
  where name = @loginbeingcreated
  and is_policy_checked = 0
 )
 BEGIN
  Print ‘Please ensure password policy is being checked by using CHECK_POLICY’
  ROLLBACK
 END

— END Trigger Code —