Using Azure Automation to copy Live Transaction DB to Test

Updating the Test system with actual transactional data from the Live system is a common task. This can be accomplished using scripts. However, in a hybrid IT environment you might want to organize, manage and monitor all your on-premises and cloud based scripts at a central place. Azure Automation is the platform to do this.

Prerequisites

  • Dynamics AX Live and Test installation on Windows Server 2012 R2
  • Azure Account (all services can be used for free in basic mode)

 

          Azure Automation

        In Azure Portal create a new instance of Azure Automation. When the instance was created, use the key to display the keys and URL. You’ll need this to connect your on-premises server with Azure Automation.

      Azure Automation

        Operational Insights

        Azure Automation can be instantly used to script your cloud-based datacenter. However, to manage the on-premises environment you have to connect your local systems with the cloud. This is done via Operational Insights.

        1. In Azure Portal create a new instance of Operational Insights
        2. Logon to Operational Insights Portal and start the “Get Started” checklist
        3. Add the Automation Solutions
        4. Connect the Dynamics AX server by downloading and installing the Agent for Windows

        Operational Insights

        At your on-premises server open PowerShell in admin mode and go to the agents installation directory e.g.

        cd "C:\Program Files\Microsoft Monitoring Agent\Agent\AzureAutomation\<version>\HybridRegistration"

        Import the Hybrid Registration module and register the server in Azure Automation.

        Import-Module HybridRegistration.psd1
        Add-HybridRunbookWorker –Name <String> -EndPoint <Url> -Token <String>

        Add-HybridRunbookWorker

        In this example I’ve used the Name parameter value “Dynamics” at the Add-HybridRunbookWorker Cmdlet. This creates a runbook worker group called “Dynamics” with one assigned on-premises server.

        On-Premises configuration

        Create a folder where to backup the Dynamics AX database. In this example I’m using a directory on the local system drive C:\AxTemp (which of course is not best practice). By default the Ops. Insights Agent runs as Local System. Make sure to give the account “NT Authority\System” appropriate rights in your SQL Server installation to access the Live DB and Test DB.

        SQL Server Security

        Runbook

        In Azure Portal, go to Azure Automation > Runbooks > Create a new runbook > Choose runbook type “Powershell”. Add the following code to your runbook.

        echo "Stopping Services"
        Stop-Service -Name AOS60`$01
        Stop-Service -Name AOS60`$02

        echo "Creating Backup from Live"
        Invoke-Sqlcmd -Query "backup database [Live] to  Disk = N’C:\AxTemp\Live.bak‘ with copy_only" -QueryTimeout 0   


        echo "Restoring Backup to Test"
        Invoke-SqlCmd -Query "restore database [Test] from  DISK = N’C:\AxTemp\Live.bak‘ with file = 1,  move N‘R3Demo1‚ TO N’C:\Data\Test.mdf‘,  MOVE N‘R3Demo1_log‚ TO N’C:\Data\Test_log.ldf‘" -QueryTimeout 0   
           
        echo "Cleanup Backup"
        Remove-Item -Path "C:\AxTemp\Live.bak"   
           
        echo "Starting Services"
        Start-Service -Name AOS60`$01
        Start-Service -Name AOS60`$02

        This will stop both AOS instances. Make sure the AOS service name fits your environment. The service name can be found in the services mmc. For example my Live AOS is named AOS60$01. Make sure to use the ` to escape the $ sign in the AOS name.

        Dynamics AOS name for scripting

        The script will then create a backup from database “Live” to C:\AxTemp\Live.bak. If your live DB has another name, change it to fit your name.

        Next the script will restore the backup to database “Test”. In my case the logical name of the database file is called “R3Demo1” and it’s log is called “R3Demo1_log”. Make sure this fits your installation. You can find the names in SQL Server management studio, by checking the database file properties.

        SQL Server database logical name

        Finally the script removes the backup file and restarts both AOS

        Runbook Execution

        In Azure Automation Runbook editor, save the actual runbook code and click publish.

        Runbook execution

        At the Azure Automation main page go to runbooks, select your newly created runbook and press the start button. On the next page select execution using a Hybrid Worker and select your worker group. In my example it’s called Dynamics (see PowerShell screenshot)

        Runbook execution

        This will submit your runbook to the on-premises server and execute it. You can check the execution by monitoring the C:\AxTemp directory where the backup will be placed. Don’t worry about the warnings when the workbook finishes. Starting the AOS’ takes a while and results in the typical message “service is not responding”.

        Runbook result

         

        More

        • Azure Automation supports timing of runbooks, so you can create a batch in the cloud to copy your data on-premises
        • This example uses a single server installation. However, the script can easily be modified to run on different servers
        • Using the Invoke-SqlCmd Cmdlet you can do all the cleanup work like changing the reporting server instance etc.
        Advertisements

        Über erpcoder
        Software Engineer and Dynamics AX enthusiast working in Research & Development for InsideAx

        Kommentar verfassen

        Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

        WordPress.com-Logo

        Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

        Twitter-Bild

        Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

        Facebook-Foto

        Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

        Google+ Foto

        Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

        Verbinde mit %s

        %d Bloggern gefällt das: