Book Review - Brad's Guide to Maintenance Plans

Or to give it its full title:
Brad's Sure Guide to SQL Server Maintenance Plans (Dba Handbooks)

by Brad M McGehee

An exhaustive haul through the various options that Microsoft provide for maintenance.  Brad spends a lot of time talking about the Wizard, much of that time warning against it!  He is wary of the wizard, in part because it could lead the unwary to ticking every box and thus doing unnecessary and even damaging stuff. Ticking Rebuild and Reorganise, for example - it just makes your plan take twice as long. If you Rebuild, then do an unnecessary Update Statistics, the update can screw up the statistics that the rebuild created and hit performance as well as wasting time.

I've never used the wizard myself - the SSIS-style drag and drop interface in the plan designer is so easy to use that I don't see the need. Brad also recommends using T-SQL or Powershell scripts for more complex maintenance. In fact he recommends this on average once per chapter, so it tends to get a little tedious.

To my mind, this leaves a lot of middle ground where the simplicity of the graphic maintenance plan designer does the job neatly and effectively. Want to take a backup? Just pick the backup task, spend a few moments telling it what to backup and where to, and then schedule it to run at a convenient time. Job done! Why write the code when the plan can do it for you?

Here's why - you can rebuild indexes, or reorganise indexes - but best practice is probably to read the fragmentation level of each one and then decide whether to rebuild, reorganise or leave alone. Especially on large databases, especially if you have a narrow maintenance window, you don't want to do an unnecessary rebuild. For this your best bet is a custom script.

But for most stuff the maintenance plan is fine. Brad reminds me of all the things I should be doing - like the history clean up task. Have I scheduled this? Probably not everywhere - note to self - go and check. And he explains very well the point of multiple sub-plans which I hadn't grasped before.

UK Phone Numbers

I've been wrestling with a list of predominantly UK phone numbers for the last couple of days.  The data has come from a list of all the calls made to and from an organisation in the past year.  In particular, I want to identify the dialling code so that I can identify what geographical areas people are calling from.

First port of call is usually Google - I certainly don't want to write some code if I can just copy something that someone else has already done.  But sadly every entry I could find was about US phone numbers, which are easy peasy - area dialling codes are 3 digit across North America.  How rational!  Here in the UK dialling codes may be three, four, five or six digits long.  So if you have North American phone numbers to sort out, most of this article will be irrelevant.

Let's start with a bit of research - here and here. "In the United Kingdom, area codes are two, three, four, or, rarely, five digits long (after the initial zero). Regions with shorter area codes, typically large cities, permit the allocation of more telephone numbers as the local number portion has more digits. Local customer numbers are four to eight figures long. The total number of digits is ten, but in a very few areas the total may be nine digits (after the initial zero). The "area code" is also referred to as an "STD (code)" (subscriber trunk dialling) or a "dialling code" in the UK."  So, basically, it's a tangle of different standards.  OK, so I need some code which checks the first few digits and identifies which one is which. 


Data Cleansing
But before we get on to that, let's do some data cleansing.  I have a lot of what look like perfectly good phone numbers with a 92 prefix.  Like most firms, it's 9 to dial out, so I don't know where 92 comes from.  It's easy to deal with though- just a straightforward REPLACE() function.

replace(LEFT(strddi,2), '92', '')
means when the leftmost two characters of the DDI string are 92, replace them with a blank.

-- identify Direct Dialled numbers with 92 prefix - apparent error

update  [CUSTOMER].[PhoneCalls]

Set    strddi = replace(LEFT(strddi,2), '92', '')

WHERE LEFT(strddi,2) = '92'

AND         strDDIAreaCode IS NULL

AND         LEN(strddi) > 5;

There's another issue with incoming mobile phone numbers - in the UK, these start with 07, but somehow they have been recorded as starting with 7.  Replace does the job.

-- identify mobile numbers omitting 0 prefix - apparent error

-- change to UK standard 0

update  [CUSTOMER].[PhoneCalls]

Set    strcli = replace(LEFT(strCLI,1), '7', '07')

WHERE LEFT(strCLI,1) = '7'

AND         strCLIAreaCode IS NULL

AND         LEN(strCLI) = 10;

