Automated builds and code deployment (Part 6 - Optimizations)

in AX 2009, Build Process

Read the other posts in this series:
Why Create an AX Build Server/Process?
Part 1 - Intro
Part 2 - Build
Part 3 - Flowback
Part 4 - Promotion
Part 5 - Putting it all together
Part 6 - Optimizations
Part 7 - Upgrades

Up until now I have only been discussing what it has taken to get a Lifecycle Management system in place for AX 2009. The system needs to be reliable and adaptable while providing a comprehensive set of automated tasks to allow changes to AX to be propagated correctly. And like most programming projects, it was approached as a “Let’s get this in place so we have something as soon as we can”. However, inefficiencies can and do add up, so now it’s time to refactor our solution and make it more efficient.

For starters, we have replaced all of the scripts we use with PowerShell scripts for consistency. Since PowerShell is based on the .NET framework, we have the power and versatility of .NET. In some cases we are already using that power, but now we should really convert everything so there is consistency across the board.

In addition, we also noticed that many of scripts are redundant, with only minor changes from copy to copy. If something needs to change in the operations of one type of script, it would need to be changed in multiple places, increasing the chance of errors or inconsistencies. To fix that, we’ve generified majority of the scripts to so they are executed with parameters, which are defined by the specific build configurations. By extracting the differences to the TeamCity build configuration, we are able to turn each type of process (Promotion vs Flowback) into templates, allowing us to spin up a new process and only need to define the unique parameters for that process.

Instead of keeping the files segregated in their own process-specific folder, we’ve moved all of the generic scripts to the root of the version control repository:

We still have process-specific scripts, but those now only hold scripts that cannot be made generic, such as the SQL update scripts, which can’t be made generic as easily as the process scripts.

Here is an example of a script we have converted from a command line to PowerShell:

1
2
3
4
5
6
7
8
9
10
11
12
13
[CmdletBinding()]
param(
    [Parameter(Mandatory=$True)]
        [string]$fileLocation)

Write-Debug "Removing cache files from $fileLocation"
$extensionList = @("alc", "ali", "aoi")

foreach ($extension in $extensionList)
{
    Remove-Item "$fileLocation\*.$extension"
    Write-Host "$extension files removed"
}

In this case, we are passing in a folder location (in our case, the network share for the specific environment), iterating over a list of extensions and removing all files with those extensions from that location. In addition to being able to run for any of our environments, it also allows us to easily remove additional file types by simply adding another entry to the list. If we wanted to remove a different list of extensions for each environments (or the ability to add additional extensions to the default on a per-environment bases), we could extract that to be a parameter as well. However, since our goal is to have each environment be as close to production as possible, we opted not to do that.

Here is another example where we can take advantage of advanced processing to allow the script to run for multiple types of environments:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#Note: default values may not work, as empty strings may be passed. 
#  Such strings should be treated as if nothing was passed.
[CmdletBinding()]
param(
    [Parameter(Mandatory=$True)]
        [string]$serverName, 
        [string]$processName)

#-----Housekeeping-----
#serverName can be comma delimited. If it is, break it out to a list of server names
if ($serverName.Contains(',')) { $serverNameList = $serverName.Split(',') } else { $serverNameList = $serverName }

# Set default values if nothing was passed in
if ([string]::IsNullOrEmpty($processName)) { $processName = 'Dynamics AX Object Server 5.0$*' }
#----/Housekeeping-----

#For each server, start the process
foreach ($server in $serverNameList)
{
    Write-Debug "Starting $processName on $server"
    Write-Host "Starting $processName on $server"
    start-service -inputobject $(get-service -ComputerName "$server" -DisplayName "$processName") -WarningAction SilentlyContinue
    Write-Host "$processName on $server started"
}

This script accepts two parameters, the server name (which is mandatory) and the process name (which is optional). Additionally, it can accept multiple servers as a CSV string - we use this for our production environment, which is load balanced across three servers. The servers are started in the same order as they are passed in, and you only need to define the process name if it is different than the default “Dynamics AX Object Server 5.0$*” (for example, if you have two environments on the same server, so you only shut down one of those environments). We’ve also been able to include debug messages to verify what actions are occurring when changing the scripts, and confirm if you want the action to execute. These messages do not appear when executed by TeamCity.

On the TeamCity side, this script would be configured as follows:

The %AosServer% in the script arguments section is a reference to a the configuration parameters of the build configuration:

Ultimately, these parameters drive the behavior of the entire process (which is why some parameters, like SqlServer, reference other parameters - because for this environment they are the same).

 
Finally, now that all the scripts are effectively applicable to all environments, it makes templating each of main processes easy, since all the scripts will take parameters. The parameters don’t need to have a value within the template, they only need to be defined - the configurations provide the actual values. You can see from the screenshot above that majority of the parameters are inherited from a template. We have the option to reset any of them to the default values (in this case, blank, as defined on the template), but we cannot delete them.

Each of the configuration parameters is also configurable within TeamCity so a value must be provided. If no value is provided, the configuration will not execute. The nice side of configuring a template this way is spinning up a new process is as easy as clicking a button and filling in some values:

 
From there, the only things you need to define are the triggers and dependencies, if you need more than those that are defined via the template.

Similar to the build scripts themselves, if there is a new process that needs to be added for each type of configuration (for example, a new step), we need only add it to the template, and it will automatically be added to all the configurations that inherit from that template.

 
The goal of all this is to decrease the amount of maintenance that needs to be done when a change needs to be made. By standardizing the language of all the scripts, less overall knowledge is needed to manage them; if a script generates an error, we need only fix one version of the script instead of 5; if a process is missing a step, we need only change the template configuration instead of 3-4 build configurations.

Here are samples for the scripts we have (not including the SQL files). Note that you may not need all these files:

UpdateAxDatabase.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
#Note: default values may not work, as empty strings may be passed. 
#  Such strings should be treated as if nothing was passed.
[CmdletBinding()]
param(
   [Parameter(Mandatory=$True)]
       [string]$sqlServerHost,
   [Parameter(Mandatory=$True)]
       [string]$sqlCloneScript,
       [string]$dbPermissionsScript,
       [string]$sqlDatabaseName)

#-----Housekeeping-----
# Set default values if nothing was passed in
if ([string]::IsNullOrEmpty($sqlDatabaseName)) { $sqlDatabaseName = "DynamicsAx1" }
#----/Housekeeping-----

Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue | Out-Null
Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue | Out-Null

Write-Host "Dropping DB Connections"
$query = "ALTER DATABASE $sqlDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
Write-Debug "QUERY: $query"
Invoke-Sqlcmd -ServerInstance $sqlServerHost -Database "master" -Query $query -AbortOnError

Write-Host "Restoring database"
if ($sqlDatabaseName -ne "DynamicsAx1")
{
   $moveCmd = " MOVE N'DynamicsAx1' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\$sqlDatabaseName.mdf',  MOVE N'DynamicsAx1_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\${sqlDatabaseName}_log.LDF',"
}

$query = "RESTORE DATABASE [$sqlDatabaseName] FROM DISK = N'\\[network location]\DBBackup.bak' WITH FILE = 1,$moveCmd NOUNLOAD, REPLACE, STATS = 10"
Write-Debug "QUERY: $query"
Invoke-Sqlcmd -ServerInstance $sqlServerHost -Database "master" -Query $query -AbortOnError -QueryTimeout 65535 -Verbose
Write-Host "Compressing database"

