Stelo Technical Documents

Automating SQDR tasks using PowerShell

Last Update: 26 May 2024
Product: StarQuest Data Replicator
Version: v6.10 and later
Article ID: SQV00DR044

Abstract

SQDR tasks can be controlled programmatically using PowerShell. For instance, the combination of PowerShell and the Windows Scheduled Tasks allow you to easily perform SQDR tasks such as running a snapshot subscription or pausing an incremental subscription group on a scheduled basis.

This information is subject to change in future versions of SQDR. Note that parameters such as source name may be case sensitive.

This document refers to the PowerShell support in SQDR v6.11 and later. For information about the COM-based programming support included in SQDR v5, refer to Automating SQDR 5.x tasks using COM (VBScript, PowerShell, Visual Basic, and C#).

Solution

• System Requirements
• Remote Servers and RBAC
• Using PowerShell
• Examples included in SQDR distribution
• Additional Examples
• Reference - list of commonly-used methods
  • Sources & Destinations
    
  • Pause/Resume
    
  • Service Properties
    
  • ODBC Information
    
  • Miscellaneous
    
  • Configuration-related methods
    
  • Service API's
• Service control
• Scheduling
• Troubleshooting

System Requirements

PowerShell 5.1 or later is required. Enter $PSVersionTable to display the version. PowerShell 5.1 is included as part of Windows Server 2016 and Windows 10 and later. If you are using Windows Server 2012r2 or Windows 8.1, you can download PowerShell 5.1 from Microsoft.

PowerShell is also available from Microsoft for non-Windows platforms such as Linux and MacOS.

• Install PowerShell as supplied by Microsoft.
• If SQDR has been installed on Linux, the Tools subdirectory is located at /opt/StarQuest/sqdr/tools. Otherwise copy the entire Tools subdirectory from a system where SQDR has been installed, or from the SQDR installer media.
• Enter the command pwsh to run PowerShell on Linux or MacOS.

Remote Servers and RBAC

For simplicity, the examples below assume that you are running the PowerShell cmdlet on the system where SQDR is installed, and that RBAC (Role Based Access Control) has not been enabled. In this case, the connection string is optional.

You can perform operations on remote SQDR servers if the following prerequisites are met:

• You must have network connectivity to the remote system.
• The listener for non-persistent connections (grcpTransADDRPORT) should be defned to listen on the appropriate interface or all interfaces e.g. 0.0.0.0:7738. Restart the SQDR service after changing this value with SQDR Service Properties.
• If the Windows Firewall is enabled on the remote system, create an inbound rule to allow access to the SQDR service application (C:\Program Files\StarQuest\SQDR\sqdrsvc.exe) or to the defined listening port (e.g. 7738).
• Similarly, if the SQDR service is running on Linux and the firewall is enabled, allow access to the defined listening port e.g.
  # firewall-cmd --add-port=7738/tcp --permanent
• You can install the "Data Replicator Manager" (manager-only) installer package on Windows systems that otherwise do not have SQDR installed.

Use the -ConnStr host:port parameter to specify a remote connection e.g.

Start-Replication -ConnStr mysqdr.mydomain.com:7738 -SourceName MYSOURCE -SubscriptionName MYTAB

 

If RBAC (Role Based Access Control) is enabled, you will need to supply credentials
as part of the connection string:

local:

Start-Replication -ConnStr myuser:mypassword@127.0.0.1:7738 -SourceName MYSOURCE -SubscriptionName MYTAB

remote:

Start-Replication -ConnStr myuser:mypassword@mysqdr.mydomain.com:7738 -SourceName MYSOURCE -SubscriptionName MYTAB

 

Using PowerShell

The SQDR PowerShell assembly (SQDRPowerShell.dll) and dependencies are installed by the SQDR installers in the Tools subdirectory.

• If desired, copy the entire Tools subdirectory to a working folder.
• Issue this command to import the .NET assembly:

import-module <path>/SQDRPowershell.dll
e.g.
import-module "C:\Program Files\StarQuest\SQDR\Tools\SQDRPowershell.dll"

or

import-module "/opt/StarQuest/sqdr/tools/SQDRPowershell.dll"

In the case of a Manager-only installation:

import-module "C:\Program Files\StarQuest\SQDR Manager\Tools\SQDRPowershell.dll"

• Alternatively, read the installation directory of SQDR from the registry and use that to locate the assembly:
  

if ($IsLinux){
$InstallPath = (Get-Content "/var/sqdr/sqdrsvcreg.conf" | ConvertFrom-Json).SOFTWARE.StarQuest.SQDRSVC.InstallPath
}
else {
$InstallPath = Get-Item -Path Registry::HKEY_LOCAL_MACHINE\SOFTWARE\StarQuest\SQDRSVC | Get-ItemPropertyValue -Name "InstallPath"
}

import-module -name $InstallPath"Tools/SQDRPowershell.dll"

• To list the cmdlets in the assembly:

Get-Command -Module SQDRPowerShell

Note that the following are not currently implemented and can be ignored:
KeyChecker
EnableKeyLog
GetKeyLogEnabled

 

• Use get-help to get help for any of the cmdlets e.g.:

PS> get-help Start-Replication

NAME
Start-Replication

SYNTAX
Start-Replication [[-ConnStr] <string>] [-SourceName] <string> [-SubscriptionName] <string> [<CommonParameters>]

For example:

Start-Replication -ConnStr 127.0.0.1:7738 -SourceName MYSOURCE -SubscriptionName MYTAB

The -ConnStr parameter is optional If the SQDR service is running on the local machine and using the default listening ports (7737 & 7738) and RBAC (Role Based Access) is not enabled:

Start-Replication -SourceName MYSOURCE -SubscriptionName MYTAB

If RBAC (Role Based Access Control) is enabled, supply credentials
as part of the connection string:

Start-Replication -ConnStr myuser:mypassword@127.0.0.1:7738 -SourceName MYSOURCE -SubscriptionName MYTAB

Some cmdlets use a JSON file for input or output; currently you must specify an absolute path for the JSON files. The help text will identify which cmdlets can use JSON files.

Examples included in SQDR distribution

Several PowerShell examples, illustrating common functions such as pausing and resuming incremental groups, deleting or moving a subscription, or checking status, are installed with SQDR in the Tools subdirectory. See the topic PowerShell Scripts in the Tools section of the SQDR Help file.

In addition, several VB Script scripts (TargetChecker.vbs and PauseResumeIRGroup.vbs) are included. These are documented in the topic TargetChecker and PauseResumeIRGroup (VBS) in the Tools section of the SQDR Help file. These scripts are compatible with the scripts with the same names supplied in SQDR v5, but use PowerShell rather than COM to communicate with SQDR.

PowerShell Examples

In the examples below, substitute your own Source, Group, and Subscription names for MYSOURCE, MYGROUP, & MYTAB.

PowerShell Example 1 - Running a subscription or a group

Copy the following text into a file called runsub.ps1, and then execute powershell -F runsub.ps1, or open a PowerShell command window and enter .\runsub.

Start-Replication -ConnStr 127.0.0.1:7738 -SourceName MYSOURCE -SubscriptionName MYTAB

or (if the service is local and using the default listening port):

Start-Replication -SourceName MYSOURCE -SubscriptionName MYTAB

To run a group rather than an individual subscription, omit the -SourceName parm:
Start-Replication -SubscriptionName MYGROUP

PowerShell Example 2 - Exporting All Service Properties

Export-Properties -JsonFileOut C:/temp/props.json

 

Reference

Note: the following describes commonly-used cmdlets and is not complete. Use the command Get-Command -Module SQDRPowerShell to list all available cmdlets and help <cmdlet> to get information about a particular cmdlet.

You must specify an absolute path for any JSON files.

Sources & Destinations

Show-SrcDest retrieves connection information where srcDestType is one of the following:
1 = Source
2 = Destination
3 = Staging connection

Show-SrcDest -SrcDestType 1
Show-SrcDest -SrcDestType 2
Show-SrcDest -SrcDestType 3

Show-SrcDestExtended -SrcDestName MYSOURCE

Subscriptons & Groups

Show-GroupMembers -GroupName MYGROUP

Show-Subscriptions -SourceName MYSOURCE
Show-Subscription -SourceName MYSOURCE -SubscriptionName MYTAB

Show-GroupStatistics -GroupName MYSOURCE
Show-SubscriptionStatistics -SourceName MYSOURCE -SubscriptionName MYTAB
Reset-GroupStatistics -GroupName MYSOURCE

Reset-SubscriptionStatistics -SourceName MYSOURCE -SubscriptionName MYTAB

Pause/Resume

Pause/Resume an incremental source, destination, or group. Specify n=0 to stop polling; n=1 to resume polling.

Enable-SourcePoll -SrcDestName MYSOURCE -EnablePolling 0
Enable-SourcePoll -SrcDestName MYSOURCE -EnablePolling 1

Enable-TargetPoll -SrcDestName MYDEST -EnablePolling 0
Enable-TargetPoll -SrcDestName MYDEST -EnablePolling 1

Enable-IncrementalPolling -GroupName MYIRGROUP -EnablePolling 0
Enable-IncrementalPolling -GroupName MYIRGROUP -EnablePolling 1

Connect-Subscriptions is similar to the Reset I/R group function:
Connect-Subscriptions -GroupName MYIRGROUP -EnablePolling 1
Connect-Subscriptions -GroupName MYIRGROUP -EnablePolling 0

Service Properties

The following cmdlets return a string, a long integer, or a BOOLEAN value:

Get-StringProperty -Name buildVersion
Get-Int32Property -Name bcpCommitAt
Get-BooleanProperty -Name archiveIgnoreDeletes

Use the SQDR Service Properties application to view all available service properties, or the Export-Properties cmdlet to export all of the available properties to a JSON file. Most of the properties are related to configuration and are typically viewed and modified using Data Replicator Manager or SQDR Configuration, with the exception of the Advanced Settings documented in the Appendix of the SQDR Help file.

You can use Set-StringProperty, Set-Int32Property, or Set-BooleanProperty to set a service property; these cmdlets should be used with caution, as there is value checking involved.

Set-Int32Property -Name bcpCommitAt -Value 2001
Set-BooleanProperty -Name addUnsupportedDrivers -Value 1

You can also use the Export-Properties and Import-Properties described in the Service API's below.

ODBC Information

Show-DSN can be used with either an ODBC data source or with a connection string:

Show-DSN -DsnName 'MYDSN' -UserID MYUID -Password password -UseIntegratedSecurity 0

Show-DSN -DsnName 'StarSQL (64-bit)' -UserID MYUID -Password password -ConnectionStringOverride 'HostName=MYHOST;Port=446;Server=MYRDB;IsolationLevel=2;PkgColID=STARSQL' -UseIntegratedSecurity 0

Miscellaneous

• Remove-Connection -ConnectionID connection-number
  
• Test-ServiceDown -Wait seconds
  This command will shut down the service if it is running.
  
  
• Show-ServiceUserid
  
  
• Invoke-Query -Script IR_group.sql -Where "GROUP_NAME like 'MYGROUP'"

This command invokes one of the pre-defined queries distributed with SQDR; these are found in the scripts subdirectory of the Program directory e.g. C:\Program Files\StarQuest\SQDR\scripts\mssql (Windows, using SQL Server for the control database) or /opt/StarQuest/sqdr/scripts/db2luw (Linux, using Db2 LUW for control database).

The purpose of each script is documented in the drmgr help file; look for the topic Query Tool under Using the Replication Manager. Additional usage information is documented in the scripts themselves.

When using Invoke-Query, supply only the script name and desired criteria (WHERE and OrderBy); you do not need to specify the path, DBMS type, or connectivity info (credentials and database name) as this information is already known by SQDR.

Results can be written to a JSON file.

In drmgr, this feature can be accessed by right-clicking on the server and name selecting Query... from the drop-down menu.

• Show-RBACJournal [[-ConnStr] <string>] [[-JsonFileOut] <string>]

Retrieves the information displayed in the Logs folder of Data Replicator Manager. In addition to RBAC information, this may contain information about scheduled tasks performed, error messages from the service that can be viewed in the Event Log, etc. If a JSON file is not specified, then output is displayed on the console.

• Remove-RBACJournal - Clear the log.
  
  
• Show-Scheduler [[-ConnStr] <string>] [[-Status] <string>] [[-Enabled] <int>] [[-Name] <string>] [[-JsonFileOut]
  <string>]

Retrieves information about scheduled tasks. If a JSON file is not specified, then output is displayed on the console. SQDR 6.20 & later only.

Configuration-related methods

The above methods are either operational or retrieve information. The following have the potential to damage your configuration and should be used with caution. In general we recommend using the Data Replicator Manager for configuration changes. Also be aware that a running instance of Data Replicator Manager may get confused about the state of a configuration if you make changes from another application.

Remove-GroupMember -GroupName MYGROUP -SourceName MYSOURCE -SubscriptionName MYTAB
Remove-Group -GroupName MYGROUP -DeleteSubscriptions 0
Remove-Group -GroupName MYGROUP -DeleteSubscriptions 1

Set the -DeleteSubscriptions parameter to 1 to delete the member subscriptions as well as the group. This is applicable only to snapshot groups as deleting incremental groups always delete the member subscriptions.

Move-Subscription -SourceName MYSOURCE -SubscriptionName MYTAB -ToGroupName NEWGROUP

Remove-Subscription -SourceName MYSOURCE -SubscriptionName MYTAB

Rename-Subscription -SourceName MYSOURCE -SubscriptionName MYTAB1 -NewSubscriptionName MYTAB2

Remove-SrcDest -SrcDestType n -SrcDestName MYNAME
where n is one of the following:
1 = Source
2 = Destination
3 = Staging Connection
Use with caution. If the source is enabled for incremental replication, be sure to delete the matching Staging Connection.

Rename-Group -GroupName MYGROUP1 -NewGroupName MYGROUP2

Advanced Configuration

The following require detailed understanding of the JSON files used as input and are beyond the scope of this document:

Set-Group
Set-GroupMembers (snapshot groups only)
Set-GroupSchedule
Set-SrcDest
Set-Subscription
Set-SubscriptionSchedule (snapshot subscriptions only)

However, you can use the appropriate Show-object or Get-object command to obtain the JSON of an existing configuration and use that as a guide.

The following examples show how to create a JSON file of an existing object, remove that object, and recreate it.

• Set-GroupMembers (snapshot groups only). Note that Set-GroupMembers will first delete all members of the group, and then add the group members specified in the JSON file.

1. Show-GroupMembers -GroupName 'mygroup' -JsonFileOut 'C:\temp\mygroup_members.json'
   
2. Remove-GroupMember -GroupName 'mygroup' -SourceName 'mysource' -SubscriptionName 'mysub'
3. Edit mygroup_members.json, replacing the name EnumerateGroupMembers with SetGroupMembers. Note that the group name in the JSON file is ignored; the group name specified on the command line is used.
4. Set-GroupMembers -GroupName 'mygroup' -JsonFile 'C:\temp\mygroup_members.json'

• Set-GroupSchedule (snapshot and I/R groups)

1. Get-Group -GroupName 'mygroup' -JsonDataSchedulesFileOut 'C:\temp\test_schedule.json'
2. Use drmgr to remove the schedule.
3. Set-GroupSchedule -GroupName 'mygroup' -JsonSchedulesFile 'C:\temp\test_schedule.json'

• Set-SubscriptionSchedule (snapshot subscriptions only)

1. Show-Subscription -SourceName 'mysource' -SubscriptionName 'mysub' -JsonDataSchedulesFileOut 'C:\temp\sub_schedule.json'
   
2. Use drmgr to remove the schedule.
   
3. Set-SubscriptionSchedule -SourceName 'mysource' -SubscriptionName 'mysub' -JsonSchedulesFile 'C:\temp\sub_schedule.json'

Service API's

The following cmdlets replace the function of Invoke-ServiceCommand. This allows finer granularity when using RBAC (Role Based Access Control).

• Export-Subscription
  
• Import-Subscription
  
• Export-Subscriptions
  
• Import-Subscriptions
  
• Export-Properties
  
• Import-Properties
  
• Show-DBInfo
• Show-SrcDestData
  
• Show-IRMetaData
  

Examples

Export-Subscription -SourceName MYSRC -SubscriptionName MYTAB -JsonFileOut C:/temp/mytab.json

Export-Subscriptions -GroupName MYSRC_MYDEST -JsonFileOut C:/temp/subs.json

Export-Properties -JsonFileOut C:/temp/props.json

Import-Properties -JsonFile C:/temp/importprops.json

where the JSON file contains something like this:
{"addUnsupportedDrivers": true,"applyMaxAutoSnapshotInterval": 180}

Show-DBInfo -SrcDestType 1 -SrcDestName MYSRC

Show-SrcDestData -SrcDestType 3 -SrcDestName MYSRC

where SrcDestType is one of the following values

• 1=source
  
• 2=destination
  
• 3=staging
  

Show-IRMetaData -SourceName MYSUB -SubscriptionName MYTAB

This will return column information for the subscribed table.

 

Invoke-ServiceCommand (deprecated)

NOTE: The Invoke-ServiceCommand has been replaced by the service API's and may be removed in future versions of SQDR.

Invoke-ServiceCommand can be used for complex tasks such as exporting and importing the subscriptions of an incremental group or exporting or modifying the service properties.

Use of this command involves JSON, though in some cases the JSON is simple enough to supply on the command line (parameter -RequestJson) rather than in a JSON file ((parameter -RequestJsonFile). See the PowerShell example above for an example.

The following commands may be run directly, without requiring a JSON input file. Use single quotes to delimit a string that contains double quotes.

Invoke-ServiceCommand -RequestJson $cmd

where $cmd is one of the following:

'{"command": "exportsub", "parameters": {"SourceName": "MYSOURCE","SubscriptionName":"MYTAB"}}'
# Exports any subscription (snapshot or incremental):

'{"command": "exportsubs", "parameters": {"GroupName": "MYGROUP"}}'
# Exports IR Group members

'{"command": "importprops", "parameters": {"addUnsupportedDrivers": true,"applyMaxAutoSnapshotInterval": 180}}'
# Import one or more service properties (use with caution).

'{"command": "exportprops", "parameters": null}'
# Exports all the service properties

'{"command": "getdbinfo", "parameters": {"srcDestName": "MYSOURCE", "srcDestType": 1}}'

'{"command": "getsrcdestdata", "parameters": {"srcDestName": "MYDEST", "srcDestType": 2}}'

'{"command": "getirmetadata" , "parameters": {"SourceName": "MYSUB", "SubscriptionName": "MYTAB"}}'

Service Control (Windows)

The following commands let you start, stop, restart, or get the status of the service. All but Get-Service need to be run from an elevated PowerShell environment. Note that these are built-in PowerShell commands and do not involve the SQDR PowerShell assembly, and are available only when running PowerShell on Windows.

Start-Service -Name SQDRSVC
Stop-Service -Name SQDRSVC
Restart-Service -Name SQDRSVC
Get-Service -Name SQDRSVC

Here are the equivalent commands when run from a remote command. Note that the user must be a domain user that is a member of the local Administrators group of the system where the service is running and the Windows firewall must allow the connection.

Get-Service -ComputerName rmtsys -Name SQDRSVC | Stop-Service -Verbose
Get-Service -ComputerName rmtsys -Name SQDRSVC | Start-Service -Verbose
Get-Service -ComputerName rmtsys -Name SQDRSVC | Restart-Service -Verbose
Get-Service -ComputerName rmtsys -Name SQDRSVC

Scheduling:

SQDR-managed Scheduled Tasks:

Scheduled tasks that are configured within SQDR are handled by the SQDR service itself. The SQDR service must be running for scheduled events to occur; however you can use the Windows Scheduler to start the SQDR service if necessary, using NET START.

Windows Scheduler:

Use Add scheduled tasks of the Scheduled Tasks control panel to set a schedule to run a PowerShell script.

Specify powershell as the application and -F <full path to script file> (e.g. -F C:\Scripts\myscript.ps1) for arguments.

You will also need to supply user & password credentials (run as user..) and configure when you want the script to run. Typically you can use SYSTEM (Local System Account) as the user; no password is required in that case.

You may need to specify the task as "run with the highest privilege".

To capture output when running as a scheduled task, you can wrap the application or script in a command file which redirects the output and instruct Scheduled Tasks to use that script. For example

MySQDRscript.cmd:

@echo Started: %date% %time% >> C:\scripts\output.log
PowerShell -noninteractive -NoLogo C:\scripts\MySQDRscript.ps1 2>&1 >> C:\scripts\output.log
@echo Ended: %date% %time% >> C:\scripts\output.log

crontab (Linux & MacOS)

crontab is the UNIX equivalent of Windows Scheduler and can be used for scheduling tasks.

Troubleshooting

Depending on the source of the error, it may be necessary to check the Application event log for error messages from the SQDR service. The message may also appear in the Logs folder of Data Replicator Manager.