System and Databases:
This paper describes the system databases that reside on all Adaptive Server systems. It also describes optional Sybase-supplied databases that you can install, and a database that Sybase Technical Support may install for diagnostic purposes.
Overview of system databases :
When you install Adaptive Server, it includes these system databases: * The master database
* The model database
* The system procedure database, sybsystemprocs
* The temporary database, tempdb
Optionally, you can install:
* The auditing database, sybsecurity
* The two-phase commit transaction database, sybsystemdb
* The sample databases, pubs2 and pubs3
* The dbcc database, dbccdb
The master, model, and temporary databases reside on the device named during installation, which is known as the master device. The master database is contained entirely on the master device and cannot be expanded onto any other device. All other databases and user objects should be created on other devices.
Note : Do not store user databases on the master device. Storing user databases on the master device makes it difficult to recover the system databases if they become damaged. Also, you will not be able to recover user databases stored on the master device.
You should install the sybsecurity and sybsystemdb databases on their own devices and segment. You can install the sybsystemprocs database on a device of your choice. You may want to modify the installation scripts for pubs2 and pubs3 to share the device you create for sybsystemprocs.
The installpubs2 and the installpubs3 scripts do not specify a device in their create database statement, so they are created on the default device. At installation time, the master device is the default device.
Master Database :
The master database controls the operation of Adaptive Server and stores information about all user databases and their associated database devices. Because the master database stores information about user databases and devices, you must be in the master database in order to issue the create database, alter database, disk init, disk refit, disk reinit, and disk mirroring commands.
Controlling object creation in master :
When you first install Adaptive Server, only a System Administrator can create objects in the master database, because the System Administrator implicitly becomes "dbo" of any database he or she uses. Any objects created on the master database should be used for the administration of the system as a whole. Permissions in master should remain set so that most users cannot create objects there.
Note: Never place user objects in master. Storing user objects in master can cause the transaction log to fill quickly. If the transaction log runs out of space completely, you will not be able to use dump transaction commands to free space in master.
Another way to discourage users from creating objects in master is to change the default database for users (the database to which a user is connected when he or she logs in) with sp_modifylogin. If you create your own system procedures, create them in the sybsystemprocs database rather than in master.
Backing up master and keeping copies of system tables :
To be prepared for hardware or software failure on Adaptive Server, the two most important housekeeping tasks are:
* Performing frequent backups of the master database and all user databases. * Keeping a copy (preferably offline) of these system tables: sysusages, sysdatabases, sysdevices, sysloginroles, and syslogins is usually a better practice.
Adaptive Server includes the model database, which provides a template, or prototype, for new user databases. Each time a user enters the create database command, Adaptive Server makes a copy of the model database and extends the new database to the size specified by the create database command.
A new database cannot be smaller than...