#Simple backup recovery
$query = "ALTER DATABASE [$sqlDatabaseName] SET RECOVERY SIMPLE"
Write-Debug "QUERY: $query"
Invoke-Sqlcmd -ServerInstance $sqlServerHost -Database $sqlDatabaseName -Query $query -AbortOnError

#Shrink log file
$query = "DBCC SHRINKFILE (N'$DynamicsAx1_log' , 0, TRUNCATEONLY)"
Write-Debug "QUERY: $query"
Invoke-Sqlcmd -ServerInstance $sqlServerHost -Database $sqlDatabaseName -Query $query -AbortOnError

#Execute database change script(s)
Write-Host "Applying Database Changes"
Write-Debug "Executing $sqlCloneScript"
Invoke-Sqlcmd -ServerInstance $sqlServerHost -Database $sqlDatabaseName -InputFile $sqlCloneScript -AbortOnError -Verbose

if (![string]::IsNullOrEmpty($dbPermissionsScript))
{
   Write-Debug "Executing $dbPermissionsScript"
   Invoke-Sqlcmd -ServerInstance $sqlServerHost -Database $sqlDatabaseName -InputFile $dbPermissionsScript -AbortOnError
}

#Enable multi-user in case restore failed
$query = "ALTER DATABASE $sqlDatabaseName SET Multi_user"
Write-Debug "QUERY: $query"
Invoke-Sqlcmd -ServerInstance $sqlServerHost -Database $sqlDatabaseName -Query $query -AbortOnError
CopyAosFiles.ps1 
1
2
3
4
5
6
7
[CmdletBinding()]
param(
   [Parameter(Mandatory=$True)]
       [string]$destination)

Write-Debug "Copying files to $destination"
xcopy "AX Build Files\*.*" $destination /Y /z
StartAxServers.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#Note: default values may not work, as empty strings may be passed. 
#  Such strings should be treated as if nothing was passed.
[CmdletBinding()]
param(
   [Parameter(Mandatory=$True)]
       [string]$serverName, 
       [string]$processName)

#-----Housekeeping-----
#serverName can be comma delimited. If it is, break it out to a list of server names
if ($serverName.Contains(',')) { $serverNameList = $serverName.Split(',') } else { $serverNameList = $serverName }

# Set default values if nothing was passed in
if ([string]::IsNullOrEmpty($processName)) { $processName = 'Dynamics AX Object Server 5.0$*' }
#----/Housekeeping-----

#For each server, start the process
foreach ($server in $serverNameList)
{
   Write-Debug "Starting $processName on $server"
   Write-Host "Starting $processName on $server"
   start-service -inputobject $(get-service -ComputerName "$server" -DisplayName "$processName") -WarningAction SilentlyContinue
   Write-Host "$processName on $server started"
}
BackupProdSql.ps1
1
2
3
4
5
6
7
$rootSourceLocation = "\\[Network location]"
$dbBackupFileName   = "DBBackup.bak"
$serverName         = "[Server Name]"
$dbBackupFile       = $rootSourceLocation + $dbBackupFileName
$query = "BACKUP DATABASE [DynamicsAx1] TO DISK = N'" + $dbBackupFile + "' WITH INIT, NOUNLOAD, NAME = N'DynamicsAx1 Clone Backup', NOSKIP, STATS = 10, NOFORMAT" 

sqlcmd -E -S $serverName -d master -Q $query | Out-Host
CleanVcDirectory.ps1
1
2
3
4
5
6
7
[CmdletBinding()]
param(
   [Parameter(Mandatory=$True)]
       [string]$fileLocation)

Write-Debug "Removing XPO files from $fileLocation"
Get-ChildItem $fileLocation -Recurse -Include "*.xpo" -Force | Remove-Item
CompileAx.ps1
1
ax32.exe -startupcmd=compileall_- | Out-Null
CopyVersionControlNotes.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
#Note: default values may not work, as empty strings may be passed. 
#  Such strings should be treated as if nothing was passed.
[CmdletBinding()]
param(
   [Parameter(Mandatory=$True)] [Int32] $dependencyBuildId,
   [Parameter(Mandatory=$True)] [string]$sourceSqlServer,
                                [string]$sourceSqlName,
   [Parameter(Mandatory=$True)] [string]$destinationSqlServer,
                                [string]$destinationSqlName)

#-----Housekeeping-----
# Set default values if nothing was passed in
if ([string]::IsNullOrEmpty($sourceSqlName))      { $sourceSqlName      = $sourceSqlServer      }
if ([string]::IsNullOrEmpty($destinationSqlName)) { $destinationSqlName = $destinationSqlServer }
#----/Housekeeping-----

Write-Debug "Transfering build $dependencyBuildId version control information from $sourceSqlServer ($sourceSqlName) to $destinationSqlServer ($destinationSqlName)"
Write-Host "##teamcity[message text='Loading build information for $dependencyBuildId' status='NORMAL']"

#Load XML assemblies
[Reflection.Assembly]::LoadWithPartialName("System.Xml.Linq") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("System.Linq") | Out-Null
Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue | Out-Null
Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue | Out-Null
#/assemblies

$buildInfo = [System.Xml.Linq.XDocument]::Load("http://[TeamCity URL]/guestAuth/app/rest/builds/id:$dependencyBuildId");
Write-Debug $buildInfo
$buildNum = $buildInfo.Root.Attribute("number").Value;
Write-Debug $buildNum
$buildDate = [DateTime]::ParseExact(($buildInfo.Root.Descendants("finishDate") | %{$_.Value}), "yyyyMMddTHHmmsszzz", [System.Globalization.CultureInfo]::InvariantCulture).ToUniversalTime().ToString("s");
write-debug $buildDate

if ($buildInfo.Root.Descendants("timestamp").Count -gt 0)
{
   $apprvDate = [DateTime]::ParseExact(($buildInfo.Root.Descendants("timestamp") | %{$_.Value}), "yyyyMMddTHHmmsszzz", [System.Globalization.CultureInfo]::InvariantCulture).ToUniversalTime().ToString("s");
   Write-Debug $apprvDate
}
else
{
   $apprvDate = Get-Date "1/1/1900 00:00:00"
}

#Update Build information in the environment
$query = "UPDATE SysEnvironment SET BUILDNO = $buildNum, BUILDDATE = '$buildDate', APPROVEDDATE = '$apprvDate'"
Invoke-Sqlcmd -ServerInstance $destinationSqlServer -Database "DynamicsAx1" -Query $query

#Pass along Version Control Items table
$query = "INSERT INTO [DynamicsAx1].[dbo].[SYSVERSIONCONTROLMORPHXITE2541] 
   SELECT DISTINCT src.*
       FROM [$sourceSqlName].[DynamicsAx1].[dbo].[SYSVERSIONCONTROLMORPHXITE2541] src
       LEFT JOIN [$destinationSqlName].[DynamicsAx1].[dbo].[SYSVERSIONCONTROLMORPHXITE2541] dest
           ON src.RECID = dest.RECID
       LEFT OUTER JOIN [$sourceSqlName].[DynamicsAx1].[dbo].[SYSVERSIONCONTROLMORPHXREV2543] rev
           ON rev.ITEMPATH = src.ITEMPATH
       WHERE dest.RECID IS NULL and rev.CREATEDDATETIME < '$buildDate'"
Invoke-Sqlcmd -ServerInstance $destinationSqlServer -Database "DynamicsAx1" -Query $query

