| |
Home |
![[Table of Contents]](/dwicons/b_toc.gif)
|
Introducing Sybase Workplace SQL Server |
![[-]](/dwicons/i_colpse.gif)
| Chapter 3: Using Workplace SQL Server |
This chapter briefly describes common tasks that a user performs with a new SQL Server. Its gives a general idea of how to begin using this product, and is not an exhaustive discussion.
This chapter covers the following topics:
For complete technical information about SQL Server, see the online documentation in SyBooks. You can also consult some of the excellent books available from Sybase Press, such as Understanding Sybase System 11: A Hands-On Approach.
You need to install SQL Server and the other server components on the server machine, and the client components on one or more client machines. On both servers and clients, you install the software into a Sybase installation directory that you can designate. You should also install SyBooks in order to have access to the online documentation.
If you have an earlier release of SQL Server already installed, you may need to upgrade your existing databases before you can access them with the newly installed release.
If your platform supports it, the installation program for server products has a graphical user interface. Figure 3-1 shows the client installation program.
Figure 3-1: The installation screen for NT and Windows client products
To install the software, follow the instructions in the installation guide and release bulletin for your platform. The installation guide is a book packaged with the software media.
SyBooks installation instructions are contained in the Postscript file sybinst.ps in the /release directory on the media and can be printed directly to a Postscript printer prior to installing any software.
Configuration of SQL Server falls into two categories: post-installation configuration and maintenance configuration.
When you install SQL Server, the installation program sets configuration parameters to default values. After installing, you need to configure SQL Server for your installation. This involves:
See the configuration guide for your platform for detailed information about performing these post-installation configuration tasks. The configuration guide is a book packaged with the software media.
Maintenance configuration is concerned more with fine-tuning memory and disk storage. See the System Administration Guide for information about ongoing SQL Server configuration.
To configure SQL Server from a Windows client with a graphical interface, use SQL Server Manager's configuration facility to set configuration parameters and configure network connections. Choose Configuration from the SQL Server Manager's Server menu.
Those who prefer a command line interface can set configuration parameters by invoking the sp_configure system procedure from isql. System procedures are fully documented in the System Administration Guide.
There are two stages of database design: logical design and physical design. In logical design, you define the entities that will be represented by tables in the database, choose the attributes of those entities (which will be represented by columns in the table), and determine how the entities are interrelated through the designation of primary and foreign keys.
Logical design can be accomplished independently of the particular relational database vendor that your organization chooses to supply its software. Logical design should rely heavily on input from the intended users of the databases, not just database and software experts. Ideally, you will have completed the logical design of your user databases before you purchase and install SQL Server.
In the physical design stage, you map the logical design to the Transact-SQL data definition commands that will actually create the databases on the server. These are commands like create database, create table, create view, create rule, and so on.
You can produce a physical design manually or, if the databases are large and complex, you can use one of the many database design software tools on the market such as S-Designor(TM).
The output of the physical design effort is a collection of Transact-SQL commands that create databases and, within the databases, database objects. To create the databases, you execute these create commands on the server.
An easy way to create databases and database objects is to use the database management tool SQL Server Manager. You specify information about the database you want to create in the SQL Server Manager command dialog box. Figure 3-2 shows the create database dialog box, which appears when you choose Create Database from the Server menu.
Figure 3-2: One of SQL Server Manager's command dialog boxes
It is also possible, of course, to create databases at the isql command line using the Transact-SQL create commands. Since most databases consist of several tables, if you use the command line method it is a good idea to save the original data definition commands in a script file that can be run to re-create consistent images of the database as many times as necessary.
After the database tables have been created, you can fill them with data. You may have to insert the data manually using either the Transact-SQL insert command or a client application that has been written to input the data. In many cases, however, the data already exists either in another vendor's database system or in operating system files.
If the data is in a file, or can be converted to a file, you can copy it into a database table using the bcp bulk copy utility. See the utilities guide for your platform for information on how to use bcp. You can also import data from a file in InfoMaker's Data Manipulation painter.
If the data is in a Sybase database from an earlier release, the original tables may be accessible after they have upgraded. See the description of the upgrade process in the installation guide to determine if the databases are upgradable.
If you are going to load a large amount of initial data into a table after creating it, it is best to create the table indexes after you load the data, because indexes slow performance in commands that insert data. This is because a row must also be added to the index for every row added to the table. However, well-chosen indexes greatly improve performance on commands that retrieve data, so it is advisable to create indexes on tables that are frequently queried.
When you create an index on a table, you have to decide on which table column or combination of columns the index will be based. You also have to decide what type of index to create and whether it should be unique. See the description of the create index command in the SQL Server Reference Manual and the chapter on indexes on the Performance and Tuning Guide to learn about selecting useful indexes.
You can create indexes using the SQL Server Manager command dialog boxes or at the isql command line using the Transact-SQL create index command.
After you input data and create indexes on the tables, you should back up the databases so that you can restore them in the event of a system failure. You should continue to back up data at regular intervals. How often you back up a database depends on how frequently the data is updated and how costly it would be for you to lose it.
Databases that are constantly being modified need to be backed up frequently, while databases containing relatively static data can be backed up less often. You should regularly back up production databases, but you can usually be less diligent about backing up databases used for test and development purposes if you can afford to lose their most recent data.
If you experience a media failure, you can restore the data from the most recent backups using SQL Server's restore facility.
You do not have to shut down SQL Server to back up or restore a database. Backup Server makes it possible to perform online backup and restore operations while SQL Server is running.
You can back up and restore databases and their transaction logs using the SQL Server Manager backup and restore command dialog boxes or at the isql command line using the Transact-SQL dump and load commands.
See the System Administration Guide for information about developing a backup plan and about backing up and restoring both user and system databases.
SQL Server security is based on a three-tiered system that separates server access, database access, and data access.
To be able to connect to a server, a person must have a login on the server. A login does not automatically give the person permission to access data or create database objects, just to connect. It consists of a login name and a password.
To give others access to a server, the Sybase system administrator (called the System Administrator) creates logins using either the SQL Server Manager create login command dialog box or the sp_addlogin system procedure from isql.
To be able to connect to a database, a person must be made a user and have a database user ID. The database username may or may not be the same as the person's login name. The System Administrator or the Database Owner (the person who created the database or was given ownership by the database creator) create database users. You can create users with either the SQL Server Manager create user command dialog box or the sp_adduser system procedure from isql.
In addition to individual users, the System Administrator or Database Owner can create a group of database users in SQL Server. The group is identified by a group name and provides a convenient way to grant and revoke permissions to more than one user in a single statement. For example, you can define a "managers" group or a "payroll" group, assign users to the group, and assign the same privileges to all the users in the group by assigning the privileges to the group as a whole. There is also a system-defined group named "all," of which all database users are members.
You can create a group using the SQL Server Manager create group command dialog box or the sp_addgroup system procedure from isql. When you add a user to the database, you can designate the groups that the user is a member of as one of the options in the sp_adduser system procedure or SQL Server Manager create user command dialog box.
Having a login and username gives an individual access to a server and a database, but it does not give automatic permission to select data from tables or views, modify data in tables, or execute stored procedures. To give a user permission to access a database object, the System Administrator or the owner of the database object (the user who created the object) must explicitly grant the user, or a group in which the user is a member, permission to access that object. They can grant and deny various permissions to other users to use specific database objects with either SQL Server Manager or the Transact-SQL grant and revoke commands from isql.
Most activity between a user and SQL Server involves either querying the data or modifying the data. Querying the data is discussed in "Queries".
Modifying data means one of the following:
If you are executing multiple data modification commands on the same or related data, you can execute them together in a single transaction. A transaction consists of multiple Transact-SQL commands, enclosed between begin and end delimiters, that are executed as an atomic command rather than as separate commands. The tables affected by the transaction are locked so that other users cannot make changes that affect the work being accomplished during the transaction. The transaction as a whole is either entirely committed or entirely uncommitted, ensuring that if the system fails in the middle of an operation, the database is not left in an inconsistent state.
The classic example of a transaction is one that removes money from your savings account (delete) and adds it to your checking account (insert). You would want to perform the delete and the insert in a single transaction, so that if the system failed after the delete but before the insert, the record of the funds would not be lost. If such a failure occurred, the transaction would be rolled back by SQL Server's recovery program and the database would appear as though the delete had never been executed.
If you find that there are commands, or sets of commands, that users will perform frequently, you should consider creating a stored procedure. Even for a single command, a stored procedure usually executes faster because the stored procedure stores the command in a partially processed form. Also, you can control access more securely by giving users permission only to execute the stored procedure without giving them permission to access to all the objects referenced by the commands in the stored procedure. Consult the Transact-SQL User's Guide for more information on creating stored procedures.
You have a number of options for establishing a client interface through which SQL Server users can access data. You can purchase one of many third-party applications, or you can build your own.
If your organization is very small and your information needs are very simple, you may be able to manage by using a set of Transact-SQL statements and creating some stored procedures. Users could perform queries through an isql client and direct the results of a query to a file.
If your needs are more complex and you want to provide a customized interface to users who may not know anything about relational databases or Transact-SQL, you can purchase a client application that has been designed for a particular purpose and runs against a SQL Server database from a third-party software company or a VAR (Value Added Reseller). Look for the "Industry Solutions" page in the "Partners and Solutions" section of Sybase's web site for up-to-date news and reference information about industry-specific applications that run against SQL Server.
If you cannot find a commercial application that meets your needs, you can build a customized, user-friendly client application with a development tool such as PowerBuilder. With such a tool you can create GUI applications that send Transact-SQL commands to access and manipulate SQL Server data. After building your application, you deploy it on the client where it is linked with the Open Client libraries. Typically these tools have their own simplified programming APIs and a graphical interface for program development. Figure 3-3 shows the PowerBuilder development window.
Figure 3-3: PowerBuilder development window
You can also design client applications from scratch using a traditional procedural programming language, such as the C, C++, or COBOL. This method usually requires more time and more skilled software engineers than are needed to develop an application using a development tool.
The advantage of the second approach is that the applications are generally smaller, and in some cases possibly faster because the executable does not have the overhead of the tool. These programs make direct calls to the Open Client library routines to communicate with SQL Server. See the Open Client documentation in the Open Client/Server SyBooks collection for complete information about coding with the Open Client libraries.
After a production database is established, you may want to examine some performance statistics and adjust some performance parameters to tune the system for high performance.
The easiest way to monitor SQL Server performance is with SQL Server Monitor. Monitor Client, which uses data provided by Monitor Server (installed on the same machine as the SQL Server it is monitoring) provides an overall picture of performance with a graphical view of overall performance parameters. It also gives specific details about performance with numeric measurements of cache usage, network traffic, device I/O, and locking activity, which are all discussed in the Performance and Tuning Guide.
Most of your actual tuning efforts ought to address the amount of time it takes for SQL Server to respond to queries. You can achieve high performance by starting out with a well designed database that includes strategic indexes and by learning to work with the SQL Server query optimizer, a SQL Server feature that analyzes queries and database objects and selects the appropriate query plan based on how much time it will take to execute. For more information, see the Performance and Tuning Guide.
|
|