http://goo.gl/EmKxy0

Archive

Posts Tagged ‘replication setup’

SAP D&T Academy Video : How to Setup table Replication ?

December 20th, 2012 No comments

Adding a Primary DB in Replication Server without rs_init

April 23rd, 2011 No comments

Task :  Adding a Primary Database to Replication Server without using rs_init.  So now we will me moving the data out to PROD_ASE.pdb2 database

1. Create the new database pdb2 as below                     <DS>

1> create database pdb2  on rep_db3_data=’5M’ log on rep_db3_log=’4M’

2> go

00:00:00000:00014:2011/04/23 04:07:02.01 kernel  Setting console to nonblocking mode.

00:00:00000:00014:2011/04/23 04:07:02.01 server  Timestamp for database ‘pdb2′ is (0×0000, 0×00001428).

CREATE DATABASE: allocating 2560 logical pages (5.0 megabytes) on disk

‘rep_db3_data’ (2560 logical pages requested).

CREATE DATABASE: allocating 1536 logical pages (3.0 megabytes) on disk

‘rep_db3_log’ (2048 logical pages requested).

Database ‘pdb2′ is now online.

Creating a table for moving the data   <DS>      

1> use pdb2

2> go

1> create table table2 ( col1 int primary key, col2 varchar(10))

2> go

2. Again create the maint user login and add the maint user in pdb2 ( I made it as alias, but we can also add it as simple user)          <DS>      

1> sp_addlogin pdb2_maint,pdb2_maint_ps,pdb2

2> go

Password correctly set.

Account unlocked.

New login created.

(return status = 0)

1> use pdb2

2> go

1> sp_addalias pdb2_maint,dbo

2> go

Alias user added.

(return status = 0)

3. Grant the replication role to pdb2_maint login.         <DS>  

1> grant role replication_role to pdb2_maint

2> go

4. Need to install the some system tables and stored procs in pdb2 for proper function as Primary database.    <DS>

Move int $SYBASE/$SYBASE_REP/scripts/

Run the script rs_install_primary.sql in pdb2 database

[sybase@localhost scripts]$ isql -Usa -SPROD_ASE -irs_install_primary.sql -w9999 -e -ors_install_primary.sql.out -Dpdb2

Password:

It will create sp and tables in pdb2 which is required for replication.

5. Configure the rep agent on the pdb2 ans its primary database       <DS>

1> use pdb2

2> go

1> sp_config_rep_agent pdb2,enable,”PROD_RS”,”PROD_RS_ra”,”PROD_RS_ra_ps”

2> go

Replication Agent enabled for database ‘pdb2′. The Replication Agent thread

needs to be started using sp_start_rep_agent.

(return status = 0)

PROD_RS_ra /PROD_RS_ra_ps is the replication user and password. If there is already a rep agent you can view

your user from rs_users tables, other wise you need to create a replication user to connect with Rep Server.

6. Now create the connection to this database with log transfer on as its primary database.      <RS>

1> create connection to “PROD_ASE”.”pdb2″

2> set error class rs_sqlserver_error_class

3> set function string class rs_sqlserver_function_class

4> set username “pdb2_maint”

5> set password  “pdb2_maint_ps”

6> with log transfer on

7> go

Connection to ‘PROD_ASE.pdb2′ is created.

7. After doing the admin who_is_down in RS, you will get rep agent down.   <RS>

1> admin who_is_down

2> go

Spid     Name                 State

Info

——– ——————– —————————————-

——————————————————————————–

REP AGENT            Down

PROD_ASE.pdb2

8. Start the rep agent in primary database as below :    <DS>

1> use pdb2

2> go

1> sp_start_rep_agent ‘pdb2′

2> go

Replication Agent thread is started for database ‘pdb2′.

(return status = 0)

9. You will not be having any thread down in replication environment:  <RS>

1> admin who_is_down

2> go

Spid     Name                 State

Info

——– ——————– —————————————-

——————————————————————————–

10. Now configuration Completed, the next step is testing:

Adding a Replicate DB in Replication Server without rs_init

April 23rd, 2011 No comments

Hi Guys,
Now the task is : We want to move the data from PROD_ASE.pdb to new database PROD_ASE.rdb2. We want to complete this setup without using rs_init.

Always remember, whenever we want to add a primary/replicate database, we need a connection. A connection always represents a replicate database (means connection must be having DSI thread).