#Pass along Version Control Lock table
$query = "INSERT INTO [DynamicsAx1].[dbo].[SYSVERSIONCONTROLMORPHXLOC2542] 
   SELECT src.*
       FROM [$sourceSqlName].[DynamicsAx1].[dbo].[SYSVERSIONCONTROLMORPHXLOC2542] src
       LEFT JOIN [$destinationSqlName].[DynamicsAx1].[dbo].[SYSVERSIONCONTROLMORPHXLOC2542] dest
           ON src.RECID = dest.RECID
       WHERE dest.RECID IS NULL and src.CREATEDDATETIME < '$buildDate'"
Invoke-Sqlcmd -ServerInstance $destinationSqlServer -Database "DynamicsAx1" -Query $query

#Pass along Version Control Revision table
$query = "INSERT INTO [DynamicsAx1].[dbo].[SYSVERSIONCONTROLMORPHXREV2543] 
   SELECT src.*
       FROM [$sourceSqlName].[DynamicsAx1].[dbo].[SYSVERSIONCONTROLMORPHXREV2543] src
       LEFT JOIN [$destinationSqlName].[DynamicsAx1].[dbo].[SYSVERSIONCONTROLMORPHXREV2543] dest
           ON src.RECID = dest.RECID
       WHERE dest.RECID IS NULL and src.CREATEDDATETIME < '$buildDate'"
Invoke-Sqlcmd -ServerInstance $destinationSqlServer -Database "DynamicsAx1" -Query $query

#Update RecID sequences for above tables
foreach ($i in (2541, 2542, 2543))
{
   $query = "UPDATE [DynamicsAx1].[dbo].[SYSTEMSEQUENCES]
       SET NEXTVAL = (SELECT NEXTVAL FROM [$sourceSqlName].[DynamicsAx1].[dbo].[SYSTEMSEQUENCES] src
           WHERE src.TABID = $i)
       WHERE TABID = $i"
   Invoke-Sqlcmd -ServerInstance $destinationSqlServer -Database "DynamicsAx1" -Query $query
}
ExportAot.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#Note: default values may not work, as empty strings may be passed. 
#  Such strings should be treated as if nothing was passed.
[CmdletBinding()]
param(
   [Parameter(Mandatory=$True)]
       [string]$server,
   [Parameter(Mandatory=$True)]
       [string]$exportLocation,
       [string]$instanceName,
       [string]$instancePort)

#-----Housekeeping-----
#serverName can be comma delimited. If it is, only take the first in the list
if ($server.Contains(',')) { $server = $server.Split(',')[0] }

# Set default values if nothing was passed in
if ([string]::IsNullOrEmpty($instanceName))   { $instanceName   = "DynamicsAx1" }
if ([string]::IsNullOrEmpty($instancePort))   { $instancePort   = "2712"        }
#----/Housekeeping-----

Write-Debug "Exporting AOT From $instanceName@$server`:$instancePort to $exportLocation"

$args = @("-startupCmd=aotexport_$exportLocation", "-aos2=$instanceName@$server`:$instancePort")
ax32.exe $args | Out-Null
MercurialCommit.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
#Note: default values may not work, as empty strings may be passed. 
#  Such strings should be treated as if nothing was passed.
[CmdletBinding()]
param(
    [Parameter(Mandatory=$True)]
        [int32] $buildNum,
    [Parameter(Mandatory=$True)]
        [string]$internalBuildId,
    [Parameter(Mandatory=$True)]
        [string]$sqlServerHost,
        [string]$sqlDatabaseName,
		[boolean]$pinned = $false)

#-----Housekeeping-----
# Set default values if nothing was passed in
if ([string]::IsNullOrEmpty($sqlDatabaseName)) { $sqlDatabaseName = "DynamicsAx1" }

$pinnedStr = if ($pinned -eq $true) {",pinned:true"} else {""}
#----/Housekeeping-----

#Load XML assemblies
[Reflection.Assembly]::LoadWithPartialName("System.Xml.Linq") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("System.Linq") | Out-Null
Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue | Out-Null
Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue | Out-Null
#/assemblies

$xmlFile = "http://[TeamCity URL]/guestAuth/app/rest/builds/buildType:(id:$internalBuildId),canceled:any,running:false$pinnedStr"
Write-Debug "Loading $xmlFile"
$buildInfo = [System.Xml.Linq.XDocument]::Load($xmlFile);
$buildDate = [DateTime]::ParseExact(($buildInfo.Root.Descendants("startDate") | %{$_.Value}), "yyyyMMddTHHmmsszzz", [System.Globalization.CultureInfo]::InvariantCulture).ToUniversalTime().ToString("s");

Write-Debug "Retrieving commits from $sqlServerHost.$sqlDatabaseName"
$commits = Invoke-Sqlcmd -ServerInstance $sqlServerHost -Database $sqlDatabaseName -Query "SELECT DISTINCT CAST(COMMENT_ as NVARCHAR(max)) FROM SYSVERSIONCONTROLMORPHXREV2543 WHERE CREATEDDATETIME > '$buildDate' ORDER BY CAST(COMMENT_ AS NVARCHAR(max)) ASC";
if (($commits | measure).Count -gt 0)
{
    $commits | ForEach-Object -Process { $commitMsgs = $commitMsgs + "`n" + $_.Column1 };
}
else
{
    # If there are no commit messages then the changes must be the 
    # automatic reversion due to unpinned builds 
    # NOTE: This will not show in a commit unless there are actual changes to be committed
    $commitMsgs = "Reverted unapproved or unpinned changes.";
} 

$message = "Build ${buildNum}: $commitMsgs"
Write-Debug "Adding to VC + Committing:`n$message"
hg addremove | Out-Host
hg commit -m "Build ${buildNum}: $commitMsgs" -u AXVControl | Out-Host
if (!$?) { return; }

Write-Debug "Pushing commit to remote repository"
hg push | Out-Host
ParseCompilerResults.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
trap
{
	# On any thrown error, return with a non-zero exit code
    exit 1
}

if ($env:TEAMCITY_VERSION) {
        # When PowerShell is started through TeamCity's Command Runner, the standard
        # output will be wrapped at column 80 (a default). This has a negative impact
        # on service messages, as TeamCity quite naturally fails parsing a wrapped
        # message. The solution is to set a new, much wider output width. It will
        # only be set if TEAMCITY_VERSION exists, i.e., if started by TeamCity.
        $host.UI.RawUI.BufferSize = New-Object System.Management.Automation.Host.Size(8192,50)
}

[xml]$xml = (Get-Content "C:\Users\Public\Microsoft\Dynamics Ax\Log\AxCompileAll.xml")

$ns = @{ Table = "urn:www.microsoft.com/Formats/Table" }

$errorNodes = Select-Xml -XPath "/AxaptaCompilerOutput/Table:Record[Table:Field[@name='SysCompilerSeverity'] = 0]" -Xml $xml -Namespace $ns
$warningNodes = Select-Xml -XPath "/AxaptaCompilerOutput/Table:Record[Table:Field[@name='SysCompilerSeverity'] > 0 and Table:Field[@name='SysCompilerSeverity'] < 255]" -Xml $xml -Namespace $ns
$todoNodes = Select-Xml -XPath "/AxaptaCompilerOutput/Table:Record[Table:Field[@name='SysCompilerSeverity'] = 255]" -Xml $xml -Namespace $ns

$success = $true

