Sybase database architecture

[Former Member]

Hello All,

Any one provide me the sybase database architecture standard diagram document & sybase architeture how many devices and whats the purpose of saptools database devices

[former_member188958 · Accepted solution]

The sybase ASE 15.7 architecture thread  references contains good information..

However, as a quick summary of available architecture diagrams:

Overall ASE System Architecture: http://talebzadehmich.files.wordpress.com/2012/02/ase15-7_architecture.pdf

Diagram of System Tables:  Systems Table Diagram

Diagram of MDA Tables (used for monitoring): Monitoring Tables Diagram

Regarding number of devices on a standalone ASE system (i.e. not involving any SAP applications installed on the ASE):

Technically, one could get away with ASE having just a master device, with all the other databases created on the master device.

By default, the master device contains the master database, model database, and the initial fragment of tempdb.  It may contain other databases, though this is not generally advised both for performance and recoverability reasons.

Typically there is another device dedicated to the sybsystemprocs database.  I usually name mine "sysprocsdev".

If auditing (the sybsecurity database) is installed, this is typically put on it's own device.  I usually name mine "sybsecuritydev".

If ASE is set up for checkstorage, the dbccdb database is typically put on it's own device.  I usually name mine "dbccdev".

I can't speak to the purpose of the saptools database.

Cheers,

-bret

[Former Member · Accepted solution]

Hi Bret,

Thanks for your response ,its very helpful for me.

If possible any architecture diagram can we understand simply like master ,model,temdb and all show,above provided diagram is very complicated .

If possible can u please explain me what are the roles sysprocsdev etc

thanks

[former_member188958 · Accepted solution]

I can't think of any particularly elegant way to diagram the relationship between the databases.

The master database contains the metadata for the whole server.

The basic core of the master database are three tables - sysdatabases, sysusages, and sysdevices.  Sysdatabases is a list of all the databases defined in the server, sysdevices is a list of all the disk devices used by the server, and sysusages maps each database in sysdatabases to fragments of the disks in sysdevices.  The master database also

has the syslogins table, which maps to a sysusers table in each database.

The model database is a template that is copied when a new database is created.

tempdb is a database used for temporary work tables.  It is rebuilt fresh (from a copy of the model database) every time ASE is rebooted.  By default there is a single tempdb, but it is possible to create additional ones to reduce contention, should that become a problem.

sybsystemprocs is a database used to hold almost all of the system provided stored procedures (a small handful are also created in the master database).  [A long time ago

the stored procedures were all stored in the master database, which is why the script that creates all the stored procedures is named "installmaster" and not something a bit more logical like "installprocedures". ]    System stored procedures have names that start with "sp_", ASE usually looks only in the user's current database for a stored procedure, but it the procedure name starts with "sp_" ASE will also look for the procedure in the sybsystemprocs and master databases.

The predefined Roles in ASE are described in the ASE System Adminisration Guide at System-defined roles

-bret