Does your company use Outlook email forwarding? As your organisation has grown, have the rules become hard to manage?

We have a customer using Exchange Server for a few dozen users. They use mailbox rules so they can delegate various activities when staff are out of the office. Occasionally they lose track of what’s being forwarded, and it’s a pain to get this information from each user one by one. Here’s a little PowerShell script I threw together to automate it. I ran this on their server from the “Exchange Management Shell”. It’s my first PowerShell script ever; I’m used to Linux shell scripts and up till now haven’t made complex scripts under Windows. That may change:

PowerShell Script to list Outlook mailbox rules for users in an organisation

$users = get-mailbox

ForEach ($user in $users)
{
        $rules = get-InboxRule -Mailbox $user.name
        if ($rules.length -gt 0) {
                echo ""
                echo $user.name
                echo ""
                $rules | select name, priority, description | fl
                echo ""
        }
}

Save this in a file with the extension “ps1” (that’s a one on the end) and run it from PowerShell to get the output – use > to redirect to a file if necessary. If you put the script in the current directory, you may need to explicitly specify the path, for example: .\run.ps1 > rules.txt Note that this only shows the server-side rules, such as placing messages in a folder, forwarding, etc. Anything that interacts with the client machine such as a popup will not appear in this report – this suits our customer because they want the rules to work when a user is not even logged in. The customer is pleased they can see the server-side rules in one report, and I’ve learned that it’s not just Linux that can support powerful scripting!

Overview

In this post, we give an example to show how a function-based index in Oracle can increase the speed of a query where null and not null values are being compared.

Our customer asked us to modify their application to enable an ETL tool to collect invoice records from an Oracle database. The invoice table contained upwards of 8 million records, but we were really only interested in looking at records which had been flagged for collection but not yet flagged as collected. However, the ETL tool’s method of flagging these records did not allow us to use a simple bitmap index to quickly retrieve the rows we needed.

Invoices example

Consider the following example data set for our Invoices table

In this example, the two columns with names starting ETL_ behave like flags. The possible values are null or a varchar2 value which happens to be based on a timestamp. For the purposes of this exercise, we are only interested in whether the values are null or not null.

SELECT * FROM INVOICES
WHERE ETL_collect IS NOT NULL
AND ETL_done IS NULL;

which would retrieve the following rows

Optimising the query’s performance using an index

In a table containing several million rows, this query would take too long to run. Adding a B-Tree index on ETL_collect and ETL_done wouldn’t help because the null values would not be included in the index. Bitmap indexes do include nulls but we have such a range of different not null values in our table, that a bitmap index is not the best choice.

We need to transform our ETL columns from null / not null values to 0 / 1 and then use an index to optimise the query. First we create a function that tests if a value is null, and returns a 1 or 0 accordingly –

CREATE OR REPLACE FUNCTION ETL_MATCH (
    field_in IN INTEGER
) RETURN INTEGER
  DETERMINISTIC
IS
BEGIN
  if field_in is not null then
      RETURN(1);
   ELSE
      RETURN(0);
  END IF;
END;

By declaring this function to be DETERMINISTIC, we indicate that the function returns the same result value whenever it is called with the same argument.

Now we create an index on our ETL columns which makes use of this function on both of the columns we are testing –

CREATE INDEX ETL_Match_idx
ON Invoices (ETL_Match(ETL_collect), ETL_Match(ETL_done));

And now to revisit our original query. To force it to use this index, we need to reference the indexed columns in the where clause, and we can do that as follows:

SELECT * FROM INVOICES
WHERE ETL_Match(ETL_collect) = 1
AND ETL_Match(ETL_done) = 0;

Results

With an underlying invoices table containing more than 8 million records, the original query took 17 seconds to retrieve 70 rows. The new query using a function-based index returns 70 rows in less than 3 seconds.

Overview

Our customer had a vendor product which used a MySQL database.  The nightly backup of the database was preventing users from accessing the data for the duration of the backup.  As the data grew, the backup was taking longer and longer, and this was causing problems with other system activity – timeouts and failures had become an issue.

There are various approaches to backing up MySQL which we don’t cover here.  In this case, it was not feasible to change the customer’s backup procedure at the time, so this workaround, which took just a couple of hours to implement and test, helped them avoid regular downtime for a few months until an alternative backup approach could be rolled out.

We reduced downtime from over an hour to a few seconds

Before we addressed the problem, our customer’s data would be inaccessible for at least one hour each night.  After we implemented our solution, the downtime was reduced to a few seconds.

Ewan’s Technical Notes

A redeployment of the vendor software was already on the horizon, including database replication as the vendor’s preferred backup strategy. In the meantime, what was the most cost effective way to prevent the old backup from disabling the system?

LVM2 – Logical Volume Management

I had previously used LVM2 snapshots to perform low level file system backups on a running system. The commands are well documented elsewhere, so a summary should suffice: If a file system (volume) has been created in a volume group with enough free space left over, an instantaneous snapshot can be taken and maintained by LVM2, using copy-on-write.  My previous use of LVM2 was to create such snapshots and run “dump” on those to perform a backup, in the knowledge that if I’d got my sums right, the snapshot would remain valid long enough to complete the backup, without interrupting any continued attempts to change the real file system.

Of course, database backups should not normally be a simple backup of the file system. Even if a snapshot is taken to guarantee no changes to the data being backed up during the process, a restore does involve an element of recovery – to the database, it looks like the system was shut down uncleanly. How could the instant snapshot to be used to take a conventional database backup?

The solution was simple, and had been hinted at elsewhere in my Google searches: mount the LVM2 snapshot as a normal file system, and create a second MySQL configuration to allow it to be accessed, albeit through a non-standard MySQL port. Then a backup could be done by pointing “mysqldump” to this copy. After the backup, the second MySQL would be shut down, the file system unmounted and the snapshot deleted.

Using LVM Snapshots to backup MySQL

So that the second MySQL would not “wake up” to a file system requiring recovery, there was the need to lock the tables just like the previous “mysqldump” would do – but only for long enough to create the LVM2 snapshot, which is just a few seconds. Reducing the system pause to such a short time meant there were no longer any timeouts or failures.

This change to the backups was quick to implement and bought us time to properly roll out new deployments of the vendor software with an entirely different mechanism for backups. Having previously enjoyed using LVM2 for more conventional uses, this was an interesting and novel use which gave us a few months to breathe more easily.