Sams Teach Yourself Microsoft SQL Server 6.5 in 21 Days

Overview

The Teach Yourself MS SQL Server 6.5 in 21 Days covers issues such as: Installing SQL Server, Backup and Restore, Creating User Accounts, Creating Tables, Importing and Exporting Data, Data Integrity, Stored Procedures and Triggers, Distributed Data and Performance, Tuning and Optimization.
  • Learn how to install, design and create a SQL Server database
  • Configure SQL Server ...
See more details below
Available through our Marketplace sellers.
Other sellers (Paperback)
  • All (13) from $1.99   
  • New (2) from $37.49   
  • Used (11) 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
$37.49
Seller since 2014

Feedback rating:

(290)

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.

New
Brand New Item.

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)
$50.00
Seller since 2014

Feedback rating:

(181)

Condition: New
Brand new.

Ships from: acton, MA

Usually ships in 1-2 business days

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

Overview

The Teach Yourself MS SQL Server 6.5 in 21 Days covers issues such as: Installing SQL Server, Backup and Restore, Creating User Accounts, Creating Tables, Importing and Exporting Data, Data Integrity, Stored Procedures and Triggers, Distributed Data and Performance, Tuning and Optimization.
  • Learn how to install, design and create a SQL Server database
  • Configure SQL Server and build effective queries
  • Discover programming features of the Transact-SQL Language
Read More Show Less

Editorial Reviews

Booknews
A hands-on tutorial to the latest SQL Server version. Offers information on how SQL Server stores databases on devices and ways to create devices; protect the server and database from unauthorized connections; back up and restore information and databases; import, export, and manipulate data; create stored procedures to encapsulate business rules; design triggers; set up replication; and how to configure SQL Server. Annotation c. by Book News, Inc., Portland, Or.
Read More Show Less

Product Details

  • ISBN-13: 9780672311383
  • Publisher: Sams
  • Publication date: 3/28/1998
  • Series: Sams Teach Yourself Series
  • Edition number: 1
  • Pages: 636
  • Product dimensions: 7.40 (w) x 9.08 (h) x 1.61 (d)

Table of Contents









[Figures are not included in this sample chapter]


Teach Yourself Microsoft SQL Server 6.5 in 21 Days


Contents



  • Introduction - xxxiii

Week 1 at a Glance



  • Chapter Day 1 - Introduction to SQL Server 6.5 and Relational Databases

    • History of SQL Server
    • What Is a Database?
    • Designing Relational Databases
    • SQL Server and the Client/Server Model
    • Summary
    • Q&A
    • Workshop
    • Exercises




  • Chapter Day 2 - Installing SQL Server 6.5

    • Installing SQL Server 6.5
    • Starting and Stopping the SQL Server and SQL Executive Services
    • SQL Server Is Installed--What Did You Get?
    • Troubleshooting Your Installation
    • Client Software
    • MS-DOS Clients
    • Summary
    • Q&A
    • Workshop




  • Chapter Day 3 - SQL Server Tools and Utilities

    • Books Online
    • The ISQL/w Utility
    • The MSQuery Tool
    • The Client Configuration Utility
    • The SQL Trace Utility
    • SQL Security Manager
    • SQL Server Web Assistant
    • SQL Service Manager
    • SQL Server Setup
    • SQL Performance Monitor
    • README.TXT
    • Microsoft ODBC SQL Server Driver
    • SQL Distributed Management Objects
    • SQL Enterprise Manager
    • Non-Graphical Tools
    • Summary
    • Q&A
    • Workshop




  • Chapter Day 4 - Devices and Databases

    • Introduction to Devices
    • Backup Devices (Dump Devices)
    • Database Devices
    • Device Logical Names
    • Default Database Devices
    • Creating Database Devices Using Transact-SQL
    • Removing Database Devices Using Transact-SQL
    • Removing Database Devices Using Enterprise Manager
    • Expanding Database Devices Using Transact-SQL
    • Expanding Database Devices Using Enterprise Manager
    • Specifying a Default Device
    • Multiple Default Devices
    • Database Device Mirroring
    • Databases
    • Default System Databases
    • Creating Databases
    • Creating Databases Using Transact-SQL
    • Creating Databases Using Enterprise Manager
    • Database Options
    • Database Information
    • System Tables Used in Tracking Databases
    • Deleting Databases
    • Deleting Databases Using Enterprise Manager
    • Enlarging a Database Using Transact-SQL
    • Enlarging a Database Using Enterprise Manager
    • Shrinking a Database Using Transact-SQL
    • Shrinking a Database Using SQL Enterprise Manager
    • Moving the Log to a Separate Device
    • Documenting the Database-Creation Sequence
    • Estimating Storage Requirements
    • Summary
    • Q&A
    • Workshop




  • Chapter Day 5 - Creating Tables

    • Tables
    • Columns
    • Strings
    • Binary Data
    • Integer
    • Approximate and Exact Numeric Datatypes
    • Special Datatypes
    • Date and Time Datatypes
    • Money
    • Automatically Updating Datatypes
    • Synonyms
    • User-Defined Datatypes
    • Create Tables
    • Estimating Database Sizes
    • Summary
    • Q&A
    • Workshop




  • Chapter Day 6 - SQL Server Login and User Security

    • The SQL Server Security Access Model
    • The Security Modes of SQL Server
    • Standard Security
    • Integrated Security
    • Mixed Security
    • Database Access
    • Summary
    • Q&A
    • Workshop




  • Chapter Day 7 - User Permissions

    • Why Use Permissions?
    • Introduction to Database Permissions
    • Levels of Permissions in SQL Server
    • Statement Permissions
    • Object Permissions
    • Effective Rights
    • Ownership Chains
    • Designing a Permission Strategy
    • Summary
    • Q&A
    • Workshop





Chapter Week 1 In Review


Chapter Week 2 At a Glance



  • Chapter Day 8 - Backing Up and Restoring

    • Mirroring, Duplexing, and Striping
    • SQL Server Mirroring
    • How Backup Works
    • Backup Considerations
    • Backing Up User Databases
    • Backing Up System Databases
    • Implementing Backups
    • Backing Up a Database
    • Backing Up Transaction Logs
    • Single-Table Backup
    • Restoring Databases
    • Backup and Recovery Scenarios
    • Recovering the master and msdb Databases
    • Restoring Backups from Previous Versions of SQL Server
    • Summary
    • Q&A
    • Workshop




  • Chapter Day 9 - Importing and Exporting Data

    • Introduction to Moving Data
    • The Transfer Management Interface (TMI)
    • The Bulk Copy Program (BCP)
    • Using Backup and Restore
    • Summary
    • Q&A
    • Workshop




  • Chapter Day 10 - Data Retrieval

    • Simple SELECT Statements
    • Manipulating Data
    • System Functions
    • Data Conversion
    • Choosing Rows
    • Eliminating Duplicate Information
    • Sorting Data Using the ORDER BY Clause
    • Summary
    • Q&A
    • Workshop




  • Chapter Day 11 - Data Retrieval (Advanced Topics)

    • Aggregate Functions
    • Super-Aggregates (ROLLUP and CUBE)
    • Data Correlation
    • MS Query Tool
    • Subqueries
    • Correlated Subqueries
    • SELECT INTO
    • UNION Operator
    • Summary
    • Q&A
    • Workshop




  • Chapter Day 12 - Data Modification

    • Data Modification
    • Inserting
    • Deleting
    • Updating
    • Summary
    • Q&A




  • Chapter Day 13 - Indexing

    • Index Basics
    • SQL Server Indexes
    • Index Usage
    • Index Maintenance
    • Enterprise Manager
    • Index Selection
    • Summary
    • Q&A
    • Workshop




  • Chapter Day 14 - Data Integrity

    • Data Integrity
    • Types of Integrity
    • Traditional Methods of Integrity
    • ANSI Constraints and Declarative-Integrity Mechanisms
    • SQL Enterprise Manager
    • Summary
    • Q&A
    • Workshop