We will use the same pdb , which we added in previous post, Now I am going to add rdb2 database in RSS. Basically it requires a connection and some set of tables in rdb which requires for replication.

1. Created rdb2 on data and log device.

create database rdb2  on rep_db3_data=’5M’ log on rep_db3_log=’4M’

create a table in database :

1> use rdb2
2> go
1>  create table table1 ( col1 int primary key, col2 varchar(10))
2> go

2. Add maint user login for rdb2 as rdb2_maint

1> sp_addlogin rdb2_maint,rdb2_maint_ps,rdb2
2> go
Password correctly set.
Account unlocked.
New login created.
(return status = 0)

3. Add the rdb2_main user in the database rdb2 as dbo alias

(To avoid any pemission issue, even we can add it as simple user ,

in that case for each and every table we need to grant the permission separately)

1> use rdb2
2> go
1> sp_addalias rdb2_maint,dbo
2> go
Alias user added.
(return status = 0)

4. Grant the replication Role to maint user

1> grant role replication_role to rdb2_maint
2> go

5. Install the replication table in rdb2 database by script rs_install_replicate.sql

[sybase@localhost scripts]$ isql -Usa -SPROD_ASE -Dpdb2 -w999 -irs_install_replicate.sql -ors_install_replicate.sql.out

This script resides in script folder of replication server.

After the installation of script you will have following tables in rdb2 DB

1> use rdb2
2> go
1> select name from sysobjects where name like ‘rs%’
2> go
name
———–
rs_get_lastcommit
rs_get_thread_seq
rs_initialize_threads
rs_lastcommit
rs_threads
rs_ticket_report
rs_update_lastcommit
rs_update_threads

(8 rows affected)

6. Now login into Replication Server andcreate the connection as :

1>
2> create connection to PROD_ASE.rdb2
set error class to rs_sqlserver_error_class
set function string class to rs_sqlserver_function_class
set username to rdb2_maint
set password to rdb2_maint_ps

3> 4> 5> 6> 7>

8> go
Connection to ‘PROD_ASE.rdb2′ is created.

Make sure there is no thread down

7. As we already created the replication def for pdb.table1 table, we will use here.

Now create the subscription for PROD_ASE.rdb2

1>
2> create subscription repdef_pdb_table1_sub
3> for repdef_pdb_table1
4> with replicate at PROD_ASE.rdb2
5> without materialization
6> go
Subscription ‘repdef_pdb_table1_sub’ is in the process of being created.
1> check subscription repdef_pdb_table1_sub
for repdef_pdb_table1
with replicate at PROD_ASE.rdb2

2> 3>
4> go
Subscription repdef_pdb_table1_sub is VALID at the replicate.
Subscription repdef_pdb_table1_sub is VALID at the primary.
1>

8. Test the replication now, set up is completed:

1> use pdb
2> go
1> insert table1 values(1890,’val’)
2> go
(1 row affected)
1> use rdb2
2> go
1> select * from table1
2> go
col1        col2
———– ——————–
1890 val

(1 row affected)
1> use rdb
2> go
1> select * from table1
2> go
col1        col2
———– ——————–
100 val1
101 val2
1890 val

(3 rows affected)

Sybase Replication Setup using rs_init on Linux -4

April 15th, 2011 No comments

5. Creating Rep Def , Sub and testing Replication:

a) creating table in the pdb and rdb with name table1

1> use pdb
2> go
1> create table table1 ( col1 int primary key, col2 varchar(10))
2> go

1> use rdb
2> go
1> create table table1 ( col1 int primary key, col2 varchar(10))
2> go

b) Marking the table1 for replication.
1> use pdb
2> go
1> sp_setreptable table1
2> go
The replication status for ‘table1′ is currently false, owner_off.
(return status = 0)
1> sp_setreptable table1,true
2> go
The replication status for ‘table1′ is set to true, owner_off.
(return status = 0)

c) Creating replication Definitation & Sub:

1> create replication definition repdef_pdb_table1
2> with primary at PROD_ASE.pdb
3> with all tables named table1
4> (col1 int, col2 varchar(10))
5> primary key (col1)
6> replicate minimal columns
7>
8> go
Replication definition ‘repdef_pdb_table1′ is created.

