Grupo Asisa, owned by Cooperativa de Médicos Lavinia, is the leading health insurance company in Spain. With a medical team of over 40,000 professionals, it has the largest national hospital network of its own, enabling it to cover the needs of its more than 2.2 million policyholders.

Its status as a cooperative allows it to dedicate annual profits 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.

From a Physical Data Server to Microsoft Azure

Thanks to a global process of digitalisation of all of their hospitals, medical centres and clinics which began in 2012, they were able to strengthen their relationship with patients, employees and collaborators through the collection, storage and processing of data. However, with more than 7.5m annual appointments, almost 19,000 users and 2.3m medical records, they soon found it very difficult to work quickly, flexibly and securely with more than 100 databases (with a volume of 3TB) that were housed in a physical Data Server. The lack of agility, scalability and especially the possible security flaws, threatened the management and confidentiality of more than 2m clinical histories of their clients. And that risk had to be urgently remedied in the face of the growing flow of data created by the company’s own activity through its international expansion process.

From Plain Concepts and in full collaboration with Microsoft Spain, we designed and implemented the migration of all of the databases to Azure, allowing ASISA access to an innovative environment which was optimised to allow them to manage their resources and investments with agility. With the added value and great weight in the health sector, such as offering maximum security to the stored data.

Data migration was an extraordinary challenge due to a large number of particularities. It was necessary to work on a critical system operating 24/7 and integrated into 14 hospitals and more than 20 clinics and a migration window of just a few hours. The framework required 5 months of planning and operations, including an intermediate migration to ensure the success of the migration without sacrificing the day-to-day operation of the group and its systems at any time.

The technological limitation of not being able to perform a jump of more than 2 versions of SQL Server was overcome by means of a transactional replication from the 2008R2 environment to the new 2017 environment with an intermediate 2014 environment. And thanks to the implementation of a rollback system, reversing the transactional replication at the time of migration, we have the guarantee of being 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 space of time.

It was also necessary to find a different technological solution for the more than 7,000 reports that attacked the OLTP system, which affected its performance. Thanks to the use of secondary reading replicas of each of the instances, taking advantage of the high availability provided by the availability groups to free resources of the OLTP system offered the same service to both the application and the analytical platform.

Another major 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 finally synchronization of the two databases. And this process had to be done for 100 databases in each jump between versions of SQL Server (there were 5 jumps in total), which meant 500 operations and 15TB to process, in addition to monitoring and supporting operations. To do this, we created an application that automated the process and allowed us to parallelize the replication operations. This tool will soon be available to the SQL Server community.

Benefits

After successfully completing all the stages of the migration, Grupo ASISA has gone from working with its databases from a physical Data Server to the environment created by Azure, a productive scenario and updated with high availability solutions.

  • Its analytical platform has constantly stopped affecting the OLTP system.
  • The physical restrictions of a Data Centre have been eliminated. Its SQL Server 2007 servers have been optimized.
  • They have achieved resource savings of more than 50%. They have placed themselves in a favourable technological scenario for the incorporation of new technologies such as VR, AR, Machine Learning, Big Data or Blockchain.
  • The service to its patients has been optimized in all its phases, with structured information available in real time.
  • The management and collaboration of the group’s employees, medical centres and hospitals have been simplified thanks to the homogenisation of the data environment.