Then we have the incoming calls from UK numbers, but specifying the full international dialling code 0044.  Fine if you are based somewhere else, but really not necessary if you are in the UK and your caller is in the UK.

-- identify UK numbers with an international code 0044

-- change to UK standard 0

update  [CUSTOMER].[PhoneCalls]

Set    strcli = replace(LEFT(strcli,4), '0044', '0')

WHERE LEFT(strcli,4) = '0044'

AND         strCLIAreaCode IS NULL

AND         LEN(strcli) > 5;

Having dealt with UK codes that think they are worldwide, I can now go on and identify the legitimate international codes.  I could pick out the French calls (0033), German calls (0049) and so on, but I only have a very small percentage of non-UK calls so I'm just going to treat all these Johnny Foreigners the same and lump them together as "International" calls.  If you do want to be more accurate with your international calls, click here for a list of the codes.

-- identify  international codes 00

update  [CUSTOMER].[PhoneCalls]

set   strCLIAreaCode = 'International'

WHERE LEFT(strcli,2) = '00'

AND         strCLIAreaCode IS NULL

AND         LEN(strCLI) > 5;

And use the same technique to identify Mobile numbers:

-- identify mobile phone numbers 07

update  [CUSTOMER].[PhoneCalls]

set   strCLIAreaCode = 'Mobile'

WHERE LEFT(strcli,2) = '07'

AND         strCLIAreaCode IS NULL

AND         LEN(strCLI) > 5;

Freefone numbers are free only to people calling from Landlines, although I understand that there are plans to make them free to people using mobile  phones too.

-- identify Freefone  phone numbers 0800 etc

update  [CUSTOMER].[PhoneCalls]

set   strCLIAreaCode = 'Freefone'

WHERE LEFT(strcli,4) in ('0800', '0500', '0808')

AND         strCLIAreaCode IS NULL

AND         LEN(strCLI) > 5;

 

There is a string of special rate numbers beginning with 08 - once upon a time you could identify 0845 as local rate and 0870 as national rate, but the list has proliferated and now you can't really tell how much it is going to cost.   

-- identify special rate phone numbers 0845 0870 etc

update  [CUSTOMER].[PhoneCalls]

set   strCLIAreaCode = 'Special rate'

WHERE LEFT(strcli,2) = '08'

AND         strCLIAreaCode IS NULL

AND         LEN(strCLI) > 5;

One thing you can be sure of - a call to an 09 number is going to be outrageously expensive...

-- identify premium rate phone numbers 09

update  [CUSTOMER].[PhoneCalls]

set   strCLIAreaCode = 'Premium rate'

WHERE LEFT(strcli,2) = '09'

AND         strCLIAreaCode IS NULL

AND         LEN(strCLI) > 5;

Identifying UK Dialling Codes
So - on to the main point of this article.  UK dialling codes - area codes - whatever you want to call them, allow you to identify where in the country a caller is based.  Aberdeen is 01224, York is 01904.  But very often the dialling code and the subscriber number are held in a single field e.g. 01169158424.
Taking this example, it could in theory be divided thus:
011        6915 8424
0116      915 8424
01169    158424
011691  58424

A dialling code can be anything from 3 digits to 6 digits - a subscriber number can be anything from 4 to 8 digits.

So here's how to tackle the problem.  There are only five cities with three digit dialling codes, ten with four digits, and 12 with six - the rest have five.  So thanks to a bit of research in Wikipedia (see the links I posted earlier) I was able to construct the following CASE statement:

-- UK Dialling codes may have 3, 4 , 5 or 6 digits

-- pick appropriate code

--CLI

 update  [CUSTOMER].[PhoneCalls]

