SAP RS - Sybase SSL error: Open Server error: Error: 16029, State: 0, Severity 20 -- 'Failed to start any network listeners'

Error:

F. 2020/08/02 08:25:22. FATAL ERROR #1030 GLOBAL RS(GLOBAL RS) - eneric/err/errhand.c(1262)

    Open Server error: Error: 16029, State: 0, Severity 20 -- 'Failed to start any network listeners'.

T. 2020/08/02 08:25:22. (-1): Exiting due to a fatal error.

Solution:

- see if the RS (or other service) run on the port that set to RS in the sql.ini/interfaces file:

sybase16@asazrlnsap16:/sybvol01/sap16rs/REP-16_0/install$ netstat -a | grep 11753

tcp        0      0 asazrlnsap16.inte:11753 0.0.0.0:*               LISTEN

- find the Process ID of the service:

sybase16@asazrlnsap16:~$ netstat -lntp | grep 11753

(Not all processes could be identified, non-owned process info

 will not be shown, you would have to be root to see it all.)

tcp        0      0 10.1.1.4:11753          0.0.0.0:*               LISTEN      61270/repserver

- kill the service:

sybase16@asazrlnsap16:~$ kill 61270

- check that the services were killed:

sybase16@asazrlnsap16:~$ ps -ef | grep 61270

sybase16  66053  65873  0 08:51 pts/2    00:00:00 grep --color=auto 61270

sybase16@asazrlnsap16:~$ netstat -lntp | grep 11753

(Not all processes could be identified, non-owned process info

 will not be shown, you would have to be root to see it all.)

Start RS

SAP ASE - Sybase SSL error: kernel ninit: bind, Address already in use

Error:
00:0002:00000:00009:2020/07/21 13:10:26.85 kernel  ninit: bind, Address already in use
00:0002:00000:00009:2020/07/21 13:10:26.85 kernel  Cannot allocate resources for listener with protocol ssltcp, host asazrlnsap16, port 5000.

Solution:
Delete duplicate definitions for the server with the same port (edit the ini file).

SAP ASE - Sybase SSL error: Configuration parameter 'enable ssl' can not be enabled without license 'ASE_ASM'.

Error:

Configuration parameter 'enable ssl' can not be enabled without license 'ASE_ASM'.

(from the error log file):

Solution:

Create a license of type "SAP Sybase ASE Enterprise Edition, Security & Direct".

1. Go to Launchpad: https://launchpad.support.sap.com/
2. Click tile "License keys"
3. Click your current server id, i.e. L16
4. Right bottom click "Edit"
5. The info should already there, so "Continue"
6. In this page search "security"
From my side I see below, but you might have different license number, for an example:
Item Description
7011762 SAP Sybase ASE Enterprise Edition Security & Directo
 
-> If you can find such "security" license, then click the "Generate" and step by step fill out the system info, to download.
-> If you can NOT find such license, call SAP hotline(560499) for help, ask them to help involve the SAP sales for license purchase.
 
7. Put the downloaded license in folder:
/sybvol01/sap16/SYSAM-2_0/licenses/
8. Restart ASE server.
9. Check ASE error log see if SSL is enabled, if still license error raised please send me back the complete ASE error log.

SSL Configuration - SAP RS (Sybase RS)

How to setup self-signed SSL with SAP RS

On the server-side:

1. Create an ASE certificate.
2. Enable SSL in ASE (if required).
3. Edit the interfaces/sql.ini file - add SSL definition.
4. Enable SSL in RS.
5. Restart RS.

A detailed description of the process to activate SSL for ASE

On the server-side:

If required: Installing OpenSSL on the machine.

1. Create an ASE certificate

1-7. Steps 1-7 like SSL for ASE.

If already done for your server - skip it. 
The certificates for the ASE on the server can be used also by the RS on this server.

8. Create/Copy the crt file to RS certificates folder
cd /sybvol01/sap16/OCS-16_0/bin/
cat ASAZRLNSAP16.public ASAZRLNSAP16.key > /sybvol01/sap16rs/REP-16_0/certificates/ASAZRLNSAP16.crt
File created: /sybvol01/sap16rs/REP-16_0/certificates/ASAZRLNSAP16.crt

9. Create  client certificate

9.a create ASAZRLNSAP16.txt in the RS certificates folder
cp root.crt /sybvol01/sap16rs/REP-16_0/certificates/ASAZRLNSAP16.txt

9.b add certificates content to the exists trusted.txt in the RS config folder (.../sap16rs/config/trusted.txt)

