Introduction

Have you ever found it annoying to manually update the On-Premises Data Gateway every month? In this blog post, I’ll show you how to automate the process and save time.

In the modern world, where security issues can occur every time, patching has become one of the most important actions of any IT guy. So what you see is that normally automated patching & testing is included in every life cycle management. For some reason, the Microsoft On-Premises Data Gateway (OPDG) doesn’t have an auto-update function. Which means that every month, an IT admin has to sign in to the virtual machine and update the ODPG by hand.

In this blog I will describe how you can automate this process via a simple script.

Steps to update the On-Premises data Gateway

To update the OPDG, the following steps can be followed:

• Download the latest version of the OPDG
• Run the installation

And in case of High Availability, do this for all the OPDG’s and stop the instances one by one.

This is the process that we are going to automate. This will be done via built-in windows server tools to reduce complexity.

Install the On-Premise Data Gateway

If you have already installed a OPDG, you can skip these steps.

First, we will install the On-Premise Data Gateway on the virtual machine. For this blog we will install an older version so that we can test the auto-update functionality. The older versions can be found here: https://learn.microsoft.com/en-us/data-integration/gateway/service-gateway-monthly-updates. Once you download is completed you can start the installation:

1. Check the box to accept the terms of use and privacy statement
2. Click Install

Next you can sign in to Microsoft with your admin mail address (or UPN if you have an admin account without an email address).

Hit Next and fill the name & recovery key of the gateway and click Configure. Make sure you save the recovery key in a secure place in case of emergency. The gateway is now installed and configured. You can see that the version is outdated and that there is an update available.

Configure the auto updating for the on-premises data gateway

Now that the OPDG is installed the auto update can be configured. To do so we will first create an PowerShell script and store it on the hard-drive. In this case, we will store it as c:\scripts\update-opdg.ps1. The code will do the following:

1. Download the latest version from the Microsoft Website
2. Stop de OPDG (This will temporay disable OPDG functionality)
3. Start the installation and wait 30 seconds
4. Start the OPDG

So let first create the PowerShell file. Create a new file in c:\scripts\update-opdg.ps1 and paste & save the following code in it:

# Download the latest version of the OnPremise Data Gateway, and store it locally
Invoke-WebRequest -Uri "https://download.microsoft.com/download/D/A/1/DA1FDDB8-6DA8-4F50-B4D0-18019591E182/GatewayInstall.exe" -OutFile "C:\temp\OnPremisesDataGatewayInstaller.exe"

# Stop the OPDG to avoid unexpected disruptions
Stop-Service "On-premises data gateway service"

# Start the installation, and wait for 30 seconds
Start-Process "C:\temp\OnPremisesDataGatewayInstaller.exe" -ArgumentList "/Silent /InstallAsService"
Start-Sleep -Seconds 30

# Start the OPDG
Start-Service "On-premises data gateway service"

The next step is to create a Scheduled Task which will run at a specific date/time during the maintenance windows. For this follow these steps:

1. On the server, start the Task Scheduler
2. Go to Task Scheduler Library and click with the right mouse button on Microsoft
3. Select Create Basic Task
4. Provide the name (i.e. Update-OPDG) and click Next
5. Select when you want to run the task and click Next
6. Fill in your schedule, click next and select
7. Select Start a program and click Next
8. In program fill in powershell
9. for arguments fill in: c:\scripts\update-opdg.ps1
10. Click Next & Finish
11. Double click on the created task, and make sure you select Run whether use is logged on or not and click OK.
12. Fill in the username & password and click OK

The scripts is now created and scheduled. The OPDG will now be automatically updated during your schedule and you have one less thing to worry about as an IT admin.

Final remarks

This script can help you in automating your application patching for the On-Premise data gateway. If you face this issue, there might be other alternatives which are even better but are a bit more complex and dependent on other tooling. One option I want to highlight is the VNET Data Gateway.

The VNET Data Gateway: The VNET Data Gateway is an gateway service same as the On-Premises data gateway, but fully managed by Microsoft. At the moment of writing (September 2023) this is still in preview. This will require a dedicated subnet & premium capacity, but it will remove the need of (multiple) servers for your hybrid connection. For more information see: What is a virtual network (VNet) data gateway (Preview) | Microsoft Learn.