Week 2 In Review


Week 3 at a Glance



  • Chapter Day 15 - Views, Stored Procedures, and Triggers

    • Creating and Manipulating Views
    • Stored Procedures
    • Working with Triggers
    • Summary
    • Q&A
    • Workshop




  • Chapter Day 16 - Programming SQL Server

    • Batches
    • Scripts
    • Transactions and Locking
    • Summary
    • Q&A
    • Workshop




  • Chapter Day 17 - Programming SQL Server (Advanced Topics)

    • Variables
    • Control of Flow
    • Example of a Simple CASE Expression
    • Cursors
    • Summary
    • Q&A
    • Workshop




  • Chapter Day 18 - SQL Server Automation

    • Why Automate SQL Server?
    • Which Scheduling Tool Should You Use?
    • SQL Mail Integration
    • Sending Email from SQL Server
    • Tasks
    • The Database Maintenance Plan Wizard
    • Alerts
    • Performance Monitor Integration
    • Summary
    • Q&A
    • Workshop




  • Chapter Day 19 - Data Distribution (Replication)

    • Introduction to Replication
    • Reasons to Replicate Data
    • Replication Terminology
    • The Replication Process
    • Designing Your Replication Environment
    • Installing Publishing Servers
    • Configuring Subscription Servers
    • Creating Articles and Publications
    • Subscribing to Publications
    • Replication Topology
    • Troubleshooting Replication
    • Summary
    • Q&A
    • Workshop




  • Chapter Day 20 - Performance Tuning and Optimization

    • Hardware Selection and Tuning
    • Windows NT Configuration Parameters
    • NT Performance Monitor Counters
    • SQL Server Tuning
    • SQL Server Performance Monitor Counters
    • Current Activity Window
    • Summary
    • Q&A
    • Workshop




  • Chapter Day 21 - SQL Server and the World Wide Web

    • Push Updates to Web Pages Using the SQL Server Web Assistant
    • Pull Methodology for Dynamic Web Pages
    • Other Internet Access Methods to SQL Server
    • Summary
    • Q&A
    • Workshop


Week 3 in Review



  • Appendix A - Restoring the master Database

    • Restoring with a Good Backup
    • Restoring master without a Backup
    • Summary




  • Appendix B - Answers

    • Day 1, "Introduction to SQL Server 6.5 and Relational Databases"
    • Day 2, "Installing SQL Server 6.5"
    • Day 3, "SQL Server Tools and Utilities"
    • Day 4, "Devices and Databases"
    • Day 5, "Creating Tables"
    • Day 6, "SQL Server Login and User Security"
    • Day 7, "User Permissions"
    • Day 8, "Backing Up and Restoring"
    • Day 9, "Importing and Exporting Data"
    • Day 10, "Data Retrieval"
    • Day 11, "Data Retrieval (Advanced Topics)"
    • Day 12, "Data Modification"
    • Day 13, "Indexing"
    • Day 14, "Data Integrity"
    • Day 15, "Views, Stored Procedures, and Triggers"
    • Day 16, "Programming SQL Server"
    • Day 17, "Programming SQL Server (Advanced Topics)"
    • Day 18, "SQL Server Automation"
    • Day 19, "Data Distribution (Replication)"
    • Day 20, "Performance Tuning and Optimization"
    • Day 21, "SQL Server and the World Wide Web"




  • Index



Read More Show Less

First Chapter

[Figures are not included in this sample chapter]

Teach Yourself Microsoft SQL Server 6.5 in 21 Days

Week 1
Day 3
SQL Server Tools and Utilities

Now that you have SQL Server installed, it is time to take a tour of the tools installed by the setup program. You're going to learn about the graphical interface tools, as well as some of the tools that do not have a graphical interface. Each of these tools can be used in its own way to help you administer and program SQL Server. Figure 3.1 displays the SQL Server program group with the GUI tools and utilities.

Figure 3.1. The SQL Server program group.

Books Online

You should start with what is perhaps the most important graphical tool--the SQL Server Books Online. Click Start | Programs | Microsoft SQL Server 6.5 | SQL Server Books Online.

Figure 3.2 The SQL Server Books Online utility.

One of the things that many people forget to do is read the manuals. In this case, that means looking at the Books Online (unless you're lucky enough to have a paper copy). You start with a brief discussion of each manual and why you'd want to read each of them.

What's New in SQL Server 6.5

Microsoft did something interesting when it shipped SQL Server 6.5. Rather than integrate all the changes from release 6.0 into the current set of manuals, it created a new book with all the changes. This means that, most of the time, you'll have to check in two places when you want to research a topic. First, you look up the topic in the original book (such as the Transact-SQL reference for syntax), and then you check the "What's New" section for updates or changes. It's not exactly fun, but you do get used to it. SQL Server 6.5 is a significant upgrade to SQL Server 6.0, so you should become familiar with this section of Books Online.

SQL Server Setup 6.0

Surprisingly enough, this is the guide to the setup program. Because you've been through Day 2, "Installing SQL Server 6.5," it's unlikely that you need to read this book, but you may want to skim through it at some point as a reference companion. Be aware that there were important changes in SQL Server 6.5, so remember to review the "What's New for Setup" section on this topic as well.

Database Developer's Companion 6.0

The Database Developer's Companion 6.0 is a basic introduction to the Transact-SQL language and some structures of SQL Server. After completing this book you will have no need to read that companion (other than to verify your skill set).

Administrator's Companion 6.0

This is the most important part of the Books Online--you should thoroughly review this book. Because most of you don't have much time (it's a pretty big book), you should use it as a reference on how to administer SQL Server. The most important topics are covered in this book, but some things are best left to the manual (such as advanced replication issues).

Transact-SQL Reference 6.0

This is an alphabetized reference of all the Transact-SQL commands and utilities you might use while working with SQL Server. It is most useful as a reference tool when working on a specific problem and you need help with syntax, parameters, or understanding some of the programs that come with SQL Server. Again, the most important parts are covered in this book.

Programming SQL Distributed Management Objects 6.0

