When performing maintenance to a cluster, as in adding an SQL Server virtual to a node or removing it from a node, setup may fail for a number of reasons. And once something went wrong, things keep getting wrong, even if you verified the procedure and you corrected the cause of the error.

Well, the procedure didn't tell you everything. First of all, you should only be logged in to the node where you are running the setup. Logoff from all other nodes. The reason is that SQL Server setup will create a Windows Scheduled task on the other nodes of the cluster for you, under your account. Having an interactive session (you being logged in) and logging in as a batch job (the setup task) with the same account, may lead to unexpected events (and thus setup failing). Obviously Task Scheduler should be running to ;-). To be honest, Microsoft does tell you about the scheduler and the logging off in a KB article (let's be nice and add some community content).

Where it does get mean in the KB article, is when setup keeps failing (and it most likely will); restart all remote nodes!!! The most likely reason for the setup failing this time, is a running setup.exe from a previous failed attempt. This is what may cause the access denied for the scheduled task (or check that your account is local admin on every node and has the login as a service permission). Here is how to avoid having to reboot;

• Exit the setup on the local node.
• Check the scheduled tasks on the remote nodes. Some are running: stop them, then delete them. Some have failed, delete them
• Next, check the processes on all nodes, there may be one or two setup.exe processes from SQL Setup active on the nodes, kill those processes.
• Don’t forget to log off from the remote nodes.

Character based data types, especially the var types varchar and nvarchar allow to declare a really big amount and just use a tiny bit and it doesn’t even hurt in SQL Server. However, it may in the application. Today I was working with a customer for the implementation of some third party software that would be doing analysis on the data. It would load the really over-dimensioned (n)varchar types in something like a (n)char.... you get the picture, huge amounts of wasted space. So from the databases side, a bit of restructuring was desirable. To see how much we can actually save, I created query to report on the character usage;

Be aware that varchar(max) and nvarchar(max) will give a max_length of –1 and that actual_max_length is –1 if no rows exist in the table or the column only contains NULLs (refer to the row_count and not_null_count to determine the situation).

In a clustered environment, it may not always be obvious what node SQL Server has been running on. Most logging is done based on the network name of the resource group, or the instance name of the virtual server. Still it is logged once in the Windows Application Event Log and the SQL Server Error Log each, though the “memory” of those sources is likely limited by configuration; size and overwrite settings of the application log and number of error logs (maximum 99) for the SQL Server Error log.

In the Windows Application Event Log, the EventID 17664 is of interest (verify the source is an SQL Server instance). Event Log filtering is relative straight forward and turns up an event like this.

Figure Event Properties; 1 = Instance name, 2 = Network name of the resource group, 3 = Computer name of the cluster node, 4 = Binary code containing network name\instance name

In case of the SQL Server Error Logs, the thing of interest is the message;

The NETBIOS name of the local node that is running the server is 'computername'. This is an informational message only; no user action is required.

Parsing the files might be the easiest from SQL Server with the xp_readerrorlog. Unfortunately error handling surrounding eXtended stored Procedures is limited. Using the script below, the last result set shows the node where a clustered SQL Server was started on.

SET NOCOUNT ON
DECLARE @iteration int
DECLARE @log_events table ( [LogDate] datetime , [ProcessInfo] nvarchar ( 20 ), [Text] nvarchar ( max ), [Iteration] int )
SELECT @iteration = 0 -- Set to current error log
WHILE @iteration < 100
    BEGIN
    BEGIN TRY
        -- EXECUTE the XP without INSERT to trip the error.
        -- XP error is handled poorly, but it is even worse
        -- when it occurs on the insert from executing an XP.
        EXEC xp_readerrorlog @p1 = @iteration , @p2 = 1 , @p3 = 'NETBIOS name' ;
        INSERT @log_events ( [LogDate] , [ProcessInfo] , [Text] )
            EXEC xp_readerrorlog @p1 = @iteration , @p2 = 1 , @p3 = 'NETBIOS name' ;
        UPDATE @log_events
            SET [Iteration] = @iteration
            WHERE [Iteration] IS NULL;
        SELECT @iteration = @iteration + 1 ;
    END TRY
    BEGIN CATCH
        SELECT @iteration = @iteration + 1 ;
    END CATCH
    END
SELECT [Iteration] , [LogDate]
        , CAST ( SUBSTRING (
            SUBSTRING ( [Text]
            , LEN ( 'The NETBIOS name of the local node that is running the server is ''' ) + 1
            , 16 -- Max length NETBIOS name is 16 char (15 actually).
        ), 1 , CHARINDEX ( '''' , SUBSTRING (
            [Text]
            , LEN ( 'The NETBIOS name of the local node that is running the server is ''' ) + 1
            , 16 -- Max length NETBIOS name is 16 char (15 actually).
        )
    ) - 1 ) AS varchar ( 16 )) AS 'NodeName'
FROM @log_events
WHERE [Text] LIKE 'The NETBIOS name of the local node that is running the server is %'

Service Pack 2 for SQL Server 2008 annoyed me on one of my machines (development system, SQL Server doesn’t autostart). It came via Windows Update started with offering the service pack without .NET Framework 4 installed… then even after .NET Framework was installed, it kept failing. So f..k Windows Update and download SQL2k8SP2 and install it.

A couple of days later I needed SQL Server on that machine, I did the net start mssqlserver trick and tried to connect using Management Studio directly after that. So management studio threw me the

Login failed for user 'domain\user'. Reason: Server is in script upgrade mode. Only administrator can connect at this time. (Microsoft SQL Server, Error: 18401)

The very “well” documented error about the fact that SQL Server is running some scripts and rather has no-one mesh around (unless you’re on a DAC). The strange thing is that SQL Server seemed to have stopped after; that, something that isn’t supposed to happen. I restarted the service and tried again… same result, so it’s Google-time; best hit: Weird SQL Server Error: Unable to Connect to Server. Reason: Server is in script upgrade mode. With that being the best answer, the behavior I witnessed isn’t supposed to happen and thus I turned to the SQL Error Logs to find some info;

