SQL Clusters And The Full-Text Search Service

I got an email from a friend this morning asking if I had a clustered resource for the Full-Text Search (FTS) service (actually called the SQL Full-Text Filter Daemon Launcher service). I pondered this for a few seconds and didn’t recall seeing one, so I logged on to one of my 2008 R2 test clusters and took a look. Nope, no FTS resource exists.

This made me a little concerned. If there’s no resource and the service fails or crashes would the cluster failover? To test this I logged on to one of the nodes and stopped the service.

Nothing happened.

The service stopped but there was no cluster failover or anything. As the FTS service is critical I thought this would break FTS. To test this out I created a new table, threw in some values and created a new Full-Text Index.

SET NOCOUNT ON;

/* Create the table */

CREATE TABLE test

    (

      c1 INT IDENTITY(1, 1) ,

      c2 NVARCHAR(MAX) DEFAULT 'some text'

    );

/* Add the PK */

ALTER TABLE test ADD CONSTRAINT Test_PK PRIMARY KEY CLUSTERED (c1);

/* Insert some records so we have something to see */

INSERT  INTO test

        DEFAULT VALUES

GO 500

/* Create the FT catalog and index */

CREATE FULLTEXT CATALOG [testft] AS DEFAULT;

CREATE FULLTEXT INDEX ON dbo.test (c2)

KEY INDEX Test_PK;

/* Wait a few seconds for population to take place and then query the FTI */

SELECT TOP 100

        *

FROM    test

WHERE   CONTAINS ( c2, 'text' )

I was expecting this to not work as I’d killed off the service.

Surprisingly I got results. How had that happened?

I went back to the server and found that the FTS service had started back up again. I killed it, ran the select statement again and still got results.

I read the description of the FTS service which states that it will “launch the full-text filter daemon process”. I went in search of that and killed off the fdhost.exe process and once more ran the query. This time I got an error which appears completely unrelated but is caused by the fdhost.exe process not running.

Msg 30053, Level 16, State 102, Line 1
Word breaking timed out for the full-text query string. This can happen if the wordbreaker took a long time to process the full-text query string, or if a large number of queries are running on the server. Try running the query again under a lighter load.

Now FTS was truly dead but I wanted to see if it could be resurrected without any intervention from me.

By default Full-Text indexes are created with automatic change tracking, so I just added a few more rows to the table to see what would happen.

Change tracking kicked in and SQL Server kicked off the FTS service, which in turn launched the fdhost.exe process. With fdhost.exe running I was then able to run the Full-Text query again and return results.

I have to say I was initially freaked out by there not being any kind of resource to manage Full-Text Search but it turns out that you really don’t need one. SQL handles it all behind the scenes for you. Excellent work Microsoft!

Reading SQL Server Error Logs Using PowerShell

I was messing around with PowerShell the other day and using it to read SQL Server error logs. It’s actually a pretty trivial thing.

From SQLPLS

$Logs = DIR SQLSERVER:\SQL\LOCALHOST

$Logs.ReadErrorLog()

Quick and easy. Then I tried to read from a clustered instance of SQL Server and ran into an issue

$Logs = DIR SQLSERVER:\SQL\CLUSTEREDINSTANCE

$Logs.ReadErrorLog()

This didn’t make a lot of sense, after all the method was there before. I decided to go and check to see if the method existed

$Logs = DIR SQLSERVER:\SQL\CLUSTEREDINSTANCE

$Logs | gm

Yup, the method exists just fine.

I couldn’t figure out what was going on. I enlisted the help of Sean McCown (blog|twitter) who posted a short training video on how to read the error logs on named instances of SQL Server.

Who Owns Your Databases And Jobs?

Quick question, who owns your databases? And how about your jobs? Don’t know? Go find out…now find out without using the GUI.

Querying sys.databases will give you the owner_sid. The trouble is that you have to relate that owner_sid back to a user. The same with sysjobs, that sid needs to relate to someone.

You could attempt joining against sys.server_principals, however if there’s a user that doesn’t exist or is a part of a domain group they wouldn’t show up and you would just get a NULL returned. Try the following example:

SELECT  d.NAME ,

        sp.NAME

FROM    sys.databases d

        LEFT JOIN sys.server_principals sp ON d.owner_sid = sp.sid;

SELECT  s.NAME ,

        sp.NAME

FROM    msdb.dbo.sysjobs s

        LEFT OUTER JOIN sys.server_principals sp ON s.owner_sid = sp.sid ;     

Now what, how can you quickly and easily get the owner? A little known system function will come to the rescue.

SUSER_SNAME (server_user_sid) will return the login name associated with a SID. Using this you can very quickly grab the information about who owns what without having to play with joins or get incomplete results:

SELECT  name ,

        SUSER_SNAME(owner_sid)

FROM    sys.databases;

SELECT  name ,

        SUSER_SNAME(owner_sid)

FROM    msdb.dbo.sysjobs;

There’s some great little hidden gems lying around in SQL Server like this. You just need to find them.

The Importance Of Good Documentation

Believe it or not I’m not actually talking about server documentation here (for an excellent post on that go read Colleen Morrow’s The Importance of a SQL Server Inventory).

I have spent the last 12 days dealing with a single production release. It is being considered a significant release, but to be honest it really isn’t. The biggest challenge has been to do with the way that the release documentation has been provided and the fashion in which the scripts have been built.

What I got

Here’s a brief example of a change request I’ve seen:

• Change Request:
  • Update database – products (this links to a Sharepoint page)
  • Use code from this location (links to a file share)