set strCLIAreaCode =

      (

            CASE

            -- 3 digit dialling codes e.g. London, Belfast

            WHEN LEFT(STRcLI,3) IN 

                 ('020', '023', '024', '028', '029' )

                  THEN LEFT(strcli,3)

                 

            -- 4 digit dialling codes e.g. Bristol, Leicester

            WHEN LEFT(STRcLI,4) IN 

                 ('0118', '0117', '0116', '0115', '0114', '0113',

                  '0121', '0131', '0141', '0151', '0161', '0191')

                  THEN LEFT(strcli,4)

                 

            -- 6 digit dialling codes e.g. Langholm, Keswick

            WHEN LEFT(STRcLI,6) IN 

                 ('013873', '015242', '015394', '015395',

                  '015396', '016973', '016974', '016977',

                  '017683', '017684', '017687', '019467' )

                  THEN LEFT(strcli,6)

                                   

            -- The remaining majority of codes are 5 digit

            ELSE  LEFT(strcli,5)

      END

      )

WHERE

       strcli <> 'WITHHELD'

       AND LEN(strcli) > 5

      AND strCLIAreaCode IS null;

       

Looking at 01169158424, it's easy to see that the first four digits match the four digit option, so the area code part of this number is 0116 - which represents Leicester

Finally, a bit of tidying up:

      

-- Remove any remaining nulls in area code fields

-- nulls remaining represent internal codes or unidentifiable

-- CLI

update  [CUSTOMER].[PhoneCalls]

set   strCLIAreaCode =  'N/A'

WHERE strCLIAreaCode IS NULL;

Incidentally, the code shown here is half the code I wrote - I had to do the job for both inbound (CLI - Caller Line Identification) numbers and outbound (DDI - Direct Dial In).  the code is essentially the same so I haven't troubled you with it. 

I hope you find this useful!

Book Review - Confessions of an IT Manager

You've read Dilbert?  Then you may have thought Scott Adams was joking.  He wasn’t, and Phil fleshes out the details.  From contract programmer to IT Director, Phil spills the dirt on them all,  not to mention recruitment agencies, for whom a special place is reserved in hell.  

This book was originally published as a series of articles on the Simple-Talk website. Take IT projects now, a subject close to my heart at the moment - they have a reputation for failing, especially the big ones.  Phil thinks it’s a well-deserved reputation.  Nonetheless he suggests a few things you can do to actually succeed in getting an IT project come in on time, under budget, and actually do something vaguely resembling whatever it’s supposed to do.  

Start by doing nothing until everyone agrees what is to be done.  Then any improvement, any change, has to go through a strict change control process.  (You may see already why there are so many failures). 

Don’t be a pioneer - use only boring proven technology.  If the programmers want to play with the latest TLAs (three letter acronyms) and put them on their CVs then fine, set up a sandbox to muck about, but do the real work using last year’s release. 

