{"id":23094,"date":"2023-02-28T20:55:33","date_gmt":"2023-02-28T19:55:33","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=23094"},"modified":"2024-09-10T15:41:29","modified_gmt":"2024-09-10T13:41:29","slug":"estimate-the-downtime-for-an-offline-ms-sql-migration-on-premise-to-azure-sql-database","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/estimate-the-downtime-for-an-offline-ms-sql-migration-on-premise-to-azure-sql-database\/","title":{"rendered":"Estimate the Downtime for an Offline MS SQL Migration: On-Premise to Azure SQL Database"},"content":{"rendered":"\n

As you want to migrate your database workload to the Azure Cloud, you must test different aspects of the migration. An important factor for an offline migration is to estimate the duration of the migration process, so the service owner can plan for downtime. I want to show some points, you should consider when estimating the downtime. For concrete offline migration process, please check my previous blog<\/a><\/p>\n\n\n\n

The idea of this blog came up during the testing phase of databases migration at a customer. The DBA told me, he was testing the migration of 2 databases to an Azure SQL Database: a small (2GB) and a large (200 GB) database. I was curious and started asking him about it. I asked him to give me access to the database, so I could take a look at it and maybe give him some information.<\/p>\n\n\n\n

Effective Database Size<\/strong><\/p>\n\n\n\n

He gives me access to the On-Premise MS SQL Instance. First, I check the size of the database. See Screenshot 1. From the screenshot 1 you can notice that the log file is empty. The assumption (migrating 200 GB) about the size of the database is not correct. The Total Space reserved is 155 GB. The Data Files Space reserved less than 100GB, and 15% are unallocated. So, in this case 82 GB of data will be migrated to the Azure SQL Database.<\/p>\n\n\n\n

\"\"
Screenshot 1: Disk Usage<\/figcaption><\/figure>\n\n\n\n

Extracting the file with SqlPackage<\/strong><\/p>\n\n\n\n

After clarifying the effective among of data, that will be move to the cloud, we decide to estimate the extraction time (T2). As the migration will be done by BACPAC, please check my previous blog<\/a>. The best way for this estimation, is just to test it on your server. We extract the BACPAC it took 20 minutes on the server. The configuration of the server can be found in the screenshot 2. The result of the BACPAC extraction is only 6.5 GB. For comparison, the Compress-BACKUP file of the same database is about 12.7 GB.<\/p>\n\n\n\n

\"\"
Screenshot 2: result of BACPAC extraction<\/figcaption><\/figure>\n\n\n\n

Upload the file to Azure Storage Account<\/strong><\/p>\n\n\n\n

As mentioned before the BACPAC File is only 6.5 GB. This mean, when applying an offline migration, you should not tell your network administrator that a 80 GB (200 GB) Database will be transfer to the network, but only a 6.5 GB BACPAC. So only consider the time you need to transfer the BACPAC file to the Azure Cloud (T3).<\/p>\n\n\n\n

The Import to Azure SQL Database<\/strong><\/p>\n\n\n\n

Of course, the import duration (T4) depends to the service tier you choose. Tip: choose a high service tier to make the import fast.<\/p>\n\n\n\n

Finally: additional tasks<\/strong><\/p>\n\n\n\n

You should also plan time for additional tasks<\/p>\n\n\n\n