Author Posts

January 1, 2012 at 12:00 am

by rj_connor at 2013-01-22 07:25:32

Hi

Not sure if this is the best place for this as it covers SQL and AD, so if incorrect please let me know.

I have the below script that runs to export AD information in to a SQL database, we have 3 Domains EMEA, AMERS and APAC and the script is duplicated 3 time one under each other pointing to a different DC in each domain.

The problem I’m having is that it takes over 3 hrs to pull the data in to SQL, can I tweak the script so that i can reduce that time.

Script..

if ( (Get-PSSnapin -Name Quest.ActiveRoles.ADManagement -ErrorAction SilentlyContinue) -eq $null )
{
Add-PsSnapin Quest.ActiveRoles.ADManagement
}

$ldapQuery ="(&(objectCategory=person)(objectClass=user)(msExchHomeServerName=*))"

#Extract and Import for EMEA domain

$oSqlConnection = New-Object System.Data.SqlClient.SqlConnection ("Data Source=LONSWEBBOX\SQLEXPRESS;Initial Catalog=IME_Decom_Reporting;Integrated Security=SSPI");
$oSqlCmd = New-Object System.Data.SqlClient.SqlCommand ("sp_Import_EMEA_MBX_Stats",$oSqlConnection);
$oSqlCmd.CommandType = [System.Data.CommandType] "StoredProcedure"
$oSqlCmd.Parameters.Add("@EA13",[System.Data.SqlDbType]"VarChar", 1024)
$oSqlCmd.Parameters.Add("@EA6",[System.Data.SqlDbType]"VarChar", 1024)
$oSqlCmd.Parameters.Add("@EA5",[System.Data.SqlDbType]"VarChar", 1024)
$oSqlCmd.Parameters.Add("@EA4",[System.Data.SqlDbType]"VarChar", 1024)
$oSqlCmd.Parameters.Add("@hidden",[System.Data.SqlDbType]"VarChar", 256)
$oSqlCmd.Parameters.Add("@Disabled",[System.Data.SqlDbType]"VarChar", 256)
$oSqlCmd.Parameters.Add("@Email",[System.Data.SqlDbType]"VarChar", 256)
$oSqlCmd.Parameters.Add("@TA",[System.Data.SqlDbType]"VarChar", 256)
$oSqlCmd.Parameters.Add("@Sam",[System.Data.SqlDbType]"VarChar", 256)
$oSqlCmd.Parameters.Add("@homeMDB",[System.Data.SqlDbType]"VarChar", 1024)
$oSqlCmd.Parameters.Add("@LEDN",[System.Data.SqlDbType]"VarChar", 1024)

Get-QADUser -Service "londemea1.emea.com" -SizeLimit 75000 -LdapFilter $ldapQuery -IncludeAllProperties | %{

$oSqlCmd.Parameters["@EA13"].Value = $_.extensionAttribute13
$oSqlCmd.Parameters["@EA6"].Value = $_.extensionAttribute6
$oSqlCmd.Parameters["@EA5"].Value = $_.extensionAttribute5
$oSqlCmd.Parameters["@EA4"].Value = $_.extensionAttribute4
$oSqlCmd.Parameters["@hidden"].Value = $_.msExchHideFromAddressLists
$oSqlCmd.Parameters["@Disabled"].Value = $_.AccountIsDisabled
$oSqlCmd.Parameters["@Email"].Value = $_.Email
$oSqlCmd.Parameters["@TA"].Value = $_.targetaddress
$oSqlCmd.Parameters["@Sam"].Value = $_.SamAccountName
$oSqlCmd.Parameters["@homeMDB"].Value = $_.homeMDB
$oSqlCmd.Parameters["@LEDN"].Value = $_.legacyExchangeDN
$oSqlConnection.Open()
$oSqlCmd.ExecuteNonQuery()
$oSqlConnection.Close()

}

by DonJ at 2013-01-22 13:40:56

As a note, you can use the CODE button in the toolbar to format your code.

Can you add some logging (e.g., Write-Host (Get-Date)) at various points? It would help to see what commands are taking all the time. I suspect it may just be the number of accounts you're querying; firing off 75,000 SQL queries can take time, depending on the SQL Server's abilities. At 1 second each, 75,000 users is over 20 hours. At three hours you're getting about 10 queries/second, which ain't bad.