One of the more interesting things you can do with SQL Server is programmatically administer the server with Visual Basic or Visual C++. This guide explains the objects, methods, and properties available to you as a developer while accessing SQL Server for administration. Note that this is not meant as a general development interface.

Guide to Microsoft Distributed Transaction Coordinator

The Distributed Transaction Coordinator provides two-phase commit functionality for SQL Server 6.5. This guide explains what two-phase commit is, and how it has been implemented with SQL Server; it is discussed on Day 16, "Programming SQL Server." Functionally speaking, this is part of the Microsoft Transaction Server (MTS) product.

ODBC API Reference

This is exactly what its name implies--a reference to ODBC version 2.5. Be careful about versions here; Microsoft has released version 3.0 of ODBC. If you've installed Active Server Pages (ASP) or another Microsoft tool that requires it, you may have a newer version of ODBC on your computer.

Programming ODBC for SQL Server

Again, it's exactly what its name implies. However, watch out for the version issue mentioned in the previous section.

Programming DB-Library 6.0 for C

If you want to get to the lowest level of programmatic access to SQL Server, you can use the DB-Library Application Programming Interface (API). DB-Library is the native API of SQL Server 6.5 and earlier releases. However, Microsoft has made it clear that ODBC is the direction in which you should be headed, so you should probably stay clear of DB-Library unless you have a good reason to work with it. There is almost nothing you can do with DB-Library that can't be accomplished with ODBC.

Programming DB-Library 6.0 for Visual Basic

DB-Library 6.0 for Visual Basic is the same as it is for C. Watch out for version issues again. This guide is for Visual Basic version 3 or 4, and may not address issues with Visual Basic 5.0, which was released after SQL Server 6.5.

Programming Open Data Services 6.0

This manual discusses advanced programming using DB-Library and ODBC, which allow you to take advantage of the Microsoft Open Data Services to create server applications for client/server systems. This subject is beyond the scope of this book.

Glossary 6.0

Finally, there is a set of definitions for most of the terms used in the Books Online. Look through this so you can understand the SQL Server's special language.

The ISQL/w Utility

Next, you examine the primary query interface that you use when you develop with SQL Server and the Transact-SQL language. ISQL/w is the fastest way to log in, run a query, and log out of SQL Server 6.5. To open the ISQL/w utility, click Start | Programs | Microsoft SQL Server 6.5 | ISQL_w.

The icon name is ISQL_w, but you can run the program as ISQLw.exe. The first time you run ISQL/w (Interactive SQL for Windows), you are prompted to log in to SQL Server. You must first decide which server with which to connect. If you leave the Server: field blank, ISQL/w assumes you are attempting to log in to a local installation of SQL Server 6.5. Local installation means a copy of SQL Server running on the same computer as ISQL/w is running. Otherwise, you can enter the name of the SQL Server computer with which you would like to connect. You can also enter (local), which is the same as leaving the field empty.

You also need to provide login information. For now, continue to log in using the Use Standard Security option, with a Login ID of SA and no password. Your login should look similar to Figure 3.3. You learn more about security, including the Use Trusted Security option, on Day 6, "SQL Server Login and User Security."

Figure 3.3. Log in to the ISQL/w utility.

After you've clicked Connect (or pressed Enter), you are logged in to the appropriate copy of SQL Server (assuming you've typed valid Login ID and Password information). You see a screen that looks like Figure 3.4.

If your login doesn't go smoothly, you might see a screen similar to Figure 3.5. You see this screen if you've incorrectly entered your password. Simply try again. You should also verify that you've typed your login name properly.

Another error you might receive looks like that in Figure 3.6. There are several things to look for if you receive this error. Verify that the MSSQLServer service is running on the computer into which you are trying to log. If it is, then the problem may be with your client configuration. See the client configuration utility discussion later today. Otherwise, make sure you typed the server name correctly. To see a list of servers on the network, rather than typing in a server name, click List Servers on the login screen. You are presented with a list of the active SQL Server machines on your network. Don't be alarmed if your server does not show up here; you can still connect to a server that isn't visible by typing its computer name here.

Figure 3.4. The ISQL/w tool.

Figure 3.5. Login Failed window.

Figure 3.6. Network connectivity problems.

The ISQL/w Toolbar

After you have successfully logged in, you can start running Transact-SQL queries (you learn how to program these queries on Day 10, "Data Retrieval"). You can open multiple windows inside ISQL/w by selecting New Query (the leftmost button on the toolbar). Each window you open is a separate connection to SQL Server, so if you open too many, you could run out of connections on your server. By default, you have 15 connections available after installation.

Selecting the second button (which looks like a standard Windows File Open button) opens a standard dialog to find Transact-SQL scripts (which, by default, have a .sql extension). The next button over, Save Query | Result, either saves the text in your query window to a file, or if you are viewing the Results window, the results of a query. The next button (with the wrench icon) sets a variety of query options. These options are examined at several points later in this book. The DB: drop-down box next to this button provides a list of the installed databases on the server to which you are connected. If you change the value here, the SQL script that you run uses this database unless you specify a different database in the SQL script itself.

The next drop-down box, Queries, enables you to navigate between each query window you have opened. The button on the right with the red X removes the window you're currently looking at, disconnecting that session from SQL Server in the process.

Submitting Queries

To submit a query after typing it, press the green VCR-style play button. You can press the red stop button to stop a query while it's running. That button is disabled unless your query is actually running. There is a spinning globe in the Queries drop-down box while your query is running. To see a quick example, you could run the following code:


USE pubs 
go 
DBCC CheckDB

If you don't see the Play and Stop buttons on your screen, you simply need to make the window wider. Drag the edge of your query window to the right to expand it, or simply grow your query to a full-screen view.

Selecting File | Connect and logging in to a different server is another useful option. Thus, you can log in to many servers at the same time using a single copy of ISQL/w. There are a couple of really useful keyboard shortcuts as well. Press Ctrl+E to execute a query, or select Query | Execute from the menu. Another nifty shortcut is Alt+X, which also runs your query (this option is nice because the keys are close together). To stop a query while it's running, go to Query | Cancel from the menu or press Alt+Break. With any luck, most of your queries will run so fast you'll never need to use this option.

Help Options

Help is the last option mentioned here. If you can't remember the syntax (the right way to type a command, or a list of options), highlight a key word with your mouse and press Shift+F1. Help for that command should pop up in a window. Help is built in for almost every Transact-SQL command.

Alt+F1 is another really useful option. If you highlight the name of a SQL Server object, such as a table, the default help is provided in the Results window. The exact type of help you receive depends on the type of object with which you're asking for help.

There are a variety of additional options with ISQL/w, but they make much more sense when they're discussed in the context of performance tuning and query optimization.

The MSQuery Tool

Microsoft Query allows you to get data out of SQL Server without understanding Transact-SQL. If you'd like to get a quick look at data and don't want to write any code, start MS Query: Start | Programs | Microsoft SQL Server 6.5 | MSQuery. You should see something similar to Figure 3.7.

Figure 3.7. The MSQuery tool.

Select New Query from the File menu and you are presented with a list of ODBC data sources. You should have an entry called LocalServer - sa that was installed during SQL Server installation.


