Mail to a Friend Receive updates via our RSS feed

SQL Server 2005 Express Tools

SQL Server 2005 Express is much improved over its MSDE predecessor in that visual tools are provided along with the database engine. These tools enable you to manage services, networking protocols, and configurations. The tools also provide the ability to manage databases and their objects, such as tables, views, stored procedures, functions, and more.

This chapter covers the basics of using the tools available in SQL Server 2005 Express. For more information about the networking aspects and considerations for these tools, see Chapters 6 and 9. The examples shown in this chapter use the default instance name of SQLEXPRESS. You can change the name of this instance when you install SQL Server 2005 Express, but it is a good idea to leave it as the default to make it obvious that this is not a different edition of SQL Server.

SQL Server Configuration Manager
The SQL Server Configuration Manager is a graphical tool that is automatically installed with SQL Server 2005 Express to enable you to manage many aspects of your SQL Server 2005 installation, such as services, network configuration, and more. Figure 3.1 shows the SQL Server Configuration Manager.

Figure 3.1: SQL Server Configuration Manager for SQL Server 2005 Express.

The SQL Server Configuration Manager displays three main categories of information:

• SQL Server 2005 Services - Displays a listing of services for your server, such as Analysis Server, SQL Server, and Report Server.
• SQL Server 2005 Network Configuration - Displays a listing of networking protocols.
• SQL Native Client Configuration - Displays a listing of configurations for SQL Native Client connections, such as protocols and aliases.

SQL Server 2005 Services
As with all Windows programs that run in the background, SQL Server operates as a set of services. Because SQL Server 2005 Express is a scaled-down edition of SQL Server 2005, not all services are available. The SQL Server 2005 Services node in the SQL Server Configuration Manager tree is used to list all possible services that are used with SQL Server 2005.

To manage a given service, follow these simple steps:

1. Navigate to the desired service under the SQL Server 2005 Services node in the SQL Server Configuration Manager tree.
2. Click the desired service. In the right-hand pane, you'll see a list of items for the selected service.
3. Right-click the desired item presented in the right-hand pane and select Properties from the drop-down menu. As an example, Figure 3.2 shows the Advanced properties available for the SQLEXPRESS instance of the SQL Server service.
   
   
   Figure 3.2: SQL Server Service Advanced Properties.
   
4. Change the desired properties and click the OK button to save your changes and close the screen. You can only change these properties:
   • Dump Directory - Folder for SQL Server logs.
   • Error Reporting - Determines whether errors are transmitted back to Microsoft.
   • SQM Reporting - Determines whether reports for service quality are transmitted back to Microsoft. These reports tell Microsoft about the features that are most often used.
   • Startup Parameters - Allows you to specify parameters for the SQL Server service.

SQL Server 2005 Network Configuration
The SQL Server 2005 Network Configuration node in the SQL Server Configuration Manager tree lists the protocols that are to be used to connect to your SQL Server 2005 instance. In SQL Server 2005 Express, there is only one node under SQL Server 2005 Network Configuration, which is Protocols for SQLEXPRESS. This node is shown in Figure 3.3.

Figure 3.3: Network Configuration Protocols for the SQLEXPRESS Instance.

The default protocols for SQL Server 2005 (including the Express edition) are:

• Shared Memory - Secure way for a client to access a SQL Server on the same computer. Shared Memory is enabled by default and cannot be used when accessing SQL Server from across the network.
• Named Pipes - Allows a client to connect to SQL Server by using any of a number of different protocols. The actual protocol used is dictated by the client application by indicating the path, or pipeipe, needed to access the server. Because of potential security risks, only local Named Pipes is enabled by default. If you want to use the Named Pipes protocol from a remote machine, you'll have to enable this in the SQL Server Surface Area Configuration manager, which is shown in Chapter 6.
• TCP/IP - Standard Ethernet protocol that enables a client application to access a specific SQL Server instance using its IP address. Because of potential security risks, TCP/IP is disabled by default.
• VIA - Stands for Virtual Interface Architecture and is a network protocol that is used in high-speed networks. VIA is not available in SQL Server 2005 Express.

