How to check Sybase Replication Queue sizes!!

Space monitoring or Space used in repserver queues is administered in a similar way
to ASE transaction logs, But all the completed transactions will be removed automatically from stable queues. We can also find the latency or Replication performance based on the Queues size. rs_ticket is the best way to find/analyze latency but knowing the queue size also important in replication system for quick understandings.

Replication stable device – Use “admin disk_space” to monitor all stable devices.

admin disk_space

Displays use of each disk partition accessed by the Replication Server.

isql –U<user> -S<RepSrv> -P<pwd>

admin disk_space
go
 

Partition	Logical	Part.Id	Total Segs	Used Segs	State
/dev/vx/rdsk/sybase2/raw2g14	SQM14	112	2000	10	ON-LINE//
/dev/vx/rdsk/sybase2/raw2g13	SQM13	111	2000	6	ON-LINE//

Total Segs -  Total number of 1MB segments on a partition.

 We can also query the RepServer's RSSD to get full info:

select 'ASE/DB'=convert(char(40),d.dsname+'.'+d.dbname), 'Q_Type'=case q_type when 0 then 'OBQ' else 'IBQ' end, 'Size_Mb'=count(used_flag)

from rs_segments s, rs_databases d
where used_flag = 1
and s.q_number = d.dbid
group by d.dsname+'.'+d.dbname, q_type, used_flag

UNION

select 'ASE/DB'=convert(char(40),name), 'Q_Type'='RSI', 'Size_Mb'=count(used_flag)

from rs_segments s, rs_sites t
where used_flag = 1
and s.q_number = t.id
group by name, q_type, used_flag
order by 'DB/Site', Q_Type

ASE/DB                                Q_Type   Size_Mb    

-------                               ------   -----------
ASE1.bobj                                OBQ              1
ASE1.livedr                              OBQ              1
ASE1.rssd_drp                            IBQ              1
ASE1.rssd_drp                            OBQ              1

We can also try using admin who, sqm to find the queue size.

1> admin who, sqm

2> go

 Spid State                Info                                     Duplicates  Writes      Reads       Bytes       B Writes    B Filled    B Reads     B Cache     Save_Int:Seg

         First Seg.Block                 Last Seg.Block                  Next Read                       Readers     Truncs

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

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

   48 Awaiting Message     133:0 ASE.user_store                             0           0           0           0           0           0           0           0 0:0

        0.1                             0.0                             0.1.0                                     0           1

   47 Awaiting Message     120:0 UTF_ASE.Bnk                                6    28140377     3305582 -1620639873      623325      386004       71758       71006 0:52468

         52468.1                         52468.1                         52468.2.0                                 1          

11 Awaiting Message 105:1 FSSOLTP.TMW 1662 41058993 43665390 -1496848285 7224036 6684446 7928857 6162835 0:196052 196052.16 196052.25 196052.26.0 1 1

The last segment and block values move as new data stored in the queue. First, the block advances until the segment is full (at block # 64); then, a new segment is assigned, and this segment may get the next segment number or not.

The first segment and block contain the oldest data to be handled by RepServer. It is a concept similar to the truncation point in ASE's syslogs. As data are used, oldest blocks and segments are no longer needed and the first segment-block marker advances.

The bold shown a queue with 10 blocks used (from 196052.16 to 196052.25).

Sybase Replication Quick HELP!!

~

Replication Server is Sybase's multiplatform system for distributing data over networks and maintaining server synchronization. Replication Server increases the flexibility and lowers the costs of managing multiple data management platforms. Sybase RepServer can replicate data across Sybase databases to Sybase/MS SQL databases, Sybase database tables to Any RDBMS scema tables - vice versa, and also from Any RDBMS schema tables to Any RDBMS schema tables.

Replication can improve your data infrastructure:

· High availability – Sales associates, branch offices, chain retail locations and more are able to exchange and share data from point-of-sale applications through regional and district offices to the corporate level and back again.

· Highly distributed – Consider sales and service companies where users are disconnected during the day but need to update orders/inventories and other information automatically after normal working hours. Advantage Replication provides an easy solution when data must be highly distributed.

· Offline Reporting or Data Warehousing – Moving data from production servers to reporting servers removes the processing loads of complex reports or data mining operations from production environments. Increase the speed and efficiencies of reporting without impacting day-to-day operations.

It's not so easy to start with Replication as it involves multiple Servers/Databases, but little Querying would give you overview and once you got the flow/architecture you can tune in.

You should know the ID Server (The 1st Replication Server installed on the System) and the ASE of the RepServer RSSD to start understanding the replication topology. you can use following T-SQL to start with. Many DBAs have doubts to know what kind of replication is in their environment (like W/S, MSA or table).

Query the RSSD to get info:

To find all the Replication Server names:

select id, name from rs_sites

To find all the warm standby databases in the replication including the Server name:

select distinct dsname, dbname from rs_databases

where ltype='L'

To find all the table-level/MSA databases in the replication including the Server name:

select distinct dsname, dbname from rs_databases

where ltype='P'

To find all the database repdefs list:

select dsname, dbname,dbrepname from rs_dbreps rd, rs_databases rs

where rs.dbid=rd.dbid

order by dsname, dbname

To find all the table repdefs list:

select dsname, dbname, objname, phys_tablename,deliver_as_name from rs_objects ro, rs_databases rd

where ro.dbid=rd.dbid

order by dsname, dbname

To find all the subscriptions list:

select dsname, dbname,subname from rs_subscriptions rs, rs_databases rd

where rs.dbid=rd.dbid

order by dsname, dbname

I hope with this information you can identify the replication flow and also type. Keep replicating this stuff too...

Thank you all..

Your Qs and Tips are highly appreciated and welcome...

~

Sybase Replication Server 15 Overview

Sybase Replication Server 15 Overview

Sybase Replication Server is database replication software that moves and synchronizes data across the Servers either From Sybase ASE to ASE or to/from Other RDBMS Servers. The data moves asynchronously. The time it takes to reach the destination or to replicate data depends on the size of the transaction, level of activity in that particular database/schema, the network performance, and last but not the least how your replication server is loaded etc., Usually, on a LAN, for small transactions, this is less than a second.

Replication Server 15.0 supports a number of new ASE 15.0 features:

ü Computed columns

ü Encrypted columns

ü Partitioned tables

ü New datatypes

ü Long identifiers

1)      Computed columns