1> create subscription repdef_pdb_table1_sub
2> for repdef_pdb_table1
3> with replicate at PROD_ASE.rdb
4> without materialization
5> go
Subscription ‘repdef_pdb_table1_sub’ is in the process of being created.

1> check subscription repdef_pdb_table1_sub
2> for repdef_pdb_table1
3> with replicate at PROD_ASE.rdb
4> go
Subscription repdef_pdb_table1_sub is VALID at the replicate.
Subscription repdef_pdb_table1_sub is VALID at the primary.

Now setup is completed.
d) Testing the data movement :

1> use pdb
2> go
1> insert table1 values(100,’val1′)
2> go
(1 row affected)
1> use rdb
2> go
1> select * from table1
2> go
col1 col2
———– ——————–
100 val1

(1 row affected)
1> use pdb
2> go
1> insert table1 values(101,’val2′)
2> go
(1 row affected)
1> use rdb
2> go
1> select * from table1
2> go
col1 col2
———– ——————–
100 val1
101 val2

(2 rows affected)

Now its working fine and setup is complicated!!

In Next post , I will try to setup the replication without rs_int and will post here.
Till then Happy Sybase Learning! Bye

Sybase Replication Setup using rs_init on Linux -3

April 14th, 2011 No comments

4.Adding Primary and Replicate Database

a)Before Moving forward, We are setting up the replication between two databases which are in same server ie PROD_ASE.pdb is Primary database, PROD_ASE.rdb is Replicate Database.

b)Logged in PROD_ASE and create database as :
1> create database pdb on rep_db1_data=”10M” log on rep_db1_log=”5M”
2> go
00:00:00000:00013:2011/04/14 10:37:59.37 server Timestamp for database ‘pdb’ is (0×0000, 0×00001428).
CREATE DATABASE: allocating 5120 logical pages (10.0 megabytes) on disk
‘rep_db1_data’ (5120 logical pages requested).
CREATE DATABASE: allocating 2560 logical pages (5.0 megabytes) on disk
‘rep_db1_log’ (2560 logical pages requested).
Database ‘pdb’ is now online.
1> create database rdb on rep_db2_data=”10M” log on rep_db2_log=”5M”
2> go
00:00:00000:00013:2011/04/14 10:39:00.88 server Timestamp for database ‘rdb’ is (0×0000, 0×00001428).
CREATE DATABASE: allocating 5120 logical pages (10.0 megabytes) on disk
‘rep_db2_data’ (5120 logical pages requested).
CREATE DATABASE: allocating 2560 logical pages (5.0 megabytes) on disk
‘rep_db2_log’ (2560 logical pages requested).
Database ‘rdb’ is now online.

c) Enable the rep agent in PROD_ASE dataserver
1> sp_configure ‘rep_agent’,1
2> go

d) Adding the pdb and rdb in Replication Server System ,
pdb = Primary Database, rdb=Replicate Database
rs_init_pdb_rdb Log

e) admin who output before adding pdb/rdb
1> admin who
2> go
Spid Name State
Info
——– ——————– —————————————-
——————————————————————————–
13 DSI EXEC Awaiting Command 101(1) PROD_ASE.PROD_RS_RSSD
9 DSI Awaiting Message 101 PROD_ASE.PROD_RS_RSSD
8 SQM Awaiting Message 101:0 PROD_ASE.PROD_RS_RSSD
10 dSUB Sleeping
6 dCM Awaiting Message
7 dAIO Awaiting Message
11 dREC Sleeping dREC
14 USER Active sa
5 dALARM Awaiting Wakeup
12 dSYSAM Sleeping

1> admin rssd_name
2> go
RSSD Dataserver RSSD Database
—————- ————————
PROD_ASE PROD_RS_RSSD

admin who output after adding pdb/rdb

