Home Microsoft SQL Server DigiMailing Contact
    Keyword



Oracle to SQL Server replication

 

SQL Server had a great number of possibilities for replications, each solution has its own advantages and disadvantages. This article describes the replication between an Oracle 10 and SQL Server 2005 instance. With this technique you can have your, near real-time, data in SQL Server and using all the analysis and reporting services or to make your own data warehouse.
We have chosen for Microsoft SQL Server Replication, Oracle Replication is also possible but is a whole different approach.

Things to pay attention to; partitioning and data types
One of the most difficult things for Microsoft SQL Server is the data type mapping of Oracle type's to SQL Server type's. In our project we had some troubles with the timestamp Oracle type. While we where setting up replication the timestamp columns where not visible in the QUI. We had a work around for this, selecting the whole table as a new article and then deselected the columns that we didn't need.
Also it is not possible (by default) to replicate table's that are partitioned on Oracle. This is because Microsoft SQL Server requests the Oracle system view ALL_INDEXES for an overview from al table's with a defined primary key. Partitioned Oracle table's who has a primary key are visible where but has a status 'N/A' which stands for Not Applicable. You can resolve this matter by making a view with the user you are stetting up replication with and say the all indexes are valid. In this case Oracle works the same as SQL Server does. First query the tables and views in your own schema and then query the tables and views in the dbo schema. You must understand that you must check the uniqueness of each table your self but then you don't have any difficulties with Oracle partitioning. Here is the syntax of the view:

CREATE OR REPLACE VIEW SQL_REPLICATION_USER.ALL_INDEXES
(
OWNER,INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,UNIQUENESS,COMPRESSION,
PREFIX_LENGTH,TABLESPACE_NAME,INI_TRANS,MAX_TRANS,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,
MAX_EXTENTS,PCT_INCREASE,PCT_THRESHOLD,INCLUDE_COLUMN,FREELISTS,FREELIST_GROUPS,PCT_FREE,
LOGGING,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,
CLUSTERING_FACTOR,STATUS,NUM_ROWS,SAMPLE_SIZE,LAST_ANALYZED,DEGREE,INSTANCES,PARTITIONED,
TEMPORARY,GENERATED,SECONDARY,BUFFER_POOL,USER_STATS,DURATION,PCT_DIRECT_ACCESS,ITYP_OWNER,
ITYP_NAME,PARAMETERS,GLOBAL_STATS,DOMIDX_STATUS,DOMIDX_OPSTATUS,FUNCIDX_STATUS,JOIN_INDEX,
IOT_REDUNDANT_PKEY_ELIM,DROPPED
)
AS
select OWNER, INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME,
TABLE_TYPE, UNIQUENESS, COMPRESSION, PREFIX_LENGTH, TABLESPACE_NAME,
INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS,
MAX_EXTENTS, PCT_INCREASE, PCT_THRESHOLD, INCLUDE_COLUMN, FREELISTS,
FREELIST_GROUPS, PCT_FREE, LOGGING, BLEVEL, LEAF_BLOCKS,
DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, CLUSTERING_FACTOR, 'VALID',
NUM_ROWS, SAMPLE_SIZE, LAST_ANALYZED, DEGREE, INSTANCES,
PARTITIONED, TEMPORARY, GENERATED, SECONDARY, BUFFER_POOL,
USER_STATS, DURATION, PCT_DIRECT_ACCESS, ITYP_OWNER, ITYP_NAME,
PARAMETERS, GLOBAL_STATS, DOMIDX_STATUS, DOMIDX_OPSTATUS, FUNCIDX_STATUS,
JOIN_INDEX, IOT_REDUNDANT_PKEY_ELIM, DROPPED
from sys.all_indexes
/

 




Syntax
Last revision
News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash