http://edge.technet.com/Media/Real-World-DBA-Episode-16-SQL-Server-Features--Web-Services/ 

 

This week:

In the news, the SQL Server 2008 Release Candidate is now available.

In this week’s feature, we’ll continue the introduction to SQL Server Features and talk about SQL Server’s Web Services.

The web link is on SQL Server Replication,

and the tip this week deals with identifiers.

 

In the News:

The SQL Server 2008 release candidate was made available on the web this week. This is a feature-complete version of Microsoft’s flagship database product. Check it out at http://tinyurl.com/6jrc22.

 

Feature:

In a previous Podcast I started an overview of the major features you can find in SQL Server 2005. I’ll cover each feature in more depth in future Podcasts, but we need a place to start the discussion for those features, so we’ll cover the basics first. This week we’ll take a look at the SQL Server’s Web Services. I won’t cover the Service Broker here, since I’ve covered that in another Podcast. I also won’t cover Reporting Services, even though it can be accessed by a web browser, since I’ve covered that in another Podcast as well.

 

SQL Server has a “surface area” that can be accessed across a number of network connection protocols, including HTTP using TCP/IP. TCP/IP assigns a unique number to each device on the network. I won’t cover TCP/IP in depth here, but you can read more about it at http://tinyurl.com/5eb2cw.

 

The important part is that once you reach the machine with a TCP/IP address, the network interface (or more properly the software that controls the network layer) has several numbers associated with various services, called ports. For instance, one of the most well-known ports is 80, which is the default for the HTTP service, which answers web requests for HTML. You can reassign these ports, as long as you stay away from the ones that are pre-assigned. You can read more about those at http://tinyurl.com/hk77. 

 

SQL Server also has a networking layer that responds back to clients. It supports several protocols including TCP/IP. The default instance listens on TCP/IP port 1433, which you can change using the Configuration Manager tool. Named instances of the Database Engine and SQL Server Compact Edition are configured for dynamic ports, which means they select an available port when the SQL Server service is started. The SQL Server Browser service helps clients identify the port when they connect. Note that when configured for dynamic ports, the port used by SQL Server may change each time it is started.  

 

You can use the HTTP Protocol with SQL Server to access Relational and Analysis Services data. For Relational data, you can connect to an Endpoint as I described in the Podcast on the Service Broker, or your web service can return XML using an endpoint.

 

You can use a SOAP call  (or what was originally called the Simple Object Access Protocol) or the ADOMD.NET provider to have SQL Server respond to web requests. ADOMD.NET is a Microsoft .NET Framework data provider that is designed to communicate with Microsoft SQL Server 2005 Analysis Services. ADOMD.NET uses the XML for Analysis protocol to communicate with analytical data sources by using either TCP/IP or HTTP connections to transmit and receive SOAP requests and responses that are compliant with the XML for Analysis specification.

 

Reporting Services can also talk across HTTP to return data, and you can also use either Remote Data Access (RDA) or Replication for Microsoft SQL Server 2005 Compact Edition to connect to SQL Server through a Microsoft Internet Information Services (IIS) server. SQL Server Compact Edition includes the “Configure Web Synchronization Wizard” that lets you configure connectivity for data synchronization between Microsoft SQL Server and SQL Server Compact Edition.

 

Web Link:

The web link for this week is a great SQL Server Replication “crib sheet”. Check it out at http://tinyurl.com/32uq7u.

 

Tip of the Week:

Identifiers, such as table and stored procedure names, depend on the compatibility level on the database. Learn more at http://tinyurl.com/3fugyj.