if I have SQL server replicate to Oracle, is it true SQL backup must be native, non-encrypted and non compressed? if so, are the restrictions only APPLIED on the initial load or ongoing? basically if golden gate capture the data via transaction logs so why it care about the backup as an ongoing process, it doesn't click in my mind.
To be specific, OGG for SQL Server requires that the transaction log backups be native, unencrypted, and uncompressed. The full and differentialdatabase backups can be in whatever format you desire. However, for continuous data replication for SQL Server, OGG will from time to time need to read data out of the transaction log backups, and you can't control when that time will be.
Here's an example scenario:
Let's say that the Extract has only 1 committed transaction in the transaction log left to read, but before it can get to it, a log backup occurs and SQL Server decides that it needs to free some space in the transaction log. So immediately after that log backup, it flushes all committed transactions that were just written to the log backup, however, OGG Extract still needed that 1 remaining transaction in the log to read. Since that transaction is no longer in the transaction log but is in the log backup, the Extract will switch over to the log backup and read the transaction from there, then switch back into the online transaction log and continue processing.
This is just one example of when an Extract needs to read from the log backup, and there may be other reasons as well.
Thanks so much for the reply.....
1. non-encrypted and non-compressed, this will raise red flag for lots of company who are tight on compliance.
2. different topic but still on SQL, DDL support for Oracle to Oracle, will it support for SQL Server to Oracle? if no, work-around?
OGG replication of DDL is not supported for SQL Server, but checkout MOS article 1326758.1 for some ideas on implementing a database trigger in SQL Server that fires and writes a record into an Event table, which OGG can be set to Capture and respond with a certain condition, like stop the Extract, run an OS command, etc. You can use that doc as a reference and expand on it to suit your needs.
could you please also point me to the white paper or documentation the minimum privileges for extract and replicat user, should the user resided on the different schema than the application schema?
The latest OGG build documentation is here, and each build for the different DBMS that is supported has it's own installation guide, which lists the security requirements. An Extract's security needs are different from a Replicat's, and they are also different per DBMS.
In summary, the extract.exe for SQL Server runs at the OS level with the Windows account that is running the Manager service (this is Local System account by default, but can be changed to a specific user) and this is the OS account that reads the physical transaction log and log backups, and that account must be a member of the Windows server Administrator's group.
You also have a System ODBC DSN to setup for Extract to pull meta-data, run some commands, and other info from the SQL Server database, and if you use Windows authentication in that DSN, then it will use the Windows account running the Manager to log into SQL Server, or if you use SQL Server authentication in the DSN, then you would create a SQL Server login. Either way, the Windows account or a SQL Server account needs to be a member of the SQL Server instance's syadmin role. This is for the Extract, no exceptions.
Oracle database permissions are different and listed as well in the OGG for Oracle install guide.