SQL Server 2016 upgrade significantly improves data warehouse at BRITA

The family-owned company BRITA, based in Taunusstein, Germany, is a leader in the field of drinking water optimization and is represented on all five continents. It operates a complex data warehouse to meet its diverse commercial requirements. By upgrading its SQL Server software to the latest version of 2016 SP1, the company is now able to use the various new functions of the Standard Edition and to remain technically up to date with justifiable effort.

noventum consulting and the development of the BRITA Data Warehouse

Performance enhancements through SQL Server 2016 SP1

With SQL Server 2016 SP1, Microsoft presented a consistent programming interface for all editions of the relational engine in mid-2016. The Standard Edition has thus become an even more convincing option, especially for use as a DWH.

Many functions that are very interesting for data warehousing are now available in the Standard Edition with this version, such as compression, which makes it possible to transparently store data for the user with considerably less storage space. As a result, hard disk space for the DWH and corresponding backups can be saved. Depending on the specific workload and hardware used, this can also have a positive effect on performance. In addition to compression, partitioning has also been added to the range of functions. With their help it is possible, for example, to exchange individual partitions during loading in the DWH and thus improve availability. This increases query performance, which is also enhanced by the Column Store Index, which is now also part of the Standard Edition.

In the area of Analysis Services Tabular, SQL Server 2016 has also added new functions relevant to many customers. For example, data models can now be created in multiple languages so that dimensions, cubes and measures can be defined in multiple languages. In addition, the native support of Many2Many relationships offers new exciting possibilities.

Data Warehouse requirements motivate to upgrade server

For some years BRITA has been operating a data warehouse based on SQL Server 2012 technology. Designed in cooperation with the consulting firm noventum consulting, this technical core is the backbone of the demanding controlling processes in the international company. BRITA operates three BOARD servers, one productive and one test DWH. There are several processes, such as the planning and the month-end closing process, which require a connection to the DWH. The majority of user accesses during the day are from BOARD.

In particular, the extended functionality in SQL Server 2016 SP1 was a motivation for BRITA to upgrade the software. Many of the features formerly reserved for the Enterprise Edition are now available in the Standard Edition. In addition, much has been done in the area of Analysis Services Tabular, so the upgrade is also worthwhile in this area. noventum consulting was commissioned to implement the upgrade.

Efficient SQL Server upgrade through professional expertise

The data warehouse at BRITA and the corresponding server environment were mainly created with the support of noventum consulting consultants. Equipped with this detailed knowledge, noventum and BRITA were able to successfully and quickly upgrade to the SQL Server 2016 version and make the necessary adjustments in the system.

The working steps in detail were:

The infrastructure upgrade

• Create a detailed schedule to communicate the data status and availability of the three frontend servers. Additional tuning of storage and compute resources to avoid bottlenecks for short-term parallel operation.

• Setup of a first SQL Server 2016 according to best practices, from hard disk formatting and database file partitioning to configuration of parameters in SQL Server such as parallelism.

• Perform a technical migration to check all functionality within the DWH on the basis of a backup taken over.

• Verification of all interfaces to other systems, e. g. frontend BOARD, a self-developed application for the administration of master data, automatic deployment, etc.

• The DWH is closely connected to SAP and extracts data from several dozen DeltaQs at night. In order to guarantee a fault-free transfer to the cutover, a parallel operation for one week was carried out with additional logical systems and intensively monitored.

• Der tatsächliche Schwenk des Testsystems ging dann an einem Tag erfolgreich über die Bühne. Auf Basis der dabei gemachten Erfahrungen konnte der Schwenk des Produktivsystems nachfolgend ohne Einschränkungen in der Verfügbarkeit umgesetzt werden.

• The actual swivel of the test system then took place successfully on one day. On the basis of the experience gained, the swivel of the production system could subsequently be implemented without any restrictions in availability.

Adaptations

• In addition to the purely technical upgrade, the time was also used to carry out automatic technical validations in addition to several hundred technical test cases, which detect structural anomalies in the SSIS project and thus contribute to improved quality. The deployment has also been extended to include automatic consistency checks in the SSISDB, which has been available since 2012, in order to achieve even greater operational reliability. The anomalies detected in this process were immediately corrected after the migration of the infrastructure.
• A large clean-up operation often offers the potential for far-reaching optimization. For example, there has been a requirement for a long time that one of the approx. 30 subject areas in the DWH can be reloaded individually. For this purpose, all dependencies between SSIS packages were collected during the project and stored as metadata. A dynamic master package based on this was created and now makes it possible to load the master package, including all its dependencies, comfortably through the layer architecture of the Enterprise DWH during the day using one or more subject areas.

Conclusion

The SQL Server 2016 upgrade has significantly improved the BRITA-DWH. Based on the experience gained from four years of joint project work on the system, BRITA and noventum consulting have now been able to determine an optimal sizing. The compression has freed up a large part of the storage space and at the same time slightly improved the loading speed. With the new capabilities of SSAS Tabular, the upgrade provided an optimal foundation for SSAS projects that have now been successfully completed.

It was particularly gratifying that, in addition to the purely technical migration, existing workarounds could also be replaced by new functionalities of SQL Server 2016 and that historically grown inconsistencies in SSDT and SSIS projects could be eliminated. Part of this cleanup was also the creation of a new dynamic master package, which enabled greater flexibility and maintainability during loading of the DWH.

Technologically, the DWH is now fit for the next few years. Especially in the tabular environment, however, the announcements of feature enhancements in SQL Server 2017 are so encouraging that the next upgrade is likely to take place within a year. Thanks to extensive and up-to-date installation instructions, this upgrade can be implemented within a few days.