if (($errorNodes | Measure-Object).Count -gt 0)
{
    foreach ($node in $errorNodes)
    {
        $success = $false
        $nodePath = ($node.Node.Field | ? { $_.name -eq "TreeNodePath" }).'#text'
        $message = ($node.Node.Field | ? { $_.name -eq "SysCompileErrorMessage" }).'#text'

        write-host "##teamcity[message text='${nodePath}: $message' status='ERROR']"
    }
}

foreach ($node in $warningNodes)
{
    $nodePath = ($node.Node.Field | ? { $_.name -eq "TreeNodePath" }).'#text'
    $message = ($node.Node.Field | ? { $_.name -eq "SysCompileErrorMessage" }).'#text'
    
    write-host "##teamcity[message text='${nodePath}: $message' status='WARNING']"
}

foreach ($node in $todoNodes)
{
    $nodePath = ($node.Node.Field | ? { $_.name -eq "TreeNodePath" }).'#text'
    $message = ($node.Node.Field | ? { $_.name -eq "SysCompileErrorMessage" }).'#text'
    
    write-host "${nodePath}: $message"    
}

if ($success -eq $false)
{
    throw $_
}
RemoveAosCacheFiles.ps1
1
2
3
4
5
6
7
8
9
10
11
12
[CmdletBinding()]
param(
   [Parameter(Mandatory=$True)]
       [string]$fileLocation)
Write-Debug "Removing cache files from $fileLocation"
$extensionList = @("alc", "ali", "aoi")

foreach ($extension in $extensionList)
{
   Remove-Item "$fileLocation\*.$extension"
   Write-Host "$extension files removed"
}
StopAxServers.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#Note: default values may not work, as empty strings may be passed. 
#  Such strings should be treated as if nothing was passed.
[CmdletBinding()]
param(
   [Parameter(Mandatory=$True)]
       [string]$serverName,
       [string]$processName)

#-----Housekeeping-----
#serverName can be comma delimited. If it is, break it out to a list of server names
if ($serverName.Contains(',')) { $serverNameList = $serverName.Split(',') } else { $serverNameList = $serverName }

# Set default values if nothing was passed in
if ([string]::IsNullOrEmpty($processName)) { $processName = 'Dynamics AX Object Server 5.0$*' }
#----/Housekeeping-----

#For each server, stop the process
foreach ($server in $serverNameList)
{
   Write-Debug "Stopping $processName on $server"
   Write-Host "Stopping $processName on $server"
   stop-service -inputobject $(get-service -ComputerName "$server" -DisplayName "$processName") -WarningAction SilentlyContinue
   Write-Host "$processName on $server stopped."
}
SynchronizeDataDictionary.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
#Note: default values may not work, as empty strings may be passed. 
#  Such strings should be treated as if nothing was passed.
[CmdletBinding()]
param(
    [Parameter(Mandatory=$True)]
        [string]$server,
        [string]$instanceName,
        [string]$instancePort)

#-----Housekeeping-----
#serverName can be comma delimited. If it is, only take the first in the list
if ($server.Contains(',')) { $server = $server.Split(',')[0] }

# Set default values if nothing was passed in
if ([string]::IsNullOrEmpty($instanceName)) { $instanceName = "DynamicsAx1" }
if ([string]::IsNullOrEmpty($instancePort)) { $instancePort = "2712"        }
#----/Housekeeping-----

Write-Debug "Performing Data Dictionary Sync on $instanceName@$server`:$instancePort"
$args = @("-startupcmd=synchronize", "-aos2=$instanceName@$server`:$instancePort")
Ax32.exe $args | Out-Null

Working on AX Lifecycle Management has shown a lot as far as how the AX infrastructure has been set up. While I’m sure this system may only have limited use in AX 2012 or AX 7 (if any at all), having a formal process has allowed me as a developer to focus more of my time on actual development without having to worry about how it is going to be deployed into our production environments. Having an automated environment update process allows me to track how changes impact the environment more realistically, so code changes are less likely to have problems when they do finally reach production. The built-in audit trail is fantastic for showing to auditors, both internal and external, what happened and when. And while we haven’t had the need to roll back an update it’s nice to know the possibility still exists if it is needed.

Ultimately, the system we have built is a very simple one - easy to understand, easy to explain, easy to show what exactly is going on. But the impact it is making has been huge - mostly because of its simplicity. I hope this series inspires others to implement similar systems for their organizations.

Comment and share

Automated builds and code deployment (Part 5 - Putting it all together)

in AX 2009, Build Process

Read the other posts in this series:
Why Create an AX Build Server/Process?
Part 1 - Intro
Part 2 - Build
Part 3 - Flowback
Part 4 - Promotion
Part 5 - Putting it all together
Part 6 - Optimizations
Part 7 - Upgrades

In our implementation, we use TeamCity to manage each of the individual processes. I’ve discussed how the system not only makes it easier to migrate code through a development process, but also how to update the systems earlier in the cycle (like Dev) to reflect Production without any manual intervention. Both of these tasks benefit by having the entire process pre-scripted, allowing for the smallest amount of downtime so users and developers alike can get back to work as quickly as possible.

This post is now going to bring all the individual pieces together, to make a fully unified Lifecycle Management system. Linking all the processes together is where TeamCity really shines. With Build Triggers and Artifact Dependencies, we can automate the entire process so users don’t have to do anything - the system just does it.

First, let’s go over one new process I have not yet discussed: the data update.

Data Update

Some of our environments (most notably Staging) have a requirement to be updated to match production on a daily basis. The remaining environments do not have to be in a production state every day, but when they are updated they should reflect the closest state to production as possible. This requires the production database to be backed up nightly. To help with this, we created a new build configuration. We did not put it into one of the other configurations because it should be an independent process.

The data update configuration only has one step: Backing up the production database. This is a simple PowerShell script:

BackupDatabase.ps1
1
2
3
4
5
6
7
8
$rootSourceLocation = "\\[Backup Network location]\"
$dbBackupFileName   = "DBBackup.bak"
$serverName         = "[AX SQL Server Name]"
 
$dbBackupFile       = $rootSourceLocation + $dbBackupFileName
 
$query = "BACKUP DATABASE [DynamicsAx1] TO DISK = N'" + $dbBackupFile + "' WITH INIT, NOUNLOAD, NAME = N'DynamicsAx1 Clone Backup', NOSKIP, STATS = 10, NOFORMAT"
sqlcmd -E -S $serverName -d master -Q $query

That’s all there is to it.

Now that we have all the processes defined, let’s examine how we want each of the pieces to execute. To help me visualize what should be happening when, I created this flowchart:

Each box represents one of the processes we have discussed previously. The round spots represent when each process line should begin. It should be noted that we also allow each individual task to be run manually. If anything is started manually, the next process in line will still fire. For example, if I trigger a Flowback: Dev manually, Flowback: Beta will automatically run when it has completed. Likewise, if I run a Promotion: Staging manually, it will be followed by a Build Process and Promotion: UAT.

The numbers in each box represent the total time to run the process (on average). This helps to determine the trigger times by working backwards from when we want the process to complete.

As you can see, we want there to be a data update, staging promotion, and build process every night, and we want a UAT Promotion to occur after every Build process (so we can begin testing the changes immediately). Dev and Beta are both manually triggered by developers, but when we update Dev we want to make sure Beta is updated with it so they have the same base code.

 
Now that we have an idea of how each of the individual tasks relate to each other, we can begin scheduling the tasks in TeamCity, using the triggers section of each build configuration:

