After you have installed SQL Server 2000, reviewed the results of the installation, and started the SQL Server service, you are ready to begin working with the primary SQL Server 2000 administration tools and utilities. These are Osql, the primary command-prompt utility, and SQL Query Analyzer and SQL Server Enterprise Manager, the primary graphical tools for querying and administering SQL Server 2000. As a database administrator, you will use these tools daily and need to become very familiar with their use.
Osql is a 32-bit command-prompt utility used to query an instance of SQL Server 2000 interactively using Transact-SQL statements, system procedures, and script files. It is also used to submit batches and jobs, including operating system commands, to SQL Server 2000. Use the GO command to signal the end of a batch and tell the SQL Server service to process the batch. By default, results are formatted and returned to the console, but can also be sent to a text file. Use QUIT or EXIT to close Osql and return to a command prompt. Osql uses the ODBC API to communicate with SQL Server 2000. Osql is frequently used to test basic connectivity to SQL Server 2000.
When using Osql to connect to SQL Server 2000, there are many arguments that you can pass as part of your connection string. Be aware that arguments passed to Osql are case-sensitive. Also, be aware that a dash (-) and a slash (/) are used interchangeably. SQL Server Books Online provides the syntax for all arguments supported by Osql, along with some examples. The two most important arguments for getting started are the authentication method and the server/instance to which you want to connect.
If you want to connect using Windows authentication using your Windows 2000 or Windows NT 4.0 user account, use the –E argument. Otherwise, use the -U and the -P arguments to pass a valid SQL Server user login ID and password. Be aware that both the login ID and the password are case-sensitive. If you want to use a SQL Server user login ID, SQL Server must be configured for Mixed Mode authentication. Use the –S argument to specify the server/instance to which you want to connect. If no server name is specified or no instance is specified, Osql connects to the default instance on the local server, or the named server. You can use Osql to connect to local and remote servers. Use the –L argument to display a list of all local instances and all remote instances broadcasting on the network. To connect to a named instance on a local or remote server, you must specify the server name followed by the instance name. For example, to connect to a named instance on your local computer using Windows authentication, use the following command: OSQL –E –S YourServerName\YourInstanceName. See Figure 3.11.
Using the Osql command with the –E switch.
In this practice you use Osql to connect to SQL Server 2000 instances.
To use Osql to connect to SQL Server 2000 instances
The Run dialog box appears.
The Command Prompt window appears.
Osql connects to the default instance of SQL Server 2000 on your local server (SelfPacedCPU) and then displays a 1> prompt, waiting for more input from you. If you cannot connect, you will receive an ODBC error message. A common error at this point is typing a lowercase "e" rather than an uppercase "E".
Notice that the 2> prompt appears. The first command is not executed because you have not informed SQL Server 2000 of the end of a batch.
Notice that the 3> prompt appears. Neither command is executed.
SQL Server 2000 is queried and returns the name of your local server (SelfPacedCPU) and the version (including the edition) of SQL Server (SQL Server 2000 - Enterprise Evaluation Edition) that is installed on your computer. In addition, the version of your Windows operating system is displayed. It also returns you to a 1> prompt for a new query.
Osql exits and returns you to a command prompt.
Osql returns the names of the instances of SQL Server installed on your local computer, or broadcasting on your network.
Osql connects to the named instance of SQL Server 2000 you installed on your local server and then displays a 1> prompt, waiting for more input from you.
SYSTEM_USER is a niladic function used to return the current system username.
Osql returns your current security context within SQL Server 2000. Because you connected to SQL Server 2000 using a trusted connection, your current security context is SelfPacedSQL\Administrator. Osql then displays a 1> prompt, waiting for more input from you.
SQL Query Analyzer is used for creating and managing database objects and testing Transact-SQL statements, batches, and scripts interactively. SQL Query Analyzer is one of the programs in the Microsoft SQL Server program group, and is available from the Start menu. When you launch SQL Query Analyzer, you can choose to connect to local or remote instances of SQL Server 2000 (as well as earlier versions of SQL Server). In the Connect To SQL Server dialog box, you can type or browse to select an instance of SQL Server to which to connect. This includes connecting to earlier versions of SQL Server. Be aware that (local) refers to the default instance on the local server and that using a period or a blank entry in the SQL Server drop-down combo box also refers to the default instance on the local server. After selecting the instance to which you want to connect, you select the authentication method you want to use to connect. You can choose either Windows authentication or SQL Server authentication. Finally, if the SQL Server instance to which you want to connect is not started, you can choose to start it.
After you connect to SQL Server using SQL Query Analyzer, you need to become familiar with the SQL Server Query Analyzer interface. See Figure 3.12.
Multiple query windows can be displayed. Each query window can be a connection to a different instance of SQL Server 2000 (or earlier version of SQL Server), or simply a different query window using the same connection. The title bar for each query window displays connection information specifying the instance, the database, and the user security context for the connection. Each query window contains a Query pane and a Results pane. You execute a query by clicking the Execute Query button on the toolbar or by pressing the F5 key or Ctrl+E. You can highlight a specific Transact-SQL statement to execute just a selected statement from a number of statements within a query window. You can also highlight a specific Transact-SQL statement or portion thereof, and then press Shift+F1 to display SQL Server Books Online for that particular statement or portion of statement. The results of a query are displayed in the Results pane. The Results pane contains multiple windows. The Grids tab displays the result set or sets from the query or queries. By default, the results are displayed as a grid, but can also be displayed as free-form text. The Message tab displays information and error messages related to the query. The Query status line also provides information regarding the query, including how long it has been running if it is still running, the number of rows returned, and the current row number if you are navigating the result set.
The SQL Query Analyzer interface.
You can configure SQL Query Analyzer to display or hide the object browser. Either press the F8 key or click the Tools menu, point to Object Browser, and click Show/Hide. The Object Browser is a powerful tool used to navigate and work with the objects in a database. The Object Browser provides object scripting, stored procedure execution, and access to table and view objects. It is used primarily by database developers, but can be very useful for database administrators as well.
Each connection has connection properties. You can view the current connection properties by clicking the Current Connection Properties button on the toolbar or by clicking Options from the Tools menu. This allows you to set the connection properties for all new connections made with SQL Query Analyzer. Do not modify these properties without fully understanding the consequences. In general, the details of the connection properties are beyond the scope of this book. Use SQL Server Books Online for more information.
In this practice you use SQL Query Analyzer to query SQL Server 2000 instances.
To use SQL Query Analyzer to query SQL Server 2000 instances
SQL Query Analyzer appears displaying the Connect To SQL Server dialog box.
You are connected to the default instance of SQL Server 2000 on your computer using your Windows user account. Verify this by reviewing the active query window title bar.
This query will use information schema views to query this instance of SQL Server 2000 for all databases in this instance. Information schema views will be covered in Chapter 5. Notice the color coding. Blue indicates a keyword and gray indicates an operator. Refer to SQL Server Books Online for more information regarding color coding.
Notice that the result set from the query is displayed in the Results pane in the form of a grid. Information regarding all six databases is returned.
Notice that the Open Table window displays the same information as the previous query.
An informational message regarding the number of rows affected by the query (6 rows affected) is displayed.
SQL Server Books Online appears displaying information regarding INFORMATION_SCHEMA.SCHEMATA in the console tree.
Notice that the color of @@SERVERNAME changed to magenta when SQL Query Analyzer recognized this character string.
Notice that you can select and execute a single query in a query window. The name of your server (SelfPacedCPU) is returned.
The contents of the Query pane are erased.
This toggles the Results pane, hiding the Results pane.
This toggles the Results pane again, restoring the Results pane to visibility.
A new query window appears. Compare the two Query panes. Notice that you are connected to the same database in the same instance of SQL Server 2000 using the same security context.
Notice that the current database displayed on the toolbar changed to Northwind. Also notice that the active query window title bar now indicates a connection to the Northwind database rather than the Master database.
The Connect To SQL Server dialog box appears.
Notice that this named instance now appears in the SQL Server drop-down combo box.
A new query window appears. The title bar indicates that you are connected to the Master database in your named instance. Notice that the current database on the toolbar is Master.
SQL Server Enterprise Manager is the primary tool for server and database administration. SQL Server Enterprise Manager is one of the programs in the Microsoft SQL Server program group, and is available from the Start menu. When you launch SQL Server Enterprise Manager from the Start menu, a preconfigured Microsoft Management Console (MMC) console appears in user mode containing a snap-in for the administration of SQL Server 2000. If you open this MMC console in author mode, you can add additional snap-ins to this console (such as a snap-in for Event Viewer) to facilitate performing multiple common administration tasks using a single MMC console. To open the SQL Server Enterprise Manager MMC console in author mode, right-click the SQL Server Enterprise Manager.msc file in the \Program Files\Microsoft SQL Server\80\Tools\Binn folder and click Author. For more information regarding MMC consoles, use Windows 2000 Books Online.
After you open SQL Server Enterprise Manager, you need to become familiar with the interface. See Figure 3.13.
The SQL Server Enterprise Manager interface.
The left pane of an MMC console is the console root container that contains separate console trees for each snap-in. A console tree is a hierarchical structure containing folders, extension snap-ins, monitor controls, tasks, wizards, and documentation. The SQL Server Enterprise Manager console contains the Microsoft SQL Servers console tree in the left pane. The Microsoft SQL Servers console tree contains the SQL Server Group container. This is the default group (or container) for all SQL Servers registered in this console for administration. When you install an instance of SQL Server 2000, the Setup program automatically registers that instance for administration on the local computer and places it in this default group. Each SQL Server 2000 instance has its own container. You can create separate groups containing selected servers for administrative convenience when administering many servers.
When you click an object in a console tree, the right pane of the MMC console (called the details pane) displays additional subcontainers or the contents of that object, depending upon the object. You can specify and customize the view of the details pane, including modifying the items that appear on the toolbar when the object is selected. Some objects in the console tree have preconfigured taskpad views for that object. These views include preconfigured report information and shortcuts to relevant wizards to make it easier for you to perform certain tasks. Taskpad views are HTML pages. By default, the taskpad views are not enabled.
An MMC console contains two types of toolbars. The first is the MMC toolbar. This is primarily used when you are in author mode. The second type of toolbar is specific to each console tree. If your focus is within the Microsoft SQL Servers console tree, the console root toolbar (directly beneath the MMC toolbar) will contain menu items and tools specific to SQL Server Enterprise Manager. If your focus is within another console tree within the MMC console (such as Event Viewer), the console root toolbar will be specific to that console tree. The console root toolbar for SQL Server Enterprise Manager contains three context-sensitive menus: Action, View, and Tools. These menu items allow you to perform a variety of tasks, including launching other SQL Server 2000 tools, such as SQL Query Analyzer and SQL Profiler. The items that are available from each menu vary depending upon your focus within the console tree. For example, most items on the Tools menu are unavailable until your focus is on a specific instance of SQL Server 2000, because these tools act upon a particular instance.
To establish a connection to an instance of SQL Server 2000 that is registered in SQL Server Enterprise Manager, simply expand the container for that instance. You can also right-click an instance of SQL Server 2000 to start, stop, pause, or disconnect from that instance. By default, SQL Server Enterprise Manager will connect using Windows authentication and will start SQL Server 2000 if it is not already started. You can change these registration configuration defaults by right-clicking the container for an instance of SQL Server 2000 and editing the properties of the registration. For example, you can choose to connect to SQL Server 2000 using SQL Server authentication using the sa account. You can also choose to hide all system databases and objects as part of the registration configuration.
Finally, be aware that SQL Server Enterprise Manager connects to an instance of SQL Server 2000 as a client. By default, the SQL Server Enterprise Manager client polls the SQL Server service every 10 seconds to verify its state. In addition, changes to objects displayed in SQL Server Enterprise Manager after you establish a connection to a SQL Server 2000 instance generally will not be reflected immediately. This can include changes made by SQL Server Enterprise Manager and by SQL Query Analyzer. To update a particular object and its contents, right-click the object and click Refresh. To refresh all objects in a SQL Server instance, disconnect from the instance and then reconnect.
In this practice you work with the SQL Server Enterprise Manager MMC console.
To work with the SQL Server Enterprise Manager MMC console
SQL Server Enterprise Manager appears displaying the Microsoft SQL Servers console tree in the console root. No other console trees appear.
Notice the only option available is Exit. No other menu options are available because SQL Server Enterprise Manager was opened in user mode.
The Search Results dialog box appears.
Notice that a plethora of preconfigured MMC consoles appear. Most are separate MMC consoles each for a specific Windows 2000 administrative task.
The SQL Server Enterprise Manager MMC console appears in author mode.
The Add/Remove Snap-in dialog box appears.
The Add Standalone Snap-in dialog box appears displaying all of the available standalone snap-ins that may be added.
The Select Computer dialog box appears.
The SQL Server Enterprise Manager MMC console now displays two separate console trees, Microsoft SQL Servers and Event Viewer (Local).
A Microsoft Management Console message box appears.
SQL Server Enterprise Manager appears displaying the Microsoft SQL Servers and the Event Viewer (Local) console trees in the console root.
The logs available within Event Viewer appear in the details pane. Notice that the menu items and tools on the console root toolbar change when you change console trees. The console root title bar indicates your focus within the console root.
Notice that the menu items and tools on the console root toolbar specific to SQL Server Enterprise Manager appear in place of the items on the Event Viewer toolbar.
The default instance (SelfPacedCPU) and your named instance (MyNamedInstance) appear in the Microsoft SQL Servers console tree, each in their own container and displaying the state of the SQL Server service for that instance. Each instance also displays the authentication method used to connect to the instance, enclosed in parenthesis (namely Windows authentication).
Notice that your focus in the console tree remains the Microsoft SQL Servers container and that the contents of the details pane do not change when you expand an item in the console tree. The details pane changes only when your focus changes.
Notice that the details pane displays the contents of this container. We will cover each of these objects in detail throughout the course of this book. Also notice that the icon indicating the state of the SQL Server service changed from a green triangle in a white circle to a white triangle in a green circle. This indicates that you have established a connection to this instance.
Notice the available options, including Taskpad. The container object that is your current focus contains a preconfigured view.
Notice that the details pane changes to display the taskpad view for this container object. The taskpad contains two tabs, General and Wizards. The General tab displays information regarding your computer and your server configuration. The Wizards tab displays the wizards that are available for your use. These wizards are also available from the Tools menu. We will use these wizards in exercises in later chapters of this book.
Notice the configured registration properties.
SQL Server 2000 contains a number of client tools and utilities to administer SQL Server 2000. Osql is the primary command-prompt utility used for the submission of batches of Transact-SQL statements to SQL Server 2000. SQL Query Analyzer is the primary graphical tool used for interactive testing of Transact-SQL statements and batches. It is also used to create and administer objects within SQL Server 2000. SQL Server Enterprise Manager is the primary graphical tool used to graphically administer objects in SQL Server 2000. SQL Server Enterprise Manager is a preconfigured MMC console that you might want to customize. Becoming familiar with each of these tools is an essential task for a database administrator because he or she will use these tools daily.