\n
Replication Quick Start Guide for Oracle. Published: March 1. SQL Server Technical Article. Writers: Matt Hollingsworth; Michael Blythe. Technical Reviewer: Sandra Ward; Deepak Kumar; Gopal Ashok. Project Editor: Jeannine Nelson- Takaki. Designer: Kristie Smith. Using a windows setup program. Copyright @ 2007, John Jay King 11 BI/XML Publisher is Web-Based. Over 4000 hours of in-depth training. Three leading publishers: O’Reilly Media, Infinite Skills, Packt Publishing. Start any video training for FREE. What is O\'Reilly Media? Technology Books, Tech Conferences, IT Courses & News. Published: March 2. Applies To: SQL Server 2. Summary: This paper provides a hands- on introduction to publishing data from Oracle to SQL Server. You will be led through a series of tools and wizards to demonstrate the steps that you must follow to configure, test, and monitor Oracle publishing. This new feature is built on the well- established foundation of SQL Server snapshot replication and transactional replication, providing both performance and usability. Publishing servers can be deployed on any Oracle supported hardware and operating system. Publishing data from Oracle is useful in the following scenarios: Scenario. Description. Microsoft . NET Framework application deployments. Develop applications in Microsoft Visual.
The word oracle comes from the Latin verb . In extended use, oracle may also refer to the site of the oracle, and to the. Oracle XML Publisher Administration and Developer\'s Guide, Release 12 Part No. Switch to SQL Server when satisfied with the migration. This paper provides a hands- on introduction to publishing data from Oracle to SQL Server. Connecting Oracle BI Publisher to SQL Server. One of the main features of Oracle Business Intelligence Publisher (BIP). You will be led through a series of tools and wizards to demonstrate the steps that you must follow to configure, test, and monitor Oracle publishing. At the end of the paper, you should have an understanding of Oracle publishing and will have set up a functional replication system with data changes flowing from an Oracle Publisher to a SQL Server Subscriber. Installing Oracle Client Software and Testing the Connection to the Oracle Server. This section of the paper covers the steps that are required before configuring replication between Oracle and SQL Server: Installing the Oracle client software and OLE DB provider on the Microsoft SQL Server Distributor. Verifying that you can make a connection between SQL Server and Oracle. Creating a replication administrative user in the Oracle database. For each table that you will publish, granting SELECT permission directly (not through a role) to the Oracle administrative user that you created in step one. For more information, see . For more information, see the Oracle documentation. You can select an installation type of Administrator, Runtime, or Custom. Installing the Oracle client software. Verify the service account under which SQL Server is running on the Distributor. You must grant to this account read and execute permissions for the directory (and all subdirectories) in which the Oracle client networking software is installed. Verifying the SQL Server service account. Verify that you can connect from the Distributor to the Oracle database by using Oracle. By default for Oracle 1. Oracle\\product\\1. Client. The account used to connect to the Oracle database must have sufficient permissions to execute the script in the next step. In this example, we connect to the Oracle database using the built- in Oracle account system. In the following illustration, . We use the runas command to run SQL*Plus under an appropriate user, using the following syntax. Runas /user: . Verifying the connection between SQL Server and Oracle. After you connect, run the oracleadmin. Oracle database. This script creates a replication administrative user and grants the minimum necessary rights to perform replication. On the SQL Server Distributor, open a Command Prompt window. Type the following syntax to use SQL*PLUS to connect to the Oracle database and execute the oracleadmin script from its default install directory: Note: The line has been split into multiple lines for readability. Creating a replication administrative user. For the tables you want to publish, use SQL*PLUS to grant SELECT permissions on an Oracle table to the replication administrative user that you created in the previous step. On the SQL Server Distributor, open a Command Prompt window. Type the following syntax to connect to the Oracle database using the scott sample user provided by Oracle: sqlplus scott/tiger@orcl. At the SQL command prompt, type the GRANT statement followed by the table and user name. Granting SELECT permissions on Oracle tables. Now that you have prepared the Oracle database and SQL Server Distributor, in the next section you will use the tools in SQL Server 2. Configuring the Distributor and Creating the Publication. This section of the paper describes how to create a publication, using SQL Server 2. Oracle database. If the instance of SQL Server 2. Distributor, it will be configured when the publication is created. First, you must connect to the SQL Server Distributor: Open SQL Server 2. Management Studio. If you are not already connected to the instance of SQL Server that will serve as the Distributor, click Object Explorer and select the instance. Figure 6. Connecting to the Distributor. After you have connected to the instance, start the New Publication Wizard. Expand the server node. Starting the New Publication Wizard. The New Publication Wizard introduction page is displayed. Figure 8. New Publication Wizard introduction page. If the SQL Server instance that you connected to has not been previously configured as a Distributor, the Distributor page is displayed. If you have already configured the instance, you will skip this step and the next step. Specifying the Distributor. If the SQL Server instance has not been previously configured as a Distributor, the SQL Server Agent Start page is displayed. By default, SQL Server Agent runs the replication agents that are used to track changes and distribute data. Therefore, in most cases, you want to make sure that SQL Server Agent is always running on that instance. Specifying how SQL Server Agent starts. On the Oracle Publisher page, you must select an Oracle database to act as the Publisher. If the instance of SQL Server has not been previously configured as a Distributor, the wizard will also configure the instance, and display a confirmation dialog box. Adding an Oracle Publisher. In the Distributor Properties dialog box, configure the Distributor with the connection information necessary to connect to the Oracle database. To change the publishing type, click Options. 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. Specifying the connection properties. After the connection succeeds, the Oracle Publisher is added to the list of Publishers in the Distributor Properties dialog box. If the connection fails, verify that all previous setup steps completed successfully. For more information about troubleshooting connection issues, see . Saving the Distributor configuration. On the Oracle Publisher page, click Next. Adding the Oracle Publisher. On the Publication Type page, you select the type of replication to use. Select Snapshot publication if you want all the published data to be copied every time replication executes. Select Transactional publication if you want to start by copying all the published data and then continuously stream subsequent data changes to SQL Server in near real time. For more information, see the topic . Specifying the publication type. On the Articles page, select the tables that you want to publish from the Objects to publish list. If there are columns of data that you do not want to replicate, you can remove the columns from the published table by clearing the check box next to each column. Selecting the tables to replicate. If a column contains an Oracle data type that might result in data loss when it is converted to a SQL Server data type, a yellow warning sign is displayed next to the column. In the following illustration, the HIREDATE column contains date data that might result in data loss. You can optionally select an alternative to the default SQL Server data type. First, access the properties for the published table. Accessing properties of published tables. You can modify the data type that is used at the Subscriber by selecting a different SQL Server data type from the Subscriber data type list. Changing data types for Subscriber data. You can optionally specify that you require only a subset of the data to be published. To specify a filter for a table, you specify a WHERE clause using Oracle- compliant syntax. Filtering data. On the Snapshot Agent page, select whether you want to start creating snapshot data files immediately after the wizard finishes, or at a later time. Scheduling the snapshot. The Snapshot Agent and Log Reader Agent pull data from the Oracle Publisher. On the Agent Security page, you specify the Windows user under which each agent should run. By default, the snapshot folder is located at < drive> \\Program Files\\Microsoft SQL Server\\MSSQL. MSSQL\\repldata\\. If you want to specify the same settings for the Log Reader Agent and Snapshot Agent, specify the Snaphot Agent settings first and then select Use the security settings from the Snapshot Agent. Setting security options for replication agents. On the Wizard Actions page, you specify when the publication will be created, and whether the publication configuration is saved in a script. We recommend that you select the check box for scripting so that you will have a copy of the replication configuration for future reference. Scripting and creating the publication. On the Complete the Wizard page, you can verify that all options are correct, and provide a name for the publication. Completing the New Publication Wizard. The New Publication Wizard now saves the configuration information that you have entered and creates the publication. Creating the publication. The publication that you created appears under the Replication folder in SQL Server Management Studio. Viewing the new publication. Verifying the Status of Replication Agents.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |