Sunday, 26 July 2015

So farewell then Hartkaiserbahn.

The Hartkaiserbahn was the funicular railway that took skiers up from Ellmau to the Panoramic restaurant.  And on into the Skiwelt.  Here's a photo which I scrounged from John Barr, the Austria Ski Guide, of the funicular in the 1970s, when his mum and dad went there on their summer holiday. 

And here's one I took in March 2015.  It's just coming down into the bottom station to collect me.  And a few others, of course.

And they have spent the summer of 2015 removing it.  (The ski lift people I mean, not John's mum and dad.)

It's progress, of course.  They are replacing the old funicular, dating from 1970, with a state of the art gondola

Here's a photo from the Skiwelt site of the crane removing the carriage.  

It was a bit of a dinosaur.  Pretty much every time I used it, it was horribly cramped, standing room only, rather less than user friendly.   You had to climb up a set of steps to get to the top of the carriage, clumpitty clump, then fight your way in.  If you were anything less than 1.6m tall you would struggle to stay upright as you probably wouldn't be able to reach the hanging straps.  

A nightmare for the maintenance engineers, parts would have to be made specially to order if anything ever went wrong. 

The track went up the mountain.   The track came down the mountain.  And halfway up it split, the two carriages thundered past each other, and then back they went onto the single track again.  Scary stuff. 

The new one promises to be far more comfortable, quicker, double the capacity, heated seats, hot and cold running wifi, the best thing since sliced bread. 

And yet...

And yet, it's a sad day for the history of skiing. Excuse me while I wipe a little tear away. 


Monday, 29 June 2015


I came across a job the other day - it was called Check Disk Space.

A laudable aim - we should all check disk space every now and then, maybe even constantly if we have one of these magical monitoring systems that do everything.  This was actually an old legacy system, dating back to the Palaeolithic era of SQL Server 2000.

But the job was failing.  It had worked for years, and now it didn't work.  So I thought I should look inside it.  Turns out it was running a stored procedure called usp_check_disk_space or something like that - so far, so consistent.

So I thought I would have a look at this elderly stored procedure, which has been running for years and giving nobody any trouble.  and it seems it was running xp_fixeddrives to get the amount of free space on the drives, then writing it to a table, and then - er - truncating the table.




Thursday, 4 June 2015

The Perfect Job

The agencies email, and ring me, and connect on Linked In.  It's nice to be wanted.  But I'm not actually looking for a new job, just keeping my options open.  I would be a mug to refuse to talk to them - they might just have the perfect job for me... 

And of course one day I might be looking, so I want to keep in with those agency bods.  So I tend to tell them that I'm looking for double my salary and a permanent SQL DBA job in a brewery.  Ha ha!

Until the day that the chap tells me that he is looking for a permanent SQL Server DBA in a Brewery!   Oh, well played sir!

I won't be able to apply - I have too many family commitments to consider relocating.  But if you are interested, track down Richard Iles at Computer Futures.  And mention my name - maybe he'll buy me a beer?

Thursday, 12 February 2015

Modern Jobs - the Process Development Coordinator

Our company Intranet had a job post today.  I have not the faintest idea what a Process Development Coordinator does. 

So I read the Job Description

This is a varied role, working on a premium brand which is still in growth phase. The account is open 7 days per week between the hours of 0800 -2100 and you will be rostered rotationally across those hours, however the majority of your hours will be worked 9-5:30 Monday to Friday. This is a high profile role with lots of client contact and requires the personal qualities of self-motivation, learning orientation, analytical thinking, process orientation and patience.

Candidates must have a demonstrable understanding of training.

The main purpose of this role is:
·           Undertake ongoing root cause analysis to identify opportunities for process improvement
·           To work  with the clients to ensure consistency of process and approach across both sites
·           To provide recommendations around achieving a demonstrable improvement in challenging external quality measures
·           Manage the Training Associate team workflow
·           support operations to achieve high standards to enhance the customer experience and increase productivity
·           Share in the operational workflow and customer facing duties.
Required abilities
·         Committed to the delivery of an exceptional level of customer service
·         Excellent communication skills
·         Ability to pay close attention to detail
·         Natural ability to inspire, motivate and energise others
·         Shows respect to others in a positive manner and builds strong working relationships
·         Strong team player and role model
·         Enthusiastic, positive, resourceful and resilient
·         PC literate

I'm still none the wiser.  Am I getting old?

Tuesday, 10 February 2015

The Missing Sundays Mystery

One of our key customers sends us a datafile every day, and it automatically gets loaded.  Except sometimes - it doesn't...

<Cue theme from "The Twilight Zone">

So I wrote some code to see when data was being loaded.  Sure enough, it works every day of the week, but on the Sunday between Christmas and New Year - it failed to load.  The following Sunday - it worked fine!  But since then, it failed every Sunday.  Only on Sunday.  Then on Monday, a double dose of data gets loaded. 

Now - I didn't tinker with the database - it's an Oracle system, running under a Cron job  under Unix.  So a long way over to the Dark Side.  I don't even have access to the box it runs on - even if I wasn't afraid to touch it, I couldn't tinker. 

And the developers swear blind that they haven't mucked about with the application for months...

I looked in the FTP site - there was a litter of .tmp files, apparently some sort of by-product of the loading, renaming and moving process.  No clues there though, and removing them made no difference. 

Luckily the client was able to send us a log showing us what files were sent and at what date and time.  Helpfully, he highlighted the missing Sundays in yellow.  and all became clear.  The ones that worked were sent to us at various times ranging from about 0500 to 0700.  The ones that failed were sent to us at various times between 0700 and 0800. 
Guess when the Cron job runs?

The job to send us the data is automated - but runs when other things finish, hence the variety of times.  And Sundays?  "That's the day we bounce our servers..."

Friday, 24 January 2014

SSMA - SQL Server Migration Assistant

At a DBA reunion dinner just before Christmas I was talking to a friend and mentioned that I was using SSMA to migrate from Oracle to SQL Server 2012.  "Poor you," he said "That program's a real dog."

I was surprised at his attitude.  I had by that time migrated a couple of databases without a great deal of difficulty and since Christmas I have migrated about 20.  Small things, admittedly, nothing larger than a dozen tables and a couple of million rows of data,  but still, "dog" seemed unfair.  Turned out he had last used the Migration Assistant that came with SQL Server 2000 - and like so much else, Microsoft has been doing some work on it.

Somewhere in Redmond, WA - early this century...
 "OK, guys, we wanna make it easy for people to move to us from Oracle - any ideas?"
 "Er, Mr Gates, how about a migration assistant that actually works?"
"Smart thinking Wilbur, here's a few million dollars, go away and spend it wisely".

 Here's what Wilbur came up with:

I log onto my development server and click on the icon you see here.  The SQL Server Migration Assistant for Oracle opens.  (Incidentally, I believe that there are at least four SSMAs - one for Access, this one for Oracle, one for Sybase and one for MySQL.  Plus versions.)  Here's the team blog.

 Up comes the splash screen:

and then the home screen:

Two menu bars at the top.
Top left quadrant is the Oracle Metadata Explorer.
Bottom Left Quadrant is the SQL Server Metadata Explorer. 
The other two quadrants are for showing you information, and they aren't terribly important - I'll touch on them in passing. 

Getting Started

Click on File, then on New Project.  Up pops the New Project Dialog box - enter a name e.g. Oracle2SQL - Databasename, and click on OK.  If you have lots of different databases to convert, it's worth making a note of which project is which - maybe start an outlook task and put the project name in the Title. 

Connect to Oracle

      Click on Connect to Oracle on the menu at the top of the screen – it will come up and ask for the connection details, and especially the password.  It's a good idea to get the Oracle DBAs to make a copy for you, so that you are never extracting data from the live system.  

      Enter the password.  After you click on Connect, check the bottom of the screen for progress messages (it takes a couple of minutes - loading this, loading that).  Wait for the magic word "Done".

      The top right quadrant now fills up with a list of the various schemas - the Oracle Source Schema, and the SQL Server Target Schema.  There is also a Type Mapping tab and a Properties tab.  You can make changes here if you want to.  I haven't done - generally it does a pretty good job without me tinkering.  And while I'm not totally certain about some of its decisions, I prefer to change them in SQL Server, where I can see the context.  More on this later. 

         Select the Oracle Schema (equivalent of the SQL database) to be converted:


Connect to SQL Server

        Click on the menu option that says "Connect to SQL Server".  

If it’s a new database, enter the name and SSMA will create it.   Do not change the name - Do NOT change from CAPS to Title Case, do not even fix a typo.  Be especially careful if you are doing one job after another – it assumes that you want to do the previous one again.  If you miss this and accept the existing name, you are probably better off abandoning this one and starting it again, being more careful next time. 

      As with Connecting to Oracle, this takes a little while - not as long-winded as Oracle though.  Look out for the word "Done".  When it appears, select the destination database in the SQL Server Metadata Explorer box.  The bottom right hand quadrant fills up with SQL Server information.  Honestly, it's so dull, there's no point in looking at it - I certainly don't intend to show you a picture.

In the Oracle metadata explorer box, highlight the schema to be converted - the easiest way is to right click on it - then Click on Convert Schema.  (For some reason Microsoft show Ctrl R as a shortcut, even though they underline the n of Convert.  Looks a bit sloppy to me, but I’ve never bothered with these shortcuts as in this case it’s easiest to do it with the mouse). 

Check for Warning messages in the dialog box at the bottom of the screen – it may be helpful to copy them to a Notepad box, or an Outlook task, if you created one earlier.

Right click on the Database name in SQL Server metadata explorer and select Synchronise with the Database.  It spins its wheels for a few moments and then pops up a box that is entitled "Synchronise with the Database". 

You are going to find this difficult to believe, but what you do now is press OK.  And off it jolly well goes, whistling a quiet little tune to itself.  It synchs the Oracle structure with the SQL Structure.  No data as yet, but we are getting there.  

      Leave the SSMA program for a moment, go into SQL Server, and take a full backup of the database. You could do this later if you prefer.  Select each table in turn, Script as Drop & Create to New Window,    
      Review the possible errors noted above in the Outlook task.  

      A Few Words about Data Types

      The data types in Oracle do not exactly match those in T-SQL, but the defaults do the job pretty well.  Despite this, I can fairly safely guarantee  that the commonest warning message you see will be:
Warnings: O2SS0356: Conversion from NUMBER datatype can cause data loss.
      This takes the Oracle NUMBER datatype and guesses that it’s a SQL Server FLOAT.  And of course, it depends.  Consider whether float should be INT e.g. for an ID, or FLOAT e.g. for a height. On most of the databases I’ve converted, INT seems to be a better choice.  

      Strings get turned to Varchar – fair enough – you might want to think about Nvarchar if you have any Unicode records.   One exception - varchar(1) should be char(1).  (What earthly use is a varchar(1), eh?)

      Then Execute to change the table.            
Save Script in Data Management Program documentation e.g. Q:\Data Management Programme\Oracle2SQL Migration\SQL\Be_The_Key

      Run exec sp_helpdb [BE_THE_KEY_ENG_NIR]  and note the logical and physical file names in your Outlook Task.  DO NOT CHANGE THE NAMES AT THIS STAGE!   Even if they are horribly wrong.  Do that and SSMA will decline to migrate the data, and you have to start all over again. 
      See notes on untangling the database.

      Migrating the Data 

      OK, back to SSMA now.  In Oracle Metadata Explorer, right click on the Schema and select Migrate Data.  Off it goes - you can watch the numbers clicking upwards if you like.  Be patient - it isn't hugely quick, especially of you have something complicated,  But generally it will think for a while and then come up with a Finished message:

      Look out for that success rate column  - it should be straight 100%.  If it isn't, you will need to do something about it, but usually it comes out fine.  Save the project, save the schema, and Exit.  Congratulations - you have just migrated an Oracle database to SQL Server.