Working with SQL Server 2000 Management Tools and Utilities
- The Microsoft SQL Server Program Group
- The Microsoft SQL Server–Switch Program Group
- Other SQL Server Tools/Utilities
- Summary
- Q&A
This chapter is from the book
This chapter is from the book
This chapter is from the book
Yesterday you learned how to install SQL Server 2000. As you quickly discovered, running the setup program isn't very difficult—in fact, it's almost trivial. It's understanding the critical selections you are making during setup that requires effort. After you make these selections, changing your collation is a significant effort, but it's alleviated somewhat in SQL Server 2000 because you can set collations from the database level down to the individual columns of each table. Changing other settings has generally been simplified by the management utilities that come with the product.
Today's lesson focuses on the tools and utilities you installed, assuming that you installed all of them. In this lesson, you examine not just the utilities that appear in your Start menu, but also the command-line utilities and utilities that simply don't have Start menu shortcuts. These utilities might be a bit hidden, but you shouldn't underestimate their value.
The Microsoft SQL Server Program Group
The best place to start when you're examining the available tools is the SQL Server 2000 program group in your Start menu. To open it, click Start, Programs, Microsoft SQL Server (see Figure 3.1). Note that only one copy of the tools is installed, no matter how many instances of SQL Server itself you install. Also, note that if you upgraded from SQL Server 7.0, the SQL Server 2000 tools completely replace your 7.0 tools, and the SQL Server 2000 tools are guaranteed to work against SQL Server 7.0.
){kind=link}
Figure 3.1 The Microsoft SQL Server program group.
This is the main group of tools you work with when building SQL Server database applications or when administering SQL Server. You must be familiar with each tool in this program group because you will almost certainly end up using every one of them before your 21-day adventure into SQL Server is over. Today's lesson examines them in the order you will probably want to look at them—not in the order they appear in your Start menu.
NOTE
MSDE does not include any of the graphical tools. The Client Network Utility is installed as part of the MDAC components, and osql.exe is your primary command line query interface to SQL Server. The graphical tools generally work with MSDE, but there are licensing restrictions and you should consult the MSDE license agreement or contact Microsoft to fully understand what license is required to use the administrative tools (Enterprise Manager, Query Analyzer, etc.) against an MSDE instance.
The readme.txt File
A great place to start is with the readme.txt file. This file contains important information you should read before you install, as well as information that didn't quite make it into the manuals before the product shipped. You should review this file once to make sure there's nothing you must do or change before moving on with SQL Server 2000. Of course, you did this yesterday before you installed the product, right?
Books Online
The SQL Server Books Online is your primary reference source for information (except, of course, for this book!). When you have a question about SQL Server, you can go to Books Online. Books Online is shipped in lieu of actual paper manuals, although you can order hard copies from Microsoft. The books ship as a compiled set of HTML pages, so you can view them within the context of Microsoft Internet Explorer 5 or later. Figure 3.2 shows the SQL Server Books Online utility.
){kind=link}
Figure 3.2 The SQL Server 2000 Books Online.
Books Online gives you the functionality you would expect from a set of manuals—the ability to view information, search for that information, and print paper copies. To print any topic, simply right-click the topic and select Print. You are prompted to print either the single page you are looking at or the page and all subpages.
TIP
Unless you want to kill lots of trees, be careful not to print from the top-level books.
The left pane of the Books Online is broken into four tabs by default:
Contents shows every SQL Server Book as if you had paper copies of the manual. If you know what book a topic is in, simply expand the book (by clicking the plus [+] sign) to view the contents. Most books have several levels you need to drill down into to find a particular topic of interest.
Index is a sorted list of just about every keyword in the Books, arranged alphabetically. Simply type the name of the word you are looking for, and the index scrolls to the closest index entry. To then display the contents (or, if multiple entries are listed for a keyword, an option to select which entry to go to), double-click the entry in the index.
Search is the place where you usually end up. Simply type the concept, command, or option you want to know more about, and click List Topics. The results of your search are then displayed. Again, simply double-click an entry to have the entry displayed on the right half of your screen.
-
Favorites, as in Internet Explorer, enables you to keep a list of favorites in the Books so you can simply click the entries here rather than search for items over and over. When you are viewing a topic, click the Add button (see Figure 3.3). When you want to go back to that topic, simply double-click the entry in your Favorites list.
){kind=link}
Figure 3.3 The Books Online Favorites tab.
The SQL Server Books Online is further broken into several "books", which you see on the Contents tab. Each book is explained in the following sections.
Getting Started
This book provides links to the rest of the books; documentation conventions (so you can understand the syntax statements and other formatting options); a list of the tools, wizards, and other features of SQL Server; and information on contacting SQL Server Technical Support. This book also includes information for people with disabilities and on backward compatibility. Everyone should read this book—it's quite short but very helpful in using the Books Online later.
What's New
This book includes a brief overview of product features that are new to people who have used previous releases of SQL Server. If you have used earlier versions of SQL Server, you should review this book to see what's changed.
Installing SQL Server
This book includes detailed information about setting up a new instance of SQL Server 2000. Because you've already read the lesson for Day 2, "Installing Microsoft SQL Server 2000," you don't need to read this book.
SQL Server Architecture
This book gives a detailed look at SQL Server 2000's architecture. It provides many details on how SQL Server works and how everything fits together. You will encounter most of the information in the SQL Server Architecture book on your 21-day adventure, but a great section to look at is "Implementation Details," where you can find all of SQL Server's documented limits. For example, you can find how big a database can be, how many tables can be in a database, and other detailed information.
Administering SQL Server
This book documents how to administer SQL Server. Because Sams Teach Yourself Microsoft SQL Server 2000 and MSDE 2000 in 21 Days teaches you everything you need to know to perform the basics (and some advanced options) of SQL Server administration, you have little need to review this Books Online book.
Creating and Maintaining Databases
This book documents how to create databases and all the objects to be contained in your databases. Again, Sams Teach Yourself Microsoft SQL Server 2000 in 21 Days teaches you everything you need to know on a day-to-day basis, so reading Creating and Maintaining Databases is also unnecessary.
Creating and Maintaining Data Warehouses
This book documents how to create data warehouses, a specialized type of relational database. The process of creating and maintaining data warehouses has become a specialty in its own right, separate from general database construction. If you intend to build or support data warehouses, you should review this book. Data warehousing typically builds on a basic understanding of building "ordinary" databases, so knowing the rest of SQL Server will give you a strong foundation should you want to pursue data warehousing. Data warehousing, although briefly mentioned on Day 22, "An Overview of Analysis Services," is beyond the scope of this book.
Accessing and Changing Relational Data
This book explains how to query and modify data in SQL Server. As with the other topics, Sams Teach Yourself Microsoft SQL Server 2000 in 21 Days has everything you need, so you don't need to review this Books Online book either.
XML and Internet Support
This book documents SQL Server 2000's support for XML. We will cover this topic extensively on Day 21, "Using XML with SQL Server 2000," but as with many topics, some advanced details are left to Books Online.
Transact-SQL Reference
This reference covers the precise syntax on how to call any object in SQL Server. If you can't remember the syntax for a T-SQL command, stored procedure, or function, you can look it up here. As you might expect, you'll examine all the important day-to-day commands in this Sams Teach Yourself book, but this part of Books Online is always available when you want to quickly look up something.
Optimizing Database Performance
This book presents performance tuning information for SQL Server. Day 20, "Performance Monitoring Basics and Tools," will help you with some performance tuning and monitoring basics, and you will encounter additional performance tuning information throughout your 21-day odyssey. However, performance tuning is a huge topic, so you might want to read this Books Online book (after you complete this 21-day book). I also recommend the Microsoft SQL Server 2000 Unleashed book from Sams Publishing for additional performance tuning information.
Replication
This book explains SQL Server 2000 replication. Day 16, "Understanding Replication Design Methodologies," and Day 17, "Implementing Replication Methodologies," fully cover SQL Server replication. So again, you don't need to review this part of the Books Online. See what a bargain this Sams Teach Yourself book is?
Data Transformation Services
The data transformation services (DTS) tool is explained in this Books Online book. Day 19, "Migrating Data Between Database Servers," covers most of what you need to use DTS. However, DTS is a very sophisticated tool and could be an entire 21-day book by itself. Therefore, for advanced transformations, you might choose to reference this book. You also can review Microsoft SQL Server 2000 Data Transformation Services (DTS) from Sams Publishing.
Analysis Services
This Books Online book explains Analysis Services, known as OLAP Services in SQL Server 7.0. We have chosen to include a brief overview of Analysis Services on Bonus Day 22, "An Overview of Analysis Services." Otherwise, you should review this Books Online book in detail.
English Query
Microsoft English Query is explained in this Books Online book. Again, we've chosen to include a brief overview of English Query on Bonus Day 23, "An Overview of English Query."
Meta Data Services
The Meta Data Services tool (known as Microsoft Repository in SQL Server 7.0) is explained in this book of the Books Online. We have chosen to include a very brief overview of Meta Data Services on Bonus Day 22.
Building SQL Server Applications
This book is huge! It contains all the programming references for various client and server technologies. The good news is that what you need to know about SQL Server commands is already interspersed throughout this Sams Teach Yourself book. This Sams book doesn't attempt to be a client programming reference, so some topics in the Books Online book are useful—particularly if you are writing client programs using open database connectivity (ODBC), Object Linking and Embedding Database (OLE DB), or ActiveX Data Objects (ADO). SQL Distributed Management Objects (SQL-DMO), which is the programmatic administrative interface for SQL Server, is also documented here.
Using the SQL Server Tools
This Books Online book covers basically what you're doing today—exploring each SQL Server tool. You will continue to use each tool for the rest of this book and by the end of your 21 days will be very adept at using most of the SQL Server toolset.
Troubleshooting
This book explains the most common problems and how to resolve them. After your 21 days, you should understand and be able to resolve most problems without ever referring to Books Online. This book is available just in case.
Glossary
The Glossary explains terms used throughout the SQL Server Books Online. For your convenience, a glossary of terms is also included in this book.
How To
This book explains how to perform specific actions using Transact-SQL, SQL Server tools and utilities, and other programming interfaces. You won't typically go directly to this book; hyperlinks (jumps) throughout the other books take you to the appropriate How To topic.
Service Manager
The SQL Server 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 see something similar to Figure 3.4.
){kind=link}
Figure 3.4 The SQL Server Service Manager utility.
The Server field contains the name of the server you are monitoring. The Services box shows which service you are examining, and the utility also contains a graphical representation of the state of the service. When a service is running, the green arrow is shown to indicate that, in this case, the SQL Server service is now running. As you learned on Day 2, the Service Manager is the main service that runs SQL Server.
NOTE
In SQL Server 7.0, the actual service names, such as MSSQLServer and SQLServerAgent, were shown in this utility. With the introduction of multiple instances in SQL Server 2000, the SQL Server team decided it would be easier to always say "SQL Server" and "SQL Server Agent" in this dialog. Thus, if you are controlling a default instance of SQL Server, the service name is MSSQLServer for SQL Server, and SQLServerAgent for the Agent service. However, for named instances, starting or stopping SQL Server is really controlling the MSSQL$Instancename service, just as you learned yesterday.
You can also stop or pause a service. Some services don't support being paused, and those that do vary in their behavior. For example, the SQL Server service continues to function when paused, but new connections aren't allowed.
NOTE
The Service Manager utility also contains an option to autostart each service when the operating system starts. This option is especially handy on Windows 9x computers because they don't have services that can be configured to start automatically. Simply make sure that the check box next to Auto-start service when OS starts is checked for each service you want started automatically.
The Services drop-down list shows which services can be controlled from this utility. They include the SQL Server service, SQL Server Agent, Distributed Transaction Coordinator, and Microsoft Search. You can use the Server drop-down list to select another server. Named instances are also shown in the drop-down list, for example, on my system (as shown in Figure 3.5, RHOME\TRADE is shown separately).
){kind=link}
Figure 3.5 Named instances in the SQL Server Service Manager utility.
NOTE
The Distributed Transaction Coordinator and Microsoft Search services show up in Service Manager for every named instance, as well as the default instance. They appear just for convenience; you still have only one copy of each service running on your computer, being shared by all instances of SQL Server on that machine.
The list can be somewhat unreliable, and the utility is flexible enough to allow you to simply type the name of the server (or servername\instancename) you want to examine. After you type a server name, click the drop-down list for Services. Service Manager then attempts to connect to the remote server and enumerate the installed services.
All the functionality of this application is also available from SQL Server Enterprise Manager. However, Service Manager runs from your taskbar and can be more convenient to access.
NOTE
At this point, your security will be checked. You must be a Windows NT/2000 administrator or server operator on the server you are connecting to in order to control services (if the remote computer is a Windows NT/2000 computer). SQL Server permissions have no effect on your Windows 2000 permissions, so being a SQL Server administrator doesn't guarantee that you can control SQL Server services.
The SQL Server Service Manager has a somewhat hidden functionality. Click the icon in the upper-left corner of the application, and the normal application menu shows up with a couple of additions (see Figure 3.6). Notice that two additional menu choices are available: Connect and Options.
){kind=link}
Figure 3.6 The additional features of the SQL Server Service Manager utility.
Connect
If you typed or selected a remote computer name under the Server list box, you can connect from the menu rather than click the Services list box. These two techniques perform the same action—attempting to connect to the remote computer to determine the state of the SQL Server services.
Options
When you choose Options, you see the dialog in Figure 3.7. In this dialog, you set the options for the Service Manager, such as the default service.
){kind=link}
Figure 3.7 The SQL Server Service Manager Options dialog.
Setting the Default Service
The default service determines which SQL Server service is monitored (and shown in your taskbar) by default when you start the Service Manager application (typically when you log in). If you were more interested in monitoring the SQL Server Agent service than the SQL Server service, you could change the default setting here.
Setting the Polling Interval
The polling interval determines how often Service Manager checks for the state of the services you are monitoring. The default interval is five seconds. This means that every five seconds the utility queries the server for the state of all SQL Server–related services.
Verifying Your Service Control Actions
The Verify Service Control Action option is enabled by default. When you click the appropriate icon, the action you've requested is taken, but only after you are prompted with an Are You Sure...? dialog (see Figure 3.8). To turn off this verification and simply have services stopped and started when you double-click the appropriate graphic, uncheck the check box in the SQL Server Service Manager Options dialog.
){kind=link}
Figure 3.8 SQL Server Service Manager's Are you sure...? dialog.
Client Network Utility
The Client Network utility isn't difficult to use, but it has a tremendous impact on client computer connectivity to your SQL Server. When you start the utility, you see a dialog that looks like Figure 3.9.
){kind=link}
Figure 3.9 The SQL Server Client Network utility.
This dialog tells you which network library you use by default when you try to connect to any SQL Server through SQL Server client software. Think of it as a language. If your server speaks only English and French and you attempt to speak German, the server doesn't understand you and can't respond. SQL Server 2000 changes the default network library to always use the TCP/IP (Transmission Control Protocol/Internet Protocol) Sockets network library, regardless of operating system.
In SQL Server 7.0, the choice of client network library depended on your operating system: On Windows NT computers, the default was to use the Named Pipes network library, whereas on Windows 9x computers, the default was to use the TCP/IP Sockets network library.
Yesterday's lesson discussed each network library during installation. Simply select the one that makes the most sense for your environment. The "universal" network library is TCP/IP Sockets, so accepting that option should allow you to connect to any SQL Server, regardless of which Windows operating system it's running on. Named Pipes is required on Windows NT/2000 computers, so you know it will work as well. However, Microsoft is moving away from Named Pipes, so sticking with TCP/IP Sockets is the wise choice for the future.
TIP
When you're connecting with a server name of (local) or (.) (a single dot), you bypass the default network library and connect to the locally installed default instance. For a named instance, use .\Instancename. When you use this option, you (by default) use the shared memory network library. With SQL Server 2000's client tools installed, bypassing the network libraries and using shared memory should happen automatically, even if you use the servername or servername\instancename syntax to connect. If you've used SQL Server before, however, you are probably in the habit of using the single dot to connect locally.
If you select a network library that's not supported by a server, your connection attempt results in the error Specified SQL Server Not Found. This error could mean that the SQL Server service isn't running on the remote computer, but it could also mean that the server doesn't support the network library with which you are attempting to connect.
Two other options at the bottom of this dialog are Force Protocol Encryption and Enable Shared Memory Protocol. The shared memory protocol is used to connect to a local installation of SQL Server, but you can turn it off if you want. You should get superior performance when connecting locally with the shared memory protocol.
The Force Protocol Encryption option requires that any connection from your client computer negotiate encryption with the SQL Server you're trying to connect to. Because SQL Server versions before SQL Server 2000 don't have this kind of network encryption, you can't connect to any earlier version, nor can any application running on your computer. Even for SQL Server 2000 computers, the server must have a certificate for the server installed. You'll examine certificates a bit more when you look at the server network utility later today.
The Alias Tab
You can also override the default settings and connect to a named server with a specific network protocol. For example, if you have a server named Sales, and the server's copy of SQL Server supports only the NWLink IPX/SPX Net Library but the rest of your servers use TCP/IP Sockets, you can add an entry on this screen. Click the Alias tab and then Add to see a dialog like the one in Figure 3.10. Select NWLink IPX/SPX (or whatever network library you want to use), type the name of the server alias (in this case, SALES), and configure any additional fields provided (such as the service name). Each network library supports different configuration options. Click OK to add the server with the selected protocol. After you do so, whenever you type SALES as a server name in any SQL Server application or utility from this computer, you will use the NWLink IPX/SPX Net Library.
){kind=link}
Figure 3.10 The Add Network Library Configuration dialog.
TIP
Yesterday you read that to connect to a named instance of SQL Server 2000, the client computers must have MDAC 2.6 installed (the version that comes with SQL Server 2000). However, you can use a workaround—an alias. For example, to connect to the named instance TRADE on my computer (RHOME), the named pipe name is \\rhome\pipe\MSSQL$Trade\sql\query. So, enter an alias for some servername (such as NAMEDINST), specify Named Pipes as the protocol, and then enter this pipe name. Now, whenever you type NAMEDINST, you'll go to the Named Pipe of the named instance and connect to your named instances. This solution is very handy if, for some reason, you're not allowed to update the client software on your computers but still want to connect to a named instance.
The DB-Library Options Tab
When you click the DB-Library Options tab, you see the dialog in Figure 3.11. Note that DB-Library is supported in SQL Server 2000 for backward compatibility only. ODBC and OLE DB technologies are the preferred mechanisms to connect to SQL Server 2000. All the client utilities that ship with SQL Server 2000 (except isql.exe) use ODBC. isql.exe still uses DB-Library, and older, user-written applications might still use this network library.
){kind=link}
Figure 3.11 The DB-Library Options tab.
This tab tells you which version of the DB-Library dynamic link library (DLL) you are using on your system. It indicates where the file is physically located, the date and size of the DLL, and the name of the DLL. You also have two options available: Use International Settings and Automatic ANSI to OEM Conversion.
International Settings uses certain settings, such as date and time formats, from Windows 9x, Windows NT, and Windows 2000. For example, if you're using Windows 2000 and have enabled a non-United States setting, your local settings are used in favor of the SQL Server settings.
The ANSI to OEM conversion translates data coming back from SQL Server into the local code page used by your client.
Generally, you shouldn't disable either of these options.
The Network Libraries Tab
The Network Libraries tab shows each network library installed on your computer, as well as the filename, version, file date, and file size. This information is most useful when you need support. The versions tell your support vendor which service packs and network library releases you're using. All network libraries are installed on your computer by default.
Server Network Utility
The Server Network utility looks and feels like the Client Network utility. They are, as you might guess, closely related. Unlike the Client Configuration utility, which controls how your applications connect to SQL Server, the Server Network utility reflects which network libraries SQL Server 2000 listens on (see Figure 3.12). Using the language comparison already described, this utility presents the list of languages your server knows how to speak. If you try to connect by using any other network library, your SQL Server can't hear you.
){kind=link}
Figure 3.12 The Server Network utility.
Simply click the Enable button to have SQL Server listen on an additional network library. To reconfigure a network library, click the Properties button. To disable a network library, highlight the library and click the Disable button. The changes you make will take effect the next time the SQL Server service is restarted.
Take a quick look at Figure 3.13. Notice that, just as discussed yesterday, only a subset of network libraries is supported with named instances of SQL Server 2000 (TCP/IP Sockets, Named Pipes, NWLink IPX/SPX). Support for the other network libraries is in a state similar to DB-Library in that no enhancements, including named instance connectivity, will happen from this point forward.
){kind=link}
Figure 3.13 The Server Network utility for a named instance.
The Network Libraries tab is exactly the same as the Network Libraries tab of the Client Network utility, so we won't examine it further here.
Force Protocol Encryption
When you check this option, the server forces encryption on all connection attempts. If encryption can't be agreed on between the client and server, no connections can be made. Encryption in SQL Server 2000 is significantly different from that in SQL Server 7.0, which depended on the multiprotocol network library.
SQL Server 2000 uses Secure Sockets Layer/Transport Level Security (SSL/TLS) to encrypt all SQL Server traffic (if you want it to). This capability can be very handy if your SQL Server holds sensitive data, and you want to prevent someone from viewing your data as it goes across a network.
Sounds interesting, but how do you make it work? First, get a public key certificate for your server. It needs to be in the name of your computer's fully qualified DNS name. Then you need to install the certificate on the SQL Server machine through Internet Explorer. If SQL Server finds a certificate that matches the servername, it simply uses this certificate. The certificate must come from a certificate authority that's trusted by your client computers.
If you're using certificates inside your company or organization, you should show this page of the book to your certificate administrator, and he can help you get the right certificate and install it. Companies often have their own certificate infrastructure, so the following directions won't work for you.
If you're doing this procedure yourself, simply get a certificate from a certificate authority, such as VeriSign (http://www.verisign.com), that's trusted by your Internet Explorer clients (VeriSign even gave a trial certificate for free at the time this book was written). To figure out the DNS name of your machine, look in the Network Identification tab by right-clicking your My Computer icon on the server's desktop. There, you can find the full name of your computer (if not, click Properties and you will see it there). For instance, my computer name might be RHOME.COMPUTERS. MYNETWORK.COM. Then ask for either a "server identity" certificate or a "Web server certificate." When you have it, use Internet Explorer to install the certificate (Tools, Internet Options, Content, Certificates in IE 5.0), and select Import to load the certificate emailed to you by VeriSign. Assuming that everything worked correctly, encryption should automatically start working the next time you start your SQL Server service.
If, for whatever reason, encryption doesn't work, you get a very specific message on the client that you were unable to negotiate encryption with the server. This message typically means that the certificate isn't trusted by the client. You can see the names of certificate authorities (companies) trusted by Internet Explorer by looking at the same location as before (Tools, Internet Options, Content, Certificates in IE 5.0), except now click Trusted Root Certificate Authorities. We chose VeriSign because it's been around a long time and is in all the lists of trusted certificate authorities.
Enable WinSock Proxy
The Enable WinSock Proxy option allows you to have a proxy server forward SQL Server requests from the Internet (or any other network) to SQL Server. Click Enable WinSock Proxy option, and then fill in the TCP/IP address of the proxy server machine, as well as the port you want to have act as a proxy for your SQL Server. This option allows requests to the proxy server to be rerouted to SQL Server and for SQL Server to respond to these requests. Most SQL Server computers being used for Internet e-commerce are the back end for Web sites and don't need to be exposed directly on the Internet. If you want to do so, however, the option is available.
Query Analyzer
The SQL Server Query Analyzer is your primary interface for running Transact-SQL queries or stored procedures. You will be using this utility throughout the rest of your 21 days, and it's a good idea to get comfortable with several available options. This said, it's worth noting that many features of this application are somewhat sophisticated, and you will explore them as you progress through this book.
Because you will use this application frequently, you might choose to simply click Start, Run, and type the application name. For historical reasons, this application's name is ISQLW.exe. So click Start, Run, and type isqlw to start the Query Analyzer.
As you will learn on Day 5, "Setting Up Login and User Security," every operation in SQL Server is secure. Therefore, you must first log in and identify yourself to any SQL Server to which you want to connect (see Figure 3.14). As we mentioned in the discussion of the Client Network utility, you can enter (local) or . to connect to your local default instance of SQL Server. You can also leave the server name blank. The Query Analyzer figures out what you mean and connects to your default instance SQL Server. If you are at the computer running the SQL Server to which you want to connect, the default local network connection (TCP/IP Sockets) is used. Otherwise, whatever client network library you have configured is used. To connect to a named instance, simply type the SERVERNAME\InstanceName combination in the SQL Server text box.
){kind=link}
Figure 3.14 The Connect to SQL Server dialog of the SQL Server Query Analyzer.
If you like, you can have SQL Server automatically start if you attempt to connect and the SQL Server service isn't running. This option is fine for local or Windows 9x computers, but make sure you think it through when you're connecting to production servers. Why is the server down? Is it for maintenance, which you will harm if you restart the SQL Server service prematurely?
You also need to provide login information. If you are on a Windows 9x computer, you can use only the SQL Server Authentication option. Use a login name of sa and the password you selected during setup. On case-sensitive servers, the login is also case sensitive, so be sure to enter it in the proper case. If you are on a Windows 2000 computer or connecting to a copy of SQL Server running on Windows NT/2000, select the Windows NT Authentication option to log in using your Windows NT/2000 security credentials. If you can do so, using Windows NT authentication is much simpler. By default, members of the Windows NT/2000 local administrator's group can log in to SQL Server. In either case, you are logging in as a system administrator or superuser to SQL Server. There's nothing you can't do, so be careful.
After you click OK or press Enter, you are logged in to the appropriate instance of SQL Server (assuming that you've typed valid login ID and password information or your Windows NT/2000 credentials allow you to connect). You then see a screen that looks like Figure 3.15.
){kind=link}
Figure 3.15 The SQL Server Query Analyzer.
If your login doesn't go smoothly, you might see a dialog similar to Figure 3.16. Usually, you see this dialog if you've mistyped your password. Simply try again. You should also verify that you've typed your login name properly. If you've changed the sa password, be sure to type the newest one in the connection dialog in Figure 3.14. You can easily produce this error if you followed along in Day 2 by trying to connect to RHOME\Trade (or whatever you installed as a named instance) with an incorrect sa password.
){kind=link}
Figure 3.16 Login failed dialog.
You might also see a dialog like Figure 3.17, indicating that you tried to connect with SQL Server Security credentials. In this case, the server (your default instance) doesn't support anything but Windows Authentication Mode (also called integrated security), and this error is letting you know that. Go back to your login dialog and change to Windows NT Authentication; the connection should work then.
){kind=link}
Figure 3.17 Login failed—not using a trusted connection.
Figure 3.18 shows another error you might get. If you receive this error, you must look for several things. First, verify that the SQL Server service is running on the computer you are trying to log in to. If it is, the problem might be with your client configuration (see the Client Network utility discussion earlier today). Otherwise, make sure that you typed the server name correctly. To see a list of servers on the network, rather than type a server name, click the three dots next to the SQL Server name box to see a list of the active SQL Server machines on your network. I have had mixed success with this list. Don't be alarmed if your server doesn't show up here; you can still connect to a server that isn't visible by typing its computer name here. In this case, I tried to connect to a server named random, which doesn't exist on my network.
){kind=link}
Figure 3.18 Network connectivity problems.
Figure 3.19 shows another blank query analyzer, but there are a few things worth noticing. Two separate connections are actually open here. Each connection has a title bar that identifies the following:
){kind=link}
Which computer you are logged in to
Which database you are currently using
Which login you used to connect
The title of any query you have opened (examined shortly)
Which window number is being displayed
Figure 3.19 Multiple connections in a single instance of the Query Analyzer.
As you might guess, having this information in the title bar when you have 40 or 50 connections open can be extremely handy. So, in Figure 3.19, you see one connection as the user sa and one using my Windows security credentials, RHOME\rwaymi. Both are connected to my computer, RHOME, in the master database, and each one is connected to a separate instance. There's no reason they couldn't both be connected to a single instance, except that our default instance supports only Windows authenticated connections.
The Query Toolbar
After you successfully log in, you can start running Transact-SQL queries (you will learn how to program these queries starting on Day 10, "Retrieving Data with the Select Statement").
The Query Toolbar is now available and has icons that represent shortcuts to the most commonly used functionality.
You can open multiple windows inside the Query Analyzer by selecting the New Query button (the leftmost button on the toolbar in Figure 3.20). Notice that this isn't the same as having multiple connections because each one could have been to a different computer. Each new query window you open is a separate connection to SQL Server; if you open too many, you might be wasting resources on your SQL Server.
){kind=link}
Figure 3.20 The Query Analyzer toolbar.
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, the Save Query/Result button, either saves the text in your query window to a file or, if you have run a query and selected (or clicked in) the results window, saves the results of a query you have run. The next button loads a "template" SQL query, so you can quickly develop variations of work you've done before. The next several buttons are the Windows standard Cut, Copy, and Paste buttons. They are followed by a button to clear the contents of the current query window, followed by the standard Windows Find button. They become enabled when appropriate. The next button gives you the option of undoing whatever it was you just did (such as undo a deletion of some text you just accidentally wiped out).
The Execute Mode button is next, and it's pretty powerful. When you click the down-arrow next to this button, you are presented with a drop-down list of options that determine how and where your results will appear when you run a query. The default option, Results in Grid, can be very nice because many names in SQL Server 2000 can be 128 Unicode characters long. The default of displaying the text usually pads out all 128 characters with spaces if the names aren't fully used. The Grid option typically leaves enough space only for the widest data column to be displayed. You can also switch to Results in Text, which can be nice when you want to look at all that long text. You can even automatically have the results of your queries routed directly to a file. Figure 3.21 shows each option.
){kind=link}
Figure 3.21 Query mode selection.
The next option on the menu in Figure 3.21 is Show Execution Plan. This option shows you the methods and indexes SQL Server uses to find the data you request with your queries. Unlike the text, grid, or file options, where you either have one or the other turned on, the Show Execution option is either on or off, regardless of the grid, text, or file options.
You will then see the next option, Show Server Trace. This option, available only to SQL Server system administrators, shows you all the underlying commands and several statistics about those commands from within SQL Server. It is basically a mini-version of the SQL Server Profiler, which you will examine later today.
The final option is Show Client Statistics. This option shows you everything that Query Analyzer learned from SQL Server when it ran your last command, as well as some averages over time as you run additional commands.
Enable each of these three Show options so you can see what each one produces. Unfortunately, you have to do so one at a time to set each one. When all three are on, verify that you are still in the default mode to return Results in Grid.
The easiest way to understand these options is to look at an example. Type the following text in your query window:
use pubs select * from authors
This query is straightforward when you understand what these commands mean. The first statement, use pubs, tells the server to switch to the pubs database, a sample database installed on all SQL Servers. The second statement, select * from authors, requests that SQL Server return all rows and all columns of data from a table or view named authors (and, because you switched to the pubs database, you know it's in pubs). So, in English, these statements mean
Switch to the pubs database
After you type the two preceding commands, you can verify that you entered them correctly. Notice that several options on the toolbar are now available. Click the blue check mark on the toolbar to verify that you've typed everything correctly. SQL Server parses your query—meaning it checks that the query is syntactically correct. If the query works okay, you get back
The command(s) completed successfully.
Now, to actually run the query, click the next button over—the Play button. You can also select Query, Execute; press F5 on your keyboard; or (my favorite) press Alt+X. After you run the query, the results pane appears with all information about authors in the pubs database (see Figure 3.22).
){kind=link}
Figure 3.22 Your query results.
Notice that while your query was executing, the Stop toolbar button (the square) became available. Using it, you can cancel a query while it's still running. Your queries should run so fast that you'll never get a chance to cancel them, though.
Now, back to the Text versus Grid option. Click the Current Mode button on the toolbar, and switch to Results in Text. You can also press Ctrl+T to set this option. Now rerun the query and examine your results pane. Notice that all the same tabs are listed below, but your query results are formatted a little differently.
To take this example a step further, add another line of code so that your query pane contains the following Transact-SQL commands:
use pubs select * from authors select * from titles
Run these queries again (using one of the methods examined earlier), and notice that Query Analyzer simply stacks up the results in the same, single results window. For each query you run, by default, Query Analyzer simply builds a new grid or separates out the text within the same window.
Moving on to the rest of the toolbar, the next option is a drop-down box that provides a list of the installed databases on the server to which you are connected. If you change the value here, the SQL script you run uses this database unless you specify a different database in the SQL script itself. When you do specify a different database in your script, when the script is finished running, the changed database is reflected in this list box.
The next button shows you how SQL Server estimates it will run your queries internally (as opposed to the previous option you set, Show Execution Plan, which shows you how the query actually ran). Why two options? What if a query takes multiple hours to run, but all you want to see is how SQL Server thinks it will execute the query? You'll look at this feature in more detail on Day 13, "Indexing for Performance."
The next option is the Object Browser on/off toggle. You can see the Object Browser turned on in the previous figures on the left side of the screen. By using it, you can easily browse all database objects for each connection. It's much more powerful than it first appears. You can drag and drop any object into your query window. That's pretty handy, but look what happens when you right-click the object and drag it into your query window. A pop-up appears (see Figure 3.23), allowing you to choose a number of different SQL statement options, such as writing a create table script or select query script—very powerful stuff. You may well find yourself using this feature frequently as you progress through the next few days.
){kind=link}
Figure 3.23 Drag and drop from the Object Browser.
Getting tired of all these great new features yet? Just wait, there are more! The next option is the object search. Click it to bring up the dialog in Figure 3.24. Can't remember where you put the table that had the salary column? Just use this feature, and the Query Analyzer searches everywhere you ask to find that table. This feature can be very handy when you're working on large or complex systems, or systems you haven't seen in a while.
){kind=link}
Figure 3.24 The Object Search dialog.
Skip over the next button for a moment. The last option on the toolbar simply allows you to always see the results window, even when you haven't run a query yet.
Configuring SQL Server Query Analyzer Options
The button with a hand holding a piece of paper (the one you skipped over a minute ago) displays the Query Analyzer Current Connection Properties dialog (see Figure 3.25). You can see the same dialog by selecting Query, Current Connection Properties.
){kind=link}
Figure 3.25 The Current Connection Properties dialog.
Until you begin working with advanced SQL Server features, you shouldn't need to change any of these options, but at least you'll know where they are if you do need to change them.
You can configure and change Query Analyzer options in yet another place. You reach the dialog shown in Figure 3.26 by selecting Options from the Tools menu. A significant number of options are exposed here. SQL Server's Books Online explains each of them in detail, but you won't set most of them until you become a much more advanced SQL Server programmer or administrator.
){kind=link}
Figure 3.26 The Query Analyzer Options dialog.
We will sometimes note that you are running a script. A Transact-SQL script is any set of SQL commands stored and executed together. In fact, the File, Open and File, Save options allow you to save a query (or set of queries) and then run them again later. By default, scripts have the file extension .SQL. You can change that extension on the General tab, but changing it isn't recommended. You can also reset the default locations to search for scripts, as well as reset options for the locations of your results from running queries and the default extensions for those saved results.
On the Editor and Results tabs, you can reconfigure your development environment to whatever settings you are most comfortable with. The Fonts tab lets you change the default fonts used to display queries and results in the user interface. The Connection Properties tab shows you the same connection properties you saw a minute ago for your current connection (the defaults set for all connections).
The Connections tab allows you to configure how long Query Analyzer will wait when attempting to log in to SQL Server. You can also configure how long to wait for long-running queries (0 means wait forever; any other value is a number of seconds), as well as set language and other international settings.
Choose these options carefully; they can really change your output, and you might easily forget you have set any options here.
Help Options
The last option to examine here is Help. If you can't remember the syntax of a command (the right way to type a command or a list of options), highlight a keyword and press Shift+F1. Help for the command should pop up in a window. Help is built in for just about every Transact-SQL command. Unlike in previous releases of SQL Server, SQL Server 2000 help always launches Books Online, taking you to the most appropriate topic for the help you requested. This feature should assure you the most accurate help by having only one definitive source of information about syntax and query options.
Another really useful option is Alt+F1. 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 you're asking for help with, but the help typically shows you useful properties about an object.
TIP
If you highlight a command in the query window and then execute the query, just the highlighted text is run. This way, you don't have to execute every statement in a window.
Enterprise Manager
As you can see, Query Analyzer is a very powerful tool. It has been greatly enhanced in SQL Server 2000 to be the programmer's and the database administrator's best friend. However, you still have SQL Server Enterprise Manager, which is meant to be SQL Server's primary graphical administrative and development interface. There's very little SQL Server Enterprise Manager can do that you can't accomplish by using a Transact-SQL command in Query Analyzer. However, using the Enterprise Manager is sometimes more convenient, especially when you are new to SQL Server.
SQL Server Enterprise Manager is an MMC snap-in. MMC stands for the Microsoft Management Console, a common utility that Microsoft and third-party vendors can use as the common administrative interface to their respective products. All Microsoft BackOffice products, as well as system components within Windows 2000 and Windows XP, use the MMC as their primary administrative interfaces.
Registering a Server
When you start SQL Server Enterprise Manager, you might need to register a server. If you are sitting at a computer with SQL Server installed, your local SQL Server is registered for you during setup. If you have an earlier release of SQL Server installed on the Windows 2000 computer on which you installed SQL Server 2000, you might also see a SQL Server 6.x group. To register a server, expand the Microsoft SQL Servers option, and you should see the default group, SQL Server Group. Highlight the SQL Server Group option, and then from the Action menu, select New SQL Server Registration. The Register SQL Server Wizard then appears (see Figure 3.27).
){kind=link}
Figure 3.27 The Register SQL Server Wizard.
I recommend that you check the box to not use the wizard in the future because registering a SQL Server is one of the easier tasks you can perform. Click Next, and you are presented with the default dialog to register a SQL server (see Figure 3.28).
){kind=link}
Figure 3.28 The Registered SQL Server Properties dialog.
Enter your computer name as I've done in Figure 3.28, and then select the security mode you want to use. You can use Windows NT security mode (if the SQL Server is running on Windows NT), or you can specify a SQL Server security login (which works only on Windows 9x by default). Select the SQL Server login option, and complete your SQL Server login credentials if you select to use SQL Server Authentication. Because you previously installed your default instance in Windows Integrated Mode, you must select Use Windows NT Authentication.
Notice that you can also choose to be prompted for your login and password every time you try to connect (if you choose to use SQL Server authentication). Use this option on a Windows 9x computer to protect your security. If you are concerned about security, you should probably be using Windows 2000, which is much more secure than the Windows 9x operating systems.
You can select to add this server under the default grouping of servers or to create a new grouping in the Server Group text box near the bottom of the dialog. This grouping is used strictly as an organizational tool for your desktop. The SQL Server computers on your network have no knowledge of this grouping. You can also change several options such as to automatically start SQL Server when you use SQL Server Enterprise Manager and try to connect to the server and view the system databases. For the purposes of this book, make sure that you check all available options.
Now click OK—that's it! You've configured your first registration for Enterprise Manager.
NOTE
Of course, you just got an error that says A Server with this name already exists, didn't you? When you install SQL Server, the setup program automatically registers your local installations in your copy of Enterprise Manager. So, for your local copies, you don't need to register them; they are already there. For any remote copies, however, you need to go through this registration process.
Examining How Enterprise Manager Works
Close the registration dialog by clicking the Cancel button. You see that your servers are already registered in the left pane of Enterprise Manager when you expand the SQL Server Group and then expand each server (see Figure 3.29). You can tell when you are connected by the presence of the red lightning bolt through your server icon. In Figure 3.29, you can see that I'm connected to both my default instance and my named instance of SQL Server, both installed on my local computer (RHOME). Note: You may have to select View -> Taskpad to get this view on your screen. Taskpads are turned off by default to make Enterprise Manager run faster.
){kind=link}
Figure 3.29 Enterprise Manager with the servers registered.
Within each server, the Databases folder lists the databases installed on your server. The Data Transformation Services folder shows any DTS "packages" that you've set up, as well as Meta Data Services packages. The Management folder shows administrative management features of SQL Server. The Replication folder shows any replication configuration changes that you've made to your server. The Security folder allows you to configure security in SQL Server. The Support Services folder enables you to control other SQL Server-related services on your computer, such as the Distributed Transaction Coordinator and full-text search. Figure 3.30 shows an expanded view of each folder. When you highlight your server, information about that server is displayed as an HTML page on the right side of the screen.
){kind=link}
Figure 3.30 The left pane of Enterprise Manager with expanded folders.
Now expand the pubs database folder, and then highlight the pubs database in the left pane. Notice how a new Web page is then displayed on the right (see Figure 3.31). This is how Enterprise Manager works: Container objects are typically shown on the left, and the contents of the container objects, or other information, are displayed on the right.
){kind=link}
Figure 3.31 The pubs database.
Some dialogs also open separate windows or dialogs for you to work with. For example, right-click the pubs database folder on the left, and select Properties from the pop-up menu. Notice that a new dialog opens on top of Enterprise Manager (see Figure 3.32). Property sheets (dialogs with the descriptions of properties of an object) often appear in these separate dialogs. Click Cancel to dismiss the pubs database's property sheet without making any changes.
){kind=link}
Figure 3.32 The properties of the pubs database.
Another feature that's worth examining is the menu options. The options available change depending on what you've highlighted. For example, you see the options in Figure 3.33 if you click the Tools menu with the pubs database highlighted. Don't be intimidated; you will learn what every option here does as you progress through the rest of your 21-day journey into SQL Server 2000.
){kind=link}
Figure 3.33 Menu options in SQL Server Enterprise Manager.
Explanations of Enterprise Manager could literally fill another 20 to 30 pages. However, you should know enough now to begin using the tool as you move on. You will use Enterprise Manager every single day, so by Day 21 you will be an Enterprise Manager expert!
Import and Export Data
The Import and Export Data choice in the Microsoft SQL Server program group starts the DTS Wizard. You will examine DTS in greater detail on Day 19.
Configure SQL XML Support in IIS
By using the Configure SQL XML Support in IIS option, you can set up your Web server to allow direct queries into SQL Server using Extensible Markup Language (XML). You will examine this option and more on Day 21.
Profiler
The SQL Server Profiler utility enables you to monitor all activity on your SQL Server. You can also use the Profiler to perform performance tuning activities, such as examining the execution plan that SQL Server will use to run your queries. The Profiler has sophisticated filtering mechanisms to let you keep track only of specific users, applications, or types of commands. You can monitor more than 100 different events from SQL Server 2000. You'll examine the Profiler on Day 20.