2010-12-03 21:02:04.28 spid7s      Performing replication job security meta-data upgrades...
2010-12-03 21:02:07.34 spid7s      Error: 824, Severity: 24, State: 2.
2010-12-03 21:02:07.34 spid7s      SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:2064; actual 0:0). It occurred during a read of page (1:2064) in database ID 21 at offset 0x00000001020000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\some_database.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
2010-12-03 21:02:07.34 spid7s      Error: 912, Severity: 21, State: 2.
2010-12-03 21:02:07.34 spid7s      Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 824, state 2, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2010-12-03 21:02:07.35 spid7s      Error: 3417, Severity: 21, State: 3.
2010-12-03 21:02:07.35 spid7s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
2010-12-03 21:02:07.86 spid7s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
2010-12-03 21:02:07.99 Server      The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/host.domain.local ] for the SQL Server service.

I had been working on a corrupted database called some_database (name changed for obvious reasons) for a customer a few weeks ago, but hadn’t removed that database. After removing the offending database files, SQL Server wasn’t tripped by the exception while opening the database.

To me, this does smell a like a bug (though I would not recommend running corrupt databases on a production system), so I filed it on connect; Finishing service pack installation prohibited by corrupt database.

There is nothing fancy about this post… just a little frustration. I wanted to compress files after they were processed with SSIS 2005. And with compression, I mean NTFS-compression, not creating a .zip or .gz (because that is what I mostly found through the search engines).

My first path of research was to do it in a Script Task, but it turns out, invoking the compression attribute of files on NTFS requires C# and can’t be done with VB.NET. So it’s a no go in SSIS 2005.

During the search, somewhere the compact.exe program in Windows was suggested as an alternative. Very useful from an SSIS perspective, it means the Execute Process Task is required. To make it all happen, three things are needed;

• A Foreach Loop container
• A string variable (either in the scope of the Foreach Loop Container or in the Package-scope), named filename in this example.
• An Execute Process Task in the Foreach Loop container

Configure (Collection Page) the Foreach Loop Container as Foreach File enumerator, point to the folder where the files reside and retrieve the file names as Fully qualified. Depending your requirements, you may also specify an additional mask and include subfolders.

Next visit the Variable Mappings tab and point to the previously created variable listed as User::filename.

Next is the Execute Process Task. But first a little on compact.exe, to compress a file, compact.exe should be called with the /C flag and the name of the file to be compressed. Something like C:\WINOWS\System32\compact.exe /C "D:\ftp\archive\dump_001208.csv"

In terms of what the Execute Process Task expects, the Executable should be set to compact.exe. The arguments should be /C "D:\ftp\archive\dump_001208.csv" and since the filename is supplied by the variable, an expression should be used. On the Expressions tab, create an expression for Arguments. With the expression designer, create the following expression

"/C \"" + @[User::filename] + "\""

The important part to recognize is that expressions are largely built on C-style syntax (download the PragmaticWorks white paper  SSIS Accelerator Series: Working with SSIS Expressions to learn more on SSIS expressions). The arguments expression should return a string, hence the opening and closing double-quotes. The filename, which is supplied by the @[User::filename] variable (drag and drop the variable in the expression designer) should be enclosed within double-quotes too. For SSIS to recognize that these double-quotes are part of the string it should return, the double-quotes need to be escaped (using the backslash). To confirm the expression is correct, use the Evaluate Expression button. The evaluated value should return /C "" (slash + C + space + double-quote + double-quote), once the filename variable is filled, it will appear between the two double-quotes.

Enclosing the filename in double quotes prevents files with spaces in the name from causing errors. There could be other errors though, like a file being locked. With current configuration, the package would stop and throw an error on the first occurrence of this condition. To prevent the package from failing in that event, set the FailTaskIfReturnCodeIsNotSuccessValue property of the Execute Process Task to false.

Some time ago I was working with performance data collected by a customer to look for I/O-patterns and make recommendations for the I/O-subsystem for a new SQL Server 2008 machine that would replace a couple of SQL Server 2000 servers. Unfortunately I ran into issues with the values for Avg. Disk sec/Read and Avg. Disk sec/Write, which were ridiculous with average values around 100-million seconds per read or write. Those values occurred on two virtualized Windows 2000 Servers.

Today, I was working for another customer. Physical Windows Server 2003 boxes (both nodes in the same cluster). I imported the .blg files into a database and was looking at very high values (up to 100 million) for Avg. Disk sec/Read and Avg. Disk sec/Write. I consulted the staff-DBA, who referred me to one off the Windows administrators. So the Windows administrator logged in, opened performance monitor and… got reasonable values for those counters (hovering at about 0.006, I’d even say those values are good). To prove I wasn’t making a fool of myself, we walked over to my desk and I showed the results I had… same servers, same counters, very different values. Next we tried using the account of the staff-DBA, which showed the “normal” values.

Simply eliminating, the Win-admin said the only difference is the account, so he went and checked whether the account I used had full administrator permissions. It had. Next he turned his attention to the user profile for the account I used (which happened to be quite big). The user profile belonging to the account I used was deleted and I logged in again, this time to be shown the correct values. RESOLVED!!!

Unfortunately the collected data still was useless. I started the counter log again, let it run for about 10 minutes and examined it… sky high values!!! In disbelief I switched to current activity, to be shown normal values!!! Next I created a new counter log definition, let it run for a couple of minutes and this time I got normal values.

Bottom line, to resolve insanely high values for Avg. Disk sec/Read and Avg. Disk sec/Write (it might apply for other counters of the type PERF_AVERAGE_TIMER too), use a fresh user profile and a fresh counter log definition.

