-1

I am trying to replicate the source database (Oracle 11g and MS SQL Server 201x) to Azure SQL Database. Initially I thought of using Azure DMS(Database Migration Service) to do a full load + Change Data Capture for changes. After started to work on this, I learnt that the DMS requires Control Server privileges on the SQL Server Database and DBA Role on Oracle. We might not get these permissions on the source databases as they are managed by third party company (SaaS model). However, we have read permissions on the source database/tables.

We have to achieve the following :

  • Replicate data from Oracle Source System to Azure SQL Database and
  • Replicate data from SQL Server 201x to Azure SQL Database.

What should be the approach?

  • Azure SQL Data Sync? I read on the forum that it actually writes/creates tables in the source database. This won't be acceptable.
  • Transactional replication for SQL Server? - Has any one tried? Can you please share your experience?
  • Third option I thought was to use Attunity Replicate (however, not sure about the licensing costs)

Can someone please help with approaching this business problem?

Regards, JE

java_enthu
  • 2,279
  • 7
  • 44
  • 74
  • So, what have you tried? That's really not a good question, we don't how to answer you, for oracle to Azure SQL Server or SQL server to SQL Azure? They are different. – Leon Yue Feb 20 '20 at 03:36
  • @LeonYue : thanks for pointing out, Its for replicating/mirroring from two different source databases one Oracle and one SQL Server and the target is Azure SQL DB. – java_enthu Feb 20 '20 at 08:42
  • Hi @jave_enthu, you're welcome. I tried my best to answer you. If my answer is helpful for you, hope you can mark(and vote) it as answer. This can be beneficial to other community members. Thank you. – Leon Yue Feb 21 '20 at 03:18

1 Answers1

0

You said, you have read permissions on the source database/tables(Oracle and SQL Server). I suppose that you just have the read-only permission.

Then the answer is no, we can not copy the data or migrate the database(Oracle and SQL Server) to Azure SQL database without the CONTROL SERVER permission.

Data sync can help you sync the data from SQL server to Azure SQL database, but you need have permission to Azure SQL too. Create the table with same schema and so.

If you don't the permission on SQL server, the Transactional replication also doesn't work.

For Attunity Replicate, I don't know much it, sorry that I can't answer you.

For your questions:

1.Replicate data from Oracle Source System to Azure SQL Database

Please reference this Azure Database Migration Guide: Migrate Oracle to Azure SQL Database.

This scenario describes how to migrate an Oracle instance to Azure SQL Database.

enter image description here

2.Replicate data from SQL Server 201x to Azure SQL Database.

Reference: Azure Database Migration Guide:Migrate SQL Server to Azure SQL Database.

This scenario describes how to migrate a SQL Server instance to Azure SQL Database.

enter image description here

We also could use bellow way to migrate the database with SSMS:

  1. Create a dacpac file and import to Azure SQL.
  2. SSMS Deploy database to Microsof Azure SQL database.

For details, please see: Learn how to Migrate a SQL Server database to SQL Azure server.

Hope this helps.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23