Process Trigger Type Description
Flowback: Dev None
Flowback: Beta Finish Build Trigger Wait for successful build in AX Lifecycle: AX Flowback: Dev
AX Data Update Schedule Trigger Cron command: 0 0 00 1,3-7 * (Server Time Zone)
Promotion: Staging Finish Build Trigger Wait for successful build in AX Lifecycle: AX Data Update
AX Build Process Finish Build Trigger Wait for successful build in AX Lifecycle: AX Promotion: Staging
Promotion: UAT Finish Build Trigger Wait for successful build in AX Lifecycle: AX Build Process
Promotion: Production Schedule Trigger Weekly on Sunday at 22:30 (Server Time Zone)
Flowback: Build Finish Build Trigger Wait for successful build in AX Lifecycle: AX Promotion: Production
Promotion: Staging Finish Build Trigger Wait for successful build in AX Lifecycle: AX Flowback: Build

 

The Schedule Triggers mean the process starts at specific times. Because we want to execute the Data Update process 6 out of the 7 days per week, instead of creating one trigger for each day, we just use a simple Cron statement to execute on certain days (in this case, Tuesday through Sunday). It should also be noted that the Data Update starts running at midnight, compared to the Production Promotion, which starts at 11:30pm, so the Data Update schedule must be offset by one day or multiple processes with overlap each other.

The Finish Build Triggers wait for the particular event (in this case, a successful execution of the previous process), and then add themselves to the queue. If you have two processes with the same Finish Build Trigger, it’s more or less random which one will start first, but because our process is linear in nature, we don’t have to worry about that.

One of the nice side-effects about setting up triggers this way is they only run on successful completion. If for some reason the Promotion: Staging fails, nothing after it runs. Similarly, if a Build Process itself fails, UAT won’t be updated with the failed code. We still need to address the failure, of course, but by stopping the process line prematurely, no unnecessary work is done.

I should also note that the Production Promotion process includes a step that is identical to the data update. This is because after a production promotion, we want the Build server to update with the new code and data. However, we only want it to update after a production promotion. If we attempted to chain the Data Update after production promotion, and the build flowback after that, Build would be updated every night, which is not a good thing when we try to accumulate changes over the course of a week. This way, we can make sure Build is only updated once a week, and the data is still updated nightly.

 

Now that everything is scheduled, all that is left is for the actual developer work to finish. This process was established to help optimize the development process we have established internally:

  1. Request comes in, any missing information gathered by Project Manager
  2. Work assigned to developer by Project Manager
  3. Developer makes changes in Dev
  4. Developer loads changes in Beta, reviews with User for functionality testing
  5. User approves functionality testing
  6. Developer loads changes (with version control information) into Build
  7. Developer triggers Build Process. Build + Automatic push to UAT
  8. Project Manager reviews changes with user in UAT
  9. Project Manager accepts changes
  10. Approved changes pushed to Staging nightly
  11. Approved changes pushed to Production weekly

There are exceptions to some of these steps (for example, Beta and the associated user review is normally reserved for large projects with many working parts; in some cases, the user may not even be involved until UAT), but for the most part this is our workflow.

Some of the nice benefits we’ve enjoyed since implementing this include:

  • Increased SOX compliance: the developer cycle (Dev -> Beta -> Build) is independent of the production cycle (Build -> UAT -> Staging -> Production).
  • The code deployment processes are all well-defined and automated, so there is no risk “forgetting” to do a step, like copy version control information.
  • All changes are traceable: If you have a production build number, you can see what the build process number is, and all the changes related to that build. There is also built-in auditing features that state who triggered a specific process, and when.
  • If something goes wrong and you need to rollback to a previous version, it’s as easy as triggering a custom Production Promotion process.

I do hope the information in this series helps others to see the value in implementing a Lifecycle Management process for AX 2009, and gives some ideas on how it can be accomplished relatively quickly and painlessly.

Comment and share

Automated builds and code deployment (Part 4 - Promotion)

in AX 2009, Build Process

Read the other posts in this series:
Why Create an AX Build Server/Process?
Part 1 - Intro
Part 2 - Build
Part 3 - Flowback
Part 4 - Promotion
Part 5 - Putting it all together
Part 6 - Optimizations
Part 7 - Upgrades

In this installment of the Automated Builds and Code Deployment series, I’m going to cover what is probably the most important component of the build process: Promotion.

The Promotion process should be the only way new code leaves one environment and enters another. Our promotion cycle is fairly straightforward: Dev => Beta => Build => UAT => Staging => Production. Projects should hit most, if not all, of these environments and must go through them in that order. We have found that Beta is really the only environment that can be skipped, but should only be skipped for very minor changes (for example, adding a single field from an existing data source on a report).

Again, we are using the Template feature of TeamCity 8.0+ to help manage our configurations. Similar to our Flowback processes, we have a template definition of a variable, Working Directory, which needs to be defined in the implementation of each build configuration.

Our non-production promotion process consists of 7 steps:

  1. Shut down AX Server Process
  2. Copy Build files
  3. Remove AOS temp/cache files
  4. Update AX database
  5. Copy Version Control attributes
  6. Start AX Server Process
  7. Synchronize Data Dictionary

The production promotion process is very similar, with 7 steps, but with some slight changes:

  1. Shut down AX Server Process
  2. Copy Build files
  3. Remove AOS temp/cache files
  4. Copy Version Control attributes
  5. Start AX Server Process
  6. Synchronize Data Dictionary
  7. Backup Production Database

As you can see, the biggest difference is the Production Promotion does not update the database (for obvious reasons), but instead backs it up. I’ll go into more details in my next post, which will bring everything together and outline how each of the pieces interact with each other as a total Lifecycle Management system.

Process Configuration

Each individual process has an Artifact Dependency on the AX Build Process. The process itself should define the criteria for which build it should take. For example:

  • UAT Promotion should take the last successful build
  • Staging and Production should take the last pinned build

During execution, TeamCity will automatically lookup the last build that meets the criteria and download the artifacts that were saved in that build.
Additionally, we have the artifact paths set to

1
BuildFiles.zip!*.*=>AX Build Files

This means the build agent that is running this will automatically extract all the files it finds in the BuildFiles zip file (which is created during the build process) and extract them to a folder named AX Build Files. This path will be referenced in future scripts so we can move the files where they need to go.

Stop AX Server Process

Because we will be manipulating the server binaries, our first step is to shut down the AX server (or servers). Originally, we used a batch script for this step. However, because we cannot specify the timeout, we would sometimes run into issues where the service did not finish shutting down or starting up before the rest of the process occurred. So instead we are using a simple PowerShell script:

StopAxServers.ps1
1
2
3
stop-service -inputobject $(get-service -ComputerName "[AOS Server 2]" -DisplayName "Dynamics AX Object Server 5.0$*") -WarningAction SilentlyContinue
stop-service -inputobject $(get-service -ComputerName "[AOS Server 1]" -DisplayName "Dynamics AX Object Server 5.0$*") -WarningAction SilentlyContinue
stop-service -inputobject $(get-service -ComputerName "[AX Load Balancer]" -DisplayName "Dynamics AX Object Server 5.0$*") -WarningAction SilentlyContinue

As you can see, we are stopping each process sequentially and in reverse order. In reality you can stop the processes in any order. Also, because we are using PowerShell stop-service, the script will naturally wait until the service has finished stopping before moving to the next line. If something causes the AOS Server 2 to not stop at all, AX will still be available because Server 1 and the Load Balancer are still up. The -WarningAction flags will prevent the warning messages (“WARNING: Waiting for service ‘[Service name]’ to finish stopping…”) from showing the TeamCity logs.

