SAP Sybase Replication Server

Replication Server maintains replicated data in multiple databases while ensuring the integrity and consistency of the data. It provides clients using databases in the replication system with local data access, thereby reducing load on the network and centralized computer systems.

The Replication Command Language (RCL) enables you to customize replication functions and to monitor and maintain the replication system. For example, you can request subsets of data for replication at the table, data row, or column level. This feature further reduces overhead by allowing you to replicate only the data that is needed at the replicate site.

Replication Server supports heterogeneous data servers. You can build a replication system from existing databases and applications without having to convert them. As your enterprise grows and changes, you can add data servers to your replication system to meet your needs.

Replication Server uses a basic publish-and-subscribe model for replicating data across networks. Users "publish" data that is available in a primary database, and other users "subscribe" to the data for delivery in a replicate database. Users can replicate both changes to the data (update/insert/delete operations) and stored procedures using this method.

Instructions to publish and subscribe to data are given at Replication Servers that control, or have a connection to, each database. The user creates a replication definition at a primary Replication Server, which controls the primary database containing the data to be published. The replication definition specifies information such as which columns are to be replicated, or in the case of a database replication definition, of the database objects to be replicated. The user creates a subscription at a replicate Replication Server, which controls the replicate database that will receive the information.

Replication Servers communicate with each other via user-defined routes. Most commonly, a primary Replication Server sends data to a replicate Replication Server through one or more routes set up to transmit data from the primary database to the replicate database. Users may also transmit stored procedures from the replicate to the primary to request updates of the primary data; in this case, data flows through one or more routes from the replicate Replication Server to the primary Replication Server.

Connections and routes define the structure of the replication system. They allow Replication Servers to send messages to each other and to send commands to databases. A connection transfers messages from a Replication Server to a database. A route transfers requests from a source Replication Server to a destination Replication Server.

RepServer Diagnostic tools

Diagnostic tools retrieve the status and statistics of Replication Server components which, depending on the type of problem, you can use to analyze the replication system. Check the troubleshooting section of the problem category for detailed information. This section summarizes the available diagnostic tools.
Use:

·        isql to log in to a Replication Server or data server to see if servers are up. You can also use isql to execute SQL commands to see if data is the same in the primary and replicate databases, or if data has been materialized or dematerialized.

·        admin who_is_down to find out which Replication Server threads are down.

·        admin who, sqm to display information, such as the number of duplicate transactions or the size of stable queues, about stable queues at a Replication Server.

·        admin who,sqt to display information, such as the number of open transactions, about stable queues at a Replication Server.

·        admin statistics,md to display information, such as the number of messages delivered, about messages delivered by a Replication Server.

·        sp_config_rep_agent to display the current RepAgent configuration settings.

·        sp_help_rep_agent to display static and dynamic information about a RepAgent thread.

·        sysadmin dump_queue to dump stable queues and view them.

·        rs_helproute to display the status of routes at a Replication Server.

·        rs_subcmp to compare a subscription's tables in the primary and replicate databases to make sure the tables are the same.

·        check subscription to display the status of subscriptions at a Replication Server.

·        rs_helppub to display publications.

·        rs_helppubsub to display publication subscriptions.

·        sp_setrepcol to check the replication status of text or image columns.

 Warm standby applications

In a warm standby application, Replication Server maintains a pair of Adaptive Servers (or SQL Servers), one of which acts as the backup of the other.

Typically, client applications update the active database while Replication Server maintains the other database as a standby copy of the active database. If the active database fails, or if you need to perform maintenance on the active data server or database, you can switch to the standby database (and back) with little interruption of client applications.

In a warm standby application, you create three connections:

·        A logical connection that Replication Server maps to the currently active database

·        A physical connection for the active database

·        A physical connection for the standby database

The logical database in a warm standby application may, with respect to other databases in the replication system, function as one of the following:

·        A database that does not participate in replication

·        A primary database

·        A replicate database

The procedure in this section demonstrates how to set up a warm standby system for a database that acts as a primary database in a replication system.

The below diagram illustrates a warm standby application operating on the BOSTON_DS data server for a pubs2 database on the NY_DS data server. The database is replicated to TOKYO_DS.
In this scenario the pubs2 database acts as a primary database in a replication environment. The primary pubs2 database for which a standby is created is called the active database.

Setting up a warm standby application