NOTE ODBC can be an extremely useful tool for connecting to multiple databases, including SQL Server 6.5. Use your Web browser and navigate to http://www.microsoft.com/odbc.

When you select Use, you are prompted to log in to SQL Server. Use the same security information you used with ISQL/w. If you click Options, you can select a database with which you'd like to work (see Figure 3.8). In this example, you choose pubs from the available options.

Figure 3.8. MS Query login dialogs.

You are presented with a list of all of the user tables in that database. Double-click authors, titleauthor, and then titles. Click Close to stop adding tables to the query. You should see something similar to Figure 3.9. Notice that the tables have relationships already created between them, symbolized by the black lines connecting different fields in each table to each other. Because MS Query already understands the relationships between your database tables, you can select data by simply double-clicking the columns of data you would like to see.

Figure 3.9. Microsoft Query view with tables added.

Double-click the au_lname column in the authors table, the royaltyper column in titleauthor, and the title in the titles table--you've just created a complex query to look at books, their authors, and how much royalty authors get for their books (see Figure 3.10).

Figure 3.10. Your query results.

To view the SQL code for the query you created, click SQL on the toolbar. You should see something similar to Figure 3.11.

Figure 3.11. Your query in Transact-SQL.

With MS Query, it's easy to write complex SQL statements and quickly get data from the database without actually coding any Transact-SQL. You can save your queries by selecting File | Save Query. You can then reopen your saved queries by selecting File | Open Query.

The Client Configuration Utility

The client configuration utility is not a terribly difficult tool to use, but has a tremendous impact on the connectivity of your client computers to your SQL Server. When you start the utility by selecting Start | Programs | Microsoft SQL Server 6.5 | SQL Client Configuration utility, you are presented with a screen that looks like Figure 3.12.

This screen tells you which version of the DB-Library DLL you are using on your system. It indicates where the file is physically located, the DLL's name, date, and size. You also have two options available--Use International Settings and Automatic ANSI to OEM.

Figure 3.12. The SQL Server Client Configuration utility.

Use International Settings uses certain settings, such as date and time formats, from Windows 95 and Windows NT. For example, if you're using Windows NT and have enabled a non-United States setting, your local settings are used in favor of the settings on SQL Server. The conversion of ANSI to OEM translates data coming back from SQL Server into the local code page used by your client.

Click the Net Library tab shown in Figure 3.13. The Net Library defines the manner in which you communicate with SQL Server from a particular computer. Think of it as a language. If your server only speaks English and French, and you attempt to speak German, the server won't understand you and cannot respond. The default language is Named Pipes, as shown in the figure.

Figure 3.13. The Net Library tab.

To change the Net Library, simply choose the drop-down list and select a different entry. Note that you must have the appropriate library installed on your server in order to communicate over an alternate Network Library. If you select a different library (such as TCP/IP Sockets) and you do not have that library installed at your server, you get error message


10004, severity 9,  Unable to connect: SQL Server is unavailable or 
does not exist. Specified SQL Server not found

the next time you attempt to connect to your SQL Server with a tool such as ISQL/w. This is SQL Server's way of telling you that it can't contact the server you are trying to contact. When you get this message, you should first check this screen in order to verify you are using a supported Network Library.

The final tab, Advanced, lets you override the default settings from the Net Library tab (see Figure 3.14). For instance, if you have a server named SALES, and that server's copy of SQL Server only supports the Multi-Protocol Net Library (but the rest of your servers used Named Pipes), you could add an entry on this screen. You type in the server's name, in this case, SALES, select Multi-Protocol in the DLL Name field, then select Add/Modify. You could also add the server name in the Connection String field, but in this case it's unnecessary. After you've done this, you use the Multi-Protocol Net Library whenever you type SALES as a server name in ISQL/w or another SQL Server tool.

Figure 3.14. The SQL Server Client Configuration utility Advanced tab.

The SQL Trace Utility

SQL Trace is new to SQL Server 6.5, and won't work with previous versions of SQL Server. SQL Trace enables you to capture all the Transact-SQL traffic coming into SQL Server, save the T-SQL as a script, keep a log, or just monitor information online. To open the SQL Trace utility, click Start | Programs | Microsoft SQL Server 6.5 | SQL Trace.

When you open SQL Trace for the first time, you are prompted to log in. Select a Login ID of SA with a blank password. You get the No Filters Defined dialog (see Figure 3.15).

Figure 3.15. No Filters Defined dialog.

If you select No, the SQL Trace application won't do anything for you. You should select Yes. You are presented with the New Filter dialog. You should name your filter, and then select the various options afforded in the dialog. The filter in Figure 3.16 has been named Generic.

Figure 3.16. The New Filter dialog. Here are the options for the New Filter dialog:

  • Login Name  This is where you can restrict which logins you would like to monitor. You have a limited selection immediately after installation (adding logins is discussed on Day 6). If you select the button with the ellipsis next to the Login Name field, you are presented with a list of all the currently defined logins in your SQL Server. If you leave it set to the All default, you see every statement submitted to SQL Server.

  • Application  This allows you to monitor Transact-SQL commands issued from either a particular application or from all applications. Again, to view a list of applications, select the ellipsis next to the Application field. You are presented with a list of the available applications currently connected to SQL Server.

TIP It's pretty rare that you can spell the name of the application you would like to monitor (at least in the way it shows up to SQL Server). Therefore, it's easiest if you have at least one copy of the application connected to SQL Server when you set up this filter. That way, that application's proper name is available in the list. The application name is part of the ODBC connection string.
  • Host Name  This option allows you to monitor Transact-SQL traffic that occurs only from a particular computer on your network. If you select the ellipsis next to the Host Name field, you are presented with a list of the computers currently connected to your SQL Server machine. If you know the name of the computer on which your application will be running, or you want to monitor all the SQL Server activity from a particular computer, you can enter the NetBIOS computer name here. If you leave it at the default value of All, you see SQL Server traffic from all computers on the network that attach to your copy of SQL Server.

  • Capture Options  Here you specify what you want to do with the information you have requested based on the logins, applications, and host names you've selected. The default action is View on Screen, which shows you all the Transact-SQL statements being submitted while you've enabled this trace. There are several other options available here.

  • Per Connection  When you've set this option, SQL Trace opens a separate child window for each connection to SQL Server. Without this option checked, all connections show up within a single window.

  • Save to Script File  If you select the Save to Script File option, a file location dialog pops up, allowing you to name the script file that you will create. The script file contains all the connections you monitor while this trace is active. Note that all non-SQL statements (such as connection numbers, performance data, and so on) are also in the script file, but are commented out with the -- characters at the beginning of each line.

  • Include Performance Information  This selection does exactly what its name implies. Select this option to view statistics about CPU usage and disk reads, as well as how long it took to run each query. Just like other non-Transact-SQL entries documented by SQL Trace, all performance data is commented out in script files.

  • Save as Log File  This option creates a tab-delimited file that can later be loaded into SQL Server for analysis, or into another program of your choice, such as Microsoft Excel.

  • Events Tab  This option allows you to modify what kinds of Transact-SQL statements are captured (see Figure 3.17). It also gives you the flexibility of capturing or filtering remote procedure calls, attentions (client interruptions of the server), and disconnects.

