In this article, we address best practices through a questionnaire for Qlik Replicate administrators and architects before an environment goes live in production. Having answers to these questions is helpful for a successful deployment and sustainable implementation. Industry wide project management best practices and tools are assumed to be conducted by the customer team.
Content:
Vocabulary used:
DBA: Database Admin
OS: Operating System
DEV: Development
QA: Quality Assurance
UAT: User Acceptance Testing
PROD: Production
HA: High Availabilty
Pre-deployment
- Is it possible to perform a POC (proof of concept)? If a POC already occurred, what would you like to repeat or avoid?
- Establish a process and budget to engage.
- Are the source and target endpoint versions in compliance with Replicate Support Matrix?
- Who is in charge of installing the correct drivers and prerequisites? DBAs, Qlik Replicate administrators, systems engineers, etc.
On roles during your deployment:
- Main a list of names, contacts, and job roles.
- It is strongly recommended to have a project deployment plan with a project management type role and actionable requirements and deliverables.
- What level of approval is needed to make production-level changes? Example: Changes to drivers, shared drives, and testing tasks in production.
- What type of replication are you aiming for?
- Although driven by your Qlik Replicate license, it is helpful to define the following:
- Number and types of sources
- Number and types of targets
-
Scope the Project: What is your business use case for Qlik Replicate? What problem are you trying to solve?
Examples:
- Switching data platform or providers
- Moving data for other teams to use (define your data availability commitment)
- Downstream reporting/analytics
- Cost savings/cheaper data landing location
Qlik Professional Services and Project Management
- Is there a process or budget to engage Qlik Professional services for the following?
- Implementing new server environments and endpoints
- Performance tuning task settings
- Upgrades
- Growth
- Health checks
- Migrations (OS, DB level)
- Disaster Recovery
- Training Qlik Replicate Administrators
- Customization such as scripts
- We recommend leveraging a Project Manager (non Qlik) to help define, document and manage customer requirements for successful implementation.
Source
- Is there a source DBA available to help configure source connection? Are they available for troubleshooting?
- For ODBC drivers specifically, who manages them?
- Do you have security and network team contacts?
- Who manages source database permissions, security, and user IDs? Does Qlik Replicate have proper permissions configured to the source database?
Each endpoint has its own required permissions documented. See Required permissions for an example with Oracle as a source.
- Test opportunity: Work with the Source DBA to confirm security policy, test permissions, and access to required tables.
- What are the source log retention limitations? As a best practice, we recommend 72-hour log retention.
- What is the log rollover frequency? What is the size of the transaction logs?
- If three or more tasks connecting to a source database, have you considered implementing Log Stream?
Source Sizing
Qlik's Professional services can assist with sizing the environment correctly based on your findings and requirements.
- Please work with Qlik Replication project owners and administrators to define values for the following:
- Rate of source changes (changes committed every second, consistently busy, or intermittently busy periods)
- Number of source schemas
- Number of source tables; do you expect to add more tables in the future, if so, how many?
- Size of all tables (rows, data)
- Do tables have LOB columns?
- If so, how many tables and what size?
- Do you allow unlimited LOB size or do you limit LOB size?
- What is the number of acceptable connections from Qlik Replicate tasks?
- Are there any table lookups which need to be performed on related tables? Example: Table lookup for LOBs, or in stream LOBs.
Sizing
Qlik's Professional services can assist with sizing the environment correctly based on your requirements.
- Define the location of servers:
- Source
- Targets
- Qlik Replicate
- Define the type of the servers:
- On-premise
- Cloud hosted
- On-premise, but virtual
- RAM is a key factor in Qlik Replicate performance
- What is the network bandwidth available between endpoints and Qlik Replicate? If bottlenecks are detected during testing or in production, what process needs to be followed to optimize network speed? See Recommended hardware configuration for general system requirements.
- What Qlik Replicate disk storage will be required and what process needs to be followed to increase available disk space? Refer to Professional Service for information on disk space requirements in your specific use case. See Recommended hardware configuration for general system requirements.
- Is your \data folder using a block device (physical, virtual, or iSCI-based)?
- Using NFS or SMB-based storage is not supported due to the associated latency which could greatly degrade the data transfer performance, as well due to reduced reliability.
- See Recommended hardware configuration for general system requirements.
- Is your \data folder in a separated location from the Qlik Replicate install folder?
- If using log stream, is your storage path on a separate drive from your /data directory?
- This helps with disk contention by having separate drives and is highly recommended.
- What is your acceptable latency requirement (time from source commit to target commit)?
- How many tasks will be on your Qlik Replicate server?
- The number of tasks which can be run on a single Qlik Replicate server should not exceed 100. More about tasks: Defining and managing tasks.
- If Qlik Replicate is deployed on Linux, what type of server UI environment will be configured?
Testing
- Define number of environments:
- Do you have at least one non-production environment that mirrors production for data volume, data types, and processes?
- Define a testing plan, scripts, queries for:
- Validating row counts between source and target
- Validating any filters or transformations
- Validating failover if a HA/failover environment
- Validating query times on source and target. If needed, reduce the batch size or increase timeout parameters.
- Validating network performance speeds.
- Validating row size limits
- Practicing a Advanced Run Options, such as restart from time or SCN/LSN. Familiarize yourself with their functionality and when to best use each option.
- Adding or removing a table from a task
- Missing data, data completeness, duplicate data. Do you have tests or a remediation plan to identify missing data records or duplicate records?
- Apply Conflicts settings such as UPSERT mode
- Disaster recovery: Passthrough filter to reload tables which cannot be fully reloaded
- Using a test schema: How to point to a testing schema or location with an isolated table/task in production
- Validate task settings related to Apply changes DDL Handling Policy. These have the potential to make changes on the target DB and need to be tested and understood by the Qlik Replicate admin.
- Validate task settings related to store changes DDL options.
- Test global rules and table level rules to ensure expected outcomes.
- How do we handle maintenance or upgrades on Source DB and target DB?
- How often do we expect these to occur?
- Best practice is to stop/schedule a stop for all Qlik Replicate tasks during source and target maintenance.
- Stress testing a scenario as close to production volume and demand as possible.
- Data volume (high volume transactions), potential end of quarter, EOY events
- Network bandwidth testing
- Large, Full Load activity (test how many Full Loads can run at one time and how to prioritize full load table order)
- Test possible failover or disconnects of source and target database servers
- Test the failover of the Qlik Replicate servers
Additional Administration
- How many admin users will be engaging with Qlik Replicate at one time?
- Who is responsible for managing, adding, removing, or configuring users? See Managing user permissions
- Are notifications configured on the server or on tasks? See Defining notifications.
Consider:
- Alerts for Disk/CPU limits
- Task Level Error Alerts
- What emails are included to receive notifications?
- Is there a change control strategy in place? Qlik Enterprise Manager has audit trail capabilities if also installed with Qlik Replicate environment. See Creating an audit trail.
- Example: Taking snapshots of the Qlik Replicate repository or backups of task JSONs.
- Qlik Replicate and Qlik Enterprise Manager should be on the same build version. If this is not feasible, Qlik Enterprise Manager must be on a more recent build than Qlik Replicate.
- Ensure for upgrades to follow the correct upgrade path process. See Release Notes (Qlik Replicate May 2023).
- Review the documentation on impact of Daylight Saving Time.
Table and data level
- Do tables have PKs? Are PKs changing on any tables? Tables with a PK which is not changing will have the best performance with bulk apply mode.
- For change data capture: are you using Transactional apply vs Bulk apply?
- Read and refer to the user guide for data, primary key, and usage limitations by source and target. See example for Oracle: Limitations and considerations.
- Are there any data cleansing tools or processes in place to prevent bad data from being replicated?
- For Truncate or Do Nothing or manually created tables on the target: Does source table DDL match your target table DDL? If not, special considerations and more testing may be needed.
- Are there any NOT NULL constraint differences between the source and the target?
- Are you capturing soft deletes on tables?
- We recommend reading up on soft delete limitations for your endpoints.
- Understand the requirements for capturing deletes in your sources and targets.
- Are there any character set conversions between the source and target db environments?
- Confirm if the character sets from source tables/columns are supported on the target tables/columns.
Go live
- Do we bring all task online at once or can they be started sequentially or in batches?
- While testing Go Live: Is there an opportunity for a grace period before end users are expecting data?
- Reload: In a worst case scenario, can you perform a reload?
- If not, what is the defined plan? What expertise is needed by the source and target DBA? Do you require signoff from management?
- Day One implementation plans: If Go Live was not successful, what is the backout and postponement plan at task and overall environment level?
- Day One upgrade plans: If an upgrade is not successful, what is the backout and postponement plan at task and overall environment level?
Support
- How long will it take to perform a full reload on each task?
- Are there any task/tables where a Full load is not an acceptable option?
- Do you know how to increase Task and Server logging and/or download diagnostic package?
- Is your team familiar with Qlik's support policy and do the required individuals have access to the support portal to review previously opened cases?
- Do we have a team who can manage 24 hour Severity 1 Support?
- Do you have someone who can access and open severity 1 tickets?
- Are team members working with source or target DBs, operating systems, and networks available to join troubleshooting calls?
- What is process do you have in place for applying Qlik Replicate service packs with fixes? (i.e Dev then prod, or direct on Prod). How quickly can these fixes be applied?
- Best practice: Backup the data directory, export the repository, and carry out individual exports of task JSONs on a regular basis.
- Are you familiar with decrypting log files?
- Do you have access and expertise to install and support correct endpoint drivers?
Security
- Have you tested access to the system with set password?
- Has openSSL been set up and configured for Qlik Replicate UI Console and Server?
Related Content
How To Get Started with Qlik Replicate
Environment
Qlik Replicate
The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.
