System Data Source Name (DSN)

Dbvisit Replicate communicates with both source and target database to replicate data. This communication includes reading and writing data through the system Data Source Name (DSN) configured using ODBC data source and drivers.  A system DSN for SQL server on source system is required by Dbvisit Replicate to successfully execute configurational scripts that prepares the target SQL Server environment for replication.  On target system, a system DSN with same name as source system is required by the APPLY service which interacts with target database to replicate changes of source database.

When source Oracle database is on Linux environment and target SQL Server database is on Windows environment, system DSN is required only on target Windows machine because the initial setup wizard is executed on target Windows machine unlike the other scenario where setup wizard is executed on source machine when both source and target databases are running on Windows OS.

System DSN on Windows

When source Oracle database and target SQL Server database are both running on Windows machine, Dbvisit Replicate required identical system DSN on both system. A system DSN is required on source machine for initial configuration step that interacts with target SQL Server database. On target system a system DSN with the same name as source machine is required to replicate changes of source database.

On Windows, a system DSN for SQL server can be created by an user with administrative rights from either administrative tools or control panel. 

Following is an example of creation of system DSN named "mstest" on WIndows 2008 for SQL server running on Windows machine "dbvisit420". Same steps needs to be performed on both source and target machine if replication is from Windows to Windows. In Linux to Windows replication, following steps are performed only on target Windows system.

1. Open ODBC Data Source Administration window from either administrative tools or control panel and select System DSN tab.

2. Click on Add and select SQL Server Native Client.

3. Enter name, description and target server name.

4. Select SQL server authentication and enter credentials of SQL server's user with admin rights.

5. Keep the default values and click on Next for two steps and then Test the connection. 

6. If test is successful then go to target server and repeat same steps if replication is from Windows to Windows and once these two system DSNs are created, you can start configuring Dbvisit Replicate. Whereas for Linux to Windows replication, Once you create system DSN on target Windows system, you can then start configuring Dbvisit Replicate.

DSN on Linux

If the source oracle database is running on Linux machine then ODBC drivers for Linux is required to set up DSN. ODBC driver manager is required in order to install ODBC driver for Linux. Microsoft provides ODBC driver for Linux that provides native connectivity from Linux to Microsoft SQL Server. You can download ODBC driver for Linux from following page.

http://www.microsoft.com/en-us/download/details.aspx?id=28160

Installation 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

[mstest]
Driver=SQL Server Native Client 11.0
Description=My Sample ODBC Database Connection
Trace=Yes
Server=<IP Address of Target Windows Machine>

oracle@dbvldemo101[/home/oracle]: isql -v dbtest sa *******
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from avi.sample