Skip to end of metadata
Go to start of metadata

You are viewing an old version of this content. View the current version.

Compare with Current View Version History

« Previous Version 10 Next »

In all replications, the source database must be an Oracle database. Therefore, two-way replication with MS SQL Server is not supported. ODBC connection is used to connect to MS SQL, thus a DSN (Data Source Name) must be set up 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 SQL.

If the source database is 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.

 

[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" 

 

[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.

 

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:

 

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.

 

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.


  • No labels