Accessing SQL Server Data from Oracle on Linux. for FREE!

Accessing

SQL Server Data from Oracle on Linux

for FREE!

COUG 15-NOV-2012 Wayne Linton

Wayne Linton

● Over 40 years in IT

● More than half that as an Oracle DBA

● 35 years at Shell Canada Limited

● SAIT Instructor

● Past-President COUG

● Presenter at OOW, Oracle Days, COUG

● Established 1999

● Remote and on-site DBA support

● Application Express Design/Development

● Training

Why do this?

● Companies may have a number of databases

on different platforms and DBMS offerings

● Vendor software might only run on MSSQL

● Want a central 'truth' of data to pull from

● Want a central warehouse to represent all data

Open Database Connectivity

● OS independent, vendor neutral API

● Mainly used to access Databases via SQL

● Built in early '90s by Microsoft, in concert with

Oracle, Apple, Sun, DEC, Sybase, Tandem, Lotus and others – ISO Standard

● Unix community picked up ODBC when MS

moved its focus to OLE-DB (JDBC is java equivalent )

ODBC

● Commonly used in Windows with Excel and

Access database

● OS provides driver manager (unixODBC)

● DBMS driver typically supplied by vendor

● Vendor drivers available at cost such as:

● Oracle gateway products (dg4msql) ● easysoft

ODBC Driver

● Translation layer between SQL and DBMS

● Large subset of standard SQL functionality

● 3rd Party vendors sell “better” drivers

● Available for Oracle, MySQL, MSSQL, DB2,

Progress, Sybase, etc.

ODBC Driver Manager

● Delivered with OS

● Loads ODBC Driver appropriate to DBMS data

source

● Free Open Source implementation on 'nix

SQL Server Driver

● Released in the spring of 2012

● Available on 64-bit Linux v5 or v6 only

● Free to use

● SQL Server 2008 R2, and SQL Server 2012

● Download at:

Demo Environment

● Laptop, 8gb, 64-bit, Windows 7

● VirtualBox, Win7, SQL Server 2012 (PC7C)

● VirtualBox, CentOS 5.8, 64-bit, Oracle

SQL Server (PC7C) ODBC Driver /opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0 /etc/odbcinst.ini ODBC Manager unixODBC v2.3.0 /etc/odbc.ini TCP/IP sqlcmd Oracle Oracle Net HS dg4odbc SQL*Plus

select * from <tab>@<alias> isql

● Database Gateway For ODBC

Oracle® Database Gateway for ODBC User's Guide 11g Release 2 (11.2)

● Comes installed with database

$ORACLE_HOME/hs/lib/dg4odbc.o

● Connects Oracle to ODBC via Heterogeneous

Services (HS)

Oracle® Database Heterogeneous Connectivity User’s Guide11g Release 2 (11.2) Oracle® Database Gateway Installation and Configuration Guide

● Other gateways available for cost (dg4mssql)

Oracle

Oracle Net

HS dg4odbc

SQL*Plus

Installation – Driver Manager

● Download the MS SQL Server ODBC file

(detailed instructions on the download site)

– Login as root

– tar xvf sqlncli-11.0.1790.0.tar.gz – yum remove unixODBC

– cd ./sqlncli-11.0.1790.0

Installation – ODBC Driver

● Still logged in as root ....

– cd ./sqlncli-11.0.1790.0 – ./install.sh verify

– ./install.sh install

– odbcinst -q -d -n "SQL Server Native Client 11.0"

● Test the driver against the SQL Server database:

SQL Server (PC7C) ODBC Driver /opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0 /etc/odbcinst.ini ODBC Manager unixODBC v2.3.0 /etc/odbc.ini TCP/IP sqlcmd isql (PC6VB2)

Configure odbcinst.ini

● Defines the ODBC Driver settings

● Should be created by the install process

● /etc/odbcinst.ini

[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

Configure odbc.ini

● Defines Data Source Name connection

● /etc/odbc.ini (local ~/.odbc.ini)

[<dsn>]

Driver = SQL Server Native Client 11.0

Server = tcp:<mssql hostname or IP address>,1433

● Test the odbc settings:

isql <dsn> <user> <passwd>

select * from information_schema.schemata; quit;

SQL Server (PC7C) ODBC Driver /opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0 /etc/odbcinst.ini ODBC Manager unixODBC v2.3.0 /etc/odbc.ini TCP/IP sqlcmd Oracle Oracle Net HS dg4odbc SQL*Plus

select * from <tab>@<alias> isql

● Configure HS in $ORACLE_HOME/hs/admin ● Filename init<dsn>.ora (case matters!)

# Database Gateway for ODBC # # HS init parameters # HS_FDS_CONNECT_INFO = <dsn> HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15 HS_FDS_PROC_IS_FUNC = TRUE HS_FDS_RESULTSET_SUPPORT = TRUE

# Environment variables required for the non-Oracle system # set LD_LIBRARY_PATH=/opt/microsoft/sqlncli/lib64:/usr/lib64: Oracle Oracle Net HS dg4odbc SQL*Plus

● Connection to database HS is done via Oracle

Net

● Oracle Listener configured like:

(SID_DESC = (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = <dsn>) (PROGRAM = dg4odbc) (ENVS = "LD_LIBRARY_PATH=/opt/microsoft/sqlncli/lib64:/usr/lib64: /u01/app/oracle/product/11.2.0/dbhome_1/lib") ) Oracle Oracle Net HS dg4odbc SQL*Plus

● Tnsnames entry such as:

<alias> =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)

(HOST = <oracle db host>) (PORT = 1521)) (CONNECT_DATA = (SID = <dsn>)) (HS = OK) Oracle Oracle Net HS dg4odbc SQL*Plus

select * from <tab>@<alias>

Database Link

create public database link <dblink>

connect to <user> identified by <pswd> using '<alias>';

● You can not connect directly to SQL Server via

Oracle Net

Demos ...

● isql, sqlcmd

● DML and some functions

● DDL handling

● HS views

Dynamic Performance Views

● V$HS_PARAMETER

● V$HS_SESSION

Some Gotchas

● DDL

● Datatype conversion issues

● Special variables like SYSDATE

● Date arithmetic, different functions

● Optimizer statistics, execution plans

Summary

● ODBC-compliant databases can exchange data

● Free drivers available

● Doesn't do everything, but probably 'good

enough'!

● Viable solution for bringing in data from other

sources into one central database/warehouse (oh, and yes it works with 11gXE)

Questions?

Accessing

SQL Server Data from Oracle on Linux

for FREE!

COUG 15-NOV-2012 Wayne Linton