Heterogeneous Database Replication

1
Heterogeneous Database Replication

• Randy Dyess
• Mentor
• rdyess_at_solidqualitylearning.com

2
About Me

• Solid Quality Learning Mentor
• Transact-SQL Performance Tuning Mentoring and
  Training
• SQL Server Performance Tuning Mentoring and
  Training
• Author of articles for SQLServerCentral.com,
  SSWUG.Org, SQL Server Standard magazine, and
  others
• Director of Membership for PASS
• www.SolidQualityLearning.com
• rdyess_at_solidqualitylearning.com

3
An association of SQL Server experts from around
the world

• Solid Quality Learning is the trusted global
  provider of advanced education and solutions for
  the entire Microsoft database platform.

• Helping you get the best out of your SQL Server
• Training
• Consulting
• Mentoring

www.solidqualitylearning.com
4
Snapshot Replication
5
Snapshot

• Full data refresh
• Bulk copy of schema and data
• Locking implications
• No incremental transaction flow
• No structural impact
• First step of transactional and merge
• Not exactly
• Different paths depending upon method

6
Snapshot
Clients
Distributor
Transactions
Distribution
Repl_commands
Publisher
Subscriber
\\snapshot folder
7
Questions?
8
Transactional Replication
9
Transactional

• One way data movement
• Copies rows across environment
• From publisher to distributor (logread.exe)
• From distribution to subscriber (distrib.exe)
• No schema change required

10
Transactional ReplicationChange Tracking Log
Reader

• SQL Server engine marks transactions for
  replication
• Log Reader retrieves last transaction id
  processed for a publisher from distribution
  database
• Retrieve commands for next N transactions from
  publisher
• Write commands to distribution database
• Formatted as TSQL or stored proc execution
• Advance replication watermark in the log to
  coordinate with the log manager of the database
  engine
• Write history and error information into the
  distribution database

11
Transactional ReplicationForwarding
Distribution Agent

• Retrieve the last transaction received by the
  subscriber
• Retrieve the next set of N transactions from the
  distribution database
• Deliver to subscriber and update the last
  transaction received

12
Transactional
Clients
Distributor
Transactions
Distribution
Publisher
Subscriber
Distrib.exe
Logread.exe
DB log
13
Change Tracking - Updating Subscribers
14
Change Tracking Immediate Updating Subscribers

• Immediate means immediate
• Log based change tracking at publisher
• Change tracking triggers at subscriber
• Trigger captures change and initiates 2pc with
  publisher
• Conflicts detected
• Loopback detection
• Requires schema change
• Binds subscriber to publisher

15
Change Tracking Queued Updating Subscribers

• Primarily for failover from immediate updating
  mode
• Change tracking triggers at subscriber
• Trigger captures change and writes to local
  queue
• QueueReader Agent delivers to subscriber
• Limited Conflict Detection and Resolution
• Requires a schema change

16
Questions?
17
Merge Replication
18
Merge

• Disconnected processing
• Rich Conflict Detection and Resolution
• Rich Filtering capabilities
• Static
• Dynamic
• Join
• Data latency
• Requires schema change
• Performance issues

19
Clients
Clients
Distributor
Distribution
Transactions
Transactions
Publisher
Subscriber
MSmerge_contents
MSmerge_contents
20
BI-Directional Replication
21
Bi-directional Replication

• Can be effective in scaling writes
• Requires careful planning
• Requires intimate understanding of data flow
• Requires control over applications
• Can be utilized
• Must be regulated

22
Clients
Clients
Distributor
Transactions
Transactions
Publisher/ Subscriber
Subscriber/ Publisher
23
Questions?
24
Oracle Replication Supported Features
25
Supported Features

• SQL Server 2005 supports both snapshot
  replication and transactional replication to and
  from Oracle
• Oracle database can reside on any operating
  system that Oracle supports

26
Supported Features

• Data can be publish from Oracle
• Oracle serves as the publisher SQL Server the
  subscriber
• Supported on Enterprise Edition (32-bit and
  64-bit)
• Must use a remote distributor

27
Supported Features

• Data can be publish to Oracle
• Oracle serves as the subscriber SQL Server the
  publisher
• Supported on Enterprise Edition (32-bit and
  64-bit)
• Supported on Standard Edition (32-bit and 64-bit)
• Only push subscriptions are allowed with
  distributor agent running on the distributor
• Only character mode subscriptions are allowed
• Cannot use immediate updating or queued updating
  subscriptions, or be nodes in a peer-to-peer
  topology.
• Cannot be automatically initialized from a
  backup.

28
Supported Features

• Snapshot Replication
• Operates the same as standard SQL Server Snapshot
  Replication

29
Supported Features

• Transactional Replication
• An Oracle Publisher cannot have the same name as
  its SQL Server Distributor any of the SQL Server
  Publishers that use the Distributor or any
  Subscribers that receive the publication.
  Publications serviced by the same Distributor
  must each have a unique name.
• Primary key to foreign key relationships in the
  Oracle database are not replicated to
  Subscribers. However, the relationships are
  maintained in the data as changes are delivered.
• Standard transactional publications support
  tables of up to 1000 columns. Oracle
  transactional publications support 995 columns
  (replication adds five columns to each published
  table).

30
Supported Features

• Transactional Replication
• Collate clauses are added to the CREATE TABLE
  statements to enable case sensitive comparisons,
  which is important for primary keys and unique
  constraints. This behavior is controlled with the
  schema option 0x1000, which is specified with the
  _at_schema_option parameter of sp_addarticle
  (Transact-SQL).
