Migrate on-prem PI AF data with transactional replication

Transactional replication can be used to push a copy of an on-prem SQL PI AF database to one of the following cloud-managed platforms:

• Azure SQL Database

• Azure SQL Managed Instance

• Amazon RDS for SQL Server

Replication provides an option to migrate data without taking a SQL Server database offline.

Steps involved in transactional replication

Both a Publisher (source database) and a Subscriber (target database) are required for transactional replication. During replication, a snapshot is taken of all the database objects and data. There are several parts to the transactional replication process:

1. Distribution setup

2. Publication creation

3. Push subscription configuration

4. Directing AF Server to to the new database

5. Resetting the SQL object tables and removing the push subscription

Changes that are made to data or schema after replication begins will get pushed to the Subscriber in near, real time. For more information, see the Microsoft topics: Transactional replication and Replication to Azure SQL Database.

Distribution setup

The Distributor stores metadata, history and transactions for transactional replication, and must exist prior to publication creation. The Distributor is often configured on the same server as the Publisher, but this setup is not required.

Publication creation

This stage involves creating the publication, selecting the tables to be replicated, setting options to ensure that existing indexes, constraints, default values, and others are included in the snapshot creation, and naming the publication.

Push subscription configuration

In this stage, you set up a push subscription to the cloud-managed platform and initiate replication along with these tasks:

• Specify the accounts used to connect to the distribution agent and the cloud database

• Ensure that jobs created to support replication are running under an account with appropriate permissions to the root snapshot folder

Direct AF Server to the new cloud-managed database

Next, you'll perform the following steps to ensure AF Server gets directed to the new cloud-managed database:

• Stop AF Server

• Modify the AFService.exe.config file to point to the new database

  Definition: The AFService.exe.config file is a standard ADO.NET connection string that defines the location of the Microsoft Azure, Amazon RDS or SQL Server PI AF database, and the security mode used to connect to the database.

  
  This file can be edited to reflect your PI AF database configuration and other settings. It is important that only authorized users have access to this file, as well as the account used to run the PI AF Application Service.

• Restart AF Server

Reset the SQL object tables

Lastly, you will need to perform these steps:

• Confirm replication

• Reset and synchronize the element count in the SQL object tables using the AF Diagnostics (AFDiag) utility

• Remove the push subscription to the cloud-managed database