A basic knowledge about Sybase
1, client / server architecture
Sybase is a establishment of the client / server architecture, the database management system.
鈼?What is a client / server architecture?
From the hardware point of view, client / server architecture refers to a task in two or more machines are distributed between, which the client (Client) to provide the user interface and used to run the application front-end processing, the server machine ( Server) to provide the client to use the resources and services.
From a software perspective, the client / server architecture is to an application or software system functions into logical part of the client software and server software. Client software, said some of the general responsible for data and applications, handling the user interface for receiving the user's data processing and converting the request to the server request to the server to provide data storage and retrieval services; server-side software is responsible for receiving client sent the request and provide corresponding services.
Client / Server combines the power of mainframes and central control, and low-cost PC, and better handling balance. Client / server for the task of centralized / local distribution of Fangfa provides a new, this system can Shi user data integrity, management and security of centralized control. In alleviating network traffic and host loads and to meet user needs, client / server system provides a good solution.
In short, client / server mode is: a network between the client and server protocol (such as TCP / IP, IPX / SPX) for connection and communication, the client makes a request to the server, the server side to respond to the request, and accordingly services .
Database application client / server model as shown below:
Second, visit the Sybase server, the basic process
1. Establish the connection between the client and server, including network connectivity, customer processes and the connection between the server process;
2. Client through the network to send SQL statements to the server, or operate the server used to query the data or database objects;
3. After the server receives the SQL statement, its parsed, optimized and compiled implementation;
4. If the Executive's statement produces a result set, the server through the network the result set returned to the client;
5. The client received the results of the treatment accordingly.
In the client / server system, there are two database engines, that is multi-process database engine and a single process, multi-threaded engines.
Multi-process structure that is more executable programs running simultaneously. Whenever the user logs on to the database system actually started a separate instance of the database engine. Play a coordinating role in the process, there is coordination to ensure that when communication between the multi-user access the same data integrity of the data. Multi-process database engine normally used in the mainframe database.
Single-process multi-threaded database engine in principle and multi-process database engine similar to and different multi-threaded database engine itself is responsible for scheduling the CPU-time applications, not dependent on the operating system. In this way, multi-threaded database engine's ability to protect themselves more.
Oracle Server is a true multi-process database engine, Sybase database management system uses a single process multi-threaded engines.
3, Sybase Product Overview
1. The composition of Sybase Software
Sybase software can be divided into three parts: First, data management and maintenance of on-line relational database management system Sybase SQL Server; Second, to support the establishment of a database application and development of a group of front-end tools Sybase SQL Tools; Third, can the heterogeneous environment, applications and other vendors of any type of data connection with the interface software for Open Client / Open Server.
SQL Server is a programmable database management system (DBMS), which is the core of the Sybase software products, play data management, high-speed buffer management, transaction management role.
2. The basic features of SQL Server
SQL Server is a relational database management system, which has the following basic features:
a.SQL Server can be placed on the number of disk devices, the initial disk space required to install at least 17MB.
b.SQL Server supports multi-bank structure, meaning that Sybase system can have multiple databases. Sybase can manage multiple databases.
c.SQL Server can compile and run T-SQL statement can return to client the required results. T-SQL statement is a standard SQL extensions, which in addition to data definition statements, data manipulation statements and data control statements, the main increase in flow control statements.
d.SQL Server can manage multiple users and high transaction throughput and lower transaction response time.
Client applications can access the Server in one or several databases of data.
4, SQL Server's main content
Sybase SQL Server is a multi-bank structure of the RDBMS, the architecture is as follows:
1. Database
Used the server's own database, it can be said to manage the database server and user database. Sybase is installed, the system automatically creates a four databases:
master, model, tempdb, sybsystemprocs
(1) master database
It is the management and control of the user database and the maintenance of the core database server is running, it saved a lot of system information, such as server configuration, users, equipment.
In the master database, which does not allow ordinary users to create database objects, or will make the master database transaction log becomes full very quickly. If the transaction log is used up, can not use the dump transaction command to release space in the master database.
(2) model database
It is provided for creating the user template database. When creating a new database, SQL Server automatically creates a copy of the database model, and it expanded to the size required by the user as a new user database.
Model database for each user database contains the required system tables. Model database can be modified in order to customize the newly created.
(3) tempdb database
It is a temporary database for the server is running and processing to provide a shared storage area, such as group by and order by the intermediate result is stored here. Tempdb space for all the database server shared by all users.
Each restart SQL Server, the server automatically processes a copy of the database model to tempdb databases, and tempdb remove the original content. Therefore, the user table in tempdb are temporary. Temporary table is divided into two categories: shared and can not be shared. Non-shared temporary table create table by the symbol # will be placed before the table name created; be shared temporary table create table specified by table name prefix tempdb .. the creation. Non-shared temporary table for SQL Server automatically add digital suffix name, and it only exists in the current session.
(4) sybsystemprocs database master database
It is designed to save the system commands (stored procedures) database, such as sp_help, sp_configure, sp_helpdevice so. When any database user runs a stored procedure beginning sp_, SQL Server find the following order: the current database, sybsystemprocs database, master database.
2. User Database
User database is the real purpose of using the Sybase server. To manage user data, you must create your own Sybase database, it refers to the create database command to create the database. Can not access the master database, the user is not entitled to create a new database.
The main contents of the database - database objects:
Tables, views, temporary tables
Indexes, primary keys, foreign keys
Default values, rules
Stored procedures, triggers, etc.
5, Sybase installation and configuration
1. Server-side installation
After the establishment of SQL Server installed, put the database to create the log and the index of the logical disk device. Database, log and index configuration should pay attention to the following principles;
a. Do not install any user objects in master database.
b. log should be kept in separate database on the disk.
c. can be optimized allocation across multiple devices I / O performance.
2. Client Installation
3. Immediate need to change the contents of the
(1) change the sa login password; (2) name server; (3) modify the file name (to ensure the normal start Sybase Central); (4) Change the default device; (5) to increase tempdb space.
4. Create a user database
Following the establishment of a database through a script that shows the process of establishing a database:
/ / Create a database device, equipment, size of the order page (2K) as the unit
disk init
name = "test_dbdev",
physname = "c: testtest_dbdev.dat",
vdevno = 10,
size = 10240
go
disk init
name = "test_logdev",
phyname = "c: testtest_logdev.dat",
vdevno = 11,
size = 5120
go
/ / Create the database TEST_DB, its size is 20M, 10M log size
create database TEST_DB
on test_dbdev = 20
log on test_logdev = 10
go
/ / Open the database
use TEST_DB
go
5. The main content management system
a. the management of physical resources
b. User and Rights Management
c. Database Backup and Recovery
6. SQL Server configuration parameters
Server configuration is the system administrator's responsibilities, the correct configuration of the system performance have a significant impact. There are two system tables store configuration information: sysconfigures and syscurconfigs; sysconfigures is permanent, once the system is running, sysconfigures the information is copied to syscurconfigs in. Shows the system configuration and change the configuration process using sp_configure.
There are two configuration values: dynamic and static, dynamic value once the changes take effect immediately, the static value to the system is restarted after the work.
The second stresses the database equipment and storage space management
I. Introduction
1. Install initialization
Initial installation of SQL Server, install programs and scripts to initialize the master device, and set up master, model, tempdb and sybsystemprocs database. System database, pre-defined devices and default paragraph in the following organizations:
a.master, model, tempdb database installed in the master on the primary equipment;
b.sybsystemprocs database installed in the installation, select the device (sysprocsdev);
c. Create a database for each of the three pre-defined section: system, default and logsegment;
d. all of the user's default device is to create a database master device;
e. If you choose to install the audit database sybsecurity, it is located in your device.
2. Equipment and storage management to consider the main issues
(1) recovery
Physical disk crash, disk mirroring, or in a separate physical device, save the log for the database recovery provides two mechanisms.
(2) performance
Disk read and write speed is I / O bottleneck operations and correctly place the database objects to physical devices that help improve performance;
The log and database objects placed in a separate device can improve system performance;
The table on a hard disk and put the index on another hard disk, because the work hours put two hard drives, so you can ensure that the physical read and write faster;
Disk image will reduce the disk write speed.
Second, device (Device)
Sybase database all the data stored in the device.
1. The concept of equipment
Equipment is pre-configured dedicated Sybase database storing a contiguous disk space, and it is mapped to an operating system file or a raw disk partition. It has two corresponding name: logical name and physical names. NT only supports devices mapped to the file.
The relationship between the equipment and the database: many to many relationships. A database can be created or expanded to multiple devices, a device can also be used to store multiple databases. Different device operating systems can read and write its parallel, so that we can artificially place a database to multiple databases on the device.
Equipment Category: Database Device and Dump Device. Databases store the device database and transaction log, the dump device used to store database or log backup.
2. Equipment to create
Command syntax:
DISK INIT
Name = 'device_name',
Physname = 'physical_name',
Vdevno = virtual_device_number
Size = number_of_pages
[... ....]
For example:
DISK INIT
Name = 'My_Device',
Physname = 'D: databaseMy_device.dat',
Vdevno = 3
Size = 5000
Note: The logical name, physical name, the virtual device number, device size
Create a dump device:
sp_addumpdevice ('disk' | 'tape')
Logical_Name,
Physical_Name,
TapeSize
3. Default Device
In the absence of the specified device, users automatically create any data objects stored in the default device.
After initial installation, the system's master master is pre-designated as the default device, so as soon as possible to create your own default devices. Ensure that the following equipment is not the default device: system main equipment, designated for use only by logging equipment.
Sp_diskdefault device name [, DefaultOn | DefaultOff]
4. Disk mirroring
For database disk image is a security consideration, when the media fails, disk mirroring to provide uninterrupted recovery. Disk mirroring is the absolute data on the disk copy. If a hard drive accident, then the damaged copy automatically becomes offline, which leads all the reading and writing are not damaged copy.
Sybase disk mirroring is carried out at the device level, so the disk device is essentially the mirror image. When the image of a device operation, Sybase automatically create an additional device, image processing by the original equipment mirror all data copied to the device.
If you want a mirror of a database, the database is assigned to each device must be into the mirror. Master equipment, a special position in the server, if it is damaged, SQL Server will crash. Therefore, if possible always to mirror Master device (in another disk).
Mirror command syntax:
Disk Mirror
Name = 'device_name', / / be mirrored device
Mirror = 'physical_name'
[, Writes = serial | noserial]
5. Remove equipment
sp_dropdevice logical_name [, delfile]
The device contains a database does not allow deletion.
Third, create and use the paragraph
Segment (Segment) is a database of device logical combination of disk space, it can be seen as pointing to one or more of the database device label. Segment can be controlled using the storage location of database objects, database objects can be classified into different segments stored.
Equipment and the relationship between paragraphs: many to many relationships. A device can create multiple segment, a segment can cover multiple devices.
1. Advantages of using segments
a. control the use of space: a database on a segment outside the growth target will not be in the segment;
b. improve performance: in a different section of the disk devices can be simultaneously read and write;
d. dealing with large tables: use section, can be a large table on a separate section on the physical device, such as a table of text or an image data storage another segment.
2. Create Section
sp_addsegment section name, database name, device name
Note: the specified device to create a segment for a database.
鈼?expansion of the scope of paragraph
sp_exetendsegment section name, database name, device name
Description: The equipment must be available in the database, or need to expand the database to the new device; designated section, database, equipment must be present.
鈼?reduce the scope of paragraph:
sp_dropsegment section name, database name, device name
Note: with the third parameter, the command does not delete the paragraph, but narrowed the scope of section. If the paragraph contains a paragraph to another exclusive device, you need to narrow the scope of the paragraph.
3. Use paragraph
鈼?two databases on the same device on different segments, they do not interact;
鈼?When the database when additional space, additional space will be automatically assigned to each of its segments;
For example: alter database my_db
on data_dev = 50
In data_dev equipment increased for the my_db 50M space, 50M space is automatically allocated to the database each paragraph. Note: If data_dev for the database is a new, system and default section will be automatically extended to the device.
Can use the alter database command log on options for allocation of additional log space.
(1) create a new object in the segment
create table table name (column name data type) [on the section name]
create [clusterd | non clusterd] index index name on table name (column name) [on the section name]
Note: By definition, the clustered index is always on the table on the same period.
(2) to place the existing objects in the segment
sp_placeobject section name, object name
Note: This command is not an object from a database device to another device, it only affect the future of space allocation.
Can be a big table text field or image field is placed in a separate device segment.
Sp_placeobject section name, "table name. Field name"
(3) create clustered index on the segment
By definition, the clustered index is always on the table on the same period. If you create a table in a paragraph, and in another segment create a clustered index, the index its table with its move, the entire table will create a table of the paragraph from migrating to create clustered index paragraph. In this way, you can quickly and easily to specify the table to move to the specified device.
(4) system pre-defined segment
When a user creates a database, Sybase automatically create three pre-defined segments:
system section: Storage Systems table (including all user defined objects)
default section: store user-created all kinds of objects, unless they are clearly assigned to a different segment.
Logsegment Section: store the database transaction log.
(5) Delete paragraph
Delete section is to narrow the scope of a special case of paragraph:
sp_dropsegment section name, database name
4. Use of threshold management
Threshold (Threshold) management is an automated mechanism for monitoring database free space, Sybase threshold management allows the user to the database on a section of free space to set thresholds and define the corresponding stored procedure. When the segment of free space is less than the threshold value of the first set, Sybase automatically run the corresponding stored procedure.
In a practical operation of the database, the log growth rate generally higher than the growth of data, Yidan log segment free space exhausted, SQL Server by default will Guaqi all data manipulation services, the client application to stop execution.
Segment of each separate database to store its transaction log is automatically set to have the last chance threshold (Last Chance Threshold), the threshold value is the backup transaction log space required for free estimates. When the segment of free space is less than the set threshold, Sybase automatically run a stored procedure named sp_thresholdaction. The name of the process and parameters predefined by the system, the content written by the user. Here is a simple example.
CREATE PROCEDURE dbo.sp_thresholdaction
/ * This process parameters passed by position, name may change, but its definition and the order can not be changed * /
@ Db_name varchar (30), / * database name * /
@ Seg_name varchar (30), / * segment name * /
@ Space_lefe int, / * remaining free space * /
@ Status int / * last chance threshold, its value is 1, the other threshold value, the value is 0 * /
AS
BEGIN
/ * Write the process of user content * /
dump transaction @ db_name
with truncate_only
相关链接:
Simple Text Or Document EditorsCaleSoft iPod Video ConverterGogo DVD To Zune Converter"Fallout 3" Aircraft Carrier Task Of The City Of Clues And Details Of The Extension.dvd fileApe FilesBEST Install And SetupSuperBurner Flash to iPhoneChristmasgift DVD to 3GPMedia Relations Public Relations Manager, how to protect journalistsUnicom announced iPhone bare metal pricesMito Xiu a plus for the perfectflv to MP4 converterGames Sports ReportHot PrinterPs3 movie formatsHow-to DVD to PS3