Code then recode.  Sling the first version and write it again properly.  It will take half the time and effort, and run twice as fast as the first tentative fumblings. (I can see his point, but I'm not sure that I would be brave enough to sling a working program and then rewrite my code.  Nor am I sure I could get it past my manager - "Yes, I'm finished, but now I'm going to rewrite it").

Allow no virtuoso programming - if you can’t understand it, it’s no good.  He mentions elsewhere the joy of documented code.  Oh, so true.  I like to put comments in my code because I haven't a clue what I've done otherwise when I look back at it three months later.  I know some people believe that their code is so clear that they don't need to bother - to them I say "Phooey" and Phil would probably agree.  

And encourage results by rewarding deadlines met. 

Fortunately it doesn’t matter if your project is abandoned or dies a painful unpleasant death.  Here I refer you to Dilbert’s The Joy of Work.  Suppose you design jet engines.  You may only ever have designed one of them, and on its first flight it crashed in flames and destroyed a remote logging town.  What matters is that you can put “Jet Engine Designer” on your CV.

Taking a backup of an SSAS database

Backups?  No problem - just set up a maintenance plan in SQL Server and schedule the backup to run every night while the world is sleeping, or my bit of it at least.  Or in my current job, use Red Gate Backup to do the same thing.  Both do a good job, both are easy to set up.

So what's the problem?

I'm starting to get involved with Microsoft's Business Intelligence tools.  I've blogged before about SSRS, the Reporting Services tool which lets you create and deploy nice-looking reports.  In fact SSRS was one of the first things I got involved with when I started using SQL Server.  But until I started my current job I had little practical experience of SSIS - Integration Services or SSAS - Analysis Services.   I plan to write more about these in future.

The idea behind SSAS (practiced egg-suckers can skip forward past this bit) is that you extract data from your regular transactional database, and create a de-normalised database showing Facts, Dimensions, and Analysis Cubes.  Never mind the details, the point is that you can do very quick analyses of things like sales of widgets by type and colour and salesperson and area and month and whatever else might possibly matter.  Yes, I know you can do all that stuff by writing an SQL query, but SSAS does it a damn sight quicker than writing individual queries.  Honestly, once it's set up, it's just a click and drag thing.  Very impressive, and the software comes free with SQL Server, so a whole lot better than the million dollars that other vendors are likely to charge for their competing products.  It takes a bit of setting up, (well, a lot) but once you have your SSAS database in place, it's very whizzy indeed.

Here's the problem.  SSAS databases are different.  Check out the files it creates - none of that boring .mdf and .ldf stuff.  And you can't take a backup with Red Gate - I rang and asked them, and they may be putting it into a future version, but not yet.  You can't take a backup with your maintenance plan, which is geared up to the bog-standard OLTP databases I'm used to.  No.  Welcome to the wacky world of OLAP!

What you CAN do is back up your SSAS database by hand.  Just right click on it, and save it to the location of your choice.  All you have to do is come into the office every night at midnight and take a backup.  Er, no thanks.  Maybe I'm a bit spoilt by the automation tools I'm used to, but I really don't fancy going back to the days of 24 hour computer operators, even if spinning tape drives have a certain retro appeal to Thunderbirds fans...

Here's how to do it.  Find the SSAS database, right click on it, and up comes the following dialog box.  Notice the backup file is in .abf format?  Analysis Backup Format, I suppose - in any event, it ain't the familiar .bak format that native SQL backup gives you, or the .sqb format of Red Gate.  It's something else... <cue spooky Outer Limits music>.

You can compress the database and encrypt it, at least in version 2008 onwards.  If you click on OK, it takes the backup.

But instead of OK, click on Script.  Suddenly you have a little chunk of XML which will backup your database.

Unfortunately the top secret encryption password shows up in plain text, so frankly there isn't a great deal of point in encrypting something with a password that any hacker can read, in my opinion.  Ho hum, maybe the next version will address this.

Set up a new job under SQL Server Agent.  Tell the dialog box that you want to run a SQL Server Analysis Services Command (don't worry, it's on the pull-down menu, you don't have to type it in) and paste the XML stuff into the box.  Schedule it to run in the middle of the night when no-one's about, and Bob's your uncle.

Here's another little wrinkle:  You probably won't want to overwrite the backup every night, so set up a second step which changes the name to include the date.  Instead of MIS.abf, you save 20120817_MIS.abf.  Next time you run the backup, it'll be 20120818_MIS.abf.  And so on.  Here's the code for doing it:

DECLARE  @Command NVARCHAR(512)

DECLARE  @DatePrefix CHAR(8)

-- convert date to 20121105 format

SELECT @DatePrefix = convert(CHAR,GETDATE(),112)

 --print @DatePrefix

--format command.  Note " " in case filenames contain spaces

SELECT @Command = 'ren "G:\OLAPBackups_Test\IPO MI.abf" '

                              + '"'

                              + @DatePrefix

                              + '_IPO MI.abf"'

                 

--print @Command

EXEC master.dbo.xp_cmdshell @Command

Essentially you create a variable for the date, and put today's date into it.  Create a variable for the command, and construct a Rename command.  Wrap it all up in quotes just in case someone wants a database name with a space in it.  Then you run that command using the xp_cmdshell stored procedure.  There are issues with using xp_cmdshell - see this helpful article.  But it can be useful.

There's a problem with this, of course - sooner or later your backup drive is going to fill up with .abf files.  Probably sooner, as your production SSAS database could easily end up containing millions of de-normalised grains of data.  So set up a third step to clear out the older versions every time the job runs.  ForFiles is a good way of doing this - and I wrote about this in my first ever blog post.  Here's the code:

-- delete abf backup files older than 4 days

exec xp_cmdshell 'forfiles

                        -p G:\OLAPBackups_Test\

                        -m *.abf 

                        -d -4      

                        -c "cmd /C del @FILE" '

So three steps then - the XML to do the backup itself, a bit of code to add a date prefix, and finally a bit of code to sling the oldest copy.  Schedule this to run nightly, set up an alert to email you if something goes wrong, and you are sorted.  

Book Review - Defensive Database Programming by Alex Kuznetsov

What are the assumptions made when code is written?  Kuznetsov gives the example of a string search - what should happen if the string contains wildcard characters like square brackets or percent signs?  You could ban wildcards, or you could write code to handle them.  But doing nothing will give you a wrong result further down the line. 

If you find a bug, look for it throughout your code - it has probably slipped through before.  Fix problems pro-actively - it’s faster and easier than waiting for a problem to occur, then troubleshooting and implementing the same bugfix.

He describes how upgrades can screw up older code - code that works perfectly when using the read_committed isolation level may fail to work if someone turns on the new read-committed-snapshot option.  There’s not much you can do about things that Microsoft haven’t released yet, but when they do happen you have to check their implications and change existing code. 

I’m reading this as an eBook and it is hard to follow the sample code, but it repays a bit of effort.  The key lesson is that it’s better to spend your time planning your code upfront than fixing cockups later.  

No Results Found

Suppose you run a query and it comes up blank?  Now, is that because there are no matching records, or is it because you got something wrong in the code?  Obviously at first the answer is (b) - there is always an error in code.  That’s why you test and test again, and then get someone else to have a look because they see immediately the things that you miss. 

But once you have got the code working properly, and you deploy a report to SSRS, you need a way of telling people that there are genuinely no matching records that satisfy the query’s criteria.  Otherwise they might ring up and tell you that the report isn't working, and disturb your meditation, or at least make your tea grow cold as you deal with their problems.  

Here’s how: add a textbox saying “no matching records” or words to that effect.  This is SSRS 2005, but subsequent versions look much the same:

Now edit the visibility property of that textbox. 

=iif(Len(Sum(Fields!bodid.Value, “MIDAS”))>0,True,False)

In this example, Midas is the name of the dataset, and the Bodid is an integer, a unique reference for each person in the table.  (Note that the field names are case sensitive, at least in 2005.  I haven't checked 2008 - so  bodid <> BodID).  

If records are selected, the length of the sum of the Bodids will be greater than zero, and the textbox will be invisible.  On the other hand, if there are no records, the length is zero and the message in the box appears.

Gone Skiing

It’s a bit chilly in Scheffau; -25C promised tonight, Friday, so the ski shop guys say.  Nice and warm inside though.  My wife dropped my mobile outside the underground car park in the snow, a bit of a shock to the nipple when I found it an hour later and put it back in my pocket.  A good day for wearing a fur hat.  I might even think about wearing some thermal underwear. 

Scheffau is a little village at the edge of Austria’s Skiwelt, claimed to be the largest inter-connected area in the country.  More details in this recent Blog by Brave SkiMom, Kristen Lummis.  Almost all the runs are red or blue - the few blacks are not very hairy, the reds usually have an easier side to them.  

We averaged one run to one hot drink on Saturday. Plus the occasional nip from the hipflask.  I wasn’t too cold considering it was somewhere below -17C - one advantage of wearing a helmet.  But if I see a thin balaclava I might get one to keep my forehead warm. 

The sun has been trying to break through the clouds but I think it might now have given up.  Still there is always Jägertee.  This is literally Hunter’s Tea, principal ingredient hot rum, and very acceptable if you are out hunting on a cold winter day.  The locals have taken to spelling this Jagatee, perhaps as part of the German language’s recent spelling revolution.  Or maybe it’s a brand name?

It’s Sunday and the snow diamonds are sparkling in the sunshine.  -17C at the Osthangbahn lift and very chilly indeed when I tuck down into a schuss.  Maybe I should buy a Hannibal Lecter face mask?  After a day’s skiing my face is tingling... I shall need to slap on loads of after sun tonight. 

Excellent lunch at the Brenneralm above Going.  Tirolergröstl mit Spiegelei - sauté potatoes with diced ham and a fried egg on top.  Schmeckt!  It’s a big, modern, efficient waiter-service place,  but perhaps a little soulless.  Big fire in the middle, panoramic views over the mountains.  I think my favourite is still the Rübezahlalm though.   

Unfortunately I have caught an ear worm today.  It goes Ich kann dich einfach nicht vergessen, Mary Jane, tum ti tum ti tum auf Wiedersehen,  round and round in my head.  They were playing it in the Aualm bar and earworms, being ferociously catchy, get into your head and stay there until displaced by something else. 

Over the mountain to Westendorf Monday; the run down to Brixen is a bit disappointing.  Maybe it seems a bit cramped after the runs higher up - this time last year it was a narrow strip of white  between brown fields, and this year I expected better.  But it was still narrow.  Still the diamonds in the air make everything beautiful.  

Went into the self-service place at the Choralpe but the smell of cigarette smoke was too off-putting; there didn’t seem to be a Nichtraucher area so we walked out and skied down the mountain to the Gassnerwirt.  An old favourite, but they have sadly taken Käsekräuterkräpfel off the menu this year.  Great for alliteration and umlaut fans, these were herby cheesy things, a bit like Greek spinakopitta.  Had Oma’s Suppentopf instead, very nice veg soup.  Oma (granny) gives you a very tasty, generous bowl, and I’m not a big soup fan. 

 

Tuesday, and Holzfällerpfandl in the Stoagrubnhütte. - don’t you just love German spelling?  This is pre-revolutionary stuff, and who can blame the Germans for storming the barricades to overthrow the tyranny of words like that?   The Woodcutter’s Pan was a pork chop with sauté potatoes and fresh veg with garlic sauce, served in an individual frying pan on a wooden platter.

Much warmer today (Wednesday) at -6C.  I have new skis and they are a big improvement - about 10cm longer and when I put them on edge they stay there instead of wobbling about.  I suspect my technique is mostly to blame - if I was a better skier they wouldn’t wobble. 

Lunch at the Gasthof Hochschwendt, which is tucked away at the bottom of the Schmiedlalm; very pleasant and I might even friend it on Facebook as the menu politely requests, although sadly I can't find it on facebook....  Geselchtes mit Sauerkraut und Bratkartoffeln.  They call this Kassler in northern Germany - I think it would be boiled gammon in English.   There is a problem with Sauerkraut unfortunately, delicious as it is, but let’s not go into that. I didn’t know this restaurant or even this run existed - it’s on the way to Going, on the Schmiedlalm, itself off to one side of a run that I’ve always zoomed straight past under the Ellmi’s 6-er chair.  Shame that it is served by one of the few T-bars left in the Skiwelt.  Wide, varied and well-groomed like most of the runs in the Skiwelt, there’s a ski-route from the bottom of the run down to the lifts at Scheffau in the next valley, although I didn’t try going that way. 

Thursday and a mid morning stop at the Bergkaiser motorway services high above Ellmau for a strange and slightly sickly concoction called chiocco intense - made of coffee, chocolate sauce and whipped cream.  Long wait for service as everything is prepared fresh; what can it be like at busy times?  Not bad for such a big place, and very civilised loos with an escalator down.  There’s allegedly a word in German which translates as “the sense of self-loathing and emotional collapse men feel as they walk down the steps into a beer hall toilet”.  That would not apply here.  

We were heading over to Going for another visit to the Brenneralm, but got sidetracked and decided to have lunch at the Hausleiternsstube just above Ellmau.  Another restaurant new to me - it’s a tiny self-service place, obviously family run and very friendly.  I had  Kartoffellauchsuppe mit Würstl.  That’s garlicky potato soup with a couple of sausages floating in it.  Tasty, but I think I prefer my frankfurters in a roll with mustard. 

Over to the Aualm and a quick  Leberkäsesemmel mit Senf on what is nearly the last run of the week.  Liver Cheese is neither liver nor cheese - it’s not unlike Spam (cue chorus of Vikings).  It makes a very tasty snack in a roll with mustard.

And finally down the blue to Scheffau.  I usually take the black (it’s not that steep, used to be marked as a red) and will do that next time.  The blue is a narrow icy road, while the red/black is wide and uncrowded.  At the middle station the red and blue runs come together and cross over.  My advice is stick to the red, although sometimes the poor signposting makes this tricky. 

Bis nächstes Mal!