Versions:6.3+

The QuerySurge QuerySync utility is used to bulk copy QueryPairs, Connections and Suites between two QuerySurge instances. The utility is designed for command-line access in order to support automated use.

We strongly urge that you test with a Target QuerySurge instance separate from your “official” installs, so that if data is corrupted, all that is required is an uninstall/reinstall to clean up. You can use the current QuerySurge installer to install in your environment; as always, it will install with a 15 day Trial license, which must be replaced with a license issued by the QuerySurge licensing team.

Caution: Rollback for any QuerySync operation is manual. You must back up your target QuerySurge database before syncing into it. Without a backup, you have no protection against corruption. In any case, you should be backing up your QuerySurge databases to minimize risk of an outage. For a discussion of backups, see:
https://querysurge.zendesk.com/hc/en-us/articles/205762226-QuerySurge-Database-Backup-Procedures

Note: QuerySync is version-dependent. A pair of QuerySurge instances used for the sync source and target must have the same version. Additionally, when QuerySurge upgrades are deployed in your environment, you will need to upgrade QuerySync along with them.

Note: The QuerySync feature is licensed separately. Contact QuerySurge Sales for information.

Command Line QuerySync Utility

Because QuerySync is a command-line utility, all specifications are controlled via property files. Export and Import property files govern the merge. If only an Export property file is provided, then only an export will be performed. If only an Import property file is provided, then only an import will be performed. If both are provided, then both steps are executed.

The files are stored in the config/ directory, which must be a subdirectory of the working directory where the QuerySync utility is launched. Properties files of other names will be ignored. The property files must be named as follows for their respective steps to be executed:

Export: export_merge.properties
Import: import_merge.properties

Note that several of the values are shared between the files; this allows separate execution of the export and import steps.

File Structure

The QuerySync utility expects the following directories, files, and working directory configuration:

sync/config/
- export_merge.properties
- import_merge.properties
Workspace folder (explained below)
QuerySync JAR file
QuerySync launch script, included in the QuerySync distribution
- Windows - .bat; Linux - .sh
- Script must set working directory as the sync/ directory

Server-to-Server Sync Modes

QuerySurge assets for copying are identified either by Treepath (i.e. a folder path in the Design Library tree), or by Test Suite (as shown in the Scheduling view). In either case, the QueryPairs identified are moved, along with associated Connections (either as a full move or as a mapping).

Use of export_merge.properties

Following is a template for export_merge.properties in all multi-server sync modes. Properties with one of several possible values are denoted by parenthesis; optional properties and values are surrounded by square brackets.

[srcHttpProtocol=(http|https)] srcHttpHost=HostnameOrIP srcDBHost=HostnameOrIP srcHttpPort=PortNumber srcDBPort=PortNumber projectId=ProjectIdNumber QuerySurgeUid=Username QuerySurgePwd=Password workspace=WorkspaceDirectoryPath mode=(byTreepath|bySuite)[,(mapConn|noMapConn|updConn)][,(mapSuites|noMapSuites)] sourceList=(DesignFolderPath|(SuiteId[[,SuiteId]...])|(SuiteName[[,SuiteName]...])) [connectionMap=SourceConnName:TargetConnName[[,SourceConnName:TargetConnName]...]] [suiteNameMap=SourceSuiteName:TargetSuiteName[[,SourceSuiteName:TargetSuiteName]...]]

Access Parameters

Access parameters for the source QuerySurge instance (target information is specified in import_merge.properties, explained below). Sample values follow:

srcHttpProtocol=https # omit for default of http srcDBHost=src-qs-db.mycorp.com srcHttpHost=src-qs-app.mycorp.com srcHttpPort=80 srcDBPort=3306 projectId=104 # omit in QuerySurge versions <= 7.2; required in QuerySurge 8.0+ QuerySurgeUid=youradminuser QuerySurgePwd=youradminpassword

workspace

This specifies a directory where the QuerySync utility will write files it needs to transfer the assets from one instance of QuerySurge to the other. This directory must be writable by the user account that the QuerySync utility is executed with. The path format is operating system-specific, and must always end in the separator character for that OS:

Windows:
workspace=C\:\\sync\\workspace\\
Linux:
workspace=/home/user/sync/workspace/

Note: Before running the export step, the workspace directory should be cleared of any existing files. After running an export, the workspace files should not be modified in any way. Contents of generated workspace files may change between QuerySync versions, and may not be backwards compatible.

mode

The mode specifies whether to locate QueryPairs by suite or path (as described above):

byTreepath - sync QueryPairs from a Design Library folder path
bySuite - sync Test Suites and the constituent QueryPairs

sourceList

