Eugene Bereznuk

When I started on this project, I found old corporate databases in multiple formats, including SQL Server 2000 to 2008, Oracle, etc. The Chinese encoding was different everywhere (including outdated and archaic encodings) and needed to be unified to UTF-8. The approximate volume of data was close to 2.5-3TB. The target platform was SQL Server 2016 cluster, and the allowed downtime was 12 hours. After downtime, every hour of data unavailability would cost the company millions.

To save time, data streams were processed consequently for encoding, quality and integrity. A portion of data was preloaded into staging tables.

The project was finished successfully, with the indexes and replication created well within the downtime frame.

It was necessary to regularly export data (with preliminary processing) to a cloud-landing site. The source data was scattered among multiple databases and data warehouses, often requiring dozens of complicated joins.

We created numerous stored procedures which provided processed and formatted data. A fully dynamic SSIS package accepted a stored procedure name as one of the parameters, executed it, exported the results into a CSV file, and placed them on target landing site—all fully automatically, without any intervention.

A global multinational corporation had a portion of its technical, online, and archive data on a vast array of SQL Servers all around the globe. This data was used in reporting as well as exporting to SAP and other systems.

The performance was deteriorating as the data volume and traffic increased.
The preliminary stage included isolating the most problematic queries, triggers, functions, and stored procedures. Also, about 50% of the queries were generated automatically by third-party software and could not be altered.

To solve this, we performed a global audit of the index system which resulted in removing excessive and unused indexes with changing the majority of the existing indexes to newly created ones.

Where possible, function calls were removed from clauses, triggers replaced with external code, and isolation levels and degrees of parallelism were reviewed.

We chose to implement SQL Server Resource Governor to separate system resources between different applications and users. As a result performance problems disappeared completely while guaranteeing future scalability and expandability.