- Shopping Bag ( 0 items )
Other sellers (Paperback)
-
All (6) from $1.99
-
Used (6) from $1.99
Ships from: Sparks, NV
Usually ships in 1-2 business days
Ships from: Ventura, CA
Usually ships in 1-2 business days
Ships from: McKinney, TX
Usually ships in 1-2 business days
Ships from: Fort Mill, SC
Usually ships in 1-2 business days
Ships from: Newport, United Kingdom
Usually ships in 1-2 business days
Ships from: Chatham, NJ
Usually ships in 1-2 business days
| Introduction | 1 | |
| Pt. I | The SQL Server Architecture | 11 |
| 1 | Changes in the Client/Server Environment | 13 |
| 2 | Understanding the Specifics of Microsoft Client/Server Architecture | 21 |
| 3 | Using SQL Enterprise Manager Features | 33 |
| 4 | Defining Systems Administration and Naming Standards | 63 |
| Pt. II | Transact-SQL Programming and Transaction Management | 81 |
| 5 | Using Transact-SQL in SQL Server 6.5 | 83 |
| 6 | Triggers | 151 |
| 7 | Stored Procedures | 179 |
| 8 | Transaction Management and Locking | 213 |
| Pt. III | System Administration | 265 |
| 9 | System and Database Administration | 267 |
| 10 | Defining Physical and Mirror Devices | 279 |
| 11 | Defining, Altering, and Maintaining Databases and Logs | 305 |
| 12 | Security and User Administration | 345 |
| 13 | DBCC | 377 |
| 14 | SQL Server Database Backup and Restoration | 423 |
| 15 | Replication Options and Implementation | 457 |
| 16 | Administrating Very Large SQL Server Databases | 489 |
| Pt. IV | Performance and Tuning | 519 |
| 17 | Understanding SQL Server Storage Structures | 521 |
| 18 | Database Design and Performance | 571 |
| 19 | Configuring, Tuning, and Optimizing SQL Server Options | 599 |
| 20 | Designing Indexes for Performance | 645 |
| 21 | Understanding Query Optimization | 679 |
| 22 | Analyzing Query Plans | 725 |
| 23 | Managing the SQL Server Optimizer | 799 |
| 24 | Application Design for Performance | 821 |
| 25 | Miscellaneous Performance Topics | 853 |
| Pt. V | Microsoft SQL Server, Microsoft BackOffice, and the Corporate Environment | 885 |
| 26 | Distributed Transactions and Microsoft Transaction Server | 887 |
| 27 | Optimizing DBA Tasks | 911 |
| 28 | Business Intelligence: Data Warehousing and OLAP | 931 |
| 29 | Clustering and Large Memory Configurations | 949 |
| 30 | Remote Server Management | 965 |
| 31 | The MS SQL Server Distributed Management Framework | 979 |
| Pt. VI | SQL Server and the World Wide Web | 999 |
| 32 | Building Dynamic Web Pages with SQL Server | 1001 |
| 33 | Using ActiveX Data Objects (ADO) with SQL Server | 1023 |
| 34 | Integrating SQL Server with Advanced Web Technologies | 1057 |
| Pt. VII | MS SQL Server 6.5 Certification | 1087 |
| 35 | A Guide to Certification | 1089 |
| 36 | Sample Questions for Exam Preparation | 1097 |
| Appendix | 1155 | |
| SQL Server Installation and Connectivity | 1157 | |
| Index | 1185 |
[Figures are not included in this sample chapter]
The SQL Enterprise Manager (SQL-EM) merges the functions of several system management
utilities for SQL Server into a unified application, keeping in sync with the Explorer
view of Windows 95 and Windows NT 4.0. It follows the tree metaphor to manage the
various SQL Servers available on an enterprise network and can be used by any user
with access to SQL Server. Permissions to individual SQL Server objects are controlled
by the access rights given to the individual user ID.
SQL-EM provides the tools to manage Version 6.0 and 6.5 servers, a function not
available through the Version 6.0 SQL-EM. It can be used to manage devices, databases,
tables, logins, replication, security and much more. It provides the functions of
starting, pausing, and stopping the SQL Server previously available through the command
line, SQL Executive, or the NT Services dialog box. Along with the management of
devices and databases, SQL-EM also provides the tools to manage the database objects
such as views, tables, and indexes.
This application is a powerful tool and deserves attention, especially for the
database administrator (DBA) who previously had to rely on a set of tools from different
vendors to manage one or more SQL Servers.
NOTE: SQL-EM uses several icons to denote object types as well as the state
of services. To learn more about the icons or to have a lookup table handy, choose
File | Toggle Legend from the SQL-EM menu.
SQL-EM requires a few steps before it can be used. The tree view when SQL-EM is
first launched shows a globe listed as Microsoft SQL Servers with a branch off of
it labeled SQL 6.5. This is the starting point of SQL-EM and the place where the
administrator configures SQL-EM for use. This is where a server is registered with
SQL-EM for the availability of management control. Figure 3.1 shows a sample of the
default view.
Registration of a SQL Server provides SQL-EM with the necessary login and password
information so that it can send the appropriate commands to the SQL Server as they're
executed through SQL-EM.
Modular management dictates the placement of servers in well-defined groups. As
SQL-EM starts, it automatically provides the SQL 6.5 group. This is where you'd ideally
place all Version 6.5 servers, but it can also be used to place groups based on organizational
divisions.
FIGURE 3.1.
The default SQL-EM view.
Similar to setting up domains under NT, groups can be set up for organizational
or functional divisions. In a manufacturing organization, for example, you might
set up the following groups to logically separate the servers for management:
To create a server group, choose Server | Server Groups from the SQL-EM menu.
This action brings up a Manage Server Groups dialog box. For the manufacturing example,
you'd need to create a top-level group for the three departments. To accomplish that,
you type the names of the new groups and click Add after each one. The groups appear
immediately in the SQL-EM window.
If a group was being set up for a department such as Information Systems, you
could conceivably have subgroups within the top-level groups. For subgroups, select
the parent group in the Manage Server dialog box and choose the Sub-Group Of radio
button. Any names added will appear under the parent group, as shown in Figure 3.2.
After the groups have been created, individual SQL Servers can be placed within
them. This placing of a server is called registering a server and is done
by choosing Server | Register Server from the SQL-EM menu, and then providing the
server name, the login specifics, and group membership information.
FIGURE 3.2.
Organizational departments set up as groups with subgroups.
The server name is the name of the server created during installation of the server.
Type the name directly into the Server drop down list box. If the server was installed
with integrated or mixed security mode, the Trusted Connection option may be used
to validate the connection, using Windows NT security. If mixed/integrated security
wasn't used, a SQL Server ID and password are required in the appropriate edit boxes.
For the DBA, this is usually sa and the appropriate password.
Registration information is now complete. A registered server appears in the SQL-EM
window--notice the ATG (SQL Server 6.50) group in Figure 3.2.
Errors after clicking the Register button happen due to one of two reasons:
SQL-EM gives you the option of registration even if failures like these occur.
It's recommended, however, that problems like these be resolved before continuing
the registration process, as the problems will occur in almost every SQL Server application
if left unresolved.
Data is an asset of almost any organization and should be protected. SQL Server,
an enterprise server, provides a large number of security options manageable from
SQL-EM. You can define groups of users and specify individual rights for each user
at a very micro level.
A group is a set of users who have certain rights within a database. Much
like network accounts, all users belong to a global group called public
but, unlike network accounts, each user can belong to only one other SQL Server
group.
Groups are used to implement rights across a set of users, by setting the permissions
for the group rather than for each individual user. Groups are created for databases,
so, from the SQL Server listed under a group, open a database tab and click Groups/Users.
The object Groups/Users can now be modified.
In SQL-EM, choose Manage | Groups to reveal a dialog box where one or more user
groups can be added, and each new group can be modified to contain one or more database
users. (You can also delete a group by "dropping" it from the same dialog
box.) Figure 3.3 shows the Manage Groups dialog box.
FIGURE 3.3.
Group management.
Database users can also be added from the Manage menu. Choose Logins to show the
dialog box in Figure 3.4, where you can add users and assign permissions to databases.
Their group memberships can also be set here.
FIGURE 3.4.
The Manage Logins dialog box.
A device is a virtual drive where objects such as tables, indexes, triggers,
views, stored procedures, and the like are stored and managed through other databases.
They can be used for a few different reasons, as described in the following sections.
A database device stores actual data, indexes, and other database objects
used by users of the database. To create such a device, choose Manage | Database
Devices from the SQL-EM menu, to display the current devices and the space consumed
by each device (see Figure 3.5). Devices can be added, modified, or deleted by using
the buttons on the toolbar or the options presented under the File menu. The toolbar
buttons are self-explanatory and provide Tooltips.
FIGURE 3.5.
Viewing the devices on the current server.
To add a device, click the appropriate button or choose File | New Device to open
the New Database Device dialog box (shown in Figure 3.6).
Creating a database device is a crucial step and should be handled carefully.
Because a device is a repository of all database objects, its size and properties
should be carefully evaluated. The main areas of concern are as follows:
FIGURE 3.6.
The New Database Device dialog box.
Devices can be for different kinds of uses and are created accordingly. They can
be used to store databases, transaction logs, and backups. Transaction log devices
are created in the same way as databases devices (see the preceding section). A backup
device is slightly different in that it could be either a file-based device or a
tape-drive-based device. To create a backup device, right-click the Backup Devices
folder and select New Backup Device from the pop-up menu to display the dialog box
shown in Figure 3.7.
FIGURE 3.7.
Creating a backup device.
Creating a disk-based device isn't much different from creating a database device
or transaction log device, but a tape device has an option concerned with ANSI headers
on the tape. If the tape is to be used with SQL Server only, it's safe to ignore
the tape headers and go straight for the backup. A tape device is only virtually
created until used for the first time, when the device information is actually
written to tape. The instruction to skip headers during backup overrides any skip
header options that may have been set during the creation of the device.
The ANSI headers on a tape contain such vital information as tape expiration (which
specifies when a tape should tell the user of the end of its useful life), along
with any user restrictions that may be set on the tape. A new tape typically has
a blank ANSI header; once used by any application, including SQL Server, the header
will contain information about the backup.
SQL Server creates the headers, whether or not the option to skip headers is selected,
once it has been told to back up to tape.
SQL-EM lends itself to being an integrated server manager and provides a good
set of tools to do just that. The following sections discuss some of the common server
management tasks that can help coordinate the DBA's server-management tasks.
At any given time, a SQL Server is working on several tasks. When troubleshooting
problems, monitoring for shutdowns, or just keeping tabs on the pulse of the server,
it's necessary to see exactly what the server is doing. The menu sequence Server
| Current Activity provides the DBA with just a table of tasks by user, as shown
in Figure 3.8.
FIGURE 3.8.
Current activity on the server.
From this monitor window, you can view detailed user activity by clicking the
Detail Activity tab. If the DBA notices illegal or inefficient activities, a message
can be sent to the client workstation (if messaging is enabled on the client). In
more drastic instances, where a user's processes might be interfering with the normal
operation of the SQL Server, the DBA can kill the user's process from this window.
SQL Server provides a stable and thorough mechanism for scheduling processes to
minimize the impact of systems administration on normal operation of the server.
Scheduled processes might include creating a device, re-index databases, importing
or exporting data--any process that lends itself to a non-immediate need. To manage
scheduling, choose Server | Scheduled Tasks (see Figure 3.9).
FIGURE 3.9.
Managing scheduled tasks.
To create a new task, choose File | New Task and specify the name for the task.
If the task isn't a Transact-SQL statement, specify the type of task. The database
that the task will affect is selected next, followed by the command to be executed.
For example, you could use a command like this to back up the pubs database
to the tape device Tape1:
dump database pubs To Tape1
A schedule can be set by using the Change button if the task is to be run at times
other than the default time selected by the SQL Server. By clicking the Options button,
you can set other options related to the schedule--such as e-mail/pager notification,
event logging, and retry intervals. Tasks that are currently running can be viewed
by clicking the Running Tasks tab; you can also cancel operations from the same location.
You can optimize your use of schedules with a little advance planning. Experiment
with creating and testing schedules for various tasks on a test server before modifying
data in a production database.
SQL Server's operation and efficiency can be affected by many events. The DBA
may want to know of certain events that would affect the operation of the SQL Server
significantly; and the way to accomplish that objective is with alerts. Alerts
are messages sent by SQL Server through e-mail or by pager to a DBA-specified user.
(The e-mail operation happens through Microsoft Mail's SQL Server e-mail account.)
The alert processes require both SQL Server and SQL Executive to be running. (Microsoft
Mail, also needed for alerts, is included with SQL Server.) To create an alert, choose
File | Alert/Operators to display the dialog box shown in Figure 3.10.
FIGURE 3.10.
Managing alerts and operators.
Adding alerts isn't much different from controlling schedules or any other objects.
You can select a new alert either by right-clicking or choosing File | New Alert.
The Manage Alerts and Operators window is similar to the Manage Scheduled Tasks window,
but differs in the event-action relationship, and that deserves attention here. In
the Demo: Full msdb example shown in Figure 3.10, for example, the alert
fires if error 1105 occurs. This error occurs when additional space for an object
can't be allocated in a particular database. The error is severe, as it refers to
an "out of space"-like error and should really be sent to an operator or
DBA for immediate check.
The operators are defined with the options on the Operators tab. Details such
as work start/stop times, e-mail/pager methods, and types of alerts assigned to the
operator can be set.
SQL Server starts a new log file at each startup, and keeps tracks of most system
events and messages. This log file is a text file in the SQL\Logs directory,
and can be viewed with any text editor (such as Windows Notepad). You can also view
this log from SQL-EM by choosing Server | Error Log, which displays the current error
log in a window (see Figure 3.11). Prior error logs can be selected using the drop-down
list box called Error Log.
FIGURE 3.11.
The error log for the current server.
Server configuration is probably the most useful area of server management, from
an administration viewpoint. Here is where you modify parameters such as user connections
and memory, security settings, SQL Server boot options, startup parameters, e-mail
parameters, and tape drive support. The Server Configuration/Options dialog box (see
Figure 3.12) also displays a detailed sheet about server parameters and configuration.
To reach the dialog box, choose Server | SQL Server | Configure.
FIGURE 3.12.
Use this dialog box to set server configuration options.
The server options determine how SQL Server starts up or boots at system startup
time or when manually started. These options are necessary to ensure that the server
comes up properly after a system reboot.
SQL Server can be set to auto start when the system reboots. This is similar to
setting up the NT Service Manager to start the SQL Server and SQL Executive processes
automatically on boot. Additional parameters--typically sent through the command
line--can be specified in the Server Configuration/Options dialog box by clicking
the Parameters button. For example, if changing options on SQL Server has made it
impossible to start the SQL Server properly, the -f parameter starts the
SQL Server with a minimal configuration to correct the configuration problems.
SQL Server provides three different setups for managing security: Standard, NT
Integrated, and Mixed. The Security Options dialog box allows for setting those options
and for auditing connection successes and/or failures. For further information about
security options, see Chapter 27, "Security and User Administration."
SQL Server configuration changes such as those in memory, database sizes, locks,
maximum open databases, or network packet size can be made through Transact-SQL,
but the SQL-EM interface makes it easier to make changes.
When adding additional RAM to the system, you need to configure the server to
use the additional memory added. To accomplish that objective, scroll down to the
Memory tab and change the current value to the new value.
NOTE: The Memory number isn't the actual number of kilobytes, but rather the
number of 2KB units of memory. So, if you add 32MB of RAM to the server, you would
add 16384 (32MB * 1024 /2) to the value already in the cell.
SQL-EM provides several tools to easily administer tasks such as backup-and-restore
or database transfers. The SQL Transfer Manager from Version 4.2 has been replaced
by a Database/Object Transfer tool, and the dump database command syntax
has been made easier with the Database Backup/Restore tool. An external query utility,
ISQL/w, is still provided with SQL Server, but its functionality has also
been added to SQL-EM. These tools are available under the Tools menu for SQL-EM.
Unless you have access to a backup tool such as Arcada Backup Exec 6.1 or above,
which has tools to back up SQL Server databases during a normal system backup, you'll
probably rely on the SQL-EM Database Backup/Restore capability for your daily backups
(see Figure 3.13). The options on the Backup tab allow for selection of one or more
tables in a database to be backed up to a backup device, such as disk or tape. The
scheduling options allow as much flexibility as the task-scheduling features of SQL-EM,
discussed earlier in the chapter.
When manually transferring a subset of data or entire databases from one server
to another, you can use the SQL-EM Database Object/Transfer tool to copy the structure,
data, dependencies, etc. of SQL Server objects such as databases from one server
to another. (See Figure 3.14.) As with most other server-intensive tasks, this can
be scheduled for running at low-usage times.
FIGURE 3.13.
The Database Backup/Restore dialog box.
For routine database transfer and copying, you may want to consider replicat¿é,
c)Ûcussed im-chapter 36, "Introduction to SQL Server 6.5 Replication."
SQL-EM integrates the dialog box from ISQL/w to provide a query tool
within the SQL-EM environment. The tool can be used to create, save, and execute
any valid query on SQL Server, just like ISQL. The tool duplicates the function
of ISQL, but is still useful because of its integration into SQL-EM.
FIGURE 3.14.
Use this dialog box to manage database or object transfer from one server to another.
Several database management tools have been integrated from the old SQL Object
Manager into SQL-EM. These tools provide for the creation of tables, rules, stored
procedures, triggers, and indexes. Some of these functions are discussed in the following
sections.
Tables are an integral part of SQL Server and can easily be created using SQL-EM.
To create a table, select a database within which the table will be created. If a
database needs to be created, you can accomplish that by right-clicking the Databases
folder and selecting New Database from the pop-up menu. The New Table command brings
up a dialog box like the one in Figure 3.15, where fields can be created for the
table.
FIGURE 3.15.
Creating fields for a table.
A natural successor to the creation of a table is the process of placing indexes
on columns in the table. The indexes are created by using the Manage | Indexes menu
command, which displays the dialog box shown in Figure 3.16.
FIGURE 3.16.
Creating indexes for the table.
The attributes for the indexes are set here, and an estimate of the index's impact
on size of the database is displayed. Name the index with the Index drop-down list
box; the Build button starts the process to create the index.
These database objects are created using SQL queries through SQL-EM, ISQL,
or other database management products. Once a database is selected in the Server
Manager window, selecting the menu option Manage allows for selection of triggers,
views, and stored procedures. Selecting the menu item Stored Procedures, for example,
brings up a window into which a query can be typed or a preexisting stored procedure
selected from the Procedure drop-down list. (A shell SQL statement related to the
task is placed within the window to start the user on the right track.) An example
of a view-creation window is displayed in Figure 3.17.
FIGURE 3.17.
Creating views.
When default data types such as char or binary don't quite suit
the task at hand, it's useful to be able to define a data type for use in the database.
In the pubs database, for example, there are three user-defined types. Suppose
that you wanted to add another one, for use as an ID for businesses. Similar to the
SSN for an individual, the IRS has a federal ID for businesses. The SSN follows this
format:
###-##-####
The federal ID is formatted a little differently:
##-#######
To add this custom ID, you'd choose Manage | User-Defined Datatypes and type the
values shown in Figure 3.18.
FIGURE 3.18.
User-defined data types.
When each database is created in SQL Server, it's necessary to check the permissions
given to users for this database. The permissions can be checked by user or by object,
and are set with the Object | Object Permissions menu sequence.
To set the permissions by user, choose Object | Object Permissions and click the
By User tab in the resulting dialog box. The dialog box lists all objects within
the current database, and provides a drop-down list box for users and groups. To
assign a particular right to a user or group, select the user/group from the drop-down
list box and click within the cell(s) containing the desired right(s).
To limit the object list to specific types of objects, such as tables or views,
check or uncheck the appropriate boxes in the Object Filters section of the dialog
box.
For example, to assign the select right to all members of the public
group for the customers table, click within the cell on the Customers
row under the Select column (see Figure 3.19). A green check mark will appear,
indicating a right assigned but pending until the Set button is clicked. Clicking
the same cell again allows you to change the right to Revoke or Cancel.
Permissions can also be assigned by object when most users will have the same
right to the object. For example, allowing all users to read data from a table used
only for lookups can be easily accomplished by giving the select right to
the lookup table. To grant the select right to Authors, click the
cell corresponding to Select and public as displayed in Figure
3.20.
FIGURE 3.19.
Assigning object rights to users.
FIGURE 3.20.
Assignment of object rights.
Replication is the process of duplicating tables and transactions from one database
to another, from a local to another local or to a remote server. SQL-EM provides
an easy-to-use interface to install replication and set up the publishers and subscribers
of data.
To install replication, your SQL Server should have at least 16MB of RAM allocated
to it. When less than 16MB is allocated to the SQL Server, an attempt to install
replication results in an error message asking for more memory.
Choose Server | Replication Configuration | Install Publishing from the SQL-EM
menu to open the dialog box in Figure 3.21. If the replicated database is local,
select the Local radio button and fill in the names for the new database and device
parameters. For a remote database, select the remote distribution server from the
drop-down list.
FIGURE 3.21.
Configuring SQL Server for replication.
To enable subscribing, choose Server | Replication Configuration | Subscribing
from the menu; then select the publishing servers to use for subscriptions, followed
by the selection of the subscribed databases.
Once set up, the publications and subscriptions can be managed through the Manage
menu. This option allows for adding, editing, or deleting of replication objects.
See Chapter 36 for further details on subscriptions and publications.
A discussion of SQL-EM would be incomplete without a reference to the plethora
of online help available through its Help menus. The SQL-EM Help itself gives ample
ideas about the use of the tool and the Transact-SQL help provides guidance through
writing SQL scripts for a variety of tasks. For the dedicated administrator, there
is also a "Tip of the Day" feature that has crept into SQL-EM, and provides
tidbits of information on the use and utility of SQL-EM.
A welcome change from the wide array of tools of yesteryear, the SQL-EM package
is complete and efficient. If you have been maintaining your SQL Server from the
command line, this toolkit will be a refreshing change!
© Copyright, Macmillan Computer Publishing. All rights reserved.
Overview