Click Add after you've fully configured your filter, and the filter begins collecting data. When you're done with the trace, select the red stop button on the toolbar. Stopping the filter closes any log or script files you've associated with this filter. You can create another filter by selecting the new filter button (the first button on the left of the toolbar). If you pause a running filter, you can view the information on the screen while data collection is momentarily halted. To resume data collection, simply select Start Filters (the VCR-style play button).

Figure 3.17. The New Filter dialog's Events tab.


WARNING If you stop the filter and you were only viewing the information on the screen, that information is lost. To keep information from being lost from your capture, specify either the Save to Script File or Save as Log File option when configuring your filter(s).

Also notice that you can start multiple filters at the same time. With creative use of this tool, you can track down a number of problems. You can also start SQL Enterprise Manager, ISQL/w, or SQL Performance Monitor from within SQL Trace by clicking the appropriate buttons on the toolbar (their icons match the icons from the Start menu).

Enabling auditing is another nice SQL Trace option. The auditing feature enables you to run a trace report to a log file. To start auditing, start SQL Trace and select Cancel on the Start Selected Filters window. If you're already in SQL Trace, begin with the next step: Select Tools | SQL Server Auditing from the menu, and you are presented with the SQL Server Auditing dialog, as shown in Figure 3.18.

At this point you might be asking why you'd want to enable auditing. The answer is that an audit is the best way to capture the information that compiles a record of all T-SQL statements run on your SQL Server. There is no other built-in way to do this with tools provided by Microsoft.

Select the appropriate options, just as you have previously. That allows you to select which events you would like to audit. The only difference is the first line, which should contain the full file path. Enter the path and filename you would like to use for your audit file. Make sure that the directory you select has enough room to accommodate the trace file as it grows.

Figure 3.18. The SQL Server Auditing window.

You might be wondering what the difference is between this and any other trace. When you close SQL Trace, your filters are stopped; however, auditing continues to run until the MSSQLServer service stops, or you explicitly stop the audit. The output from an audit is also different than the file produced with a normal trace that's written to a log file. To stop the audit, go back into the Tools | SQL Server Auditing menu. When an audit is currently enabled, Stop becomes available (see Figure 3.19).

Figure 3.19. The SQL Server Auditing dialog. Note the Stop button.

When you select Stop, you get a message box indicating that auditing has been successfully stopped. Everything you've seen here with SQL Trace can be accomplished programmatically using an extended stored procedure, xp_sqltrace. In fact, xp_sqltrace has significant additional functionality beyond the graphical tools examined here. Extended stored procedures are discussed further on Day 15, "Views, Stored Procedures, and Triggers."

A simple exercise shows why SQL Trace is a valuable tool. Start SQL Trace and create a generic filter that captures everything to the screen.

Now start ISQL/w while leaving SQL Trace running, and log in to your server as sa. Return to the Trace window and look at the Transact-SQL statements that are run merely by starting ISQL/w and logging in as SA. Many utilities provided by Microsoft (and others) run many commands that you never see. One way to determine what your tools are doing behind the scenes is to start SQL Trace and run your favorite program. You examine the Transact-SQL language on Day 10.

Take one step further. You have developers working with Visual Basic and it's your job to optimize the server for them. They may not even know, however, what T-SQL statements they are submitting to SQL Server. How do you determine what's going on so that you can make SQL Server run better? How do you pass information back to them so that they can improve their code? Run SQL Trace, capture their T-SQL statements, and supply it to them. In a world of tools that are writing SQL statements on behalf of developers, SQL Trace is an extremely beneficial utility.

SQL Security Manager

The SQL Security Manager utility is used to control who has administrative access to SQL Server, and who has ordinary user access to SQL Server. This utility is only used, however, to configure trusted security connections. You learn more about trusted security and how to use this tool on Day 6.

SQL Server Web Assistant

This utility enables you to generate static World Wide Web pages using SQL Server data. You fully examine this utility on Day 21, "SQL Server and the World Wide Web."

SQL Service Manager

The SQL Service Manager utility enables you to control the SQL Server-related services on your computer (or any SQL Server computer on your network). When you start this utility, you are presented with something similar to Figure 3.20.

The Server field contains the name of the server you are monitoring. The Services box shows which service you are examining, and the stoplight graphically represents the state of the service. The green light shows when a service is running. That indicates that, in this case, the MSSQLServer service is currently running. As you learned on Day 2, this is the main service that runs SQL Server.

Figure 3.20. The SQL Service Manager utility.

A service may also be stopped or paused. Some services do not support being paused, and those that do vary in their behavior. For instance, the MSSQLServer service continues to function when paused, but new connections are not allowed.

The Services drop-down list shows which services can be controlled from this utility. This includes the MSSQLServer service, the SQLExecutive service, and the MSDTC service. The Server drop-down list can be used to select another server. The list can be somewhat unreliable, and the utility is flexible enough to allow you to simply type the name of the server you'd like to examine. After you've entered a server name, select Connect.


NOTE At this point, your security is checked. You must be a Windows NT administrator or a server operator on the server to which you are connecting in order to control services. SQL Server permissions have no effect on your Windows NT permissions; that means that even if you are a SQL Server administrator, that doesn't guarantee you can control SQL Server services.

The SQL Service Manager utility has a somewhat hidden functionality. Click the icon in the upper-left corner of the application and the normal application menu shows up, with additions (see Figure 3.21). Notice that three menu choices are available.

Figure 3.21. The SQL Service Manager utility's additional features.

Action Verify

Action Verify is off by default. When you double-click the appropriate light in the stoplight, the action you've requested is taken. For instance, when you double-click the red stoplight, the service you are examining is stopped. If you prefer to have a confirmation dialog appear to verify your actions, enable this option by clicking it from the menu. After you've enabled this option, you receive a dialog box that requests you to verify your choice (see Figure 3.22).

Figure 3.22. The Are You Sure? dialog.

Remote Control

Remote Control is enabled by default. When you turn this option off, you are unable to control services on a remote computer; in that case, you can only control services on the local computer. If you turn this option off, the Server field disappears.

Polling Interval

The Polling Interval determines how often the Service Manager utility checks for the state of the services you are monitoring. The default interval for foreground viewing (when you have SQL Service Manager selected) in Figure 3.23 is two seconds. That means the utility queries the server for the state of the three SQL Server-related services every two seconds. The background polling interval defaults to five seconds. This means that as long as a utility is open on your computer--even if you are not looking at it--the state of these services is checked every five seconds on the appropriate computer.

Figure 3.23. SQL Service Manager Polling Interval configuration.

You should note that most, if not all, of the functionality of this application is also available from SQL Enterprise Manager.

SQL Server Setup

You learned yesterday how setup is run to install SQL Server. Now take a second look at the setup program and what it can do for you when you have your server running. Start the Setup program. After the program has started, you are presented with a welcome screen; Select Continue two times to acknowledge you have already installed SQL Server on this computer. You are then presented with the Options screen shown in Figure 3.24.

