Your local database administrator is responsible for maintaining the database and database server. We recommend you perform regular maintenance on your SQL database, such as full backup, transaction log backup, rebuild index, update statistics, maintenance cleanup, etc. Shrinking should be used sparingly as it may fragment your database.
For the Cartegraph database, you may wish to trim the error log as well. This can be done in the software but is more convenient to do in SQL. The script below is an example of how to remove error logs that are older than 90 days. Please review this before executing it on your Cartegraph database.
delete from ELMAH_Error where datediff(day, timeutc, GETUTCDATE()) >= 90
When you restore your database from Prod to Test, you will need to disable notifications, GIS connections, and automations, as well as set the Project Home and Instance ID. Some of this can be done in the software but is more convenient to do in SQL. Before starting this process, use the SQL scripts that follow on your Test database to get the location of the Project Home and obtain your Instance ID. You will need this information when you copy files from the Production to Test Project Home and to reset the Project Home path and Instance ID in the Test database after it has been restored.
Project Home: select sValue from cgSysSettings where sKey = 'ProjectHome' Instance ID: select TextVal1 from cgSysObjects where ObjectName = 'extensions'
Basic Steps to Restore From Production to Test
Each customer’s site is different; please adjust to fit your location.
Normally the Test Project Home is not updated from Production. Please make sure to back up any files you need to keep in case they are overwritten.
- Perform a full backup of the Production database.
- Copy the Production Project Home to the Test Project Home and overwrite any files if necessary.
- Stop the Test application server app pool and report pool.
- Restore the backup of Production database to Test, overwriting the existing database.
- Configure any SQL logins as needed to have "db_owner" permission to the database.
- Run the SQL statements that follow to disable notifications, GIS integrations, and automations. This will also set the Project Home and Instance ID in the Test database. Replace <Test Project Home Path> with the path to your Test Project Home. Replace <Test Instance ID> with the Instance ID for your Test site.
- update NotificationTypes set NotificationsOn = 0
- update cgGISAssociations set Inactive = 1
- update cgAutomations set IsEnabled = 0
- update cgSysSettings set sValue = '<Test Project Home Path>' where sKey = 'ProjectHome'
- update cgSysObjects set TextVal1 = '<Test Instance ID>' where ObjectName = 'extensions'
- Start the Test application server app pool and report pool.
- Sign in to Test.
- Go to Administration > Structure Manager > Publish.
Alternatively, you can disable notifications, GIS integrations, and automations and set the Project Home directly in the application.
More information is in the following links, including how to publish the site.
Test Database Support
The normal workflow in a Test database is to examine the issue and try to resolve it. But because it is a Test database and should be regularly updated from Production, we do not invest a lot of time into sorting out issues that are not seen in Production.
The exception to that rule is when you are building something you want to move to Production. In that case, we need to find what changed and caused the issue.
Here are some troubleshooting questions:
- Do you know what changed before the error?
- Do you know when the Test was last refreshed from Production?
Hint: If you are building something to be moved into Production, you need to refresh Test before you start your work.
- Is there any reason not to overwrite Test with a copy of Production?
Your GIS Administrator is responsible for maintaining all of the GIS data and servers, including the REST services used to GIS connect to OMS. The Cartegraph GIS Sync runs each night at the time configured in the scheduled task on the application server. The GIS connection to Cartegraph will cause your delta tables to grow and can slow GIS performance if you do not regularly compress your GIS environment.
After the initial connections are set up, the number of changes (entries in the delta table) will be greatly reduced.