New and Enhanced Database Engine Features Part 1 of 5 - SQL Server 2005

(1)  Microsoft .NET Framework: Common Language Runtime Integration

The Microsoft .NET Framework common language runtime (CLR) is now hosted in the SQL Server Database Engine. This CLR integration environment supports procedural database objects, including functions, stored procedures, and triggers, that are written in .NET Framework-based languages such as Microsoft Visual C# and Visual Basic .NET.

(2)  Microsoft .NET Framework: CLR User-Defined Aggregates

(3)  User-defined aggregate functions written in a .NET Framework-based language extend the basic aggregate functions, such as SUM, MIN, and MAX, included in Transact-SQL. An aggregate function is one that processes several data values and returns a single value.

(4)  Microsoft .NET Framework: User-Defined Types

CLR user-defined types (UDTs) allow you to implement database data types in any of the languages supported by the .NET Framework CLR. CLR user-defined types are implemented as classes in a .NET Framework-based language and can have complex structures and behavior defined by the fields, methods, and properties of the class. CLR user-defined types are integrated with the Database Engine type system and can be used in all the contexts where the built-in types are used.

(5)  Microsoft .NET Framework: Stored Procedures, Triggers, and User-defined Functions

Database procedural objects can be written in .NET Framework-based languages to incorporate more complex logic than is supported by the Transact-SQL language. Programmers develop the objects using the same language and development environment as they use to develop .NET Framework components and services.

(6)  Web Access: Native HTTP SOAP Access – deprecated in SQL Server 2008 and replaced with Windows Communications Foundation (WCF) or ASP.NET


==========================Extended Reading================================

Native XML Web Services Overview

This topic compares Native XML Web Services in Microsoft SQL Server 2005 to Microsoft SQLXML, describes how native XML Web services works, and lists some benefits from using it.

Native XML Web Services is not useful or recommended for the following scenarios:

·        Applications characterized by real time highly concurrent access, with short duration transactions.

·        Web farm type scale-out.

·        As a replacement for the middle tier, specifically where your application architecture has large-scale business logic demands that are better accommodated within middle-tier components.

Comparing Native XML Web Services to SQLXML

In earlier versions of SQL Server, access to a SQL Server database requires using Tabular Data Stream (TDS). TDS is a proprietary protocol that must be supported for Windows-based desktop clients. Sometimes, SQL Server clients must use Microsoft Data Access Components (MDAC). The MDAC stack is installed on the client computer that connects to SQL Server. For SQL Server, SQLXML 3.0 is a middle-tier component that supports Web-based access to SQL Server, but Internet Information Services (IIS) must also be used.

In SQL Server 2005, by combining the use of HTTP and SOAP, native XML Web services provides an alternative for environments other than Windows, as shown in the following illustration.

Because there is no longer a need for either MDAC installed at the client or for SQLXML with its dependency at the middle-tier on IIS, SOAP and HTTP access enables a broader range of clients to access SQL Server. These include Web application clients that use existing client applications, such as a Web browser. Native XML Web Services makes it easier to work with the Microsoft .NET Framework, Microsoft SOAP Toolkit, Perl, and other Web development operating systems and toolsets.

The following table shows some features that each technology offers.

Native XML Web Services

Microsoft SQLXML

  • A fully compliant SOAP server implementation that can support SOAP 1.1 and SOAP 1.2 clients.
  • Full support for parameterized batch execution.
  • Dynamic WSDL generation at the server.
  • XML template and schema files. These support updatable XML views.
  • Updategrams.
  • XML bulk load.

How Native XML Web Services Works

To use Native XML Web Services in SQL Server, an HTTP endpoint must to be established at the server. This endpoint is essentially the gateway through which HTTP-based clients can query the server. After an HTTP endpoint is established, stored procedures or user-defined functions can be added or made available to endpoint users. This can occur when the endpoint is either created or updated. When procedures and functions are enabled, they are specified as Web methods. A collection of Web methods that are designed to be used together can be called a Web service.

These Web services can be described by using the WSDL format. The WSDL format is generated by an instance of SQL Server and returned to SOAP clients for any HTTP endpoints on which WSDL is enabled, as shown in the following illustration. If required, the WSDL format can be a custom solution instead of one generated by SQL Server. Optionally, the endpoint can be configured to not answer WSDL requests.

