STEPS TO SETUP REPLICATION IN SYBASE
• At the destination data server: Create a copy of a table into which data will be replicated from the primary table. The copy may contain all or a subset of the columns from the primary table.
• At the primary Replication Server: Create a replication definition to identify the table data you want to replicate. You can create one or more replication definitions per table that can be replicated into different destination databases. You can also create replication definitions for stored procedures.
Once you have created a replication definition, transactions are available for replication to qualifying destination Replication Servers that subscribe to the replication definition.
You can create a set of articles that reference replication definitions and group them in a publication. If you want to limit the transactions sent to the destination database to those that affect certain rows, use a where clause in the article.
• At the primary Adaptive or SQL Server: Use the sp_setreptable system procedure to mark a table as replicated.
When you mark a table as replicated in the primary data server, the replication agent for the primary database can forward the table’s transactions to the primary Replication Server.
If you want to replicate text or image columns, you may also need to use the sp_setrepcol system procedure.
If you use a different data source with a replication agent, refer to your replication agent documentation for information about marking primary objects for replication.
• At destination Replication Servers: Create a subscription for replication definitions that were created in primary Replication Servers. A subscription allows the destination table to receive the initial data from the primary (source) table through a process known as materialization, and to begin receiving subsequent replicated data updates. You can create multiple subscriptions for each replication definition, but a replicate table can subscribe to only one replication definition. You can set up a subscription to receive all transactions for a destination table, or use a where clause to receive just the transactions that affect certain rows. Create publication subscriptions for publications created at the primary Replication Server. When you do so, Replication Server creates an article subscription for each article in the publication. Creating subscriptions completes the process of replicating data. See Chapter 10, “Managing Subscriptions” for details.
Steps for CP2 Replication
----------------------------1. create user ra_user_name set password {ra_password | null}
2. grant connect source to ra_user_name
Note : If the Replication Server already manages a primary database, you can use the “RepAgent user” that already exists for the new primary database.
3. alter connection to data_server.database set log transfer to ’on’
4. sp_configure ’enable rep agent threads’
5. sp_config_rep_agent dbname, ’enable’, ’rs_name’,’rs_user_name’, ’rs_password’
6. Create the rs_marker stored procedure and set its replicate status to “true,” using the sp_setrepproc system procedure with the function keyword. You can find the rs_marker stored procedure in the file rs_install_primary.sql or rsinssys.sql in the scripts directory of the Sybase release directory.
7. sp_start_rep_agent dbname
8. Find out the maintenance user for the Replication server ( using rs_helpuser in RSSD DB)
9. grant all on table_name to maintenance_user
10. grant execute on rs_marker to public
grant execute on rs_update_lastcommit to public
-----------------------------------------------------------------------11 . Create the table as the Database Owner in the primary database, if it does not already exist, or, if there is a different table owner, specify the table owner name when you create the replication definition.
12 In the primary Replication Server, create one or more replication definitions for the table from which you want to copy data. Each replication definition can be subscribed to by a different site that uses a different table view.
When you create replication definitions, anticipate the requirements for the subscribing table. The replication definition may contain all or a subset of the columns in the source table. It may specify the same or different table names, owner names, column names, or datatypes for the source and destination tables. It may change the datatype of the replicated value.
13 If you are using publications, execute the following steps at the primary Replication Server.
• Create one or more publications for the tables you want to replicate using create publication.
• Create one or more articles, replication definition extensions, for each replication definition you want to include in the publication using create article. You can include a where clause to specify a subset of rows to send to the destination database.
• Validate the publications, using validate publication, so that you can create subscriptions against them.
14 Mark the source table for replication. In the primary Adaptive Server, use sp_setreptable to enable table replication. This step allows the RepAgent thread to forward transactions for the table to the primary Replication Server.
15 If the source table contains text, image, or rawobject columns, you may need to use sp_setrepcol in the primary Adaptive Server to adjust the replication status for these columns.
16 Prepare a login name for the user creating the subscription. Login names that create subscriptions at destination Replication Servers must also exist at the source Replication Server.
17 In the replicate database, create a table that matches the schema published by the replication definition. Create the destination table as the Database Owner or as the same table owner specified in the replication definition. In Adaptive Server
The destination table may have the same or different name and/or the same or different owner name as the source table. It may contain all or a subset of the columns in the source table, with the same or different column names or datatypes. The replication definition must specify any such differences between the source and destination tables. Note The destination table may include a column that is not in the replication definition if the column accepts null values, has a defined default value, or you use a custom function string to apply a value to that column.
18 Grant the replicate database maintenance user login name select, insert, delete, and update permissions on the destination table. The maintenance user executes commands for replicated transactions.
19 If necessary, customize your database operations using functions, function strings, and function-string classes. Replication Server function strings execute data server operations.
20 Create a subscription in the replicate Replication Server. If you are using publications, Log in to a replicate Replication Server and create one or more subscriptions to the table replication definition for the data you want to copy. You can subscribe to all the rows in the replication definition’s columns, or use a where clause to copy only certain rows. A replicate database can subscribe to multiple replication definitions of a primary (source) table, but a replicate table can subscribe to only one replication definition of a source table. When you create a subscription, the destination table is filled in with the initial table data in a process called materialization. In most cases, Replication Server copies data into the destination table automatically. You can also manually materialize the data.
21 If you are using publications, create a publication subscription against the publications created in step 4. Execute create subscription at the replicate Replication Server.
When you create a publication subscription, Replication Server creates subscriptions against each article in the publication. Article subscriptions do not contain where clauses.
22 Check the subscription status. Verify that the subscription data has fully materialized in the replicate database and that transactions are replicating successfully.