Why is Replication not Working?

First of all, what is Replication about?  You take a copy of a database - all of it, part of it, whatever you need.

• Maybe you want to copy data to a redundant server so that it's ready to take over if the original one fails.  Today you might use mirroring or AlwaysOn, but before these technologies came along you might use Replication
• Maybe the master database contains a price list - you can send an up to date price list to every branch every morning at 0700 ready for the working day.  

Here's a one page intro that I like.  

So why is it not working?  No doubt there are hundreds of reasons why replication isn't working.  Here's one more to add to the list.  I set up what I thought was a straightforward Publication, using the wizard that Microsoft kindly provide.

I won't go into detail - just work through the wizard, tell it what table you want to publish, etc, etc and schedule it to run at an appropriate time - every hour, let's say - that's the default, fine by me.

So the wizard creates a publication - it takes data from a database and makes it available for other databases, probably on other servers, to read.  Sorry, to subscribe to.  

OK, so much for Publication, now for the Subscription.  Again, follow the wizard:

Start by telling it the name of the Publication you just created

 Connect to the subscription server i,e, the destination:

Schedule the job, and set it off.  Easy peasy.  Actually, there's a bit more work than I have shown here, but essentially you follow the wizard and it sets up two SQL Agent jobs - one to publish the data from Location A, and one to subscribe to that data and copy it to Location B.

The problem that cropped up was that I scheduled the jobs using the wizard to run every hour - as it assumes by default.  Then I changed one item of data in the original, and sat back to watch it replicate to the destination.

It didn't.

There's a Replication Monitor (just open up Replication and right click on the Publication or Subscription) and it said it ran fine - no errors, nothing.  But the changed data did not appear in the destination.

It took me a little while to work it out, and then a bit longer to stop kicking myself. The wizard assumes that the jobs will run every hour on the hour.  So by default the Publication and the Subscription both start at the same time i.e. the subscription starts before the publication has finished.

Therefore a change to the original Article gets published okay, but it won't get across to the Subscriber for an hour.

Solution - edit the schedule of the subscriber job to run 2 minutes after the hour. (Publication might only take a few seconds to run, depending of course on how much you are publishing)

Thus:

18:17 Change is made to the original data

19:00 Article is published

19:02 Subscriber receives article

Had I been a bit more patient, it would have worked out fine, an hour later.  But I wanted to go home!

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.