Home

	Expand Search

(Workplace UNIX) Introducing SQL Server

Chapter 2: Managing a SQL Server Installation

Chapter 2

Introduction

This chapter discusses system-wide activities that you as System Administrator, or other designated users, perform while managing the SQL Server installation, including:

• Configuring SQL Server
• Managing SQL Server users
• Managing SQL Server's physical resources
• Customizing and fine tuning SQL Server
• Auditing SQL Server user activity

SQL Server Manager

You can perform many of the activities described in this chapter interactively with SQL Server Manager, a Windows application for managing SQL Server. Use SQL Server Manager to:

• Manage disk storage
• Set up SQL Server user accounts
• Create databases and database objects
• Monitor system performance
• Troubleshoot server problems
• Start and stop SQL Server
• Fine-tune SQL Server by changing server parameters

Using SQL Server Manager, you can accomplish SQL Server management, administrative, and maintenance functions quickly and easily.

Configuring SQL Server

sybinit

After you install SQL Server, you configure it using the configuration utility, sybinit. See Configuring Sybase SQL Server for your platform for instructions on using sybinit.

SQL Server Backup Server

Backup Server is provided as part of SQL Server for Workplace UNIX. It is used for maintaining backup copies of your databases and transaction logging files. To use Backup Server, you need to activate it explicitly.

Localizing SQL Server

SQL Server supports international use through files that contain translated error messages, character set definitions, sort order definitions, and utilities for converting SQL Server's character set into the appropriate character set for a PC or terminal. These files are called localization files, and they are included when you install any Language Module with your SQL Server.

SQL Server Logins and Users

For users to access data stored on SQL Server, they must have:

• A SQL Server login account with its associated login ID and password
• Explicitly-granted access to one or more databases
• Explicitly-granted permissions for performing various actions on the database and database objects

Users may be organized into groups and permissions granted to the group. This mechanism allows similar types of users to be granted data access capabilities at one time, rather than one by one.

Activities related to setting up and managing SQL Server users are carried out by the System Security Officer, the System Administrator, and the Database Owner.

The table below summarizes the activities and who performs them.

Activity	Executed By
Create new logins, assign default database and default language	System Security Officer
Create groups	Database Owner or System Administrator
Add users to database, assign aliases, assign groups	Database Owner or System Administrator
Grant permissions on statements and database objects to groups or users	Database Owner, System Administrator, or database object owner

Database Users

Once an individual has been given a SQL Server login, that person may then become a database user for any user database on that server. Although a SQL Server login does not automatically grant the user access to a database, a default database may be assigned to a SQL Server login account that allows the user to directly access that database upon logging in.

After a user has been given access to a database, the owner of the database or the owner of objects within the database still must give the user permission to read, modify or delete data, use certain Transact-SQL statements, and execute stored procedures.

The System Security Officer sets up users and groups for individual databases on SQL Server. The System Security Officer also grants permissions to users and assigns a default database and default language for users. Users and their permissions are assigned for individual databases, one at a time.

Use the SQL Server Manager to manage SQL Server logins and database users and groups.

For a small installation, you can assign both System Security Officer and System Administrator roles to the same user in order to consolidate database user management activities with a single person. For large installations with many users, it is recommended that System Security Officer and System Administrator functions be separate and carried out by different users.

Passwords

Passwords help prevent unauthorized access to SQL Server data. SQL Server passwords must be at least six characters long and can contain any printable letters, numerals, or symbols.

When creating your own password or one for another user, choose one that cannot be guessed. Do not use personal information, names or nicknames of pets or loved ones, or words that appear in the dictionary. The most secure passwords are ones that combine uppercase and lowercase characters with numbers and punctuation, for example, "4A$sk."

Users can change their passwords at any time.

Groups

A group is a mechanism for assigning a collective name to multiple database users for the purpose of granting and revoking permissions. Using groups, you can manage permissions for many users in a single operation.

Every user is a member of the predefined group "public" and can be a member of one other group. (Users remain in "public" whether or not they belong to another group.)

A System Administrator or Database Owner can create a group at any time. The System Administrator assigns or reassigns users to groups.

Visitor Login Accounts

To accommodate occasional users of SQL Server, the System Security Officer can create a login name (for example, "visitor") and password that visiting users can use. Typically, such users are granted very restricted permissions.

Note that visitor accounts allow more than one person to log in to SQL Server on a single account. This results in poor auditing records, since you cannot distinguish the actions of individual users of the visitor account. To avoid this situation, assign each user a separate login account.

Remote Users

You can enable remote access to allow users on another SQL Server to execute stored procedures on your server. You can also set up users of your SQL Server to execute remote procedure calls to the remote server.

To enable remote procedure calls, both the local and the remote server must be specifically configured. The System Security Officer and the System Administrator share control over remote user access and any associated security issues. Database Owners are responsible for granting permissions to remote users.

