Wednesday, 28 February 2007

A really slick backup idea!

And it wasn't even mine.....

Its another not very exciting post about backup, I know its not the most fun subject even for fellow nerds but its a fact that backup is very important (see the last but 1 post). One issue we have as a company with distributed sites and therefore computers is trying to make sure we get copies of the data from harassed managers. Its no use giving them a cake of Cd's and a printout which requires them to do complicated computing tasks, it simply doesn't work, so you have to think of something really slick and this is what our main mac man Nigel has done.

At the same time that I was playing with rsync to backup my Manchester installation over the VPN Nigel was using the same program, which comes as standard on the Mac, to do some simple backup tasks on his computer. Having both recently installed a nice RSS reader called Vienna we have access to a useful little blog called Mac OSX Hints which pretty much does what it says on the tin. One hint which Nigel picked up on was a rather nifty little application called "Do Something When" which very simply runs a script when a USB Flash drive is plugged into a Mac. Using the power of his brain Nigel that put these 2 ideas together resulting in "A really slick backup idea", luckily Nigel doesn't blog so I get to write all about it and reflect in the glory of the idea without doing a thing - sweet :o)

So after a small amount of programming, setting up you Flash drive and rsync script, every time you plug the Flash drive into the Mac it automatically does an incremental backup of your predefined files! This will allow us to present our managers with a backup solution which is so slick they can't fail to use it.....

The rest of this week has been programming for me, a mix of finishing our new job applicant database and moving our exisitng data into the new database, from Microsoft SQL server to MySQL which has been a bit painful. A thing to watch out for if you are trying to use DTS to move data between these databases is that text data will not copy across using an ODBC connection and I couldn't tell you why. Varchar data will move and I have checked its not a collation issue, it just won't go, answers on a postcard please. The solution I used in the end was moving data to excel spreadsheets and then pushing the data into MySQL from there, not very elegant really.

Monday, 26 February 2007

Improving the 'Manchester connection'

Sounds a bit like a Tom Clancy novel doesn't it - "The Manchester Connection" - in reality of course it's nothing very classy (if Tom Clancy novels can be described as classy) it is a slot in a rack in Manchester which is currently occupied by one of our little VPN firewalls.

When we first embarked upon extending the VPN to Manchester it was a bit of an experiment and as such we didn't necessarily want to go mad buying expensive hardware for the first phase. However now that we have approaching 20 connections to this part of the network and are beginning to use the applications and data store there more and more, we are in need of a bit more horse power.

One thing we have noticed is that when the total number of connections on our current Draytek 2900 exceeds the recommended 16, although it will quite happily chug along for days without a problem every now and again if a connection is dropped it can fail to automatically pick up again. The only solution is to manually drop the connection and then everyone is happy again. Of course when you are relying on this connection to serve up applications it is not acceptable to have this occasional glitch and the resulting lockout so its time for an upgrade. What we were intending to use was a Draytek 330V as these units have capacity for 200 simultaneous connections and a dedicated VPN processor and was the choice for our main VPN hub, however it turns out that Draytek have a new toy out - the 2950 Note especially the cutting edge case design which is such a hall mark of any Drayteck product, the designers obviously frightened themselves with the racy blue case of the 330V and retreated to their comfort zones with the latest offering! Its a good job they work so well.

Click here for a full spec and here for a link to their control panel demonstration. We have been given a delivery date for Friday so if its on time I should be able to relate the tale of the setup on the Friday afternoon post :o)

Other news is that on the Google calendaring and sync front so far the Mac system - spanning sync - seems to be fine. However the PC solution syncmycal is having some minor emotional problems, so far the most impressive thing is the prompt service from their technical support department. Nuff said.

Friday, 23 February 2007

Mounting a network drive and dabbling in cron jobs

Now before you all think I have gone crazy and started a sideline in email scams I must explain some background before things will become clear.

Our new all singing and dancing application we are building in Manchester is starting to become useful and inevitably when something becomes useful people start actually using it to do important work. Also when people start using software they trust their friendly IT manager to look after all their useful information in a responsible manner so that if anything bad happened their precious information will be preserved. Imagine if you will a collection of harvest mice with a big stash of blackberries, if the stash of berries is squished the IT manager has an opportunity to become a hero by unsquishing them or alternatively slightly fired :o)

(The operations Director was very angry when she found out all the data was lost)

So the solution for guarding our 'blackberries' is to duplicate them and put them in several places at the same time, we have a clever piece of software in head office which does this - when it can be bothered (thank you to the creators of retrospect). However our new system is being created on a Linux server and as such has no access to our main backup software so we have had to create our own. Its pretty simple really, every night copy our 'pile of blackberries' from this place to this other place, as I have bored you already with the story of replicating our database we are simply concerned here with files. The thing is that there would be no point in copying our files from one part of the Linux server to another so it needs to be sent to head office, this means going from Linux to windows and this is where the fun is.

And so to a brief instruction on mounting a windows network share:
1. Stretch - its important.
2. Create a new windows account on your domain.
3. Create a folder on your windows server and allow access to it for your new user
4. Try connecting to it using the following command:

mount.cifs //servername.domain/share /mnt/ -o user=user,password=password,domain=domain

Don't try smb, mount, mount.smb or any of that because it doesn't work, I have no idea why - its probably very simple but 2 hours of banging my head against it could not get it going!

If the preceding command works edit your /etc/fstab file and simply copy and paste the command into it. That way every time you restart your Linux machine it will automatically make your windows share available to you in /mnt/ magic!

So next to the cron job:
For those of you who do not know, cron is a simple program which runs another program at a certain time on a certain day so you don't have to remember to. A cron job is a preconfigured task which cron will perform at a specified time and date, told you I could explain.

In our scenario we have a very simple command to be run cp -r "/home/store" "/mnt/" so the first step is to simply run it at the command line and see if it works. If it does you copy and paste the line into the /etc/crontab file with a preceding '0 1 * * *' which it turns out translates 'to please run this every night at 1am' simple really.

I hope you find this ramble as useful as I would have done if I had found it 2 hours ago :o)

The Ultimate Calendaring System?

First job this morning was to spec out and test the idea outlined yesterday and I have even made a pretty picture, I might print it out and put it on the fridge at home :o)

I have had Spanning Sync on the Mac for a couple of weeks now and it seems fine, ditto for sync mycal on the PC and at £12 per license its very reasonable but they have been linked into separate calendars. This morning I have linked up all of these systems via my Google calendar and so far they have not started fighting. I think the key must be to accept that the Google calendar has to wear the trousers and although its 2 way sync set both of the client tools to defer to Google if there is a dispute. The next step in testing this system will be to actually set up one of the new Google domain accounts, I will keep you in the loop :o)

BTW in writing this blog I thought I might put in a little picture to illustrate the fact that this Calendaring strategy might be 'all things to all men', when I searched for this phrase another quirky burp of Google images threw up a picture of a squaddie with an enormous gun(see right). Not quite what I had in mind but he definitely does seem to have the equivalent of my Calendaring strategy tucked in his sweaty pit doesn't he :o)

Thursday, 22 February 2007

Groupware Eaton Mess?

For those of you who don't know it Eaton mess is a kind of pudding, I know this because even though I am a techie I work for a pub company and you pick up nuggets like that. Eaton mess is not just any kind of pudding though, its the Rio carnival of puddings, the fact is it shouldn't really work, its just a pile of stuff that just happens to be delicious.

I was reading the write ups this morning about Google's paid for 'sort of office' suite and was quite impressed but it has taken me all day to actually digest the possible opportunity which this service might be offering. I had been looking before at using just the calendar aspect but the App suite brings a few more things to the table for a quite reasonable £25 per year. As a suite of tools the offering is quite basic but there is nothing wrong with that, and the simple fact that you have to be online to do anything is quite limiting. For 360 days of the year it would not be a problem but I can just imaging the other 5 could be very frustrating indeed. That said there are 3 aspects of the suite I really like, namely the Calendars, Gmail and the Personalised Home Page the question is how to make best use of them and this is where it turns suddenly into Eaton Mess. So could a useful Groupware system be shackled together using Google Apps and some supporting software to create a masterpiece or just a big pile of stuff that doesn't quite gel.

Our problem as described before is the requirement to support Mac's and PCs and a variety of software applications with 2 way synchronisation for all applications. Or to put it another way is it feasible to replace Exchange with Google?

The requirement:
Shared Calendars, Personal Contacts, Web Mail, POP Mail and Mobile Access

Outlook 2007, Entourage, Ical, Apple Mail, Pocket Windows 5, Apple Address Book

