BusinessPlus Data Refresh Guide
20.11 and later
This page contains detailed steps to complete a data refresh of a BusinessPlus Test Environment from current Production data. The steps make the following assumptions:
Databases are named bpprod, bptest, syscat and syscattest
Connection names are Prod and Test
Pre-Requisites
(Optional) If the syscat database is refreshed, document the Connection Properties from the Test connection.
(Optional) Run the dashboard backup utility. Refer to PowerSource for additional details (requires a login): https://support.powerschool.com/article/88077.
Preserve any data from test prior to beginning this process, such as:
Workflow Models
CDD Reports
User Security Roles
Custom Business Rules
Stop the following services on the Test Application and 7i server(s) using the Windows Service Manager:
App Server: SunGard IPC Daemon Service
7i Server: BusinessPlus Workflow Service
BusinessPlus Data Processing Service
Backup and Restore Database
On the production DB Instance, backup the bpprod database.
Refer to the following for further information on backing up SQL Server databases.SQL Server 2017: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/create-a- full-database-backup-sql-server?view=sql-server-2017.
SQL Server 2016: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/create-a- full-database-backup-sql-server?view=sql-server-2016.
(Optional) On the production DB Instance, backup the syscat database.
Copy database backup files to a location accessible to the Test SQL Instance.
(Optional) On the Test SQL Instance, backup the bptest and syscat test databases.
Restore the bpprod backup to the test database instance using the same database name for the test database, bptest. Refer to the links above for further information on restoring an SQL Server database.
Database Configuration
Delete user mappings from the bptest database
Expand Databases, then expand the bptest database.
Expand Security, then expand Users.
Right-click “bsidba” and click Delete and confirm.
Right-click “BSI” and click Delete and confirm. (Windows Account)
Right-click 7i Job User (typically IUSR_BP or IUSR_IFAS) and click Delete and confirm. (Windows Account)
Delete user mappings from the syscattest database
Expand Databases, then expand the syscattest database.
Expand Security, then expand Users.
Right-click “syscat” and click Delete and confirm.
Add required user mappings for bsidba user
Expand Security, then expand Logins.
Right-click the “bsidba” user and select Properties.
Click User Mappings on the left.
Select bptest database and select db_owner permissions.
Add required user mappings for syscat user
Expand Security, then expand Logins.
Right-click the “syscat” user and select Properties.
Click User Mappings on the left.
Select syscattest database and select db_owner permissions.
Add required user mappings for BSI user (Windows Account)
Expand Security, then expand Logins.
Right-click the “BSI” user and select Properties.
Click User Mappings on the left.
Select bptest database and select db_owner permissions.
Add required user mappings for the 7i Job User (Windows Account)
Expand Security, then expand Logins.
Right-click 7i Job User (ex. “IUSR_BP”,“IUSR_IFAS”) user and select Properties.
Click User Mappings on the left.
Select bptest database and select db_owner permissions.
Run the following SQL against the bptest database to set the appropriate BLOB server for the environment. The serverFQDN is typically the FQDN of a single web server in the Test Environment. This name must align with the installed SSL Certificate.
USE [bptest]
GO
UPDATE us_setting SET value='https://serverFQDN' WHERE subsystem = '@@' AND area = 'BLOB_DATA' AND name = 'LOCATION'Run the following script against bptest database to set all workflow models and workflow schedules to inactive except DO_ARCHIVE, JOB, and REBUILD_SECURITY. This will also update the workflow instance and history tables for the deactivated models.
USE bplustest
GO
UPDATE wf_model SET wf_status ='I' WHERE wf_status ='A' AND wf_model_id NOT IN ('JOB','REBUILD_SECURITY','DO_ARCHIVE','DO_ATTACH');
UPDATE wf_schedule SET wf_status ='I' WHERE wf_status ='A' AND wf_model_id NOT IN (SELECT wf_model_id FROM wf_model WHERE wf_status = 'A' );
UPDATE wf_instance SET wf_status ='H' WHERE wf_status IN ('I','R','A','P') AND wf_model_id NOT IN (SELECT wf_model_id FROM wf_model WHERE wf_status = 'A' );
UPDATE wf_history SET wf_status ='Y', wf_note='Manually approved via SQL' WHERE wf_status in ('P','H') AND wf_model_id NOT IN (SELECT wf_model_id FROM wf_model WHERE wf_status = 'A' );(Optional) If Cognos is used, load the current Cognos Stored Procedure and set the Cognos user permissions
Copy the Cognos_SP.sql to the DB server from the app server. By default, the procedure is located on the app server in C\powerschool\app\update\Cognos_SP.sql but the installation location may vary.
Run the Cognos_SP.sql file in SQL management Studio against the bptest database.
Add required user mappings for the Cognos user in the bptest database, typically crnuser
Expand Security, then expand Logins.
Right-click crnuser and select Properties.
Click User Mappings on the left.
Select bptest database and select db_owner permissions.
Environment Configuration
Start the SunGard IPC Daemon Service on the Test Application Server.
Log onto the Test 7i Web Server, web1, if a farm.
(Optional) Run the dashboard Backup Utility to restore dashboard. Refer to this link for additional details: https://support.powerschool.com/article/88077.
Launch Admin Console.
(Optional) If syscat was refreshed, perform the following. Otherwise, skip to step 5.
Right click the Prod connection and select Edit Properties…
Verify the General tab is selected.
Change the name to: Test.
NOTE: Connection name is case sensitive.
In the Icon section, click the radio button next to Test to change the connection icon.
Click the Database tab.
Change the Host Name appropriately.
Change the Database Name appropriately.
If needed, update the bsidba user information.
Click the Telnet Host tab.
Change the Host Name appropriately.
If needed, update the Telnet User and 7i Job User information.
Click the WWW Servers tab.
Update the URL appropriately.
Click the Workflow tab.
If needed, update the Workflow User, Password, and E-mail.
Best Practice Recommendation: Each BusinessPlus environment should have its own dedicated Workflow email account.
Click OK.
Login to the Test connection in Admin Console as bsi.
Go to 7i Server Admin > Setup > Configure Local Server.
On the Server Config tab click on the Refresh button next to the System Catalog Connection text to refresh the connection.
On the Miscellaneous Settings tab:
Review Password complexity settings.
Verify/Update BusinessPlus Web Servers.
Web Servers should be indicated by both Name and IP address.
EO (Employee Online) Server and Farm (if applicable) should be indicated by IP Address only.
To add servers, enter the FQDN or IP address of the server and click the Add to List button.
To remove servers, highlight the server and click the Remove from List button.
Click the Save Changes button once the current list of BusinessPlus Web Servers is accurate.
Click the Start Service button to start the Data Processing Services. If it is already running, Stop and Start the service.
Start the BusinessPlus Workflow Service on the appropriate BusinessPlus 7i Web Server, typically web1. The BusinessPlus Workflow Service can be started via the shortcut in the B+ Tools folder on the user desktop, or via the Windows Services Manager.
(Optional) If a server farm is used in test, Launch Admin Console on all other webservers and go to 7i Server Admin > Setup > Configure Local Server. Click the Update from Database to pull the list of servers from the database prior to starting the Data Processing Service.
Open the BusinessPlus Test Environment using a supported web browser and log in using a user account with full access.
Navigate to the NUUPAUSY – Audit System Information screen.
Change the Organization Name to: BusinessPlus Test (or another appropriate label).
Change the Long Name to: BusinessPlus Test (or another appropriate label).
Press <Enter> to save the record.
Click on the Close button located in the top right corner of the screen.
Run smoke test. Refer to the following link for smoke test instructions: 20.11 Smoke Test.
(Optional) For BusinessPlus 22.4 or higher using Cognos Reporting, review the Cognos Configuration settings on NUUPCO: Manage Cognos Configuration - NUUPCO.
(Optional) For BusinessPlus 22.4.5 or higher using OAUTH in sending mail, review Configuration Settings in Admin Console.