Microsoft SQL Server 6.5 Unleashed

Overview

This comprehensive reference details the steps needed to plan, design, install, administer, and tune large and small databases. In many cases, the reader will use the techniques in this tome to create and manage their own complex environment.
  • Covers programming topics, including SQL, data structures, programming constructs, stored procedures, referential integrity, large table strategies, and more
  • Includes updates to cover all new features of ...
See more details below
Available through our Marketplace sellers.
Other sellers (Paperback)
  • All (8) from $1.99   
  • Used (8) from $1.99   
Close
Sort by
Page 1 of 1
Showing All
Note: Marketplace items are not eligible for any BN.com coupons and promotions
$1.99
Seller since 2005

Feedback rating:

(21870)

Condition:

New — never opened or used in original packaging.

Like New — packaging may have been opened. A "Like New" item is suitable to give as a gift.

Very Good — may have minor signs of wear on packaging but item works perfectly and has no damage.

Good — item is in good condition but packaging may have signs of shelf wear/aging or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Acceptable — item is in working order but may show signs of wear such as scratches or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Used — An item that has been opened and may show signs of wear. All specific defects should be noted in the Comments section associated with each item.

Refurbished — A used item that has been renewed or updated and verified to be in proper working condition. Not necessarily completed by the original manufacturer.

Good
Indianapolis, IN 1998-05-01 Trade Paperback 3rd ed. Good

Ships from: Sparks, NV

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$1.99
Seller since 2008

Feedback rating:

(585)

Condition: Good
Indianapolis, IN 1998 Other 3rd ed. Good. Unleashed.

Ships from: Toledo, OH

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$1.99
Seller since 2008

Feedback rating:

(585)

Condition: Good
Indianapolis, IN 1998 Other 3rd ed. Good. Unleashed.

Ships from: Toledo, OH

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$1.99
Seller since 2005

Feedback rating:

(1130)

Condition: Good
1998 Paperback Good Binding somewhat worn. Some marks.

Ships from: Ventura, CA

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$3.89
Seller since 2014

Feedback rating:

(0)

Condition: Good
1998 Paperback Good Connecting readers with great books since 1972. Used books may not include companion materials, some shelf wear, may contain highlighting/notes, and may not ... include cd-rom or access codes. Customer service is our top priority! Read more Show Less

Ships from: McKinney, TX

Usually ships in 1-2 business days

  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$26.99
Seller since 2014

Feedback rating:

(37)

Condition: Good
Buy with Confidence. Excellent Customer Support. We ship from multiple US locations. No CD, DVD or Access Code Included.

Ships from: Fort Mill, SC

Usually ships in 1-2 business days

  • Standard, 48 States
$27.79
Seller since 2009

Feedback rating:

(0)

Condition: Very Good
1998 Paperback Very Good 8vo-over 7?-9?" tall SQL Server 6.5 Unleashed: Third edition. The comprehensive solution. Very large paperback, glossy covers in very good+ condition. ... V. slightly rubbed to edges and creased to rear cover. *****PLEASE NOTE: This item is shipping from an authorized seller in Europe. In the event that a return is necessary, you will be able to return your item within the US. To learn more about our European sellers and policies see the BookQuest FAQ section***** Read more Show Less

Ships from: Newport, United Kingdom

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
$34.29
Seller since 2014

Feedback rating:

(273)

Condition: Good
Possible retired library copy, some have markings or writing. May or may not include accessories such as CD or access codes.

Ships from: Chatham, NJ

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
Page 1 of 1
Showing All
Close
Sort by
Sending request ...

Overview

This comprehensive reference details the steps needed to plan, design, install, administer, and tune large and small databases. In many cases, the reader will use the techniques in this tome to create and manage their own complex environment.
  • Covers programming topics, including SQL, data structures, programming constructs, stored procedures, referential integrity, large table strategies, and more
  • Includes updates to cover all new features of SQL Server 6.5 including the new transaction processing monitor and Internet / database connectivity through SQL Server's new Web Wizard
  • CD-ROM includes source code, libraries, and administration tools
Read More Show Less

Editorial Reviews

Booknews
New edition of a guide to unleashing the full potential of SQL Server 6.5. Provides detailed information on how to build web pages, design indexes, master VLDB SQL Server administration, analyze database design issue and performance implications, avoid pitfalls of complex queries and locking, understand storage structures, apply large memory configurations, manage Query Optimizer, and implement effective distribution, replication, and clustering techniques. The included CD- ROM contains bonus chapters on open client programming, and BigPubs database for testing and tuning performance. Annotation c. by Book News, Inc., Portland, Or.
Read More Show Less

Product Details

  • ISBN-13: 9780672311901
  • Publisher: Sams
  • Publication date: 11/1/1997
  • Series: Unleashed Series
  • Edition description: 3RD BK&CDR
  • Edition number: 1
  • Pages: 1272
  • Product dimensions: 7.34 (w) x 9.07 (h) x 2.07 (d)

Table of Contents

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
Read More Show Less

First Chapter









[Figures are not included in this sample chapter]


Microsoft SQL Server 6.5 Unleashed






- 3 -

Introduction to the SQL Enterprise Manager


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.



Getting Started with SQL-EM


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.

Server Groups


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:


  • Production
  • Accounting
  • Engineering

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.

Registering Servers


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:


  • Invalid login. If the standard security mode is in use, make sure that
    the password and user ID are correct. If integrated or mixed mode is selected, make
    sure that the server was installed with that form of security.



  • Server not found. This problem happens due to network protocol incompatibilities.
    Make sure that the server and client are set to use the same protocol.

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.

Security


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.

Groups


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.

Logins


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.

Devices


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.

Database Devices


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:


  • Size. The size of a device should be carefully set to provide enough room
    for all objects and have room to grow in the near future. The available storage space
    on the hard disk is shown in a graph. The devices shouldn't be too large because
    the larger the devices, the more memory SQL Server is going to require to manage
    them. Similarly, the smaller the device, the more frequent the management required
    to expand it to meet the data storage needs.



  • Name. This is the name that uniquely identifies the device--not necessarily
    the name that users will see. For easier administration, however, this name should
    follow some form of standard naming conventions. Appending the word _log
    to a log device is an example that provides readability during maintenance and administration.



  • Creation time. The device-creation process is a fairly disk-intensive
    task and if at all possible shouldn't be performed during production hours. To create
    the device immediately, choose Create Now in the New Database Device dialog box.
    To create the device during low usage or routine maintenance, you may want to click
    the Schedule button and schedule the creation of the device at a certain time and
    date. This option is highly recommended when creating large devices due to the large
    I/O load a device creation process can place on the SQL Server, affecting performance
    of applications using the SQL Server.



  • Mirroring. This is the process whereby a device can be duplicated onto
    another hard disk for online backups. You can select a mirror device on another hard
    drive, but remember that this choice will slow the transactional processes due to
    interaction with two separate devices.

FIGURE 3.6.

The New Database Device dialog box.

Backup Devices


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.

Server Activities


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.

Monitoring the Server


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.

Scheduling Tasks


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.

Alerts


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.

Error Logs


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


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.

Server 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.

Security Options


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."

Changing the Configuration


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.



System Tools


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.

Database Backup/Restore


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.

Database Object/Transfer


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."

Query Tool--ISQL/w


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.

Database Management


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


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.

Indexes


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.

Triggers, Views, and Stored Procedures


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.

User-Defined Data Types


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.

Object Permissions


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.

Setting Permissions by User


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.

Setting Permissions by Object


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


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.

Installing Publishers


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.

Setting Up Subscribers


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.

Help


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.

Summary


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.



Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)