My article Replicating Transactions Between Microsoft SQL Server and Oracle Database Using Oracle GoldenGate is now available at OTN. It is a step-by-step tutorial on how to build a simple on-line replication between SQL Server 2008 and Oracle Database 11gR2.
Update from September 16, 2001: Kim Prisk sent me an e-mail pointing out that change data capture will not work with SQL Server 2008 Standard Edition. This is something that I missed to mention in the article. Here is the error message hit by Kim:
ODBC error: SQLSTATE 37000 native database error 22988. [Microsoft][ODBC SQL Server Driver][SQL Server]This instance of SQL Server is the Standard Edition. Change data capture is only available in the Enterprise, Developer, and Enterprise Evaluation editions.
All that are interested should keep in mind that GG 11gR1 is certified for the following SQL Server editions:
- SQL Server 2000/2005: Enterprise and Standard Edition
- SQL Server 2008: Enterprise (capture or delivery) or Standard Edition (delivery only)
- SQL Server 2008 R2: Enterprise (capture or delivery) or Standard Edition (delivery only)
Update from September 22, 2001: More findings from Kim:
A couple of other issues that I have found that you or others might find helpful:
1. If you use the 'sql server' driver in the ODBC connection, the tcp/ip protocol under the 'SQL Native Client 10.0 Configuration' must be enabled. It was not enabled by default during the sql server installation (sql server developer 2008).
2. When I executed the ‘ADD TRANDATA HRSCHEMA.EMP’ command, I got an error:
ODBC error: SQLSTATE 37000 native database error 22830. [Microsoft][ODBC SQL Server Driver][SQL Server]Could not update the metadata that indicates database EMP is enabled for Change Data Capture. The failure occurred when executing the command 'SetCDCTracked (Value = 1)'. The error returned was 15404: 'Could not obtain information about Windows NT group/user 'my domain\my userid', error code 0x6e.'. Use the action and error to determine the cause of the failure and resubmit the requst.
I was able to fix this by running the following script in a sql window opened against emp:
use EMP go exec sp_changedbowner 'sa'
I now have succeeded, and see the message “supplemental log data is enabled for table hrschema.emp” when I run the ‘ADD TRANDATA HRSCHEMA.EMP’statement.