Install PI AF SQL Server database on the active node in the SQL Server failover cluster using Manual SQL Script Execution

Before you begin, make sure you complete the tasks listed in Pre-installation tasks for PI AF in a failover cluster.

• To run the necessary scripts from the command line, you must first install the sqlcmd utility, available with SQL Server. For information about installing sqlcmd, see the Microsoft topic sqlcmd Utility.

• Use an account that has sysadmin privileges on the SQL Server instance to execute SQL scripts to install a PI AF SQL Server database for a failover cluster.

You do not need to run the PI Server install kit to install the PI AF SQL Server database. You can instead download the SQL Scripts from the customer portal and manually execute these scripts to create the PI AF SQL Server database. Execute the AF SQL scripts against the active node only of the SQL Server failover cluster to create and populate the PI AF SQL Server database (typically called PIFD).

The GO.bat batch file contains the commands that execute the deployed SQL Server scripts. If you used the install kit to step through the installation and only selected the AF SQL database scripts feature, then the scripts and the GO.bat file are located in the %PIHOME64%\AF\SQL folder and must be run manually. If you chose to download the SQL scripts zip file from the customer portal, then the files need to be extracted to a location of your choosing.

Follow these steps to install the PI AF SQL database on the active node in the SQL Server failover cluster using Manual SQL Script Execution:

1. On the active SQL Server Cluster node only, open a command prompt window using the Run as administrator option.

2. Change the directory to the folder that holds the SQL scripts. By default, they are located in the %PIHOME64%\AF folder (for example: cd C:\Program Files\PIPC\AF\SQL).

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

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

   where:

   • <SQLClusterName> is the name of the SQL Server Cluster for 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 during command-line installations and upgrades.

   • <SQLUserName> and <SQLUserPassword> are only needed if mixed mode authentication is required to connect to the SQL Server cluster. Omit these to use Windows authentication. Typically, mixed mode authentication is required when the PI AF SQL Server database and PI AF Application Service are on different, non-trusted domains.

When the process is complete, a message line displays in the command prompt window indicating the SQL script execution is complete. The message includes the location of the SQL Script execution log file.