We Migrated Over 100 Databases for ASISA Group

Grupo Asisa, owned by the Lavinia Medical Cooperative, is the leading health insurance company in Spain. With a medical staff of more than 40,000 professionals, it has the largest national proprietary hospital network, which enables it to cover the needs of its more than 2.2 million policyholders.

Its status as a cooperative allows it to devote the profits obtained each year to the effective improvement of its healthcare activity through the incorporation of new technologies applied to the constant improvement of its services in direct relation to its policyholders.

How to migrate data base to Azure

Asisa began a global process of digitalization of all their hospitals, medical centers, and clinics in 2012. This allowed them to strengthen their relationship with patients, employees, and collaborators through the capture, storage, and processing of data.

However, with more than 7.5M visits per year, almost 19,000 users, and 2.3M medical records, they soon found it very difficult to work in an agile, flexible, and secure way with their more than 100 databases (with a size of 3TB), which were hosted in a physical Data Server. The lack of agility, scalability, and especially the possible security failures, threatened the management and confidentiality of more than 2M clinical records. And this risk had to be urgently addressed in view of the increasing flow of data created by the company’s own activity through its international expansion process.

Plain Concepts in full collaboration with Microsoft Spain were in charge of designing and executing a migration of all the databases to Azure, allowing ASISA to access an innovative and optimal environment that allows them to manage their resources and investments with agility. And also providing them with an important added value in the health sector: data storage with maximum security.

From the physical Data Server to the Microsoft cloud

Data migration was an extraordinary challenge due to a large number of particularities. We had to work on a critical system that had to be operative 24/7, and was integrated into 14 hospitals and more than 20 clinics, with a migration window of a few hours.

A window that required five months of planning and operational measures, including an intermediate migration to ensure a successful migration process without impacting the day-to-day operation of the group and its systems.

The technological limitation of not being able to perform an upgrade jump of more than 2 versions of SQL Server was overcome by a transactional replication from the 2008R2 environment to the new 2017 environment with a 2014 intermediate environment. And thanks to the implementation of a rollback system, reversing the transactional replication at the time of migration, we guaranteed to be able to go back to any previous scenario at any time of the process with all the information generated from the migration in a short period of time.

It was also necessary to find a different technological solution for the more than 7000 reports, which attacked the OLTP system, undermining its performance. To that effect we used secondary reading replicas for each of the instances, taking advantage of the high availability provided by the availability groups to free up OLTP system resources while providing the same service to both the application and the analytical platform.

Another great challenge was to automate the replication process to minimize migration times. The replication process of each of the databases requires a backup, transmission, restoration, and synchronization of the two databases. And this process had to be done for 100 databases in each jump between SQL Server versions (there were 5 jumps in total), which means 500 operations and 15 TB to process, in addition to the monitoring and support operations.

To do this, we created an application that automated this process and allowed us to parallelize the replication operations. Soon this tool will be available for the SQL Server community.

Benefits

After successfully completing all the stages of the migration, the ASISA Group has moved from working on its databases from a physical Data Server to an Azure environment, a productive and updated scenario with high availability solutions.

• Its analytical platform no longer undermines the OLTP system. The physical restrictions of a Data Center have been eliminated.
• Its SQL Servers 2007 have been optimized. They have managed to save over 50 % of their resources.
• They have placed themselves in a favorable technological scenario for incorporating new technologies such as VR, AR, Machine Learning, Big Data or Blockchain.
• Patient service has been optimized in all its phases since they now have structured information in real time.
• Management and collaboration of the group’s employees, medical centers, and hospitals have been simplified thanks to data homogenization.