Author Archive | Stuart Moore

Generating a list of full text word breakers for SQL Server

This isn’t going to be a nice handy list, but it will show how to quickly get such a list.

The basic principle is going to rely on sys.dm_fts_parser. As described on the linked page this takes a string, and then breaks it down into ‘tokens’ based on the defined language, word breaker, thesaurus and stop list. As an example:

select * FROM sys.dm_fts_parser ('"the quick brown fox jumpsover"', 1033, 0, 0)

The typo is deliberate, for once!. And this returns:

Splitting full text strings into tokens in SQL Server

So it’s returned a row for every token it can find. This example was nice and simple as it only contains words and spaces. The type was to show that without a word breaker SQL Server will just return the string without interpreting it. So that was for spaces, now just to confirm for other characters. First of all commas:

select * FROM sys.dm_fts_parser ('"quick,brown"', 1033, 0, 0)

Splitting full text strings with word breakers in SQL Server

So, that returns 2 rows as expected. How about hyphens?:

select * FROM sys.dm_fts_parser ('"quick-brown"', 1033, 0, 0)

Are hyphens word breakers in SQL Server full text searches?

Interesting, we get 3 rows this time. This makes sense when you think that hyphenation isn’t an exact science so some people will use them, and some won’t. So by combining both ‘sides’ of the hyphenated word SQL Server can hopefully match both uses.

So, that’s the basic theory. So this little piece of T-SQL is going to loop through all the 255 ASCII characters. For each one we’re going to use it to join 2 ‘words’, and then run that string through sys.dm_fts_parser. If the function returns more than 1 row we now that it’s found a word breaker, so we then output the character, and the character code as not all the characters are printable. You’ll also notice that code 34 throws an error, that’s because it’s ” which is a reserved character within full Text searches.

declare @i integer
declare @cnt integer
set @i=0
while @i<255
begin
  set @cnt=0
  select @cnt=COUNT(1) FROM sys.dm_fts_parser ('"word1'+CHAR(@i)+'word2"', 1033, 0, 0)
  if @cnt>1
  begin
    print 'this char - '+char(@i)+' - char('+convert(varchar(3),@i)+') is a word breaker'
  end
  set @i=@i+1
end

Which gives a nice long list:

this char –  – char(1) is a word breaker this char –  – char(2) is a word breaker this char –  – char(3) is a word breaker this char –  – char(4) is a word breaker this char –  – char(5) is a word breaker this char –  – char(6) is a word breaker this char –  – char(7) is a word breaker this char –  – char(8) is a word breaker this char – – char(9) is a word breaker this char – – char(10) is a word breaker this char – – char(11) is a word breaker this char – – char(12) is a word breaker this char – – char(13) is a word breaker this char –  – char(14) is a word breaker this char –  – char(15) is a word breaker this char –  – char(16) is a word breaker this char –  – char(17) is a word breaker this char –  – char(18) is a word breaker this char –  – char(19) is a word breaker this char –  – char(20) is a word breaker this char –  – char(21) is a word breaker this char –  – char(22) is a word breaker this char –  – char(23) is a word breaker this char –  – char(24) is a word breaker this char –  – char(25) is a word breaker this char –  – char(26) is a word breaker this char –  – char(27) is a word breaker this char –  – char(28) is a word breaker this char –  – char(29) is a word breaker this char –  – char(30) is a word breaker this char –  – char(31) is a word breaker this char – – char(32) is a word breaker this char – ! – char(33) is a word breaker Msg 7630, Level 15, State 3, Line 7 Syntax error near ‘word2’ in the full-text search condition ‘"word1"word2"’. this char – # – char(35) is a word breaker this char – $ – char(36) is a word breaker this char – % – char(37) is a word breaker this char – & – char(38) is a word breaker this char – ( – char(40) is a word breaker this char – ) – char(41) is a word breaker this char – * – char(42) is a word breaker this char – + – char(43) is a word breaker this char – , – char(44) is a word breaker this char – – – char(45) is a word breaker this char – . – char(46) is a word breaker this char – / – char(47) is a word breaker this char – : – char(58) is a word breaker this char – ; – char(59) is a word breaker this char – < – char(60) is a word breaker this char – = – char(61) is a word breaker this char – > – char(62) is a word breaker this char – ? – char(63) is a word breaker this char – @ – char(64) is a word breaker this char – [ – char(91) is a word breaker this char – \ – char(92) is a word breaker this char – ] – char(93) is a word breaker this char – ^ – char(94) is a word breaker this char – { – char(123) is a word breaker this char – | – char(124) is a word breaker this char – } – char(125) is a word breaker this char – ~ – char(126) is a word breaker this char –  – char(127) is a word breaker this char –

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Stuart Moore

About Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

Execute as login versus Execute as user

A handy feature on SQL Server is EXECUTE AS, this allows you to temporarily jump context to either another User or another Login, and then REVERT back once you’re done. Normally this is used in applications for allowing some functionality to run with higher than usual privileges, but it’s a great tool for debugging permission queries without changing your own settings.

Where people can get confused with this is with the differnce between User and Login contexts and why things work under one and not the other.

The key to remember is that User only references the database level user context. These means:

  • You can only ‘see’ things within the database context you switched as a ‘User’ only exists in one database. So no views, tables or sprocs in other dbs (so no access to linked server or system procedure in master,msdb,etc)
  • You will only get the database level permissions and roles, so no inheritance of Server level roles (sysadmin, dbcreator, etc)

Executing as Login means that you get the Instance wide permissions assigned to that Login. So if the login has rights across multiple DBs you can query them all, or execute system procedures.

So for a quick demo, let’s set up a little test environment. We’ll create a 2 databases called aasectest and absecttest, a SQL login call sectest, then grant it access into aasectest and absectest with a User testsec, and to give us something to select, a table in each DB:

USE [master]
GO

CREATE DATABASE [aasectest] 
go

CREATE DATABASE [absectest] 
go

CREATE LOGIN [sectest] WITH PASSWORD=N'', DEFAULT_DATABASE=[aasectest]
go

use [aasectest]
go

CREATE USER [testsec] FOR LOGIN [sectest] WITH DEFAULT_SCHEMA=[dbo]
GO

CREATE USER [testsec] FOR LOGIN [sectest] WITH DEFAULT_SCHEMA=[dbo]
GO

CREATE TABLE atable
(
col1 char(9)
)
GO
insert into atable (col1) values ('assectest')
GO

use [absectest]
GO

CREATE USER [testsec] FOR LOGIN [sectest] WITH DEFAULT_SCHEMA=[dbo]
GO

CREATE TABLE atable
(
col1 char(9)
)
GO
insert into atable (col1) values ('absectest')
GO

 

Now we’ll try some queries. First of all, with AS USER testsec:

use[aasectest]
go
EXECUTE AS USER = 'testsec'
GO
select col1 from aasectest.dbo.atable;
go
select col1 from absectest.dbo.atable;
go
use [absectest]
print 'switch database'
go
REVERT
GO
use [absectest]
go
EXECUTE AS USER = 'testsec'
GO
select col1 from aasectest.dbo.atable;
go
select col1 from absectest.dbo.atable;
go
REVERT
GO

The Messages returned being:

(1 row(s) affected)
Msg 916, Level 14, State 1, Line 1
The server principal "sectest" is not able to access the database "absectest" under the current security context.
Msg 916, Level 14, State 1, Line 1
The server principal "sectest" is not able to access the database "absectest" under the current security context.
swtich database
Msg 916, Level 14, State 1, Line 1
The server principal "sectest" is not able to access the database "aasectest" under the current security context.

(1 row(s) affected)

As can be seen, we can only see the data within the correct database for the user context we’re executing under.

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Keep your notes as up to date as your knowlege

So I’m sitting here refreshing myself on SQL Performance tuning by having a leaf through Grant Fritchey’s (blog|twitter) updated SQL Server 2012 Performance Tuning

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Stuart Moore

About Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

Any days a good day if you can learn something

Had one of those days where I didn’t do anything stunningly impressive as every job seemed to be just some tinkering to fix other people’s problems.

But looking back on it the day has bee a great success as each one of the three problems lead to me revising some old skills or learning more about something I’ve not had to play with as much before hand

