Admin: Sizing your transaction log

You will often hear DBA folk telling you how you need to size your transaction log appropriately and, like data files, avoid shrinking it. Why is this?

Well, lets try using a finance theme with a Credit Card analogy which records all your purchases but it also has a limit. When you fill your credit card and reach your limit you either need to pay off the balance or extend the limit by asking/begging your issuer for a bigger limit. Its possible of course (and will probably be successful) but will take up some of your time and may involve the provider asking some awkward questions. Of course, there may be times when you need to extend your limit, perhaps when you're moving house but there is a cost to it and its something you want to avoid doing too frequently. Hopefully when you set up your credit card, you do so with an appropriate limit based upon knowledge of your spending habits.

All of this is the same with a transaction log. It too has a limit which is its size and when it gets full it will either stop taking transactions or ask the operating system for more space (if set to Autogrow). Think of auto grow as akin to asking your credit card provider for that extension which will most probably granted but not without effort, here the time it takes to provision the file growth. While the file is growing, transactions will be queued up meaning your application can't process its requests and things will start to get ugly. Alternatively, rather than extend the limit of your log, you can perform a transaction log backup and "clear the balance" or if your database is in SIMPLE recovery mode, this behaviour is done on checkpoint.

Your ideal scenario is to have auto grow set on your log but with a maximum size set on the file (you don't want this credit card brining down the whole of the bank!!) and ideally it will be sized at a level which won't require too many file growth operations. You want to avoid shrinking the physical file as you don't want to repeatedly go through the "limit extension" process as its just unecessary cost. If you know your app needs 10GB, then just set it at that.

SSRS: Using SSIS as Data Source - a gotcha

Although using SSIS as a data source for SSRS isn't a supported configuration in SQL Server and has indeed been deprecated from 2008R2, i've been trying to make use of it for a particular scenario (using SQL2008). Its not the most straight forward of processes as it requires changes to config files, careful use of dataset names and plenty of security jiggery pokery but i'm not going to touch on that here.

The issue I came across was due to me having SQL2005 on my machine previous to upgrading to 2008. When I developed my report with SSIS as the datasource in BIDS, everything worked perfectly and it was only when it was deployed to the report server that I got the error message:


Initially, I thought it was a permissions problem (as thats what the majority of issues appear to be using this configuration) but after exhausting all other possibilities and actually READING the error message it became apparant that the issue was a little more fundamental than that. Logging had shown that the package wasn't even being executed and the message bears that out. The issue seemed to be more that the command being passed to the SSIS engine wasn't correct. It was then that i'd remembered that I'd upgraded and there was a good chance that it was trying to call my SQL2005 version of DTEXEC. Fortunately, i'd seen another post from Jens which suggested how the problem might be fixed.

Hey presto, changing the version of the SSIS extension element in rsreportserver.config did the trick (with a restart of the SSRS service).



TO:



So I may still have plenty of security issues to resolve and as the feature is no longer supported in SQL2008R2 and above, I doubt whether I'll go ahead and use it in Production but hopefully someone may find this useful.

Admin: Disk Configuration RAID 0+1

I saw an interesting question SQL Server Central recently on RAID Configurations. Essentially, it was asking which RAID level is more tolerant to multiple disk failures RAID 1+0 or RAID 0+1.

I have to be honest, i'd never even heard of RAID 0+1 before then and it got me thinking exactly what the configuration would look like. So i understand that 0 is striped and 1 is mirroed but how does mirror/stripe differ to stripe/mirror? Reading wikipedia gave me some information but to be honest, it still wasn't 100% clear so i had to get out my pencil and paper to try and visualise the difference.

I think i've got it, hopefully aided by the following diagram:



Essentially, if you have a RAID0+1 array, the whole sum of the disks is mirrored, not the individual disks (10 = 10). So if you have a disk failure, then the mirror is broken as there is no quorum disk to calculate the missing disk data and read requests can only be serviced by the one stripe set (7 != 10). This leaves you exposed to any disk failure in the remaining stripe set giving you a increased risk of data loss. In a RAID 1+0 array, as each disk is mirrored requests can be serviced by either array and as long as you don't suffer a failure of a pair of mirrored disks, your data is available.

I'm not quite sure what the benefits are of using one over the other. Once there has been a disk failure, I suppose you get better write performance with 0+1 as it is effectively a RAID0 array but i'm not sure that counts as a benefit. I'll be sticking with RAID 1+0.