Sybase ASE is a relational database system developed by Sybase Corporation of the United States. It is a typical large-scale database system in a client/server environment on UNIX or Windows NT platforms. Sybase provides a set of application programming interfaces and libraries that can be integrated with non-Sybase data sources and servers, allowing data to be copied between multiple databases, and is suitable for creating multi-layer applications. The system has complete triggers, stored procedures, rules, and integrity definitions, supports optimized queries, and has good data security.

Sybase is usually used in a client/server environment with SybaseSQLAnywhere, the former as a server database and the latter as a client database. It uses PowerBuilder developed by the company as a development tool and is widely used in large and medium-sized systems in my country.

1.Insert the Sybase Server installation CD.

2.Execute setup.exe in the installation CD directory.

3.After completing the ASE installation, select Start->Programs->Sybase->Server Config to start the server installation process and configure the server.

1.Operating system patches

For Intel x86: Red Hat Enterprise Linux (AKA Red Hat Advanced Server): AS, ES, and WS versions
SuSE Linux Enterprise Server/United Linux

For Intel Itanium：Red Hat Enterprise Linux(AKA Red Hat Advanced Server):AS and WS versions
SGI Advanced Linux Environment Itanium (SGI Altix)

2.Create Sybase group and user. Generally, the recommended user name is sybase and the group name is sybase. If you install two machines, make sure the uid and gid of the primary and standby machines are consistent.

3.Insert the Sybase Server installation CD

4.The system automatically mounts cdrom. If the system does not mount cdrom, you can use the command：#mount /mnt/cdrom(#mount -t iso9660 /dev/cdrom mnt/cdrom)

5.Log in as the sybase user and execute the software installation in the installation file directory $./setup (use ./setup -console for character terminals)

6.Set environment variables:csh:$cp $SYBASE/SYBASE.csh $SYBASE/.cshrc
sh or ksh use $cat $SYBASE/SYBASE.sh>>$SYBASE/.profile

Configuration parameters are SA-defined settings that control the behavior of Adaptive Sever. A server installed with the default configuration is only sufficient to start the server, but this configuration is not enough in real production and development environments. Therefore, after a server is installed, the first thing the system administrator has to do is to reconfigure the server according to actual performance requirements.

Configuration parameters include dynamic parameters and static parameters:
Dynamic parameters take effect immediately after modification;
After static parameters are modified, the server must be restarted for them to take effect.

There are generally two ways to set configuration parameters:
View and modify configuration parameter values ​​through the sp_configure system stored procedure;
Manually modify the configuration file and then read the value into the server or restart the server.
The configuration file is an ASCII text file that stores configuration parameter values. By default, the file is name :.cfg

When the server starts, the configuration file is used to allocate server resources:
On UNIX, the configuration file is used unless another file is specified $SYBASE/.cfg ；
On NT, the configuration file is used unless another file is specified %SYBASE%\.cfg 。

1. Configure memory

sp_configure "max memory",mmm (unit is 2k) configures Sybase to use mmm*2k memory, which is usually configured as 70-75% of the system memory.
For example, if a machine has 2G memory, to allocate 1.4G to the ASE database, it needs to be set to 700000
It should be noted here that for a 32-bit operating system, due to 32-bit addressing, the operating system (such as Windows-x86) gives the database a maximum of 1.6G. Even if there is more than 4G of physical memory, only 1.6G can be used here.
sp_configure "allocate max shared mem",1
Allocate the maximum memory at startup. I especially recommend using it when there are multiple programs. Otherwise, if other programs occupy the memory, ASE may report an error during database operation.
After max memory takes effect, configure data cache: sp_cacheconfig "default data cache","xxxM"
It is generally 50% of max memory, in M. In the above example, 1.4G of max memory, 700M is more appropriate for ASE.
procedure cache: sp_configure "procedure cache size",yyy
Generally 20% of max memory, in units of 2k. In the above example, 1.4G max memory, 280000 is more appropriate

2. Configure engines

sp_configure "max online engines",n
The engines mentioned here are what we commonly call CPUs, but there are a few points to note:
If this is a hyperthreaded CPU, it is recommended to turn off hyperthreading. Hyperthreading has no effect on database operation and may have a negative impact
The engines mentioned here are the total number of processors in the CPU. For example, if there are two 4-core CPU servers, n should be 8
Assuming that this server is only used to run the database, the general rule for the number of engines m at startup is:
If engine n=1,2, then m=n is the same, which is 1,2.
If engine n=3,4,5, then m=n-1, that is, 2,3,4.
If engine n>=6, then m=n-2

