Tuesday, December 4, 2007
Replication Configuration Between MSSQL 2005 and Oracle
1. Oracle as publisher
1) Configuring an Oracle Publisher
Replication modes are: snapshot and transactional replication.
Preparation steps before creating a publication from an Oracle:
1.1 Create a replication administrative user within the Oracle database using the supplied script.
Connect to the Oracle database using an account with DBA privileges and execute the script. This script prompts for the user and password for the replication administrative user schema as well as the default tablespace in which to create the objects (the tablespace must already exist in the Oracle database).
It is recommended that the schema be used only for objects required by replication; do not create tables to be published in this schema.
If you create user schema manually, you need grant schema the following permissions to user or via role:
CREATE PUBLIC SYNONYM and DROP PUBLIC SYNONYM
and you must also grant the following permissions to the user directly (not through a role):
CREATE ANY TRIGGER. This is required only for transactional replication; snapshot replication does not use triggers.
1.2 For the tables that you will publish, grant SELECT permission directly on each of them (not through a role) to the Oracle administrative user you created.
For example, login to Oracle as user "SCOTT":
SQL> grant select on DEPT to repluser;
SQL> grant select on EMP to repluser;
1.3 Install the Oracle client software and OLE DB provider on the Microsoft SQL Server Distributor, and then restart the server.
You can use Oracle Universal Installer and Network Configuration Assistant to configure Oracle database network connectivity.
The account under which the SQL Server service on the Distributor runs must be granted read and execute permissions for the directory (and all subdirectories) in which the Oracle client networking software is installed.
To test the connectivity, run "sqlplus /@" in command line, and you will see a SQL prompt.
1.4 Configure the Oracle database as a Publisher at the SQL Server Distributor.
Oracle Publishers always use a remote Distributor; you must configure an instance of SQL Server to act as a Distributor for your Oracle Publisher (an Oracle Publisher can only use one Distributor, but a single Distributor can service more than one Oracle Publisher). After a Distributor is configured, identify the Oracle database instance as a Publisher at the SQL Server Distributor through SQL Server Management Studio, Transact-SQL, or Replication Management Objects (RMO).
When you identify the Oracle database as a Publisher, you must choose an Oracle publishing option: Complete or Oracle Gateway. After a Publisher is identified, this option cannot be changed without dropping and reconfiguring the Publisher. The Complete option is designed to provide snapshot and transactional publications with the complete set of supported features for Oracle publishing. The Oracle Gateway option provides specific design optimizations to improve performance for cases where replication serves as a gateway between systems.
After the Oracle Publisher is identified at the SQL Server Distributor, replication creates a linked server with the same name as the TNS service name of the Oracle database. This linked server can be used only by replication. If you need to connect to the Oracle Publisher over a linked server connection, create another TNS service name, and then use this name when calling sp_addlinkedserver (Transact-SQL).
a. Script to grant Oracle permissions
This script is also available in the following directory after installation: :\\Program Files\Microsoft SQL Server\\MSSQL\Install\oracleadmin.sql.
-- PL/SQL script to create a database user with the required
-- permissions to administer SQL Server publishing for an Oracle
-- &&ReplLogin == Replication user login
-- &&ReplPassword == Replication user password
-- &&DefaultTablespace == Tablespace that will serve as the default
-- tablespace for the replication user.
b. Managing Oracle tablespace
To specify a tablespace for an article logging table, you can specify a tablespace in the Article Properties dialog box or by using sp_changearticle (Transact-SQL).
2) Design considerations and limitations for Oracle publisher
2.1 The Oracle Gateway option provides improved performance over the Oracle Complete option; however, this option cannot be used to publish the same table in multiple transactional publications. A table can appear in at most one transactional publication and any number of snapshot publications. If you need to publish the same table in multiple transactional publications, choose the Oracle Complete option.
2.2 Replication supports publishing tables, indexes, and materialized views. Other objects are not replicated.
2.3 There are some small differences between the storage and processing of data in Oracle and SQL Server databases that affect replication.
a. Oracle has different maximum size limits for some objects. Any objects created in the Oracle publication database should adhere to the maximum size limits for the corresponding objects in SQL Server.
b. By default Oracle object names are created in upper case. Ensure that you supply the names of Oracle objects in upper case when publishing them through a SQL Server Distributor if they are upper case on the Oracle database.
c. Oracle has a slightly different SQL dialect from SQL Server; row filters should be written in Oracle-compliant syntax.
d. Oracle triggers fire when rows containing LOBs are inserted or deleted; however updates to LOB columns do not fire triggers. An update to a LOB column will be replicated immediately only if a non-LOB column of the same row is also updated in the same Oracle transaction.
e. For both snapshot and transactional replication, columns contained in unique indexes and constraints (including primary key constraints) must adhere to certain restrictions:
I. The maximum number of columns allowed in an index on SQL Server is 16.
II. All columns included in unique constraints must have supported data types.
III. All columns included in unique constraints must be published (they cannot be filtered).
IV. Columns involved in unique constraints or indexes should not be null.
f. Primary key to foreign key relationships in the Oracle database are not replicated to Subscribers.
2.4 There are a number of differences in how transactional replication features are supported when using an Oracle Publisher.
a. Subscribers to Oracle publications cannot use immediate updating or queued updating subscriptions, or be nodes in a peer-to-peer or bidirectional topology.
b. Subscribers to Oracle publications cannot be automatically initialized from a backup.
c. SQL Server supports two types of validation: binary and rowcount. Oracle Publishers support rowcount validation.
d. SQL Server offers two snapshot formats: native bcp-mode and character-mode. Oracle Publishers support character mode snapshots.
e. Schema changes to published Oracle tables are not supported. To make schema changes, first drop the publication, make the changes, and then re-create the publication and any subscriptions.
3) Administrative considerations for Oracle publisher
3.1 Importing and loading data
Triggers are used in change tracking for transactional publications on Oracle. Changes to published tables can be replicated to Subscribers only if the replication triggers fire when an update, insert, or delete occurs. The Oracle utilities Oracle Import and SQL*Loader both have options that affect whether triggers will fire when rows are inserted into replicated tables with these utilities.
While using Oracle Import, if option "ignore" is set to 'n', the table is dropped and re-created during import. This removes replication triggers and disables replication.
With SQL*Loader, if option "direct" is set to 'false', rows are inserted using conventional INSERT statements, which fire replication triggers. If direct is set to 'true', the load is optimized, and triggers are not fired.
3.2 Making changes to published objects
The following action requires you to stop all activity on the published tables: Moving a published table.
The following actions require you to drop the publication, perform the operation, and then recreate the publication:
a. Truncating a published table.
b. Renaming a published table.
c. Adding a column to a published table.
d. Dropping or modifying a column that is published for replication.
e. Performing non-logged operations.
3.3 You must drop and reconfigure the Publisher if you drop or modify any Publisher level tracking tables, triggers, sequences, or stored procedures.
4) Performance tuning for Oracle publisher
4.1 The Oracle Gateway option provides improved performance over the Oracle Complete option; however, this option cannot be used to publish the same table in multiple transactional publications. A table can appear in at most one transactional publication and any number of snapshot publications. If you need to publish the same table in multiple transactional publications, choose the Oracle Complete option.
4.2 Changes to published Oracle tables are processed in groups called transaction sets. To ensure transactional consistency, each transaction set is committed as a single transaction at the distribution database. If the transaction set becomes too large, it cannot be processed efficiently as a single transaction. By default, transaction sets are created only by the Log Reader Agent. Transaction sets can be created with the Xactset job (an Oracle database job installed by replication), which uses the same mechanism that the Log Reader Agent does to create sets. To prevent the transaction set from becoming too large, ensure that transaction sets are created at regular intervals, even if the Log Reader Agent does not run or cannot connect to the Oracle Publisher.
5) Data type mapping for Oracle publisher
6) Backup and Restore for Oracle publisher
6.1 Ensure the Log Reader Agent does not run and that other database activity on the published tables does not occur while the Publisher is being backed up.
6.2 Backup up the Publisher and Distributor at the same time.
6.3 If the Publisher or Distributor must be restored, reinitialize all subscriptions.
6.4 To restore a Subscriber from a backup (without having to reinitialize subscriptions), the transactions delivered to the distribution database after the last subscription database backup was completed must still be available.
6.5 If the Publisher or Distributor becomes out of sync as the result of a database restore, the replication agents log error messages. At this point, you must drop and recreate all relevant publications and subscriptions.
6.6 If the Publisher must be dropped and reconfigured, drop the MSSQLSERVERDISTRIBUTOR public synonym and the configured Oracle replication user with the CASCADE option to remove all replication objects from the Oracle Publisher.
7) Objects created on Oracle publisher
2. Oracle as subscriber
1) General Considerations for Non-SQL Server Subscribers
1.1 Replication supports publishing tables and indexed views as tables to non-SQL Server Subscribers.
1.2 If a publication will have SQL Server Subscribers and non-SQL Server Subscribers, the publication must be enabled for non-SQL Server Subscribers before any subscriptions to SQL Server Subscribers are created.
1.3 The account under which the Distribution Agent runs must have read access to the install directory of the OLE DB provider.
1.4 By default, scripts generated by the Snapshot Agent for non-SQL Server Subscribers use non-quoted identifiers in the CREATE TABLE syntax.
1.5 If the SQL Server Distributor is running on a 64 bit platform, you must use the 64 bit version of the appropriate OLE DB provider.
1.6 Replication moves data in Unicode format regardless of the collation/code pages used on the Publisher and Subscriber. It is recommended that you choose a compatible collation/code page when replicating between Publishers and Subscribers.
1.7 If an article is added to or deleted from a publication, subscriptions to non-SQL Server Subscribers must be reinitialized.
1.8 The only constraints supported for all non-SQL Server Subscribers are: NULL, and NOT NULL. Primary key constraints are replicated as unique indexes.
1.9 Published schema and data must conform to the requirements of the database at the Subscriber.
1.10 Tables replicated to non-SQL Server Subscribers will adopt the table naming conventions of the database at the Subscriber.
1.11 SQL Server offers two types of subscriptions: push and pull. Non-SQL Server Subscribers must use push subscriptions, in which the Distribution Agent runs at the SQL Server Distributor.
1.12 SQL Server offers two snapshot formats: native bcp-mode and character-mode. Non-SQL Server Subscribers require character mode snapshots.
1.13 Non-SQL Server Subscribers cannot use immediate updating or queued updating subscriptions, or be nodes in a peer-to-peer topology.
1.14 Non-SQL Server Subscribers cannot be automatically initialized from a backup.
2) Configuring an Oracle Subscriber
2.1 Install and configure Oracle client networking software and the Oracle OLE DB provider on the SQL Server Distributor.
2.2 Create a TNS name for the Subscriber.
2.3 Create a snapshot or transactional publication, enable it for non-SQL Server Subscribers, and then create a push subscription for the Subscriber.
2.4 The account under which the SQL Server service on the Distributor runs must be granted read and execute permissions for the directory (and all subdirectories) where the Oracle client networking software is installed.
3) Considerations for Oracle Subscribers
3.1 Oracle treats both empty strings and NULL values as NULL. This is important if you define a SQL Server column as NOT NULL, and are replicating the column to an Oracle Subscriber. To avoid failures when applying changes to the Oracle Subscriber, you must do one of the following:
a. Ensure that empty strings are not inserted into the published table as column values.
b. Use the –SkipErrors parameter for the Distribution Agent if it is acceptable to be notified of failures in the Distribution Agent history log and to continue processing.
c. Modify the generated create table script, removing the NOT NULL attribute from any character columns that may have associated empty strings, and supply the modified script as a custom create script for the article using the @creation_script parameter of sp_addarticle (Transact-SQL).
Posted by Money Sense at 7:04 PM