This section focuses on the tools that you can use to configure and manage services, administer client and server network configurations, configure email support in SQL Server 2005, and run SQL Server scripts from the DOS command prompt.
SQL Server Configuration Manager
SQL Server Configuration Manager is yet another example of Microsoft's initiative to combine multiple tools into a single integrated tool. SQL Server Configuration Manager provides the functionality of three tools provided with SQL Server 2000: Service Manager, Server Network Utility, and Client Network Utility. SQL Server Configuration Manager is an MMC snap-in (%SystemRoot%\system32\SQLServerManager.msc) that provides the means to manage SQL Server services and connectivity settings. SQL Server Configuration Manager internally uses WMI to access and update SQL Server services and connectivity details.
Figure 5.10 shows the SQL Server Configuration Manager. The three top-level items on the tree on the left are SQL Server 2005 Services, SQL Server 2005 Network Configuration, and SQL Native Client Configuration. When you select SQL Server 2005 Services from the tree, the grid on the right shows various SQL Server 2005 services including Relational Engine, Analysis Services, Integration Services, Notification Services, Reporting Services, Agent, Full-Text, and the SQL Server Browser service. You can right-click a service and select Properties to view and change service settings. Figure 5.10 shows SQL Server Configuration Manager, listing various SQL Server 2005 services.
Figure 5.10. SQL Server Configuration Manager is an MMC snap-in application that helps you manage services and connectivity settings.
You can use SQL Server Configuration Manager to start, pause, resume, or stop services and to view and change service properties, such as service start mode (automatic, manual, or disabled) and the account name under which the service runs. If you would like to change the account under which the services should run, it is recommended that you use SQL Server Configuration Manager to do that. This ensures that the new account is assigned the required permissions on the disk folders and registry hives.
The SQL Server 2005 Network Configuration node in the SQL Server Configuration Manager tree can be used to configure network libraries that SQL Server listens on. If you expand this node in the tree, you should see items such as Shared Memory, Named Pipes, TCP/IP, and VIA for Virtual Interface Architecture, which is a network library designed to support highly reliable, fast, efficient data transfer between servers by using hardware from Giganet. You can right-click any network library and select Properties to view and configure network library settings or to enable or disable the protocol.
The SQL Native Client Configuration node can be used to manage the client net-libraries and define server alias names. Click Client Protocols and you should see all the client network libraries on the right hand side. You can right-click or double-click any entry to set the properties for the selected client network library. To create a new client alias, you expand the SQL Native Client Configuration node, right-click Aliases, and select New Alias.
The SQL Server Configuration Manager can also connect to a different server and manage services and connectivity settings for that server. To connect to a different server, you select the Start menu, right-click My Computer, and click Manage. In Computer Management, you right-click Computer Management (Local) and then select Connect to Another Computer. You connect to the remote server and under Services and Applications, you should see SQL Server Configuration Manager, which you can use to control SQL Server services and network settings on that server.
SQL Server Configuration Manager ships with all SQL Server 2005 editions, including SQL Server 2005 Express Edition.
The SQLCMD Utility
SQLCMD is a new DOS command-line utility introduced in SQL Server 2005 to interactively execute T-SQL statements and also to execute scripts from the command line.
SQL Server 2005 no longer ships the DB-librarybased isql.exe, and it discourages the use of the ODBC-based osql.exe. SQLCMD.exe uses the SQL native client, which is OLE DB based, to communicate with SQL Server. SQLCMD doesn't just replace osql.exe, but it also contains various enhancements that make it more robust and usable.
SQLCMD accepts several command-line switches, similarly to osql.exe. Also as with osql.exe, the SQLCMD command-line parameters are case sensitive. You can pass the -? or /? command-line switch to get a list of all the parameters that SQLCMD accepts. If you pass the -S parameter along with the server name, such as MyServer or MyServer\InstanceName, SQLCMD connects to the specified SQL Server instance by using Windows authentication.
Let's look at some basic SQLCMD examples.
The following statement connects to an instance named YUKON on the server DDGXP (-S) by using a trusted connection (-E); update the statement to connect to your SQL Server 2005 instance. It defaults to a database named AdventureWorks, and the -p switch tells SQLCMD to print performance statistics after every query is executed:
SQLCMD -S DDGXP\YUKON -E -d AdventureWorks -p
After you run this statement from a DOS command prompt, you should see the 1> prompt, where you can type T-SQL commands, press Enter, type GO, and press Enter to execute the query. You can type EXIT to end the SQLCMD session.
You use the -Q switch to execute the specified query and exit:
SQLCMD -S DDGXP\YUKON -E -d AdventureWorks -Q "SELECT Name FROM Sales.Store"
If you pass the -q switch, SQLCMD executes the query and stays in interactive query mode, and you have to type EXIT to end the SQLCMD session.
You can use the -Z switch to change the SQL Server password:
SQLCMD -S DDGXP\YUKON -U sa -P OldPassword2005 -Z NewPWD1234
If you pass the -z switch, SQLCMD changes the password and stays in interactive query mode, and you have to type EXIT to end the SQLCMD session.
One of the nice enhancements introduced in SQLCMD is the ability to provide multiple input script files. The following statement asks SQLCMD to connect to a SQL Server 2005 instance named YUKON, run the script files c:\1.sql and c:\2.sql, save the output results in a file called results.txt, separate the columns using the tilde character (~), and save the results.txt file as a Unicode file (-u switch) instead of using the default ANSI file format:
SQLCMD -S DDGXP\YUKON -i c:\1.sql,c:\2.sql -o results.txt -s~ -u
The -L switch is for getting a list of local and other remote SQL Server machines that are broadcasting on the network:
If you pass -Lc instead of -L, SQLCMD generates "clean" output, which does not have the Servers: heading line or any formatting spaces in front of the listed server names.
Table 5.3 lists all the SQLCMD switches.
SQLCMD and Environment Variables
One way to instruct SQLCMD about the server and database to connect to, the authentication method to use, the network packet size, and other settings, is by passing the appropriate command-line switches, as described in the previous section. The other method is to set some environment variables. For instance, if you set an environment variable named SQLCMDDBNAME to a database name string, either by using a SET statement at the command prompt or in a batch file or by using the Environment Variables button on the Advanced tab of System Properties dialog (which you open by selecting Control Panel | System), SQLCMD connects to the database specified by the environment variable, provided that the -d switch is not passed to it. Similarly, if you set the SQLCMDSERVER environment variable and you don't pass the -S parameter to SQLCMD, SQLCMD connects to the server specified by the environment variable. In other words, when you run SQLCMD, in addition to command-line switches, SQLCMD also looks at certain environment variables. If an environment variable is set to some value, and if an overriding switch is not provided on the command line, SQLCMD honors the environment variable. Here is how it works:
C:\>SET SQLCMDSERVER=DDGXP\YUKON C:\>SET SQLCMDDBNAME=AdventureWorks C:\>SET SQLCMDPACKETSIZE=8192 C:\>SQLCMD -p1 1>SELECT TOP 1 Name FROM Sales.Store; 2>GO Name -------------------------------------------------- A Bike Store (1 rows affected) 8192:1:1:1.00:1000.00
These statements at the DOS command prompt set environment variables for server, database, and network packet size and then run SQLCMD with -p1 to have colon statistics printed after each line. Note that SQLCMD automatically connects to a SQL Server 2005 instance, makes AdventureWorks the current database, and sets the packet size to 8192.
You can use the :setvar SQLCMD command to change the SQLCMD environment variable value. SQLCMD commands such as :setvar are discussed in the next section.
The following DOS command-prompt statements connect to a SQL Server 2005 instance and set ~ as the column separator:
C:\>SQLCMD -S DDGXP\YUKON -E -dAdventureWorks -s~ 1> SELECT TOP(1) LEFT(Name, 15) Name, SalesPersonID FROM Sales.Store; 2> GO Name ~SalesPersonID ---------------~------------- A Bike Store ~ 280 1> :setvar SQLCMDCOLSEP "," 2> SELECT TOP(1) LEFT(Name, 15) Name, SalesPersonID FROM Sales.Store; 3> GO Name ,SalesPersonID ---------------,------------- A Bike Store , 280 1> :setvar SQLCMDCOLSEP ";" 2> SELECT TOP(1) LEFT(Name, 15) Name, SalesPersonID FROM Sales.Store; 3> GO Name ;SalesPersonID ---------------;------------- A Bike Store ; 280
After executing one SELECT statement, this script sets SQLCMDCOLSEP to a comma (,), and the results returned by a subsequent SELECT statement separate the columns by using a comma instead of a tilde. This is repeated again to change the column separator to a semicolon (;).
Table 5.4 lists the SQLCMD-specific environment variables and their command-line switch equivalents.
In addition to T-SQL commands, SQLCMD supports few other commands that you can run in interactive mode or inside T-SQL scripts executed using SQLCMD. The previous section shows an example of thisusing the setvar command to set environment and scripting variables.
Every SQLCMD command must be preceded with a colon character. For backward compatibility with osql.exe, a colon is not required before EXIT, QUIT, ED, !!, and RESET, but it is recommended that you use the colon in front of all SQLCMD commands except GO. The !! is used to execute operating system commands, much like xp_cmdshell in SQL Server 2000 extended stored procedures.
Try out the following:
C:\>SQLCMD -S DDGXP\YUKON -E -dAdventureWorks 1> :Help <Output omitted for brevity; shows help on all SQLCMD commands> 1> :ServerList <Output omitted for brevity; List of servers, output similar to -L switch> 1> !!DIR <Output omitted for brevity; Runs Operating System command> 1> :!!CLS <Runs Operating System command; clears the screen> 1> INSERT INTO tblTest VALUES (10); 2> GO 100 < Output omitted for brevity; Runs the INSERT statement 100 times> 1> :r c:\1.sql < Output omitted for brevity; Runs another script file>
The final command in this example can be particularly very helpful. You can use the :r command to execute some other script from within your T-SQL script. Also note the command before the :r line. The number 100 is passed with the GO statement, which leads to executing the batchthe INSERT statement, in this case100 times.
Table 5.5 lists the available SQLCMD commands.
The Query Editor in SQL Server Management Studio supports SQLCMD mode, which can be useful for authoring, editing, and executing T-SQL scripts that use SQLCMD features, such as SQLCMD commands and variables. You can use the SQLCMD Mode button on the SQL Editor toolbar or the Query | SQLCMD Mode menu item to turn on or off the SQLCMD mode.
SQLCMD and Variables
The support for using variables with SQLCMD script enables you to write generic scripts so that one script can be used for multiple scenarios. Your T-SQL scripts can use $(VariableName) syntax in the T-SQL statements, and then you can pass the variable values from the command line (by using the -v switch), by using the :setvar SQLCMD command, or by setting their values as environment variables, using the SET statement.
Let's say that the following text is available in a script file called SQLCMDVariables.sql:
EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = '$(DeviceLogicalName)', @physicalname = '$(DevicePhysicalName)'; GO BACKUP DATABASE $(DBName) TO $(DeviceLogicalName) GO
This script creates a new backup device and then performs a full database backup. The logical device name, physical file location, and database to back up are variables that can be set by using the :setvar command in the script (which is not done in this case), or they can be environment variables that are set by using the SET statement or by using the Control Panel, or they can be passed to SQLCMD by using the -v switch. Here is how to pass variables by using the -v switch:
Instead of passing the variable values on the command line by using the -v switch, you can set an environment variable with the same name as the variable name in the script, and SQLCMD uses the environment variable value if the variable value is not passed using -v:
C:\>SET DeviceLogicalName=AW2 C:\>SET DevicePhysicalName=c:\AW2.bkp C:\>SET DBName=AdventureWorks C:\>SQLCMD -E -S DDGXP\YUKON -i "c:\SQLCMDVariables.sql"
The third way to set variable values is by using the :setvar SQLCMD command, as illustrated earlier.
You can also use the $(VariableName) syntax to access environment variables:
1> SELECT '$(NUMBER_OF_PROCESSORS)' 2> GO
These statements, when executed in a SQLCMD session, return the value of the NUMBER_OF_PROCESSORS environment variable, indicating the number of processors on the local machine.
You can set the SQLCMDINI environment variable to a SQL script file that will be run every time SQLCMD is started. Assume that the following text is available in the c:\SQLCMDStartup.sql file:
SET NOCOUNT ON PRINT '' PRINT @@SERVERNAME PRINT @@VERSION PRINT GETDATE() PRINT SYSTEM_USER PRINT USER PRINT DB_NAME() PRINT ''
Now, if you set the SQLCMDINI environment variable to the c:\SQLCMDStartup.sql file, this script will be run every time you start SQLCMD:
C:\>SET SQLCMDINI=c:\SQLCMDStartup.sql C:\>SQLCMD -E -S DDGXP\YUKON <executes script c:\SQLCMDStartup.sql; output omitted for brevity>
Using a Dedicated Administrator Connection
In situations where SQL Server is locked or running in an abnormal state and not responding when you try to connect to it, the members of sysadmin server role can connect to a local or remote SQL Server 2005 instance over TCP/IP by using SQLCMD and the -A switch to troubleshoot problems on the SQL Server machine. This feature is called dedicated administrator connection (DAC). SQL Server allows only one DAC per instance. When you connect to SQL Server by using the -A switch, it is strongly recommended that you restrict your usage to certain diagnostic and troubleshooting commands only and set the isolation level to READ UNCOMMITTED to avoid any blocking. The 'remote admin connections' sp_configure option must be turned on in order to allow remote connections using DAC. If you are connecting to a named instance, the SQL Browser service must be started.
Introducing Database Mail
SQL Server 2005 introduces a highly scalable, reliable, and secure method of sending emails from within T-SQL code. This new feature, called Database Mail, uses Simple Mail Transfer Protocol (SMTP) to send email messages. Unlike the SQL Mail feature in SQL Server 2000, Database Mail does not require Microsoft Outlook or MAPI to be installed on the server.
Database Mail scales well because it uses Service Broker to asynchronously send email messages. Service Broker in turn activates an external process (DatabaseMail90.exe) to send email messages. This external Database Mail process uses a standard ADO.NET connection to communicate with SQL Server. This process isolation increases server reliability because even if the external process stops or fails, it does not affect the SQL Server process. The other reliability feature introduced in Database Mail is the ability to specify a failover SMTP server. If Database Mail cannot send by using one SMTP server, it tries to use another specified SMTP server to send emails. Unlike SQL Mail in SQL Server 2000, Database Mail is cluster aware and is fully supported on a cluster. Database Mail is fully supported on 64-bit SQL Server 2005 installations.
Some of the ways in which Database Mail offers a secure emailing solution are the MaxFileSize (to limit the size of email attachments) and ProhibitedExtensions (to restrict attachment file types) configuration settings, the fact that it does not require Outlook or MAPI on the SQL Server machine, and auditing and logging.
Database Mail only supports sending emails. Unlike SQL Mail in SQL Server 2000, Database Mail in this release cannot be used to read emails. MAPI-based SQL Mail is still available in SQL Server 2005 and can still be used to read and send emails.
Configuring Database Mail
Following the "secure by default" principal, SQL Server 2005 does not enable Database Mail support by default. You have to configure Database Mail before you can use msdb.dbo.sp_send_dbmail stored procedure to send emails. You can enable and configure Database Mail by using the Database Mail Configuration Wizard. You can launch the Database Mail Configuration Wizard from within Management Studio by right-clicking the Database Mail node under Management folder in Object Explorer and selecting the Configure Database Mail menu item.
Because Database Mail depends on Service Broker to send email messages, Service Broker must be enabled in the msdb database.
The Database Mail Configuration Wizard allows you to perform multiple tasks. You can use this wizard to enable and install Database Mail, to manage Database Mail profiles and accounts, to manage Database Mail security settings, and to configure Database Mail system parameters, such as MaxFileSize and ProhibitedExtensions.
The msdb database is the Database Mail host database. It contains the stored procedures and messaging objects for Database Mail. Database Mail stores the SMTP server information, profiles, accounts, security, and system parameters in the msdb system database.
Figure 5.11 shows various things that you can do by using the Database Mail Configuration Wizard.
Figure 5.11. The Database Mail Configuration Wizard can be used to enable and configure Database Mail.
The msdb.dbo. sp_send_dbmail stored procedure posts the email message in a Service Broker queue. The Service Broker activation feature launches an external application (DatabaseMail90.exe) when there is a message in the queue. This external process reads the messages from the Service Broker queue and the SMTP account and profile setting, including the SMTP server settings, from the msdb mail host database; and sends messages to the email server. The SMTP server is then responsible for actually delivering the messages.
Here are the steps for enabling Database Mail and sending an email by using Database Mail. These steps assume that you have an SMTP server installed and working. These steps use the local SMTP service installed as part of an IIS Web server. The SMTP virtual server is configured to grant connection and relay access to the IP address on which SQL Server 2005 is installed (local, in this case):
The msdb.dbo.sp_send_dbmail stored procedure takes various parameters, which you can use, for instance, to send results of a query in text or HTML format, attach files, and specify CC recipients, importance, sensitivity, and so on. Refer to SQL Server 2005 Books Online for details on other parameters.
Monitoring and Troubleshooting Database Mail
You can monitor and troubleshoot Database Mail by using the auditing and logging tables in the msdb mail host database.
For each outgoing email, Database Mail adds a status/logging record to the sysmail_log table, and the actual mail details are saved in the sysmail_mailitems table. You need to run a SELECT query on these two tables to find out whether Service Broker was able to activate the DatabaseMail90.exe application, whether DatabaseMail90.exe encountered problems while sending messages, and the status of each outgoing email. The event_type and description fields in the sysmail_log table and the sent_status field in the sysmail_mailitems table can be used to determine the success or failure for each outgoing email. The value of 0 for the sent_status field means that the DatabaseMail90.exe application is yet to process the message; 1 means success, indicating DatabaseMail90.exe was able to deliver message to the SMTP server; and 2 means that DatabaseMail90.exe received an error while delivering the message to the SMTP server, and in this case you should look at the sysmail_log table for more details about the failure.
In addition to the above-mentioned two tables, you can also access other Database Mail tables also in the msdb mail host database to verify the profile, SMTP account, security, and other system configuration settings. Table 5.6 lists these Database Mail-related tables in the msdb database.
In addition to tables mentioned in Table 5.6, you should also check the system and application event log (eventvwr.exe) entries for any errors related to Database Mail or SMTP. You should also check the folders under the C:\Inetpub\mailroot directory to see whether emails are being queued.