Preparing databases and servers for upgrade

After ensuring all computers meet the system requirements, and valid licenses have been obtained and a database SysAdmin account is set up, apply updates, tests, and settings to prepare your database servers, iMIS servers, and workstations.

Generally, you need to walk through these procedures only once, when first setting up the servers and clients used in your implementation.

Verify that each server to host an iMIS database meets all system requirements.

Do the following to prepare the application.

Note: The installer warns you if it cannot enable or detect a required service.

  1. Verify that each server host meets all system requirements.
  2. Verify that the Turn off Windows Installer RDS Compatibility setting is enabled in the Group Policy Editor.
  3. For upgrades only: (Application servers only) Update your current installation of iMIS to required release levels and licensing.

    4. Note: iMIS must be release 15.2.1 or higher.

  4. Verify that you can make a SQL connection from each iMIS server host to the SQL Server instance on the database host where the iMIS database resides.

Do the following to prepare workstations:

  1. Verify that each user workstation meets all system requirements for the type of client used.
  2. Verify that a high-bandwidth SQL connection (100/1000) can be made between workstations and the SQL Server instance hosting the iMIS database.

    3. Note: Avoid low-bandwidth connections to the database host, such as through VPN. The high-volume SQL traffic will slow performance for Advanced Accounting Console users. If you have remote staff that need to access the Advanced Accounting Console, have them run the Advanced Accounting Console from a terminal server that is on the same network as the database host.

  3. Optimize Internet Explorer settings for iMIS:
    1. Open Server Manager.
    2. Select Local Server.
    3. Configure the IE Enhanced Security Configuration. Disable both Administrators and Users. Expect Internet Explorer to warn you that this is disabled.

    Continue optimizing Internet Explorer settings for iMIS:

    1. In Internet Explorer, go to Tools > Internet options.
    2. On the General tab, select Browsing history > Settings. Under Temporary Internet Files, set Check for newer versions of stored pages to Every time I visit the webpage.
    3. Click OK.
    4. On the Security tab, select Trusted sites.
    5. Click Default level.
    6. Set the security level to Low.
    7. Click Sites.
    8. Add the name of your iMIS application server host (http://<myserver>).
    9. Disable Require server verification (https:) for all sites in this zone.
    10. Click Close.
    11. On the Advanced tab, check Disable script debugging (Other).
    12. Click OK.

Do the following to prepare a database for an upgrade:

  1. If you are upgrading a 1700 site, a 1600 site created in 15.1.2, a 15.1.1 WCM site, or a Web View site see Upgrading older websites for more information.

    2. Warning!
    The iMIS Web View and Public View sites are deleted during upgrade.

  2. If you added any navigation items to the iMIS Desktop view that use the Open the link in a new window option, disable that option or delete the items and make a note to restore them after upgrading. Otherwise, the site administrator will need to remove the navigation item and then recreate the navigation item.
  3. If the WebPartGalleryEntryRef table exists, then to prevent an upgrade error, ensure that all custom content items contain a description by running the following script and manually updating any rows returned with a description:
    4. SELECT * 
FROM [dbo].[WebPartGalleryEntryRef]
WHERE ([WebUserControlPath] IS NOT NULL OR [WebPartTypeName] IS NOT NULL)
       AND ([WebPartDescription] IS NULL OR [WebPartDescription] = '')
ORDER BY [WebPartName]
  4. To prevent duplicate key upgrade errors resulting from bad registration records, run the following scripts:
    • No ShipTo ID – A registration record without an ST_ID occurs when entering new contact records through the Events registration Attendee tab in Advanced Accounting Console without enabling Add to Master. To find such problems, run the selection part of the script (Step 0). If you receive any results, run the entire script (Steps 1 through 3):
      • https://download.advsol.com/public/restricted/UpdateMeetingOrdersWithNoSTID.sql

      Although this script lets you upgrade your database successfully, it does not prevent future problems. To avoid recreating the problem, always enable Add to Master when entering new contact records through the Events registration Attendee tab.

    • Duplicate registration – An error also occurs if your database contains duplicate event registration records for the same event. Run the selection script to locate these records:
      • https://download.advsol.com/public/restricted/SelectDuplicateRegistrations.sql

      Often the best fix is to close the event, which creates activity records for each registrant and purges all related entries in Orders, Order_Lines, Order_Meet, and Order_Badge.

  5. If you use SQL Server’s Database Engine Tuning Advisor to create additional index statistics, note that these are deleted during upgrade and will have to be re-added, if needed. See Deleting Database Engine Tuning Advisor index statistics.
  6. In iMIS, post any open A/R Cash batches manually. If not, the upgrade might duplicate DUES activities.
  7. If you customized any standard business objects, examine their properties in Business Object Designer (BOD) and record your changes so that you can reproduce them after upgrading.

    8. Note: New business objects that you created with Business Object Designer are automatically upgraded, but standard business objects might be overwritten.

  8. Itemize any other customizations and locate their upgrade instructions.
  9. (SQL Server 2000) Upgrade to a supported version of SQL Server, relocate your iMIS database to the new service, and upgrade using the relocated database.

    10. Note: Be sure to uninstall iMIS from your application server, install the new version of iMIS, and upgrade workstations with the remote installer from that application server.

  10. In your SQL Server management environment, perform the following checks:
    • Check for database corruption by running DBCC CHECKDB.
    • Set the database option Auto Close to False.
    • (if applicable) Enable the database option to Truncate Log on Checkpoint. Be sure your database log has sufficient size and space to grow to twice the size of the largest table. Revert to your original log settings only after you have upgraded successfully.
    • Set the Recovery Model to Simple before running the iMIS upgrade. Record what you changed Recovery Model from, and change the setting back only after you have successfully upgraded.
    • If you have customized iMIS by adding SQL Server Full Text indexes, you should itemize, and then drop, all full-text indexes and catalogs. However, avoid dropping other types of indexes before upgrading, as some upgrade procedures need them. After the upgrade, you can recreate them.

      • Note: Failure to perform this step might cause the upgrade to fail.

    • If you have customized any ASI triggers, record your customizations so that you can reapply them after upgrading. All ASI triggers, procedures, views, and functions are replaced during upgrades, and all ASI indexes on tables are rebuilt.

      • Warning!
      Be sure that you are customizing new SQL. Do not overwrite preexisting code; this could break iMIS functionality.

    • Verify that all custom triggers and stored procedures in your iMIS database use only ANSI-compliant SQL (see Upgrade error for non-ANSI join operators).
    • If you have custom views, refresh their metadata (see Refreshing metadata for custom views).
  11. Shrink your log file with the following SQL Script:
    12. -- Shrink first log file of THIS database
DECLARE @sql nvarchar(1000)
DECLARE @dbName sysname
DECLARE @logName sysname
SELECT @dbName = DB_NAME()
SELECT TOP 1 @logName = [name] FROM sys.database_files WHERE [type_desc] = 'LOG' ORDER BY [name]
SET @sql = 'DBCC SHRINKFILE( ' + @logName + ' , 1)'
EXEC (@sql)
SET @sql = 'ALTER DATABASE ' + @dbName + ' SET RECOVERY SIMPLE'
EXEC (@sql)
SET @sql = 'DBCC SHRINKFILE( ' + @logName + ' , 1)'
EXEC (@sql)
go
  12. Back up your database.

    13. Note: For large databases, check that you have sufficient free disk space relative to the size of your database, as its tables are copied during the process of conversion and rebuilding for ANSI support.

    Note: You can run a pre-upgrade conversion on a backup to ensure that all non-ANSI-compliant tables can be rebuilt successfully. See ANSI Conversion Utility - ConvertTableToANSI.

  13. Verify that the database backup can be restored.

    14. Note: If the upgrade process fails for any reason, you can fix whatever caused the failure, restart the upgrade, and the process will pick up where it left off.

  14. Schedule system downtime for the upgrade to occur.

    15. Note: To enable default ANSI settings, the upgrade utility rebuilds every non-compliant table and recreates all stored procedures, triggers, functions, and views. Because of this extensive rebuilding, expect upgrading to be significantly slower for very large databases.

If you have custom SQL views, be sure to refresh the metadata for the views to avoid database errors when upgrading iMIS. Views are created with schema information (column data types and sizes) as the data exists at that moment. If a column is later changed, renamed, or dropped, the view's cached value is out-of-sync. Using such a view can cause errors and unexpected issues, such as strings being truncated. User-created views easily become out-of-date because the iMIS schema changes from version to version.

Do the following to refresh the metadata for custom views:

  1. Run the following script to locate any and all bad views. This script refreshes the views for schema objects in the [dbo] schema (includes all iMIS tables). If there are many tables in your database that exist in other schemas, you may wish to remove the clause that limits the script to only [dbo] so that everything is refreshed and up-to-date. Running this script in SQL Server Management Studio, gives two outputs - one titled Results, and the other Messages. The Results tab features a result set for each bad view, and each view is named. The Messages tab shows a concise list of bad views and a total count. 
    2. -- Refresh the metadata for all views in the 'dbo' schema
SET NOCOUNT ON
DECLARE @viewName AS VARCHAR(255)
DECLARE @count int

DECLARE listOfViews CURSOR FOR
	SELECT [TABLE_NAME]
  	FROM INFORMATION_SCHEMA.VIEWS v
       	LEFT OUTER JOIN sys.sql_dependencies d ON v.[TABLE_NAME] = OBJECT_NAME(d.object_id)
 	WHERE [TABLE_SCHEMA] = 'dbo' AND
       	(d.[class_desc] IS NULL OR d.[class_desc] <> 'OBJECT_OR_COLUMN_REFERENCE_SCHEMA_BOUND')
 	GROUP BY [TABLE_NAME]
 	ORDER BY [TABLE_NAME]
 
OPEN listOfViews
FETCH NEXT FROM listOfViews into @viewName
 
SET @count = 0
WHILE (@@FETCH_STATUS <> -1)
BEGIN
	BEGIN TRY
    	EXEC sp_refreshview @viewName
	END TRY
	BEGIN CATCH
    	IF XACT_STATE() = -1 ROLLBACK
    	PRINT @viewName + ' has ERRORS: ' + ERROR_MESSAGE()
    	SET @count = @count + 1
	END CATCH
	FETCH NEXT FROM listOfViews INTO @viewName
END
 
CLOSE listOfViews
DEALLOCATE listOfViews
 
PRINT 'Total Views with errors: ' + CAST(@count AS nvarchar(10))
 
SET NOCOUNT OFF
  2. Check the report for errors. Keep in mind:
    • SQL Server only reports the first bad column it encounters in a given view, even if there are several bad columns. Run the script repeatedly until no errors remain.
    • If you have renamed the column in the view, the column name listed in this output is the original column name in the Table, not the name used in the View.
  3. If any errors are reported, fix the problem views immediately. This might involve:
    • Renaming a column in your view definition
    • Dropping a column
    • Getting the data from a new or different table
    • Dropping an obsolete view
    • Republishing the associated Business Object in Business Object Designer, if the view name starts with vBo (an automatically generated view)
  4. Once the view is fixed, run the script again.
  5. When errors no longer occur, proceed with the upgrade.

If you use SQL Server’s Database Engine Tuning Advisor to create additional indexes, be aware that DB Upgrader needs the additional indexes to be deleted before upgrading.

Note: Not only should indexes be dropped before upgrading, but so should any Database Tuning Advisor-generated statistics.

Note: After upgrading iMIS, you can re-add these indexes using the Database Tuning Advisor. However, iMIS gained many indexing improvements, so test the performance of your upgraded system to determine whether you still need these indexes.

If you are unsure whether tables in your database contain these statistics, run the following query:

SELECT OBJECT_NAME(s.[object_id]) AS TableName,
           s.[name] AS StatName
      FROM sys.stats s
     WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
           AND s.name LIKE '_dta_stat%';

Do the following to prepare to upgrade your websites:

  1. If you have not kept track of changes implemented into your theme, perform a comparison between the theme files you originally copied and your updated files, including all CSS, skin, image, and master page files. There are many free file comparison tools available online.
  2. If you have tailored any of the communication templates, such as the Default or Order Confirmation template, copy them to the Templates folder.
  3. In the CSS, skin, and master page files, annotate the code in your copy to mark the changes you have made so that you can identify these changes after the upgrade.
  4. Copy any original theme files to retain all style modifications after upgrade:
    1. Copy a theme folder, for example, C:\Program Files (x86)\ASI\iMIS\Net\App_Themes\Austin, and rename the copied folder. Use a unique name for the theme folder copy, for example, \<my_organization>Austin.
    2. Create a new CSS file with the same name you used for your theme folder. Use this file for storing override classes and new classes. This will allow you to get the latest theme files during an upgrade, but you will be able to keep all style modifications.
  5. For the image files, note which images you have changed or added. For example, if you are currently on 15.2.5 and are using a copy of the Aspen2 theme, compare your copied files to the original 15.2.5 version of the Aspen2 theme files. Themes are located by default in C:\Program Files\ASI\iMIS\Net\App_Themes.

Note: Back up your current theme and master page before you upgrade, so that you do not lose changes.