The Guest Database User

A Database Owner can create a database user named "guest". Adding a guest user to a database allows an owner to permit all SQL Server users to use that database without having to name each one as a database user explicitly.

When the user name "guest" is first added to a database, "guest" inherits the privileges of the default group "public". The Database Owner and the owners of database objects can change these permissions to make the privileges of "guest" more or less restrictive.

Locking SQL Server Logins

For security reasons, you can lock a SQL Server account to prevent a particular user from logging in. To ensure that someone always has access, SQL Server maintains at least one unlocked System Security Officer account and one unlocked System Administrator account.

Getting Information About Users

SQL Server Manager displays information about users, such as:

• Logins mapped to a user
• Objects on which users have permissions
• Objects owned by a user
• Users and aliases mapped to a login
• Users in a group

Additionally, Sybase-provided stored procedures report the following information about users:

• Currently active SQL Server users and processes
• Login accounts
• Users and aliases in the database
• Groups within a database

Changing User Information

The table below summarizes ways in which user information can be changed and who can make the changes.

Activity	Executed By
Change another user's password	System Security Officer
Change own password	User
Change group assignment of a user	System Administrator, Database Owner
Change a login account's default database, default language, or full name	System Administrator
Change own default database, default language, or full name	User

Aliases

You can use an alias to treat more than one person as the same user, with the same privileges, inside a database. In particular, several users can assume the role of Database Owner.

For example, you might create an alias called "vp" to be used by all vice presidents, each of whom has an individual SQL Server account linked to that alias.

You also can use an alias to set up a collective user identity, within which the identities of individual users can be traced by auditing their activities. You cannot do this with a visitor or guest account.

Managing Physical Resources

As System Administrator, you are responsible for managing the physical resources used by SQL Server.

Database Devices

You must prepare database devices before you can create user databases to store information. This process is called initialization.

A database device stores the objects that make up databases. The term "device" does not necessarily refer to a distinct physical device. A database device is any piece of a disk (such as a partition or a file in the file system) used to store databases and database objects.

Databases are allocated storage space when you create or enlarge them. When creating a database, you can specify one or more database devices and the amount of space on each device to be allocated to the new database.

Management Tasks

A System Administrator initializes new database devices. After initializing the devices, the System Administrator creates a pool of default database devices to be used by all SQL Server users when creating databases. Whenever users create (or enlarge) databases without specifying a database device, new disk space is allocated from this pool.

SQL Server uses default values for many aspects of its storage management¾where databases, tables, and indexes are placed and how much space is allocated for each of them. However, the System Administrator can modify any of these values.

After stabilizing an application and determining its data-handling requirements, you refine storage management to improve performance. These refinements are not an essential aspect of data definition, but rather a means of improving performance.

Some of the activities associated with management of physical resources are:

• Making a physical device available to a specific server
• Assigning a database device name
• Making a database device available as the default device
• Mirroring (duplicating) a database device
• Placing the database's logs on the default device or on a specific device
• Allocating space on database devices
• Creating a segment (a named subset of database devices) from the devices available to a specific database
• Creating a table or index on a specific segment or on the default device

Database Size

If you do not specify a size when creating a database, the database is created with the default amount of space. This amount will be the greater of these two values:

• The default database size configuration parameter
• The size of the model database

You can allocate more space for the model database as well as reset the database size configuration parameter.

It is difficult to reclaim storage space after it has been assigned. You cannot deallocate space that has been assigned to a database unless you drop the database first; however, you can always add more space.

When a database grows to fill all of the allocated space, you can add space for database objects or the transaction log, or both. Permission to enlarge a database defaults to the Database Owner.

Location and Size of the Transaction Log

The transaction log is a system table that records all changes made to a database. By default, the transaction log is located on the same device as the database, but unless you are creating very small, non-critical databases, you should always place the transaction log on a separate device.

Designating a separate device for the transaction log has the following benefits:

• SQL Server performance is improved because writes to the database and to the transaction log occur concurrently rather than serially.
• Full recovery is guaranteed if the hard disk crashes.
• You only need to back up the transaction log as a recovery mechanism rather than always backing up the entire database, thus saving time and tapes.
• You can establish a fixed size for the log, keeping it from competing with regular database activity for space.
• SQL Server creates default free-space threshold monitoring on the log segment, allowing you to create additional free-space monitoring on the log and data portions of the database.

The size of the device required for the transaction log varies according to the amount of update activity and the frequency of transaction log dumps. In general, allocate to the log 10 to 25 percent of the space you allocate to the database itself.

Database Segments

Segments are named subsets of the database devices available to a specific database. A named segment functions as a label that points to one or more database devices. Use segment names when creating databases or indexes to place tables or indexes on specific database devices.

