Most optimized way to move data from on premise to azure sql

Question

I am working on a project, where we are moving data from on premise to data lake first. Two tables are SCD 1 and SCD2 type.

for the scd 1 and scd 2 should we create a seperate pipeline that when data gets created they automatically get triggered. or should I create a single pipeline only .

my flow is like read watermark tables-> foreach-> get max delta value-> check if last processed is smaller than delta then copy data in the folders. Like (schemaname/tablename)

Answer 1

Hi @Aniket Gupta

Thanks for the Question and using Microsoft Q&A platform.

 There are 7 ways to migrate an on-premise SQL database to Azure. Based on my understanding, you're migrating only two tables from on-prem to Azure SQL Database, so there's no need to create a separate pipeline. A better recommendation would be to use transactional replication for this activityTransactional.

Replication Replication:

Transactional replication duplicates changes between two databases, including stored procedures, database objects like tables, views, data, and more. Data is copied in real-time from the source server (Publisher)  to the receiving database (Subscriber). You have to add the Azure SQL Database as a subscriber and On-Premises SQL Database as the publisher when doing a transactional replication. It will let you easily migrate the data from your on-premises SQL Server to Azure SQL Database. Thus, transactional replication is a good backup for frequent, daily database changes. You can only replicate tables with a primary key. So if you have many tables without them, you’ll need to pair them with other techniques to move those tables.

Migration from SQL Server to Azure SQL Database Using Transactional Replication | Microsoft Community Hub

SQL Server to Azure SQL Database: Migration guide | Microsoft Learn

Database Migration Services (DMS)

Post the assessment done by DMA, you can use the database migration service(DMS)  to migrate workloads from different databases to the Azure Data platform. The migration can be offline or online.

Online the downtime is minimal and only happens in the final step when switching to the new environment. DMS allows you to assess, identify and migrate from on-premises to Azure SQL database by continuously synchronizing after the initial schema/data is moved. Using the assessment tools, you can assess migration blockers and features that hamper smooth data transition.  Online migration using DMS supports, SQL, PostgreSQL, Oracle, MySQL, RDS MySQL, and other sources that can be migrated to Azure SQL Data Base, Azure SQL VM, Azure SQL DB Managed Instance, Azure Cosmos DB, MySQL, PostgreSQL, etc.

Offline migration involves system downtime. Offline you can migrate just the schema or the schema and data in one shot. Ideally, you should use this method for testing small databases or those applications where more extended downtime does not affect productivity.

DACPAC

DACPAC, or Data Application Component package, is a file that contains the details of your database objects, such as tables, stored procedures, views, and other database details.   Using SSDT, SSMS, or SQLpackage.exe, developers and database administrators can create a single DAPAC file consisting of database objects and restore one for Azure SQL Database.  

BACPAC Import/Export

Using BACPAC or Backup Package file, you can import a SQL Server database into Azure SQL Database. Because it contains both the database schema and data, you can perform the export and import efficiently. You can bring your entire On-Premise SQL database to Azure SQL Database. 

Using the export data-tier application option on the on-premises database, you can export to BACPAC. You can import the BACPAC as an Azure SQL Database using the Azure portal. You can import the BACPAC file using SSDT, sqlpackage.exe, or SSMS.

Using the Import data-tier application option, you can use the BACPAC file to perform a restore to Azure SQL Database. It is easy to use if you are migrating small on-premises databases. But when you have to work with larger databases, use command line tools like sqlpackage.exe.

Import Export Using the BCP

BCP or Bulk Copy Program is not really a migration tool. You can combine it with others tools like transactional replication if you want to import an On-Premises database without primary keys. You can export the table to a data file using the Bulk Copy Program (BCP) utility. Once that is done, you can import data into an Azure SQL Database using the import option. You must ensure the receiving server has the correct formatting for the table structures. In case of any error in the table structure, the BCP import process will fail. 

Suggested read: How to import and export bulk data using Bulk Copy Program

Generate Transact-SQL Scripts

You can use T-SQL Scripts to migrate and deploy a business applications toolset from the On-Premise SQL Server database to Azure. to migrate and deploy a business applications toolset from the On-Premise SQL Server database to Azure. It helps generate a plain text script file using the Transact-SQL language. The text file includes the complete database content and the database structure. The text can be viewed and edited using the SQL Server Management Studio or any text editor. It is very similar to BACPAC but there are limitations to SQL scripts - they might not work on very large databases.

 GeoPITS Database Management Platform for Seamless Database Migration 

GeoPITS has expertise in SQL Server database administration, performance tuning, problem-solving, and in-depth SQL Server Database training. If you are planning a migration, we are your go-to partner who can assist you in planning and implementing a complete database migration to Azure SQL Database Get in touch with us today.

Please refer this documentation link: https://www.geopits.com/blog/7-ways-to-migrate-on-premise-sql-database-to-azure.html

Hope this helps. Do let us know if you 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.