3. Other parameters

Number of locks: sp_configure "number of locks",100000
If row locks are used, the initial setting is generally 100,000 or higher. When the number of locks in the error log is found to be insufficient, it is expanded. It is a dynamic parameter and can take effect immediately
Number of user connections: sp_configure "number of user connections",50
Number of open objects: sp_configure "number of open objects",5000
Number of open indexes: sp_configure "number of open indexex",5000
Number of devices: sp_configure "number of devices",256
For ASE 15, there is also the number of partitions: sp_configure "number of open partitions",5000
The above are all required settings after the database is installed, and are not complete. Other configuration parameters, as well as the refinement of these parameters, need to be adjusted according to the application and server settings

The character set is based on the database operating system platform and language set. In the C/S system, in the data processing that supports multiple languages, all the languages ​​used must belong to the same language group. Therefore, in order to support "all" languages ​​(650 languages) in the world, there is a common character set - unicode.

For the support of Chinese, we generally recommend the use of cp936, which is based on CB13000-90, an extension of GB2312 (EUCGB), and is backward compatible with EUCGB. Currently, the most abundant support for Chinese is GB18030, which not only supports 27,000 Chinese characters, but also supports ethnic languages ​​such as Tibetan, Mongolian, and Uyghur. However, it only supports one case-sensitive sorting method, binary. utf8 uses 3 bytes to represent a character, so it will consume 50% more storage space.

If both the server and the client use ISO_1, the system will automatically treat a Chinese character as two characters. Generally, there will be no impact, but if you execute "like", an error may occur.

After the initial installation of the ASE database, there is no CP936. If you need to load it, the process is as follows:
1. In the $SYBASE\charsets\cp936 directory, execute charset -Usa -Ppassword -Sserver_name binary.srt cp936
2. In the SQL environment, check whether the load is successful, select name,id from syscharsets, and see if there is a row with name cp936 and id 171
3. If it exists, the load is successful, set the default character set to CP936, sp_configure "default character set id",171
4. Restart the database, it will automatically crash the first time, and then restart the second time

If you use isql to connect and find that the characters between the client and the server cannot be automatically converted, you need to modify the client character set:
1. Log in as the sybase user in the operating system and check the client language LANG. If the SYBASE environment variable configuration is installed, it is generally "C"
2. Go to the $SYBASE\locales directory to modify the character set definition in the locales.dat file. First find the corresponding operating system, then find the line corresponding to the language (such as "C", or default), and then modify it to the corresponding character set (such as cp936)

If the system uses two machines, you need to modify the same files on both machines, $SYBASE/ASE-12_5/servername.cfg and $SYBASE/locales/locales.dat.

When the database is running, there is a large transaction, such as deleting a table (drop table), which fills up the database log, and the database reports an error and stops running. This is a common problem. If it is not handled properly, such as shutdown, it is very dangerous.

The most direct and safest way to deal with this kind of failure is to increase the "log space": alter datbase your_db_name log on your_db_dev=xxxM

If it is restarted, the server will report an error, and the database will be unavailable because there is no recovery. At this time, you need to modify the system table, change the status of this database to -32768, and then clear the log. The steps are as follows:
1. sp_configure "allow updates",1
2. update master.sysdatabases set status = -32768 where name = "your_db_name" restart the service;
3. dump tran you_database_name with no_log
4. update master.sysdatabases set status = 0 where name ="your_db_name"
Restart the service again. Generally, the ASE system can be used.

There are a few more points to note:
1. If the amount of data in the log is large, the startup time may be very long, tens of minutes or even longer;
2. If the log is full of master database, you need to set flag -T3607 instead of directly changing the sysdatbases table;
3. If the above method cannot be used to repair it, you need to delete the data log from the operating system level, which is risky. It is necessary to back up important tables through bcp.

Delete tempdb from the master device. The operation is very simple. Just delete the three segments system, default, and logsegment.

