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 sqlcmd enhancements The sqlcmd utility supports the following commands in addition to Transact-SQL statements. Command Description RESET ED !! cmd QUIT EXIT (statement) The following commands all require a colon prefix (:) Command Description :ServerList :List :Listvar :Error filename | STDOUT | STDERR :Out filename | STDOUT | STDERR :Connect server[\instance] [timeout] [user_name[password]] :On Error [exit | ignore] :SetVar variable value :Help 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 -L[c] This switch cannot be used in conjunction with other switches. -U login_id [-P 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 -S server_name[\instance_name] -H wksta_name -d db_name -l time_out -t time_out -h headers -s col_separator -w column_width -a packet_size -e -I -c cmd_end -q “query” -Q “query” -m error_level -r [0 | 1] -i input_file[,file2…] -o output_file -p[1] -b -u -R -v var=”value”[var="value"…] -A -X[1] -V severitylevel
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 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 Using a text editor, create a text file containing the commands and statements you want to execute. Note Tip 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 Create a sqlcmd script. Reference values that will be passed in using the following syntax: $(variable) SELECT $(colname)FROM $(tabname)GO sqlcmd –i MyScript.sql –o MyScript.out –v colname=”name” tabname=”sys.databases” 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 Invoke the sqlcmd utility specifying the -A switch: SQLCMD –A 1>SHUTDOWN WITH NOWAIT2>GOServer shut down by request
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 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 SMO and WMI SMO Scripting 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 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 IsClustered IsSingleUser Language NetName OSVersion Parent PhysicalMemory Platform Processors Product ProductLevel VersionString 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 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 BackupSetDescription BackupSetName BlockSize Checksum Database DatabaseFileGroups DatabaseFiles Devices DeviceType ExpirationDate FormatMedia Incremental Initialize LogTruncation MediaDescription MediaName NoRewind Restart RetainDays SkipTapeHeader UnloadTapeAfter
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 Click an item to view more information. 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. 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 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 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 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 […,n] ]{ FOR | AFTER } { event_type | event_group ] [ ,…n]AS { sql_statement [ …n] | EXTERNAL NAME < method specifier > }[ ; ] 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 }[ ; ] Important 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 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) View the events captured in the audit log. SELECT *FROM dbo.AuditLogGO 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 Capturing events 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 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. 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 Processing messages 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 Removing an event notification For more information on the DROP EVENT NOTIFICATION command, see the Transact-SQL Reference in SQL Server 2005 Books Online. Note 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 Creating a Service CREATE SERVICE NotifyService Creating a Route CREATE ROUTE NotifyRoute Creating an event notification that fires when a CREATE TABLE operation is performed on the AdventureWorks database CREATE EVENT NOTIFICATION Notify_Create_Table Processing messages and showing the contents of the AuditLog table DECLARE @messageTypeName NVARCHAR(256), @messageBody XML; 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 Querying dynamic management views SELECT * FROM sys.dm_tran_locks Dynamic management viewsys.dm_db_partition_stats sys.dm_exec_sessions sys.dm_io_pending_io_requests sys.dm_os_memory_pools sys.dm_os_threads sys.dm_broker_queue_monitors sys.dm_tran_locks 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 USE masterSELECT * FROM sys.databases Querying Dynamic Management Views 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 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 This code lists the tables in the current database. USE AdventureWorksSELECT * FROM sys.tables This code lists the columns from all the tables in the current database. USE AdventureWorksSELECT * FROM sys.columns 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’) This code lists the registered assemblies in the database. USE AdventureWorksSELECT * FROM sys.assemblies This code lists the registered events in the database. USE AdventureWorksSELECT * FROM sys.events 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 This code lists the table partitions in the database. USE AdventureWorksSELECT * FROM sys.dm_db_partition_stats USE AdventureWorks 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 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 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.
Step 1 : Create the DDL trigger. Start SQL Server Management Studio and connect to the localhost server using Windows Authentication. SET @data = eventdata() Add the following statements to the body of the trigger: Execute the code to create the trigger. Result: Step 2 : Test the DDL trigger. 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. Result: Step 3 : Drop the DDL trigger and Production.ProductCopy table Delete the CheckDropTable trigger from the Programmability\Database Triggers folder in Object Explorer. Result: Task 2 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. 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. Create the EventLogProc stored procedure, as shown here: Result: Step 2 : Create the EventLogQueue queue, EventLogService service, EventLogRoute route, and EventLogNotify event notification. 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: Result: Step 3 : Test the EventLogNotify event notification. Use the following commands to create and drop a new table: Use the following command to query the EventLog table: Result: Step 4 : Remove the event notification objects. Drop the EventLogNotification event notification. Result: |
erftrace filename | STDOUT | STDERR