Migrate Upgrade SDK

PRE v6.9.5: MIGRATE/UPGRADE CLIENT EXISTING SDK CODE TO VERSION 7 SDK

1. Run Install-ClientSDK.ps1 to set up the new SDK
   1. M:\_tpi\scripts\Install-ClientSDK.ps1 -BaseVersion 6.9.0.0 -UseLegacyBaseInstaller $true 
      (use localhost unless multiple devs working on upgrade, then do dev though one person will need to get database upgraded and working before other devs start effort).  
   2. The below example shows default values for working on localhost.

   3. .\Install-ClientSDK.ps1 -BaseVersion 6.9.0.0 -UseLegacyBaseInstaller $true
      Enter client ID (e.g. "NWS", "MCW", "MPS", etc.): [client ID] 
      Enter system ID (leave empty for "Public"): 
      Master repo will be created: \\northwoodsoft.com\src\[client ID]\Titan\Public\Current 
      Local repo will be created: C:\Src\[client ID]\Titan\Public\Current 
      Is there an existing database that you want to use for this SDK environment? For instance, an existing dev database [Y/N]: N 
      Enter database instance (e.g. sql-dev-1\titanclients, localhost\mssql2016, etc. Enter for localhost\mssql2016: 
      Enter database name (Enter for [client ID]_Titan_Public): 
      Database name: [client ID]_Titan_Public 
      Database restricted username: [client ID]_Titan_PublicUser 
      Database restricted password: abc123 
      Restricted connection string: Data Source=localhost\mssql2016;Database=[client ID]_Titan_Public;Persist Security Info=False;User ID=[client ID]_Titan_PublicUser;Password=abc123;Encrypt=True;TrustServerCertificate=True 
      Database admin username: [client ID]_Titan_PublicAdmin 
      Database admin password: abc123 
      Admin connection string: Data Source=localhost\mssql2016;Database=[clinet ID]_Titan_Public;Persist Security Info=False;User ID=[client ID]_Titan_PublicAdmin;Password=abc123;Encrypt=True;TrustServerCertificate=True 
      Database system admin username (for localhost\mssql2016. Leave empty for current user): 
      Master connection string: Data Source=localhost\mssql2016;Database=master;Persist Security Info=False;Encrypt=True;TrustServerCertificate=True;Integrated Security=SSPI 
      Bootstrapper Version: 0.1.0.102 
      TPI username: TpiAdmin 
      TPI password: abc123 
      TPI Version: 0.1.0.102 
      Titan base package file: \\northwoodsoft.com\src\_tpi\Packages\NWS.Titan.Base\NWS.Titan.Base_6.9.0.0.zip 
      Ready to install 
      Install? [Y/N]: Y 
      

2. Diff old SDK to new one.  CustomUISupport to UISupport, CustomWkst to Wkst, etc. 
   TIP: When merging any base overrides, add to Mercurial right away as they are filtered by default,
   TIP: If any base overrides are compiled, you will need to figure out what they were and get into 7 
   1. Non-standard projects should be moved in their entirety, but will need to upgrade to work with 2017 and use .NET 4.7.2. If web project, need to update build tools in project file to use the vsbuildtools 2017.  If project is named Custom, recommend removing and namespacing using standards [ClientID].Titan.[Instance].[Project] 
       
   2. Open solution, add in non-standard projects, include files into each of the projects
       
3. For specific tips, see the Refactoring Code section. 

PRE v6.9.5: Database Export

1. Restore a copy of production legacy database over your working database using M:\_tpi\LegacyUpgrade\DatabaseUpgrade\RestoreDB.ps1 
   1. This should be the same copy already restored to the new dev site.
       
   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 v7 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 
      

2. 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.
    
3. 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
   

4. 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
    
5. Remove any LOCAL files that are the same as base. These files clearly have no overrides.
    
6. Remove /useFilePathHash:$false parameter from Export-DatabaseObjects.ps1
    
7. Commit 

PRE v6.9.5: Upgrade restored db to v7

1. Run upgrade legacy DB script against the restored database (M:\_tpi\LegacyUpgrades\DatabaseUpgrade\UpgradeLegacyDB.ps1). The below examples uses the default values. 
   REMINDER: This is only necessary if the instance is less than 6.9.0.0 

   1. .\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
      

2. Upgrade the target database to 6.9.5.xx using the Titan Platform Installer (TPI). This MUST be done prior to upgrading to v7
    
3. Upgrade the target database to v7 using TPI and compare upgraded db objects
    
4. 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). 
    
5. Export objects (Export-DatabaseObjects.ps1) for upgraded database (this will overwrite local). You'll need to change Line 88 again to add /useFilePathHash:$false
    
6. Remove /useFilePathHash:$false parameter from Export-DatabaseObjects.ps1
    
7. Use mercurial to diff any updated file with committed version (should only be base overrides that got updated) – resolve differences 
    
8. Diff to 7 export and remove same files 
    
9. Commit database objects and first part of database upgrade complete 

PRE v6.9.5: 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  

Review Refactoring Code section and build preRelease package

1. If starting from v6.9.5, update SDK using this guide.
    
2. Start building projects starting with components and working forward, adding missing references as you run into them until the entire project builds. 
    
3. After project builds, build a new package by using the package manager console. When upgrading build a preRelease package for Internal Testing. Upgrades should go to v7 and preRelease indicates the upgrade is still in progress. This will also commit to mercurial and push to master. 
    
4. Create package and test install into dev