SQL Server Magazine

The SCHEMA setting determines whether SQL Server returns the XSD schema for the method with the resultset (SCHEMA=STANDARD) or not (SCHEMA=NONE). You don't have to request schema for Web methods mapped to scalar UDFs and stored procedures that use FOR XML. You have to request schema when executing a stored procedure or a T-SQL batch that returns a tabular resultset. If you want schema with the FOR XML type of procedure, you need to specify XMLSCHEMA in the SELECT statement. If you don't request schema when calling an endpoint Web method mapped to a FOR XML stored procedure, you'll still be able to load the resultset into an XmlElement object.

This process is a little different if you want to work with a DataSet object. If you specify SCHEMA=NONE, the Web service returns a dataset, and without a schema, you'll get an error in your .NET code when you try to access a DataTable object in the returned dataset.

The last Web method option I'll mention is FORMAT. The default setting is ALL_RESULTS, for which SQL Server returns an array of objects containing a result set (either a dataset or an XML element) plus a few additional objects such as row count, SQL Server errors and warnings, stored procedure output parameters, and results of PRINT statements. If you don't want all that extra stuff, you can optimize the Web method by specifying FORMAT = ROWSETS_ONLY and you will get back just the result set.

Now, let's create a stored procedure and expose it as a Web method. For my example, I wanted to create something that could be used in the real world. One area in which I expect Native XML Web Services to be used a lot is remote database monitoring. Monitoring SQL Server installations behind firewalls isn't easy. But if you expose some monitoring and management stored procedures as Web services, you should be able to connect much more easily through HTTP, as long as your firewall routes the HTTP traffic to your SQL Server.

Listing 2 shows the code that creates a stored procedure that returns a list of indexes in which fragmentation is greater than the specified parameter. The stored procedure uses the new dynamic management function sys.dm_db_index_physical_stats. (Note that this function is the SQL Server 2005 replacement for the DBCC SHOWCONTIG command.) The next step is to run the code in Listing 3 to create an endpoint. When you run the script, SQL Server creates an endpoint called DBAdministration. This endpoint will have the GetFragmentedIndexes method mapped to a stored procedure of the same name in the AdventureWorks sample database.

Once you create the endpoint, you can start creating a client application in Visual Studio. The first step is to right-click the project and select Add Web Reference. Next, type the URL for WSDL as specified when you created the endpoint. On my computer, the URL is http://rehakr/sql/dba?wsdl, as you can see in Figure 1. Click Add Reference, and you're ready to start coding. First, you create an instance of the Web service and set credentials. In this case, I use the credentials of the logged-on user:

DBAdministration ws = new  
   DBAdministration();
ws.Credentials = System. 
   Net.CredentialCache.
   DefaultCredentials;

Next, call the Web service. The return object is an array of objects:

object[] results = ws.Get
   FragmentedIndexes(int.
   Parse(txtFragPercent.
   Text));

Because the return object contains many types of objects, you need to loop through the array and find the type of object you're looking for. To get the resultset, we need to search for a DataSet object:

DataSet ds = null;
for (int i = 0; i <
    results. Length; i++)
{
  object result = 
     results[i];
  if (result.ToString() == 
     "System.Data.DataSet")
     {    
  ds = (System.Data.DataSet)
     results[i];    
  break;
      }
  }

As you can see, you need to do some extra work to get the data from the return object. Because this is the same code you'll have to use in every client application, I recommend that you create a helper object that implements methods such as GetDataSet() or GetXmlElement() to minimize coding the same plumbing over and over. Please consult SQL Server 2005 Books Online (BOL) to get the complete list of possible objects returned by an endpoint. Figure 2 shows a grid displaying the results of calling GetFragmentedIndexes(80).

Security Recommendations and Best Practices
The most basic security advice I can give you is to stick to the defaults as much as you can because the design of Native XML Web Services is heavily security focused. Use the default Windows-only authentication, keep T-SQL batches disabled, use SSL, and utilize Kerberos for HTTP authentication.

Although Native XML Web Services offers new options for data access, you need to be careful about when you consider using them. The most important thing you should know is that the overhead of making a SOAP call is much higher than using ADO.NET. In my own benchmark, I could see that stored procedures with minimum duration time (1ms to 2ms) executed about 15–20 times slower. As the length of execution time increases, the overhead becomes less significant, but my results clearly show that SOAP access is not the best choice on systems that have heavy loads in which you need to process high volumes of short OLTP transactions. SOAP is also not the best choice for returning very large result sets or retrieving binary objects.

Another big concern is scalability. Because an endpoint essentially replaces your middle tier, it can become a bottleneck. Unlike when you use a Web farm with .NET Web services as a middle tier, you can't easily scale out a SOAP endpoint because it's tied to a single instance of SQL Server.

However, it can make sense to use this feature in heterogeneous environments in which you need to expose your data easily to non-Microsoft clients, as long as your SQL Server is expected to handle only a slow to medium load. Native XML Web Services can also be useful if you need Web-service functionality without having to use IIS, if you require zero-footprint for data access on your clients, and if your applications don't require the use of a middle-tier layer. I also already showed another useful scenario, remote database monitoring over HTTP without using IIS. You shouldn't blindly use this feature, but when you take into consideration all the performance, security, and scalability implications, it can be a useful part of your enterprise architecture.

End of Article

Prev. page     1 [2]     next page -->