This guide will hopefully cut to the chase and show how you can set up a simple replication system consisting of 1 dataserver, 2 databases (one replicating one receiving) and 2 replication servers, one for each database.
Installing Replication server
This is quite straight forward;
1) After unloading the files from the CD you set up the $SYBASE variable and also $PATH to be $SYBASE/bin:$PATH.
2) Next run dsedit and add the names and network addresses of the replication server you are building, the ID server (original Repserver) if this is your 2nd or more Repserver, and also any dataservers you will be connecting to.
3) The menus are relatively self-explanatory, make sure that if this is the first Replication server in the system that you define it as an ID server. Also note that the RSSD devices are on the same machine as the dataserver used for the RSSD’s, ideally each Repserver would have its own dataserver, but in this example they also go from the one dataserver. The disk-partition goes in the same place as the Repserver files.
4) After having set all the option and having a complete next to each menu item, you can build the Repserver, if there are any problems you will be notified.
Setting up the first Replication server
This replication server is called phoebe_rep
On this replication server we need to create a connection to the primary database which is called sundry and sits on dataserver phoebe. This is done using rs_init, and selecting the option add database, the menu’s are self-explanatory, the only thing to look out for is the option deciding whether the database is replicated or not, for this connection select yes, as it is the primary database. This should create OK and set up the dsi links etc and also the rep-agent.
The connection to a database can be stopped by issuing the following command from the Repserver, which controls it; suspend connection to phoebe.sundry and restarted with resume connection to phoebe.sundry.
Setting up the second Replication server
The second replication server is called phoebe_2nd_rep
On the creation of this second Repserver be sure to make sure the first Replication server is up and running and then point to it as the ID server, also when installed add the replicate database to it using rs_init, for this select no for the question, is this database replicated. The replicate database in this example is called sundry 3.
Setting up Route
We need to create a route from each primary replication server to each replication server that subscribes to data from it(In this case from phoebe_Rep to phoebe_2nd_rep).
To do this you will need to do the following;
You will need to alter the connection from the RSSD database for the primary Repserver as follows (from the Primary Repserver), suspend connection to phoebe_rep_RSSD
Then alter connection to phoebe_rep_RSSD set log transfer to on followed by resume connection to phoebe_Rep_RSSD . The reason for this is so that the Replicate Repserver can read any changed to the primary Repserver in terms of new definitions created etc and also get all the ones which already exist.
Next you have to enable a repagent for this database, so log in to the phoebe server and issue the following command; sp_config_rep_agent phoebe_rep_RSSD, enable, phoebe_rep, sa, NULL followed by sp_start_rep_agent phoebe_rep_RSSD
The command to actually create the route can now be issued from the primary Repserver (phoebe_Rep),
Create route to phoebe_2nd_rep
Set username as sa
Set password as NULL
The progress of this route creation can be checked with rs_helproute run from the RSSD for the primary Repserver i.e. from phoebe_rep_RSSD.
If everything is OK this will report the rote as active.
Setting up Replication
The next step is to select the table you want to replicate in the primary database (sundry) which is managed by the primary Repserver (phoebe_rep). In this example the table is called alpha1 and contains only 2 columns, one is an int and the other a char(10). To enable this table for replication, issue the following command from database sundry;
sp_setreptable alpha1, true
Incidentally the commands to start and stop the repagent for sundry (which monitors the transactions on the alpha1 table through the syslogs) is sp_start_rep_agent sundry and sp_stop_rep_agent sundry. Also to replicate a stored procedure (which can be a much more efficient way of doing things) the command is sp_setrepproc proc1, true
You also need to create a table with the same column structure in the replicate database (sundry3) which is managed by the replicate Repserver (phoebe_2nd_rep), i.e.
create table alpha1 (a int, b char(10))
This is also true for replicating stored procs where an identical stored proc must already exists in the replicate database.
Now we can check to see whether all the connections/routes and repagents etc are all up by issuing admin who_is_down at each of the replication servers. If this is all clear then we are ready to
To create the replication definition the command issued from the primary Repserver is;
create replication definition repdef1
with primary at phoebe.sundry
with all tables named ‘alpha1’
(a int, b char(10))
primary key (a)
You always have to specify a primary key!!
To check that the routing is working fine you can check whether the replication definition has been copied over to the replicate Replication server (phoebe_2nd_rep) by issuing the following command in phoebe_2nd_rep_RSSD on dataserver phoebe;
Incidentally the stored proc to check on subscriptions is rs_helpsub
Now we need to set up a subscription to this replication definition in the replicate Repserver as follows (command issued from phoebe_2nd_rep);
create subscription sub1
with replicate at phoebe.sundry3
The server will now start materializing this table from the primary database to the replicate database and you can see the progress of this by issuing the following command from the Replicate Repserver;
check subscription sub1
with replicate at phoebe.sundry3
If this comes back as valid then everything is fine
You also check on subscription by issuing rs_helpsub from the replicate RSSD database.
The replication process is now set up and to check it insert a few rows into the sundry database alpha1 table and see if they are replicated to the sundry3 alpha1 table.