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.
[UPDATE]
Scott Bueffel contacted me to say that he's written a new version of this code to work in his large Exchange environment which is far quicker to run as a result of using bypassing the Exchange cmdlets to get the minimum amount of data count the mailboxes and just update the hash table. I'd recommend checking out his post.
I'm not so worried about the speed because these days I'm just rebuilding that hash table once a week and saving it in a file which the mailbox creation script reads and updates as it adds more mailboxes. I still need to re-do the counts in full because our current method of user/mailbox removal doesn't update the counts. If I remember correctly, it was /\/\o\/\/'s suggestion to store the hash table in a file, but if I'm wrong and someone else deserves the credit, get in touch.
Thursday, June 25, 2009 at 17:22 