To enable or disable any of the protocols (except VIA), simply right-click the desired protocol name and select Enable or Disable as desired. If a protocol is enabled, a client application will be able to connect to SQL Server 2005 Express by using that protocol.

SQL Native Client Configuration
SQL Native Client is a new and efficient data access technology that takes advantage of new features in SQL Server 2005. On the other hand, if you are trying to use existing applications that leverage MDAC, you will likely want to upgrade those applications to use the SQL Native Client to gain the advantages of easy distribution. Distribution of SQL Server 2005 Express applications is covered in Chapter 8, while MDAC and SQL Native Client are covered further in Chapter 9.

The SQL Native Client Configuration node in the SQL Server Configuration Manager tree is used to manage aspects of the SQL Native Client and is broken into two separate nodes in the tree:

• Client Protocols
• Aliases

Client Protocols
The Client Protocols node is used to configure the protocols that are to be used to connect to your SQL Server 2005 instance via the SQL Native Client provider. Figure 3.4 shows the Client Protocols node under the SQL Native Client Configuration node.

Figure 3.4: SQL Native Client Protocols for the SQLEXPRESS Instance.

These protocols are the same ones listed under the SQL Server 2005 Network Configuration node. Not only do you enable a protocol to be used with the SQL Native Client, but also the order in which the protocol is to take precedence. To manage the protocols used with the SQL Native Client, follow these easy steps:

1. Right-click the Client Protocols node.
2. Select Properties from the drop-down menu. This will bring up the screen shown in Figure 3.5.
   
   
   Figure 3.5: Client Protocols Properties.
   
3. Enable a protocol by selecting the desired item in the Disabled Protocols box and clicking the > button. This will move the protocol from the Disabled Protocols box to the Enabled Protocols box. The only exception is if you want to enable the Shared Memory protocol. If you do, simply ensure the Enable Shared Memory Protocol check box is selected.
4. Disable a protocol by selecting the desired item in the Enabled Protocols box and clicking the < button. This will move the protocol from the Enabled Protocols box to the Disabled Protocols box.
5. To configure the precedence of a protocol, select the desired item in the Enabled Protocols box and click the t or 4 buttons as desired. The order in which the protocols are shown is the order of precedence.
6. Click the OK button to save your changes and close the screen.

Aliases
An alias is user-friendly way to specify and configure all the properties required by a protocol to make a connection to SQL Server when using the SQL Native Client. For example, if you have a production server named SERV01 and you wish to connect via TCP/IP, you might configure an alias that is named Prod and specify the server name or IP address, along with the port to use for connection to the server. Then, your application needs only to reference the name Prod in its connection. The Aliases node is used to confi gure these aliases for use with your SQL Native Client connections.

To create a new SQL Native Client alias, follow these easy steps:

1. Right-click the Aliases node.
2. Select New Alias from the drop-down menu. This will bring up the screen shown in Figure 3.6.
   
   
   Figure 3.6: Creating a New SQL Native Client Alias.
   
3. Enter the desired Alias Name . This name will be used to make the connection.
4. Enter the Port No for the TCP/IP address on which the desired instance will listen. If you selected Named Pipes as the protocol, you must enter the pipe name.
5. Select the desired Protocol from the drop-down list. TCP/IP is selected by default.
6. Enter the desired Server, which can be the name of your server or its IP address.
7. Click the OK button to save your changes and close the screen.

Express Manager
Express Manager, sometimes known as XM, is a tool that lets you graphically manage the objects in your SQL Server 2005 Express instance. These objects include databases, tables, views, stored procedures, and more. XM also lets you issue queries against a database by using the built-in Query Editor.

Figure 3.7 shows how the Express Manager looks, although your configuration will be completely dependent on the objects that exist on your server.

Figure 3.7: SQL Server 2005 Express Manager (Preview).