• If you use stored procedures to configure or
  maintain an Oracle Publisher, do not put the
  procedures inside an explicit transaction. This
  is not supported over the linked server used to
  connect to the Oracle Publisher.
• If you use stored procedures to propagate changes
  to Subscribers (the default), be aware that the
  MCALL syntax is supported, but it has different
  behavior when the publication is from an Oracle
  Publisher. Typically MCALL provides a bitmap that
  shows which columns were updated at the
  Publisher. With an Oracle publication, the bitmap
  always shows that all columns were updated.

31
Supported Features

• Transactional Replication
• Oracle publications do not support all of the
  schema options that SQL Server publications
  support.
• Log based and procedure based
• Oracle Publishers only support Row Count
  validation
• Only validates that the published table and
  subscribed table have same number of rows of data
  does not validate actual data.

32
Questions?
33
How to Configure Oracle Replication
34
Configuring

• Publisher
• Created the same way as SQL Server based
  publishers with a few steps that must take place
  before creating the publication
• Subscriber
• Created the same way as SQL Server based
  publishers with a few steps that must take place
  before creating the subscriber

35
Configuring a Publisher

• Create a replication administrative user within
  the Oracle database using the script supplied in
  SQL Server 2005 Books Online.
• Found in directory ltdrivegt\\Program
  Files\Microsoft SQL Server\ltInstancefoldergt\MSSQL\
  Install\oracleadmin.sql.
• Also in BOL article Script to Grant Oracle
  Permissions
• 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 in step one.

36
Configuring a Publisher

• Install the Oracle client software and OLE DB
  provider on the Microsoft SQL Server Distributor,
  and then stop and restart the SQL Server
  instance.
• If the Distributor is running on a 64 bit
  platform, you must use the 64 bit version of the
  Oracle OLE DB provider.
• You must set permissions to the folders on which
  the software is installed
• Described in BOL Article Configuring an Oracle
  Publisher

37
Configuring a Publisher

• Use the Net Configuration Assistant to configure
  network connectivity
• Test network connectivity
• Described in BOL Article Configuring an Oracle
  Publisher
• Considerations for Oracle Home
• Set Oracle Home binaries
• Only one set of binaries can be used by
  replication at a given time.
• These binaries are in the directory
  ORACLE_HOME\bin.
• You must ensure that these binaries are used when
  replication makes connections to the Oracle
  Publisher.
• Configure Distributor
• Log into the Distributor with the accounts used
  by the SQL Server service and the SQL Server
  Agent service and set the appropriate environment
  variables.
• The ORACLE_HOME variable should be set to refer
  to the installation point you specified when you
  installed the client networking software.
• The PATH must include the ORACLE_HOME \bin
  directory as the first Oracle entry that is
  encountered.

38
Configuring a Publisher

• Configure the Oracle database as a Publisher at
  the SQL Server Distributor.
• Two options are available Complete or Oracle
  Gateway.
• 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.

39
Publications

• Use SQL Server Management Studio to connect to
  the distributor used for your Oracle replication
  and create your publications

40
Configuring a Subscriber

• Install the Oracle client software and OLE DB
  provider on the Microsoft SQL Server Distributor,
  and then stop and restart the SQL Server
  instance.
• If the Distributor is running on a 64 bit
  platform, you must use the 64 bit version of the
  Oracle OLE DB provider.
• You must set permissions to the folders on which
  the software is installed
• Described in BOL Article Configuring an Oracle
  Publisher

41
Configuring a Subscriber

• Considerations for Oracle Home
• Set Oracle Home binaries
• Only one set of binaries can be used by
  replication at a given time.
• These binaries are in the directory
  ORACLE_HOME\bin.
• You must ensure that these binaries are used when
  replication makes connections to the Oracle
  Subscriber.
• Configure Distributor
• Log into the Distributor with the accounts used
  by the SQL Server service and the SQL Server
  Agent service and set the appropriate environment
  variables.
• The ORACLE_HOME variable should be set to refer
  to the installation point you specified when you
  installed the client networking software.
• The PATH must include the ORACLE_HOME \bin
  directory as the first Oracle entry that is
  encountered.

42
Configuring a Subscriber

• Create a TNS name for the subscriber
• Use the Net Configuration Assistant to configure
  network connectivity
• Test network connectivity
• Described in BOL Article Oracle Subscribers

43
Subscriptions

• Use SQL Server Management Studio to connect to
  the publisher and create your subscriptions to a
  publication
• Make sure you understand the data mappings
  between your SQL Server and Oracle databases
• Described in BOL Article Oracle Subscribers

44
Questions?
45
A few administrative notes
46
Administration

• Oracle Import When the ignore option set to n
  Oracle Import will drop and recreate the table
  this will remove replication objects created on
  the table for transactional replication.
• SQLLoader When the direct option is set to
  true replication triggers will not fire on
  insert. This option must be set to false
  (default) to fire replication triggers.

47
Administration

• You must drop and rebuild a publication if you
• Truncate a published table.
• Rename a published table.
• Add a column to a published table.
• Drop or modify a column that is published for
  replication.
• Perform non-logged operations.

48
Administration

• Oracle replication will create a linked server to
  the Oracle server from SQL Server.
• This linked server can only be used for
  replication you must configure additional
  linked servers if you wish to interact with
  Oracle from SQL Server for any other purpose.

49
Questions?