What Happens if you Get Hit by a Truck?

Accountants, bless them, sometimes have no clue about backups and data integrity. This is why these things should be left to the IT Department to take care of. Or should they?

I am going to go with mixed mode on this one. (The SQL guys will get that one.) The Financial Systems Manager is responsible for the backups but the Networks Team performs them. BUT who checks and tests them? In our environment we do. We regularly restore the Financial Databases to alternate locations and connect to them using our test installation. Why is this done? A very hard lesson was learned many years ago.

I had just started to work as a permanent employee for the company that I was contracted to. The Networks Department were quite proud of the backup strategy and assured us that they were backing up our database every evening. So we just continued with our day to day lives and never thought of it again. Then the unthinkable happened. During a re-index of certain tables the database was corrupted. (Ingres 1.2 not SQL). No problem we'll just restore to the previous nights backup and all will be well.

This is when the fabric of the know universe started to unravel. The backup operator had missed a warning to say that one of the files were locked and could not be backed up. Without going into the detail of how Ingres was being backed up, needless to say, we could not restore the database. So we started to go back in time through all the backups to find the last "good" one. It was 13 days prior! What ensued makes Stephen Hawkins and his space theories look like child's play. 48 hours later, sleep deprived and looking like Arthur Philip Dent after watching the destruction of earth, all was recovered and all systems were in sync.

We learned a lot from this hard lesson. First and foremost do not just take the word of your IT department that things are being backed up. Test them and test them regularly. The second item of concern that came to the fore was how much information about the systems was retained in my head. We had good documentation but more was needed. From this exercise we have developed one of the most comprehensive disaster recovery plans that I have seen.

I have lever arch files, stored in 2 locations, that contains amongst other things the following:

• Hardware setup
• Operating System version and setup
• Folder trees, permissions and shares
• Drive letters and space requirements
• User groups and users
• SQL Setup
• Printer setup with drivers (some systems are case sensitive, got caught on this one)

This file also has the disks stored in a sleeve with all the registry keys. The installation guide has screen shots and important notes at every step. It has been tested by a randomly picked person who had no prior knowledge of our system.

There are 2 types of IT people, those who have lost data, and those that will lose data. I cannot stress enough the importance of proper backup and recovery procedures.

So you may ask what does this have to do with the title of my blog? Because in 2000 while training for an ultra marathon early one morning I was hit by a truck. Luckily I survived but what if I had not?

- Paul Steynberg

The Financial System Manager's Toolbox (Part I)

A common question. What tools should a Financial Systems Manager have in his/her toolbox in order to be effective? I started to add up the bits and pieces that my team and I surround ourselves with and it turns out to be quite a lot. I suppose the list depends on what responsibilities the team is tasked with in each organisation but here we do pretty much everything from project management, help desk support, training, operational checks, backups, server updates, installs etc. So what do we use?

Excel - Without a doubt the corner stone of every accountants existence. Take this away and you risk imploding the known universe.

Word - Pretty obvious but essential for documenting. We make sure that every system is fully documented with backup and recovery plans. Hand in hand with documentation comes screen shots and this is where a tool such as Snag-It comes into play. A must have in order to make your documentation "idiot" proof. Once you have finished your document it is a good idea to save it as a pdf so that it becomes difficult for others to tamper with it. Microsoft has an add-in for the Office Suite which saves to pdf or xps. You can find it here.

Text Editor - I use EditPad. Especially useful when you receive and or send files between PC based technology and mainframes etc. It allows you to check record lengths, convert between Windows, UNIX and Mac, change cases and a whole lot more. Very useful tool.

SQL Editor - The current version of SQL Server Management Studio does not provide intellisense or auto complete. It also does not allow you to save your scripts in an orderly searchable fashion. I use Speed SQL IDE from Quest Software (which is no longer supported) as it provides all this functionality required and saves versions of your scripts in a central repository which can be backed up daily. They have replaced it with Toad, which I do not like. You can also use a tool called SQL Prompt from Red-gate. It works within SQL Server Management Studio and Visual Studio.

Backups - All our Financial System have been consolidated to our standard database, SQL Server. Sometime back I started to run out of disk space and also window time for backups. We found a product, also from Quest Software, called LiteSpeed. Now this is a product that saved my bacon. Our backups were taking 1,5 hours and size on disk of 80Gb's for our main ERP system. After installing LiteSpeed the backup times reduced to less than 30 minutes and the files size only 10Gb's. It compresses on the fly as it backs up. An absolute winner, saves you time and cost of additional disk space.

Text File Searching - A number of older style ERP systems produce files for every process within the business. These files are mainly text based and for example can be log files and spool files. Now in the past if things did not go according to plan these files would contain error messages or warnings. If the person in charge of the system was not aware of any problems they may go unchecked. These files were also produced during interfaces between systems and the only way to make sure that they were successful was to troll through the files and make sure that they were OK. A massive task if you have as many orgs as we do with as many interfaces running daily. I could not find anything to do this job automatically back in 2000 so speced and had my own application written by a Delphi programmer. It still works today and saves my guys hours. One sets up a search location, a file mask for the files being searched, what you are looking for or not looking for, a location for the file to be copied or moved to, an SMS number and message if you need to SMS someone etc. It was written as a service or to be interactive. Love it!

All the above tools combined assist myself and my team in maintaining our Financial Systems environment to a very high standard. Maybe one or two of them are just what you are looking for.

In Part II I will touch on more specific tools such as MS Integration Services, Analysis Services and Reporting Services.

- Paul Steynberg