The following procedure is used to set up a warm standby application for an active database. In this procedure, an active database is already established. The procedure will be somewhat different if the active database has not yet been created. Make sure you review the warm standby information in the Replication Server Administration Guide before proceeding.
You must use Adaptive Server or SQL Server databases for this procedure.

1.1.1.1    In the active database:

1.     Mark the entire active database for replication to the standby database with the sp_reptostandby stored procedure.

sp_reptostandby enables replication of data manipulation language (DML) and supported data definition language (DDL) commands and stored procedures. Refer to Chapter 15, "Managing Warm Standby Applications," in the Replication Server Administration Guide for detailed information.

2.     Reconfigure RepAgent using the sp_config_rep_agent stored procedure with the send_warm_standby_xacts option. Restart RepAgent.

3.     Grant replication_role to the active database maintenance user.

4.     On the active data server, add the maintenance user of the standby database to the active database, and grant replication_role to the new maintenance user. This step ensures that the maintenance user ID exists in the standby database after the database is loaded (step 8).

5.     Log in to the Replication Server that is to manage the warm standby database, and create a logical connection for the active database, using the create logical connection command. The name of the logical connection must be the same as the name of the active database.

If you create the logical connection before you create the active database connection, use different names for the logical connection and the active database.

6.     On the standby data server, create the standby database with the same size as the active database.

7.     Use Sybase Central or rs_init to create the standby database connection. For more information, see the Replication Server online help and the Replication Server installation and configuration guide for your platform.

After the connection is created, log in to Replication Server and use the admin logical_status command to make sure that the new connection is "active."

8.     Initialize the standby database using dump and load without the rs_init "dump marker" option. (Or you can use bcp. Refer to the Replication Server Administration Guide for more information.)

1.     On the Replication Server, suspend the active database connection.

If you cannot suspend the active database, use dump and load with the rs_init "dump marker" option.

2.     On the active Adaptive Server, dump the active database.

3.     Load the active database dump into the standby database.

4.     On the standby Adaptive Server, put the standby database online.

9.     On the Replication Server, resume connections to the active and standby databases, using the resume connection command.

Check the logical status, using the admin logical_status command. Do not continue unless both active and standby databases are marked "active."

10.  Verify that modifications occur from active to standby database.

Using isql, update a record in the active database and then verify the update in the standby database.

Switching to the standby database

If it becomes necessary to switch from the active database to the standby database, you need to take steps to prevent client applications from executing transactions against or updating the active database. After the switch is complete, clients can connect to the new active database to continue their work. See "Switching clients to the new database" for details.
Before switching to a standby database, you should determine whether a switch is necessary:

·        Don't switch if the active data server is experiencing a transient failure. A transient failure is a failure from which the Adaptive Server recovers when restarted, without additional recovery steps.

·        Do switch if the active database will be unavailable for a long period of time.

You must use the switch active command to switch the active and standby databases. The following procedure illustrates how to switch the warm standby system illustrated in Figure 3-8 from the active database to the standby database.

1.     On the Replication Server, use switch active to switch processing to the standby database.

2.     Monitor progress of the switch. The switch is complete when the standby connection is active and the previously active connection is suspended.

1.     On the Replication Server, check the logical status, using the admin logical_status command.

2.     To follow the progress of the switch, check the last several entries in the Replication Server error log.

3.     Start the RepAgent for the new active database.

4.     Decide what you want to do with the old active database. You can:

1.     Bring the database online as the new standby database, and resume connections so that Replication Server can apply new transactions, or

2.     Drop the database connection using the drop connection command. You can add it again later as the new standby database.

o   Using isql, update a record in the new active database, and then check the update the new standby database.

Switching clients to the new database

Switching from the active to the standby database does not switch client applications to the new active data server and database. You must devise a method to handle client switching. For example, you could:

·        Set up two interfaces files, one for client applications and one for Replication Server. At switch time, modify the client interfaces file to point to the new active server.

·        Create an interfaces file entry with a symbolic data server name for use by client applications. At switch time, modify the address information associated with the symbolic name.

·        Use a mechanism, such as an intermediate Open Server, to map the client application data server connections to the currently active data server automatically.

 

 

 

 

2.     Standby applications

