Microsoft SQL Server data protection reimagined: CDC and data masking for secure data processes!
Microsoft SQL Server serves as a central data platform for business-critical applications, data warehouses and operational processes in many organisations. Consequently, sensitive information is frequently stored there – such as personal data, customer data, contract information or financial data. Traditional security measures such as access controls, encryption and network security remain important, but are not always sufficient on their own.
Data protection becomes particularly relevant when production data is replicated, made available for analytical purposes, transferred to test and development environments, or further processed within ETL and integration processes. This is precisely where the combination of Change Data Capture (CDC), data transformation and data masking comes into play.
Capturing only relevant changes instead of reprocessing entire data sets: In production Microsoft SQL Server databases, data is constantly changing. New records are created, whilst existing ones are updated or deleted. If these changes need to be transferred to data warehouses, replication systems, analytics platforms or other target systems, it is often unnecessary to process large tables in their entirety. Instead, Change Data Capture focuses specifically on the changes that have actually taken place!
With IRI Ripcurrent, changes in Microsoft SQL Server and other supported relational databases can be detected. New, modified and deleted records can thus be identified and made available for downstream processes. The advantage is clear: instead of repeatedly moving entire data sets, only the relevant changes are processed.
Intelligently combining CDC and data masking!
Change Data Capture becomes particularly useful when the detected changes are not merely replicated, but processed directly.
Depending on requirements, the data can, for example:
This enables new or modified SQL Server data records to be detected, and sensitive fields to be protected whilst they are being transferred to other systems. As a result, production data does not automatically have to be transferred in plain text to test systems, data warehouses, analytics platforms or other target environments.
Integrate data protection directly into the data flow!
In many traditional data processes, information is first extracted or replicated and only then masked in a separate step. Integrated processing makes this workflow more efficient. By combining CDC, transformation and data masking, data protection can be taken into account whilst the data is in motion.
A typical example: A Microsoft SQL Server application contains customer data such as name, address, telephone number or account details. For an analytics platform, certain changes are required almost in real time.
IRI Ripcurrent detects the relevant changes to data records. During subsequent processing, sensitive information can be pseudonymised or masked before it reaches the target system. The required data structure is preserved, whilst at the same time the disclosure of personal data is reduced.
Applications for analytics, DevOps and test systems: Particularly outside of production systems, there is often an increased risk of sensitive information being unnecessarily disclosed. Whilst development, testing, reporting or analytics environments require realistic data, they usually do not require the actual identities of the individuals concerned.
By combining Microsoft SQL Server CDC and data masking, continuously updated test or analytics datasets can be generated without replicating every change unprotected.
Typical areas of application are:
Detect deltas even without traditional real-time CDC!
Not every scenario requires continuous real-time monitoring. With SortCL, the data processing engine behind IRI CoSort and IRI Voracity, even large tables, files and other data sources can be compared with one another. In this way, inserts, updates and deletes can also be identified as deltas.
This approach is particularly suitable when changes between different data sources need to be analysed or when more complex transformations are to be carried out simultaneously. The detected deltas can then be processed directly – for example, through cleansing, conversion, aggregation, reporting or data masking. This provides a range of approaches: from the continuous capture of database changes to the targeted comparison of large data sets.
Protect MS SQL Server data and keep data processes efficient:
Data protection and data integration do not have to be considered separately. The combination of Microsoft SQL Server Change Data Capture, data replication, transformation and data masking makes it possible to capture relevant changes in a targeted manner and to protect sensitive data even before it is transferred to downstream systems.
Instead of regularly recopying entire data sets and then securing them separately, data changes can be processed and protected directly. This creates a modern data flow in which performance, data integration and data protection are all taken into account. Particularly in data-intensive Microsoft SQL Server environments, this approach can help to make ETL and replication processes more efficient whilst avoiding unnecessary plain-text copies of sensitive data.
Efficiency meets experience: For more than four decades, our software solutions have been supporting companies in data management and data protection – technologically leading, reliable in productive use and applicable across all industries.
In use since 1978: Numerous well-known companies, service providers, financial institutions and state and federal authorities are among our long-standing customers.
Maximum compatibility: Our software supports both classic mainframe platforms (Fujitsu BS2000/OSD, IBM z/OS, z/VSE, z/Linux) and modern open system environments such as Linux, UNIX derivatives and Windows.