Copy Build Files

As mentioned before, the files from the build are automatically extracted to a folder that we can reference. We cannot just extract them to the AX Server folder because the extraction process occurs before the first defined step, meaning the files will be in use. Instead, we will just copy them there now that the server is offline:

CopyBuildFiles.bat
1
2
3
4
5
6
7
8
9
10
11
@echo off
REM Resources
set fileSource="..\..\AX Build Files\*.*"
set fileDestin=\\[server name]\DynamicsAx1\
REM /Resources
 
xcopy %fileSource% %fileDestin% /Y /z
IF ERRORLEVEL 1 ECHO ##teamcity[message text='No files to copy' status='ERROR']
IF ERRORLEVEL 2 ECHO ##teamcity[message text='File copy terminated prematurely' status='ERROR']
IF ERRORLEVEL 4 ECHO ##teamcity[message text='Initialization Error' status='ERROR']
IF ERRORLEVEL 5 ECHO ##teamcity[message text='Disk write error' status='ERROR']

The AX Build Files folder will be in the root build directory, which is two levels up from where the script resides. Additionally, we have the server files shared across the network to the build server, which allows us to update all the files remotely. There is also some generic error handling at the bottom, since xcopy won’t actively throw any error codes if there was an issue.

Remove AOS temp/cache files

This step is another simple script which removes the now old temp and cache files the AX server uses to help make things run faster. If they aren’t removed, the server may continue to use the old code, which could cause issues for uses. These files will be re-built with the new codebase once the first AX server starts up.

RemoveTempFiles.bat
1
2
3
4
5
6
7
8
@echo off
REM Resources
set fileLoc=\\[server name]\DynamicsAx1
REM /Resources
 
del "%fileLoc%\*.alc"
del "%fileLoc%\*.ali"
del "%fileLoc%\*.aoi"

As you can see, I’m only targeting some of the temp/cache files:
ALC = Application Label Cache files
ALI = Application Label Index files
AOI = Application Object Index files

You can additionally remove more files if you like, but only keep to those ending with the letter C or I. You can find more details on what each file extension means at http://www.artofcreation.be/2009/10/27/application-file-extensions/.

Update AX Database

This is only for non-production promotions, and is very similar to the Database Update step of the Flowback processes. We restore the current backup of production into the specific server’s database, and run a SQL update script that points necessary system values to the correct values for the environment.

Copy Version Control Attributes

This is probably one of the more tricky scripts. Because we use the built-in AX MorphX Version Control system, and this information is only entered with the code back in Build, we need a way of bringing the information forward to each system. We use a PowerShell script to manage this process.

Additionally, we have a modification in our system which tells us which the internal build number that the system is running, and when it was originally created/approved:

This information is stored in the database on the SysEnvironment table, and since it directly relates to the version information, we update it during this process. All the information comes directly from TeamCity using the REST API. Additionally, each database server is linked with the previous database server in line (IE, UAT has a link to Build, Staging has a link to UAT, and Production has a link to Staging).

In this case, the script takes a number, which represents the build ID (not to be confused with the build number). This is passed into the script from the TeamCity configuration.

CopyVersionControlInfo.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
param([Int32]$buildId)
 
Write-Host "##teamcity[message text='Loading build information for $buildId' status='NORMAL']"
 
#Load XML assemblies
[Reflection.Assembly]::LoadWithPartialName("System.Xml.Linq") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("System.Linq") | Out-Null
Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue | Out-Null
Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue | Out-Null
#/assemblies
 
#Local vars
$sourceSqlServer      = "[Source DB Server]"
$sourceSqlName        = "[Source DB Name]"
$destinationSqlServer = "[Destination DB Server]"
$destinationSqlName   = "[Destination DB Name]"
#/Local vars
 
$buildInfo = [System.Xml.Linq.XDocument]::Load("http://[TeamCity Build Server Root URL]/guestAuth/app/rest/builds/id:$buildId");
$buildNum = $buildInfo.Root.Attribute("number").Value;
$buildDate = [DateTime]::ParseExact(($buildInfo.Root.Descendants("finishDate") | %{$_.Value}), "yyyyMMddTHHmmsszzz", [System.Globalization.CultureInfo]::InvariantCulture).ToUniversalTime().ToString("s");
$apprvDate = [DateTime]::ParseExact(($buildInfo.Root.Descendants("timestamp") | %{$_.Value}), "yyyyMMddTHHmmsszzz", [System.Globalization.CultureInfo]::InvariantCulture).ToUniversalTime().ToString("s");
 
 
#Update Build information in the environment
$query = "UPDATE SysEnvironment SET BUILDNO = $buildNum, BUILDDATE = '$buildDate', APPROVEDDATE = '$apprvDate'"
Invoke-Sqlcmd -ServerInstance $destinationSqlServer -Database "DynamicsAx1" -Query $query
 
#Pass along Version Control Items table
$query = "INSERT INTO [DynamicsAx1].[dbo].[SYSVERSIONCONTROLMORPHXITE2541]
    SELECT DISTINCT src.*
        FROM [$sourceSqlName].[DynamicsAx1].[dbo].[SYSVERSIONCONTROLMORPHXITE2541] src
        LEFT JOIN [$destinationSqlName].[DynamicsAx1].[dbo].[SYSVERSIONCONTROLMORPHXITE2541] dest
            ON src.RECID = dest.RECID
LEFT OUTER JOIN [$sourceSqlServer].[DynamicsAx1].[dbo].[SYSVERSIONCONTROLMORPHXREV2543] rev
on rev.ITEMPATH = src.ITEMPATH
        WHERE dest.RECID IS NULL and rev.CREATEDDATETIME < '$buildDate'"
Invoke-Sqlcmd -ServerInstance $destinationSqlServer -Database "DynamicsAx1" -Query $query
 
#Pass along Version Control Lock table
$query = "INSERT INTO [DynamicsAx1].[dbo].[SYSVERSIONCONTROLMORPHXLOC2542]
    SELECT src.*
        FROM [$sourceSqlName].[DynamicsAx1].[dbo].[SYSVERSIONCONTROLMORPHXLOC2542] src
        LEFT JOIN [$destinationSqlName].[DynamicsAx1].[dbo].[SYSVERSIONCONTROLMORPHXLOC2542] dest
            ON src.RECID = dest.RECID
        WHERE dest.RECID IS NULL and src.CREATEDDATETIME < '$buildDate'"
Invoke-Sqlcmd -ServerInstance $destinationSqlServer -Database "DynamicsAx1" -Query $query
 
#Pass along Version Control Revision table
$query = "INSERT INTO [DynamicsAx1].[dbo].[SYSVERSIONCONTROLMORPHXREV2543]
    SELECT src.*
        FROM [$sourceSqlName].[DynamicsAx1].[dbo].[SYSVERSIONCONTROLMORPHXREV2543] src
        LEFT JOIN [$destinationSqlName].[DynamicsAx1].[dbo].[SYSVERSIONCONTROLMORPHXREV2543] dest
            ON src.RECID = dest.RECID
        WHERE dest.RECID IS NULL and src.CREATEDDATETIME < '$buildDate'"
Invoke-Sqlcmd -ServerInstance $destinationSqlServer -Database "DynamicsAx1" -Query $query
 