1> admin who
2> go
Spid Name State Info
——– ——————– —————————————- ——————————————————————————–
13 DSI EXEC Awaiting Command 101(1) PROD_ASE.PROD_RS_RSSD
9 DSI Awaiting Message 101 PROD_ASE.PROD_RS_RSSD
8 SQM Awaiting Message 101:0 PROD_ASE.PROD_RS_RSSD
25 DSI EXEC Awaiting Command 102(1) PROD_ASE.pdb
20 DSI Awaiting Message 102 PROD_ASE.pdb
22 DIST Awaiting Wakeup 102 PROD_ASE.pdb
23 SQT Awaiting Wakeup 102:1 DIST PROD_ASE.pdb
21 SQM Awaiting Message 102:1 PROD_ASE.pdb
19 SQM Awaiting Message 102:0 PROD_ASE.pdb
24 REP AGENT Awaiting Command PROD_ASE.pdb
37 DSI EXEC Awaiting Command 104(1) PROD_ASE.rdb
36 DSI Awaiting Message 104 PROD_ASE.rdb
35 SQM Awaiting Message 104:0 PROD_ASE.rdb
10 dSUB Sleeping
6 dCM Awaiting Message
7 dAIO Awaiting Message
11 dREC Sleeping dREC
39 USER Active sa
5 dALARM Awaiting Wakeup
12 dSYSAM Sleeping
1> admin rssd_name
2> go
RSSD Dataserver RSSD Database
—————- ————————
PROD_ASE PROD_RS_RSSD

Sybase Replication Setup using rs_init on Linux -2

April 13th, 2011 No comments

Step 3: Creating/Building the Replication Server

Before creating the Replication Server please perform the following steps

a) create  a common interface file which will include all the dataserver, replication servers and copy this new interface file to all dataserver and replication server enviorment. By doing this, you will not get any connectivity problem.

b) Touch the stable device file before creating the Replication Server.

[sybase@localhost install]$ mkdir /home/sybase/REP/data
[sybase@localhost install]$ touch /home/sybase/REP/data/stable_device.dat
[sybase@localhost install]$ pwd
/home/sybase/REP/REP-15_0/install
[sybase@localhost install]$ cat ../../interfaces
PROD_RS
master tcp ether localhost 11752
query tcp ether localhost 11752
SAMPLE_RS_ERSSD
master tcp ether localhost 11751
query tcp ether localhost 11751
PROD_ASE
master tcp ether localhost 5000
query tcp ether localhost 5000
PROD_ASE_BS
master tcp ether localhost 5001
query tcp ether localhost 5001
c) Execute the replication Server SYBASE.sh to include RS env and move to install dir and execute rs_init.
rs init Log File

Sybase Replication Setup using rs_init on Linux -1

April 13th, 2011 No comments

Sybase Replication Server Installation Steps:

1. Download the package and untar it.

2. Run the setup executable to unload the package in Sybase Rep Server Installation dir.

3. Building the Replication Server

4. Adding Primary & Replicate Databases.

5. Creating rep def, sub and testing.

Step 1 &2 :

[sybase@localhost Desktop]$ tar -xvf rs15lin32.tgz
./
README.SAMreport
archives/
archives/dsparser.jar
archives/jndi.jar
archives/ldap.jar
archives/log4j.jar
archives/providerutil.jar
archives/sylapi.jar
archives/libsylapij.so
media.inf
samreport/
samreport/SAMreport_SYBASE.lic
samreport/install_unix.htm
samreport/j2re-1_4_1_03-linux-i586.bin
samreport/samreport-3_6-i86_r6.bin
setup
suite.jar
JVM/
JVM/bin/
JVM/bin/classic/
JVM/bin/classic/libcore.so
JVM/bin/classic/libjvm.so
JVM/bin/JavaPluginControlPanel
………..
JVM/lib/psfont.properties
JVM/lib/security.jar
JVM/lib/xerces.properties
JVM/lib/xml.jar
JVM/.systemPrefs/
JVM/.systemPrefs/.systemRootModFile
JVM/.systemPrefs/.system.lock

[sybase@localhost Desktop]$ ./setup -console
InstallShield Wizard

Initializing InstallShield Wizard…

Searching for Java(tm) Virtual Machine…
……..
Running InstallShield Wizard…

——————————————————————————-
Welcome to the InstallShield Wizard for Replication Server Software, Version
15.0

The InstallShield Wizard will install Replication Server Software, Version 15.0
on your computer.
To continue, choose Next.

Press 1 for Next, 3 to Cancel or 4 to Redisplay [1] 1

——————————————————————————-
1) Argentina 2) Australia
3) Belgium(English) 4) Belgium(French)
5) Brazil 6) Canada
7) Denmark 8) Europe,Middle East, and Africa – General
9) France(English) 10) France(French)
11) Germany(English) 12) Germany(German)
13) Hong Kong 14) India
15) Italy(English) 16) Italy(Italy)
17) Japan 18) Korea
19) Malaysia 20) Mexico
21) Netherlands 22) New Zealand
23) Norway 24) People’s Republic of China(PRC)
25) Philippines 26) Singapore
27) Spain(English) 28) Spain(Spanish)
29) Sweden 30) Switzerland(English)
31) Switzerland(German) 32) Taiwan
33) Thailand 34) United Kingdom
35) United States of America 36) Any Other Locations

