by rj_connor at 2013-01-22 07:25:32
Hiby DonJ at 2013-01-22 13:40:56
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()
}
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.