Upgrading v6.9.5 - Database Upgrade

« Return to Notes Listing

Author:Aaron S.
Last Updated:September 02, 2021 2:12 PM

Overview

The steps below are the database upgrade. The goal of this section is to upgrade the database and export all unique database objects for the base version that need to be kept in 6.9.5 and to make sure unique app var are properly scripted. 

 

Restore db backup locally

  1. Restore a copy of production legacy database over your working database using M:\_tpi\LegacyUpgrade\DatabaseUpgrade\RestoreDB.ps1 
    1. If there is not a current backup of the prod db, first go the prod server (host-prod-1) and take a backup of the db. Copy the backup to: \\sql-dev-1\DatabaseRefresh.
    2. The below example shows the default values.

      TIP: in some cases these values may need to be changed. This script also restores the database and sets the current sql version which may not be correct for 6.9.5 in the restored version.  
    3. .\RestoreDatabase.ps1 
Enter client ID (e.g. "NWS", "MCW", "MPS", etc.): [client ID] 
Enter system ID (leave empty for "Public"): 
Please enter server (leave blank for localhost\mssql2016): 
Enter full path, relative to SQL server for backup file (leave blank for 'Q:\DatabaseRefresh\ECE_Titan_Public-For-Refresh.bak'): [path to backup] 
Enter Public Application user from source database (leave blank for 'ECE_Titan_PublicUser'): 
Enter Public Application user for restored database (leave blank for 'ECE_Titan_PublicUser'): 
Enter Admin Application user for restored database (leave blank for 'ECE_Titan_PublicAdmin'): 
Please enter user id with sysadmin role (leave blank for Windows auth): 
Restoring 'ECE_Titan_Public' from .  Continue (Y/N)?: Y

 

Export restored db objects

Next, we need to extract all of the database objects from the production database so that we can compare them to the base set of objects. This will show us which base objects have been overridden.We do not trust that all overrides have been properly recorded in the SDK.

  1. Run export database objects script against restored version \[client]\Titan\Public\Deployments\Export-DatabaseObjects.ps1. You will need to change export script at line 88:. You are going to add /useFilePathHash:$false to the end of the command line (as shown below) 
    & $PackageUtilityExe /mode:CreateDatabaseObjectFiles /filePath:$DatabaseObjectsPath /deleteExisting:true /cmsDatabaseName:$DatabaseName /cmsDatabaseConnectionString:$DatabaseConnectionString /config:$PackageUtilityConfigFile /useFilePathHash:$false
  2. Compare the exported objects to base versions located at M:\_tpi\LegacyUpgrades\DatabaseObjects\v6.x.0.0 where X is the current production version for the client
  3. Remove any LOCAL files that are the same as base. These files clearly have no overrides.
  4. Remove /useFilePathHash:$false parameter from Export-DatabaseObjects.ps1
  5. Commit 

 

Upgrade restored db to v6.9

  1. Run upgrade legacy DB script against restored production database M:\_tpi\LegacyUpgrades\DatabaseUpgrade\UpgradeLegacyDB.ps1. The below examples uses the default values. 
    1. This is only necessary if the instance is less than 6.9.0.0 
    2. .\UpgradeLegacyDB.ps1 
Enter client ID (e.g. "NWS", "MCW", "MPS", etc.): [client ID] 
Enter system ID (leave empty for "Public"): 
Enter version being upgraded from: 6.6.0.0 
Please enter server (leave blank for localhost\mssql2016): 
Please enter database (enter for [client ID]_Titan_Public): 
Please enter user id with sysadmin role (leave blank for Windows auth): 
Copy M:\_tpi\LegacyUpgrades\DatabaseUpgrade\Templates\v6.2.0.0\StoredProcedures\Loader.sql to M:\_tpi\LegacyUpgrades\DatabaseUpgrade\v6.2.0.0\StoredProcedures\Loader.sql 
Copy M:\_tpi\LegacyUpgrades\DatabaseUpgrade\Templates\v6.3.0.0\StoredProcedures\Loader.sql to M:\_tpi\LegacyUpgrades\DatabaseUpgrade\v6.3.0.0\StoredProcedures\Loader.sql 
Copy M:\_tpi\LegacyUpgrades\DatabaseUpgrade\Templates\v6.4.0.0\StoredProcedures\Loader.sql to M:\_tpi\LegacyUpgrades\DatabaseUpgrade\v6.4.0.0\StoredProcedures\Loader.sql 
Copy M:\_tpi\LegacyUpgrades\DatabaseUpgrade\Templates\v6.5.0.0\StoredProcedures\Loader.sql to M:\_tpi\LegacyUpgrades\DatabaseUpgrade\v6.5.0.0\StoredProcedures\Loader.sql 
Copy M:\_tpi\LegacyUpgrades\DatabaseUpgrade\Templates\v6.6.0.0\StoredProcedures\Loader.sql to M:\_tpi\LegacyUpgrades\DatabaseUpgrade\v6.6.0.0\StoredProcedures\Loader.sql 
Copy M:\_tpi\LegacyUpgrades\DatabaseUpgrade\Templates\v6.7.0.0\StoredProcedures\Loader.sql to M:\_tpi\LegacyUpgrades\DatabaseUpgrade\v6.7.0.0\StoredProcedures\Loader.sql 
Copy M:\_tpi\LegacyUpgrades\DatabaseUpgrade\Templates\v6.8.0.0\StoredProcedures\Loader.sql to M:\_tpi\LegacyUpgrades\DatabaseUpgrade\v6.8.0.0\StoredProcedures\Loader.sql 
Copy M:\_tpi\LegacyUpgrades\DatabaseUpgrade\Templates\v6.9.0.0\StoredProcedures\Loader.sql to M:\_tpi\LegacyUpgrades\DatabaseUpgrade\v6.9.0.0\StoredProcedures\Loader.sql 
Upgrading [client ID]_Titan_Public from 6.6.0.0 to 6.9.0.0.  Continue (Y/N)?: Y

 

