|
|
Chapter 3 - SQL Server 2005 Express Tools
Wednesday, July 6, 2005
|
|
|
To order this book or to find additional details, go to www.rationalpress.com.
Microsoft® SQL Server 2005™ Express is a free edition of Microsoft's flagship database product. The Express edition, which replaces MSDE, contains many of the features of other editions of SQL Server 2005, including the integration of Microsoft's .NET technology into the SQL Server platform.
This book gives you an early look into the beta preview of SQL Server 2005 Express. It takes a rational, no-nonsense approach in a compact guide - only 224 pages. The book is written to give you the basics…fast! With this book, you'll learn the basics of using SQL Server 2005 Express and hosting SQL Server Common Language Runtime (CLR) objects in the database. With its friendly tutorial approach and numerous examples, this book will help you quickly learn how to create, configure, maintain, and deploy SQL Server 2005 Express databases. It also covers advanced topics, such as security, distributed support, and XML support.
Technical accuracy is assured by Roger Wolter, Group Program Manager, SQL Server 2005 Express, Microsoft Corporation.
Copyright © 2005 by Mann Publishing Group.
www.mannpublishing.com
By Anthony Mann
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.
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:
- Navigate to the desired service under the SQL Server 2005 Services node
in the SQL Server Configuration Manager tree.
- Click the desired service. In the right-hand pane, you'll see a list of items for
the selected service.
- 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.
-
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.
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 pipe, 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
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.
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:
- Right-click the Client Protocols node.
- Select Properties from the drop-down menu. This will bring up the screen
shown in Figure 3.5.
- 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 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.
- 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.
- To configure the precedence of a protocol, select the desired item in the
Enabled Protocols box and click the
 or  buttons as desired. The order in
which the protocols are shown is the order of precedence.
- 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
configure these aliases for use with your SQL Native Client connections.
To create a new SQL Native Client alias, follow these easy steps:
- Right-click the Aliases node.
- Select New Alias from the drop-down menu. This will bring up the screen
shown in Figure 3.6.
- Enter the desired Alias Name. This name will be used to make the
connection.
- 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.
- Select the desired Protocol from the drop-down list. TCP/IP is selected by
default.
- Enter the desired Server, which can be the name of your server or its IP
address.
- 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 confi guration will be
completely dependent on the objects that exist on your server.
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 fi nished 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:
- Open a command prompt by clicking the Start
 ProgramsAccessories
Command Prompt menu item. Alternatively, you could simply click the
StartRun menu item and type cmd (which stands for Command Prompt),
and press the Enter key.
- 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.
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 fi nd 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.
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.
|
|
|
68 people have rated this page.
Average rating: 3 out of 9
|
PUBLISHED BY
Rational Press - An imprint of the Mann Publishing Group
710 Main Street, 6th Floor
PO Box 580
Rollinsford, NH 03869, USA
www.rationalpress.com
www.mannpublishing.com
+1 (603) 601-0325
Copyright © 2005 by Mann Publishing Group.
All rights reserved. No part of the contents of this book may be reproduced in any form or by any means without the written permission of the publisher. For questions about rights and permissions, send e-mail to permissions@mannpublishing.com.
ISBN: 1-932577-16-5
Library of Congress Control Number (LCCN): 2005928284
Printed and bound in the United States of America.
Trademarks
Mann Publishing, Mann Publishing Group, Agility Press, Rational Press, Inc.Press, NetImpress,
Farmhouse Press, BookMann Press, The Rational Guide To, Rational Guides, ExecuGuide,
AdminExpert, From the Source, the Mann Publishing Group logo, the Agility Press logo, the
Rational Press logo, the Inc.Press logo, Timely Business Books, Rational Guides for a Fast-Paced
World, and Custom Corporate Publications are all trademarks or registered trademarks of Mann
Publishing Incorporated.
All brand names, product names, and technologies presented in this book are trademarks or
registered trademarks of their respective holders.
Disclaimer of Warranty
While the publisher and author(s) have taken care to ensure accuracy of the contents of this book,
they make no representation or warranties with respect to the accuracy or completeness of the
contents of this book and specifically disclaim any implied warranties or merchantability or fitness
for a specific purpose. The advice, strategies, or steps contained herein may not be suitable for your
situation. You should consult with a professional where appropriate before utilizing the advice,
strategies, or steps contained herein. Neither the publisher nor author(s) shall be liable for any
loss of profit or any other commercial damages, including but not limited to special, incidental,
consequential, or other damages.
All Mann Publishing Group books may be purchased at bulk discounts. Visit us on the web at www.mannpublishing.com or call (877) 877-MANN.
|
|