9.c copy more files to the RS certificates folder:
cp ASAZRLNSAP16.csr /sybvol01/sap16rs/REP-16_0/certificates/ASAZRLNSAP16.csr
cp ASAZRLNSAP16.key /sybvol01/sap16rs/REP-16_0/certificates/ASAZRLNSAP16.key
cp ASAZRLNSAP16.public /sybvol01/sap16rs/REP-16_0/certificates/ASAZRLNSAP16.public
cp root.crt /sybvol01/sap16rs/REP-16_0/certificates/root.crt
cp root.csr /sybvol01/sap16rs/REP-16_0/certificates/root.csr
cp root.key /sybvol01/sap16rs/REP-16_0/certificates/root.key

2. Enable SSL in ASE  (if required)

10. Enable ssl in ASE
→ if already done for the ASE (step 10) - skip it.
sp_configure "enable ssl", 1

11. Add ssl certificate into ASE (already done for ASE, step 11)

3. Edit the interfaces/sql.ini file - add SSL definition

12. Edit the interfaces/sql.ini file to create an ssl port:
ASAZRLNSAP16
    master tcp ether ASAZRLNSAP16 5000 ssl="CN=ASAZRLNSAP16"
    query tcp ether ASAZRLNSAP16 5000 ssl="CN=ASAZRLNSAP16"
 
RSFOG2 
   master tcp ether ASAZRLNSAP16 11753 ssl="CN=ASAZRLNSAP16"
   query tcp ether ASAZRLNSAP16 11753 ssl="CN=ASAZRLNSAP16"

4. Enable SSL in RS

