Migration_Azure

Question

I have migrated the data from SQL server to Azure SQL Database. The size on the SQL dataset was around 250 mb but on migration when i checked the size it is around 500 mb. I would like to know the reason and how can i cross check that

Answer 1

@Prateek Hiremath
Thank you for the question and for using Microsoft Q&A platform.
When migrating data from SQL Server to Azure SQL Database, the size of the data may increase due to various reasons such as differences in data types, indexes, and storage mechanisms.

1. Data types: Azure SQL Database may use a different data type than SQL Server for the same column. For example, SQL Server may use the datetime data type, while Azure SQL Database may use the datetime2 data type. The datetime2 data type uses more bytes than datetime, which can result in an increase in the size of the data.
2. Indexes: Azure SQL Database may use a different indexing mechanism than SQL Server. For example, SQL Server may use clustered indexes, while Azure SQL Database may use columnstore indexes. Columnstore indexes can be more space-efficient than clustered indexes, but they may also require more storage overhead.
3. Storage mechanisms: Azure SQL Database may use a different storage mechanism than SQL Server. For example, SQL Server may use filegroups, while Azure SQL Database may use blob storage. Blob storage can be more scalable than filegroups, but it may also require more storage overhead.
4. Compression: Azure SQL Database may use a different compression mechanism than SQL Server. For example, SQL Server may use row-level compression, while Azure SQL Database may use page-level compression. Page-level compression can be more space-efficient than row-level compression, but it may also require more storage overhead.
5. Statistics: Azure SQL Database may use a different statistics mechanism than SQL Server. For example, SQL Server may use the legacy cardinality estimator, while Azure SQL Database may use the new cardinality estimator. The new cardinality estimator can produce more accurate statistics, but it may also require more storage overhead.

It is normal for the size of the migrated data to increase due to the reasons mentioned above. However, the extent of the increase will depend on the specific characteristics of your data and the migration process. It is important to note that the increase in size does not necessarily mean that there is a problem with the migration. As long as the data is migrated correctly and is accessible in Azure SQL Database, the increase in size should not be a cause for concern.

Hope this helps. Do let us know if you have any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.