Using segments can improve SQL Server performance and give increased flexibility over placement, size, and space usage of specific database objects. For example:

• If, by using segments, a table is placed on one device and its index is on a device on another disk controller, disk head travel, and thus the time required to read or write to the disk, can be reduced.
• If you use segments to split a large, heavily used table across devices on two separate disk controllers, read and write times can improve.
• If you place tables and indexes on specific segments, the space available to the devices is defined by those segments, and other objects cannot contend with them for that space. If necessary, segments can be extended to include additional devices.
• When you specify database segments, you can establish thresholds that trigger a warning when space becomes low on a specific database segment.

You can use SQL Server Manager to create and manage segments. Or you can use Sybase-provided system stored procedures, if you prefer.

Disk Mirroring

Mirroring a disk duplicates the database; all writes to the device are copied to a separate physical device. If one of the devices fails, the other contains an up-to-date copy of all transactions. Disk mirroring can provide recovery of SQL Server databases without downtime in the event of media failure.

When a read or write to a mirrored device fails, the bad device becomes unmirrored, and SQL Server displays an error message. SQL Server continues to run, unmirrored.

Deciding Whether to Implement Mirroring

The decision about whether to implement disk mirroring should be based on your analysis of the trade-offs described in this section. You can also consider mirroring just the transaction logs, all devices on a server, or selected devices.

Disk mirroring involves the following cost and performance trade-offs:

• Impact on performance

  Mirroring user databases increases the time needed to write transactions because they are written to two disks.

• Cost of downtime

  If disk mirroring is not implemented, recovery of SQL Server in the event of media or power failures causes system downtime that will impact productivity of your users.

• Speed of recovery

  You can achieve recovery without downtime when the master and user databases (including logs) are mirrored, and you can recover without the need to reload transaction logs.

• Storage space

  Immediate recovery requires full redundancy (all databases and logs mirrored); this consumes disk space.

Using SQL Server Manager, you can issue any of the disk mirroring commands while the devices are in use, including starting or stopping the mirroring operation.

Customizing and Fine-Tuning SQL Server

The System Administrator, System Security Officer, or Database Owner can monitor the performance of SQL Server and customize and fine-tune its operation.

The variables that affect SQL Server and database behavior are:

• Configuration parameters, which affect the environment for the entire SQL Server
• Database options, which affect the behavior of a single database
• Transact-SQL set statements, which affect the current user session or an executing stored procedure or trigger

SQL Server for Workplace UNIX provides the following applications for performance tuning:

• SQL Server Manager to change SQL Server configuration parameters and database settings
• SQL Server Monitor to monitor and report information on SQL Server performance

SQL Server for Workplace UNIX also provides a query tool, wisql, which you can use to execute stored procedures that change configuration parameters and database options.

Configuration Parameters

Configuration parameters control various aspects of SQL Server's operating environment. When you install SQL Server, default values for these parameters are established, based on assumptions about how SQL Server is generally used. You can change the values of these parameters to fine-tune the performance of SQL Server.

Configuration parameters can be grouped according to the area of SQL Server behavior they affect:

• Backup and recovery
• Cache management
• Disk I/O
• Languages
• Lock management
• Memory use
• Network communications
• Operating system resources
• Physical memory
• Processors
• SQL Server administration
• User environment
• General characteristics

System Administrators and System Security Officers can modify configuration parameters using SQL Server Manager or wisql.

Database Options

Database options control many aspects of database behavior, such as:

• The behavior of transactions
• Defaults for table columns
• Restrictions to user access
• Performance of recovery and bulk copy operations
• Log behavior

System Administrators and Database Owners can configure the database option settings for a SQL Server database using SQL Server Manager or wisql.

The set Statement

The Transact-SQL set statement tells SQL Server how to handle queries and stored procedures. These query processing options are set for the duration of the user's work session. If a stored procedure contains a set statement, its effect lasts while the stored procedure executes. No permissions are required to use the set statement.

For example, the following statement causes SQL Server to stop processing queries after it returns the first ten rows:

set rowcount 10

You can execute the set statement from wisql.

Monitoring SQL Server Activity

SQL Server for Workplace UNIX provides two facilities for monitoring SQL Server activity: SQL Server Monitor and a set of predefined global variables.

SQL Server Monitor

SQL Server Monitor is a tool for gathering and displaying performance data. SQL Server Monitor helps you evaluate SQL Server operation, isolate the causes of performance problems, and gather statistics for capacity planning.

Monitor Components

SQL Server Monitor consists of four components:

