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.

SQL2012 It’s The Small Things Pt4–User Defined Server Roles

Server roles have been around since before I started working with SQL Server however these roles have always been strictly defined and there has been no ability to either alter them or create new ones. SQL Server 2012 finally changes that and provides you with the opportunity to create server level roles that will let you provide greater permissions to logins without having to constantly deal with the minutiae of managing them on a login by login basis.

Let’s say for example you wanted to grant several logins the ability the view any definition. Normally this would require you to perform those grants on a very granular level. Now you can just create a server role and drop logins into that role:

USE [master]

GO

CREATE SERVER ROLE [SrvViewDefs]

GO

use [master]

GO

GRANT VIEW ANY DEFINITION TO [SrvViewDefs]

GO

ALTER SERVER ROLE [SrvViewDefs] ADD MEMBER [sirsql\Joe];

ALTER SERVER ROLE [SrvViewDefs] ADD MEMBER [sirsql\Biggles];

That’s some quick and easy management.

Here’s some examples of other permissions that can be quickly and easily provisioned to multiple users via user defined server roles:

• Alter any linked server
• Alter any login
• Alter any server audit
• Alter any server role
• Alter resources
• Alter server state
• Alter settings
• View any database
• Shutdown SQL Server

It goes beyond basic SQL Server permissions, you can also allow server roles to impersonate logins or manage Availability Groups

USE [master]

GO

CREATE SERVER ROLE [SrvImpersonate]

GO

use [master]

GO

GRANT IMPERSONATE ON LOGIN::[SIRSQL\nic] TO [SrvImpersonate]

GO

ALTER SERVER ROLE [SrvImpersonate] ADD MEMBER [sirsql\Biggles];

Anything that allows me to manage SQL Server more efficiently makes me happy. User defined server roles certainly make me happy.

SQL2012 It’s The Small Things Pt3–End Of The Month Function

If you work with financial data or have to do regular reporting type functions then I’m sure you currently use some kind of funky function/case statement to figure out what the last day of the month is and something even more funky to find the last day of next month or the month after.

SQL Server 2012 fixes that for you with the introduction of the EOMONTH function.

It’s very simple to use:

SELECT EOMONTH('2012/03/16')

Even better, the EOMONTH function can accept an additional parameter where you can specify a number of months to add or remove to get relevant results:

SELECT EOMONTH('2012/03/16', -1)

It even handled the leap year correctly.

Simplicity itself. A great addition.