The steps are as follows:
Log in to the server as sa and execute
>sp_dropsegment system,'tempdb','master'
>sp_dropsegment 'default','tempdb','master'
>sp_dropsegment logsegment,'tempdb','master'
>sp_configure "allow updates to system tables",1 ----Allow modification of system tables
>delete sysusages where dbid=2 and vdevno=0 ----dbid=2 is tempdb, vdevno=0 is the master device
>update sysusages set lstart=0 where dbid=2 ----Here only one tempdb device is considered
>sp_configure "allow updates to system tables",0
After execution, you can use sp_helpdb tempdb to view the usage of the device. If it is no longer on the master device, it means that the deletion is successful.
In Sybase ASE In versions prior to 15.0, you need to expand tempdb after building the service (build server). After ASE 15, you can directly expand tempdb when building the service, but strangely, tempdb still leaves a tail in the master, which needs to be deleted manually by the DBA. Leaving a little tempdb in the master may cause performance degradation and cause system insecurity and instability. It is best to remove it after the system is installed

1. Monitor disk I/O performance through monDeviceIO and monIOQueue in the MDA table

These two tables are part of the MDA monitoring table, which allows you to determine whether there is a problem with I/O. In other words, when ASE is doing a lot of I/O, does the host operating system complete it in time or not? The information in these two tables tells us the average disk working time over a period of time, which devices have done the most I/O, and how many I/Os are in the queue because the host cannot process them in time. It is generally believed that if your host is connected to a SAN, each I/O exceeding 10 milliseconds is very bad, 9~5 is also relatively slow, and less than 4 is relatively normal. If you feel that the I/O performance is unacceptable, it is a good idea to spread the I/O to multiple disks or LUNs. You can use the monOpenObjectActivity table to determine which objects are "hot spots" and move them from the same disk.

2. Monitor the operating system through iostat and vmstat to see if the "hot spots" disk and the MDA table are consistent

Further confirm the values ​​in the MDA table by monitoring the disk and I/O at the operating system level.

3. Confirm that the libaio library has been installed

Whether libaio is installed depends mainly on whether it is installed by default when the Linux operating system is released. This library provides asynchronous I/O for ASE (other I/O methods include "standard UNIX synchronous I/O" and "UFS cache I/O"). ASE 15 requires this library to be installed. If the ASE database is not available, it will not start.

Different versions of Linux handle the llibaio library differently. You need to query the corresponding instructions to confirm whether it has been installed and how to obtain and install it.

4. If you use the file system, please make sure that DSYNC is not turned on

DSYNC is simply this: when you create a database device with disk init, ASE will write this file synchronously to ensure that the data is written successfully and can be recovered. In earlier versions, ASE needs to turn it on. If it is turned off and the file buffer is used, if there is a problem when writing data or recovering (recovery), the system may fail or even crash the ASE database.

If you use ASE 15 or higher, you need to turn DSYNC off and turn DIRECTIO on. This way, writing to the disk will be done in a similar way to raw partitions.

If you use raw partitions, these two parameters are turned off by default, and they should always be turned off.

5. Check the mount options

If you are using a file system device, you need to check the mount options to confirm whether there is a bottleneck.

For example, the default mount option is ext3, but its files cannot provide the best performance as database devices; "noatime, nodiratime, data=writeback" can improve performance. The XFS file system has better performance than ext3 (or ext2).

6. Confirm that /etc/sysctl.conf is set correctly

This parameter determines the core parameters of the operating system and takes effect at each startup (at runtime, you can use "sysctl -p"). For ASE applications, it is recommended to set the following two key parameters: kernel.shmmax shared memory, fs.aio-max-nr maximum asynchronous I/O

In the modern information society, the importance of data is self-evident. In database management, emergency and disaster recovery have become an important topic of data. How to automatically and reliably back up data and enable it conveniently and promptly when a failure occurs is a very important task for database administrators.

Currently, in our application of Sybase ASE database management system, there are many ways to achieve high reliability. The method of automatically backing up transaction logs at regular intervals and restoring them to the backup library in time is a very stable, effective and practical method.

On two hosts of the same platform, Server A and Server B, there are database sets of ASE database systems a and b respectively, and the BACK_UP service has been started on both servers.

The first step is to log in to host A with isql and back up database a to file c:\a.dat: dump database a to "c:\a.data"

The second step is to log in to host B with isql and restore the data file c:\a.dat backed up from a to b: load database b from "c:\data"

The third step is to use DSEDIT on the client to create interfaces A and B with the database respectively, and create the bcka2b.bat batch file date /t >>bcka.log
time /t >>bcka.log
isql -Usa -P -SA -ibcka.sql >>bcka.log
date /t >>ldb.log
time /t >>ldb.log
isql -Usa -P -SB -ildb.sql >>ldb.log