I have alluded before to a couple of pieces of sync software that look promising, syncmycal and spanningsync, these are both coming along and although I am nervous about 3rd party plugins getting in the mix if, just if, they did get finished and worked well they could make all the difference. I will put together a diagram to illustrate but I would say that off the top of my head all that is missing is a way of syncing contacts with Gmail.

One thing which would be great about this whole house of cards would be that looking forward we could actually think about binning outlook and any other email/calendar clients thereby making life very simple. I love the idea of opening a web browser and having my mail and calendar immediately available to me, it was in fact a part of the initial idea for our central management system but it didn't seem worth while reinventing the wheel so to speak.

Monday, 19 February 2007

Why I like SQL and a matter of AS Dysfunction

One of the little projects I an engaged in at the moment is quite a nice little program to store facts, its the latest chapter in the drive to put information traditionally stored in a managers spreadsheet horde onto a central system which anybody can access.

The program is very simple, a set of pubs and a set of facts but one aspect which was quite intriguing was how to efficiently add an address for an entry without ending up with a huge database of contact details which would require constant maintenance, we have enough of those already! In due course a solution presented itself, why not simply use one of those other databases and what better than the accounts system because frankly businesses like to get paid and if there is one set of contact details which they make sure to get updated if anything changes its accounts. And this is why I like SQL, the new 'fact' system is in MySQL the accounts system in Microsoft SQL but thanks to the gift of SQL and PHP all of this information can be glued together in a really elegant little system.

The next step with this program is to then load this contact information into a VCard, we may be writing lots of our own software but you have to have an eye on standards and for contact information the VCard is it. Thankfully a nice group of people have written a PHP addon for creating VCards via the PEAR system and if you know what I am talking about and fancy a laugh pop into the documentation section for the VCard PEAR module :o) I'll tell you how I get on with this part tomorrow.

One other little note for today was a confusing feature of the asfunction in Flash, if you are ever programming in Flash and cannot get a function to work using asfunction check the target level, it does not reference the root level but whichever level the text area is in which is calling it. Bet you'll all sleep better now :o)

Friday, 16 February 2007

The rack is open and Rob earns 2 cents

When I first started musing upon life and aspects of IT on my little blog it was really a test, sorry to break the news but there you are. The theory was that the blog would be a valuable way of getting interesting and topical content onto our company website, explaining to anyone who cared what we do in the IT department and dispensing some useful nuggets of information to anyone having trouble with things we find solutions to. Its what makes the web so interesting, whatever mess you have got into you can almost guarantee someone has been there before and was good enough to write about it so other people could dig themselves out of the mire slightly quicker.

The question we had to answer was, is it possible to blog on a regular basis, say something interesting and informative and would it be worth linking into the site. So here we are 2 months later, still posting, still enjoying it and very pleased with the amount of traffic on my blog, at present only about 20 a day but its encouraging to know people are finding it and we have decided to link it onto my profile on the company website :o)

Having extolled the virtues of regular blogging as a tonic for the soul our revered communications director has decided to join in and I would like to link you at this point to his blog - a view from the bin. As our wine aficionado this of course relates to his tales from the wine bin in the same way that mine relates to the server rack, as opposed to the instrument of medieval torture... although...

And moving onto our other company blog the big news from compadre Rob is that he has sold out and plugged Google AdSense into his site, obviously a worth exercise as in the first week it has earned him a truly mighty 2 cents, or 1p after tax.

This of course now leaves Rob in a terrible quandary as he will
have to choose between a black jack or a fruit salad.

Thursday, 15 February 2007

Shared calendars - how hard can it be?

Not been very much of note going on this week, mostly moving some of my little applications over to our Mancunian installation. I have now moved over our R&R invoicing system, the SMS system and our Pub facts, these are all little Flash applications now running on AMF and served up over the VPN.

One big movement this afternoon has been with our company calendars, for the last few days we have been evaluating Outlook 2007 to see where its new Ical support is worth us spending the money for an upgrade on our Head Office PC's. Compadre Rob has been less than impressed - click here for his frank evaluation. Officially we have a support contract but according to Microsoft our Small Business Server license does not entitle us to an outlook upgrade until exchange 2007 is out which conveniently might be after the contract runs out. If anyone ever offers you a Microsoft support contract tell them to pound it, if they brought the software out on time it would be fine but they don't and its more trouble than its worth.

Enough of that or I'll get all angry.