This specifies the QueryPairs to be merged, either by a path to a QueryPair folder (or a single QueryPair) in the Design Library tree, or by a list of Suites from the Scheduling view.

Example sourceList for byTreepath sync of a folder:
sourceList=QueryPairs/XMART

In this case, all QueryPairs and their folders from the XMART folder on down (inclusive) will be merged.

Example sourceList for byTreepath sync of a single QueryPair:
sourceList=QueryPairs/XMART/ADDRESS_DIM/XMART - STREET (BILLING)

In this case, just the QueryPair XMART - STREET (BILLING) will be merged.

Example - sourceList for bySuite sync, using Suite names:
sourceList=CustomersSuite,OrdersSuite

In this case, all QueryPairs and their folders from the CustomersSuite and OrdersSuite Suites will be merged.

Example - sourceList for bySuite sync, using Test Suite ID numbers:
sourceList=321,1054

In this case, all QueryPairs and their folders from Test Suites with ID numbers 321 and 1054 will be merged.

connectionMap and mapConn mode modifier

Connections associated with QueryPairs and Test Suite connection override settings in the source instance can be handled in two ways:

Default - no modifier: Update or create connections in target instance
mapConn: Map source instance connections to target instance connections

Update-or-create mode

Update-or-create mode will, upon import, attempt to locate source connections by name in the target instance. Connections that exist in the target will be updated to reflect all details from the source instance at time of export, except ID number. Connections that are not found in the target will be created anew in the target, with a new ID number, and all other details from the source instance.

This mode is useful for when it is required to frequently update identical connections in the source and target instances, or for large one-time migrations of QueryPairs and connections between QuerySurge instances.

Example - byTreepath mode with connection update-or-create mode:

mode=byTreepath

In this case, all connections used in all source instance QueryPairs and Test Suite connection overrides will be exported, and either used to update connections in the target instance or create them.

Connection mapping mode

Connection mapping mode will change the connections used by source instance QueryPairs to specified connections that exist in the target. The connectionMap property accepts a comma-separated list of source-to-target connection name pairs. In the case where connections used by source instance QueryPairs are missing from the connection map, those QueryPairs will use the same connections in the target (and if such connections do not exist in the target, they will be set to blank).

This mode should be used when connections in the target instance must not be altered or replaced (ex. to maintain connections with identical names and different database server addresses or credentials between the source and target instances).

Example - bySuite mode with connection mapping enabled and connectionMap:

mode=bySuite,mapConn connectionMap=myDevConn1:myStgConn1,myDevConn2:myStgConn2

In this case, any source/target query in the source instance's QueryPairs that uses myDevConn1 will be mapped to use myStgConn1 in the target instance, and similarly for myDevConn2 to myStgConn2. Also, if Source/Target Connection Overrides are enabled in the Test Suite properties, the same connectionMap will be used to map the selected connection overrides.

suiteNameMap and mapSuites mode modifier

Applicable only for bySuite mode. This specifies one or more mappings between Suites in the source QuerySurge instance and the Target QuerySurge instance. Using this option means that Suites are not moved, but that QueryPairs in the Source QuerySurge instance are mapped by QueryPair Name to Suites that already exist in the Target QuerySurge instance.

Example - bySuite mode with suite mapping disabled:

mode=bySuite mode=bySuite,noMapSuites # explicit indication sourceList=myDevSuite1,myDevSuite2

In either of these cases, the myDevSuite1 and myDevSuite2 suites will be created in the target instance, and all QueryPairs they contain in the source instance will be synced to the target instance and added to their respective suites.

Example - bySuite mode with suite mapping enabled and suiteNameMap:

mode=bySuite,mapSuites sourceList=myDevSuite1,myDevSuite2 suiteNameMap=myDevSuite1:myStgSuite1,myDevSuite2:myDevSuite2

In this case, QueryPairs in source suite myDevSuite1 will be synced to the target instance, and placed into myStgSuite1 on the target. (Neither myDevSuite1 nor myStgSuite1 will be created on the target instance. myStgSuite1 must be created prior to syncing).

Use of import_merge.properties

Access Parameters

Access parameters for the target QuerySurge instance (source information is specified in export_merge.properties, explained above). Sample values follow:

trgHttpProtocol=https # omit for default of http trgDBHost=trg-qs-db.mycorp.com trgHttpHost=trg-qs-app.mycorp.com trgHttpPort=80 trgDBPort=3306 projectId=65 # omit in QuerySurge versions <= 7.2; required in QuerySurge 8.0+ QuerySurgeUid=youradminuser QuerySurgePwd=youradminpassword

workspace

Same as workspace directory in export_merge.properties. If running import on a different machine from where the export was run, copy the workspace files to a directory on the import machine (using FTP, SCP, etc.), and set the workspace to this local directory.