Computed columns are columns that are defined by an expression, instead of a datatype

For example

create table sales

(order_num char(20), order_date date

order_day_of_week compute datename(dw, order_date))

ü  Replication Server can only replicate materialized computed columns.

·         A materialized computed column is stored in the table

·         Virtual computed columns are not replicated

2)      Encrypted columns

ü  ASE 15.0.1, 12.5.3a, and 12.5.4 support the definition of encrypted columns

For example:

create table cust_table

(cust_id char(15),

credit_card char(20) encrypt)

Replication Server 15.0 can replicate these encrypted columns.

3)      Partitioned tables

ü  ASE 15.0 introduced semantic data partitioning

·         Tables can be partitioned by lists or ranges of key values, or by a hashing algorithm

·         These partitioning methods are supported by Replication Server 15.0

·         In addition, truncate table has been extended to support partition-level truncation

4)      New datatypes

ü  Release 15.0 adds support for new datatypes:

·         Bigint – whole numbers between -263  and 263 –1

·         Unitext – variable-width, nullable Unicode datatype

5)      Long identifiers

ü  The limit of 30 bytes for selected database and replication object names (identifiers) is extended to 255 bytes for Replication Server version 15.0. Longer identifiers are supported for these objects:

·         Tables and columns

·         Stored procedures and parameters

·         Function strings

·         Replication definitions – including table replication definitions, function replication definitions, and database replication definitions

·         Publications and articles

Sybase ASE Performance Tuning précis...

Performance tuning is a continuous process in any database environments. Many organizations spend more time and resources on tuning the database than on the original development because of data growth and usage on top of a poor database design. 80% of performance issues can be avoided with a proper database design. In addition, developing Transact-SQL code is very different from developing front-end applications code and middle-tier components. Transact-SQL code developed by front-end or middle-tier experts can often be optimized depending on the transparency of the code. Finally, there is a lot of requirement and necessity for optimizing databases when the volume of operations grows and the application that worked great with 5-10 users and 100-200GB no longer lives up to tera bytes of data and 1000s of users' expectations.  Yes as all of us aware scalability is the root cause.

A common place where most people like to start tuning databases is tuning the Server configuration. Generally, Most developers that are unhappy with the application performance will demand adding more memory to the server and reconfiguring the dynamic configuration parameters. But, extending the memory or keep on changing configuration parameters will only help optimizing the performance up to a certain point. In other words, if you keep adding memory and do not tune the application or t-sql code in any other way, you will reach the point where additional memory or re-configuration produces marginal or no performance improvement. 

It is also important to realize that improvement in one area often means compromising others. If you can optimize 90% of the critical queries by slowing down the performance of other 10% it might be well worth of your time. Sometimes you can improve the performance of online transactions by increasing the response time with the expense of reducing concurrency or throughput. Therefore it is important to determine the application performance requirements.  If we improve the performance of selects obviously we compromise DML statements/transactions (may be adding indexes).

It is common to spend more time identifying the cause of the problem then actually troubleshooting and fixing it. If all other areas of application are working properly and you can be sure there is a problem with the database code, then you need to investigate your code modules and decide which one is causing problems. Many times improvement in only one stored procedure or trigger can fix most of the issues.

The Optimizer in the Adaptive Server takes a query and finds the best way to execute it. The optimization is done based on the statistics for a database or table. The optimized plan stays in effect until the statistics are updated or the query changes. You can update the statistics on the entire table or by sampling on a percentage of the data.

Adaptive Server can generate an abstract plan for a query, and save the text and its associated abstract plan in the sysqueryplans system table. Abstract plans provide an alternative to options that must be specified in the batch or query in order to influence optimizer decisions. Using abstract plans, you can influence the optimization of a SQL statement without having to modify the statement syntax.

Adaptive Server locks the tables, data pages, or data rows currently used by active transactions by locking them. Locking is a concurrency control mechanism: it ensures the consistency of data within and across transactions. Locking is needed in a multiuser environment, since several users may be working with the same data at the same time.

Carefully considered indexes, built on top of a good database design, are the foundation of a high-performance Adaptive Server installation. However, adding indexes without proper analysis can reduce the overall performance of your system. Insert, update, and delete operations can take longer when a large number of indexes need to be updated.

What to observe - high level (We have many many :) ... but top 10)

1. Server error logs
2. OS error logs
3. Configuration files
4. sp_monitorconfig output
5. sp_sysmon reports
6. sp_object_stats reports
7. sp_helpdb output
8. options and traceflags used
9. ER diagram or database model
10. and all applications running on the database.