Following this process, collections of SQL Server-enabled Web services can be implemented and used to help build and populate a Service-Oriented Architecture (SOA). 


In short, in SQL Server 2005, SQL Server provides native XML Web Services through the SQL Server Database Engine by using the following open standards:

·        Hypertext Transfer Protocol (HTTP) - As the core protocol behind the World Wide Web, HTTP provides a platform-neutral Web-based exchange of data.

·        SOAP - SOAP defines how to use XML and HTTP to access services, objects, and servers regardless of the operating system.

·        Web Services Definition Language (WSDL) - WSDL is an XML document format that can be used to describe Web-based services.


When you use Native XML Web Services in SQL Server 2005 or later, you can send SOAP messaging requests to an instance of SQL Server over HTTP to run the following:

·        Transact-SQL batch statements, with or without parameters.

·        Stored procedures, extended stored procedures, and scalar-valued user-defined functions.


(7)  Transact-SQL Enhancements: New xml Data Type

XML data can be stored in columns, stored procedure parameters, or variables created using the xml type.

(8)  Transact-SQL Enhancements: FOR XML Enhancements (RAW, AUTO, EXPLICIT, PATH)

·        TYPE Directive in FOR XML Queries 

·        RAW Mode Enhancements 

·        AUTO Mode Enhancements 

·        EXPLICIT Mode Enhancements 

·        Nested FOR XML Queries 

·        Generating Elements for NULL Values Using the XSINIL Parameter 

·        FOR XML Support for Various SQL Server Data Types 

·        Inline XSD Schema Generation 

·        Change in Inline XDR Schema Generation 

(9)  Transact-SQL Enhancements: Improved Error Handling - TRY-CATCH.

(10)        Transact-SQL Enhancements: New Metadata Views - System catalog views and DMVs

(11)        Transact-SQL Enhancements: Ranking Functions

·        RANK

·        DENSE_RANK

·        NTILE

·        ROW_NUMBER

(12)        Transact-SQL Enhancements: Data Definition Language (DDL) Triggers

(13)        Transact-SQL Enhancements: Event Notifications – still in SQL Server 2016

Event notifications execute in response to a variety of Transact-SQL data definition language (DDL) statements and SQL Trace events by sending information about these events to a Service Broker service.


Event notifications can be used to do the following:

·        Log and review changes or activity occurring on the database.

·        Perform an action in response to an event in an asynchronous instead of synchronous manner.

Event notifications can offer a programming alternative to DDL triggers and SQL Trace.


Benefits of event notifications: Event notifications run asynchronously, outside the scope of a transaction. Therefore, unlike DDL triggers, event notifications can be used inside a database application to respond to events without using any resources defined by the immediate transaction.


Unlike SQL Trace, event notifications can be used to perform an action inside an instance of SQL Server in response to a SQL Trace event.


When an event notification is created, one or more Service Broker conversations between an instance of SQL Server and the target service you specify are opened. The conversations typically remain open as long as the event notification exists as an object on the server instance. In some error cases the conversations can close before the event notification is dropped. These conversations are never shared between event notifications. Every event notification has its own exclusive conversations. Ending a conversation explicitly prevents the target service from receiving more messages, and the conversation will not reopen the next time the event notification fires.


Event information is delivered to the Service Broker as a variable of type xml that provides information about when an event occurs, about the database object affected, the Transact-SQL batch statement involved, and other information


USE AdventureWorks2008R2;








(14)        Transact-SQL Enhancements: Queue Processing Extensions

Three Transact-SQL enhancements simplify building message queuing applications in loosely coupled, reliable, Internet-scale and business process automation systems. The INSERT, UPDATE, and DELETE now support an OUTPUT clause that return data based on the rows modified by the statement. READPAST can now be specified on UPDATE and DELETE statements, letting these statements skip queue rows held by other tasks and picking the next available row from a queue.


Question 1: Why three, it should be two: OUTPUT and READPAST

Question 2: How does OUTPUT fit the picture of queuing is not very clearly explained.


(15)        Transact-SQL Enhancements: Unified Large Object Programming Model

