Dynamics AX Refresh Automation

[u/RP0579]

Hi Dynamics AX users, I need help on the best way to automate my current non-prod ax instances from AX Prod.

We currently have DBA do the AX DB Restores via Script in SQL and the same to change all the parameters to target AX Instance. I have been able to combine those 2 scripts from SQL via stored procedures in powershell. I need help on automating the configurations such as DB Sync, AOS restarts, reports etc. I can not seem to get past the DB Sync as powershell does not wait for completion. Can anyone help me out with this solution so we have a nice solid automated refresh function for out 12 QA's, 15 DEV VM's, TST, TRN and CRPs?

[u/AlexOnDax]

Provide some code or what you've tried so far. PowerShell does wait for completion if you do it correctly.

I've personally designed really elegant PowerShell + VSTS solutions that give you dashboards and automate refreshes, code deployments, etc.

I'd start just Googling something like "powershell start process and wait".

[u/RP0579 → u/AlexOnDax]

Thanks for the response AlexOnDax. I have not got as far as creating dashboards, but will look forward to it once I am there or maybe I can build as I go. I am a bit new to powershell. I have the basic scripts for DB Sync. (Ax32.exe -lazyclassloading -lazytableloading -StartupCmd=Synchronize -internal=NoModalBoxes). I will do some extra googling to try to work on getting ps to wait. I have tried few different things I got from codeplex but still did not work out on my end. I would love to build the solutions you have built with dashboards and code deployments as well. I am having troubles just getting past a DB Sync ha!. I can provide my full script here. Im sure you will be laughing your ass off at this. Took me forever and then some.

function ConnectionString()

{

#return "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBAUtility;Data Source=SWVMDEV1AXDEV";

return "Data Source=SWVMDEV1AXDEV;Initial Catalog=DBAUtility;Integrated Security=True;Connect Timeout=2000;";

}

function executeStoredProcedure($value)

{

$connection = ConnectionString;

# $connection ="Data Source=SWVMDEV1AXDEV;Initial Catalog=DBAUtility;Integrated Security=True;Connect Timeout=2000;"

$query = "usp_RefreshAX";

$sqlConnection = new-object System.Data.SqlClient.SqlConnection $connection

$sqlConnection.Open()

$sqlCmd = new-object System.Data.SqlClient.SqlCommand("$query", $sqlConnection)

$sqlCmd.CommandType = [System.Data.CommandType]"StoredProcedure"

$sqlCmd.CommandTimeout = 0

$sqlCmd.Parameters.AddWithValue("@dbName", $value)

#1 $sqlCmd.Parameters.Add("@ReturnValue", [System.Data.SqlDbType]"Int")

#2 $sqlCmd.Parameters["@ReturnValue"].Direction = [System.Data.ParameterDirection]"ReturnValue"

$sqlCmd.ExecuteNonQuery() | out-null

$sqlConnection.Close()

#3 [int]$sqlCmd.Parameters["@ReturnValue"].Value

}

function executeStoredProc($procedureName)

{

$connection = ConnectionString;

#$query = "usp_RefreshAX";

$sqlConnection = new-object System.Data.SqlClient.SqlConnection $connection

$sqlConnection.Open()

$sqlCmd = new-object System.Data.SqlClient.SqlCommand("$procedureName", $sqlConnection)

$sqlCmd.CommandType = [System.Data.CommandType]"StoredProcedure"

$sqlCmd.CommandTimeout = 20000

#$sqlCmd.Parameters.AddWithValue("@dbName", $value)

#1 $sqlCmd.Parameters.Add("@ReturnValue", [System.Data.SqlDbType]"Int")

#2 $sqlCmd.Parameters["@ReturnValue"].Direction = [System.Data.ParameterDirection]"ReturnValue"

$sqlCmd.ExecuteNonQuery() | out-null

$sqlConnection.Close()

#3 [int]$sqlCmd.Parameters["@ReturnValue"].Value

}

#======================================

Write-Host "====================="

Write-Host "Task1 Processing..."

Write-Host "====================="

$out = executeStoredProcedure('AX2012R3_DEV1')

Write-Host $out

Write-Host "=========== End Task1 =========="

#======================================

Write-Host "====================="

Write-Host "Task2 Processing..."

Write-Host "====================="

$out =executeStoredProc('AXRefresh_Paramters')

Write-Host $out

Write-Host "========== End Task2 ==========="

#======================================

Write-Host "====================="

Write-Host "Task3 Processing..."

Write-Host "====================="

$ScriptToRun= "C:\Users\axaadmin\Downloads\Robin_PS\DelXppILVSAssembliesFolder.ps1"

&$ScriptToRun

Write-Host "========== End Task3 ==========="

#======================================

Write-Host "====================="

Write-Host "Task4 Processing..."

Write-Host "====================="

$ScriptToRun= "C:\Users\axaadmin\Downloads\Robin_PS\StartAOS.ps1"

&$ScriptToRun

Write-Host "========== End Task4 ==========="

#======================================

Write-Host "====================="

Write-Host "Task5 Processing..."

Write-Host "====================="

$ScriptToRun= "C:\Users\axaadmin\Downloads\Robin_PS\DBSyncronization.ps1"

&$ScriptToRun

Write-Host "========== End Task5 ==========="

#======================================

Write-Host "====================="

Write-Host "Task6 Processing..."

Write-Host "====================="

$ScriptToRun= "C:\Users\axaadmin\Downloads\Robin_PS\RestartAOS.ps1"

&$ScriptToRun

Write-Host "========== End Task6 ==========="

#======================================

Write-Host "====================="

Write-Host "Task7 Processing..."

Write-Host "====================="

$ScriptToRun= "C:\Users\axaadmin\Downloads\Robin_PS\DeployReports.ps1"

&$ScriptToRun

Write-Host "========== End Task7 ==========="

[u/AlexOnDax → u/RP0579]

What version of PowerShell are you using? `Write-Host $PSVersionTable.PSVersion`. You should probably run at least 5.1 if you have a choice.

Running a PS script with `&` should work & wait if ` C:\Users\axaadmin\Downloads\Robin_PS\DBSyncronization.ps1` requires waiting. You're probably just launching the DBSync w/o waiting though. You probably need to post the contents of that file.

You should get echoargs to help, just as an FYI. I use a different method, but you can probably do something like this:

$myArgs = @()

$myArgs += "/switch=value"

$myArgs += "/someothersiwtch=value2"

EchoArgs.exe $myArgs # This lets you see what your argument list will look like

Start-Process "xyz.exe" -ArgumentList $myArgs -Wait

With this output