Thanks Ruud.

I am working on an MS Access database that needs to be integrated in a CRM system. Since the backend of the CRM is SQL Server (why else would I be involved), I migrated the Access database to SQL Server using the SQL Server Migration Assistant for Access v4.2 (SSMA for Access). Next I turned my attention to translating attributes between the systems and unfortunately, a free text notes field in the Access database is used for a lot of things that need to be translated to attributes. Some things I know how to translate, but some things have to be determined by the administrators for the CRM System and Access application. So I built a query in SQL on the tables formerly stored in the Access database and used Excel (with MS Query) to create a report for the administrators, so they can sort out how customer statuses should be translated.

Extremely simplified, the query looked like this;

SELECT tlbCustomer. CUST_CODE
  , tlbCustomer. PAY_REF
  ,COALESCE(UPPER(REPLACE(tblNotes.NOTE_TXT,'.','')),'')AS NOTE_TXT
FROM tlbCustomer LEFTOUTERJOIN tblNotes
      ON tlbCustomer.CUST_ID = tblNotes. CUST_ID
GROUPBY tlbCustomer. CUST_CODE
  , tlbCustomer. PAY_REF
  ,UPPER(REPLACE(tblNotes.NOTE_TXT,'.','' ))
ORDER BY COUNT (*) DESC

The query worked great on SQL, but did not return the text (NOTE_TXT) to Excel.

To cut the long story short, the original tblNotes.NOTE_TXT field in the Access database was of type MEMO. SSMA 2008 for Access converted this to nvarchar(max) and somehow MS Query (in Office 2007) does not return, nor throw an error on, large data types (varchar(max), nvarchar(max)). A quick test showed that MAX was not really needed;

SELECT MAX ( LEN (tblNotes.NOTE_TXT))FROM tblNotes

So modify the query to return NOTE_TXT as nvarchar(256) does the trick;

SELECT tlbCustomer. CUST_CODE
  , tlbCustomer. PAY_REF
  ,CAST (
      COALESCE ( UPPER ( REPLACE (tblNotes.NOTE_TXT,'.','')),'')
    AS nvarchar (256))AS NOTE_TXT
FROM tlbCustomer LEFTOUTERJOIN tblNotes
      ON tlbCustomer.CUST_ID = tblNotes. CUST_ID
GROUPBY tlbCustomer. CUST_CODE
  , tlbCustomer. PAY_REF
  ,UPPER(REPLACE(tblNotes.NOTE_TXT,'.','' ))
ORDER BY COUNT (*) DESC

Sometimes one is confronted with forgetting an option... It all started with a database that had a single data file of 1.8 GB and a transaction log more than 6 times the size of that. Usually that would be a database in full recovery model and no transaction log backups. This database was in simple recovery model though and according to the Disk Usage report, 99.99% of the transaction log was free. So a DBCC SHRINKFILE should solve the problem, however DBCC SHRINKFILE solved nothing, the log remained at about 11 GB.

A query on sys.databases learned that this particular database had ACTIVE_TRANSACTION for log_reuse_wait_desc. A peek in Activity Monitor then showed that there was an open transaction for the database; a DELETE task with a huge wait time and the BROKER_RECEIVE_WAITFOR wait type.

Then it occurred to me this is the database that used to have a deadlock problem. After implementing some procedure fixes, there haven’t been any deadlocks for over a half year and neither has the server (or service) been shutdown in between.

The stored procedure on the queue does a receive, but no timeout associated to it. Effectively this means that the receive will wait inside the transaction, until a messages comes into the queue. That hasn’t happened in the last six months. The resolution is simple, add the TIMEOUT option to the RECEIVE and the procedure will stop when there wasn’t any work to be done for the specified amount of time (in milliseconds). For the code, see the post “Deadlock Alerts Through Event Notification”, which has been updated with the TIMEOUT option.

After altering the procedure, kill the spid. Service Broker will restart, but now with the TIMEOUT option in the procedure and stop after the specified amount of time has elapsed without messages arriving in the queue.

When restoring a backup of a database, SQL Server recreates the files with the same sizes as listed in the backup, or at least tries to do so. The only option you have, is specifying the path for each file. Generally not a problem, but every now and then I encounter a database with a transaction log about 10 to 50 times the size of the data file(s) . . . yep, full recovery model and just daily full backups. I am the first to acknowledge transaction logs backups are key to prevent this situation, but the next best thing still is that recent valid database backup. . . and then you can’t restore it, because the 200 GB to write that big empty file called transaction log cannot be allocated.

Today was one of those days;

Msg 3257, Level 16, State 1, Line 1
There is insufficient free space on disk volume 'D:\' to create the database. The database requires 101197938688 additional free bytes, while only 19386400768 bytes are available.
Msg 3119, Level 16, State 4, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Surely I wouldn’t be the only one having problems with this and I found two others on connect;

• Option to resize datafile/transaction log during a backup or restore operation
• Restore option to restore minimum file sizes

I voted (as important) for both items, though the second one is already closed. Yet I was triggered by the comments from the MS-people to ask for a specific solution towards the transaction log;

RESTORE DATABASE ... WITH STUB_LOG

One of the habits of RESTORE DATABASE is that it wants to allocate the log size (and files) as found in the backup. Strange, as the size of those files is the result of past activity (not contained in the backup being restored). The only space needed in the transaction log, is that of the activity during the backup. Additional space requirements may exist for subsequent restores of differential backups and log backups (in norecovery or standby) or future activity (after recovery), but that isn’t an issue as the log files would grow again during these restores.

The only real issue I can think of is having a database with fixed size transaction log file(s), which means no auto grow. So for a WITH STUB_LOG option to work properly in these scenarios, it may need to imply that the log file(s) are always set to unrestricted auto grow (possibly only honoring the original increment).

It would at least prevent me from having to allocate 100GB for an empty transaction log on 20GB available.

