Create the PI AF SQL Server database manually

You can create the PI AF SQL Server database by running the AF SQL scripts from the command line.

To run the AF SQL scripts, you must be authenticated. The default approach is Windows authentication. It requires no special action on your part. The other approach is to authenticate directly to SQL Server, which requires you to provide credentials when you execute the SQL scripts.

Note: If you plan to use Azure SQL Database or Azure Managed Instance to deploy and manage your PI AF database, you will need to edit the GO.bat batch file and set the preferred authentication method for connections. See Set the authentication method in GO.bat for Microsoft Azure.

Prerequisites

• Before you begin, install the sqlcmd utility to run the necessary scripts from the command line.

• For information about installing sqlcmd, see the Microsoft article sqlcmd Utility.

1. Open a command prompt window.

2. Use the cd command to switch to the %PIHOME64%\AF\SQL folder.

   Note: The AF SQL scripts are placed in the %PIHOME64%\AF\SQL folder during a PI AF installation.

3. Use the following syntax to execute the AF SQL scripts:

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

   where:

   <SQLName> is the name of the SQL Server that hosts the PI AF SQL Server database.

   \<SQLInstanceName> is the SQL Server instance. This value is only necessary if the SQL Server you are accessing is 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 beginning with release 2018 SP2 you can assign any name to this database.

   <SQLUserName> and <SQLUserPassword> are the login credentials for SQL Server. Specify these values if SQL Server authentication is required. If these values are not provided, the scripts use Windows authentication to connect to SQL Server.

   Here are examples of the syntax needed to execute the scripts:

   • Syntax using SQL Server authentication:

     GO.bat "MySQL\MyInstance,4452" PIFD-2018 MySQLLogin MySQLLoginPwd

   • Syntax using Windows authentication:

     GO.bat MySQL PIFD

   • See Set the authentication method in GO.bat for Microsoft Azure for syntax examples of Microsoft Entra and other authentication modes used with Azure SQL connections.

The process is complete when the command line looks like:

PI AF and PI AF SQL Service SQL script execution completed successfully.
Log file:
"C:\Program Files\PIPC\AF\SQL\PIAFSqlScriptExecution_ 2016_09_15_14_33_52.txt"

C:\Program Files\PIPC\AF\SQL>_

If you run the go.bat command as an Administrator, the SQL script execution log file is saved in the same folder as the SQL scripts. Otherwise, the log file is saved in the %Temp% folder.

The log file is a .txt file that uses the following naming convention : PIAFSqlScriptExecution_[year]_[month]_[day]_[hour]_[min]_[sec].txt

See Modify the AFService.exe.config file to learn more about specifying the location of the PI AF SQL database and the security mode used to connect to the database.