Please enter the number of the location you are installing. (1-37) [1] 35

LICENSE AGREEMENT

United States and Canada

IMPORTANT NOTICE: Read this License Agreement (“Agreement”) carefully before
using the enclosed Program. You may Use the Program acquired in the United
States and Canada only, in accordance with the following terms and conditions.
IF YOU DO NOT AGREE TO BE BOUND BY THESE TERMS, YOU MAY NOT USE THE PROGRAM. By
installing or using the Program in any way, You acknowledge that You have read,
understand and agree to the terms of this Agreement. If You do not agree with
these terms, promptly return the Program and package (and all other product
contents, including Documentation) with Your receipt or other proof of purchase
to the entity from which You obtained this product within 30 days and request a
refund. THIS IS A LICENSE AND NOT A SALE.

click the “I agree” button if you wish to install the program.

Press ENTER to read the text [Type q to quit] q

If you cannot read the agreement for the related location, you may not have the
fonts installed on your system to view the agreement properly. If you cannot
read this agreement, go to www.sybase.com/softwarelicenses and read it there
before agreeing.

I agree to the terms of the Sybase license for the install location
specified.(Y/N) [N]Y

Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1] 1

Please wait…

——————————————————————————-
Please enter the directory where you would like to install the products or
press ENTER to accept the default.

Destination Directory [/home/sybase/15.5] /home/sybase/REP

Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1] 1

——————————————————————————-
Choose the setup type that best suits your needs.

[X] 1 – Typical
The program will be installed with the suggested configuration.
Recommended for most users.

[ ] 2 – Full
The program will be installed with all the products and features.

[ ] 3 – Custom
The program will be installed with the features you choose.
Recommended for advanced users.

To select an item enter its number, or 0 when you are finished: [0]

Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1] 1

——————————————————————————-
Replication Server Software, Version 15.0 will be installed in the following
location:

/home/sybase/REP

with the following features:

Sybase Servers
Replication Server
Connectivity
Common Connectivity Components
Language Modules for Replication Server
Language Modules – Connectivity
Shared
Sybase Software Asset Management
SySAM Network License Server
SySAM Agent Plugin
SySAM Network License Utilities
Replication Monitoring Services
Replication Server Administration Tools

Press ENTER to read the text [Type q to quit]

Sybase Central
Replication Manager Plugin

for a total size:

318.8 MB

Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1] 1

Installing Replication Server Software, Version 15.0. Please wait…

|———–|———–|———–|————|
0% 25% 50% 75% 100%
||||||||||||||||||||||||||||||||||||||||||||||||||

Creating uninstaller…

——————————————————————————-
The InstallShield Wizard has successfully installed Replication Server
Software, Version 15.0. Choose Next to continue the wizard.

Press 1 for Next, 3 to Cancel or 4 to Redisplay [1] 1

——————————————————————————-
Will licenses be obtained from the License Server?

1. Yes
2. No

Enter one of the options above : [1] 2

Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1] 1

Please remember to download and install the license file after this
installation.

[OK]

——————————————————————————-
Please configure the Sybase Software Asset Management (SySAM) email alert
mechanism. When configured, specified receipients will receive email
notifications about SySAM events that may need administrator attention.

Do you want to configure email alerts?

1. Yes
2. No

Enter one of the options above : [1] 2

Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1] 1

——————————————————————————-
Would you like to start a sample Replication Server?

[ ] 1 – Yes
[ ] 2 – No

To select an item enter its number, or 0 when you are finished: [0] 2

[ ] 1 – Yes
[X] 2 – No

To select an item enter its number, or 0 when you are finished: [0]

Press 1 for Next, 2 for Previous, 3 to Cancel or 4 to Redisplay [1] 1

——————————————————————————-
The installation was successful.

Please check regularly for updates at http://www.sybase.com/downloads.

If you have not done so, please go to https://sybase.subscribenet.com to
obtain the Sybase software licenses.

Press 3 to Finish or 4 to Redisplay [3] 3
[sybase@localhost Desktop]$