Test System Refresh

Jonathan Semones -

Many organizations have a test system for training and development purposes.  This system is usually created from a copy of their production database.  Often this test data needs to be updated from the production system so that training and tests are performed with accurate current data.

During a test system refresh, you will lose all of your test system data and settings as all of these are stored in the database which you will overwrite with your production database.  This guide reviews how to refresh the system with the least amount of hassle.  

Important! Refreshing a Test system is a process that is not covered by Lucity Support.  We can provide information, but cannot do the process for you.  If you would like Lucity to do this for you then contact us and we will give you a quote.

 

Preparation

Web App URLs

Make sure you know the URLs for the web applications in your test system.  These are found in the System > Settings and include:

  • Lucity Web - Website settings > Internal Website
  • Lucity Web Windows Authentication - Website settings > Windows Authentication Website
  • Lucity Web REST API - REST API settings > Url for Internal REST API
  • Lucity Identity Server (web)* - Identity settings > URL to the Identity Server for the internal Identity Server
  • Lucity Mobile Server - Mobile settings
  • Lucity Identity Server (mobile)* - Identity settings > URL to the Identity Server for the mobile Identity server
  • Lucity Document Server - Documents settings > URL of Document Server
  • Lucity REST API - REST API settings > Url for REST API
  • Lucity Citizen Portal REST API - REST API settings > Url for Citizen Portal REST API

*These may be the same depending on your setup.

 

Document Paths

Make sure you know the paths to your test systems document hives.  These are found in the System > Settings and include:

  • Citizen App Hive - Citizen settings > Path where custom citizen app files are stored
  • Citizen Document Hive - Documents settings > Path where uploaded documents are stored 
  • Internal Document Hive - Documents settings > Path where uploaded documents are stored
  • Sign Picture Library - Documents settings > Path where uploaded Sign Library images are stored
  • Custom Reports Hive - Reporting settings > Path where Reports are stored.

 

Geodatabase Configuration

If you have a test geodatabase make sure you know the connection information.  This is found in the Lucity Administration Tool under GIS > Connection Strings.

 

GIS Services

If you use have test gis services make sure you know the Urls for these services.  These are found in the Lucity Administration Tool under GIS > GIS Services.

 

 

Database Refresh

Database Backup/Restore

  1. Go to your production database server and perform a full backup of your Lucity Production database.
  2. Copy the Lucity backup to the test database backup folder.
  3. Go to SSMS (SQL Server Management Studio) and right click on the test database. This database could be named Lucity as well. Some instances for test databases could used the same SQL instance as production so this database may be named differently such as ‘LucityTest’
    Note: Make sure the log file has plenty of space to grow if you are using the restore process to upgrade to a newer version of Lucity.
  4. Choose tasks, restore and database.
    1.png
  5. When the Restore Dialog appears, click on the radio button ‘Device’ and then the ellipse button to the right.
    2.png
  6. When the select backup devices dialog appears, click on add.
    3.png
  7. Browse to the backup file, highlight and click ok twice.
  8. Once you are in the restore database dialog, click on the options tab in the TOC, select ‘Overwrite the existing database (with replace)’ checkbox, uncheck ‘take tail-log backup before restore’ and click on the checkbox for ‘close existing connections to destination database’. Click ok.
    4.png
  9. You should see a restored successful dialog. Click ok.
    5.png

 

Refresh database

  1. Find a computer with Lucity Desktop installed that is linked to your test system.
  2. Run Lucity's Client Maintenance program on that computer.
  3. Make sure that client maintenance is pointed at the Config folder for your test system.cm_login.png
  4. The test system should still be pointed at the test database that you just overwrote.
  5. Click the Next> button.
  6. Click the Update button this will both update the database as well as perform cleanup functions.cm_update_db.png
  7. Click Finish>>|.

Note: if you have other users inside of the database on the test instance, you’ll need to reinstitute them to the restored database.

 

Document Refresh

Restore Documents, Reports and Citizen Apps

Go to the Production LucityServer hive and copy the contents of DocShare folder and paste them inside of the test LucityServer hive DocShare location. This will update the test Lucity flat files to be current with the restored Lucity test database.

7.png

Update Lucity Document Links

After you've copied over the documents into the test system you will want to update your test database to point to the location of the documents for you test system.

  1. Go to SSMS and create a new query 8.png
  2. Below is an example script that will help update link locations.

Example

--failsafe to make sure you are using the Lucity Test database
use Lucity
--change location of document paths (change rollback to commit when action needed)
begin transaction
update cmdoc set doc_path = replace(doc_path, 'test-db','actags')
update efdoc set doc_path = replace(doc_path, 'test-db','actags')
update eldoc set doc_path = replace(doc_path, 'test-db','actags')
update pkdoc set doc_path = replace(doc_path, 'test-db','actags')
update smdoc set doc_path = replace(doc_path, 'test-db','actags')
update stdoc set doc_path = replace(doc_path, 'test-db','actags')
update swdoc set doc_path = replace(doc_path, 'test-db','actags')
update wkdoc set doc_path = replace(doc_path, 'test-db','actags')
update wtdoc set doc_path = replace(doc_path, 'test-db','actags')
select * from cmdoc
select * from efdoc
select * from eldoc
select * from pkdoc
select * from smdoc
select * from stdoc
select * from swdoc
select * from wkdoc
select * from wtdoc
rollback transaction