Figure 3.24. The Main SQL Server Setup screen.

By default, the Setup program assumes you want to upgrade to this release of SQL Server. Concentrate on the other options available here.

Change Network Support

This option allows you to add or remove network libraries installed with SQL Server. For example, if you decide to add TCP/IP Sockets support for SQL Server, you select this option and are presented with the Select Network Protocols dialog shown in Figure 3.25. Simply check or uncheck the network support boxes to enable or disable each Net Library.

Figure 3.25. The SQL Setup Select Network Protocols dialog.

Click OK after you've completed your choices on this screen. Each network library raises its own dialog to confirm your choices and provide options to change the defaults used by each library. Figure 3.26 shows the Named Pipe dialog.


WARNING Do not change these until you fully understand the implications of doing so.

Figure 3.26. The Named Pipe configuration dialog.

For this exercise, click Exit. To actually execute changes, select Continue until you are presented with the Setup Completion dialog. The dialog notes that the changes you have made will be effective the next time SQL Server is started. Remember that this means the MSSQLServer service must be stopped and restarted for any changes to take effect.

Add Language

If you've purchased an additional language pack (for instance, German) from Microsoft and would like to install it in addition to the default language (U.S. English), you set it up here. Installing an additional language allows you to get error messages, date formats, and other language differences. For your users in Germany, for instance, you could have error messages in German. SQL Server also supports French and Japanese languages at the time of this writing.

Rebuild Master Database

This option re-initializes SQL Server. It is used to change the sort order, character set, or repair critical system failures when a valid backup of the master database does not exist. With any luck, you will never need to choose this option. You learn more about this option on Day 8, "Backing Up and Restoring."

Set Server Options

This is a catch-all of changes that can be made to SQL Server. The Select Server Options dialog is shown in Figure 3.27.

Figure 3.27. The Select Server Options dialog.

You can see in what directory SQL Server is installed, where your Master device and error logs can be found, in addition to being able to set whether SQL Server will start automatically when the computer reboots. Almost every option on this screen can also be set inside SQL Enterprise Manager.

Set Security Options

This dialog allows you to change the security integration of SQL Server with Windows NT, as discussed in Day 2. This dialog has also been integrated with SQL Enterprise Manager. Because these entries are related to how security has been set up, they are discussed on Day 6.

Remove SQL Server

Just as its name implies, this option removes SQL Server from your system. When selected, it removes the MSSQLServer, SQLExecutive, and MSDTC services. It also removes most of the files from the \MSSQL directory if you select that option. To finish removing SQL Server completely, delete the rest of the \MSSQL directory. It can't delete its own files while it's still running the Setup program.


NOTE If you are trying to completely remove SQL Server, either because setup failed and you'd like to start again, or if you simply want to completely remove SQL Server from a computer, you may want to also delete the Registry keys related to SQL Server. These keys are not completely removed by the Setup program. This option may delete entries needed for connectivity with other SQL Server databases, so don't do this unless you intend to re-install SQL Server on this computer. Run REGEDT32.EXE, and navigate to HKEY_LOCAL_MACHINE\ Software\Microsoft\MSSQLServer. Highlight MSSQLServer here, and press Delete. The Registry Editor asks if you are sure. If you are, select Yes. This removes any further knowledge that your system has about SQL Server. As always, editing the Registry can potentially damage your system beyond repair, so caution should be taken when performing any operation on your Registry.

SQL Performance Monitor

The SQL Performance Monitor program allows you to use the Windows NT performance monitor utility to track the condition of SQL Server databases and the server as a whole. This utility is discussed further on Day 20, "Performance Tuning and Optimization." It's a good idea to become familiar with your server's performance monitoring.

README.TXT

README.TXT is the file you should read before you install SQL Server 6.5. Although you should read it before installing, because you have already installed SQL Server, read it at your leisure.

Microsoft ODBC SQL Server Driver

This is a help file documenting the ODBC driver that ships with SQL Server 6.5. Because this information has been integrated into the SQL Server Books Online, this file has been made obsolete.

SQL Distributed Management Objects

Again, this help file for SQL-DMO (SQL Distributed Management Objects) has been made obsolete by integration of this information into the SQL Server Books Online.

SQL Enterprise Manager

SQL Enterprise Manager will most likely be your primary view of SQL Server until you become comfortable with the Transact-SQL language. Even then, Enterprise Manager provides functionality that makes some operations much easier than running T-SQL directly. When you start Enterprise Manager, you are prompted to register your server. Because this has already been examined on Day 2, you should have successfully registered your server. Connect to your server by clicking the plus sign. This logs you on with the security credentials you provided when you registered your server name. You see something similar to Figure 3.28.

Figure 3.28. The SQL Enterprise Manager.

If it looks a little intimidating at first, that's because you can do almost any administrative task from this tool. However, when you are comfortable with the interface, you find it quite easy to use. Start by examining the first level of objects/folders. The first icon, SQL Mail, controls integration with a mail server such as Microsoft Mail or Microsoft Exchange Server. Configuring mail for SQL Server is fully documented in the Books Online.

The next icon controls the SQLExecutive service. If you right-click this icon (or the name SQL Executive), you are presented with options related to this service. You can stop and restart the service, configure the properties of the service, and look at the SQL Executive error log if you've enabled that option.

The next icon controls the Distributed Transaction Coordinator, or MSDTC service. By default this service is not running. This service is discussed on Day 16.

You can view each of the options you see, Database Devices, Backup Devices, and Databases by expanding each folder. You can view logins by expanding the Logins folder. Typically, a pop-up menu is available by right-clicking each folder. You can also see many menu options.

Each time you look at an additional feature as you progress through this book, the focus is on how to perform tasks with Enterprise Manager. For now, take a look at these three options.

First, click the stop light on the toolbar. You see something that looks very much like the SQL Service Manager utility. The difference here is that this window only controls the MSSQLServer service. To control the other services, right-click their icons after you have connected to a server.

The second option you look at is the SQL Query tool. You can start the tool by clicking its icon (the icon has two tables and a question mark on it) on the toolbar, or selecting Tools | SQL Query Tool from the menu.

The last thing see is the icons legend. You can see what is offered by clicking the legend icon in the toolbar. It is on the right side and looks like a horizontal bar chart.

You are presented with a scrollable list of all the icons and what they mean (see Figure 3.29). This helps familiarize you with the visual cues that Enterprise Manager provides.

You can view the SQL Enterprise Manager configuration screen by right-clicking your server name, or selecting Server | SQL Server | Configure from the menu. You see something similar to Figure 3.30.

Here you can see options, including whether SQL Server should start automatically, where SQL Server is installed on your computer, the location of the master database device, and the location of the SQL Server Error Log.

Parameters shows you the startup parameters for SQL Server. You should see two parameters in this window:


-dD:\mssql\data\master.dat 
-eD:\mssql\log\errorlog

Figure 3.29. The SQL Enterprise Manager legend.