#Update RecID sequences for above tables
foreach ($i in (2541, 2542, 2543))
{
    $query = "UPDATE [DynamicsAx1].[dbo].[SYSTEMSEQUENCES]
        SET NEXTVAL = (SELECT NEXTVAL FROM [$sourceSqlName].[DynamicsAx1].[dbo].[SYSTEMSEQUENCES] src
            WHERE src.TABID = $i)
        WHERE TABID = $i"
    Invoke-Sqlcmd -ServerInstance $destinationSqlServer -Database "DynamicsAx1" -Query $query
}

Each of the queries is run on the destination SQL server, so the information is ‘pulled’ forward. Additionally, it will only take version notes that were created before the current build. This allows multiple builds to be in the system, without the version information being passed upstream.

The biggest risk with this setup is if you need to roll back Build before a production promotion occurs. If you do not load the same elements in the same order, you run the risk of the RecID on the version control tables getting out of sync.

Start AX Server Process

Now that all the database maintenance has been completed, we start up the AX processes again:

StartAxServers.ps1
1
2
3
start-service -inputobject $(get-service -ComputerName "[AX Load Balancer]" -DisplayName "Dynamics AX Object Server 5.0$*") -WarningAction SilentlyContinue
start-service -inputobject $(get-service -ComputerName "[AOS Server 1]" -DisplayName "Dynamics AX Object Server 5.0$*") -WarningAction SilentlyContinue
start-service -inputobject $(get-service -ComputerName "[AOS Server 2]" -DisplayName "Dynamics AX Object Server 5.0$*") -WarningAction SilentlyContinue

Again, this is a PowerShell script, so we can take advantage of the indefinite wait while each process starts up. You may also notice that we start the processes in the reverse order we shut them down. While this is not necessary for everyone, it is something that should be kept in mind.
Our license allows for 2 concurrent AOS servers, and technically an unlimited number of load balancers (since they do not consume a server license). However, when starting up the load balancer, the process is not yet aware that it is a dedicated load balancer, and consumes a license. During a normal startup this license would normally be released in a few seconds. However, since we deleted the cache/index files earlier, and it is the first server process to start, it will rebuild all those files prior to releasing the license.
The end result of all this is that if we do not wait for the load balancer to finish starting up, the second production server (the third in the list) will not start at all.

Also, this step has a slight configuration change compared to the previous steps. All the previous steps are configured to only execute if the build status is successful. This means that if any step fails, the subsequent steps will not run - a helpful feature, especially when the server processes fails to stop. However, this step is configured as “Always, even if a build stop command was issued.” This allows the servers to always come back online, even if the promotion was a failure or manually stopped.

Synchronize Data Dictionary

This step ensures that the database schema matches what the code says it should. Since the database was restored earlier (for non-production environments), this applies any database changes that have been introduced since the last production promotion.

Backup Production Database (Production only)

This step is only in the Production Promotion configuration. I will explain more about why this step is here in my next post, but for the time being the step is relatively simple: backup the production database to a network location. The backup is then used in all the other processes to restore to a production-like status.

And that’s how we run our promotions. Manually promoting is just a button click away, and the total system downtime is minimized. For non-production promotions, the average time to run (from shutdown to completion of the database sync) is about 40 minutes. For the production promotion, average system downtime is about 30 minutes. The total time to run (including the database backup) is about 2 hours. This gives us a lot of flexibility in scheduling our updates with minimal downtime.

Comment and share

Automated builds and code deployment (Part 3 - Flowback)

in AX 2009, Build Process

Read the other posts in this series:
Why Create an AX Build Server/Process?
Part 1 - Intro
Part 2 - Build
Part 3 - Flowback
Part 4 - Promotion
Part 5 - Putting it all together
Part 6 - Optimizations
Part 7 - Upgrades

I’m going to skip what really should be my next post in the Automated Builds and Code Deployment series (which should be about how code is promoted from Dev -> Build -> UAT -> Staging -> Production), and instead write about the reverse process, which I will be referring to as flowback.

Basically, since our environments need to be kept up to date with production, for both code and data, we need to have processes in place that allow us to update those development environments after certain conditions. In our case, we have 2 distinct development environments that need to be updated in this way: Build and Dev. All the other environments should be based on one of those two environments (or based on an environment that is based on those environments, etc).

Because we are still working through our own implementation of automated code deployment, we decided to implement the flowback before the promotion, since it would help with our existing manual promotion strategy. Once the promotion architecture is all set up, we can adjust the flowback processes as necessary, which will be fairly minor.

To manage our flowback processes, we are again using TeamCity. We are running TeamCity 8, and taking advantage of a new feature that was not present in version 7: Templates. Templates are a base build configuration, with steps, triggers, conditions, dependencies, parameters and requirements defined. When you create a new configuration based on a template, you are not able to delete anything that is defined in the template, but you can add additional attributes or change some of them. It is important if you want to use templates that not everything can be overridden. If in doubt, assume the template will override any settings on the configuration.

We’ve hooked our flowback template to be based on the same VCS repository that was mentioned in the previous post. This repository holds all the script files necessary to operate every aspect of the project: Build, Promotion and Flowback. We have it organized so each server has its own folder. If the server has both a promotion and a flowback process (Build in this case), it has a Promotion and Flowback folder as well. This structure will become helpful in a moment.

The flowback process itself is fairly simple, with only 4 steps: Shutdown the AX server process, update the server binaries, restore the SQL database, restart the AX server process.

Our production server still has a few operations that are run outside of the managed build process. Because we don’t want to bring the production server offline every night to copy the binaries, we use a tool that takes a Shadow Copy of the files, and moves them on to a central network location. In addition, the production SQL database is backed up to the same location. Our managed flowback process will use these files to restore the environments.

Here is an example of what one of our steps look like:

You will notice the script name is hardcoded into the step (in this case ‘.\StopAxServers.ps1’). This is where the VCS folder structure comes in. In our template, we define a build parameter:

We do not specify a value on the template itself, we simply declare its existence. When a new configuration is created, it will prompt us for the value of this parameter, and the configuration will not run without it. The value is the relative path of the scripts in relation to the VCS root. So, for our Build Flowback, the value will be ‘Build/Flowback’; for Build promotion to UAT it will be ‘Build/Promotion’; for Dev flowback, it will simply be ‘Dev’. Each flowback process has identically named files, with only the script commands changed as needed to reflect the different environments.

Since each flowback process should be identical except for a couple key variables (namely the destination server name), this makes things much easier to manage. If, in the future, we decide that all flowback processes should have another step, we simply add it to the template, ensure the new script file is in the correct directories and it will automatically show up the next time the configuration is run.

Each step in the template calls a single file:

Shutdown AX Server: StopAxServers.ps1
Update AX Source Files: UpdateAxFiles.ps1
Update AX Database: UpdateAxDatabase.bat
Start AX Server: StartAXServers.ps1

Each UpdateAxDatabase.bat file looks like this:

UpdateAxDatabase.bat
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
@echo off
REM local resources
set sqlServerHost=[Destination SQL Server Name]
set sqlServerCloneScript=Server Clone.sql
REM /local resources

REM remote resources
set rootSourceLocation=[Network Database backup location]
set dbBackupFileName=DBBackup.bak
REM /remote resources

REM calculations
set dbBackupFile=%rootSourceLocation%%dbBackupFileName%
REM /calculations

