Challenges of move SQL Server data to Microsoft Azure SQL Server PaaS

-

This post covers one of the challenges we face when moving our infrastructure from on-prem to Microsoft Azure: Moving SQL Server data from on-premise to Microsoft SQL Server Paas.

Moving our data was a challenge for the following reasons:

  • Our company has tight controls and rules about what software/connectivity is allowed on our network
  • The database that we need to migrate after compression was over 100GB in size.
  • No information about how long other migration processes would take. As needed to ensure our migration was complete before the next Monday on the weekend of our migration.

For most people migrating their data, the best option is to use Azure Database Migration Service, especially when transferring a database size that we were. We couldn't use the Azure Database Migration Service for the following reasons:

  • Azure Database Migration Service requires software installed on a server within our network
  • Connections to both databases were needed.
These were not acceptable for our company security teams.

Another approach Azure offers for transferring data is Azure Data Boxes. There are various Azure data boxes options available, which the Azure Data Box Disk. There were a few reasons we had to discount this approach: 

  • The lead time for Azure Data Box disk exceeded our migration window.
  • There are extra costs for using this approach
  • Our use case didn't precisely fit with the use cases for using Azure Data Boxes.

Azure's final method for data transfer, which we used, is SQL Package (DacFx) to create a BACPAC for exporting/importing.  Our risk with SQL Package was as follows: 

  • Can it handle a database of over 100 GB in size (our largest table has 400 millions rows of data)?
  • How long will it take to package, transfer and import?

We found that SQL Package can handle our database size once we resolve our package errors:

  • Cannot access a closed stream
  • Not enough disk space to complete option, even though the final location of the BACPAC file had enough free disk space.

To resolve "not enough disk space error" we found that we needed to move the windows temp folder (TMP and TEMP environment variables) to a disk with as much free space as the database's size.  

For the error: "cannot access a closed stream" we notice the location set by environment variable: LOCALAPPDATA needed to be on a disk with an excess of 80GB of free space.

We were able to complete the process of packaging, copying and importing into Azure within our required timeframe.  Here is our breakdown of each stage of the process: 

  • To export into BACPAC completed in about 9 hours.
  • Copying from our on-premises server to Azure took just over an hour using AZCopy.
  • To Import into our SQL Server PaaS database took about 9 hours to complete.

Comments

Unknown said…
I appreciate you taking the time and effort to share your knowledge. This material proved to be really efficient and beneficial to me. Thank you very much for providing this information. Continue to write your blog.

Data Engineering Services 

Machine Learning Solutions

Data Analytics Solutions

Data Modernization Services
9/5/22 07:10
Post a Comment

Popular posts from this blog

Azure SQL Server IaaS SSIS: A required privilege is not held by the client

-
Image
As mention in a previous post, we are using Azure SQL Server IaaS to help our transition to the cloud. The main reason for this is how some of our SSIS projects, which we inherited, were created. Some were using SSIS to manage the transactions, which uses MSDTC not supported in Azure. Use extra .Net libraries to read JSON and office files; external programs to do file operations like decompression. Migration timelines and priorities we didn't have time to refactor these projects. While ago we started to deploy our SSIS projects to our Azure SQL Server IaaS virtual machine, a Windows 2019 server with SQL Server 2019, to get the following error: SSIS Deployment Error 6522 "A required privilege is not held by the client". We able to resolve our problem by using the steps we found in the following blog posts: System.ComponentModel.Win32Exception: A required privilege is not held by the client while Deploying SSIS Project | Microsoft Docs . Fixing SQL 2012 SSIS Deployment Err
Read more

SSIS File handle leak in For Each Loop

-
I am currently working on a project which needs to load over a 1000 xml files. The files are stored across 10 subfolders. I am using a foreach loop with a file enumerator, which is configured at the top of the folder structure and traverses the subfolders. This loops through the files, load the data and then moves the file to another folder. The package executes fine for a few 100 files but then hangs; this happens after a different number of processed files each time the package is run. While trying to resolve the problem we ran performance counters and noticed that the number open handles increased significantly just about the time Dtexec looked like it had hanged and DTexec also then started taking a lot of the cpu processing time. Update: I put this on the Microsoft forums and got a intresting answer back See Here ; There seems to be a memory leak with foreach loop which should be fixed in sp1.
Read more

Cannot update a database that has been registered as a data-tier application

-
Within SQL Server 2008 R2 a new feature was introduced, which was the data-tier application database. This feature allow developers / database administrators to package a database schema so that it could be deployed to any other database server and versioned. More about this feature can be found here: http://msdn.microsoft.com/en-us/library/ee240739(v=sql.105).aspx or http://dacguy.wordpress.com/ This feature has been included, as I have just found out, to previous versions: SQL Server 2005 via Services Pack 4 and 2008 via Services Pack 2. However SQL Server Management Studio, for SQL Server 2008, hasn't been updated with the tools for the new feature, you have to use the SQL Server 2008 R2 version. The reason for including the support to the previous version might have something to do with the release of the new SQL Server Data Tools (SSDT), the replacement to visual studio database professional, which uses DAC Packs and the framework to deploy a database. I have been using the
Read more