If you're interested in expanding your skills in SQL Server, check out these events;

• Quest Virtual Training for SQL Server, an online and worldwide available event on Wednesday March 3^rd
• SQL Zaterdag, hosted at InfoSupport in Veenendaal, Netherlands at Saturday March 6^th

Happy learning.

Microsoft recently launched a Virtual Business Card site for Microsoft Certified Professionals. So if you ever passed one or more of those Microsoft exams in the NT 4.0 or later eras, check out the www.mcpvirtualbusinesscard.com site (Windows Live ID sign in to connect to you records in Microsoft's certification database).

I set my profile page up to show off the credentials..

Also had a look at including the transcript, but that would be a 13-page pdf, a bit of overkill if you ask me.

Yesterday I visited the SQL Server day 2009, organized by the Belgian SQL Server User Group SQLUG.BE, in Mechelen. Congratulations on the event guys!

After the keynote by Microsoft Belgium (I wish they had talked a little bit more about SQL Azure), I visited the session by Henk van der Valk on world record ETL... now Henk has control over some top notch iron, but that doesn't mean his tips don't apply to a modest setup. Henk also mentioned he recently joined the blogsphere at www.henkvandervalk.com.

Next I sat (sorry I have to say so) horrible sponsored session by Quest... and this has nothing to do with the FogLight product. On another occasion (an afternoon session by Quest Netherlands) I witnessed the possibilities of Foglight (for .NET, SQL Server and VMware) and I must say it's a good looking product. However we got 30 minutes of boring listing of challenges and day to day problems (as if we weren't aware of them already) and in the end got some screenshots, which were completely out of context. I would have be completely lost in the presentation if I hadn't been to the session earlier by Quest NL.

