Upgrade Guide › Upgrading Databases › Upgrading a 6.0 Database › SQL Server: Post-Upgrade Change To Event Participant Table › 1. Run the Post-Upgrade Pre-Flight Check

1. Run the Post-Upgrade Pre-Flight Check

Before you actually update the Wgn3EventParticipant table so that it does not accept null Address UIDs, you must run a post-upgrade pre‑flight check script. (Do not confuse this pre-flight check with the pre-upgrade pre-flight check!) This post-upgrade pre‑flight check is standalone and only exercises a small data export test. It does not update any data in the database.

We strongly recommend that you run the pre‑flight check one or two days before the actual database upgrade. This gievs you time to fix any problems uncovered by the script without disrupting your upgrade schedule.

What Does the Pre-Flight Check Do?

The pre‑flight check script:

• Verifies that all required permissions are set (for example, bcp permission to write to the target directory).
• Verifies that the target folder exists.
• Verifies that sufficient free disk space is available in the target folder.

The pre‑flight check also estimates how long the table updates will take. The time estimate is based on a sample of one million table rows. Using the 'bcp' utility, the pre-flight check script copies a maximum of one million rows from the table to the file system, measuring the time and disk space required to do so. Using these figures, it then calculates the disk space required to copy out the entire table and estimates how long this will take.

To run the pre-flight check.

1. Locate the pre-flight check file in your CA DLP distribution media.

   The post-upgrade pre-flight check is implemented as a stored procedure (SP), contained in the following file:

   wgn_post_upgrade_pfc.sql
   

   Find this file in the following folder:

   \Support\Upgrade12\MSSQL
   

2. In SQL Server Management Studio, open and execute this file to create the SP.
3. Run the following command as a login with the sysadmin role (we recommend sa). Note that you must use the same login when you actually modify the table (see step 1 in section 4, Run the Modify Table Script).

   EXEC dbo.wgn_post_upgrade_pfc '<dir>'
   

   where <dir> is the target folder where participant records will be copied to. You must specify a folder on the database host server (this is not necessarily the same as the CMS host server). This folder must already exist. For example:

   EXEC dbo.wgn_post_upgrade_pfc '\\unxsvr1\particpants\MyFolder'
   

   Important: Note the following requirements for the target folder!

   • Specify the target folder as a local path or UNC path. You cannot specify a mapped network drive letter (such as 'Z:\MyDrive').
   • Do not include a trailing backslash.
   • If the target folder is on a clustered SQL Server system, this folder must exist on all cluster nodes.
   • The Windows account that xp_cmdshell runs as must have Read/Write access to this folder.

     If xp_cmdshell runs as the SQL admin user, this account is the MSSQL service account.

     If xp_cmdshell runs as a different user, this account is the configured ##xp_cmdshell_proxy_account##.

   • You must specify the same target folder for the pre-flight check and the actual table update. This ensures that the time estimate, permissions check, and disk space check are meaningful.
   • For performance reasons, we strongly recommend that this directory is on a separate disk from the actual database.
4. Check the script output in the Messages pane in SQL Server Management Studio.

   Note: By default, the Results pane displays. Switch to the Messages pane to view the output.

   This output indicates whether the necessary conditions have been met and estimates the time needed to update the table. In particular, the script output:

   • Confirms whether the AddressUID columns are currently defined as NULL.
   • Confirms whether the xp_cmdshell feature is enabled and the specified login has permission to use it. The table modification script will use this feature to run bcp commands when copying participant records between the database and the target directory.
   • Shows the actual bcp command used by the pre‑flight check to copy table records to the target directory. To help diagnose problems, the bcp output is also piped to a log file, bcp_pf_ep_data.log.
   • Indicates whether there is sufficient free disk space in the target directory to store all the participant records.
   • Estimates how long the entire procedure will take to modify the Wgn3EventParticipant table, or the script indicates that bcp command failed..

More information:

Pre-Flight Check Output

Example 1: Pre-Flight Check All OK

Example 2 Pre-Flight Check: CHECK Failed