Upgrade the PI AF SQL Server database

Before you begin this procedure, make sure to complete the following tasks:

• Install the ODBC version of the sqlcmd utility. For information about installing the ODBC version, see the Microsoft article sqlcmd Utility.

• Ensure you have db_owner privilege to upgrade the PI AF SQL Server database. Contact your SQL administrator for assistance.

You upgrade the PI AF SQL Server database with AF SQL scripts. Execute the scripts from the SQL folder, using either SQL Server or Windows authentication:

• Using SQL Server authentication

  The following command uses SQL Server authentication to access the SQL Server, and includes an instance name and a PI AF SQL database named PIFD-2018:

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

  Use double quotes when specifying the SQL Server instance and port number: "MySQL\MySQLInstance,4452". If only specifying the instance name, double quotes are not required .

• Using Windows authentication

  The following command uses Windows Authentication to access the SQL Server. The command does not include an instance name but it does specify a PI AF SQL database named PIFD-2018:

  GO.bat MySQL PIFD-2018

1. Ensure that the PI AF application service is stopped.

   Note: Data loss and corruption could occur if you do not stop the PI AF Application Service while the PI AF SQL Server database is being upgraded.
   
   If you do not restart PI AF application service before proceeding with the installation, some services such as PI Notifications Service and PI Analysis Service will not install successfully.

   1. Click Server Manager > Local Server > Services.

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

2. Open a command prompt window. Use the following syntax to execute the SQL scripts found in the SQL folder:

   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.

   As part of the SQL Script execution, a backup of the PI AF SQL Server database is generated, along with a log file containing the details of the SQL Script execution.

   The PI AF SQL Server database is saved in the default backup location for the SQL Server instance. The backup file uses the following naming convention: PIAFSqlBackup_[year]_[month]_[day]_[hour]_[min]_[sec].bak

   The process is complete when the command line looks like the following example. The file path shown assumes the installation occurred in the default location on a 64-bit computer:

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

3. Restart the PI AF Application Service.

   1. Open the Services administrative tool on the PI AF server computer.

   2. Right-click PI AF Application Service and select Start.