In earlier versions of SQL Server, large strings over 8,000 bytes had to be stored in the textntext, and image data types which had a different programming model than the data types for shorter strings (charncharvarcharnvarcharbinary, and varbinary). The Database Engine now supports a MAX length specification for the shorter data types. When MAX is specified, the data types can store the same size strings as textntext, and image values up to 2 gigabytes (GB), but are processed in the same way as when they store shorter strings.


(16)        Transact-SQL Enhancements: Recursive Queries

A Common Table Expressions (CTE) is a results set that is stored temporarily during the execution of a SELECT, INSERT, UPDATE, or DELETE statement. Using a CTE allows the use of recursive queries and can simplify logic by replacing the use of temporary tables or views.


(17)        Transact-SQL Enhancements: New APPLY Operator

The APPLY operator supports invoking a table-valued expression (a table-valued function or subquery) for each row returned by an outer table reference. The table-valued expression is evaluated for each row in the outer table reference.


A UDF can be joined with other tables, but not by simple joins. They have special joins called APPLY operator. An APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.


– CROSS APPLY acts as INNER JOIN, returns only rows from the outer table that produce a result set from the table-valued function.

– OUTER APPLY acts as OUTER JOIN, returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.


--Create Employees table and insert values.



  empid   int         NOT NULL,

  mgrid   int         NULL,

  empname varchar(25) NOT NULL,

  salary  money       NOT NULL,





INSERT INTO Employees VALUES(1 , NULL, 'Nancy'   , $10000.00)

INSERT INTO Employees VALUES(2 , 1   , 'Andrew'  , $5000.00)

INSERT INTO Employees VALUES(3 , 1   , 'Janet'   , $5000.00)

INSERT INTO Employees VALUES(4 , 1   , 'Margaret', $5000.00)

INSERT INTO Employees VALUES(5 , 2   , 'Steven'  , $2500.00)

INSERT INTO Employees VALUES(6 , 2   , 'Michael' , $2500.00)

INSERT INTO Employees VALUES(7 , 3   , 'Robert'  , $2500.00)

INSERT INTO Employees VALUES(8 , 3   , 'Laura'   , $2500.00)

INSERT INTO Employees VALUES(9 , 3   , 'Ann'     , $2500.00)

INSERT INTO Employees VALUES(10, 4   , 'Ina'     , $2500.00)

INSERT INTO Employees VALUES(11, 7   , 'David'   , $2000.00)

INSERT INTO Employees VALUES(12, 7   , 'Ron'     , $2000.00)

INSERT INTO Employees VALUES(13, 7   , 'Dan'     , $2000.00)

INSERT INTO Employees VALUES(14, 11  , 'James'   , $1500.00)



--Create Departments table and insert values.

CREATE TABLE Departments



  deptname  VARCHAR(25) NOT NULL,

  deptmgrid INT NULL REFERENCES Employees



INSERT INTO Departments VALUES(1, 'HR',           2)

INSERT INTO Departments VALUES(2, 'Marketing',    7)

INSERT INTO Departments VALUES(3, 'Finance',      8)

INSERT INTO Departments VALUES(4, 'R&D',          9)

INSERT INTO Departments VALUES(5, 'Training',     4)

INSERT INTO Departments VALUES(6, 'Gardening', NULL)




  empid   INT NOT NULL,

  empname VARCHAR(25) NOT NULL,

  mgrid   INT NULL,

  lvl     INT NOT NULL




  WITH Employees_Subtree(empid, empname, mgrid, lvl)



    -- Anchor Member (AM)

    SELECT empid, empname, mgrid, 0

    FROM Employees

    WHERE empid = @empid


    UNION all


    -- Recursive Member (RM)

    SELECT e.empid, e.empname, e.mgrid, es.lvl+1

    FROM Employees AS e

      JOIN Employees_Subtree AS es

        ON e.mgrid = es.empid



    SELECT * FROM Employees_Subtree







deptid      deptname    deptmgrid   empid empname      mgrid lvl

1           HR          2           2     Andrew      1      0

1           HR          2           5     Steven      2      1

1           HR          2           6     Michael     2      1

2           Marketing   7           7     Robert      3      0

2           Marketing   7           11    David       7      1

2           Marketing   7           12    Ron         7      1

2           Marketing   7           13    Dan         7      1

2           Marketing   7           14    James       11      2

3           Finance     8           8     Laura       3      0

4           R&D         9           9     Ann         3      0