One of them was a classic developer’s SQL Server performace problem with parameter sniffing. One set of parameters pushed for a full scan and a spooled sort,

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Stuart Moore

About Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

Never credit SQL Server with intelligence it doesn’t have

and that goes double for SQL Server Management Studio (SSMS)

Yet another example of someone having problems with backups and SQL Server, mostly caused by not checking exactly what’s happening and not having a full written procedure in place.

DBA was notified of a problem with a key production system. After a bit of digging they realised that a misbehaving SSIS package had truncated a table. Weighing up the chance of the package running perfectly and not causing any more problems, the DBA decided to restore the database to a temporary location from a backup and then migrate the data across. The package run at 19:30, before the full backup kicked in at 20:00. Being a good DBA they had plenty of hourly transaction log backups as well, so a few clicks in SSMS and a point in time restore was underway. Feet up, a coffee while waiting and then off to the pub having pocketed some nice overtime.

Unfortunately, when the the restore had completed our brave DBA found that the table in the restored DB was also empty. OK, maybe the table was corrupted by something else and not the SSIS package, so they setup a point in time restore for close of business @17:00, as the users hadn’t complained then. Hoping for a quick restore as last orders is fast approaching our brave DBA sets it off.

Restore finishes, and the table is still empty. Getting slightly worried by now the DBA tries the night before’s backup as a last resort, though they can’t imagine how the system could have run a day with an empty table. Guess what, still no luck.

At this point we get called in as noone can work out what’s going on. A quick look in msdb.dbo.backupfiles and we had our answer:

select 
a.backup_start_date,
b.physical_device_name
from 
backupset a
inner join 
backupmediafamily b on a.media_set_id=b.media_set_id 
where 
a.type='D' and 
a.database_name='dbname' 
order by a.last_lsn desc

Our DBA had been diligently backing up the database every day like the should, but they’d been backing it up to the same dbname.bak file every night, complete with init and noappend, so every night the same file was being overwritten. SQL Server will only do what you tell it to.

But why didn’t the point in time restores complain? Well again, SQL Server went off to look in msdb.sys.backupfile and worked out which files it needed to do the restore. Guess which file it used every time? And then guess what, as the following transaction backups were for later LSNs, SQL Server happily ignored them.

Once we’d pulled the earlier version of dbname.bak off of tape and manually built up some T-SQL statements to do the required point in time restore everything worked as expected and we could just migrate the table data across.

The first moral of the story is to be careful about what you tell SQL Server to do, as it will do exactly that. Just like the petulant 5 year old.

And the second is that SSMS can be pretty dumb about what it lets you set when building up backups and restores. If the restore here had been down with T-SQL in the first instance, then the DBA would have seen error messages as SQL Server refused to apply transaction log backups due to LSN order.

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Stuart Moore

About Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

The Internet – My saviour and My nemesis

Like any good SQL DBA these days, the internet is a godsend to my career and professional development. I can quickly and easily find articles and notes from world experts on complex topics, and even interact with those experts as well via twitter, email or skype. Compared to the bad old days of having to know someone’s phone number, or battling through tiers of support to get to someone who knew something this is absolutely amazing.

But, on the other hand…….

How many of us are now used to a user (of many levels) coming across to our desk, or dropping us an email about a great new technique they’ve just seen on a random internet search. The canonical examples are with (nolock) and the ever classic one about restoring deleted rows directly out of transaction logs.

As always these are things where the correct answer to “Is this a good idea?” is “It depends”.

As SQL Server DBAs/experts we’ve worked hard to build up a level of internal filtering that lets us look at the post on StackExchange or Twitter, step back and evaluate if it’s something that’s really a good idea. We’ll then have taken it away and played with it on development systems before we think about adding it to our toolkit so we’re fully aware of the techniques limitations. And if we ever do have to deploy it in a production environment we’re also well aware of the amount of work that’s needed, and the amount needed if it goes wrong, and we’ll know which mug will be fixing it,

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Stuart Moore

About Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

SQL Server Resource Governor Classification function based on db role membership

Inspired by a question on #SQLHelp on twitter here’s a quick classifier function for resource governor that lets you base the resource group based on the connections db role membership.

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Stuart Moore

About Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

In praise of pens, pencils and paper