So we have been using exchange for about 18 months now and it is less than impressive when you have a mixed network as support on the Mac's is poor so we are desperately trying to extract ourselves. Moving the email back to POP3 is easy but we still need to be able to share calendars. One thing we have been looking at quite seriously is using Google calendars but in order to have your calendar offline you need 3rd party software in the shape of Spanning Sync on the Mac or Syncmycal on the PC, both of which seem to work but we have tried so many other pieces of calendar syncing software in the past that I would be very nervous of recommending it.

So given the new Ical support in Outlook we are looking at the simple option of Webdav and Ical/Outlook. I have dusted off my old Pentium 3 based server called Chubba and given it an install of fedora core 5, setting up webdav on Apache is easy thanks to this rather niffty tutorial. The only thing we need to sort out now is whether Ical can cope with 12 calendars, it used to cope with about 10 but ironically it was an upgrade to Tiger which caused major problems. Seeing as all our Mac's are now core 2 I have high hopes that they will have sufficient cojones to keep everything synced up. If they struggle it will have to be Google calendars and plug in hell! No offence Bill but exchange is going and that's that.

Still loving the Mac by the way, must have quicksilver, Neo office and synegy if you have another machine though - again I will point you to the bug for a good post on stuff you need on your Mac.

Monday, 12 February 2007

Return of the MAC

Just so that my newly expanded international audience doesn't get too worried that I might inflict some off key UK R&B schmoltz on them the MAC I am referring to is not Mark Morrison but in fact a shiny new MAC Book. Although in fact its not shiny because its a rather nifty Matt black, it's just new.

Gotta love Google Images - turns out this guy is called Mark Morrison too :o)

(Can I just reiterate this is not a picture of me - he's called Mark Morrison and I don't know him, its funny and if you don't know why Google Mark Morrison the singer)

Firstly I must say hello to the international audience I have alluded to, thanks to links from my com padre Rob at bugeyedchron, and his rather popular brother Thom, this humble little blog has now been visited by people in all kinds of places including Australia, Singapore, Mexico, Norway and China. I hope you have been informed/entertained and please be assured each new blob on my Google analytics Geo Map makes me very happy :o)

So to the Mac...
Normally I am a Windows person it must be said, I don't like Microsoft particularly but I am a developer, I write programs and web apps, mostly cross platform actually, but I like an easy life, I like a choice of development tools and I like things I can tinker with. I suppose you could say I like PC's but that does not mean Windows. In fact I would love to use Linux more often but it just not quite there yet on the desktop.

I have had a hankering for a Core 2 Duo machine for some time as my Dell laptop is getting a bit long in the tooth but I have a feeling that a Vista based laptop could be a bit lacking in the trouser department as XP does not run that quickly and newer laptops do not have that much more horse power. I almost went for a desktop but the opportunity to swap out my Ibook presented itself and I have been really very impressed by the new Mac books so I thought - "twist my arm I'll have one". I have only had it a day but it has definitely impressed far more than the original Ibook did, here are a few Plus points:

Seems nice and quick, a good step up from what was already a quick machine
Much better value for money, Ibook was expensive compared to the PC competition, this seems good value
love the inbuilt Isight, great quality, very convenient for Skype
Screen is the best I have seen on a laptop
Still like the CD drive, its a great piece of design
The new magnetic power connector is brilliant, probably the most vulnerable part of a laptop is instantly rendered much more robust,
Lovely tactile keyboard.

What more could you ask for in a laptop? Frankly not much, I suppose you would think me ungrateful if I said outlook and a maximise button :o) , throw in Half life and I would never go back!

Seriously though, within 2 hours I had FireFox, Thunder bird, Open Office and Skype on it and I began to feel quite at home, its almost unfortunate that my Flash Pro license is PC only because I would move my license if I had the option.

The only dark cloud in my otherwise seamless experience was that MySQL Maestro does not have a Mac version so I am having to use Cocoa, its OK but just the sort of compromise which gets on my nerves. I am keeping an eye on Parallels and crossover just in case they solve this annoying aspect of running a mac.

Will keep you posted.

Thursday, 8 February 2007

Hunting and killing orphaned processes

