| |
Home |
![[Table of Contents]](/dwicons/b_toc.gif)
|
(Workplace UNIX) Introducing SQL Server |
![[-]](/dwicons/i_colpse.gif)
| Chapter 1: What Is Sybase SQL Server for Workplace UNIX? |
This chapter provides background information to help you use Sybase SQL Server for Workplace UNIX effectively. The chapter includes:
Sybase SQL Server for Workplace UNIX is an integrated set of Sybase relational database products for developing and deploying client/server applications on desktop platforms. SQL Server for Workplace UNIX brings the power of Sybase enterprise client/server computing to departmental, workgroup, and small business users.
This table shows the products that SQL Server comprises:
Product | Description |
|---|---|
SQL Server | The Sybase high-performance relational database that features an advanced multithreaded architecture, server-enforced integrity, and high transaction and query throughput for multiple users. |
Backup Server(TM) (installed as part of SQL Server) | A control server that operates concurrently with SQL Server and features high-speed, online backup, loading, and recovery capabilities. |
SQL Server Monitor(TM) | SQL Server monitoring tool consisting of four components. SQL Server Monitor Server captures performance data about SQL Server and databases. SQL Server Monitor Historical Server stores performance data for deferred inspection and analysis. SQL Server Monitor Client is a Windows application that displays performance data captured by Monitor Server. SQL Server Monitor Client Library is an application programming interface that enables you to build your own SQL Server monitoring applications. |
SQL Server Manager(TM) for Windows | A Windows tool to administer SQL Server through a graphical user interface. |
Open Client(TM) | The Client-Library (and DB-Library(TM)) programming interface that provides applications, third-party products, and other Sybase products with library functions to communicate with SQL Server. Includes Net-Library(TM), which provides network protocol services to support for the Open Client/SQL Server connections on many desktop platforms. |
ODBC Driver Kits | Application programming interfaces (API) that let you access SQL Server data from 16-bit or 32-bit Windows applications and development tools. |
SyBooks(TM) | A collection of SQL Server for Workplace UNIX documentation and a browser for searching and viewing the documentation online. |
The table below describes the high-level tasks involved in using SQL Server for Workplace UNIX to organize and access information.
Activities are performed by various categories of user, as shown in the table. You will learn more about these user categories, or roles, in the section "Roles in SQL Server," later in this chapter.
Activity | When Performed | Responsible Role | For More Information |
|---|---|---|---|
Installing or upgrading SQL Server for Workplace UNIX products | Before beginning to use SQL Server for Workplace UNIX | System Administrator | Installing Sybase SQL Server for Workplace UNIX |
Managing SQL Server
| After installing SQL Server; also, as needed due to changing conditions and needs at your site | System Administrator,System Security Officer | Chapter 2, "Managing a SQL Server Installation" and:
|
Creating SQL Server databases, tables, and other database objects | When setting up SQL Server to store user data; also, as needed to redefine how the data is stored or to add, update, or delete databases, tables, and database objects | Database Owner | Chapter 3, "Managing SQL Server Data," and:
|
Backing up and recovering the data in the SQL Server databases | For backing up, periodically, while using SQL Server; for recovering, after a system crash, media failure, or other loss of data | System Administrator,Operator | Chapter 4, "Backing Up and Restoring Data," and:
|
Issuing Transact-SQL queries to SQL Server | When accessing, updating, or deleting SQL Server data; also, when performing database and system maintenance activities | SQL Server end users, Database Owner, System Administrator | Chapter 5, "Accessing Data with Transact-SQL," and:
|
Building client applications to access data on SQL Server | When creating client applications for end users to access SQL Server data on an ongoing basis | Open Client Application Developers | Chapter 6, "Building Client/ServerApplications," and:
|
In a relational database management system (RDBMS), data is represented as tables, also known as relations. A database is made up of a set of related tables.
Each row of a table describes one occurrence of an entity, such as a person, a product, or a sale. Each column describes one characteristic of the entity, such as a person's name or address, a product's price, or the date of a sale.
Figure 1-1 shows how an RDBMS stores data in a table.
Figure 1-1: A table in a relational database
An RDBMS database comprises tables of related information. For example, the pubs2 sample database that comes with SQL Server for Workplace UNIX represents information about a book distributing business. One table in this database contains information about authors, another table contains records of book sales, and other tables represent other aspects of the book distribution business.
In addition to tables, SQL Server databases contain other objects that you use to store or manipulate data. The table below describes the objects that the SQL Server databases can contain.
Object | Description | For Information |
|---|---|---|
Table | A collection of rows and columns containing related data items. There are two types of table: user tables and system tables. | |
Rule | Specifies what values users are allowed to enter in a specific column. | |
Default | A value that SQL Server inserts into a column if the user does not explicitly enter a value. | |
Stored Procedure | A collection of SQL statements that you can issue as a single command. Using stored procedures improves SQL Server performance because they are stored in a preprocessed form. | |
Trigger | A type of stored procedure that goes into effect when you insert, delete, or update data in a specified table. | |
View | A virtual table derived from columns and rows of one or more tables (or other views). The plan for creating a view is stored by SQL Server, not the view itself. | |
Constraint | There are two types of constraint:
|
SQL (Structured Query Language) is a high-level language for accessing data stored in relational database systems. SQL has been approved as the official relational database query language standard by the American National Standards Institute (ANSI) and the International Standards Organization (ISO).
Transact-SQL is the Sybase version of Structured Query Language. Transact-SQL is compatible with IBM SQL and most other commercial implementations of SQL and provides important extra capabilities and functions not defined by the ANSI standard. SQL includes statements for querying databases, as well as creating databases and database objects, inserting data, modifying existing data, and other functions.
You can install the sybsyntax system database, which contains syntax information for Transact-SQL statements, system procedures, utilities, and other routines. To access the information in the sybsyntax database, use the sp_syntax system procedure. You can specify all or part of a statement name, and SQL Server returns the syntax for using that statement.
Just as you organize your own information into databases and tables, SQL Server organizes the information that it uses to manage user data into system databases and system tables.
SQL Server includes the system databases described in this table:
Name | Description |
|---|---|
master | Controls user databases as well as the operation of SQL Server as a whole. |
sybsystemprocs | Stores SQL Server system and user-created stored procedures that can be used to query the tables in the system databases. |
model | Provides a database template that is copied each time a user creates a user database. Any changes that you make to this database are reflected in each new database created by a user. |
tempdb | Provides storage that SQL Server uses for temporary tables and other temporary working storage needs (for example, intermediate results of complex queries). |
sybsecurity | Contains the audit system for SQL Server (optional). |
sybsyntax | Contains syntax descriptions for Transact-SQL statements and SQL Server commands (optional). |
pubs2 | A sample database (optional). |
The master database, created when you install SQL Server, is made up entirely of system tables containing information to keep track of SQL Server as a whole.
Each user database contains a subset of the system tables to keep track of information specific to that database. The system tables are also called the data dictionary or the system catalogs.
Use Transact-SQL to query the system tables just as you would any other SQL Server table. In this way, you can get information about the contents and operations of SQL Server and your databases.
For example, the following SQL query returns the names of all databases on SQL Server:
select * from sysdatabases
Data in the system tables is inserted, updated, or deleted by Transact-SQL statements or by system procedures. You should normally not modify the system tables.
SQL Server supplies system procedures for you to use as:
The system procedures that SQL Server supplies constitute one of its most powerful features. You can use a single procedure to accomplish complex sets of activities related to updating the system tables and getting data from them. There are system procedures for such activities as adding users, auditing system activity, and configuring SQL Server.
SQL Server creates the system procedures in the sybsystemprocs database during the installation process. The System Administrator owns this database. The names of all system procedures begin with "sp_".
One example of a system procedure is sp_dboption, which returns the current values of the SQL Server options. These options can be configured after SQL Server is installed on your server.
The pubs2 sample database is the basis of most of the examples in the SQL Server for Workplace UNIX documentation. Use the pubs2 database to practice the concepts you learn while using the products and the documentation.
The pubs2 database represents information for a book distributing business. It contains a guest user mechanism that allows any authorized SQL Server user to access it and to view, update, insert, and delete data. Guest accounts are discussed in "The Guest Database User".
SQL Server uses the concept of roles for assigning permissions to users to perform certain administrative tasks and functions. Roles are granted to individual server login accounts, and actions performed by these users can be audited and attributed to them.
There are three special SQL Server roles that have certain administrative tasks associated with them:
Users who will perform administrative tasks on SQL Server should have appropriate roles assigned to their individual login accounts. More than one SQL Server login account can be granted a particular role, and one account can possess more than one role.
A System Administrator performs administrative tasks unrelated to specific applications. The System Administrator is not necessarily one individual; the role can be granted to any number of individual login accounts. It is important, however, that the System Administrator's functions be centralized or very well coordinated.
System Administrator tasks include:
A System Administrator takes on the identity of Database Owner when accessing any database, including master. There are several activities that only a System Administrator can perform.
A System Security Officer is responsible for security-sensitive tasks on SQL Server, such as:
The System Security Officer can access any database but, in general, has no special permissions on database objects. An exception is the sybsecurity database where only a System Security Officer can access the sysaudits table. There are also several activities that only a System Security Officer can perform and for which permissions cannot be transferred to other users.
An operator is a user who can back up and load databases on a server-wide basis. The Operator role allows a single user to back up and restore all databases on a SQL Server without having to be the owner. These operations can be performed in a single database by the Database Owner and the System Administrator.
When first installed, SQL Server comes with a single login account, known as "sa". Log into the "sa" account to begin performing administrative tasks, such as setting up users and assigning roles.
A user who logs into the "sa" account has wide privileges throughout SQL Server, since it is configured for both the System Administrator and System Security Officer roles. This account should not be used regularly by any user.
In addition, SQL Server recognizes two kinds of object owners, who gain special status because of the objects they own. These ownership types are:
The Database Owner is the creator of a database or someone to whom database ownership has been transferred. The System Administrator grants users the authority to create databases. The owner of a database may:
Database objects are tables, indexes, views, defaults, triggers, rules, constraints, and procedures. A user who creates a database object is its owner. The Database Owner must first grant the user permission to create the particular type of object. There are no special login names or passwords for database object owners.
The creator of a database object is automatically granted all permissions on it. System Administrators also have all permissions on the object. The owner of an object must explicitly grant permissions to other users before they can access it. Even the Database Owner cannot use an object directly unless the object owner grants him or her the appropriate permission.
Sources of information for topics discussed in this chapter are listed below. For information on where these topics are discussed in this book, refer to the index.
Topic | Source(s) of Information |
|---|---|
Client-Library |
|
Client/server application development |
|
Database objects |
|
DB-Library |
|
pubs2 sample database |
|
Roles |
|
Syntax for Transact-SQL |
|
System databases and system tables |
|
System procedures |
|
User permissions |
|
|
|