SQL
Server 2008 ships with a set of nine tools for managing SQL Server
instances and interacting with data: OSQL, SQLCMD, Tablediff, Bulk Copy
Program (BCP), SQLDiag, Resource Governor, SQL Server Configuration
Manager, SSMS, and Database Mail.
OSQL
is a command line utility which was added to SQL Server 2000 as a
replacement for ISQL. OSQL allows you to connect to and execute queries
against a SQL Server instance without requiring the overhead of a
graphical interface.
Important
 | OSQL
has been deprecated as of SQL Server 2005. While OSQL is still
available in SQL Server 2008, you should rewrite any OSQL routines to
utilize SQLCMD. |
SQL
Server 2005 introduced SQLCMD as the command line query interface that
replaced OSQL. While OSQL allowed you to submit interactive queries
from a command line along with very limited automation capabilities,
SQLCMD provides a rich automation interface complete with variable
substitution and dynamic code creation/execution.
You
can use Tablediff.exe to compare the data between two tables. Tablediff
can be run to alert you if the data or structure of two tables is
different. Additionally, Tablediff can generate a script file
containing the statements necessary to bring the destination table into
synchronization with the source table. Tablediff is primarily used
within replication architectures.
The
BCP utility is the oldest utility within the SQL Server product, dating
all the way back to the very first version of SQL Server. BCP has been
enhanced with each successive version to handle new data types and
named instances, but the speed and feature set has not changed. BCP is
used to export data from a table to a file as well as import data from
a file into a table. If your import and export needs are reasonably
simple and straightforward, BCP should be the only utility that you
would need. For more advanced import and export capabilities, you
should utilize SQL Server Integration Services (SSIS).
SQLDiag
is a utility that collects diagnostic information about a SQL Server
instance. SQLDiag is designed to capture Windows performance counters,
event logs, SQL Server Profiler traces, SQL Server blocking, and SQL
Server configuration information. Primarily used as a data collection
engine for Microsoft Customer Service and Support (CSS) to troubleshoot
SQL Server issues, the data collected can also be used by a DBA to
analyze SQL Server performance and stability issues.
Resource
Governor is a new feature in SQL Server 2005 that is found within SSMS.
The purpose of Resource Governor is to allow a DBA to configure rules
around resource allocation such as processor or memory that is then
applied to specific queries, users, or groups of users. The goal of
Resource Governor is to allow high-priority workloads to take priority
over other workloads in order to provide the best response based on
user expectations.
SQL Server Configuration Manager
Shown in Figure 1,
SQL Server Configuration Manager is responsible for managing SQL Server
services and protocols. The primary tasks that you will perform with
SQL Server Configuration Manager are:
Start/Stop/Pause/Restart a service
Change service accounts and passwords
Manage the startup mode of a service
Configure service startup parameters
Once
you have completed the initial installation and configuration of your
SQL Server services, the primary action that you will perform within
SQL Server Configuration Manager is to periodically change service
account passwords. When changing service account passwords, you no
longer have to restart the SQL Server instance for the new credential
settings to take effect.
Important
| Windows
Service Control Applet also has entries for SQL Server services and
allows you to change service accounts and passwords. You should never
change service accounts or service account passwords using the Windows
Service Control Applet. SQL Server Configuration Manager needs to be
used, because SQL Server Configuration Manager includes the code to
regenerate the service master key that is critical to the operation of
SQL Server services. |
While
you can Start, Stop, Pause, and Restart SQL Server services, SQL Server
has extensive management features which should ensure that you rarely,
if ever, need to shut down or restart a SQL Server service.
In this procedure, you will review the options and settings available for SQL Server services.
Review Service Options
1. | Launch
SQL Server Configuration Manager by selecting Start | All Programs |
Microsoft SQL Server 2008 | Configuration Tools | SQL Server
Configuration Manager.
|
2. | In the left-hand pane, highlight SQL Server Services.
|
3. | Double-click the SQL Server service in the right-hand pane to display the Properties dialog box shown here.
|
4. | Review the options on each of the tabs.
|
5. | Click Cancel to close the Properties dialog box without making any changes.
|
SQL
Server Configuration Manager also allows you to configure the
communications protocols available to client connections. In addition
to configuring protocol- specific arguments, you can also control
whether communications are required to be encrypted or whether an
instance will respond to an enumeration request, as shown in Figure 2.
Tip
 | Applications
can broadcast a special command, called an enumeration request, across
a network to locate any SQL Servers that are running on the network.
While being able to enumerate SQL Servers is valuable in development
and testing environments where instances can appear, disappear, and be
rebuilt on a relatively frequent basis, enumeration is not desirable in
a production environment. By disabling enumeration responses by setting
the Hide Instance to Yes, you prevent someone from using discovery
techniques to locate SQL Servers for a possible attack. |
SQL Server Management Studio
SQL
Server Management Studio is the core tool that you will be spending a
large part of your time using. SSMS provides all of the management
capabilities for SQL Server services along with the ability to create
and execute Transact-SQL (TSQL), Multidimensional Expression (MDX)
query language, Data Mining Extensions (DMX), and XML for Analysis
(XMLA) code. This section will provide a brief overview of SSMS, as
shown in Figure 3, to get you started. Each subsequent chapter within this book will extend your knowledge of SSMS capabilities.
Launch SSMS and Connecting to an Instance
1. | Launch SSMS by selecting Start | All Programs | Microsoft SQL Server 2008 | SQL Server Management Studio.
|
2. | When the Connect To Server dialog box is displayed, accept the default options and click Connect.
|
Note
 | Because