echo Dropping DB Connections...
osql -E -S %sqlServerHost% -d master -Q "ALTER DATABASE DynamicsAx1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE" -b
IF ERRORLEVEL 1 ECHO ##teamcity[message text='Failed to drop database connections' status='ERROR']

echo Restoring database...
set query=RESTORE DATABASE [DynamicsAx1] FROM DISK = N'%dbBackupFile%' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
osql -E -S %sqlServerHost% -d master -Q "%query%" -b
IF ERRORLEVEL 1 ECHO ##teamcity[message text='Failed to restore database' status='ERROR']

echo Compressing database
osql -E -S %sqlServerHost% -d DynamicsAx1 -Q "ALTER DATABASE [DynamicsAx1] SET RECOVERY SIMPLE" -b
IF ERRORLEVEL 1 ECHO ##teamcity[message text='Failed to set simple recovery mode' status='WARNING']

REM Simple backup recovery
osql -E -S %sqlServerHost% -d DynamicsAx1 -Q "DBCC SHRINKFILE (N'DynamicsAx1_log' , 0, TRUNCATEONLY)" -b
IF ERRORLEVEL 1 ECHO ##teamcity[message text='Failed to truncate database log' status='WARNING']

REM Shrink log file
echo Applying Database Changes...
osql -E -S %sqlServerHost% -d DynamicsAx1 -i "%sqlServerCloneScript%" -b
IF ERRORLEVEL 1 ECHO ##teamcity[message text='Failed to apply database changes' status='ERROR']

REM Execute database change script
osql -E -S %sqlServerHost% -d DynamicsAx1 -Q "ALTER DATABASE DynamicsAX1 SET Multi_user" -b
IF ERRORLEVEL 1 ECHO ##teamcity[message text='Failed to restore database connections' status='ERROR']

REM Enable multi-user in case restore failed

The script is set up to have the items that change (the hostname and file locations) at the top, and use them throughout the rest of the script. Feel free to adjust it as needed. Because of a bug with powershell scripts in TeamCity, it’s difficult to capture any errors that occur. The osql command (with the -b flag) allows us to check the result of the SQL command and issue messages back to TeamCity. Depending on the command, it could fail the build (failing to restore the database will cause an error, but if we can’t shrink the database it’s not a big of a deal so we only issue a warning).

The SQL Clone Script referenced is separate SQL file. The purpose is to fix URLs, user permissions, etc so the server operates correctly. This is a sample of our script (note: This is for AX 2009; this may not work in 2012 because of database schema changes):

SQL Clone Script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
/* Old Server Information */
Declare @P_AOS  varchar(15)
Declare @P_SQL  varchar(15)
Declare @P_SSRS varchar(15)

Set @P_AOS =  '{Production AOS Server}'
Set @P_SQL =  '{Production SQL Server}'
Set @P_SSRS = '{Production SSRS Server}'
 
/* New Server Information */
Declare @AOS  varchar(15)
Declare @SQL  varchar(15)
Declare @SSRS varchar(15)
 
Set @AOS =  '{Development AOS Server}'
Set @SQL =  '{Development SQL Server}'
Set @SSRS = '{Development SSRS Server}'
 

--Remove old server connections ----------------------------------------------------
TRUNCATE TABLE [SYSSERVERCONFIG] 
TRUNCATE TABLE [SYSSERVERSESSIONS]

--Update SSRS Setting---------------------------------------------------------------
Update SRSSERVERS Set [SERVERID] = REPLACE(upper([SERVERID]), upper(@P_SSRS), upper(@SSRS)) 
Where upper([SERVERID]) like '%' + upper(@P_SSRS) + '%'

Update SRSSERVERS Set [SERVERURL] = REPLACE(upper([SERVERURL]), upper(@P_SSRS), upper(@SSRS)) 
Where upper([SERVERURL]) like '%' + upper(@P_SSRS) + '%'

Update SRSSERVERS Set [REPORTMANAGERURL] = REPLACE(upper([REPORTMANAGERURL]), upper(@P_SSRS), upper(@SSRS))
Where upper([REPORTMANAGERURL]) like '%' + upper(@P_SSRS) + '%'

--Update SQL Settings---------------------------------------------------------------
Update [SYSCLIENTSESSIONS] Set CLIENTCOMPUTER = REPLACE(upper(CLIENTCOMPUTER), upper(@P_SQL), upper(@SQL)) 
Where upper(CLIENTCOMPUTER) like '%' + upper(@P_SQL) + '%' 

Update [SYSLASTVALUE] Set [DESIGNNAME] = REPLACE(upper([DESIGNNAME]), upper(@P_SQL), upper(@SQL)) 
Where upper([DESIGNNAME]) like '%' + upper(@P_SQL) + '%' 

Update SYSUSERLOG Set [COMPUTERNAME] = REPLACE(upper([COMPUTERNAME]), upper(@P_SQL), upper(@SQL)) 
Where upper([COMPUTERNAME]) like '%' + upper(@P_SQL) + '%' 
------------------------------------------------------------------------------------
UPDATE SysSQMSettings Set GlobalGUID = '{00000000-0000-0000-0000-000000000000}'
------------------------------------------------------------------------------------
UPDATE [SYSBCPROXYUSERACCOUNT] SET [SID] = 'S-x-x-xx-xxxxxxxxx-xxxxxxxxx-xxxxxxxxxx-xxxxx', [NETWORKALIAS] = '{Development Business Connector Proxy Account}'
where [networkalias] = '{Production Business Connector Proxy Account}'

------------------------------------------------------------------------------------
-------------------USER AND PLUGIN CHANGES------------------------------------------
------------------------------------------------------------------------------------

--Disable all users
UPDATE USERINFO Set [ENABLE] = 0 WHERE ID != 'admin'

--Re-enable all dev users
UPDATE USERINFO Set [ENABLE] = 1 
WHERE ID IN ({User list})
UPDATE USERINFO Set [SID] = 'S-x-x-xx-xxxxxxxxx-xxxxxxxxx-xxxxxxxxxx-xxxxx' WHERE ID = 'admi3'
.
.
.
 
--Set compiler to level 2
UPDATE USERINFO SET [COMPILERWARNINGLEVEL] = 2 WHERE [COMPILERWARNINGLEVEL] > 2
------------------------------------------------------------------------------------

--Assign new permissions as needed
INSERT INTO USERGROUPLIST ([USERID],[GROUPID],[MODIFIEDDATETIME],[DEL_MODIFIEDTIME],[MODIFIEDBY],[CREATEDDATETIME],[DEL_CREATEDTIME],[CREATEDBY],[RECVERSION],[RECID])
VALUES 
     ('{user}','Admin','2012-01-01 12:00:00.000',0,'-AOS-','2012-01-01 12:00:00.000',0,'-AOS-',1,5637148952)
.
.
.
 
------------------------------------------------------------------------------------
--UPDATE SYSVERSIONCONTROLPARAMETERS Set [VCSEnabled] = 1 WHERE KEY_ = 0
------------------------------------------------------------------------------------

In addition to modifying user settings and permissions, we also manage things like 3rd party plugins that should be pointed to different environments, and obfuscate certain information that should only be readily available in production. We also have a modification in our system that allows us to change the background of the windows on a company-by-company basis to easily identify which environment/company you are working in. When this script is run, the color is set to Red across all companies (Dev is Purple, Staging is Green, UAT is orange).

When the server is finally started back up, everything is repointed to the development settings, and it now operates independently of production as it should.

Comment and share

  • page 1 of 1

James McCollum

author.bio


author.job