13. Grant execute permissions for sp_serverinfo (CR# 814027)
use sybsystemprocs
go
grant execute on sp_serverinfo to public
go

14. Enable SSL on RS
1> configure replication server set use_ssl to 'on'
2> go
Config parameter 'use_ssl' is modified. This change will not take effect until the Replication Server is restarted.

5. Restart and do checks

15. Restart RS
Stop RS:
1> shutdown
2> go
Start RS:
cd /sybvol01/sap16rs/REP-16_0/install/
startserver -f RUN_RSFOG2

• Windows: stop and start the service in Services.

16. Check that SSL is enabled:

1> use RSFOG2_RSSD
2> go
1> select * from rs_config where optionname like "%ssl%"
2> go
optionname                     objid              charvalue         status comments                                                                                                                                             
 ------------------------------ ------------------ -------------
 ssl_protocol                   0x0000000000000000 TLSv1        0 Indicated the SSL protocol value of Replication Server.                                                                                                                     
 use_ssl                        0x0000000000000000 on   
should be:
1. ssl_protocol is not null
2. ssl_protocol is on

1> select @@ssl_ciphersuite
2> go
 ------------------------------
 TLS_RSA_WITH_AES_256_CBC_SHA

SSL Configuration - SAP ASE (Sybase)

How to setup self-signed SSL with ASE

On the server-side:

1. Create an ASE certificate.
2. Enable SSL in ASE.
3. Edit the interfaces/sql.ini file - add SSL definition.
4. Restart ASE.

On the clients:

1. Copy the certificate files to C:\SAP\ini in the client.
2. Edit sql.ini / interface file - add SSL definition.
3. Restart ASE

The process based on https://launchpad.support.sap.com/#/notes/0001899365

A detailed description of the process to activate SSL for ASE

On the server-side:

If required: Installing OpenSSL on the machine.

1. Create an ASE certificate

1. cd /sybvol01/sap16/OCS-16_0/bin/

2. Create root certificate
openssl genrsa -passout pass:dba4ever -out root.key 4096
output:
Generating RSA private key, 4096 bit long modulus (2 primes)
.............................................................................++++
.................................++++
e is 65537 (0x010001)
File created: root.key

3. Create root certificate request to be signed:
openssl req -new -key root.key -passin pass:dba4ever -out root.csr -subj "/C=XX/ST=XX/L=city/O=Org/CN=root"
File created root.csr

4. Self-Sign root certificate
openssl x509 -req -days 3650 -in root.csr -signkey root.key -passin pass:dba4ever -out root.crt
    output:
    Signature ok
    subject=C = XX, ST = XX, L = city, O = Org, CN = root
    Getting Private key

File created: root.crt

5. Create ASE private key (Note: Add your servername/"select @@servername" in for ASEname):
openssl genrsa -des3 -passout pass:dba4ever -out ASAZRLNSAP16.key 2048
File created: ASAZRLNSAP16.key

6. Create ASE certificate request to be signed:
openssl req -new -key ASAZRLNSAP16.key -passin pass:dba4ever -out ASAZRLNSAP16.csr -subj "/C=XX/ST=XX/L=city/O=Orig/CN=ASAZRLNSAP16"
File created: ASAZRLNSAP16.csr

7. Sign ASE request with root:
openssl x509 -req -days 3650 -in ASAZRLNSAP16.csr -CA root.crt -CAkey root.key -passin pass:dba4ever -set_serial 1 -out ASAZRLNSAP16.public
    output:
    Signature ok
    subject=C = XX, ST = XX, L = city, O = Orig, CN = ASAZRLNSAP16
    Getting CA Private Key
File created: ASAZRLNSAP16.public

8. Combine ASE certificates together:
cat ASAZRLNSAP16.public ASAZRLNSAP16.key > $SYBASE/$SYBASE_ASE/certificates/ASAZRLNSAP16.crt
File created: ASAZRLNSAP16.crt

9. Create ASE client certificate.
This is the root public certificate created in step 3. This is for all client side connections.

cp root.crt $SYBASE/$SYBASE_ASE/certificates/ASAZRLNSAP16.txt

cp root.crt $SYBASE/config/trusted.txt
File created:
    $SYBASE/$SYBASE_ASE/certificates/ASAZRLNSAP16.txt
    $SYBASE/config/trusted.txt

content of file:
-----BEGIN CERTIFICATE-----
MIIFEzCCAvsCFCi6ELH5OlZt96P56Zkz3r+DUeWJMA0GCSqGSIb3DQEBCwUAMEYx
CzAJBgNVBAYTAlhYMQswCQYDVQQIDAJYWDENMAsGA1UEBwwEY2l0eTEMMAoGA1UE
CgwDT3JnMQ0wCwYDVQQDDARyb290MB4XDTIwMDYxNjE0NTEwNFoXDTMwMDYxNDE0
NTEwNFowRjELMAkGA1UEBhMCWFgxCzAJBgNVBAgMAlhYMQ0wCwYDVQQHDARjaXR5
MQwwCgYDVQQKDANPcmcxDTALBgNVBAMMBHJvb3QwggIiMA0GCSqGSIb3DQEBAQUA
A4ICDwAwggIKAoICAQDD7h2H+PPYXlt+E9mvxFTnayo2S/2+TNLwpXYlrnasbbzG
hT9c2y6FD/NMo7C7ncrLHr+BUM5cBNRfDyijeb8Tm1ASQiSCj/CJQYDbxJ/VBt4N
aESN8POOMbNqQezZISWk8dPX4cHOh8d5oZqAUo/D+Y69VScAOKIGq1PmT1a/StXz
TIiw+xmibmvBClRC9oH9vujVFDTOG8fwUm38yaV9iz30upriPL9Ly17/klzauaRY
yJ4N6HRC79jZwgbMTbHP3j5fPsETstCD9S7LZxUNmlyXW14bndU/EYjeKGHUzvtr
7mIINQTc7u1/dRZGQVGl7NtaP5t0MIH7I5e1H8y56wQHd8umaE+Uwv+P9Mn2J9bb
zRIVcK/6vVz5KdU3XahgEr8U0mbA93Kn/8Xijx1wbzTFYcvqLpNd2MGSWEjy08N9
kxN13tC/aTm8vzkygnUvrfzjeYXXsji8fRDMYCanYzutA2FWpm+L0im8ob8uqwoL
egB+ZGOkuIwKrqP0gPJXr2GFTqgRyOWbfTHC31mv0Qo3Rg5bfsPXUkIbSBXUrz6i
pKYsMjFSi3npmuw/CCli2RIQMtCMMv8kn2Q0WqlWvePXNBcEiwVs1f/8J3h/cXAN
fjU2iaqT2aANc40hrmmJPVR/L5AladwGRESzphFEacVoKz/c+RZP/0wm2EQzMQID
AQABMA0GCSqGSIb3DQEBCwUAA4ICAQATVj1EazHcTonBVcthL2mgQaluSJaMYnxi
tEy8UKWBJj4Bu+THed7FCwJy9MDw5ReZgB1yh+PQvclg19LbGrVX+x9W/3cuQ9Cy
A7PMl9r3B5WnNwzCvxlltDenzzszcOGXSP+oUAql8nYp6wM9FbLnjW3aZ036d5+V
QEx8xsbEZ+9Qwb7SFWkIJky8sEVjueeXb9u0WP84bYrSp+T+YOESWUwZvwvJGCwc
YsRM1nCpLgFQlQNZjjtPcx/lKSB0+gKGmlrePoyaa8MYlswRanzYdnk487dAk3u9
X9JYO2yyNL7drGBf0VVnT8b7X2nOlDnw0wYs4mgIuLwBWhSbYpNHYcPFCxXjEoh1
jjdGnZGsQSGMoPKfxpNq8sZUyhzEroJHpV7OxLikvpRSz/IUhLpyNWoHVKaAehkJ
Iwc9pDja4Fz2ArO5l5g7P60rwq2cCTS0zKXrRqaPGm11WIX6ovCpobOZwBDvRGiv
6KiTF2y5Ib9xL+9Q6E8R0kPWOsPzOLaeyN5pz4IHLbRPppCIAurWxD4bozJ5CWVP
W9l+LF3IIxoLLtK2+8lxHiqMiV9o9PJTBd0I83YjPJ+QrB6ARMIg2fJWAaVQH8CN
Mdlw0aVwiVo9X06Lb6FW4AC9a2b5iBVF+ncUPeVIZYyrHNmoA65sEy/hb2rwdS+w
FoueokT6Zg==
-----END CERTIFICATE-----

2. Enable SSL in ASE

10. Enable ssl in ASE:
sp_configure "enable ssl", 1;

11. Add ssl certificate into ASE:
Note: Use fully qualified path

sp_ssladmin addcert, "/sybvol01/sap16/ASE-16_0/certificates/ASAZRLNSAP16.crt", "dba4ever"

3. Edit the interfaces/sql.ini file - add SSL definition

12. Edit the interfaces/sql.ini file to create an ssl port:
   interfaces (Unix)
   -------------
    ASEname
           master tcp ether myhost myport  ssl
           query tcp ether myhost myport ssl        
    sql.ini  (Windows)
    -------
    [ASEname]
    master=tcp,myhost,myport,ssl
    query=tcp,myhost,myport,ssl

for example:
ASAZRLNSAP16
  master tcp ether asazrlnsap16 5000 ssl="CN=ASAZRLNSAP16"
  query tcp ether asazrlnsap16 5000 ssl="CN=ASAZRLNSAP16"

4. Restart and do checks

13. Restart your ASE.

14. Checks

14.1 Check the log to make sure everything loaded:
16.0:
kernel  Common Crypto Library SSL symbols loaded.
kernel  Common Crypto Library SSL startup succeeded.
...
kernel network name host, interface IPv4, address ipaddress, type ssltcp, port port, filter ssl
15.7:
kernel Certificate load from file `$SYBASE/$SYBASE_ASE/certificates/ASEname.crt`: succeeded.
kernel Trusted root certificates loaded from file '$SYBASE/$SYBASE_ASE/certificates/ASEname.txt': succeeded.
For all versions:
kernel network name host, interface IPv4, address ipaddress, type ssltcp, port port, filter ssl

14.2 sp_ssladmin lscert
1> sp_ssladmin lscert
2> go
 certificate_path
 ---------------------------------------------------------
 /sybvol01/sap16/ASE-16_0/certificates/ASAZRLNSAP16.crt 

14.3 Check that "select @@ssl_ciphersuite" return value
1> select @@ssl_ciphersuite
2> go
 ----------------------------------
 TLS_RSA_WITH_AES_256_CBC_SHA 


On the clients:

1. Copy the certificate files to C:\SAP\ini in the client.

1. copy ASAZRLNSAP16.crt and ASAZRLNSAP16.txt to C:\SAP\ini in the client.

2. Edit sql.ini / interface file - add SSL definition.

2. Edit sql.ini / interface file - add SSL definition

3. Restart ASE

3. Restart ASE

SAP ASE (sybase) historical monitoring tables

Adaptive Server maintains context information for each client connection that accesses the historical tables, and on each successive query on the table returns only rows that the client has not previously received (read here).

The historical monitoring tables are:

• monErrorLog
• monDeadLock
• monSysStatement
• note: When executing a procedure from a procedure, monSysStatement return a record both to the two procedures.
• monSysSQLText
• monSysPlanText

The maximum number of statement statistics returned can be tuned with statement pipe max messages.

The historical monitoring tables require the enable monitoring, statement statistics active, per object statistics active, statement pipe max messages, and statement pipe active configuration parameters to be enabled.

Each message stored adds one row to the monitoring table. Once all entries in the buffer have been used, new messages overwrite old messages in the buffers, so only the most recent messages are returned.

Filter the result set with a where clause filters from the "statement pipe max messages" number.

select SPID, ErrorMessage from master..monErrorLog

SPID      ErrorMessage

------    --------------------------------------

20        An error from SPID 20

21        An error from SPID 21

(2 rows affected)

 

-- ==========================

-- in a different session:

-- the filter return only one rows

select SPID, ErrorMessage from master..monErrorLog

where SPID=20

SPID ErrorMessage

------ --------------------------------------

20 An error from SPID 20

(1 row affected)

 

--And:

 

-- but the filtered rows was set as "returned" in the previous query and won't be return without the filter now:

select SPID, ErrorMessage from master..monErrorLog

where SPID=21

SPID ErrorMessage

------ --------------------------------------

(0 rows affected)

SAP RS - The transaction log in database RSSD is almost full

What happened?
Error when trying to start SAP Replication Server.

Error Message:
E. 2020/12/14 21:39:52. ERROR #11061 GLOBAL RS(GLOBAL RS) - generic\sts\stscol.c(2631)

Check the log for error messages from RSSD.
I. 2020/12/14 21:39:52. Message from server: Message: 7415, State 1, Severity 10 -- 'The transaction log in database RS1_RSSD is almost full. Your transaction is being suspended until space is made available in the log.

Reason:
Cause of the error: The transaction lof of the RSSD database is (almost) full.

Solution:

use master

go

sp_helpdb RS1_RSSD  --a check

sp_helpdevice rssd_log --a check

disk resize name = "rssd_log", size = "1500M"

go

alter database RS1_RSSD log on rssd_log = "1500M"

go

SAP RS - Create a new Replication

Before declaring a Replication

We should have:

• Primary database and Replicate database/s.
• Instances were set in sql.ini.
• RSSD database.

Create a primary database

use master

go

 

-- Create devices

disk init name="RepTestPrimaryDdevice", physname="/sybvol01/sap16/data/RepTestPrimaryDdevice1.dat", size="100M"

GO

 

disk init name="RepTestPrimaryDdeviceLog", physname="/sybvol01/sap16/data/RepTestPrimaryDdeviceLog1.dat", size="100M"

GO

 

-- Create a Database

create database RepTestPrimary on RepTestPrimaryDdevice='100M' log on RepTestPrimaryDdeviceLog='100M'

GO

 

use RepTestPrimary

go

-- Create a Segment

sp_addsegment RepTestPrimarySeg, RepTestPrimary, RepTestPrimaryDdevice

GO

Create a replicate database

use master

go

 

-- Create devices

disk init name="RepTestReplicateDdevice", physname="/sybvol01/sap16/data/RepTestReplicateDdevice1.dat", size="100M"

GO

 

disk init name="RepTestReplicateDdeviceLog", physname="/sybvol01/sap16/data/RepTestReplicateDdeviceLog1.dat", size="100M"

GO

 

-- Create a Database

create database RepTestReplicate on RepTestReplicateDdevice='100M' log on RepTestReplicateDdeviceLog='100M'

GO

 

use RepTestReplicate

go

-- Create a Segment

sp_addsegment RepTestReplicateSeg, RepTestReplicate, RepTestReplicateDdevice

GO

Create a replicated table

use RepTestPrimary

go

 

create table dbo.FirstTable (ID numeric(10, 0) not null, IntColumn int null, StrColumn varchar(20) null)

go

alter table dbo.FirstTable add constraint pkFirstTable primary key (ID)

go

 

-- =============================================================

use RepTestReplicate

go

 

create table dbo.FirstTable (ID numeric(10, 0) not null, IntColumn int null, StrColumn varchar(20) null)

go

alter table dbo.FirstTable add constraint pkFirstTable primary key (ID)

go

• The replicated table must have a primary key!

Create connections to the primary and replicated server

cd /sybvol01/sap16rs/REP-16_0/install/

./rs_init

Add a database to the replication system

Choose: "2. Configure a Server product"

Choose: "1. Replication Server"

Choose: "2. Add a database to the replication system"

In windows:

Replication Server Information

Choose: "1. Replication Server Information"

Choose: "1. Replication Server Name"Choose: "RS SA password"

Ctrl-a Accept and Continue

In windows:

Database Information

Add the primary database

Choose: "2. Database Information"

Choose: "1. SQL Server name"
For the primary database: set "5. Will the database be replicated" to "yes".
Ctrl-a Accept and Continue

Choose: "3. SA password"

Choose: "4. Database name"

Choose: "7. Maintenance password"

Choose: "3. Database Replication Agent"

Choose: "2. RS password"

Change RS user to sa.

Ctrl-a Accept and Continue

Ctrl-a Accept and Continue, Approve to run the task.

Execute the Replication Server tasks now? y

Running task: check the SQL Server.

Task succeeded: check the SQL Server.

Running task: verify users and their passwords.

Verified that 'sa' can log into Replication Server 'RSFOG2'.

Verified that 'sa' can log into Replication Server 'RSFOG2'.

Task succeeded: verify users and their passwords.

Running task: check the database.

Verified that database 'RepL16TestPrimary' exists.

Verified that SQL Server 'SERVERNAME' supports replication.

Added maintenance user login 'RepL16TestPrimary_maint' to database 'RepL16TestP

rimary'.

Verified that maintenance user 'RepL16TestPrimary_maint' can log into SQL Serve

r 'SERVERNAME'.

Task succeeded: check the database.

Running task: configure database for primary data.

Loading script 'rs_install_primary.sql' into database 'RepL16TestPrimary'.

.....Done

Loaded script 'rs_install_primary.sql' successfully.

Done

Granting permissions on the lastcommit functions and rs_marker.

Granting permissions on the lastcommit functions.

Granted maintenance user permissions on the lastcommit functions and rs_marker.

Granted replication role to maintenance user

Task succeeded: configure database for primary data.

Running task: configure the Replication Agent.

Task succeeded: configure the Replication Agent.

Running task: set connection to the database.

Adding database 'RepL16TestPrimary' to the replication system.

Successfully executed 'create connection'. Database 'RepL16TestPrimary' is now

managed by Replication Server 'RSFOG2'.

Task succeeded: set connection to the database.

Running task: start the Replication Agent.

Task succeeded: start the Replication Agent.

Configuration completed successfully.

Press <return> to continue.

Add a replicate database

Do the same as for the primary database, with the following changes:

1. For the replicate databases: set "5. Will the database be replicated" to "no".
2. "Database Replication Agent" definition is not required.

Ctrl-a Accept and Continue, Approve to run the task.

Execute the Replication Server tasks now? y

Running task: check the SQL Server.

Task succeeded: check the SQL Server.

Running task: verify users and their passwords.

Verified that 'sa' can log into Replication Server 'RSFOG2'.

Task succeeded: verify users and their passwords.

Running task: check the database.

Verified that database 'RepL16TestReplicate' exists.

Added maintenance user login 'RepL16TestReplicate_maint' to database 'RepL16Tes

tReplicate'.

Verified that maintenance user 'RepL16TestReplicate_maint' can log into SQL Ser

ver 'SERVERNAME'.

Task succeeded: check the database.

Running task: configure database for replicate data.

Loading script 'rs_install_replicate.sql' into database 'RepL16TestReplicate'.

...Done

Loaded script 'rs_install_replicate.sql' successfully.

Done

Granting permissions on the lastcommit functions.

Granted maintenance user permissions on the lastcommit functions.

Granted replication role to maintenance user

Task succeeded: configure database for replicate data.

Running task: set connection to the database.

Adding database 'RepL16TestReplicate' to the replication system.

Successfully executed 'create connection'. Database 'RepL16TestReplicate' is no

w managed by Replication Server 'RSFOG2'.

Task succeeded: set connection to the database.

Configuration completed successfully.

Press <return> to continue.

In windows:

Create replication definition, publication and subscription

Create a replication definition

Replication definition is defined at the primary database.

Connect to the RS:

isql64 -S RSFOG2 -Usa -Ppassword -w2048

create replication definition RepL16TestDef

with primary at SERVERNAME.RepL16TestPrimary

with all tables named 'FirstTable'

(ID numeric,

IntColumn int,

StrColumn varchar(20))

primary key(ID)

replicate minimal columns

go

Expected output:

Replication definition 'RepL16TestDef' is created.

Create a publication

Publication is defined at the primary database.

Connect to the RS:

isql64 -S RSFOG2 -Usa -Ppassword -w2048

create publication RepL16TestPub with primary at SERVERNAME.RepL16TestPrimary

go

Expected output:

-- Publication 'RepL16TestPub' for SERVERNAME.RepL16TestPrimary is created.

Create a subscription

Subscription is defined at the replicate database.

Connect to the RS:

isql64 -S RSFOG2 -Usa -Ppassword -w2048

create subscription RepL16TestSub for RepL16TestDef with replicate at SERVERNAME.RepL16TestReplicate

go

Expected output:

Subscription 'RepL16TestSub' is in the process of being created.

check subscription RepL16TestSub for RepL16TestDef with replicate at SERVERNAME.RepL16TestReplicate

go

Expected output:

Subscription RepL16TestSub is VALID at the replicate.

Subscription RepL16TestSub is VALID at the primary.

Validation and checks for the connections and the definitions

Connect to the RS:

isql64 -S RSFOG2 -Usa -Ppassword -w2048

1> admin who

2> go

Spid Name State Info

---- --------------- -------------------- ------------------------------------------------------------

...

304 DSI EXEC Awaiting Command 109(1) SERVERNAME.RepL16TestPrimary

299 DSI Awaiting Message 109 SERVERNAME.RepL16TestPrimary

301 DIST Awaiting Wakeup 109 SERVERNAME.RepL16TestPrimary

303 SQT Awaiting Wakeup 109:1 DIST SERVERNAME.RepL16TestPrimary

300 SQM Awaiting Message 109:1 SERVERNAME.RepL16TestPrimary

298 SQM Awaiting Message 109:0 SERVERNAME.RepL16TestPrimary

302 REP AGENT Awaiting Command SERVERNAME.RepL16TestPrimary

311 DSI EXEC Awaiting Command 110(1) SERVERNAME.RepL16TestReplicate

310 DSI Awaiting Message 110 SERVERNAME.RepL16TestReplicate

309 SQM Awaiting Message 110:0 SERVERNAME.RepL16TestReplicate

...

Connect to the RSSD in the ASE:

isql64 -S SERVERNAME -Usa -Ppassword -w2048 -X

1> use RSFOG2_RSSD

2> go

1> select * from rs_databases

2> go

dsname dbname dbid dist_status src_status ....

----------------- -------------------- ----------- ----------- ----------- ....

...

SERVERNAME RepL16TestPrimary 109 1 1 ....

SERVERNAME RepL16TestReplicate 110 1 0 ....

...

(8 rows affected)

• rs_helpdb can also be good: exec rs_helpdb.

Add database and table to replication

Connect to the primary database in the ASE:

isql64 -S SERVERNAME -Usa -Ppassword -w2048 -X

1> USE RepL16TestPrimary

2> go

1> -- displays the current replication status of a table:

2> sp_setreptable FirstTable

3> go

The replication status for 'FirstTable' is currently false, owner_off, primary key = 'pkFirstTable'.

(return status = 0)

1> -- add table to replication

2> sp_setreptable FirstTable, true

3> go

The replication status for 'FirstTable' is set to true, owner_off.

(return status = 0)

1> -- displays the current replication status of a table:

2> sp_setreptable FirstTable

3> go

The replication status for 'FirstTable' is currently true, owner_off, primary key = 'pkFirstTable'.

(return status = 0)

1> -- check replication status for database:

2> sp_reptostandby RepL16TestPrimary

3> go

The replication status for database 'RepL16TestPrimary' is 'NONE'.

The replication mode for database 'RepL16TestPrimary' is 'off'.

(return status = 0)

1> -- activate replication status for database

2> sp_reptostandby RepL16TestPrimary, 'all'

3> go

Warning: The execution of the stored procedure sp_reptostandby will take time to process all the tables and all the text/image columns in database 'RepL16TestPrimary'.

The replication status for database 'RepL16TestPrimary' has been set to 'ALL'.

(return status = 0)

1> -- check replication status for database

2> sp_reptostandby RepL16TestPrimary

3> go

The replication status for database 'RepL16TestPrimary' is 'ALL'.

The replication mode for database 'RepL16TestPrimary' is 'off'.

(return status = 0)

Grant permissions to maint user on replicated DB

Connect to the replicate database in the ASE:

isql64 -S SERVERNAME -Usa -Ppassword -w2048 -X

use RepL16TestReplicate

go

GRANT INSERT ON dbo.FirstTable TO RepL16TestReplicate_maint

go

GRANT UPDATE ON dbo.FirstTable TO RepL16TestReplicate_maint

go

GRANT DELETE ON dbo.FirstTable TO RepL16TestReplicate_maint

go

Activate Rep Agent

-- Connect to the primary database and to the RSSD in the ASE:

isql64 -S SERVERNAME -Usa -Ppassword -w2048 -X

1> use RepL16TestPrimary

2> go

1> -- let RepAgent sends changes to the replicate database

2> sp_config_rep_agent RepL16TestPrimary, send_warm_standby_xacts, true

3> go

Parameter_Name Default_Value Config_Value Run_Value

----------------------- ------------- ------------ ---------

send warm standby xacts false true false

(1 row affected)

Replication Agent configuration changed for database 'RepL16TestPrimary'. The changes will take effect the next time the Replication Agent thread is started.

(return status = 0)

1> -- config connect dataserver to the ASE name

2> sp_config_rep_agent RepL16TestPrimary, 'connect dataserver', 'SERVERNAME'

3> go

Parameter_Name Default_Value Config_Value Run_Value

------------------ ------------- ------------ ------------

connect dataserver SERVERNAME SERVERNAME SERVERNAME 

(1 row affected)

Replication Agent configuration changed for database 'RepL16TestPrimary'. The changes will take effect the next time the Replication Agent thread is started.

(return status = 0)

1> sp_config_rep_agent RepL16TestPrimary, 'enable', 'RSFOG2', 'sa', 'password'

2> go

Msg 18382, Level 16, State 1:

Server 'SERVERNAME', Procedure 'sp_config_rep_agent', Line 664:

Database 'RepL16TestPrimary' is already configured to use Replication Agent. Request to enable Replication Agent has been ignored.

(return status = 1)

→ Bebause it already done in rs_init. No problem. 

1> -- stop RepAgent

2> sp_stop_rep_agent RepL16TestPrimary

3> go

The Replication Agent thread for database 'RepL16TestPrimary' is being stopped.

(return status = 0)

1> use RSFOG2_RSSD

2> go

1> rs_zeroltm 'SERVERNAME', 'RepL16TestPrimary'

2> go

Locater has been reset to zero.

(return status = 0)

1> use RepL16TestPrimary

2> go

1> dbcc settrunc(ltm, valid)

2> go

secondary trunc page secondary trunc state dbrepstat generation id database id database name ltl version

-------------------- --------------------- --------- ------------- ----------- ------------------------------ -----------

25757 1 175 0 14 RepL16TestPrimary 760

1> -- truncate the log

2> dump tran RepL16TestPrimary with no_log

3> go

1> -- grant permissions

2> grant role replication_role to sa

3> go

1>

2> -- start RepAgent

3> sp_start_rep_agent RepL16TestPrimary

4> go

Replication Agent thread is started for database 'RepL16TestPrimary'.

(return status = 0)

View information about all definitions and declarations

In the primary database:

sp_config_rep_agent RepL16TestPrimary

go

sp_help_rep_agent "RepL16TestPrimary"

go

sp_displaylogin sa

go

In the Replication Server:

admin who

go

admin who_is_down

go

In RSSD:

rs_helpdbrep DETestDB_repdef, win16, DETestDBPrimary

go

rs_helpcheckrepdef

go

rs_helprep DETestDB_repdef

go

rs_helpdbsub DETestDB_pub_subscript, ubuntu16, DETestDB

go

select * from rs_subscriptions A

go

select * from rs_dbreps B

go

select * from rs_sites --B

go

select * from rs_databases --C

go

Check the replication

Connect to the primary database and to the replicate database in the ASE:

isql64 -S SERVERNAME -Usa -Ppassword -w2048 -X

1. Insert data to the primary table.
2. Check if it was replicated in the replicate database.

1> use RepL16TestPrimary

2> go

1> INSERT INTO dbo.FirstTable ( ID, IntColumn, StrColumn ) VALUES ( 1, 1, 'Str 1' )

2> go

(1 row affected)

1> select * from dbo.FirstTable

2> go

ID IntColumn StrColumn

------------- ----------- --------------------

1 1 Str 1

(1 row affected)

1> use RepL16TestReplicate

2> go

1> select * from dbo.FirstTable

2> go

ID IntColumn StrColumn

------------- ----------- --------------------

1 1 Str 1

(1 row affected)

SAP (sybase) ASE - The maximum number of configured devices has been reached

The case:

Creation of a new device.

Error message:

The maximum number <X> of configured devices has already been reached. Please reconfigure 'number of devices' to a larger value and retry disk initialization.

1> disk init name="DdeviceNameLog", physname="/sybvol01/sap16/data/DdeviceNameLog1.dat", size="100M"

2> go

Msg 5162, Level 16, State 1:

Server 'SRSSERVER', Line 1:

The maximum number 20 of configured devices has already been reached. Please reconfigure 'number of devices' to a larger value and retry disk initialization.

Solution:

sp_configure 'number of devices', 30

go