you have only installed a default instance at this point, this dialog
should default to Database Engine for the server type,
for the server name, and Windows Authentication for
the authentication option. Now that you have connected to an instance
within SSMS, for all remaining exercises in this book, we will assume
that you can perform these steps and will not repeat them. |
SSMS has a variety of windows that you can open and position within the interface in order to access various feature sets.
The
Registered Servers window provides a place to store connection
information for all of the SQL Server services within your environment.
Once stored, you can right-click any server and launch a connection to
the server in either the Object Explorer or a query window.
The Template Explorer, shown in the right-hand pane of Figure 3-4,
enables access to hundreds of predefined templates to create, alter, or
drop objects as well as query various objects using TSQL, MDX, XMLA, or
DMX. You can use the templates that ship with SQL Server, modify the
templates to include your organization-specific coding standards, and
add additional templates or template groups.
The
Community menu on the toolbar allows you to launch a browser window
into the center pane to access the MSDN forums and Microsoft Connect in
the same way as previously described for Books Online.
The Tools | Options menu on the toolbar will display the Options dialog box, as shown in Figure 4 on page 40, so that you can set up the SSMS environment specificly the way you want to work.
Configure the SSMS Environment
1. | Select Tools | Options from the toolbar.
|
2. | Expand
the Environment tree and select the General node. Use the At Startup
drop-down list to configure the startup look and feel of SQL Server
Management Studio.
|
3. | Expand Text Editor | All Languages | Tabs.
|
4. | Set the Tab Size to 4.
|
5. | Set the Indent Size to 4.
|
6. | Select the Insert spaces option.
|
7. | Explore the rest of the options that are available for configuration.
|
8. | Click OK to save your settings.
|
Tip
| When
you set SSMS to start up with an empty environment, you will not see a
Connect To Server dialog and SSMS will immediately start. You will then
need to explicitly connect to an instance for the Object Explorer or
query window through either the Registered Server pane, File | Connect
Object Explorer, or the New Query button. By setting the tab size and
insert spaces options, SSMS will automatically replace any tabs with
spaces in a query window, allowing you to more easily format and align
code even when using a proportional font. |
As you can see from Figure 5,
the Object Explorer provides access to practically any action that you
wish to perform against any SQL Server object. You will be using the
functionality within the Object Explorer throughout virtually every
chapter in this book.
Two
additional capabilities of SSMS are object summaries and built-in
reporting capabilities. The Object Explorer Details tab will display
summary information according to the object that is currently selected
within the Object Explorer. SSMS Reports, shown in Figure 6,
allow you to display either Standard Reports that ship with SQL Server
or to access your own custom reports that have been designed using the
Reporting Services Report Designer that you will learn about in the
article, “Reporting Services,” which can be found on the Microsoft Press Online Windows Server and Client Web site at www.microsoft.com/learning/books/online/serverclient.
Database Mail
Database
Mail enables a SQL Server to send outbound mail messages. While
messages can contain the results of queries, Database Mail is primarily
used to send alert messages to administrators to notify them of
performance conditions or changes that have been made to objects. In
the procedure that follows, you will learn how to configure Database
Mail.
Configure Database Mail
1. | Click the New Query button to open a new query window and execute the following code to enable the Database Mail feature:
EXEC sp_configure 'Database Mail XPs',1
GO
RECONFIGURE WITH OVERRIDE
GO
|
2. | Within the Object Explorer, open the Management Node, right-click on Database Mail, and select Configure Database Mail.
|
3. | Click Next on the Welcome screen.
|
4. | Select the Set Up Database Mail by Performing the Following Tasks option and click Next.
|
5. | Specify a name for your profile and click Add to specify settings for a mail account.
|
6. | Fill
in the account name, e-mail address, display name, reply e-mail, and
server name fields on the New Database Mail Account page.
|
7. | Select
the appropriate SMTP Authentication mode for your organization and, if
using Basic Authentication, specify the user name and password. Your
settings should look similar to the following:
Note | Your
screen should look similar to the settings in the graphic. I am using
my Internet e-mail account and have purposely left the Server Name,
User Name, and Password out of the graphic. You will need to fill in
the Server Name field if you are using an internal mail server. |
|
8. | Click OK and then click Next.
|
9. | Check
the box in the Public column next to the profile you just created and
set this profile to Yes in the Default Profile column and click Next.
|
10. | Review the settings on the Configure System Parameters page and click Next.
|
11. | Click OK, then click Next, and then click Finish.
|
12. | The final page should show success for all four configuration steps; click Close.
|
13. | Within
Object Explorer, right-click SQL Server Agent item and select Start
from the shortcut menu in order to start the SQL Server Agent service,
if it is not already running.
Note | Database
Mail utilizes the services of SQL Server Agent to send messages as a
background process. If SQL Server Agent is not running, messages will
accumulate in a queue within the msdb database. |
|
14. | Right-click Database Mail and select Send Test E-mail from the shortcut menu.
|
15. | Select the Database Mail Profile you just created, enter an e-mail address in the To: line, and click Send Test E-Mail.
|
16. | Go to your e-mail client and verify that you have received the test mail message. |