Even in these days of Netbooks, Tablets and Evernote I still find that a simple pad of paper and a basic pen or pencil is a vital thing to carry round.

Ultra simple to carry everywhere, even when going ultralight. Easy to replace if you do manage to misplace or forget them, I can certainly think of more places with 300m of this desk that I can pick them up from than I can tablet sellers. And most of them will be selling it for less than the cost of an App, let alone the device.

As a follower of “Getting Things Done” I find dumping all the ideas out onto paper a much quicker way of working. Just turning over the page or grabbing another sheet off the stack is always faster than saving a note and starting a new one. It also just lets you flow as you go. And if you suddenly find you need to mind map or brainstorm something there’s no panic about which app to use, maybe just whether to grab a larger sheet or not.

Yes, there’ll be a little overhead later when it needs transcribing. But you could always just photograph it with a SmartPhone and upload it, or invest in a scanner and some OCR software. In fact I find this a useful way of making sure I only take onboard the pertinent actions following a meeting, by going through each page of the notebook I just snap the ones I need and send them up to Evernote. Job Done.

Suddenly find yourself needing to sketch a schema diagram or an new proposed infrastructure architecture? No problems with pencil and paper.

 

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Stuart Moore

About Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

Problems migrating and upgrading SQL Server Reporting Services DBs

Ever upgaded a SQL Server Reporting Services instance after migrating to a new SQL Server, and everything looks good until you click on the Report Server URL to be greeted with:

The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is ‘C.0.8.54’. The expected version is ‘147’. (rsInvalidReportServerDatabase)

You dillegently read through all the google and bing results you can find. But there’s nothing useful in there, and all the guides say that connecting the Reporting Services instance should upgrade the database schemas to the correct versions. So why isn’t it for you?

By now you’ve started to delve into the depths of the Reporting Services logfiles (always a great place to look for any Reporting Services issues, can be quicker than googling). And you’ve spotted what looks like the smoking gun:

library!WindowsService_0!b84!07/23/2012-11:28:31:: i INFO: Current DB Version C.0.8.54, Instance Version 147.
library!WindowsService_0!b84!07/23/2012-11:28:31:: i INFO: Starting upgrade DB version from C.0.8.54 to 147.
library!WindowsService_0!b84!07/23/2012-11:28:32:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: , An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database.;
library!WindowsService_0!b84!07/23/2012-11:28:32:: e ERROR: ServiceStartThread: Exception caught while starting service. Error: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database. ---> System.Data.SqlClient.SqlException: Cannot find the object "dbo.SnapshotData" because it does not exist or you do not have permissions.
Changed database context to 'ReportServerTempDB'.

So you dutifully go and check if dbo.SnapShotData exists, check the permissions, maybe even give you service user sysadmin rights as a last chance hope?

And still none of it works. So what is the problem then. Well, did you move over ReportServerTempDB as well as ReportServer? Wait a sec, why should you? Didn’t some guy on the internet say you didn’t need to backup that one, just like TempDB? Well they may have said it, and sometimes they might have been right. But when doing an uprade you need ReportServerTempDB as well. So now you need to find it, hopefully you’ve still got the original kicking around to make life easier. If not you’ll need to find one of the same original version and then sort the permissions out.

This is a very common issue we see when we get called in to look at Reporting services upgrades gone bad. For most instances, the space used by a ReportServerTempDB backup isn’t really a worthwhile reason for not taking one just to make your life easier.

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Stuart Moore

About Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

Managing Microsoft Message Queuing in a cluster remotely

Ever had the problem when you’ve only got remote access to your cluster nodes and you need to modify a clustered MSMQ instance, but the tools just won’t connect to the correct instance, even when you’ve tried the set computername command prompt trick, you can’t use mstsc /console or you’re stuck on Windows 2008R2 and you don’t have the manage option on right click in Failover Manager.

Make your life so much easier by getting a nice simple free tool from Microsoft MMCV –

Stuart Moore

Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Stuart Moore

About Stuart Moore

Nottingham based IT professional with over 15 years in the industry. Specialising in SQL Server, Infrastructure design, Disaster Recovery and Service Continuity management. Now happily probing the options the cloud provides

When not in front of a computer, is most likely to be found on the saddle of his bike