mode

This value should match the mode specified in the export_merge.properties file, including mapConn and mapSuite mode flags (if specified).

connectionMap, suiteNameMap

Omit these properties from import_merge.properties; they will be retrieved from the workspace directory contents generated during the export step.

A Worked Example - Sync Folder of QueryPairs and Map Connections

The QuerySurge instance for a dev environment contains the following five QueryPairs. The first four QueryPairs have CUST_MASTER_DEV as the source connection, while EMAILADDRESS has WEB_ORDERS_DEV. The target connection for all QueryPairs is CORP_EDW_DEV.

QueryPairs/CustomerETL/NAME/FIRSTNAME
QueryPairs/CustomerETL/NAME/LASTNAME
QueryPairs/CustomerETL/CONTACT/POSTALADDRESS
QueryPairs/CustomerETL/CONTACT/PHONE
QueryPairs/CustomerETL/CONTACT/EMAILADDRESS

We would like to promote these QueryPairs to the QuerySurge instance for staging environment, and change the connections to their "_STG" equivalents. Here, dev will be the source instance, and staging the target instance. We will configure the following export_merge.properties file in C:\querysync\workspace, with connection details for the source instance, sourceList referencing this folder, and an appropriate connectionMap:

srcHttpHost=qs-dev.mycorp.com srcDBHost=qs-dev.mycorp.com srcHttpPort=80 srcDBPort=3306 projectId=104 # omit in QuerySurge versions <= 7.2; required in QuerySurge 8.0+ QuerySurgeUid=dev_user QuerySurgePwd=dev_pass workspace=C:\\querysync\\workspace\\ mode=byTreepath,mapConn sourceList=QueryPairs/CustomerETL connectionMap=CUST_MASTER_DEV:CUST_MASTER_STG,WEB_ORDERS_DEV:WEB_ORDERS_STG,CORP_EDW_DEV:CORP_EDW_STG

The import_merge.properties file will have corresponding info for the target instance:

trgHttpHost=qs-stg.mycorp.com trgDBHost=qs-stg.mycorp.com trgHttpPort=80 trgDBPort=3306 projectId=65 # omit in QuerySurge versions <= 7.2; required in QuerySurge 8.0+ QuerySurgeUid=stg_user QuerySurgePwd=stg_pass workspace=C:\\querysync\\workspace\\ mode=byTreepath,mapConn

Upon the first export and import with this configuration, assuming an empty Design Library on the target instance, the appropriate folder structure will be created in the target:

QueryPairs/CustomerETL
QueryPairs/CustomerETL/NAME
QueryPairs/CustomerETL/CONTACT

Then the QueryPairs will be created in the folders, and their source/target connections will be set according to the specified connectionMap. On subsequent executions, any changes made in these QueryPairs in the source instance will be synced to the target, including SQL query text, key column definitions, thresholds, etc, and new QueryPairs in the same path will be created.

Local Copy Modes

These additional modes handle copying of assets within a single QuerySurge instance, rather than from one instance to another. For all three modes, the source/target DB and app connection info should point to the same QuerySurge instance. Following are the available export_merge.properties configuration options:

[srcHttpProtocol=(http|https)] srcHttpHost=HostnameOrIP srcDBHost=HostnameOrIP srcHttpPort=PortNumber srcDBPort=PortNumber QuerySurgeUid=Username QuerySurgePwd=Password projectId=ProjectIdNumber # omit in QuerySurge versions <= 7.2; required in QuerySurge 8.0+ workspace=WorkspaceDirectoryPath mode=(localSuiteCopy|populateSuite|localQPCopy)[,(mapConn|noMapConn)][,mapQPName] sourceList=(DesignFolderPath|SuiteId|SuiteName) targetList=(DesignFolderPath|SuiteId|SuiteName) [connectionMap=SourceConnName:TargetConnName[[,SourceConnName:TargetConnName]...]] [qpNameMap=SourceSuiteName:TargetSuiteName[[,SourceSuiteName:TargetSuiteName]...]]

Note: Local copy modes are intended for use with the same QuerySurge instance as both the source and target. For QuerySurge 8.0 and above, these modes must additionally be used with the same source and target project.

Copy Suite

Copy a suite, including its properties and not including its QueryPairs, to a suite with a new name in the same QuerySurge instance. If a suite with the target name already exists, a new suite will be created with the timestamp appended to its name. If connection mapping is enabled, and the specified source suite uses source and/or target connection overrides, then the connections used for overrides will be mapped accordingly.

Sample export properties without connection mapping:

Sample export properties with connection mapping:

srcHttpHost=localhost srcDBHost=localhost srcHttpPort=80 srcDBPort=3306 projectId=104 # omit in QuerySurge versions <= 7.2; required in QuerySurge 8.0+ workspace=C\:\\merge\\output\\ QuerySurgeUid=admin QuerySurgePwd=admin mode=localSuiteCopy,mapConn sourceList=myDevSuite targetList=myStagingSuite connectionMap=ZCITY_SOURCE:DW_OLD,DW_TARGET:DW_UPGRADE

Sample import properties (with or without connection mapping):

trgHttpHost=localhost trgDBHost=localhost trgDBPort=3306 trgHttpPort=80 projectId=104 # omit in QuerySurge versions <= 7.2; required in QuerySurge 8.0+ workspace=C\:\\merge\\output\\ QuerySurgeUid=admin QuerySurgePwd=admin # omit mapConn; it will be inferred from the workspace contents mode=localSuiteCopy

Add Folder of QueryPairs to Suite

Add QueryPairs from the specified folder (and all contained subfolders) to the specified suite in the same QuerySurge instance. The specified suite must exist before running the export.
QueryPairs already in the suite will remain; adding QueryPairs that already exist in the suite will be
ignored (i.e. will not be added a second time).

Sample export properties:

Sample import properties:

Copy Contents of QueryPair Folder

Add QueryPairs from the specified folder (and all contained subfolders) to the folder in the same QuerySurge instance. The specified target folder must exist before running the import. Folders and QueryPairs already in the target folder will be updated; those that do not exist will be created. QueryPair names can be mapped to new names, and connection mapping is also available.

Sample export properties without connection or QueryPair name mapping:

srcHttpHost=localhost srcDBHost=localhost srcHttpPort=80 srcDBPort=3306 projectId=104 # omit in QuerySurge versions <= 7.2; required in QuerySurge 8.0+ workspace=C\:\\merge\\output\\ QuerySurgeUid=admin QuerySurgePwd=admin mode=localQPCopy sourceList=QueryPairs/XMART - Dev targetList=QueryPairs/XMART - Stg

Sample export properties with connection and QueryPair name mapping (each can be used
independently):

srcHttpHost=localhost srcDBHost=localhost srcHttpPort=80 srcDBPort=3306 projectId=104 # omit in QuerySurge versions <= 7.2; required in QuerySurge 8.0+ workspace=C\:\\merge\\output\\ QuerySurgeUid=admin QuerySurgePwd=admin mode=localQPCopy,mapConn,mapQPName sourceList=QueryPairs/XMART - Dev targetList=QueryPairs/XMART - Stg connectionMap=ZCITY_SOURCE:DW_OLD,DW_TARGET:DW_UPGRADE qpNameMap=QP ABC Dev:QP ABC Stg,QP XYZ Dev:QP XYZ Stg

Sample import properties without connection or QueryPair name mapping:

trgHttpHost=localhost trgDBHost=localhost trgDBPort=3306 trgHttpPort=80 projectId=104 # omit in QuerySurge versions <= 7.2; required in QuerySurge 8.0+ workspace=C\:\\merge\\output\\ QuerySurgeUid=admin QuerySurgePwd=admin mode=localQPCopy sourceList=QueryPairs/XMART - Dev targetList=QueryPairs/XMART - Stg

Sample import properties with connection mapping:

Note: If connection mapping is enabled for export, it can be disabled for import if desired. QueryPair name mapping can only be enabled or disabled before exporting.

Articles in this section

Using QuerySync to Synchronize QuerySurge Instances

File Structure

Server-to-Server Sync Modes

Use of export_merge.properties

Access Parameters

workspace

mode

sourceList

connectionMap and mapConn mode modifier

Update-or-create mode

Connection mapping mode

suiteNameMap and mapSuites mode modifier

Use of import_merge.properties

Access Parameters

workspace

mode

connectionMap, suiteNameMap

A Worked Example - Sync Folder of QueryPairs and Map Connections

Local Copy Modes

Copy Suite

Add Folder of QueryPairs to Suite

Copy Contents of QueryPair Folder

Comments

Articles in this section

File Structure

Server-to-Server Sync Modes

Use of export_merge.properties

Access Parameters

workspace

mode

sourceList

connectionMap and mapConn mode modifier

Update-or-create mode

Connection mapping mode

suiteNameMap and mapSuites mode modifier

Use of import_merge.properties

Access Parameters

workspace

mode

connectionMap, suiteNameMap

A Worked Example - Sync Folder of QueryPairs and Map Connections

Local Copy Modes

Copy Suite

Add Folder of QueryPairs to Suite

Copy Contents of QueryPair Folder

Related articles