A warm standby application is a Replication Server application that maintains a pair of Adaptive Server or SQL Server databases, one of which functions as a standby copy of the other.
Client applications generally update the active database, while Replication Server maintains the standby database as a copy of the active database. Replication Server keeps the standby database consistent with the active database by replicating transactions retrieved from the active database transaction log.
If the active database fails, or if you need to perform maintenance on the active database or data server, you can switch to the standby database so that client applications can resume work with little interruption.
Figure 2-4 illustrates a warm standby system.

Figure 2-4: A warm standby system

The two databases in a warm standby application appear as a single logical database in the replication system. Depending on your application, this logical database may not participate in replication, or it may be a primary database or a replicate database with respect to other databases in the replication system.

1.2    Basic primary copy model

The basic primary copy model allows you to replicate data from a primary database to destination databases. This model is well suited to decision-support applications, although low-volume transaction-processing applications can update primary data remotely, either directly over the WAN or through request functions (replicated stored procedures). Primary data that is updated from remote sites can then be replicated back to subscribing sites.
You can implement the basic primary copy model by using any or all of the following:

·        Table replication definitions

·        Applied functions

·        Request functions

This section provides basic examples for using table replication definitions and applied functions. For examples of request functions and other, more advanced uses of the primary copy model, see "Model variations and strategies".

Using table replication definitions

Using table replication definitions allows you to replicate data from a primary source as read-only copies.
You can create one or many replication definitions for a primary table although a particular replicate table can subscribe to only one of them. See "Multiple replication definitions" for an example using multiple replication definitions.
You also can collect replication definitions in a publication and subscribe to all of them at one time with a publication subscription. See "Publications" for an example using publications.
For each table you want to replicate according to the basic primary copy model, you need to:

·        Set up routes and connections between Replication Servers.

·        Create the table you want to replicate in the primary database.

·        Create the table (or tables) to which you want to replicate in destination databases.

·        Create indexes and grant appropriate permissions on the tables.

1.2.1.1    At the primary site:

·        Mark the primary table for replication using the sp_setreptable system procedure.

·        Create one (or more) replication definitions for the table at the primary Replication Server.

1.2.1.2    At the replicate sites:

·        Create subscriptions for the table replication definitions at each replicate Replication Server.

See the Replication Server Administration Guide for details on setting up the basic primary copy model.
In Figure 3-1, a client application at the primary (Tokyo) site makes changes to the publishers table in the primary database. At the replicate (Sydney) site, the publishers table subscribes to the primary publishers table--for those rows where pub_id is equal to or greater than 1000.

Basic primary copy model using table replication definitions

Marking the table for  replication

This script marks the publishers table for replication.

-- Execute this script at Tokyo data server

 -- Marks publishers for replication

 sp_setreptable publishers, 'true' 

 go

 /* end of script */

Replication definition

This script creates a table replication definition for the publishers table at the primary Replication Server.

