CSV DAG Creation in Powershell for Exchange 2010

by [Published on 27 Oct. 2011 / Last Updated on 27 Oct. 2011]

In this article the author will provide a PowerShell script which will allow for Exchange 2010 administrators to layout their Mailbox Databases within a CSV file as well as their associated Database copies which can then be created in bulk within their Exchange 2010 environments.

Introduction

Automation is a key theme in modern day Exchange administration – and with the advent of PowerShell in Exchange 2007, administrators have enjoyed rich potential to automate almost every aspect of the day to day operations of Exchange.

The ability to “script” repetitive tasks is a real boon – one such task that I have personally found a bit of a pain in the neck over the years has been the creation of Mailbox Databases – especially in larger environments.

In this article I would like to present to you a sample PowerShell script for Exchange 2010 DAG environments, which will allow admins to configure a number of parameters for their mailbox stores within a CSV file (including DAG copies of the stores), and then have the script create these stores for you within your Exchange 2010 environment.

The provided script is designed to perform the following actions:

  • Parse the CSV file which contains the following information:
    o   Database Name
    o   Transaction Log Drive
    o   Database Drive
    o   Parent Log Folder
    o   Parent Database Folder
    o   Primary Activation Server
    o   DAG Replica Servers
    o   Activation Preference
  • Check to ensure that the DB and Log drives are present on the server
  • Check to see if the folder paths exist, if not create them
  • Create the primary Databases
  • Mount the Databases
  • Create the DAG replicas on the relevant servers
  • Assign Activation Preferences to the copies

The Script

The script consists of 3 functions which are as follows:

  1. Select-FileDialog

The Select File Dialog function is a little bit of PowerShell that I picked up from here some time ago – in essence it is a neat little PowerShell function that allows for you to call the Windows.Forms open dialog from your PowerShell script. This is used to locate the CSV file for the overall script for parsing your Database configuration.

  1. CountDown

The countdown function is used to pause the script to allow for Active Directory replication to take place when new databases are created in larger environments that have multiple domain controllers.

In essence this function is made up of a “for” loop which runs until the value of $count is less than or equal to 100 (e.g. when it hits 100 %) it will stop.

Within the body of the loop is a “Write-Progress” statement that naturally increments as the loop iterates – I have used a “Sleep” command to pause the loop for 1 second so that the “Write-Progress” cmdlet keeps in sync for 1 second per pass (100 seconds) – and allows for directory replication to take place.

  1. Parse_CSVData()

The parse_CSVData function is the heart of the entire script and is the first to be called. In essence the function takes a single parameter ($Filename) which is naturally the name and path to the CSV file that contains the Database layouts. The CSV file is then imported into a variable called $CSVFile (using the Import-CSV cmdlet).

Using a “for” Loop each entry within the $CSVFile variable is cycled through – the script checks to see if the Transaction Log drive stipulated in the script exists using the Test-Path cmdlet.

If the path does exist the relevant folders are created. If the Drive does not exist then the script will halt.

The same tests are then performed for the Database Drive.

When the paths have been verified as present – the script will then create the database on the Primary Exchange server (using the New-MailboxDatabase cmdlet) using the relevant parameters passed from the CSV file (Drive, Paths and Name).

The script will then pause for 100 seconds (by calling the Countdown function) to allow for replication within AD of the new Database (the time period that you wait can be changed by adjusting the integer to the right of the Sleep command in the Countdown function). When the Countdown function has completed the script will then try to mount the newly created Database (using the Mount-Database).

The next part of the script then attempts to parse both the DAGReplicaServers and ActivationPrefs from the CSV file.

This is performed by getting the values from relevant cells in the CSV file into two separate variables ($DAGReplicas& $ActPrefs) – where the script encounters a “!” placing the next value on a newline within the variable.

The script then splits the newlines into new values within a pair of two new variables ($arr& $ap) – creating an array which is addressable by another “for” loop.

This in effect allows the script to cycle through each DAG Replica Server and Activation Preference, and create the relevant copies using the Add-MailboxDatabaseCopy

Add-PSSnapin "Microsoft.Exchange.Management.PowerShell.E2010"

functionSelect-FileDialog

