Best option to replicate sql server to oci

Question

Hi!
Thank you beforehand for your help

We want to replicate and consolidate some sqlserver schemas to oracle cloud infrastructure with sqlserver too.

We wonder if we can use golden gate (and if it replicates ddls and what restrictions we will face) or using always on.

Is there any other option? What option is easier to implement?

Answer 1

Hi @Antonio A ,

So it is data replication from SQL server to SQL server. If so please do not use Heterogeneous Database Replication that I mentioned above. Using transaction replication.

> Will we have any kind of restriction in ddl replication or changes in sql server in source to sql server in destination?

Quote from MS document Considerations for Schema Changes;

• ALTER TRIGGER can be used only for data manipulation language [DML] triggers because data definition language [DDL] triggers cannot be replicated.
• Schema changes are subject to any restrictions imposed by Transact-SQL. For example, ALTER TABLE does not allow you to ALTER primary key columns.
• Schema changes are propagated to Subscribers running previous versions of SQL Server, but the DDL statement should only include syntax supported by the version at the Subscriber.

Please read the MS document to get more about this.

For Oracle GoldenGate, please refer to the blog Limitations of Oracle GoldenGate DDL Support .

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking

Answer 2

Hi @Antonio A ,

You can use Oracle GoldenGate. Oracle GoldenGate is a very powerful replication solution. You can also use Heterogeneous Database Replication or Transactional Replication.

Refer to this old thread and the blog Replicating Transactions Between Microsoft SQL Server and Oracle Database Using Oracle GoldenGate..

SQL server always on is not supported for this situation.

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.

Answer 3

Hello

Thank you for your response

Will we have any kind of restriction in ddl replication or changes in sql server in source to sql server in destination?

Answer 4

Hi @Antonio A ,

>Will we have any kind of restriction in ddl replication or changes in sql server in source to sql server in destination?

DDL replication from SQL server to Oracle?

Quote from MS document Considerations for Non-SQL Server Subscribers;

DDL is not supported for non-SQL Server Subscribers.

For Oracle GoldenGate, please refer to the blog Limitations of Oracle GoldenGate DDL Support .

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.

Answer 5

Source: sqlserver on prem
Destination: sqlserver on oci