-- Execute this script at Tokyo replication server

 -- Creates replication definition pubs_rep

 create replication definition pubs_rep

 with primary at TOKYO_DS.pubs2

 with all tables named 'publishers'

 (pub_id char(4),

     pub_name varchar(40)

     city varchar(20)

     state varchar(2)

 primary key (pub_id)

 go

 /* end of script */

Subscription

This script creates a subscription for the replication definition defined at the primary Replication Server.

-- Execute this script at Sydney replication server

 -- Creates subscription pubs_sub

 Create subscription pubs_sub

 for pubs_rep 

 with replicate at SYDNEY_DS.pubs2

 where pub_id >= 1000

 go

 /* end of script */

.HTH.

Queries to get some important Sybase ASE Tables statistics..(Ver: 15.x/16.x)

To find the Top 100 tables RowCnt, Fragmentation, Space Utilization and Datachange% (in terms of #Rows)

select top 100  "Table"=left(name,32), "#Rows"=row_count(db_id(), id), "DataChanged%"=datachange(name, NULL, NULL), "Space_Utilization"=derived_stat(object_id(name),0, 'sput'), "Fragmentation"=derived_stat(object_id(name),0, 'dpcr') from sysobjects
where type='U'
order by 2 desc  

To Find all the indexes are not used in any Queries/Processes since last boot 
(This Query works only with MDA tables) :

select DatabaseName = convert(char(20), db_name()), TableName = convert(char(20), object_name(si.id, db_id())), IndexName = convert(char(20),si.name), IndexID = si.indid
from master..monOpenObjectActivity mo, sysindexes si
where mo.ObjectID =* si.id
and mo.IndexID =* si.indid
and (mo.UsedCount = 0 or mo.UsedCount is NULL)
and si.indid > 0
and si.id > 99 -- No system tables
order by 2, 4 asc

To find the Top 21 tables (in terms of #Indexes)

SELECT top 21 left(o.name,32), count(i.indid)
FROM sysobjects o, sysindexes i
where o.id = i.id
group by o.id
order by 2 desc

To find all the tables with LockScheme:

select DatabaseName = convert(char(20), db_name()), "TableName"=left(name, 32), row_count(db_id(),id) as "#Rows", 
"lock_scheme" = case when (sysstat2 & 57334) < 8193 then "All Pages" 
when (sysstat2 & 57334) = 163848193 then "Data Pages" 
else "Data Rows" end
from sysobjects where type='U' order by 2 desc

To find all the Tables and their Triggers and Status.

select convert(varchar(30), name) as TableName,

convert(varchar(30), isnull(object_name(instrig), '')) as InsertTriggerName,

case sysstat2 & 1048576 when 0 then 'enabled' else 'disabled' end as InsertTriggerStatus,

convert(varchar(30), isnull(object_name(updtrig), '')) as UpdateTriggerName,

case sysstat2 & 4194304  when 0 then 'enabled' else 'disabled' end as UpdateTriggerStatus,

convert(varchar(30), isnull(object_name(deltrig), '')) as DeleteTriggerName,

case sysstat2 & 2097152  when 0 then 'enabled' else 'disabled' end as DeleteTriggerStatus

from sysobjects where type='U' and (instrig<>0 or updtrig<>0 or deltrig<>0)

order by name

To find the HostName and Server Details 

select left(address_info,32) as "Server's Host Name & PortNumber"

      ,host_name() as "Client Host Name"
      ,@@servername as "ASE Server Name"
      ,net_type as "N/W Protocol"


from master..syslisteners

.

SAP HANA for Sybase DBAs

Transform yourself to the better career keeping your core alive..

Sybase ASE, which stands for Adaptive Server Enterprise, was Sybase’s enterprise-class database for transactional applications which brought revolutionary with its Architecture of 1 Server multiple databases, ease of access and Robust database management with optimal performance at minimal TOC. Sybase ASE became widely popular in the finance industy, but was only able to run SAP applications with the ASE verison 15.7 released in Sep' 2011. In April' 2014, SAP released ASE 16 (dropping Sybase Name) and SAP is planning for ASE to be the preferred database for running transactional applications in SAP’s Real-Time Data Platform, a collection of data management tools that SAP is integrating to provide very fast performance.

SAP (Systems, Applications & Products in Data Processing) brought revolution of data management with HANA (The SAP HANA® platform can increase analysis speed by more than 10,000x) which is capable to handle columnar data or COLUMN-STORE (Sybase ASIQ is pioneer, which SAP IQ software holds the Guinness World Record for loading, storing, and analyzing Big Data at 34.4 terabytes per hour) and ROW-STORE data (Sybase ASE is one of the top RDBMSs). It also encapsulates the InMemoryDatabases (SAP HANA is an in-memory database, is a combination of hardware and software made to process massive real time data using In-Memory computing)  and PhysicalDatabases dynamically to handle the data effectively. While SAP HANA can support transaction and analytic processing on the same data, not every business application stands to benefit greatly enough from up-to-the-moment analysis and ad-hoc queries to justify the expense of running it on HANA. SAP plans for ASE to be a less-expensive alternative to HANA for such cases. At the same time, SAP plans to make data in ASE easily accessible to HANA and vice versa, easing integration for companies with a mixed landscape.

SAP is including a license for Sybase ASE with SAP ERP on HANA, indicating that SAP has plans for these two databases to work together. SAP is also planning to make ASE a transitional database between a standard disk-based database and SAP HANA. SAP is working toward making migration from ASE to HANA very smooth using the Sybase Replication Server. For those planning to use HANA as a database in the future, SAP recommends moving to ASE in the short term to make for an easy transition to HANA in the long term.

SAP HANA Architecture

The SAP HANA database is developed in C++ and runs on SUSE Linux Enterpise Server. SAP HANA database consists of multiple servers and the most important component is the Index Server. SAP HANA database consists of Index Server, Name Server, Statistics Server, Preprocessor Server and XS Engine. I'm providing herewith very high-level info, so as to make yourself comfortable and get ready to reach new level with SAP-HANA. Start exploring and learning the future's Database..

Index Server: (like the DATA SERVER in ASE)

• Index server is the main SAP HANA database component
• It contains the actual data stores and the engines for processing the data.
• The index server processes incoming SQL or MDX statements in the context of  authenticated sessions and transactions.

Persistence Layer: 

The database persistence layer is responsible for durability and atomicity of transactions. It ensures that the database can be restored to the most recent committed state after a restart and that transactions are either completely executed or completely undone. 

Preprocessor Server: 

The index server uses the preprocessor server for analyzing text data and extracting the information on which the text search capabilities are based. 

Name Server: 

The name server owns the information about the topology of SAP HANA system. In a distributed system, the name server knows where the components are running and which data is located on which server. 

Statistic Server: 

The statistics server collects information about status, performance and resource consumption from the other servers in the system.. The statistics server also provides a history of measurement data for further analysis. 

Session and Transaction Manager: 

The Transaction manager coordinates database transactions, and keeps track of running and closed transactions. When a transaction is committed or rolled back, the transaction manager informs the involved storage engines about this event so they can execute necessary actions. 

XS Engine: 

XS Engine is an optional component. Using XS Engine clients can connect to SAP HANA database to fetch data via HTTP. 

Source : SAP website and various SAP GURUs (Thanks much to ALL)

SAP ASE Edge Edition

SAP ASE Edge Edition for Small and Medium Businesses

Good NEWS for Sybase passionate guys.  This might be the sweetest announcement from SAP to cool down your sweat thinking Sybase era is over. After dropping the name Sybase from its product names last year, SAP is quietly reshaping its database platform offerings in a better way.

Sybase Inc. has  2 ASE products; Sybase ASE Enterprise Edition (EE) and Sybase ASE Small Business Edition (SBE). The Sybase ASE EE product was and still is the flagship and the most commonly known edition of Sybase with a high price tag. To offer a Sybase ASE database server to the SMB market space, Sybase created ASE SBE, limited to 2 CPU sockets and 8 engines on a physical server, at a very low price.

The server virtualization revolution has dramatically changed the server landscape and database servers made their transitions to the new world with PaaS, IaaS and CLOUD. The majority of newly deployed database servers are virtualized, either on premise or in the cloud. The Sybase ASE EE 15.7 database server enabled Sybase clients to make the move to the virtual servers with the introduction of the threaded kernel model. Especially SAP ASE EE 16 continued the optimization for virtual servers and offers one of the most advanced database servers in the market along with weel known SAP brand and support.

SAP has improved upon SAP ASE Small Business Edition (ASE SBE) with the release of SAP ASE Edge Edition providing small and medium sized businesses with the same enterprise-level features that are found in SAP ASE Enterprise Edition.  SAP ASE Edge Edition can run on physical or virtual machines with 4 cores or less.  Machines are not limited to 2 chips as they were for SAP ASE SBE.  This gives small businesses the ability to leverage the benefits of virtualization on today's more powerful, multi-core platforms.

SAP ASE Edge Edition replaces SAP ASE SBE and includes many options that were not available to SAP ASE SBE customers.  Every license includes:

·         Security and Directory Services which provides SSL, LDAP authentication, row-level security, and more to provide the highest levels of security and protection from un-authorized access

·         Encryption of sensitive information such as credit card numbers or SSNs that can only be decrypted by authorized users

·         Intelligent Partitioning of data based on its content, which improves performance, shortens maintenance times, and simplifies operations for aging data

·         Compression which can lower storage costs and improve I/o performance

·         Warm standby replication of ASE Edge data to protect businesses from data loss and accessibility in the event of a system failure

SAP ASE Edge Edition provides small and medium sized businesses with enterprise-grade features for enhanced performance, advanced security, and data availability in the event of hardware failure.  It can be purchased through SAP partners at a low price point that will fit the budget of small businesses.

The following matrix shows the comparison between SAP ASE Edge Edition and Sybase ASE SBE. But pay close attention to the options that are now included and compare this to other vendors standard offerings.

Parameter	ASE Edge Edition	ASE SBE
Max Engines	No Limit	8
Max Cores OS can use	4	No Limit
Max CPU Chips	No Limit	2
Max Concurrent User Connections	No Limit	256

Hope this excites your Zeal and to consider Database Management Systems in Virtual Environment. Refer to scn.sap for more info.

.