In this video, we are going to go over the procedure for migrating the PI AF
SQL database from one machine to another. The PI AF SQL Database is one of two components of
the PI AF Server. The second component is the PI AF Application Service which we will migrate
in a later video. Let's say you have a simple architecture such as this one where the AF Server
and SQL Server are both installed on the same machine, PIAF01. You would like to migrate the
AF SQL database also known as PIFD by default to a different SQL Server, SQL-NEW. The first thing
you need to do is verify that SQL Server 2017 is compatible with PI AF Server 2018 SP2. You can do
this by checking the SQL Server requirements section of the AF Server release notes.
In this case, the minimum SQL Server version is 2008 so we can go ahead and do the migration.
To migrate the PIFD database, we will need to: Take a full backup of it on PIAF01.
Restore the database backup on SQL-NEW. We'll re-create the AFServers local group on SQL-NEW
and add service account under which the PI AF Application Service is running to it.
We'll re-run the GO.BAT against the restored PIFD database. This is to recreate the instance level
objects such as maintenance and backup jobs. The GO.BAT script will also create a login for
the AFServers local group and assign it all the necessary permissions with the database.
Finally we'll update the SQL connection string in the AFService config file on the AF Server.
Then we'll restart the PI AF application service so that it can connect to the new SQL Server.
Before taking a backup, we need to stop the PI AF Application Service to ensure that no more changes
are made to PIFD database. I'll go to services.msc and stop the service.
Alright! let's open SQL Server Management Studio. Expand databases. Right-click on PIFD,
Tasks and select Backup. We need to verify that the backup type is "Full" and select a destination
for the backup file. I'll leave the default location but if you want to store the backup in
a different location, you can click the "Add…" option and navigate to the desired directory.
Alright! We're ready to take our backup! I'll go ahead and click "OK".
Okay! The execution has been successful.
Let's verify that we have a good Backup and we can copy it over to the new machine.
I have copied over the backup file from PIAF01 to SQL-NEW and I'll restore it using
SQL Server Management Studio. The instance we want to connect to here is SQLEXPRESS.
Right click on Databases. Restore Database.
Select Device and we'll go and find the backup file which is stored in c:\PIFD Backup folder.
I'll leave the database name as PIFD but if you wanted to, you can change the database name to
something else in this field. Let's go ahead and click OK to start the restore process.
Alright! Looks good. The backup restoration process has been completed successfully.
Normally, the AFServers local group is created by default when running the PI AF Server installation
kit if the user running the installation kit has local admin privileges on the SQL Server machine.
The installation kit also adds the account under which the PI AF Application Service is running
to the AFServers group. Since we are only migrating the SQL Database and not running
the PI AF Server installation kit, we will need to manually create the AFServers local group.
If you do not wish to use the AFServers local group to grant access to the PI AF Application
Service, you can create a login directly for its service account and map it to db_AFServers.
To create the AFServers local group, we can go to computer management
and right-click the white space under groups to create a new group. We'll call it AFServers.
And add the following description. I will now add the machine account for PIAF01
as the AF Application Service is not running under a service account.
Before running the script, we can verify from SQL Server Management Studio that there is no login
for the AFServers group. I'll run the following script from
command prompt to execute the GO.BAT script against the PIFD database.
The scripts have successfully executed. Let's go to SQL Server Management Studio to verify
that there is now a login for the AFServers group and that it is mapped to db_AFServers.
We're back on PIAF01 and we're going to update the connection string with the new SQL Server name.
We'll store that change and restart the PI AF Application Service. Finally, we need to open PI
System Explorer to make sure that everything works fine and that we can connect to AF.
Alright! Everything looks good. We can see that we're successfully connected to AF
and our migration is now complete.