At this time every year, I have to arrange the provisioning of Active Directory user accounts and Exchange mailboxes for thousands of students. This year, I'm completing the whole of that process using Windows PowerShell. This means spreading them evenly over a set of Exchange mailbox databases.

The first step in this is finding out how many mailboxes are already in each database, which I'm doing like this:

#requires -pssnapin Microsoft.Exchange.Management.PowerShell.Admin
$mailboxcount = @{}
Get-MailboxDatabase -Server EXSRV02 | ?{$_.recovery -eq $FALSE} | `
%{$mailboxcount["$($_.storagegroup)\$($_.name)"] = 0;
Get-Mailbox -Database $_ -ResultSize Unlimited | `
%{$mailboxcount["$($_.database)"]++}}
$mailboxcount.GetEnumerator() | sort name

Breaking that down...

I'm not actually running this in the Exchange Management Shell. My profile imports the necessary Exchange snapin to the standard shell (along with a few other things), but I'm just checking that it's registered first by using the #requires statement because otherwise we wouldn't get very far (see my friend Aleksandar's post on #requires for more explanation).

Then we declare an empty hashtable, $mailboxcount, which we're going to use to hold the mailbox databases and the number of mailboxes in each.

In my example script, I'm specifying a server name to the Get-MailboxDatabase cmdlet, but you could equally leave off the -server parameter, which will get all of the mailbox stores (databases) in your Exchange 2007 environment. You could also specify a set of servers by reading them from a file, or passing an array of strings containing server names, to the get-mailboxserver cmdlet and then into get-mailboxdatabase like this:

get-content mailboxserver.txt | get-mailboxserver | get-mailboxdatabase

or

"EXSRV01","EXSRV02" | getmailboxserver | get-mailboxdatabase

That gets all of the databases on the servers that we want to look at, but it also includes Recovery Storage Groups, so we're filtering those out by piping our mailbox databases through...

?{$_.recovery -eq $FALSE}

Next, we're piping each database into a script block by using the foreach-object cmdlet (aliased to %). This script block contains two lines of PowerShell, separated with a semi-colon. The first populates the $mailboxcount hashtable with each of the database names. It does it in a slightly strange manner: $($_.storagegroup)\$($_.name) because this the same format as we'll get from the mailbox's database property and we need them to match up, and also because this produces the most useful output - SERVERNAME\STORAGEGROUP\DATABASE. We're assigning each database an initial value of 0.

That line may be optional to you, depending on how you want this to work. If you want to see any empty databases, you need it in there, but if you've added a new database you're not quite ready to use just yet, you can leave it out and the empty database will be left out of your results (and therefore the allocations in the next phase).

The next line uses the Get-Mailbox cmdlet, specifying the -database parameter to return all the mailboxes for the current database and these are passed down the pipeline to increment the value for that database in the hashtable with

mailboxcount["$($_.database)"]++

The mailbox's database property is enclosed in $() so that it is evaluated before it's treat as the key string, otherwise you'd end up with a hashtable full of MAILBOXNAME.database, with a value of 1. That part of the script takes longer the more mailboxes you have.

That done, we're displaying the contents of the hashtable, which is better viewed sorted, so we're using the GetEnumerator method and sorting by name to get something like:

Name                           Value
----                           -----
EXSRV02\SG01\MS01              199
EXSRV02\SG02\MS02              201
EXSRV02\SG03\MS03              199
EXSRV02\SG04\MS04              200
EXSRV02\SG05\MS05              189
EXSRV02\SG06\MS06              188
EXSRV02\SG07\MS07              200
EXSRV02\SG08\MS08              172
EXSRV02\SG09\MS09              195
EXSRV02\SG10\MS10              183
EXSRV02\SG11\MS11              77

Alternatively, you can sort by value to see the most used databases by using

$mailboxcount.GetEnumerator() | sort value -desc

which will show largest down to smallest, like this:

Name                           Value
----                           -----
EXSRV02\SG02\MS02              201
EXSRV02\SG07\MS07              200
EXSRV02\SG04\MS04              200
EXSRV02\SG01\MS01              199
EXSRV02\SG03\MS03              199
EXSRV02\SG09\MS09              195
EXSRV02\SG05\MS05              189
EXSRV02\SG06\MS06              188
EXSRV02\SG10\MS10              183
EXSRV02\SG08\MS08              172
EXSRV02\SG11\MS11              77

That's a useful script for seeing how many mailboxes are in each database, although you may want to discount the disconnected mailboxes, i.e. those that are waiting to be purged because their associated user object has been deleted (see my earlier post about orphaned mailboxes).

Now on to adding new mailboxes...

Now that we've got that hashtable, we don't need to query the system again for this round of provisioning. All we're going to do is, for each new mailbox we create, grab the database with the fewest mailboxes, increment the value in the hashtable, like so...

$mbdatabase = ($mailboxcount.GetEnumerator() | sort value | select -first 1).key
$mailboxcount[$mbdatabase]++

and this gives us the database to use for the new mailbox in our variable $mbdatabase, which we can give to the Database parameter of the New-Mailbox cmdlet, which will create the mailbox AND the user object for us, or the Enable-Mailbox cmdlet, which will give a mailbox to an existing user.