PowerShell script for synchronizing the database

UPDATE! Just a day after posting this article, I got some valuable feedback that made me rewrite the script. I kept the top part of the post as is, for historical reference, but the new script is below. Keep reading!

In this post I want to share a neat way to use a PowerShell script for running the database synchronization when working. You probably already know you can run the database synchronization from within Visual Studio, and that is probably where most developers and consultants will do this operation, but sometimes you want the option to just run a script. Examples of this is when you copy a database between environments, or during upgrade operations.

Let's put the script out, and I'll discuss the parts below.

#Requires -RunAsAdministrator
Import-Module "$PSScriptRoot\AOSEnvironmentUtilities.psm1" -DisableNameChecking
Import-Module "$PSScriptRoot\CommonRollbackUtilities.psm1" -DisableNameChecking

function Run-DBSync()
{
    $SyncToolExecutable = '{0}\bin\Microsoft.Dynamics.AX.Deployment.Setup.exe' -f $(Get-AosWebSitePhysicalPath)
    $params = @(
        '-bindir',       $(Get-AOSPackageDirectory)
        '-metadatadir' , $(Get-AOSPackageDirectory) 
        '-sqluser',      $(Get-DataAccessSqlUsr)
        '-sqlserver',    $(Get-DataAccessDbServer)
        '-sqldatabase',  $(Get-DataAccessDatabase)
        '-setupmode',    'sync' 
        '-syncmode',     'fullall' 
        '-isazuresql',   'false' 
        '-sqlpwd',       $(Get-DataAccessSqlPwd)
    )
    & $SyncToolExecutable $params 2>&1 | Out-String    
}

Run-DBSync

Let's look at what this script does. The very first line is just a hint to the runtime that this script must be run in elevated mode. The reason is that it must get some information from the system that requires admin rights. Typically I also stop some services, like the Management Reporter Process Service, before I run the synchronization, and obviously a non-admin will struggle to do that.

Notice that I have imported some modules, and you may be wondering where I got those. These PowerShell modules are part of the Software Deployable Packages, and either you can create one yourself, or simply download one of those made available by Microsoft in LCS. Extract the package and look under the following path, \AOSService\Scripts. Just grab the two files and make sure you save them alongside your script, like the example below:

The rest is simply building the parameters for the synchronization operation, and running the tool that does the job. The output is sent to the host, so if you want to look at the result you may want to run this script in PowerShell ISE (Admin mode).

What is also neat, is that it will pick up the database credentials used for your environment, so you don't have to put those details in the script yourself.

In any case, it is a neat study in how you can organize your script in such a way that you get the code all in one visible column. It's also a stepping stone to start building your own set of scripts to maintain your development environments.

Finally, a small disclaimer: Microsoft may very well change how their PowerShell modules work in the future, so if that happens, the script above will have to change.

Updated script - no modules and works for non-admin

So here is a way to run the database synchronization without having to rely on the PowerShell modules and without having to have local admin rights. Remember this is limited to OneBox environment.

function Run-DBSync()
{
     # Find the correct Package Local Directory (PLD)
    $pldPath = "\AOSService\PackagesLocalDirectory"
    $packageDirectory = "{0}:$pldPath" -f ('J','K')[$(Test-Path $("K:$pldPath"))]  

    $SyncToolExecutable = '{0}\bin\SyncEngine.exe' -f $packageDirectory
    
    $connectionString = "Data Source=localhost; " +
        "Integrated Security=True; " +
        "Initial Catalog=AxDb"

    $params = @(
        "-syncmode=`"fullall`""
        "-metadatabinaries=$packageDirectory"
        "-connect=`"$connectionString`""
    )

    & $SyncToolExecutable $params 2>&1 | Out-String    
}

Run-DBSync

Notice how I feed the parameters to the executable here, in comparisson to the Setup tool above. It is currently stated in the docs that you may want to use the Setup tool during upgrade scenarios.

List hotfixes using PowerShell in D365FO (AX7)

You probably already know that you can open Visual Studio and from the "Dynamics 365" menu, under "Addins" and "Apply Hotfix", you will find a grid that lists all the hotfixes installed on your environment. The list can be copied and pasted into Excel if you need a better view and you need to filter and search the list. It works, but it could be a bit easier.

In this post I will share a neat function you can use to list installed hotfixes using PowerShell. It inspired by the post from Microsoft Support (Thomas Treen), and I got some help by some of my fellow MVPs to get inspired (shout out to Martin Draab and Lane Swenka).

The function is as follows:

function Get-HotfixList()
{
    # Find the correct Package Local Directory (PLD)
    $pldPath = "\AOSService\PackagesLocalDirectory"
    $packageDirectory = "{0}:$pldPath" -f ('J','K')[$(Test-Path $("K:$pldPath"))]  
    
    [array]$Updates = @()

    # Get all updates XML
    foreach ($packagefile in Get-ChildItem $packageDirectory\*\*\AxUpdate\*.xml)
    {
        [xml]$xml = Get-Content $packagefile                         
        [string]$KBs = $xml.AxUpdate.KBNumbers.string

        # One package may refer many KBs
        foreach ($KB in $KBs -split " ")
        {
            [string]$package = $xml.AxUpdate.Name
            $moduleFolder = $packagefile.Directory.Parent

            $Updates += [PSCustomObject]@{
                Module = $moduleFolder.Parent
                Model = $moduleFolder
                KB = $KB
                Package = $package
                Folder = $moduleFolder.FullName
            }
        }
    }
    return $Updates
}

With this function, you can list out the hotfixes to a resizable, sortable and searchable grid like this:

Get-HotfixList | Out-GridView

You can list out the hotfixes into a long string where each KB number is separated by a space. Then copy this string into LCS when searching for KBs you want to use in a Hotfix Bundle.

$list = Get-HotfixList | select KB | sort KB
$list = [string]::Join(" ", $list.KB)
$list

Obviously you can use the function to quickly search for a specific hotfix.

Get-HotfixList | where {$_.KB -eq "4055564"}

And one final example, when installing a hotfix bundle, one of the steps are to compile the modules patched, and while you can do a full compile of all modules in the application, you could also just compile only the ones patched. To create a distinct list of modules, run the following statement.

Get-HotfixList | select module | sort module | Get-Unique -AsString

A quick note on the Package Local Directory (PLD) path. In my script I shift between K and J drive. I have only used this script on VMs in the cloud. If you need to run this where the PLD path is on some other drive, you will need to change that in the script.