• Monitor Server is an Open Server(TM) application that reads SQL Server performance data from shared memory. Monitor Server collects data in a manner that has a minimal impact on SQL Server performance.
• Monitor Client is a Windows application that displays performance data generated by Monitor Server.
• Monitor Historical Server is an Open Server application that records SQL Server performance data for deferred analysis.
• Monitor Client Library is an application programming interface that provides routines for acquiring and integrating performance data from Monitor Server and SQL Server. You can use this API for building custom SQL Server monitoring applications.

What Is Monitored?

SQL Server Monitor provides system information about

• Cache management
• Device I/O
• Memory allocation
• Network activity
• Object lock status
• Object page I/O
• Performance trends
• Process activity
• Process detail
• Process lock activity
• Stored procedure activity
• Transaction activity

Advantages of SQL Server Monitor

SQL Server Monitor helps you manage SQL Server by:

• Presenting an overall performance picture
• Establishing a performance baseline
• Pinpointing the nature of problems
• Giving feedback on the effect of tuning activities
• Providing session and display management features

Monitoring with Global Variables

SQL Server uses a series of pre-defined global variables that hold system-supplied values that track SQL Server activity. These variables are distinguished from local variables by having two @ signs preceding their names (for example, @@error). The global variable @@error, as an example, contains the number of the last error message generated by SQL Server.

You can find the values of these variables in either of two ways:

• Use the sp_monitor system procedure
• Query the variables directly

Execute the sp_monitor procedure to return the current values of global variables. You can monitor how they have changed since the last time you ran the procedure.

Auditing SQL Server Activity

SQL Server can generate an audit trail to record system activity. The audit trail can detect all activity within SQL Server and its databases.

By examining the audit trail, the System Security Officer can inspect patterns of access to objects in databases and can monitor the activity of specific users. In the role of System Security Officer, you can:

• Execute the auditing system procedures
• Read the audit trail
• Access the audit database

The SQL Server audit system consists of these components:

• The auditing (sybsecur.dat) database, which is created using the Server Config utility. This database contains two important system tables used specifically for auditing:
  • sysaudits, which contains the audit trail
  • sysauditoptions, which contains the global auditing choices
• The audit queue, which holds information about an audited event in memory. The audit process then adds the information to the audit trail.

Auditing Options

You can audit the following SQL Server activities:

• Successful or failed login attempts by all users or by individual users, including remote logins
• Logouts from SQL Server, including unintentional logouts such as dropped connections
• Restarting of SQL Server
• Turning roles on and off
• The use of statements requiring a specific role for execution
• Fatal errors (errors that disconnect from SQL Server and require the client program to be restarted), nonfatal errors, or both kinds
• Attempts to access tables and views
• Use of certain statements within a database
• Execution of stored procedures and triggers

Getting More Information

Sources of information for topics discussed in this chapter are listed below.

Topic	Source(s) of Information
Aliases	SQL Server System Administration Guide
Auditing SQL Server	Configuring Sybase SQL Server for your platform SQL Server System Administration Guide
Configuration, default installation	Installing Sybase SQL Server for Workplace UNIX Configuring Sybase SQL Server for your platform SQL Server System Administration Guide
Configuration parameters	SQL Server System Administration Guide SQL Server Manager User's Guide
Configuring SQL Server	Configuring Sybase SQL Server for your platform SQL Server System Administration Guide
Database devices	Configuring Sybase SQL Server for your platform SQL Server System Administration Guide
Default devices	Configuring Sybase SQL Server for your platform SQL Server System Administration Guide
Enlarging database	SQL Server System Administration Guide
Error log	Configuring Sybase SQL Server for your platform
Groups	SQL Server Security Administration Guide
Indexes	SQL Server System Administration Guide
Initializing SQL Server	SQL Server System Administration Guide
Installing and configuring SQL Server	Installing Sybase SQL Server for Workplace UNIX Configuring Sybase SQL Server for your platform SQL Server System Administration Guide
Localization	SQL Server System Administration Guide Transact-SQL User's Guide
Location of database	Configuring Sybase SQL Server for your platform SQL Server System Administration Guide
Locking a user login	SQL Server Security Administration Guide
Monitoring SQL Server	SQL Server Monitor Client User's Guide for Microsoft Windows SQL Server Monitor User's Guide SQL Server Monitor Historical Server User's Guide SQL Server Monitor Client Library Programmer's Guide Configuring Sybase SQL Server for your platform SQL Server System Administration Guide
Performance optimization	SQL Server Performance and Tuning Guide
Remote stored procedure calls	SQL Server Security Administration Guide SQL Server Reference Manual
Resource management	SQL Server System Administration Guide SQL Server Performance and Tuning Guide
SQL Server Manager	SQL Server Manager User's Guide
Transaction log	SQL Server System Administration Guide
User information	SQL Server Manager User's Guide SQL Server Reference Manual Transact-SQL User's Guide
Users	SQL Server System Administration Guide
Utilities	SQL Server Utility Programs for UNIX