Figure 3.30. The Server Configuration/Options window.

The first line tells SQL Server where to find the master database device at startup, and the second line tells SQL Server where to log errors. This screen directly reflects the values stored in your Windows NT Registry.

Tape Support displays a screen that asks how to deal with tapes being used for backups. The default behavior is to wait indefinitely for an operator to mount a tape in your tape drive. You can change that here.

Mail Login presents a dialog that allows you to configure SQL Server for integration with your mail server. This is covered on Day 18, "SQL Server Automation."

Notice that many of these options are the same as you looked at in the Setup program earlier. One exception is the last checkbox on the screen--the xp_cmdshell - Use SQLExecutiveCmdExec Account for Non - SAs checkbox. You should select this box (although it's not selected by default).


NOTE The xp_cmdshell extended stored procedure allows you to run NT commands from within SQL Server. It normally runs in the context of the account running the MSSQLServer service, which is LocalSystem by default.

LocalSystem is the equivalent of saying Windows NT itself. Therefore, anyone who can run xp_cmdshell will have the same permissions as Windows NT itself on your server. By default, only the SA user can run this extended stored procedure. If you want to have other users run this procedure but want to restrict their security, you should enable the option just mentioned.

During the Setup program, SQL Server setup adds a new local Windows NT account: SQLExecutiveCmdExec. This account is added as an ordinary user account, and the password is randomly generated by the Setup program. You should then set permissions for this user account appropriately. After you've done that, give people the right to run the xp_cmdshell stored procedure. When SQL Server notices that you are not logged in as SA, it runs under the context of the SQLExecutiveCmdExec account rather than the account running the MSSQLServer service. The security on your server is maintained and you won't need to worry about ordinary users gaining additional permissions via the xp_cmdshell extended stored procedure.

The Security Options tab has the same options the Set Security Options has during setup.

The third tab, Configuration, allows you to set all the server-wide options that determine how your server works (see Figure 3.31). As you progress through this book, you examine many of the options available in this dialog.

The final tab, Attributes (see Figure 3.32), shows a variety of your server's properties, including which release is installed (and which service pack is applied). You can determine this based on the Product Version field. Here is a list of their meanings as of this book's writing:

Product Version Meaning
6.50.201 SQL Server 6.5 without any service packs
6.50.213 SQL Server 6.5 with service pack 1
6.50.240 SQL Server 6.5 with service pack 2
6.50.258 SQL Server 6.5 with service pack 3

Figure 3.31. The SQL Server Configuration tab.

Figure 3.32. The SQL Server Attributes Tab.

The Refresh option is the other thing you really need to know about Enterprise Manager. If you make any changes in ISQL/w--or any other tool outside Enterprise Manager--but don't close it, the information displayed in the Enterprise Manager may become stale. This is because Enterprise Manager caches information on your client computer, rather than always querying SQL Server. You get faster response time when using this utility. However, it can cause difficulties as you work.

For instance, if you were to create a database, but did so from ISQL/w instead of Enterprise Manager, you may not see your new database in the Databases folder. If you right-click the Databases folder, you find a Refresh option available in the pop-up menu. If you select this, Enterprise Manager queries SQL Server and refreshes its knowledge of your server's contents. Unfortunately, it won't refresh everything it thinks it knows, just the Databases folder in this case. To completely refresh all information in Enterprise Manager, right-click your server name. The last menu option in the pop-up menu is Disconnect. When you select this option, it logs you off your SQL Server; when you click the plus sign in front of your server name, you are logged back in and all information is refreshed.

Non-Graphical Tools

Now that you've looked at the graphical utilities that shipped with SQL Server 6.5, examine the other utilities that ship with the product.

BCP.EXE

BCP stands for Bulk Copy Program. This utility loads data from a file into SQL Server, or exports data from SQL Server to a file. You learn the details of this utility, including its syntax, on Day 9, "Importing and Exporting Data."

ChkUpg65.EXE

ChkUpg65.EXE verifies that a database from a previous version of SQL Server is compatible for an upgrade. It primarily looks for three problems:

  • Read Only  If a database is set to read-only mode, it cannot be upgraded. Upgrading modifies the system tables, or system catalog, of SQL Server. Before you can upgrade your databases to version 6.5, you should turn off this database option. You learn about database options on Day 4, "Devices and Databases."

  • Reserved Words  SQL Server 6.0 and 6.5 added a significant number of reserved words, which can no longer be used in objects you create. If you were using one of these words as a table name or column name, those objects would not be valid in SQL Server 6.5, and you would need to change the affected names before upgrading.

  • Entries in Syscomments  When you create objects such as views, triggers, and stored procedures, the creation text of those objects is stored in each database's system catalog, in the syscomments table. In version 4.x of SQL Server, if a vendor based its product on SQL Server, it would have essentially shipped its source code if it left this text in syscomments. Many vendors deleted this text so that their programs would not be readable by people who purchased their products. However, SQL Server uses this text to re-create all of these objects during an upgrade. Hence, upon upgrade, all of these objects disappear.

    Starting with SQL Server 6.0, Microsoft included the capability to encrypt these entries without actually deleting them. You examine how this encryption works as you examine each type of object. The only thing you can do is either re-create the objects if you have a script for them, or contact your product vendor for assistance.

ISQL.EXE

ISQL.EXE allows you to run Transact-SQL queries. In particular, it allows you to run queries through batch (or scheduled) tasks. This tool will be your primary batch tool to support administrative tasks with SQL Server. You learn this in great detail when you get into scheduled tasks on Day 18.


isql /U 
login_id [/b] [/e] [/E] [/p] [/n] [/d 
dbname] [/q "
query"] [/Q "
query"] 
[/c 
cmdend] [/h 
headers] [/w 
columnwidth] [/s 
colseparator] 
[/t 
timeout] [/m 
errorlevel] [/L] [/?] [/r {0 | 1}] 
[/H 
wksta_name] [/P 
password] [/O] [/I] 
[/S 
servername] [/i 
inputfile] [/o 
outputfile] [/a 
packet_size]

Where:

  • /U is your login ID.

  • /P is your password (if you don't code it, ISQL prompts you for it).

  • /S is the server to which you'd like to connect.

  • /b sets DOS error levels based on problems with your script, so that you can detect problems in a batch file.

  • /q runs a query but leaves ISQL running.

  • /Q runs a query and immediately exits ISQL.

  • /i specifies the path and filename of a "script" of Transact-SQL commands you'd like to run.

  • /o specifies the path and filename where the results of your T-SQL commands should go. If not specified, they go to the screen (in batch, you simply lose them).

There are additional parameters documented in the SQL Server Books Online, but those covered here are the most important for use on a day-to-day basis.

Figure 3.33 is a sample run with the ISQL command-line utility.

Figure 3.33. An ISQL session.

Note that most of the parameters to start ISQL are optional, but you must tell ISQL who you want to log in as. If you don't specify the password, it prompts you to enter it. Simply type in a Transact-SQL query. When you press Enter, ISQL will not run your query. It doesn't run the query until you type the word GO on a line by itself. It then returns the answer to you. Type quit or exit on a command line (1>, in the example here) to end the application.

Makepipe.EXE/Readpipe.EXE

These utilities are used to verify the integrity of the Named Pipes file system. Because you looked at them on Day 2, they are not discussed further here.

ODBCPing.EXE

ODBCPing.EXE allows you to verify that ODBC is working successfully from a client to connection to SQL Server.


odbcping  /S
server /U
login /P
password

Where:

  • /S is the server to which you want to connect.

  • /U is the login ID you're using to connect to SQL Server.

  • /P is the password for the login ID you've chosen.

When you installed SQL Server 6.5, it set up ODBC on the server with a connection called LocalServer. Try the following to test that connection.

Go to a command prompt and enter the following to connect to your copy of SQL Server:

odbcping /Smyserver /Usa /P

The server should respond with something similar to the following:


CONNECTED TO SQL SERVER 
ODBC SQL Server Driver Version: 02.65.0201 
SQL Server Version: Microsoft SQL Server 6.50 - 6.50.201 (Intel X86) 
     Jun 16 1997 11:46:49 
     Copyright (c) 1988-1997 Microsoft Corporation

This means that ODBC is working fine. You can also connect to an ODBC DSN (Data Source Name). If something doesn't work right, a meaningful error message should display.

RegFont.EXE

RegFont.EXE changes the font used with SQL Enterprise Manager and the query tools. It modifies your Registry to change the fonts of these applications the next time they are started. To change the font, click Font (see Figure 3.34), and you see a list of all of the fonts available on your system. If you select a font that's too big or too wide, some information is not visible in Enterprise Manager--the dialogs' sizes don't change, just the text's font. Figure 3.34. The Registry Font Writer utility.

SQLMaint.EXE

This command supports routine system maintenance, including backup, consistency checking, and index maintenance. There's a wizard in SQL Enterprise Manager called the Database Maintenance Plan Wizard--it's the toolbar's rightmost icon. It helps you configure maintenance plans and backups for your databases without having to know a lot about what's going on behind the scenes. What the wizard actually does is configure automated tasks to call this utility. You learn more about how to use the wizard on Day 8. For now, here's the syntax:


SQLMAINT  [-?] | [-S 
server_name] 
   [-U 
login_ID [-P 
password]] 
   -D 
database_name -Rpt 
output_file    [-To 
operator_name] 
   [-CkDB | -CkDBNoIdx] [-CkAl | -CkAlNoIdx]    [-CkTxtAl] 
   [-CkCat][-UpdSts] [-RebldIdx 
free_space] 
   [-BkUpDB 
backup_path | -BkUpLog 
backup_path] 
   [-BkUpOnlyIfClean] 
   [-BkUpMedia {DISK [-DelBkUps 
number_of_weeks] | TAPE}]

Where:

  • -D database_name  Specifies the name of the database you are maintaining.

  • -Rpt output_file  This parameter identifies where to put the output file reporting errors, and the like from running this utility. It should be a full path and filename.

  • -S server_name  Just like the other utilities, the -S parameter is the name of the server you're running against.

  • -U login_ID  This is the login name you're using to run the utility. Usually this is SA.

  • -P password  This is the password for the login name you just specified.

  • -To operator_name  This is the name of an email account to which you'd like to have a copy of the report sent. This is discussed on Day 18.

  • -CkDB    -CkDBNoIdx  These parameters run the DBCC Newalloc commands. They are discussed on Day 8.

  • -CkAl    -CkAlNoIdx  These parameters run the DBCC Textall commands. Again, see Day 8.

  • -CkCat  The DBCC CHECKCATALOG command. Is this getting familiar?

  • -UpdSts  This runs t he Update Statistics command. It's discussed further on Day 20.

  • -RebldIdx free_space  This runs the DBCC DBREINDEX command. It's discussed on Day 20, as well.

  • -BkUpDB backup_path | -BkUpLog backup_path  This is where your backups are placed. This is discussed more on Day 8.

  • -BkUpMedia {TAPE | DISK}  Indicates you're backing up to either disk or tape.

  • -DelBkUps number_of_weeks  This indicates how long you want to keep your backups.

  • -BkUpOnlyIfClean  This option indicates that the database should only be backed up if it's not corrupt. This is the most powerful feature of this utility and is discussed further on Day 8.

NOTE Most of these options will mean a lot more to you when you're done reading the book than they do right now. Again, you learn more on Day 8.

SqlServr.EXE

SQLServr.EXE is the actual program that runs the MSSQLServer service. You will read, on Day 8, how to perform advanced data recovery.

Summary

There are quite a few utilities shipped with SQL Server 6.5. Many are provided to you in the form of shortcuts or program items, while others are not readily visible and are found in your \MSSQL\BINN folder. Each has a special purpose, and together they provide a very flexible toolset for use in supporting your database server.

Q&A

Q Which SQL Server utilities can you use to control the SQL Server-related services on your server?

A The SQL Service Manager and SQL Enterprise Manager.

Q Which utilities allow you to run queries against SQL Server 6.5?

A ISQL, ISQL/w, and the SQL Query Tool inside SQL Enterprise Manager.

Q Which tool provides most of the functionality you need to support SQL Server as an administrator (or a developer, for that matter)?

A SQL Enterprise Manager.

Q Which tool contains syntax help for you if you can't remember the exact way to type a command?

A SQL Server Books Online, or ISQL/w help.

Q If you don't see an object in Enterprise Manager, and you're sure that the object exists, what should you do?

A Refresh Enterprise Manager, either by logging off and then back in to your server, or refresh the appropriate folder.

Workshop

This Workshop provides quiz questions to help you solidify your understanding of the material covered, as well as exercises to provide you with experience using what you've learned. Try to understand the quiz and exercise answer before continuing on to the next day's lesson. Answers are provided in Appendix B.

Quiz

1. Where is the login security information kept in Enterprise Manager?

2. Where would you set the default Network Library for your client computer?

3. Which utility would you use to monitor Performance Information about SQL Server?

4. Where would you add TCP/IP Sockets support for SQL Server?

5. Where would you check to determine whether a service pack had been applied to your server?

Exercises

1. Start SQL Trace, set up a generic filter to capture all traffic, start ISQL/w or SQL Enterprise Manager, and log in to your server. Switch back to SQL Trace and examine the Transact-SQL code that has been sent to the server on your behalf by each tool. Make sure to run each tool separately.

2. Explore the Books Online, looking for additional information about the utilities discussed today. In particular, look for ISQL.EXE and SQLMAINT.EXE.

3. Use MS Query to connect to the PUBS database. Select data from some tables and view the relationships between tables.

4. Verify your configuration settings in SQL Enterprise Manager, and familiarize yourself with the available options. Change the User Connections option to 20. (You have to stop and restart your MSSQLServer service for this change to take effect.)

5. Start your client configuration utility. Go to the Advanced tab and add an entry for a server named Bob. Use Named Pipes as your DLL name, and in the Connection String window, type in your server name. Select Add. Now launch ISQL/w and log in to server Bob. Why did it work?
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)