Ships from: Chatham, NJ
Usually ships in 1-2 business days
[Figures are not included in this sample chapter]
[Figures are not included in this sample chapter]
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.
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.
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.
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.
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).
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).
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.
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.
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.
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.
Again, it's exactly what its name implies. However, watch out for the version issue mentioned in the previous section.
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.
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.
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.
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.
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.
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.
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 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.
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 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.
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:
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.
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.
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.
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."
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 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 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.
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.
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.
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.
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.
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."
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.
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.
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.
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 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.
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.
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 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.
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 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 verifies that a database from a previous version of SQL Server is compatible for an upgrade. It primarily looks for three problems:
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 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:
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.
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 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:
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 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.
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:
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 is the actual program that runs the MSSQLServer service. You will read, on Day 8, how to perform advanced data recovery.
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.
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.
Overview