In all replications, the source database must be an Oracle database. Therefore, two-way replication with MS SQL with SQL Server is not supported. ODBC connection is used to connect to MS SQL, thus a to SQL Server, therefore System DSN (Data Source Name) with the same name must be set up created by the system administrator first (e.g. by the ODBC Data Source Administrator tool included in MS Windows). Currently, no DDL replication is supported for MS SQLon both source and target systems.
If the source database is both source and target databases are running on Linux machine then ODBC drivers for Linux is required to set up DSN. To setup DSN on Linux machine, following steps needs to be performed.
1. First of all Install ODBC driver on Linux. Driver can be downloaded from this link : http://www.microsoft.com/en-us/download/details.aspx?id=28160
Installation instructions are available on same page. If required packages are installed then It will also work on different flavor of Linux and not just on 64-bit Red Hat Enterprise Linux 5 or 64-bit Red Hat Enterprise Linux 6.
2. Installtion of ODBC driver will insert following record in /etc/odbcinst.ini file.
| Section |
|---|
| Column |
|---|
| | Panel |
|---|
| [SQL Server Native Client 11.0] Description=Microsoft SQL Server ODBC Driver V1.0 for Linux Driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0 Threading=1 UsageCount=1 |
|
|
Crosscheck whether this specification are inserted or not.
3. Once the installation is done, DSN can be created. To create DSN, login as root and create a DSN entry in /etc/odbc.ini file.Following is an example of DSN named "dbtest1"
| Section |
|---|
| Column |
|---|
| | Panel |
|---|
| [dbtest] Driver=SQL Server Native Client 11.0 Description=My Sample ODBC Database Connection Trace=Yes Server=<IP Address of Target Windows Machine> |
|
|
4. DSN is created. "isql" command can be used to test DSN. If DSN is working properly then isql command will return SQL prompt from where you can execute SQL statements on target database running on Windows machine. Following is the example of testing DSN testdb1.
| Section |
|---|
| Column |
|---|
| | Panel |
|---|
| oracle@dbvldemo101[/home/oracle]: isql -v dbtest sa ******* +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select * from avi.sample |
|
|
Using the correct Snapshot Isolation
Snapshot isolation must be enabled by setting the ALLOW_SNAPSHOT_ISOLATION ON database option before it is used in transactions. You must enable snapshot isolation in each database that uses it with the Transact-SQL ALTER DATABASE statement. In this respect, snapshot isolation differs from the traditional isolation levels of READ COMMITTED, REPEATABLE READ, SERIALIZABLE, and READ UNCOMMITTED, which require no configuration. The following statements activate snapshot isolation and replace the default READ COMMITTED behavior with SNAPSHOT:
| Section |
|---|
| Column |
|---|
| | Panel |
|---|
| ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON |
|
|
To check what the current snapshot isolation level, run the following SQL:
If the query returns 0 then it is turned off.
| Section |
|---|
| Column |
|---|
| | Panel |
|---|
| select name, snapshot_isolation_state_desc, is_read_committed_snapshot_on from sys.databases |
|
|
See the child page Setup Wizard below for details of the configuration steps.Windows platform then System DSN (Data Source Name) with the same name are required on source and target machine. But if source is Linux then System DSN is only required on Windows target machine.
| Note |
|---|
- Navicat Premium can be used to load data from oracle to SQL Server in order to sync source and target environment before initiating replication.
- Currently, no DDL replication is supported for Oracle to SQL Server Replication.
- Timestamp datatype of Oracle is not supported for Oracle to SQL Server Replication.
|
| Note |
|---|
| title | MS SQL Server specific |
|---|
|
Name of the target tables has to contain three parts: DATABASE, SCHEMA, TABLENAME Questions in the wizard, step 1, database type SQL Server: Enter the schema the table are created by the user specified: [SCHEMA] Enter the database with the user data: [DATABASE] |