Acceleration and Protection of SQL Server
Optimize, find & protect, migrate & test ETL/Reorgs
Challenges
You may have encountered one or more of these time-consuming problems when working with MS SQL Server databases on-premises or in the Azure cloud:
· Data Discovery: Profiling, Classification, ERDs
· Loading and unloading large tables
Routine utility operations (Reorgs)
Slow queries, SSIS ETL jobs, or Power BI displays
Data migration or replication
Poor data quality or consistency
· Masking PII in tables statically or in applications like MS Dynamics dynamically
Generation of intelligent and secure test data for prototyping, DevOps, demos, etc.
Specific performance diagnostics and optimizations also take time and can affect other users. Finally, stored SQL procedures can also be programmed inefficiently, require optimization, and still take too long.
Solutions
Simple and cost-effective IRI software solves these problems. For special requirements, there are also Services available.
Unload acceleration
IRI FACT (Fast Extract) to accelerate SQL Server unload. Use SQL syntax in FACT's CLI or GUI, to quickly transfer large tables into flat files.
Specify SELECT * FROM very large tables so that you do not burden unloading with qualifiers such as „distinct“, „order by“, or „group by“. Once the data is in flat files, use the program SortCL in IRI CoSort, ... to de-couple, sort, join, group (and report) the extracts much faster... in parallel, outside the database.
Use the IRI Voracity platform, um FACT, CoSort und Ihre Load Utility oder Downstream-Anwendungen in schnellen, kostengünstigen, visuellen ETL operations to combine in Eclipse.
Load acceleration
IRI CoSort for pre-sorting flat files for bulk BCP imports.
Microsoft recommends, that almost every SQL Server table should have a clustered index. First, create the clustered index and pre-sort the input files (on the primary index key) to shorten the Create Index step. Use the SORTED_DATA option when creating indexes to show that CoSort bypassed the slower sorting in bcp.
Reorganization and query acceleration
The Offline Reorg Assistant in the IRI Workbench, to combine and automate the combination of the following functions: Fast extraction, sorting, and reloading of classic (offline) SQL Server reorgs. This helps you maintain your tables in the optimal query order.
Optimization of Transformations (ETL) and BI Data Preparation
IRI CoSort to relieve the database of processing overhead. Leverage the advantages of file system I/O, multithreading, and the proven data transformation power and consolidation of the CoSort SortCL program: Use case
If you're looking for a faster alternative to SSIS or another ETL Tool need, use IRI Voracity, a total data managementPlatform based on CoSortor Hadoop) and frontend in Eclipse for accessing (and integrating and managing) multiple Data sources. Use CoSort or Voracity as well, to process data for Power BI.
Migration and Replication of SQL Server Databases
Database edition for capturing, remapping, reformatting, and creating/populating new tables during migration to and from SQL Server. You can also use IRI NextForm or the program SortCL in IRI CoSort or Voracity use to remap and convert data in SQL Server, Customized reports, Replicates and Federated views of data.
Clean and enrich data (quality)
Classify and Mask Sensitive Data
IRI FieldShield or IRI Voracity) to in SQL Server Find, to classify and different from mask, such as personally identifiable information (PII) or protected health information (PHI). FieldShield applies masking, encryption, and other identification functions to one or more columns and tables at the same time. It also optionally integrates with Integrate Azure Key Vault for managing encryption keys. Use FieldShield to comply with data privacy laws such as HIPAA, PCI DSS, FERPA, and the EC GDPR.
SQL Server Test Data Generation
IRI RowGen or Voracityrow Gen can quickly populate SQL Server or SSIS ETL operations with secure test data. RowGen uses your data models to automatically generate test data for an entire database with referential integrity. A Subsetting Assistant (with masking options) is another available approach that also simultaneously FieldShield- Can include functions to keep the subset data masked. All of the IRI operations mentioned above are command-line driven and can be used with DB cloning tools such as Windocks to be executed to provision virtualized test databases in a cloud or on-premise repository or container. FieldShield and RowGen also function within Azure DevOps CI/CD Pipelines.