0

We are needing to create a copy of all data that is on an Azure SQL Managed instance to an Azure SQL Server. The data will be updated/refreshed regularly. ideally some sort of job on a schedule would help as well but manual is fine. We just cannot find a solution to get the data from MI DB to SQL DB without manually creating a bacpac of MI data and then restoring to SQL DB every time. Any ideas? Has anyone successfully done this?

EDIT: Methods besides pipline/DF method. This is what we were looking at https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/replication-two-instances-and-sql-server-configure-tutorial but minus SQL VM part. Cannot get it to work.

Thanks

Amommy
  • 47
  • 1
  • 8
  • 1
    MS doco is terrible but you may be able to do transactional replication from MI to Azure SQL https://learn.microsoft.com/en-us/azure/azure-sql/database/replication-to-sql-database – Nick.Mc Oct 09 '21 at 07:56
  • Yes MS docs about Azure features are atrocious. I was actually trying to implement it without the setting up a SQL Virtual Machine part. I dont know if this can be done without a SQL virtual machine, all they show is the SQL VM steps. – Amommy Oct 13 '21 at 16:07
  • We are actually rethinking the whole Azure thing, it's been around for a while now but always seems like it's in a perpetual beta state. – Amommy Oct 13 '21 at 16:16
  • You should be able to do transactional replication without a VM from your on-prem to Azure SQL. It's wierd that the only useful solution is old technology, but that's how it is. In my opinion the PaaS Azure SQL is worth it if you can get over not having cross db and not having SQL Agent. There are a lot of things you don't have to worry about (backups, running out of space or capacity). – Nick.Mc Oct 13 '21 at 23:36
  • We dont have on-prem we have a few SQL managed instances and a few SQL servers. What we want to be able to do is copy data from a SQL MI to a SQL DB. As far as cross db I got that to work with 2 SQL DB on same SQL Server. – Amommy Oct 14 '21 at 11:55
  • Also as far as docs https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/replication-two-instances-and-sql-server-configure-tutorial#prerequisites and https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/replication-two-instances-and-sql-server-configure-tutorial#create-a-sql-server-vm it says it requires a 'SQL VM' regardless if on-prem or on an Azure VM. Maybe I'm not getting the VM part, they throw that around and expect everyone to know what they referring to. – Amommy Oct 14 '21 at 12:07
  • I know than on prem replication to azure sql works. MI is a lot like on prem. So what happens when you set up MI as a publisher pushing to an azure sql subscriber? – Nick.Mc Oct 14 '21 at 12:14

1 Answers1

1

You can use the Azure Data Factory pipeline to copy data from Azure SQL Managed Instance DB to Azure SQL Server DB.

  1. Create Azure data factoryresource.

  2. Create Azure SQL Managed Instance linked service for source dataset connection.

enter image description here

  1. Provide Azure subscription and SQL Managed instance details in linked service connection.
  2. You can select different types of authentication methods in the connection.

enter image description here

  1. Create Azure SQL Database linked service for Sink (destination) dataset connection.

enter image description here

  1. Provide your Azure SQL database details in the connection.

enter image description here

  1. Create a new pipeline and select Copy data activity from Activities.
  2. Create Source and sink datasets with database table details in Copy data activity and select source linked service and sink linked service respectively in Source and Sink settings.

enter image description here

You can go through these MS documents for more details on Source and Sink dataset.

NiharikaMoola-MT
  • 4,700
  • 1
  • 3
  • 15
  • Thanks. I am somewhat familiar with pipeline, have tested it doing a Managed inst to mang inst DB. We would rather not use pipeline method. Also We need to copy all tables in a database initially and then changes from Managed Inst DB to SQL Server DB. Also Pipeline source asks for table we have hundreds. – Amommy Oct 13 '21 at 16:01
  • I went back into datafactory after reading your post again last week and there is a workaround for multiple tables but ran into problems if a new column or table table on source. Probably need to add more to the script to create the tables if it doesn't exist. Used instructions here https://learn.microsoft.com/en-us/azure/data-factory/tutorial-bulk-copy-portal substituting the dw part with sql server/MI and no storage account. Another problem is constraints and large tables. It was at almost 8 hour runtime when I cancelled it. Also needs a work around, staging or something. – Amommy Nov 04 '21 at 14:31
  • Should not be this difficult. Sql Server has a sync feature but not Mang Inst. hopefully this new feature will help and be available to all soon. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/link-feature – Amommy Nov 04 '21 at 14:32