I shall await the comments from emotional teenagers with interest, I have just spent the last 30 minutes wondering why I could not restore a SQL server database which had had some problems. It turned out the culprit was pesky orphaned processes, so in true Elma Fudd style I went hunting them and brutally slaughtered any I found. (If it wasn't for the fact that Warner Bros would probably sue my arse off I would have put a jaunty picture of Elma in at this point but you will just have to imagine over to the right there is Elma complete with deer stalker and double barrelled shotgun)

So you want to do something on SQL and its telling you you cannot because the database is in use, first ring round the office and make doubly sure no-one is on it. Next use query profiler to examine current connections but don't expect to see every process in the list, it will allow you to see active connections though and you might find someone on there who didn't realise it :o)

Take a deep breath and open query analyser, the command to see a complete list of processes is sp_who so type

exec sp_who

Then if you can see your pesky orphaned connection you use its spid to kill it, so type:

kill spid
exec sp_who

and they will magically dissappear from the list :o)

Everyone is happy and you can get on with the day, maybe after a congratulatory cuppa and biscuit.

Sunday, 4 February 2007

MySQL Stored Proceedures - You just need a maestro

And I am not talking about an 80's talking car, seriously Austin (went bust surprise surprise), night riders car might have talked to him but that didn't make it right.

No the Maestro I am talking about is the rather nifty new MySQL administration tool which makes creating stored procedures so much easier. There has been a lot of discussion on the web about whether MySQL has what it takes to run enterprise level databases for real world companies and despite the fact that there are lots of examples of huge companies using it I feel that the feeling of nervousness stems from the fact that although the database is rock solid the management apps are definitely not. Lots of good progress has been made of late particularly by the people at MySQL but most of their GUI apps are recently out of alpha and I do not like the fragmented approach they have adopted, ie. mange your permissions and backup with this app, analyse your queries with this one and extract data with this one. I end up having 4 applications open to do a days work. Well not any more, is the place to get the only MySQL management tool you will ever need, and all for only $149!

Cheesy plug over - the reason I came across this software was because I wanted to use the new MySQL stored procedures and to see how they compare to Microsoft's offering, which I have been recently introduced to as you know (because you read this every day don't you). I turns out that Stored procedures have only recently been introduced into MySQL in version 5.1 and documentation and examples of their use are a little sparse at present. This particular piece of software however has its own really good stored procedure manager which is why i downloaded it and found the rest of the tools it offers as a very nice surprise.

I will go into how to actually write a stored procedure tomorrow, when I actually write a useful one, but to anyone who is just looking around to see how they can get into this there are a couple of things you have to do first it turns out. Unless you have just installed the latest build of Fedora or some other main stream Linux build you will have to upgrade both PHP and MySQL (assuming you want to use PHP of course). Stored procedures are supported in MySQL 5.1 but you cannot actually get at your stored procedures from PHP without the mysqli addon. As we use Centos as our weapon of choice on the Linux servers this means having a whole scale muck out. It turns out the safest way to upgrade either of these programs is to backup - remove - and reinstall which frankly appeals not at all (he he). I have tried it locally here but tomorrow I will be reorganising these programs, when its all over I will write some proper stored procedures and tell you all about it :o)


Friday, 2 February 2007

Freeing the beast - foreign keys and other nerdy guff

Been on a course - brushing up on my SQL with the rest of the department and finding out some very interesting little tit bits which will speed things up at head office no end. I had always considered for example that stored procedures were really an organisational tool designed to reduce duplication of queries on the client side. Interestingly it turns out that they carry a good improvement in performance because the query is essentially precompiled and all the execution planning and checking is already done. Could make for a busy Monday :o)

Having got home I decided to look at some of the details of the MySQL databases which are less well used than the Microsoft SQL databases, and definitely less critical, but still in need of efficient design. While reorganising things a little I found out a couple of slightly less well documented issues with creating foreign keys on a MySQL database which is already in use and therefore carrying a little baggage shall we say.

A couple of things seem to cause problems and errors 105 and 121 seem to crop up a lot
1. ensure that columns to be associated are of exactly the same type, for example a column of type INT will not be forced into a liaison with a column of type INT(10) unsigned.
2. Ensure that all columns will match exactly, for example if one column has a NULL entry it is not going to conform to the relationship and it will ruin your evening.

That's just a couple of things, I'll keep jotting about whatever else drops out of the woodwork. Next week I will post the first installment of the main event - taking our main MS SQL databases to demon tweaks - exciting stuff :o)

A view from the rack is the personal blog of an IT manager who works for a pub company - hence beer