Note: When you build the query for the first time, test first. We’ve included the script with rollback functionality. Do not use this for the finished script. Resave your script after you’ve finished testing with the transaction and select statements removed.

 

System Settings Refresh

Since you refreshed your database from production it will still contain all of the system settings paths and URLs that point back to your production system resources.  These will need to be updated and are found in the Lucity Administration Tool for your test system under System > Settings.

  • Lucity Web - Website settings > Internal Website
  • Lucity Web Windows Authentication - Website settings > Windows Authentication Website
  • Lucity Web REST API - REST API settings > Url for Internal REST API
  • Lucity Identity Server (web)* - Identity settings > URL to the Identity Server for the internal Identity Server
  • Lucity Mobile Server - Mobile settings
  • Lucity Identity Server (mobile)* - Identity settings > URL to the Identity Server for the mobile Identity server
  • Lucity Document Server - Documents settings > URL of Document Server
  • Lucity REST API - REST API settings > Url for REST API
  • Lucity Citizen Portal REST API - REST API settings > Url for Citizen Portal REST API
  • Citizen App Hive - Citizen settings > Path where custom citizen app files are stored
  • Citizen Document Hive - Documents settings > Path where uploaded documents are stored 
  • Internal Document Hive - Documents settings > Path where uploaded documents are stored
  • Sign Picture Library - Documents settings > Path where uploaded Sign Library images are stored
  • Custom Reports Hive - Reporting settings > Path where Reports are stored.

Scripted Web URL Updates

  1. Go to SSMS and add to the above query
  2. The below script will update the URLs in Lucity System Settings to the new location. In this example, we assume you are using the FQDN (fully qualified domain name)

Example:

--change location of URLs (change rollback to commit when action needed)
begin transaction
update SYSTEMSETTINGS set SYSSET_VALUE= replace(SYSSET_VALUE, 'imp.lucity.net','actags.test.local') where sysset_value like '%imp.lucity.net%'
select * from SYSTEMSETTINGS where sysset_value like '%actags%'
rollback transaction
select * from SYSTEMSETTINGS where sysset_value like '%imp.lucity.net%'

Scripted Document Configuration Update

  1. Go to SSMS and add to the above query
  2. The below script will update the URLs in Lucity System Settings to the new location. In this example, we assume you are using the FQDN (fully qualified domain name)
--change document settings (change rollback to commit when action needed)
begin transaction
update SYSTEMSETTINGS set SYSSET_VALUE= replace(SYSSET_VALUE, 'test-db','actags') where sysset_value like '%test-db%'
select * from SYSTEMSETTINGS where SYSSET_value like '%actags%'
rollback transaction
select * from SYSTEMSETTINGS where SYSSET_value like '%test-db%'

 

GIS Refresh

May organizations do not keep a separate GIS test system, however, if you do the configuration will also need to be updated.

Update Geodatabase Configuration

Next, we will move to the GIS system. 

  1. Open the Lucity Administration Tool and go to GIS > Connection Strings.
  2. If you have a test geodatabase update the existing connection string to point to the test geodatabase.
  3. If you do not have a test geodatabase you will want to clear out the fields on the connection string.
  4. Your changes are automatically saved.
  5. You may want to go to ArcCatalog and open the Lucity GIS > Geodatabase Configuration tool.  Within that tool, you can validate your setup to make sure it is connecting to the test geodatabase.

 

GIS Services

Next, we can update your map services to point to any test map services.

  1. Open the Lucity Administration Tool and go to GIS > Connection Strings.
  2. If you have test services update the existing services with the Url for the test service.
  3. If you do not have test service you will likely want to remove the services listed here.  This protects your system from test data being accidentally added into the production system.
  4. Click save.

 

Update ArcGIS Portal Integration

If you use the ArcGIS Portal Integration and your users login using the ArcGIS Single Sign-On you will need to update their user accounts in Lucity Security to use a login to a test ArcGIS account.  Otherwise, they log into their test system using production ArcGIS credentials, thereby connecting to their production ArcGIS data in the map.

  1. Open Lucity Security.
  2. Go to Security > User/Group Setup
  3. Select a user and click Edit User.
    • If you have credentials for a test login for ArcGIS enter it in the ESRI Logon ID field.
    • If you do not have credentials for a test login for ArcGIS clear out the ESRI Logon ID field.
  4. Click OK.

 

 

 

Have more questions? Submit a request

Comments

  • Avatar
    Sanford Hess

    This process makes sense, but it feels like the settings that must be written down and manually re-entered should be stored on a discrete set of tables. Does anyone know what those tables are, or has anyone scripted something to save only those tables from TEST and re-insert their data after a restore from PROD?

  • Avatar
    Ben Gregory

    I agree. The settings above are already stored in the TEST DB, they should be extracted before the restore and then simply inserted again after the TEST DB is refreshed. Rather than have to manually perform those steps. Assuming it's the same version, or there are no schema changes..

  • Avatar
    Jonathan Semones

    Hey, so during ACT 2018 Luke presented on this process and included some more complete instructions on parts and some example scripts that should help make the process easier. Support still can't really help you with this but I've added his information into the article to give you a better point to start off with.