Step 4: Create the bcka.sql file to back up the transaction log of changes in database A:
dump tran a to 'c:\a.tran'
go

Step 5: Create the ldb.sql file to write the transaction log of changes to database B:
load tran b from 'c:\a.tran'
go

Step 6: Add the task bcka2b.bat to the Windows "Task Scheduler" and determine the backup time (such as 5 minutes) according to the backup requirements in "Properties->Advanced Schedule Options->Repeated Tasks".

Step 7: When a fault occurs and emergency response is required, just change the IP address of Server B to the IP address of Server A, or directly modify the host name, restart the database server, log in to the database, and type "online database b", and the database will be available.

Note:
1. If the business volume is large, the backup has a greater impact on the performance of the primary point. You can increase the backup cycle, such as 10 minutes, half an hour, one hour, etc.;
2. During operation, please do not make the backup point database "online", otherwise you will need to dump the database again;
3. Since BCP, select into and other operations are not logged, these data cannot be backed up.

一、Sybase复制服务器(Sybase Replication Server)安装步骤：

Preparation:RS12.5 and earlier versions require an ASE database server to store the RSSD database. You can use an existing ASE or create a new ASE as the RSSD database server; RS12.6 and later versions can use ASA or ASE as the RSSD database server. If you use ASA, no preparation is required. It will be automatically created when you install RS

Installation: Use the setup or install application to uninstall the RS product from the installation media to the machine

Configuration: Use the rs_init application to install and configure RS

2. Installation on UNIX platforms

Create a sybase user

Create a sybase installation directory

Execute install (graphical interface) or install –c (text interface) to install the RS server product

Set the SYBASE environment variable
Use sh or ksh to use cat $SYBASE/ SYBASE.sh >> $SYBASE/.profile
Use csh to use cp $SYBASE/SYBASE.csh $SYBASE/.cshrc

Execute dscp to add a replication server and a replication service manager interface information

Execute rs_init to configure a new replication server (RS)

Execute rsmgen to configure a new replication service manager (RSM)

3. Installation on NT platform

Execute setup to install RS server products

Restart the machine

Execute dsedit to add a replication server and a replication service manager interface information

Execute rs_init to configure a new replication server

Execute "RSM Server Settings" in the start menu to configure a new replication service manager (Note that it is best to select iso_1 for the RSM Server Char Set item)

4. RS client installation

Install RS_pcclient client product

If the RSM service is not on the local machine, use dsedit to add an RSM service

In sybase Select "Tools" -> "Connection" -> "Sybase Replication Server" from the central menu, then select the RSM server in the pop-up interface, and log in as sa for the first time

Use "add server" in the logged-in RSM server to add the relevant ASE and RS. Before adding RS, you need to add the ASE where its RSSD is located

Installation Configuration

RSSD should be large enough, RSSD data: 500M RSSD log: 500M is recommended, and RSSD is set to automatically clear logs

Configure a large enough partition for the replication server

After installing the replication server, several parameters must be modified to ensure stable operation:
set memory_limit to "256" (default is 1048576)
set sts_cachesize to "2000" (default is 100)

If there are many database services connected to the replication server, you need to modify the following configurations
set cm_max_connections to "256" (default is 64)
set um_client_connections to "100" (default is 30)
set num_threads to "1024" (default is 50)
set num_mutexes to "2048"
set num_msgqueues to "2048"

Delete the user (user) of maint login (syslogins) in the primary point and the replica point and change it to dbo_alias
>use PDB
sp_dropuser dbname_maint
sp_addalias dbname_maint ,dbo
>use RDB
sp_dropuser dbname_maint
sp_addalias dbname_maint,dbo

If WarmStandby is used, it is recommended to modify the following parameters:
suspend connection to RDS.RDB
alter connection to RDS.RDB set dsi_xact_group_size to "-1"
resume connection to RDS.RDB

If there are no new tables added to the database in the replication system and the table structure will not change, it is recommended to use table replication to implement WarmStandby

Do not ignite triggers for data replicated to the replication point (WarmStandby does not ignite triggers by default, and table replication does not ignite triggers by default. Regardless of the method used, it is recommended to forcefully specify that triggers are not ignited)
suspend connection to RDS.RDB
alter connection to RDS.RDB set dsi_keep_triggers to 'off'
resume connection to RDS.RDB