Upgrade local instance to v6.9.5 and compare upgraded db objects

  1. Install the current Titan Base version with your local Platform Installer (local-platform.titanclient.com) however, do NOT update Available packages (make sure to click Do Not Update Available Packages on the Test and Install results screen). 
  2. Export objects (Export-DatabaseObjects.ps1) for upgraded database (this will overwrite local). You'll need to change Line 88 again to add /useFilePathHash:$false
  3. Remove /useFilePathHash:$false parameter from Export-DatabaseObjects.ps1
  4. Use mercurial to diff any updated file with committed version (should only be base overrides that got updated) – resolve differences 
  5. Diff to 6.9.5 export and remove same files 
  6. Commit database objects and first part of database upgrade complete 

 

Save prod app control parameters

  1. Next step is to pull production app control parameters and insert into upgraded database appcontroldata table under appid > 1 
    1. Connect to the prod app control database in SSMS. [lookup in keepass under Northwoods > Azure > appcontroldata-*]. You can find the appID by looking in the web.config file under the Display or Wkst folders on production
    2. In the appcontroldata database search by the app id of the instance you're upgrading.
      The appcontroldata table is located in the database appcontroldata-prod-1-sql-nws located on the server appcontroldata-prod-1-sql-nws.database.windows.net.  You can access this SQL Server from HOST-PROD-1
    3. select * from appcontroldata where applicationid = 9 order by category,dataname.  Copy the results into an Excel spreadsheet for easy reference.
    4. Populate the following standard app vars in Database/DatabaseScripts/Upgrade/6.9.5-Cms-Post.sql: 
      1. UPDATE AppControlData SET DataValue = N'[prod value]' WHERE DataName = N'ADSIAdminPassword' AND ApplicationID = @appID
UPDATE AppControlData SET DataValue = N'[prod value]' WHERE DataName = N'DefaultPassword' AND ApplicationID = @appID
UPDATE AppControlData SET DataValue = N'[prod value]' WHERE DataName = N'ContactBlockSourceEmail' AND ApplicationID = @appID	
UPDATE AppControlData SET DataValue = N'[prod value]' WHERE DataName = N'TotalWkstBlocks' AND ApplicationID = @appID
UPDATE AppControlData SET DataValue = N'[prod value]' WHERE DataName = N'TreeLimit' AND ApplicationID = @appID
UPDATE AppControlData SET DataValue = N'[prod value]' WHERE DataName = N'DefaultPresentationSiteID'
UPDATE AppControlData SET DataValue = N'[prod value]' WHERE DataName = N'DOCID_Default'
UPDATE AppControlData SET DataValue = N'[prod value]' WHERE DataName = N'DragAndDropAllowUserUploadFolder'
UPDATE AppControlData SET DataValue = N'[prod value]' WHERE DataName = N'BrowseLimit'
    5. If there are non standard app control parameters, copy those in Database/DatabaseScripts/PopulateAppControlData.sql: 
      1. EXEC sp_executesql @updateAppVar, @paramList, @appID, N'CustomAppVar', N'CustomAppVarValue', N'Category', 0, 1, N'Description',1;
  2. If there are many custom parameters it may be easier to copy them to the local instance. Copy the values from the prod db and paste them in the AppControl table of the local instance.
  3. Run appcontrol compare script and populate the PopulateAppControlData script with all custom app vars.  
  4. Run in SSMS using sqlcmd mode – need boiler plate  
top