After that, I meant to sit the session "Reporting Services a DBA's tool? YES!!!", but since the agenda card was a little confusing (or better said, I didn't pay enough attention) I walked into the session by Nico Jacobs on "Handling XML in SQL Server". Funny, as there was nothing new for me in the session but still I really enjoyed it... most important because as a trainer you rarely get an opportunity to see one of your colleagues at work on a familiar subject. Thanks Nico, I really enjoyed it.

The other session I attended was on "Policy Based Management", again by Nico Jacobs. I hoped it would go deeper into the possibilities of implementing it for SQL Server 2005 and SQL Server 2000. Unfortunately that was not the case, so I'll have to dive into the Enterprise Policy Management Framework and PowerShell without a quick start. But again, it was a joy listening to Nico.

Final session and closing keynote was by Chris Webb on PowerPivot (a.k.a. Gemini). It wasn't my first glance at Gemini, but it definitely is the kind of quick start I was looking for. Sitting a session like that saves a day of looking for stuff.

All-in-all, a day well spent.

Creating linked servers between two instances of SQL Server may not always be as straight forward as hoped. To make it more complicated, interference may be caused by matters beyond the control of the database administrator; Active Directory. After creating a linked server, being able to successfully use it may be the real challenge, it may even work for one user and fail for another.

Service Account

To communicate successfully in a domain environment, the service account for your database engine is the first to check/configure. Possible choices for the service account are;

• Domain User (recommended)
• Local User
• LocalSystem
• LocalService
• NetworkService (recommended)

From those options, a local user or LocalService for the service account will eventually stop you. In both cases a SQL Server has the permission set of user on the machine where SQL Server operates, with no identity on the network. In case the SQL Server has to connect to any remote resource, it will do so as an anonymous user (null session). Unless you want to be absolutely sure that SQL Server should never do anything beyond anonymous on the network, you can go this route... but hey, you're reading about linked servers, so just drop the thought of using a local user or LocalService.

Valid options to enable SQL Server to operate on the network are LocalSystem, NetworkService and Domain User. All have the potential to work well, but LocalSystem is not recommended from a security perspective. LocalSystem has the permission set of an administrator on the system where SQL Server operates, anyone obtaining control over your SQL Server has full access to the underlying operating system (think about the damage that could be done when xp_cmdshell is enabled). From a network perspective, LocalSystem and NetworkService are identical; when the SQL Server operates over the network, it will do so under the credentials of the Windows machine. With a domain user as service account, SQL Server has user permissions locally (as is the case with NetworkService, LocalService and a local user), on the network SQL Server will present the credentials of the domain user.

So two good options remain; NetworkService and a domain user. Which one you chose mainly depends on the advantages of one over the other;

• NetworkService:
  • No account or password management in Active Directory
  • Service Principal Names are automatically generated
• Domain User
  • Multiple instances on different machines can have the same identity which is required for clustering.
  • Transparent security for Database Mirroring. 
  • Usually SQL Agent will be the same account, this makes things easier with Log Shipping and Replication.
  • Improved access to Service Master Key (in case you don't have a backup of the key itself). -->

Personally, I favor using a domain user.

Should you find that you need to change the Service Account; use the "SQL Server Configuration Manager"

Configuring and using a SQL Server linked server for Windows Authentication

There are two ways to go about the configuration; graphically and via stored procedure. The graphical approach brings up a dialog, where just a little configuration is needed.

On the General page, select the server type to be SQL Server (choosing SQL Server Native or OLEDB drivers will work too, but require extra configuration) and type the name of the target instance using the flat name or FQDN followed by a backslash and instance name in case of a named instance.

On the Security page, select the option to "Be made using the login's current security context", which effectively tells SQL Server to impersonate or delegate the login requesting access though the linked server.

On the Server Options page, verify that Data Access is set to True. To see the stored procedures involved, either use the Script button on top of the dialog instead of pressing ok or right-click a configured linked server and "Script Linked Server as >".

To avoid running into (later explained) problems when creating linked servers, create the linked server from a shell (interactive or remote desktop) on the Windows machine where the linked server is configured.

To use the linked server, issue the query

SELECT TOP 50 * FROM [HOST.NET.LOCAL\INSTANCE].AdventureWorks.Sales.SalesOrderDetail

Note that the linked server HOST.NET.LOCAL\INSTANCE is enclosed in braces, which is required when the server name is fully qualified and/or when it is a named instance. If you don't want to expose host and/or instance names, consider creating an alias in "SQL Server Configuration Manager" under client configuration and point the linked server to the alias.

Impersonation and delegation

During the configuration (Security page) impersonation and delegation were mentioned. Also to avoid problems while configuring a linked server, the best way to do so was on the server where the linked server was to be created. The reason to do so is to avoid delegation. Below are a summary of impersonation and delegation (and links to a more complete overview). Windows accounts will be impersonated if necessary, Windows accounts will not be delegated unless both account and delegating system are configured to do so.

Impersonation happens when a process connects to resources pretending to have the identity of it's caller. Like when a user Bobbie is connected to SQLINST1 and issues a query for a table via a linked server on SQLINST2. Depending on the configuration of the linked server, SQLINST1 will pretend to be Bobbie when connecting on Bobbies behalf to SQLINST2. For a more detailed overview of impersonation, check the Pluralsight-wiki on impersonation. Note that impersonation is limited to the  system where process making the call is active.

Delegation is much like impersonation, with the additional feature that delegation is not limited to the system of the process making the call. Because the process now has to forward the credentials to another system and prove to operate on behalf of someone else than the system itself, the Kerberos protocol is required (that or knowledge of the password of the delegated user). For a more detailed overview of delegation, check the Pluralsight-wiki on delegation.

From the perspective of Windows systems, Bobbie (DOMAIN\Bobbie) is logged in on a typical client system (Windows XP, Vista or 7) and uses an application that connects to SERVER1\SQLINST1. In SQLINST1 two linked servers are configured; SERVER1 (being the default instance on SERVER1) and SERVER2\SQLINST1. The context of the application is DOMAIN\Bobbie and thus an access token for DOMAIN\Bobbie is handed to SERVER1 (for logging in on the SQL Server service SERVER1\SQLINST1). When the application issues a query for an object on the default instance of SERVER1, one local process on SERVER1 has to impersonate DOMAIN\Bobbie to the other local process on SERVER1. This will succeed and can work both with NTLM and Kerberos, because SERVER1 trusts itself and the domain; DOMAIN\Bobbie was authenticated by the domain and is only used by SERVER1 locally.

Next the application calls for SERVER2 and now the challenge is on! Bobbie get's a

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

SERVER2 does not trust SERVER1 and therefor will not accept the claim of SERVER1 that it acts on Bobbies behalf, SERVER2 wants to see PROOF! One way of proof would be if SERVER1 had the password of Bobbie, indicating that Bobbie fully trusts SERVER1... but Lesson number 1; never give anyone your password. Lesson number 2; don't write it on a Post-It... Lesson number 3; don't trust computers that violate lessons 1 or 2. The other option is to turn to a mutually trusted third party: the domain, but now the configuration needs to be right for Kerberos.

For Kerberos to do it's trick, the user (Domain\Bobbie) and the machine delegating (SERVER1) the user have to be trusted for delegation. This is sufficient if the SQL Server services on SERVER1\SQLINST1 and SERVER2 run as NetworkService.

In case SERVER1\SQLINST1 is running under a domain user account, trust that user account for delegation, not the server.

Service Principal Name

Kerberos distinguishes three roles;

• Key Distribution Center (KDC), in Active Directory this role is held by the Domain Controllers.
• Client, these are the users.
• Server, these are the computers in your network.

With this basic division of roles, things work well in a domain, until a service account like that of the SQL Server database engine is a domain user. If so, that particular domain user should be made a server too. To mark an account as a service, a Service Principal Name must be registered. With NetworkService, this was done by the server (because servers can write SPN's on their own behalf), for any other account, by default only Domain Administrators can write an SPN. Use the SetSPN tool to mark an account as Service Principal Name, SetSPN can be downloaded, is part of the Windows Server 2003 Support Tools and is installed on Windows Server 2008 with the AD DS role.

To register an SPN for a service account, use the following syntax:

SetSPN -a MsSqlSvc/server1.domain.local:1433 domain\user

SetSPN -a MsSqlSvc/server1:1433 domain\user

Substitute the blue parts in the syntax for the server names (FQDN and NetBIOS), the port where SQL Server listens (this could be different from 1433, especially when registering an SPN for additional instances) and the domain user account used as SQL Server service account.

When running more SQL Server instances under the same domain account, it may be useful to check the approach listed in Step 3 of How to use Kerberos authentication in SQL Server, so the AD-people have to be called upon only once for the service account, not for every instance installation.

Reference Environment

As reference environment (may be a layout for a test-network), the following machines, users and services are used in a single domain configuration;

• Windows Server 2003 or 2008: DC1 (domain controller), SERVER1 (member), SERVER2 (member)
• Windows XP, Vista or 7: CLIENT (member)
• SQL Server 2005 or 2008: SERVER1 (default instance) tcp 1433 static, SERVER1\SQLINST1 tcp dynamic (named instance), SERVER2\SQLINST1 (named instance) tcp 1433 static

To make it work, user Domain\Bobbie has to be trusted for delegation in all four scenario's. Depending on the service accounts for SERVER1\SQLINST1 and SERVER2\SQLINST2, use the following settings

Things to consider

When working with Kerberos, tickets are granted for a longer period of time (typically 10 hours), so configuration changes usually take a longer time to apply. Use the KERBTRAY and/or KLIST resource kit utilities to view and purge tickets, in order to speed things up (avoids rebooting systems or logging off/on users). Alternatively, consider shortening the ticket lifetimes to the minimum; 10 minutes for services and 1 hour for users.

When configuring the linked servers, it was best to do so at the server where the linked server is configured from. Depending on your role you may be a domain admin, so should domain admins be delegated in order to make setting up linked servers easier? Rather not! Imagine someone installed malicious program or script on a system that is trusted for delegation and somehow a domain administrator executes this program or script. It could hurt any system or user in your network.

I knew this was coming and still I was taken by surprise; my son Bram was born last week (July 27th). An event like that turns your life upside down, forever. I'm not in the habit of putting personal stuff on the net, but this is worth the exception. So here they are, a proud father and his one day old son.

Sometimes SQL Server gets it wrong, UNIQUE constraints and NULL is one of the parts where it is wrong. People call it a bug and request for it to be fixed, Microsoft calls requests to fix it common and offers workarounds and overall current SQL Server behavior does not adhere to the standard (ISO-9075).

To see the bug reports (and vote); 126533, 299229, 311223 (closed), 387273. Basically they are each others duplicates and from those, the request 299229 has most attention and the best discussion.

Available workarounds;

• Filtered index (starting with SQL Server 2008)
• Indexed view (starting with SQL Server 2000)
• Triggers (not recommended as the check is after the modification is completed and if invalid has to perform a rollback)

... and don't say Microsoft didn't get it right because it's Microsoft... Access implements UNIQUE constraints correctly.

I've been holding this one off for quite some time, but finally bit the bullet... migrate the server.Coming from Windows Small Business Server 2003 (32-bit) going to Windows Server 2008 with Exchange 2007 (both x64), including transferring the web site. There were a few nasty pieces in the process and I didn't have/take the time/resources to test it all though before jumping in... so I got a disruption of about one hour for the blog and inbound smtp.

First issue I encountered was the installation of the server, which I ordered from Dell without operating system. I am a Microsoft Action Pack subscriber, so I slammed the 64-bit Windows Server from the Action Pack on the system, entered the Product Key and then went on to phone based activation. After putting in the last confirmation code and pushing the next button... my activation wasn't accepted!!! ... to cut a long story short, after dozens of voice menu selections and 4 persons, I found the guy who could help me out: Philip. Though it still toke Philip and me about 20 minutes to find the actual problem... I wasn't an Action Pack subscriber anymore!!! Technical story here, but it happened when Action Pack was integrated more tightly with the Partner Program. Somehow I didn't complete the Action Pack renewal transaction completely in February, but the partner site didn't show that (it only showed I'm still good for the Partner Program).

Couple of days later, I could go ahead with the migration process. It turned out to be easier than I expected after I found a very useful document about Upgrading Small Business Server 2003 to Exchange 2007. Some pointers on using the document though;

• Transferring the the other FSMO's (Schema Master is described); Determining FSMO Role Holders
• With Windows Server 2008 you NEED the Exchange 2007 SP1 DVD, Exchange 2007 without SP1 will not pass installation checks.
• Section Migrate mail send does not cover migrate mail receive. This caused me a bit of downtime... The issue was
  SMTP error from remote mail server after MAIL FROM:<abcd@efg.hi> SIZE=2703:
  host abcdef.ghi [10.10.10.10]: 530 5.7.1 Client was not authenticated
  And can be solved in the following manner in Exchange Management Console:
  • Go to the Server Configuration, Hub Transport. Select your server and add Anonymous to the Default SERVERNAME Receive Connector. While you're there, you can increase receiving message size here too.

And there were parts I did different;

• I continue to rely on Vamsoft's ORF for the anti-spam. Version 4.3 is fully compatible with Windows Server 2008 and Exchange 2007 and an ease to configure.
• Since I will continue the same DNS name for OWA and Windows mobile Active Sync, I transferred the Self-signed certificate from the SBS2003 (find it in your IIS, web site, directory security tab) to the new server.

My other bit of downtime was the blog... simply copy from the old server to the new one didn't cut it. So I downloaded the latest release of dasBlog from Codeplex, copied it into the virtual director of new server. Next I had to;

• Copy my own theme
• Compare (and adjust) the \SiteConfig files
• Transfer \Content files and \Logs files
• Make sure the account running the Application Pool has read on all dasBlog folders and change on \SiteConfig, \Content and \Logs.
• The Application Pool is running in Classic Managed Pipeline Mode

Guess I can say I didn't plan for the blog to be upgraded... that just had to happen.

Microsoft launched a new preview site for SQL Server 2008 R2. You can sign up for notifications about the releases of CTP's.

From an administrative viewpoint, focus seems at tools to manage, utilize and consolidate instances and machines.

Towards usability (mainly business intelligence), enhancements to integrate with Office 2010 (mainly Excel and SharePoint) are featured. Also further integration with Virtual Earth is shown.

Finally there is mention about something called Master Data Services and the Stewardship Portal. In concept a very powerful way to create and maintain a data warehouse bus.

Noticed this one on Born to Learn... Microsoft has two sessions today/tomorrow on preparing for the exam 70-432

Tuesday, March 24  7:30 A.M. Pacific Time   (What time is this in my region? 2009-03-24 15:30 CET)

Tuesday, March 24  5:30 P.M. Pacific Time   (What time is this in my region? 2009-03-25 01:30 CET)

It slipped past me too, but since I can manage to squeeze the first session in my schedule, I'll attend. Follow up and link to the recording to be posted later.

My impressions of this sessions is that Rob Boek takes you through the main areas of interest for the exam, at least it aligned quite well with what I can remember from my beta-test. Good stuff if you need an impression of the most important topics of the exam to prepare for.

Updated 2009-04-02

Updated 2010-07-24; added timeout to the receive command in the stored procedure. For more info, see Service Broker can make your transaction log big .

Event Notifications are based on Service Broker. This brings an advantage and a disadvantage; enhanced scalability at the cost of increased complexity (and lack of a User Interface, so we have to take it to T-SQL code). Lets take it there step by step for SQL Server 2005 and 2008;

1. Some code to cause deadlocks.
2. See how the deadlocks look like using the Trace Flags.
3. See how the deadlocks look like in Profiler.
4. Use the Event Notification on the deadlocks.
5. Sending out the mail.

The code shown as part of this article is also available at this link. To code to create the database for the deadlocks is available at this link. The code works with SQL Server 2005 SP2 and SQL Server 2008

1. Some code to cause deadlocks.

To get the code to cause the deadlocks, click this link. Run the code from SQL Server Management Studio (SSMS) to create the DeadLockSample database. Next create two additional connections (New Query) to the DeadLockSample database, execute the procedure procLockedTran1 on one connection and procLockedTran2 on the other connection. There is a 5 seconds delay in the two procedures between locking the first and second resource. So if you execute the two procedures within 5 seconds, you get your deadlock. You could also change the delay in the procedures if you think 5 seconds is to short/long.

Normally a process would be terminated when it is the deadlock victim. For the procedures created by the script, the error is handled , so the process that is the deadlock victim just selects it is the victim and the connection will not be terminated.

If you want to create a deadlock in any of the following scenarios, just run procLockedTran1 and procLockedTran2 on two separate connections simultaneously.

2. See how the deadlocks look like using the Trace Flags.

To get the deadlocks recorded in the SQL Server Error Log, either set the trace flag as startup parameter in SQL Server Configuration Manager (see picture) which will cause the trace flag to be active each time you start your SQL Server (until you remove the trace flag again), or issue a DBCC TRACEON (1222,-1) command which sets the trace flag until you stop SQL Server or issue a DBCC TRACEOFF (1222,-1).

With the trace flag active, you can now see the deadlock appear in you SQL Server Error Log.

3. See how the deadlocks look like in Profiler.

... or in SSMS for that matter. With the SQL Server tools it is possible to display deadlock graphs; XML documents (with .xdl extension) describing the deadlock information of one or more deadlocks. When the deadlock graph event (EventClass 148) with the TextData column is included in the trace definition, the XML to create the deadlock graph is captured (both through the Profiler graphical tool and the sp_trace procedures) and the graph will automatically be displayed when opened in Profiler. Also the graphical profiler has the option to write deadlock graphs directly to a file. Note that the graphical presentation of the deadlock is limited compared to the information in the .xdl document. The information in the .xdl document is the same information as the information written to the SQL Server Error Log with trace flag 1222. One extra comment if you filter your trace on database, you won't get the deadlock graph, as noticed by Kalen Delaney.

4. Use the Event Notification on the deadlocks.

Event Notification lets you capture events that can occur on server, database or broker queue, for a list of events, query sys.event_notification_event_types. For this purpose, the event type 1148, DEADLOCK_GRAPH is of interest. Also note that the DEADLOCK_GRAPH is a trace event, which can only be captured at server level.

The second thing needed is a broker SERVICE to send the event to. Since we'll be handling the events at server level, the SERVICE (and QUEUE) will be created in the msdb database. There is no need to create the MESSAGE TYPEs  or CONTRACTs as they already exist for Event Notification.

USE msdb
GO
-- Create a queue to receive messages.
CREATE QUEUE queDeadLock_Graph ;
GO
-- Create a service on the queue that references
-- the event notifications contract.
CREATE SERVICE svcDeadLock_Graph
ONQUEUE queDeadLock_Graph
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification] );
GO
-- Now query the sys.databases for the service_broker_guid of the msdb database.
SELECT service_broker_guid FROMsys.databasesWHERE name ='msdb';

The result of the query for the Service Broker GUID for the msdb databases is needed to point to the right database for the service.

-- Create the event notification.
CREATE EVENT NOTIFICATION evnDeadLock_Graph
ONSERVER
FOR DEADLOCK_GRAPH
TOSERVICE 'svcDeadLock_Graph'
, '2A7C5300-7C64-43F5-AB10-5B079D07678E' ; -- the GUID for msdb goes here.

You can look at the content of the queue through SELECT * FROM msdb.dbo.queDeadLock_Graph, as a queue in fact is a table. Next step will be processing the messages that get into the queue and more specifically get the deadlock details out of the message_body. The message body looks binary, but if you query it like this;

SELECT CAST (message_body ASxml)FROM msdb.dbo.queDeadLock_Graph

you'll notice that it is actually XML, where the textdata node holds the xdl structure of the deadlock graph.

5. Sending out the mail

Okay, we're in Service Broker land by now. Event Notification acts as the Initiator Service and the svcDeadLock_Graph is the Target Service. In this scenario, the only thing left to be done is specify how the queue should be processed. The processing shall be done by a stored procedure that will be tied to the queue as the activation program. Once something comes in to the queue, this stored procedure will be invoked... kind of like a trigger for insert on a normal table. The specification for the stored procedure will be:

1. Extract the deadlock graph from the message_body and store it in a variable.
2. Extract the datetime of the deadlock graph notification and store it in a variable.
3. Store the datetime and deadlock graph in a table.
4. Send the datetime as part of the message and deadlock graph as attachment in an email. This does require database mail to be set up, if you haven't, an error will be logged in the table.

The code to create the table to hold the deadlock information (and the reason why no mail was sent).

USE msdb
GO
CREATE TABLE dbo.tblDeadLock_Log (
DeadLock_ID intIDENTITY(1,1)CONSTRAINT pk_tblDeadLock_Log PRIMARY KEY
, DeadLock_Detected datetime
, DeadLock_Graph xml
, NoMailReason nvarchar(2048 ))
GO

The stored procedure to be used as the activation program on the queue, this is where the real work is done. Alter the @profile_name and @recipients in at sp_send_dbmail to match the setup of your dbmail setup.

CREATE PROCEDURE dbo.procReceiveDeadLock_Graph
AS
DECLARE @conversation_handle uniqueidentifier
DECLARE @message_body xml
DECLARE @message_type_name nvarchar(128 )
DECLARE @deadlock_graph xml
DECLARE @event_datetime datetime
DECLARE @deadlock_id int
BEGIN TRY
BEGIN TRAN
WAITFOR (
RECEIVE TOP (1) @conversation_handle = conversation_handle
, @message_body =CAST(message_body ASxml )
, @message_type_name = message_type_name
FROM dbo.queDeadLock_Graph)
, TIMEOUT 10000 -- Line added 2010-07-24;
-- http://resquel.com/ssb/2010/07/24/ServiceBrokerCanMakeYourTransactionLogBig.aspx
-- Validate message
IF (@message_type_name ='http://schemas.microsoft.com/SQL/Notifications/EventNotification' AND
@message_body.exist('(/EVENT_INSTANCE/TextData/deadlock-list)')= 1 )
BEGIN
-- Extract the info from the message
SELECT @deadlock_graph = @message_body.query('(/EVENT_INSTANCE/TextData/deadlock-list)' )
, @event_datetime = @message_body.value('(/EVENT_INSTANCE/PostTime)[1]','datetime' )
-- Put the info in the table
INSERT dbo.tblDeadLock_Log(DeadLock_Detected, DeadLock_Graph )
VALUES (@event_datetime, @deadlock_graph )
SELECT @deadlock_id =SCOPE_IDENTITY ()
-- Send deadlock alert mail.
-- Requires configured database mail, will log an error if not (or anything else goes wrong).
BEGIN TRY
DECLARE @subj nvarchar(255), @bdy nvarchar(max), @qry nvarchar(max), @attfn nvarchar(255 )
SELECT @subj ='A deadlock occurred on '+ @@SERVERNAME
, @bdy ='A deadlock occurred at '+CONVERT(varchar(50),@event_datetime, 120)+' on SQL Server: '+@@SERVERNAME+ '. See attached xdl-file for deadlock details.'
, @qry ='SET NOCOUNT ON; SELECT deadlock_graph FROM msdb.dbo.tblDeadLock_Log WITH (READUNCOMMITTED) WHERE DeadLock_ID = '+CAST(@deadlock_id ASvarchar(10 )) -- Locking hint is to prevent this dynamic query to be blocked by the lock held by the insert. The dynamic SQL will not come from inside this transaction.
, @attfn =@@SERVERNAME+'_'+CAST(@deadlock_id ASvarchar(10))+ '.xdl'
EXEC sp_send_dbmail @profile_name = 'default'
, @recipients = 'administrator@localhost'
, @subject = @subj
, @body = @bdy
, @query = @qry
, @attach_query_result_as_file = 1
, @query_attachment_filename = @attfn -- http://support.microsoft.com/kb/924345
, @query_result_header = 0
, @query_result_width = 32767
, @query_no_truncate = 1
END TRY
BEGIN CATCH
UPDATE dbo.tblDeadLock_Log
SET NoMailReason =ERROR_MESSAGE ()
WHERE DeadLock_ID = @deadlock_id
END CATCH
END
ELSE -- Not an event notification with deadlock-list
END CONVERSATION @conversation_handle
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
GO

Finally to tie the stored procedure to the queue so incoming notifications will be processed.

ALTER QUEUE dbo.queDeadLock_Graph
WITH
STATUS = ON ,
ACTIVATION (
PROCEDURE_NAME = msdb.dbo.procReceiveDeadLock_Graph ,
STATUS = ON ,
MAX_QUEUE_READERS = 1 ,
EXECUTE AS OWNER )
GO

Now try the procedures procLockedTran1 and procLockedTran2 and see the results in the msdb.dbo.tblDeadLock_Log and in your mail (if configured).

Okay, here's the deal; deadlocks sometimes occur and you want to be notified as they happen.

So define an SQL Agent Alert on error 1205 to send you an e-mail notification... partially, but it won't work as error 1205 isn't logged.

How to get the error logged; either set the severity level to 19 or above when the message is created (does not apply to 1205) or flag the message to be logged to event log, which can be done using sp_altermessage... at least this was the way to do it with SQL Server 2000 but one of the changes for SQL Server 2005 says:

sp_altermessage cannot be used to change the logging behavior of system messages (messages with Message ID < 50000). To audit system messages, use SQL Trace and the User Error Message Event Class. For more information, see Introducing SQL Trace.

For SQL Server 2005 it used to be a dead end, but with as of Service Pack 3 it is again possible to alter sys.messages < 50000 so they are written to the Windows NT Application Event Log. Specifically for deadlocks you'd run:

EXEC sp_altermessage 1205, 'WITH_LOG', 'true'

but it would apply to other system events you want logged too.

Update 2009-04-08; issue fixed in Service Pack 1 for SQL Server 2008. For SQL Server 2008 (up until Cumulative Update 3) it is still impossible to alter sys.messages < 50000. Though it was mentioned (2008-07-28) at connect in FeedBackID=294122 that this issue would be fixed for SQL Server 2008 too, so far (2009-02-27) it isn't.

Besides the fact that the alert option is still impossible with SQL Server 2008, it can only tell you a deadlock occurred. Should you need more information on the deadlock however, your next option would be TRACE flags 1204 or 1222, the difference between the two of them being the amount of detail. Trace flag 1222 will be use during the post as it provides the most detail. Setting the trace flags writes the deadlock information to the SQL Server Error Log, but not to the Windows NT Application Event Log... so there's one two (and a half) option remaining:

Process the Error Log with the undocumented sp_readerrorlog as part of the alert response... but that won't work for SQL Server 2008 (yet) and you have a better option for SQL Server 2005. For more info see MSSQLTips.

SQL Server 2005 & 2008: Event Notification

SQL Server 2005 & 2008: WMI Alert, see MSDN

So next post will be on getting your deadlock info through Event Notification.