Course 2937: Administering and Monitoring Microsoft® SQL Server™ 2005

Overview of the sqlcmd UtilityIt is not always possible, or desirable, to use a graphical tool such as SQL Server Management Studio for executing Transact-SQL statements. For example, batch scripts scheduled for unattended execution do not usually operate in a graphical environment. SQL Server 2005 includes a utility called sqlcmd to facilitate this requirement.Definition The sqlcmd utility allows you to execute Transact-SQL statements and scripts from the command prompt and schedule batch jobs. This utility uses OLE DB to run Transact-SQL batches. Previous SQL Server utilities used ODBC or DB-Library. Important Although SQL Server 2005 provides both sqlcmd and osql, osql will not be supported in future versions of SQL Server. You should use sqlcmd for all new development. sqlcmd enhancements The sqlcmd utility extends the features found in osql, including the ability to use variables, connect to servers dynamically, query server information, and pass error information back to the calling environment. It also provides a Dedicated Administrator Connection (DAC) that allows a database administrator to connect to the server and resolve problems. The sqlcmd utility supports the following commands in addition to Transact-SQL statements. Command Description GO [count] Execute the command batch the specified number of times (default is once). RESET Clear the statement cache. ED Invoke the text editor over the most recent batch. The environment variable SQLCMDEDITOR specifies the editor to use (edit.com by default). !! cmd Execute the specified operating system command and return to the sqlcmd utility. QUIT Exit the sqlcmd utility. EXIT (statement) Exit the sqlcmd utility but pass the first value returned by the specified statement to the calling environment as the exit code. The following commands all require a colon prefix (:) Command Description :r filename Read the contents of the specified file into the statement buffer. :ServerList List all available computers in the network running SQL Server on the network. :List Display the contents of the statement cache. :Listvar Display the scripting variables and values that are currently set. :Error filename | STDOUT | STDERR Redirect all error output to the specified file, the standard output stream, or the standard error stream. :Out filename | STDOUT | STDERR Redirect all query results to the specified file, the standard output stream, or the standard error stream. erftrace filename | STDOUT | STDERR Redirect all performance trace information to the specified file, the standard output stream, or the standard error stream. :Connect server[\instance] [timeout] [user_name[password]] Connect to the specified server and instance using the supplied username and password. Time out if the server does not respond in the specified number of second. Default values for the connection parameters can be specified in the SQLCMDLOGINTIMEOUT, SQLCMDSERVER, SQLCMDUSER, and SQLCMDPASSWORD environment variables. :On Error [exit | ignore] Specify an action to be performed when an error occurs during command execution. The exit option quits the sqlcmd utility with an error. The ignore option displays an error message but the script will continue. :SetVar variable value Create or set a scripting variable with the specified value. :Help List sqlcmd commands with a short description of each. Sqlcmd command-line switches The following table describes the sqlcmd command-line switches. Many of these options are primarily useful when running sqlcmd as part of a non-interactive batch job. Switch Description -? Display the syntax summary of sqlcmd options. -L[c] List the server computers available on the network. If the c option is specified, the list is displayed without a heading. This switch cannot be used in conjunction with other switches. -U login_id [-P password] Connect using the specified SQL Server 2005 login user name and password. If these switches are omitted, sqlcmd will attempt to connect using Windows Authentication as the account running the sqlcmd utility. Alternatively, the user name and password can be specified in the sqlcmd and SQLCMDPASSWORD environment variables. -E Connect using Windows Authentication. (This is the default if the -U and -P switches are omitted.) -S server_name[\instance_name] Specify the server and instance to connect to. This information can also be specified in the SQLCMDSERVER environment variable. -H wksta_name The workstation name displayed by the sp_who stored procedure and the Activity Monitor in SQL Server Management Studio. The default value is the name of the computer being used to run the sqlcmd utility. This information can also be specified in the SQLCMDWORKSTATION environment variable. -d db_name The database to connect to. This information can also be specified in the SQLCMDDBNAME environment variable. -l time_out The number of seconds before the login to the OLE DB provider times out. The default is eight seconds. This information can also be specified in the SQLCMDLOGINTIMEOUT environment variable. -t time_out The number of seconds before a command or SQL statement times out. By default, commands do not time out. This information can also be specified in the SQLCMDSTATTIMEOUT environment variable. -h headers The number of rows to be printed between column headings. By default, column headings only appear once, at the start of a result set. Use –h-1 to indicate that column headings should not be displayed. This information can also be specified in the SQLCMDHEADERS environment variable. -s col_separator The column separator character to use. By default, the sqlcmd utility uses a blank space. This value can also be specified in the SQLCMDCOLSEP environment variable. -w column_width The output screen width. The default value is 80. This value can also be specified in the SQLCMDCOLWIDTH environment variable. -a packet_size Request that communications with SQL Server use the specified packet size. This information can also be specified in the SQLCMDPACKETSIZE environment variable. -e Echo input. -I Enable the QUOTED_IDENTIFIER connection option. -c cmd_end The batch terminator to use. By default, this is the word GO on a line by itself. Do not use a Transact-SQL reserved word. -q “query” Execute the specified query but remain connected when the query finishes. Do not include the GO terminator in the query. -Q “query” Execute the specified query, and then disconnect and close the sqlcmd utility. Do not include the GO terminator in the query. -m error_level Display message number, state, and error level only for messages of the specified level or higher. This information can also be specified in the SQLCMDERRORLEVEL environment variable. -r [0 | 1] Redirect error messages to the standard error device (the screen by default). The switch -r 0 will redirect messages with a severity level of 17 or higher only. The switch -r 1 will redirect all error message output (including print statement output). -i input_file[,file2…] Read input from the specified list of files rather than the keyboard. -o output_file Redirect output to the specified file rather than the screen. -p[1] Print performance information for each result set. If the 1 parameter is specified, the performance information is displayed as a series of colon-separated raw data values. -b If an error occurs, exit the sqlcmd utility and return a DOS ERRORLEVEL value. -u Store the output file in Unicode format. -R Use regional settings when converting currency data and data and time information into character format. -v var=”value”[var="value"…] Create scripting variables with the specified names and values. -A Use a DAC. -X[1] Disable commands that might compromise system security (ED and !! commands), display a warning, but then continue. If -X1 is used, display an error message and exit the sqlcmd utility if one of these commands is attempted. -V severitylevel Specify the lowest severity level that will be reported by the sqlcmd utility. Working with the sqlcmd Utility Any user who has a valid SQL Server 2005 account can invoke the sqlcmd utility interactively, submit commands, and display the results. Executing commands using sqlcmd Use the following procedure to execute a command interactively: Open a command prompt window. Type sqlcmd followed by any appropriate switches. To connect using Windows Authentication, simply type sqlcmd. Type Transact-SQL statements and sqlcmd commands. You can type in multiple commands and spread them across multiple lines. Type GO to execute the command batch. Type QUIT to close the sqlcmd utility. Executing Scripts Using the sqlcmd Utility As well as running the sqlcmd utility interactively, you can store commands and Transact-SQL statements in a script file and invoke sqlcmd to execute this script. Many of the command-line switches for the sqlcmd utility are intended for use when running sqlcmd in this manner. Executing a script Use the following procedure to execute a script using sqlcmd: Using a text editor, create a text file containing the commands and statements you want to execute. Note Make sure that you include the GO statement to actually run the commands. Set the environment variables necessary for accessing the appropriate computer running SQL Server, most commonly SQLCMDUSER, SQLCMDPASSWORD, and SQLCMDSERVER. Tip Although you can specify login name, password, and server as command-line parameters to the sqlcmd utility, it is better practice to use the environment variables, since you can then avoid the need to hard-code these values into batch scripts. From a command prompt, invoke sqlcmd using the -i switch to specify the name(s) of the script files. Capture the output using the -o switch. Examine the ERRORLEVEL variable to ensure that the script executed successfully. Examine the captured output file and check that the results are as expected. Using Variables with the sqlcmd Utility Using variables with sqlcmd allows you to create generalized scripts that can be executed against different databases and servers. Apart from the sqlcmd environment variables, you can define variables inside scripts using the :setvar command. You can also pass variables into sqlcmd scripts from the environment, or use the -v switch from the command line. Using variables Use the following procedure to execute a script using variables: Create a sqlcmd script. Reference values that will be passed in using the following syntax: $(variable) For example: SELECT $(colname)FROM $(tabname)GO Execute the script using the –v switch to specify the values of variables. For example: sqlcmd –i MyScript.sql –o MyScript.out –v colname=”name” tabname=”sys.databases” Alternatively, you can define colname and tabname as environment variables. Working with a Dedicated Administrator Connection In earlier releases of SQL Server, connecting to a server that was unresponsive was not possible. However, a Dedicated Administrator Connection (DAC), a feature of SQL Server 2005, will allow access even when the server hangs or is otherwise unavailable. A DAC has its own private SQL Server scheduler. Therefore, the only time you cannot connect is if the SQL Server service has been stopped or paused. Note that SQL Server only supports a single DAC; an attempt to use a second DAC while the first is still active will be refused. Once you have gained access to the unresponsive server, you can execute commands to diagnose the problem, terminate misbehaving connections, or shut the server down cleanly and restart it. Using a Dedicated Administrator Connection Use the following procedure to open a DAC: Invoke the sqlcmd utility specifying the -A switch: SQLCMD –A Diagnose and fix the problem, terminate misbehaving connections, or shut the server down cleanly. 1>SHUTDOWN WITH NOWAIT2>GOServer shut down by request Tip An unresponsive user connection is often caused by a process waiting for a lock that is held by a long-running transaction. You can use the sp_lock stored procedure to find connections that hold exclusive locks over resources and that are blocking requesting connections. You can terminate blocking connections using the KILL command if they do not complete in a timely manner. ********************************************************************* Overview of SQL Management Objects It is useful to be able to automate the administrative tasks that an administrator needs to perform, especially because it reduces the possibility of errors and inconsistencies. This is particularly true when an enterprise contains multiple SQL Server servers and instances. SQL Server 2005 provides the SQL Management Objects (SMO) API for this purpose. Definition SMO provides the ability to create administrative programs and scripts. It is a set of programming objects intended for writing programs used to manage SQL Server objects and tasks. SMO is implemented as a .NET assembly (Microsoft.SqlServer.Smo.dll). SMO defines a hierarchy of objects and its own object model. You can use SMO to build applications that perform many administrative tasks, such as retrieving and modifying configuration settings, creating new databases, managing SQL Server Agent jobs, and scheduling backups. Comparing SMO and SQL Server Distributed Management Objects SMO is intended to be a complete replacement for SQL Server Distributed Management Objects (SQL-DMO) supplied with earlier versions of SQL Server. The database engine in SQL Server 2005 has undergone many significant internal changes, and SMO is designed to take full advantage of these changes. SMO is optimized to make the most efficient use of resources, and balances the need to save memory against the requirements to query fully instantiated objects over the network. Using SMO, a developer can closely control the point at which objects are instantiated. Note Although SQL-DMO is shipped with SQL Server 2005, it is provided for backward compatibility only. SQL-DMO does not operate in an optimal manner with SQL Server 2005. You should use SMO for all new development. SMO and WMI SQL-DMO was intended to be a programmer-centric interface to SQL Server. SMO extends this programmability, and provides a more administrator-centric view through its simplified interface to Windows Management Instrumentation (WMI). You can make use of WMI with SMO for monitoring and configuring SQL Server 2005 servers and instances. SMO Scripting SMO provides enhanced scripting features through the Scripter class. For example, you can use this class to automatically script an entire dependency tree based on a single object. Creating an SMO Application and Retrieving Server Information You can use Visual Studio 2005 to develop SMO applications. These can be designed as Windows Forms applications, ASP.NET Web applications, or console applications, depending on your specific requirements. For example, if you need to manage databases from many locations within your company, an ASP.NET application running on the corporate intranet removes the need for installation on client computers. Creating a .NET SMO application Use the following procedure to create a .NET SMO application: Open Visual Studio 2005. Create a new project using the language of your choice and the required application type, for example, a Microsoft Visual Basic® .NET Windows Form project. Reference the SQL SMO assemblies. To work with SQL Server, you must reference Microsoft.SqlServer.SMO. Depending on the functionality of your application, you may need to reference other assemblies in the Microsoft.SqlServer namespace—for example, Microsoft.SqlServer.ConnectionInfo. Import the SMO namespaces by using the Imports statement in Visual Basic .NET or the using statement in Microsoft Visual C#®. Connect to the computer running SQL Server by instantiating a Server object, obtaining the ConnectionContext object, specifying the server instance name, and connecting to the server. By default, Windows Authentication is used to connect when developing SMO applications. Code your application. Retrieving Information After you have connected to a computer running SQL Server, you can use the Server object to retrieve server-specific information. The Server object has an Information property that exposes many of the non-configurable properties of the server. The following table lists the properties of the Information object that you can use to return server information to your application. Property Description Edition Gets the edition of the computer running SQL Server IsClustered Gets a Boolean value indicating whether the server is part of a cluster IsSingleUser Gets a Boolean value indicating whether the server is currently set as read-only Language Gets the default language of the computer running SQL Server NetName Gets the NetBIOS name of the server OSVersion Gets the operating system version of the computer Parent Gets the Server object that is the parent of this Information object PhysicalMemory Gets the total RAM (in MB) of the computer Platform Gets the hardware platform of the computer Processors Gets the number of processors installed on the computer Product Gets the product title for the computer running SQL Server ProductLevel Gets the product level for the computer running SQL Server VersionString Gets the version of the computer running SQL Server Using SMO to Back Up a Database You can also use SMO to perform regular administrative tasks, such as backing up a database. You can use the properties of the Backup object to customize your backup to meet your requirements. Backing up a database Use the following procedure to write SMO code to back up a database: Connect to the computer running SQL Server. Declare and instantiate a Backup object. Set the following properties of the Backup object: Action BackupSetName Database DeviceType Any other custom properties, such as Incremental and NoRewind Call the Devices.Add method of the Backup object, passing the path to the device. Call the SqlBackup method of the Backup object, passing the Server object. Properties of the Backup Object You can use the properties of the Backup object to customize your backup to meet your requirements. The information in the following table describes the key properties of the Backup object. Property Description Action The type of backup to be performed BackupSetDescription Textual description for a backup set BackupSetName Name used to identify a backup set BlockSize Block size unit to be used when formatting tapes as part of a backup Checksum Boolean property that determines whether a checksum value is calculated during backup or restore operations Database Database on which the backup or restore operation runs DatabaseFileGroups SQL Server file groups targeted by the backup operation DatabaseFiles Operating system files targeted by the backup operation Devices Backup devices specified for the backup operation DeviceType Device type of a device associated with the backup operation ExpirationDate Date and time when the backup set expires and the backup data is no longer considered relevant FormatMedia Boolean property that determines whether a tape is formatted as the first step of the backup operation Incremental Boolean property that determines whether to perform an incremental backup Initialize Boolean property that determines whether the devices associated with a backup operation are initialized as part of the backup operation LogTruncation Name of method used to truncate the database log as part of the backup operation MediaDescription Textual description of the media that contains a backup set MediaName Name used to identify particular media set NoRewind Boolean property that determines whether SQL Server will keep the tape open after the backup operation Restart Boolean property that determines whether the backup operation will restart if it is interrupted RetainDays Number of days that must elapse before a backup set can be overwritten SkipTapeHeader Backup operation logic that determines whether correct media is loaded UnloadTapeAfter Boolean property that determines whether tape media is rewound and unloaded after the backup operation completes using System.SqlServer.Management.Common; using System.SqlServer.Management.Smo;Server myServer = new Server();ServerConnection conn = myServer.Connection.Context;conn.ServerInstance = txtServerName.Text;conn.Connect();1stDatabases.Items.Clear();for (int i = 0; i < myServer.Databases.Count; i++){ 1stDatabases.Items.Add(myServer.Databases[i].Name);}Database newDb = new Database(myServer, “NewDatabase”);newDb.Create(); Understanding SQL Profiler Enhancements in SQL Server 2005 SQL Profiler in SQL Server 2005 provides more information about performance and database processing than was available in earlier versions. New and enhanced features The important changes to SQL Profiler are summarized in the following table. Click an item to view more information. Feature Description Profiling Analysis ServicesSQL Profiler can capture and trace events raised by SQL Server 2005 Analysis Services. (Profiling Analysis Services is beyond the scope of this course and will not be discussed further.) Tracing Showplan and deadlock eventsSQL Profiler provides enhancements to Showplan and deadlock trace events, including: Graphical representation of deadlock cycles, allowing you to analyze how deadlock occurs. The ability to save Showplan results as XML, which can be imported into other tools capable of reading XML files. Saving results as XMLSQL Profiler trace results can be saved as XML as well as the standard formats: ANSI, Unicode, and OEM. Aggregating data SQL Profiler data can be aggregated based on a selected key. You can use this new feature by providing a simple count of the number of times events have occurred. SQL Profiler can capture and trace events raised by SQL Server 2005 Analysis Services. (Profiling Analysis Services is beyond the scope of this course and will not be discussed further.) Saving a Trace as XML XML is a portable data format, understood by an ever-increasing number of tools. Saving trace information as an XML file, rather than in the SQL Profiler native format, allows an administrator to use a variety of tools for analyzing the data. For example, you can import the XML data into applications such as Microsoft Office Excel®, or you can write a custom application to perform your analysis. Saving a trace as XML Use the following procedure to save a trace as XML: In SQL Profiler, create and execute a trace. When the trace has ended, on the File menu, point to Save As, and then click Trace XML File. Specify a file name and location, and then click Save. Overview of DDL Triggers It is useful to be able to monitor data definition operations, such as a user creating or dropping a table, when auditing and regulating database actions. DDL triggers provide the means for capturing data definition events transparently, so that a user executing a data definition language statement will not be aware that the task is being observed. A DDL trigger fires whenever a user executes a specified data definition language statement, such as CREATE TABLE, DROP TRIGGER, or UPDATE STATISTICS. DDL trigger scope Unlike ordinary triggers that are associated with tables, a DDL trigger executes when a particular statement is executed, regardless of the target of that statement. Some DDL triggers operate at the database level and only fire whenever the corresponding event occurs in a specific database. These triggers are predominantly concerned with objects in a single database, such as tables, indexes, users, procedures, views, and so on. An example of a DDL trigger that operates with database scope is CREATE_TABLE. Other DDL triggers operate at the server level and fire regardless of the database in use. These triggers are associated with server-level objects, such as logins and security certificates. The CREATE_LOGIN DDL trigger is an example that operates with server scope. For information on which DDL triggers operate at database and server scope, see the section “Event Groups for Use with DDL Triggers” in SQL Server 2005 Books Online. DDL trigger process DDL triggers fire on completion of the triggering action. For example, a DROP_TABLE trigger fires after the target table has been successfully dropped. There is no equivalent to INSTEAD OF triggers for DDL operations. A single DDL operation can fire multiple DDL triggers. The order in which the triggers fire is not documented; you should not rely on DDL triggers to execute in any particular sequence. You can use the ROLLBACK TRANSACTION statement to abort the current transaction and undo any work that has been performed, including the DDL operation that caused the trigger to fire. Creating DDL Triggers You create DDL triggers using the CREATE TRIGGER statement. In addition, you can use some new syntax extensions that have been added to SQL Server 2005. CREATE TRIGGER Syntax CREATE TRIGGER trigger_nameON { ALL SERVER | DATABASE}[ WITH […,n] ]{ FOR | AFTER } { event_type | event_group ] [ ,…n]AS { sql_statement [ …n] | EXTERNAL NAME < method specifier > }[ ; ] Event Data XML In the trigger code, information about the DDL operation that fired the trigger is available in the form of an XML document, which can be obtained by using the eventdata() function. You can retrieve this XML data into an xml variable, and use the xml data type’s query method to extract specific information.Managing DDL Triggers SQL Server 2005 has extended the syntax of the commands used for managing triggers with extensions specifically for DDL triggers. You can obtain a list of available triggers in a database, including DDL triggers, by querying the sys.triggers catalog view. You can retrieve the definition of a trigger by querying the sys.sql_modules catalog view. Triggers are deleted with the DROP TRIGGER command. Information on the syntax for viewing, modifying, and deleting triggers is detailed in the following table. Viewing triggers SELECT nameFROM sys.triggersSELECT definitionFROM sys.sql_modulesWHERE [object_id] = (SELECT [object_id] FROM sys.triggers WHERE name=’trigger_name’) Modifying triggers DROP TRIGGER trigger_name [, …n]ON { DATABASE | ALL SEVER }[ ; ] SELECT nameFROM sys.triggersSELECT definitionFROM sys.sql_modulesWHERE [object_id] = (SELECT [object_id] FROM sys.triggers WHERE name=’trigger_name’) Important When modifying or dropping a trigger, you must specify the ON DATABASE or ON ALL SERVER clause as appropriate for the trigger. If you omit this clause, SQL Server 2005 will assume you are referring to a standard trigger and report an error because it will fail to find it. Using a DDL Trigger to Audit Database Operations DDL triggers are important administrative tools. In this section you will see a fully worked example for a common administrative task; using a DDL trigger to audit database operations. The three key stages in the process are shown in the following table. Create an audit log table. USE AdventureWorks GO CREATE TABLE dbo.AuditLog(Command NVARCHAR(1000),PostTime NVARCHAR(24),HostName NVARCHAR(100),LoginName NVARCHAR(100)) GO Create the trigger. CREATE TRIGGER AuditOperationsON DATABASEFOR DDL_DATABASE_LEVEL_EVENTSASDECLARE @data XMLDECLARE @cmd NVARCHAR(1000)DECLARE @posttime NVARCHAR(24)DECLARE @spid NVARCHAR(6)DECLARE @hostname NVARCHAR(100)DECLARE @loginname NVARCHAR(100) – Retrieve the event data XMLSET @data = eventdata()– Extract data from the XMLSET @cmd = CONVERT(NVARCHAR(100), data.query(‘data(//TSQLCommand//CommandText)’))SET @posttime = CONVERT(NVARCHAR(24), @data.query(‘data(//PostTime)’)) SET @spid = CONVERT(NVARCHAR(6), @data.query(‘data(//SPID)’))- - Retrieve other system informationSET @hostname = HOST_NAME()SET @loginname = SYSTEM_USER– Write event data to the log tableINSERT INTO dbo.AuditLog(Command,PostTime,HostName,LoginName)VALUES(@cmd, @posttime, @hostname, @loginname)GO View the events captured in the audit log. SELECT *FROM dbo.AuditLogGO USE AdventureWorks GO CREATE TABLE dbo.AuditLog(Command NVARCHAR(1000),PostTime NVARCHAR(24),HostName NVARCHAR(100),LoginName NVARCHAR(100))GO Overview of Event Notifications Event notifications, like triggers, can respond to a variety of database events. Unlike triggers, which execute code in the same SQL Server 2005 database engine as the event that fired the trigger, event notifications can communicate with service brokers to transmit information about events to external processes—often other SQL Server 2005 instances. Definition An event notification reacts to one or more events in the database and sends a message to a Service Broker service designed to handle the events. A database event can be a DML or DDL operation on a database object, or a trace event similar to those that can be captured using SQL Profiler. Capturing events A Service Broker service is a stored procedure or application built using the Service Broker framework that monitors a queue for incoming messages. A Service Broker service can be written using Transact-SQL or one of the CLR languages. Messages are sent to the service by Service Broker, which uses a queue to persist message information until the service is ready to accept them. The service can also use a queue to hold requests until it can process them. To simplify the event notification creation process, SQL Server 2005 includes a message type and contract designed for event notifications. The only Service Broker objects that you need to create are a queue, a service, and a route. Creating Event Notifications Use the CREATE EVENT NOTIFICATION statement to create a new event notification object. This table describes the four stages in creating and event notification and shows the syntax elements relevant to each of the steps. Description The first step is to specify the name of the event notification that you are creating. This name must conform to the rules for identifiers and must be unique with the scope they are created in. CREATE EVENT NOTIFICATION event_notification_name ON { SERVER | DATABASE | ASSEMBLY assembly_name | object_specifier object_name FOR { event_type | event_group } [ ,…n] TO SERVICE broker_service [ ,’broker_instance_specifier’] The first step is to specify the name of the event notification that you are creating. This name must conform to the rules for identifiers and must be unique with the scope they are created in. Syntax CREATE EVENT NOTIFICATION event_notification_name ON { SERVER | DATABASE | ASSEMBLY assembly_name | object_specifier object_name FOR { event_type | event_group } [ ,…n] TO SERVICE broker_service [ ,’broker_instance_specifier’] You specify the scope of an event notification when you create it. For example, you may use a server-wide scope, and the event notification will occur whenever the UPDATE STATISTICS command is executed in any database on the server. You can restrict the notification so that it occurs only when a command is executed in the current database using ON DATABASE, or on a specified object. CREATE EVENT NOTIFICATION event_notification_name ON { SERVER | DATABASE | ASSEMBLY assembly_name | object_specifier object_name FOR { event_type | event_group } [ ,…n] TO SERVICE broker_service [ ,’broker_instance_specifier’] The events trapped by an event notification can be very specific, as shown in the previous examples, or they can be more generic. For example, to trap all DDL events in the database, use FOR DDL_DATABASE_LEVEL_EVENTS. You can also trap all DML events over an object by specifying FOR DML_EVENTS. You can also capture a trace event. CREATE EVENT NOTIFICATION event_notification_name ON { SERVER | DATABASE | ASSEMBLY assembly_name | object_specifier object_name FOR { event_type | event_group } [ ,…n] TO SERVICE broker_service [ ,’broker_instance_specifier’] This specifies the target service that receives the event instance data. The target service must use the same SQL Server Event Message type and contract that is used to send the message. CREATE EVENT NOTIFICATION event_notification_name ON { SERVER | DATABASE | ASSEMBLY assembly_name | object_specifier object_name FOR { event_type | event_group } [ ,…n] TO SERVICE broker_service [ ,’broker_instance_specifier’] The first step is to specify the name of the event notification that you are creating. This name must conform to the rules for identifiers and must be unique with the scope they are created in. Processing Event Notifications The simplest way to create a Service Broker service for handling SQL Server events is to use a stored procedure that is activated whenever a message appears on a queue, sent by an event notification. Receiving messages The stored procedure should first retrieve the message from the queue using the RECEIVE command and store the information in variables. The WAITFOR command is used to suspend processing until a message is received or two seconds have passed. If no messages are received, no further action needs to be taken. Processing messages The stored procedure can then process the message. Messages in the queue are assigned a message_type_name that describes the type of the message. You can use this to identify whether the message is an event notification or an event notification error message. Messages also contain a message_body column that contains the actual event information. This column contains an EVENT_INSTANCE XML document that includes the same elements as the document generated by DDL trigger. You can use the query method of the document to retrieve event information. Managing Event Notifications SQL Server 2005 provides new Transact-SQL statements for managing event notifications. These statements make it possible to view, modify, and remove event notifications. Viewing event notifications and queues You can obtain a list of event notifications by querying the sys.event_notifications catalog view. You can also view a list of queues, including those used by event notifications, by querying the sys.service_queues catalog view. Removing an event notification Use the DROP EVENT NOTIFICATION command to permanently remove an event notification from the database. For more information on the DROP EVENT NOTIFICATION command, see the Transact-SQL Reference in SQL Server 2005 Books Online. Note Event notifications rely on SQL Server 2005 Service Broker functionality. To use event notifications, you must enable Service Broker by enabling the ENABLE_BROKER setting for the database in which your event notification code resides. Defining Event Notifications This code example enables Service Broker and creates a table called AuditLog that will be used to log captured events. Creating a Queue CREATE QUEUE NotifyQueue GO Creating a Service CREATE SERVICE NotifyService ON QUEUE NotifyQueue( [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]) GO Creating a Route CREATE ROUTE NotifyRoute WITH SERVICE_NAME = ‘NotifyService’,ADDRESS = ‘LOCAL’ GO Creating an event notification that fires when a CREATE TABLE operation is performed on the AdventureWorks database CREATE EVENT NOTIFICATION Notify_Create_Table ON DATABASE FOR CREATE_TABLETO SERVICE [NotifyService] Processing messages and showing the contents of the AuditLog table DECLARE @messageTypeName NVARCHAR(256), @messageBody XML; RECEIVE TOP(1) @messageTypeName = message_type_name, @messageBody = message_body FROM dbo.NotifyQueue; IF @@ROWCOUNT = 0 RETURN PRINT CONVERT(NVARCHAR(1000),@messagebody) DECLARE @cmd NVARCHAR(1000) DECLARE @posttime NVARCHAR(24) DECLARE @spid NVARCHAR(6) DECLARE @hostname NVARCHAR(100)DECLARE @loginname NVARCHAR(100)SET @cmd = CONVERT( NVARCHAR(100),@messagebody.query(‘data(//TSQLCommand//CommandText)’)) SET @posttime = CONVERT(NVARCHAR(24), @messagebody.query(‘data(//PostTime)’)) SET @spid = CONVERT(NVARCHAR(6),@messagebody.query(‘data(//SPID)’)) SET @hostname = HOST_NAME() SET @loginname = SYSTEM_USERINSERT INTO AuditLog(Command,PostTime,HostName,LoginName)VALUES(@cmd, @posttime, @hostname, @loginname) GO SELECT * FROM AuditLog Querying Dynamic Metadata You can use dynamic management views and dynamic management functions to query dynamic metadata in SQL Server 2005. They provide information about the current state of SQL Server, such as locks currently held within a database. Dynamic management functions are a special case of a dynamic management view in which the query requires parameters to be sent in order to execute. For example, sys.dm_db_index_physical_stats, which replaces the DBCC SHOWCONTIG statement, needs to know which table and index you require information about. Dynamic management views Dynamic management views are listed alongside the catalog views in the \Views\System Views folder in Object Explorer in SQL Server Management Studio. They are also defined in the sys namespace, but their names generally contain the dm prefix to distinguish them from catalog views—for example, sys.dm_tran_locks. Querying dynamic management views You can query dynamic management views using a standard SELECT statement. For example, the following code returns a list of queue monitors in SQL Server: SELECT * FROM sys.dm_tran_locks Dynamic management viewsys.dm_db_partition_stats Returns page and row count information for each partition in the database sys.dm_exec_sessions Returns information about all the current sessions connected to the server sys.dm_io_pending_io_requests Returns information about pending input/output requests sys.dm_os_memory_pools Returns information about each simple object cache in the system sys.dm_os_threads Returns information about the threads in the system sys.dm_broker_queue_monitors Returns information about each Service Broker queue monitor in the system sys.dm_tran_locks Returns information about every currently granted or requested lock in the system Querying Metadata Catalog views of static metadata and dynamic management views of dynamic metadata provide tabular results showing queried information. On this page you have examples of the syntax used to view metadata for each and the resulting tables of information that you will receive. Catalog Views List the databases on the SQL Server server. USE masterSELECT * FROM sys.databases Querying Dynamic Management Views List the tables in the database. USE AdventureWorksSELECT * FROM sys.dm_db_partition_stats The following code samples can be used to monitor a SQL Server server. Where a database name is required the AdventureWorks example database is used. Catalog Views List Databases This code lists the databases on the server. Note that this view returns the same results regardless of current database context. USE AdventureWorksSELECT * FROM sys.databases List Tables This code lists the tables in the current database. USE AdventureWorksSELECT * FROM sys.tables List Columns from all Database Tables This code lists the columns from all the tables in the current database. USE AdventureWorksSELECT * FROM sys.columns List Columns in a Single Table This code lists the columns in the Person.Contact table. USE AdventureWorksSELECT * FROM sys.columnsWHERE [object_id] = (SELECT [object_id] FROM sys.tables ST JOIN sys.schemas SS ON ST.schema_id = SS.schema_id WHERE ST.name=’Contact’ AND SS.name = ‘Person’) List Registered Assemblies This code lists the registered assemblies in the database. USE AdventureWorksSELECT * FROM sys.assemblies List Registered Database Events This code lists the registered events in the database. USE AdventureWorksSELECT * FROM sys.events List Server Configuration Options This code lists the server configuration options. Note that this view returns the same results regardless of current database context. USE AdventureWorksSELECT * FROM sys.configurations Dynamic Management Views List Table Partitions This code lists the table partitions in the database. USE AdventureWorksSELECT * FROM sys.dm_db_partition_stats Transaction Locks USE AdventureWorks – list the transaction locks currently held in the database SELECT * FROM sys.dm_tran_locks – execute a query without releasing locks and then list the locks. – Note that there are many more locks than previously listed BEGIN TRANSELECT * FROM Person.Contact WITH (HOLDLOCK)SELECT * FROM sys.dm_tran_locks – commit the transaction, thus releasing the locks. – Note that the list of locks has returned to the original value. COMMIT TRANSELECT * FROM sys.dm_tran_locks Introducing New Index Features in SQL Server 2005 SQL Server 2005 provides clustered and nonclustered indexes, just as SQL Server 2000 does. SQL Server 2005 also still supports indexes on computed columns and views. However, SQL Server 2005 extends the way in which indexes are structured and processed, as well as how new index types are added. Click an item to view the description Item Description The ALTER INDEX statementOne new index feature in SQL Server 2005 is the ALTER INDEX Transact-SQL statement, which allows you to rebuild, reorganize, disable, or modify the options for an index. This command removes the need to drop and create an index to defragment it, and replaces some of the Database Consistency Checker (DBCC) commands used by earlier versions of SQL Server. Online index operationsSQL Server 2005 provides the ability to manage indexes while they are in use, minimizing the maintenance window required. Previous versions of SQL Server acquired exclusive locks on the table and index, effectively blocking access to the table until the index operation had completed. Online index operations require additional disk space to support online operations. You specify whether an index should permit online operations as part of the CREATE INDEX, ALTER INDEX, and DROP INDEX commands. Parallel index operationsIndex operations, such as creating, altering, or dropping an index, can take advantage of multiple CPUs to perform sorting and scanning operations in parallel. This happens automatically in SQL Server 2005, and operations can use any number of processors up to the value specified by the max degree of parallelism server configuration option. You can manage the number of processors used by specifying the MAXDOP query hint when executing CREATE INDEX, ALTER INDEX, and DROP INDEX Transact-SQL commands. Locking optionsSQL Server 2005 introduces two new locking options when using indexes. These can be enabled simultaneously. ALLOW_PAGE_LOCKS When switched on, this option provides access to the index using table and page-level locks. When off, page-level locks are not used. ALLOW_ROW_LOCKS When switched on, this option provides access to the index using table and row-level locks. When off, row-level locks are not used. Indexes with included columnsNonclustered indexes can now contain nonkey data in their leaf nodes. This feature can improve the performance of queries that access the included columns, at the cost of the additional storage required to hold those values. Indexes with included columns can be used to replace covering indexes used in earlier versions of SQL Server. An additional benefit of indexes with included columns concerns the size limitations of index keys. In SQL Server 2000, the index key size was limited to 900 bytes. The leaf node data in an index with included columns can be up to 8060 bytes in size, which is the same as the maximum row size. Partitioned indexesPartitioned indexes are another new index management feature of SQL Server 2005. A partitioned index is an index that is spread across the filegroups comprising the physical database. Partitioned indexes are commonly created over partitioned tables. Partitioning can improve performance by distributing data across multiple files, reducing contention, and increasing the possibility of performing IO operations in parallel. For more information, see “Partitioned Index Concepts” in SQL Server 2005 Books Online. XML indexesThe xml data type is a new feature in SQL Server 2005. You can create XML indexes over xml columns using the CREATE PRIMARY XML INDEX and CREATE XML INDEX Transact-SQL commands. XML indexes can greatly improve the performance of XQuery operations over XML data, although they can incur some significant cost when data is modified. XML indexes have a number of features and limitations. For more information about XML indexes, see “Indexes on xml Type Columns” in SQL Server 2005 Books Online. One new index feature in SQL Server 2005 is the ALTER INDEX Transact-SQL statement, which allows you to rebuild, reorganize, disable, or modify the options for an index. This command removes the need to drop and create an index to defragment it, and replaces some of the Database Consistency Checker (DBCC) commands used by earlier versions of SQL Server. The following example shows how to use the sys.dm_db_index_physical_stats function over all tables in the current database: SELECT TableName, IndexName, AvgFragmentation FROM sys.dm_db_index_physical_stats (DEFAULT, ‘*’, DEFAULT, ‘DETAILED’) The parameters of the sys.dm_db_index_physical_stats function, in order, are: @TableName. The table about which to gather information. This can be the name of a specific table, or the values DEFAULT or NULL. DEFAULT and NULL both cause sys.dm_db_index_physical_stats to return information about all tables and views in the current database. @IndexName. The index about which to gather information. This can be the name of a specific index, DEFAULT, NULL, or ‘*’. The values DEFAULT and NULL both return information about the base table only (or clustered index, if the table has one). The value ‘*’ returns information about all indexes on the table. @PartitionId. The partition ID number of the index. The values DEFAULT, NULL, or 0 return information about all partitions. @Mode. The scan level to use to obtain the requested information. Supported values are LIMITED, which reads only parent-level pages but is very fast, SAMPLED, which reads parent-level pages and sample leaf pages, and DETAILED, which reads parent-level pages and all leaf pages. For more information about the sys.dm_db_index_physical_stats function, see the Transact-SQL Reference in SQL Server 2005 Books Online Database Tuning Advisor The Database Tuning Advisor, or DTA, in SQL Server 2005 is a new tool that extends the functionality of the Index Tuning Wizard in previous releases. To tune a database, you need to specify the workload you want the DTA to analyze. The workload can be a table or file created from SQL profiler trace, or a file containing Transact-SQL scripts. You also need to specify the databases, and optionally the database objects, that you want the DTA to include in the analysis. The DTA uses the workload to analyze index utilization in the specified databases. From its analysis of the workload, the DTA generates recommendations for indexes, and also generates reports that show index usage and performance metrics. ————————————————– LAB Step 1 : Create the DDL trigger. Action: Start SQL Server Management Studio and connect to the localhost server using Windows Authentication. Create a new SQL Server Query and connect to the localhost server using Windows Authentication. Using the query editor, add Transact-SQL code to create a new DDL trigger called CheckDropTable that responds to the DROP_TABLE event in the AdventureWorks database. Add the following statements to the body of the trigger: DECLARE @data XML DECLARE @tablename sysname DECLARE @schemaname sysname SET @data = eventdata() SET @tablename = CONVERT(sysname, @data.query(‘data(//ObjectName)’)) SET @schemaname = CONVERT(sysname, @data.query(‘data(//SchemaName)’)) These statements use the eventdata function to retrieve information concerning the table being dropped, and then parse the returned XML document to find the name of the table and the schema it belongs to. Add the following statements to the body of the trigger: IF @schemaname = ‘Production’ BEGIN PRINT ‘Cannot drop ‘ + @tablename + ‘ as it belongs to the Production schema’ ROLLBACK TRANSACTION END This code examines the name of the schema the table belongs to. If it is named Production, a message is displayed and the DROP TABLE operation is rolled back. Execute the code to create the trigger. Result: A new DDL trigger is added to the AdventureWorks database. Step 2 : Test the DDL trigger. Action: Create a new SQL Server query that creates a copy of the Product table called ProductCopy in the Production schema using a SELECT INTO statement. Attempt to drop the ProductCopy table you just created using a DROP TABLE statement. The message Cannot drop ProductCopy as it belongs to the Production schema is displayed and the transaction is aborted. Create another copy of the Product table in the guest schema. Drop the ProductCopy table you just created in the guest schema. The table will be dropped successfully, as it does not belong to the Production schema. Try to query the ProductCopy table in the guest schema to verify that it was dropped. The message Invalid object name ‘guest.ProductCopy’. should appear. Result: The trigger prevents the ProductCopy table in the Production schema from being dropped, but allows the ProductCopy table in the Guest schema to be dropped. Step 3 : Drop the DDL trigger and Production.ProductCopy table Action: Delete the CheckDropTable trigger from the Programmability\Database Triggers folder in Object Explorer. Drop the ProductCopy table in the Production schema. The table should be dropped successfully. Result: Both the CheckDropTable trigger and the ProductCopy table are dropped. Task 2 Creating Event Notifications You can view the correct Transact-SQL code for this exercise in CreateEventNotification.sql in the D:\Labfiles\Solution folder. Step 1 : Create the EventLog table and the EventLogProc stored procedure. Action: In SQL Server Management Studio, create a new table in the AdventureWorks database called dbo.EventLog. This table should have a single column called eventinfo using the XML data type. Enable Service Broker, as shown here: USE AdventureWorks ALTER DATABASE AdventureWorks SET ENABLE_BROKER; Execute this query. Create the EventLogProc stored procedure, as shown here: CREATE PROCEDURE dbo.EventLogProc AS DECLARE @message_body XML ;WAITFOR( RECEIVE TOP(1) @message_body=message_body FROM EventLogQueue ), TIMEOUT 2000 ; IF @@ROWCOUNT=0 RETURN INSERT INTO dbo.EventLog(eventinfo) VALUES (@message_body) GO This stored procedure copies the information from the message to a new row in the EventLog table. Execute this query. Result: A new table named EventLog is created, and the Service Broker is enabled for the AdventureWorks database. A new stored procedure named EventLogProc is also created. Step 2 : Create the EventLogQueue queue, EventLogService service, EventLogRoute route, and EventLogNotify event notification. Action: Create the EventLogQueue queue that will be used by Service Broker to submit requests to the event notification service and execute the EventLogProc stored procedure: CREATE QUEUE EventLogQueue WITH STATUS = ON, ACTIVATION ( PROCEDURE_NAME = dbo.EventLogProc, MAX_QUEUE_READERS = 5, EXECUTE AS SELF) GO Create the EventLogService that will be called by the event notification: CREATE SERVICE EventLogService ON QUEUE EventLogQueue ( [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification] ) GO Create the EventLogRoute that will be used to identify where messages should be directed: CREATE ROUTE EventLogRoute WITH SERVICE_NAME = ‘EventLogService’, ADDRESS = ‘LOCAL’ GO Create the EventLogNotification event notification. This event notification will fire whenever a CREATE TABLE or DROP TABLE statement is executed in the AdventureWorks database and invoke the EventLogService to record the event: CREATE EVENT NOTIFICATION EventLogNotification ON DATABASE FOR CREATE_TABLE, DROP_TABLE TO SERVICE ‘EventLogService’, ‘current database’ Execute the queries in steps 1-4 above. Result: The EventLogQueue queue, the EventLogService service, the EventLogRoute route, and the EventLogNotify event notification are created. Step 3 : Test the EventLogNotify event notification. Action: Use the following commands to create and drop a new table: CREATE TABLE Test (col char) GO DROP TABLE Test GO These DDL statements will cause the event notification to fire and the two commands will be recorded in the EventLog table. Execute this query. Use the following command to query the EventLog table: SELECT * FROM EventLog GO Execute this query and verify that two rows are returned, each containing an XML document that describes the operations performed in the previous step. Result: A new table is created and then dropped, and these actions are logged in the EventLog table. Step 4 : Remove the event notification objects. Action: Drop the EventLogNotification event notification. Drop the EventLogService service. Drop the EventLogRoute route. Drop the EventLogQueue queue. Drop the EventLogProc stored procedure. Drop the dbo.EventLog table. Close SQL Server Management Studio without saving changes. Result: The EventLogNotify event notification, the EventLogService service, the EventLogRoute route, the EventLogQueue queue, the EventLogProc procedure, and the EventLog table are all deleted.