5           Training    4           4     Margaret    1      0

5           Training    4           10    Ina         4      1



FROM Departments AS D

  CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST



deptid      deptname    deptmgrid   empid empname      mgrid lvl

1           HR          2           2     Andrew      1      0

1           HR          2           5     Steven      2      1

1           HR          2           6     Michael     2      1

2           Marketing   7           7     Robert      3      0

2           Marketing   7           11    David       7      1

2           Marketing   7           12    Ron         7      1

2           Marketing   7           13    Dan         7      1

2           Marketing   7           14    James       11      2

3           Finance     8           8     Laura       3      0

4           R&D         9           9     Ann         3      0

5           Training    4           4     Margaret    1      0

5           Training    4           10    Ina         4      1

6           Gardening   NULL        NULL  NULL        NULL      NULL




FROM Departments AS D

  OUTER APPLY fn_getsubtree(D.deptmgrid) AS ST


(18)        Transact-SQL Enhancements: New PIVOT and UNPIVOT Operators

·        What is PIVOT?

PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

·       T-SQL Pattern for PIVOT:

SELECT <non-pivoted column>,

    [first pivoted column] AS <column name>,

    [second pivoted column] AS <column name>,


    [last pivoted column] AS <column name>


    (<SELECT query that produces the data>)

    AS <alias for the source query>



    <aggregation function>(<column being aggregated>)


[<column that contains the values that will become column headers>]

    IN ( [first pivoted column], [second pivoted column],

    ... [last pivoted column])

) AS <alias for the pivot table>

<optional ORDER BY clause>

·       Example:

USE AdventureWorks;



SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5


(SELECT PurchaseOrderID, EmployeeID, VendorID

FROM Purchasing.PurchaseOrderHeader) p



COUNT (PurchaseOrderID)

FOR EmployeeID IN

( [164], [198], [223], [231], [233] )

) AS pvt


·        What is UNPIVOT?

UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

·        Data sample:

o   Original Pivoted Table

VendorID    Emp1  Emp2  Emp3  Emp4  Emp5

1           4     3     5     4     4

2           4     1     5     5     5

3           4     3     5     4     4

4           4     2     5     5     4

5           5     1     5     5     5

o   Desired unpivoted table

VendorID    Employee    Orders

1           Emp1        4

1           Emp2        3

1           Emp3        5

1           Emp4        4

1           Emp5        4

2           Emp1        4

2           Emp2        1


5           Emp5        5


·       T-SQL Pattern for UNPIVOT:

SELECT <non-pivoted column>,

    [Unpivoting the pivoted column] AS <column name>,

    [Aggregate] AS <column name>,





    Aggregation FOR

Collapsed column from the pivoted columns

    IN ( [first pivoted column], [second pivoted column],

    ... [last pivoted column])

) AS <alias for the unpivot table>

<optional ORDER BY clause>


·       Example:

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,

    Emp3 int, Emp4 int, Emp5 int);


INSERT INTO pvt VALUES (1,4,3,5,4,4);

INSERT INTO pvt VALUES (2,4,1,5,5,5);

INSERT INTO pvt VALUES (3,4,3,5,4,4);

INSERT INTO pvt VALUES (4,4,2,5,5,4);

INSERT INTO pvt VALUES (5,5,1,5,5,5);


--Unpivot the table.

SELECT VendorID, Employee, Orders




   (Orders FOR Employee IN

      (Emp1, Emp2, Emp3, Emp4, Emp5)

)AS unpvt;


(19)        Transact-SQL Enhancements: Query Notifications

Query notifications allow an application to cache a result set and request the Database Engine to notify it if any of the underlying data is modified by another application. Applications using query notifications do not have to periodically verify the state of the data in the database. They only have to validate the data if the Database Engine notifies them of a change.


Query notifications are a new feature available in SQL Server 2005 and SQL Native Client. Built upon the Service Broker infrastructure introduced in SQL Server 2005, query notifications allow applications to be notified when data has changed. This feature is particularly useful for applications that provide a cache of information from a database, such as a Web application, and need to be notified when the source data is changed.


Microsoft SQL Server 2005 introduces query notifications, new functionality that allows an application to request a notification from SQL Server when the results of a query change. Query notifications allow programmers to design applications that query the database only when there is a change to information that the application has previously retrieved.