There are a few things to notice in Figure 3.7 besides the hierarchical object structure of each database. The first is the connection information at the top of the screen. Figure 3.7 shows that this connection to the SQLEXPRESS instance is made by using a trusted (Windows) connection. Figure 3.7 also shows the instance name at the top of the tree from which all objects are displayed. It also shows the version of the instance. For each of the databases, it is also shown if the database is not running. If the database is not running, you'll see the word ( Shutdown) after the name of the database. A database that is shut down means that no users are connected to it and it is effectively detached from the SQL Server instance. Likewise, the instance will automatically start when a user connects to it. Finally, the query window on the right-hand part of the screen shows the database that is currently in use within the SQL Server instance. This is very similar to the way the SQL Server Query Analyzer works in SQL Server 2000. The Query Editor lets you type SQL statements, execute them, and return results.

SQLCMD
SQLCMD is a command-line tool for issuing commands against a SQL Server 2005 instance without the need for any graphical tools. SQLCMD can connect (using the -A parameter) to a SQL Server 2005 instance using a dedicated administrative connection so that any commands that you issue will not be hampered by slow performance on your server. SQLCMD issues commands in batches, so it prompts you for each line in the batch with a line number. When you are finished entering each line in your batch, enter the GO keyword and your batch will be executed.

SQLCMD can be a little complex to use because there are so many connection options, but here are the basics of how you use SQLCMD:

1. Open a command prompt by clicking the StartO Programs1^ Accessories O Command Prompt menu item. Alternatively, you could simply click the StartO Run menu item and type cmd (which stands for Command Prompt), and press the Enter key.
2. Run the SQLCMD program. When you run the program, you establish a connection between your console and a SQL Server 2005 instance (including the Express edition). Here are the basics of creating a connection:
   
   

In Example 1 and Example 2, a connection is established with an instance named SQLExpress on the computer named Prod01. Example 1, because it uses SQL Server security, connects with a user name of sqladmin and a password of 12345. Example 2 connects using Windows security, so no user name or password is required. The current network login is used.

You'll see the SQLCMD program running, which is shown in Figure 3.8.

Figure 3.8: SQLCMD Program.

After you login to SQL Server 2005 using SQLCMD, you will see a command prompt beginning with the current line number in the batch. This is very similar to what you see in the Query Editor of the Express Manager. You can begin entering valid commands, such as SQL or operating system commands. You can select data from the database, kill all connections, start processes, run stored procedures, and perform virtually any other task that can be run by issuing a command. To find out what commands are available through SQLCMD, you can refer to SQL Server Books Online (BOL) or issue the :help command (followed by pressing the Enter key). This displays a list of commands, as shown in Figure 3.9.

Figure 3.9: SQLCMD :help Command.

Summary
SQL Server 2005 Express includes useful tools for managing services, networking protocols, configurations, and more.

The SQL Server Configuration Manager tool contains a SQL Server 2005 Services node (which lists services for your server), a SQL Server 2005 Network Configuration node (which lists networking configurations), and a SQL Native Client Configuration node (which lists configurations for SQL Native Client connections).

Another tool is Express Manager, or XM, which lets you graphically manage the objects in a SQL Server Express 2005 instance, such as databases, tables, views, stored procedures, and more.

The SQLCMD tool allows you to use the command line to issue commands against a SQL Server 2005 instance. It also allows a dedicated administrative connection, so that your commands are not affected by slow server performance.

Copyright © 2004 by Mann Publishing Group.

InfoWorld's Java IDE Comparison Strategy Guide If you're looking for a Java IDE, you want one based on Eclipse. But which offering do you want? Download the Infoworld Java IDE Comparison Strategy Guide. In this three-part guide we'll go deep into the details, comparing Technology of the Year winner JBuilder 2007, IBM's IRAD, MyEclipse and the free open-source Eclipse platform. We close this Java IDE Strategy Guide with a look at an advanced concept in Java development: Application Factories. This innovative development metaphor and associated collection of tools allows developers to focus more on the nature and purpose of the application, and less on the underlying platform, framework, and technologies being used. Request Your Free Strategy Guide!