• Sharepoint page
  • Go to this location (but replace the middle part of the link with the link from the change request page)
  • Copy this subfolder to your machine
  • Follow the process on Sharepoint page 2 to deploy the code
  • Once Sharepoint page 2 is complete run script X
• Sharepoint page 2
  • run script 1
  • run script 2
  • run script 3

Pretty painful right? Now multiply that by 8 for each of the database code deployments that needed to be completed. No fun, no fun at all.

What do I want?

It’s going to be a work in progress but we’ll be working with this particular dev team to put together a unified document to simplify the release structure.

Here’s what I want to see:

• Change Request:
  • Update database – products – deployment instructions attached
• Attachment
  • Deploy script 1 (link to script)
  • Deploy script 2 (link to script)
  • Deploy script 3 (link to script)
  • Deploy script X (link to script)
  • Rollback script (link to script)

The difference?

Instead of having to reference several different Sharepoint locations in addition to a change control document I now have a single document, attached to the change, which clearly defines the process for the release, the order for scripts to be executed, a link to each of those scripts and the relevant rollback information.

It’s not something that I think is too out of line to provide, but I’ve found the folks who have been providing releases in this method are extremely resistant to change. I can understand that, but to be fair, they aren’t the ones under the gun trying to put something in to a production environment in a consistent and stable manner.

I’ve lots of fun meetings coming up to talk about this.

What about you?

How do you get your change control documentation? Is it something plainly written and easy to follow? Or do you have to have a degree in cryptography to get code in to production?

SSRS–Email Subscriptions Not Being Delivered

If you work with replication much I'm sure you've come across the situation whereby somebody created a publication and at some point in the future left the company. Once their AD account had been disabled or removed then the replication jobs, for some reason, would stop functioning and you would have to spend your time trying to figure out why. Eventually you would realize that the jobs had been mysteriously created under the context of that user account and so now longer work. A quick change so that jobs run under SA and everything is all happy happy again.

Well I had something similar happen this week with Reporting Services where all of a sudden email subscriptions were failing and critical reports were not being sent out. This is not the kind of call that you want to get during the night.

Identifying the failure was pretty easy, that was just a case of opening up the SSRS log file (by default found at C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles) and looking through for the error:

emailextension!WindowsService_463!508!04/19/2012-07:10:06:: e ERROR: Error sending email. Microsoft.ReportingServices.Diagnostics.Utilities.RSException: The permissions granted to user ' <domain>\<user> ' are insufficient for performing this operation. ---> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user '<domain>\<user>' are insufficient for performing this operation.

notification!WindowsService_463!508!04/19/2012-07:10:06:: e ERROR: Error occured processing subscription 0ceefc23-7857-4fa4-a180-49c1590f00d9: Failure sending mail: The permissions granted to user ' <domain>\<user> ' are insufficient for performing this operation.Mail will not be resent.

emailextension!WindowsService_463!508!04/19/2012-07:10:06:: e ERROR: Error sending email. Microsoft.ReportingServices.Diagnostics.Utilities.RSException: The permissions granted to user ' <domain>\<user> ' are insufficient for performing this operation. ---> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user ' <domain>\<user> ' are insufficient for performing this operation.notification!WindowsService_463!508!04/19/2012-07:10:06:: e ERROR: Error occured processing subscription 0ceefc23-7857-4fa4-a180-49c1590f00d9: Failure sending mail: The permissions granted to user ' <domain>\<user> ' are insufficient for performing this operation.Mail will not be resent.

Error in hand I went looking at the report in question. It had been created by the user who had left the company and so had two subscriptions. The first subscription was file based. That was having no trouble executing, which was interesting. The other, an email subscription was the one that was failiing. 

My belief at this juncture was that the file subscription, which has a user context for execution, was working ok because of that user context and the other was failing because the AD account of the old user was no longer around.

Going back on old knowledge I figured that I could just change the owner of the subscription and everything would be happy again and the reports would flow. I spent a good 30 minutes trying to figure out a way to do that. There's actually no option, you have to drop and recreate the subscription. For me this would require changes to a lot of subscriptions, with several of them being data driven. Doing this would have taken an age.

Time to see if a meta-data update would help fix the issue.

I started digging through the ReportServer database and was able to come up with a list of subscriptions that were owned by the old user:

SELECT  u.UserName ,

        s.SubscriptionID ,

        Report_OID ,

        Description ,

        LastStatus ,

        EventType ,

        DeliveryExtension

FROM    ReportServer.dbo.Subscriptions s

        INNER JOIN ReportServer.dbo.Users u ON s.OwnerID = u.UserID

        WHERE u.UserName = '<old user>'

With this information in hand I went out and create a new AD account specifically for owning reports that would send emails. After making sure that the account had permissions to access the reports that it would subscribe to I went and updated the subscriptions so that they would run under this new security context:

BEGIN TRAN

BEGIN TRY

    DECLARE @newid UNIQUEIDENTIFIER 

    SELECT  @newid = userid

    FROM    ReportServer.dbo.Users

    WHERE   UserName = '<new user>'

    UPDATE  s

    SET     OwnerId = @newid

    FROM    ReportServer.dbo.Subscriptions s

            INNER JOIN dbo.Users u ON s.OwnerID = u.UserID

    WHERE   u.UserName = '<old user>'

END TRY

BEGIN CATCH    

    SELECT  ERROR_MESSAGE()

    IF @@TRANCOUNT > 0 

        ROLLBACK TRAN;

END CATCH

IF @@TRANCOUNT > 0 

    COMMIT TRAN;

This updated 73 reports for me and only took a couple of seconds. Much better than the couple of days it would have taken to try and change every subscription.

Knowing this I'll be keeping a much close eye on subscription ownership in SSRS going forward.