The Database Engine uses Service Broker to deliver notification messages. Therefore, Service Broker must be active in the database where the application requests the subscription. The query notification functionality does not require or use Notification Services. Query notifications are independent of event notifications!!!


 Note 1: Event notifications execute in response to a variety of Transact-SQL data definition language (DDL) statements and SQL Trace events by sending information about these events to a Service Broker service.


Note 2: Notification Services is a programming framework for creating applications that generate and send notifications, as well as a platform for hosting those applications. Using the programming framework, you can quickly create applications to generate and send notifications to subscribers. After creating the application, you can deploy the application on the Notification Services platform.


The syntax of the query notifications options string is:


service=<service-name>[;(local database=<database> | broker instance=<broker instance>)]


For example:

service=mySSBService;local database=mydb


SQL Native Client applications typically receive notifications by using the Transact-SQL RECEIVE command to read notifications from the queue associated with the service specified in the notification options.


To use query notifications a queue and a service must exist on the server. These can be created using Transact-SQL similar to the following:



CREATE SERVICE myService ON QUEUE myQueue ([])


The service must use the predefined contract as shown above.


For more, see


(20)        Transact-SQL Enhancements: Bulk Operations on OPENROWSET

OPENROWSET now supports a BULK operation for bulk copying data from data files. OPENROWSET bulk operations are controlled using new table hints and bulk options, such as BULK_BATCHSIZE and FORMATFILE. The new bulk_column_alias clause supports assigning column names to bulk operation data.


Uses the BULK rowset provider for OPENROWSET to read data from a file. In SQL Server 2005, OPENROWSET can read from a data file without loading the data into a target table. This lets you use OPENROWSET with a simple SELECT statement.

The arguments of the BULK option allow for significant control over where to start and end reading data, how to deal with errors, and how data is interpreted. For example, you can specify that the data file be read as a single-row, single-column rowset of type varbinary, varchar, or nvarchar. The default behavior is described in the argument descriptions that follow.


For more information, see OPENROWSET (Transact-SQL)Table Hint (Transact-SQL).


( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password' 
   | 'provider_string' } 
      , {   [ catalog. ] [ schema. ] object 
       | 'query' 
   | BULK 'data_file' , 
       { FORMATFILE = 'format_file_path' [ <bulk_options> ]
} ) 
<bulk_options> ::=
   [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] 
   [ , ERRORFILE = 'file_name' ]
   [ , FIRSTROW = first_row ] 
   [ , LASTROW = last_row ] 
   [ , MAXERRORS = maximum_errors ] 
   [ , ROWS_PER_BATCH = rows_per_batch ]


      FileName nvarchar(60),

      FileType nvarchar(60),

      Document varbinary(max)




INSERT INTO myTable(FileName, FileType, Document)

SELECT 'Text1.txt' AS FileName,

       '.txt' AS FileType,

       * FROM OPENROWSET(BULK N'd:\Text1.txt', SINGLE_BLOB) AS Document



SELECT * from MyTable


--Example 2 - Using OPENROWSET to bulk insert file data with a format file


The following example uses a format file to retrieve rows from a tab-delimited text file, values.txt that contains the following data:

1     Data Item 1

2     Data Item 2

3     Data Item 3


The format file, values.fmt, describes the columns in values.txt:



1  SQLCHAR  0  10 "\t"        1  ID             SQL_Latin1_General_Cp437_BIN

2  SQLCHAR  0  40 "\r\n"      2  Description     SQL_Latin1_General_Cp437_BIN



SELECT a.* FROM OPENROWSET( BULK 'c:\test\values.txt',

   FORMATFILE = 'c:\test\values.fmt') AS a;


(21)        Transact-SQL Enhancements: TOP Enhancements

The TOP operator has been enhanced to take any numeric expression (such as a variable name) instead of only an integer number to specify the number of rows returned by the operator. TOP can also now be specified in INSERT, UPDATE, and DELETE statements.


(22)        Transact-SQL Enhancements: Distributed Query

The EXECUTE statement now supports an AT LinkedServer clause that specifies executing a stored procedure on a linked server. The CONTAINS full-text predicate supports four-part names to execute queries against linked servers. Distributed queries also support the new large object data types nvarchar(max),varchar(max), and varbinary(max). New SQL Trace events have been added to help analyze the interaction of distributed queries and OLE DB providers.

