Move the PI AF SQL Server database

Before you begin this procedure, make sure the following criteria is met:

• The version of PI AF server you are using supports both the new computer's operating system version and the version of Microsoft SQL Server installed on that computer. For more information, see Pre-installation requirements for PI AF.

• Be aware that Microsoft does not allow you to restore a SQL Server database from a more recent version of SQL Server to an older version of SQL Server. A step in this procedure requires you to restore a SQL Server database. For more information see this Microsoft documentation: Restore a Database Backup Using SSMS.

• You have sysadmin privileges. A step in this procedure requires you to run a script for which you must have elevated privileges.

If you want to move PI AF server to a new computer, there are two tasks you may need to perform:

• Move the PI AF SQL Server database to a new computer, as described in this procedure.

• Move the PI AF application service.

1. On the computer where PI AF is installed, stop the PI AF application service to ensure no more changes are being made.

   1. Using Server Manager, select Local Server and then select Services.

   2. Right-click PI AF Application Service and select Stop Services.

2. Create a backup of the current PI AF SQL Server database.

   1. Open SQL Server Management Studio and connect to the existing SQL Server instance.

   2. Expand Databases.

   3. Right-click PIFD (or whatever you have named the PI AF SQL Server database) and select Tasks > Backup.

   4. For Backup type, select Full.

   5. For Backup to, select Disk and specify an appropriate file path for the backup.

   6. Click OK.

3. Move the backup file from the existing SQL Server instance to a new SQL Server instance.

4. Restore the backup to the new SQL Server machine.

   Note: If you are restoring a backup that includes audit trail information, skip this step and run a RESTORE command that includes the KEEP_CDC option. To run that command, use Microsoft Transact-SQL (T-SQL). For more information, see this Microsoft documentation: RESTORE Statements (Transact-SQL). If the backup does not include audit trail information, perform the sub-steps described below.

   1. On the new computer, open SQL Server Management Studio and connect to the new SQL instance.

   2. Right-click Databases and select Restore Database.

   3. Select Device and browse for the backup file.

   4. Under Destination, for Database, specify the name of the database being created.

      If there is already a database with the same name, you can either:

      • Specify a new name for the database.

      • Specify the existing name, select the Options page, and select Overwrite the existing database (WITH REPLACE).

   5. On the General page, under Backup sets to restore, select the most recent backup set.

   6. Open the Files page and confirm where the database files are being restored.

      We recommend you keep the file names so they correspond to a standard installation. For example, restore PIFD to PIFD.mdf, DATA1 to PIFD_DATA1.ndf, and so forth.

   7. Click OK to begin the restore process.

5. Run GO.BAT against the new database to recreate instance level objects, such as maintenance jobs, backup jobs, and so forth.

   Note: The GO.BAT batch file is only present on a computer if you have installed the PI AF service. If you have not installed the PI AF service, you can run GO.BAT remotely from another node where the PI AF service is installed or you can download GO.BAT and the required SQL scripts from the Customer Portal. If you have to search the website for the scripts, use the Product Content Downloads tab and enter a search term of "PI AF SQL Scripts."

   1. Ensure that you have sysadmin privileges.

   2. Open a command prompt window on the computer where you want to run GO.BAT.

   3. Change the directory to the folder that holds the SQL scripts. For example:

      cd C:\Program Files\PIPC\AF\SQL

      The scripts reside in the %PIHOME64%\AF folder.

   4. Use the following syntax to execute the SQL scripts found in the SQL folder:

      GO.bat "<SQLName>[\<SQLInstanceName>][,<Port>]" <PIFD>

      where:

      • <SQLName> is the name of the computer hosting the PI AF SQL Server database.

      • \<SQLInstanceName> is the SQL Server instance. This value is only necessary if SQL Server is installed with a non-default (or "named") instance. For example: MySQL\MySQLInstance.

      • <Port> specifies a port number. If you are specifying a port number, enclose the entire string in double quotes. For example: "MySQL,4452" or "MySQL\MySQLInstance,4452".

      • <PIFD> is the name of the PI AF SQL Server database. By default the name is PIFD, but starting with release 2018 SP2, you can assign any name during a manual installation or upgrade.

6. Establish the appropriate PI AF SQL database permissions for the PI AF application service login account.

   1. Open SQL Server Management Studio and connect to the new SQL instance.

   2. If you do not already have one, create a login for the PI AF Application Service login account.

      Note: A default AF installation creates a login for a local AFServers group, but you may want to determine for yourself how to set up the login.

   3. Open the properties for the login you just created and map the login to the PI AF SQL Server database.

   4. Select the PI AF SQL Server database and assign a database role membership of db_AFServer.

   5. Click OK.

7. Configure the PI AF application service to look at the new SQL Server instance.

   1. On the PI AF application service computer, navigate to the AF directory and open AFService.exe.config in a text editor such as Notepad.

   2. Find the connectString entry and adjust its value to reflect the name of the host computer for the new SQL Server instance and/or the new database.

   3. Start the PI AF application service.

8. Test connectivity between a PI AF server and a PI AF client, such as PI System Explorer.