{

       param([string]$Title,[string]$Directory,[string]$Filter="CSV Files (*.csv)|*.csv")

       [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") | Out-Null

       $objForm= New-Object System.Windows.Forms.OpenFileDialog

       $objForm.InitialDirectory =$Directory

       $objForm.Filter =$Filter

       $objForm.Title =$Title

       $objForm.ShowHelp =$true

      

       $Show=$objForm.ShowDialog()

      

       If ($Show-eq"OK")

       {

              Return$objForm.FileName

       }

       Else

       {

              Exit

       }

}

functionCountDown() {

       cls

       for($Count= 1;$Count-le 100; $Count ++){

      

       Write-Progress -PercentComplete $Count -Activity "Waiting for AD Replication" -Status "Waiting for AD Replication"

       Sleep 1

       }

}

functionparse_CSVData(){

       param(

              $FileName

       )

       $csvFile= Import-Csv $FileName

      

       foreach($DBin$csvFile){

             

             

              $TSDrivePresent= Test-Path $DB.TransactionLogDrive

             

              if($TSDrivePresent-eq$true){

                     Write-Host "Transaction Drive is present" -ForegroundColor Cyan

                     $DBDrivePresent= Test-Path $DB.DatabaseDrive

                     $TSfldr=$DB.TransactionLogDrive+"\"+$DB.ParentLogFolder

                     $TSFolderPresent= Test-Path $TSfldr

                    

                     if($TSFolderPresent-eq$false){

                          

                           Write-Host "Creating Transaction Log Folder" -ForegroundColor Yellow

                           New-Item $TSfldr -ItemType Directory

                          

                     }

                     if($DBDrivePresent-eq$true){

                           Write-Host "Database Drive is present" -ForegroundColor Cyan

                           $DBfldr=$DB.DatabaseDrive+"\"+$DB.ParentDatabaseFolder

                           $DBFolderPresent= Test-Path $DBfldr

                          

                           if($DBFolderPresent-eq$false){

                          

                           Write-Host "Creating Database Folder" -ForegroundColor Yellow

                           New-Item $DBfldr -ItemType Directory

                          

                           }

                          

                           $DBPath=$DBfldr+"\"+$DB.DatabaseName+".edb"

                           $LogPath=$TSfldr+"\"+$DB.DatabaseName+"_LOG"

                          

                           Write-Host "Creating Mailbox Database" -ForegroundColor Blue

                          

                           New-MailboxDatabase -Name $DB.DatabaseName -Server $DB.PrimaryServer -EdbFilePath $DBPath -LogFolderPath $LogPath

                           CountDown

                           Write-Host "Mounting Mailbox Database" -ForegroundColor Green

                           Mount-Database -Identity $DB.DatabaseName

                          

                           $DagReplicas=$DB.DAGReplicaServers.Replace("!","`n");

                           $ActPrefs=$DB.ActivationPrefs.Replace("!","`n");

                          

                           $arr=$DagReplicas.split("`n")

                           $ap=$ActPrefs.split("`n")

                           

                           foreach ($elementin$arr){

                                         foreach ($prefin$ap){

                                                Write-Host "Adding DAG Copies" -ForegroundColor White

                                                Add-MailboxDatabaseCopy -Identity $DB.DatabaseName -MailboxServer $element -ActivationPreference $pref -ErrorActionSilentlyContinue

                                         }

                                 

                           }

                          

                     }else{

                           Write-Host "Error: Database Drive Specified is not present" -ForegroundColor Red

                     }

              }else{

                    

                     Write-Host "Error: Transaction Drive Specified is not present" -ForegroundColor Red

                    

              }

       }

}

$File=Select-FileDialog -Title "Import an CSV file" -Directory "c:\"

parse_CSVData$File

The format of the CSV File

The CSV is naturally key to the overall functionality of the script – as it is within this that you will design and layout your Mailbox Databases and DAG copies.

The file itself requires the following headings to be populated:

  • Database Name [DatabaseName]
  • Transaction Log Drive Letter [TransactionLogDrive]
  • Database Drive Letter [DatabaseDrive]
  • Parent Log Folder [ParentLogFolder] (on the Transaction Log Drive)
  • Parent Database Folder [ParentDatabaseFolder] (on the Database Drive)
  • Primary Server [PrimaryServer] (on which the database should be mounted)
  • DAG Replica Servers [DAGReplicaServers] (each separated by an exclamation mark ‘!’)
  • Activation Preference [ActivationPrefs] - (each separated by and exclamation mark ‘!’)

A correctly populated CSV file will (should) look like the following example;


Figure 1

It is key to note that the fields [DAGReplicaServers] and [ActivationPrefs] can contain values which are separated by an exclamation mark “!”. Therefore if you create a database which has two DAG copies, the Replica Server Names should be contained within the DAGReplicaServers field, each separated with a ‘!’.

The same principle applies for the [ActivationPrefs] field – the number of activation preferences should match the number of DAGReplicaServers and should start at 2 (as the priority 1 preference is catered for within the [PrimaryServer] field.

Using the script

In order to use the script you will need to download a copy of it from the following location to one of the Exchange 2010 Mailbox Servers within your Exchange environment.

You will need to ensure that the Execution Policy for scripts is at least set to “RemoteSigned”.

Note:
In order for the DAG Replicas to be created properly you must ensure that each of the Mailbox Servers has matching drives as the primary server for the copy to be added correctly.

Open a PowerShell command prompt, navigate to the location where you have downloaded the script to and type in the following:

.\CreateDAGDatabasesFromCSV.ps1 – as per the screen shot below:


Figure 2

You will then be prompted for the location of the your DAG Layout CSV file – see below;


Figure 3

The script will then execute, creating the paths, databases and copies as specified in the CSV file – see below:


Figure 4

During the execution – the script will pause – this is normal, and is allowing for AD Replication in larger environments – see below:


Figure 5

Once the AD replication process timer has completed the script will attempt to mount the database that has been created – see below:


Figure 6

When the Database has mounted, the script will then create the relevant Database Copies within the DAG configuration.

Summary

This article has looked at how you can use PowerShell and a CSV file to create Exchange 2010 mailbox Databases and associated DAG copies.

Advertisement

Featured Links