/* Using EXECUTE with AT linked_server_name

The following example passes a command string to a remote server.

It creates a linked server SeattleSales that points to another instance of

SQL Server and executes a DDL statement (CREATE TABLE) against that linked server.


EXEC sp_addlinkedserver 'SeattleSales', 'SQL Server'


EXECUTE ( 'CREATE TABLE AdventureWorks.dbo.SalesTbl

(SalesID int, SalesName varchar(10)) ; ' ) AT SeattleSales;



(23)        Transact-SQL Enhancements: TABLESAMPLE

The new TABLESAMPLE clause limits the number of rows processed by a query. Unlike TOP, which returns only the first rows from a result set, TABLESAMPLE returns rows selected from throughout the set of rows processed by the query.


/* Example 1 - Selecting a percentage of rows

The Person.Contact table contains 19,972 rows. The following example returns approximately 10 percent of the rows. The number of rows returned usually changes every time that the statement is executed.


USE AdventureWorks ;


SELECT FirstName, LastName

FROM Person.Contact



/* Example 2 - Selecting a percentage of rows with a seed value

The following example returns the same set of rows every time that it is executed. The seed value of 205 was chosen arbitrarily.


USE AdventureWorks ;


SELECT FirstName, LastName

FROM Person.Contact


   REPEATABLE (205) ;


/* Example 3 - Selecting a number of rows

The following example returns approximately 100 rows. The actual number of rows that are returned can vary significantly. If you specify a small number, such as 5, you might not receive results in the sample.


USE AdventureWorks ;


SELECT FirstName, LastName

FROM Person.Contact



(24)        Transact-SQL Enhancements: New CASCADE Integrity Constraints

The REFERENCES clause now supports the SET NULL and SET DEFAULT cascading referential integrity actions. SET NULL specifies that cascading referential integrity actions set foreign keys to NULL. SET DEFAULT specifies that foreign keys are set to the default value defined for the column.



(25)        Transact-SQL Enhancements: Overflow Data Can Exceed Page Size

Rows can exceed the limit of 8,060 bytes if they contain variable-length columns using the nvarcharvarcharvarbinary, and sql_variant data types. If the combined widths of these variable-length columns cause the row to exceed the page size limit, the Database Engine may move data from variable-length columns to separate pages.


(26)        Database Administration Programming: New SQL Server Management Objects (SMO) API

The SMO object model extends and supersedes the Distributed Management Objects (DMO) object model. The SMO object model provides classes for configuring and managing instances of the Database Engine. The Replication Management Objects (RMO) object model complements the SMO object model, providing classes for configuring and managing replication.

SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server. It Provides information about programming the SMO objects in the namespaces. You can use SMO to create databases, perform backups, create jobs, configure SQL Server, assign permissions, and to perform many other administrative tasks, etc.

·        Microsoft.SqlServer.Management.NotificationServices,

·        Microsoft.SqlServer.Management.Smo,

·        Microsoft.SqlServer.Management.Smo.Agent,

·        Microsoft.SqlServer.Management.Smo.Broker,

·        Microsoft.SqlServer.Management.Smo.Mail, Microsoft.SqlServer.Management.Smo.RegisteredServers, Microsoft.SqlServer.Management.Smo.Wmi,

·        Microsoft.SqlServer.Management.Trace


SQL Server Replication Management Objects (RMO) is a collection of objects that encapsulates SQL Server replication management. It provides information about programming the RMO objects in the Microsoft.SqlServer.Replication namespace.


(27)        Database Administration Programming: New SQL Server WMI Providers

The WMI Provider for Computer Management is an instance provider. The purpose of this layer is to provide a unified way for interfacing with the API calls that manage the registry operations requested by the Server and Client network utility functionality and that provide enhanced control and manipulation over the selected SQL services of the Computer Manager snap-in component.


The WMI Provider for Server Events enables you to use the Windows Management Instrumentation (WMI) to monitor events in SQL Server. The provider works by turning SQL Server into a managed WMI object. Any event that can generate an event notification in SQL Server can be leveraged by the WMI using this provider. Additionally, as a management application that interacts with the WMI, SQL Server Agent can respond to these events, increasing the scope of events covered by SQL Server Agent over previous releases.