Really Simple SQL Server Syndication (SQLRSS)

This community project provides a place for distributing and developing a server application which exposes changes to SQL Server 2008 database tables as either an RSS or Atom syndication feed.

Introduction

The SQLRSS community application is designed to expose changes made to SQL Server 2008 tables as an RSS or ATOM feed. The application is useful as is. Many enhancements are possible and community contributions to this project are encouraged.

Click on the Releases table and then click on SQLRSS.zip to download the code.

Got questions? Want to contribute? Check out the SQL Server Community & Samples Discussion Forum!

Implementation

This application is based on the following features:
  • Change Tracking (CT) feature of SQL Server 2008
  • Reporting Services
  • The syndication feed feature of WCF
  • LINQ to XML
  • ADO.NET

The use of Reporting Services is optional, but is a handy way to display the changed data.

Visual Studio Projects

There are two principle parts of the application. The first part is the implementation of the WCF based syndication feed service which transmits changes to one or more tables via RSS or ATOM feeds. For the remainder of this document we will refer to the first part of this application as the SQLRSS web service. The second part of the application is a demonstration of how to use the SQLRSS web service to monitor activity on the Production.ProductReview table in the AdventureWorks2008 sample database. The SQLRSS web service is designed to be completely general purpose and can be applied to any SQL Server 2008 database in a server where Change Tracking is enabled. The second part is for illustration purposes only and can be discarded in a production application environment.

The implementation of the SQLRSS web service is located in the SQLRSS solution which is located in the SQLRSS\RSSDataService directory. The project is named RSSDataService. It is the only essential project necessary for production use. The TestServiceHost project in that solution provides the code necessary to host the service in a console application. In an actual production environment this kind of web service would normally be hosted in a Windows Service and some additional code is necessary to enable that particular scenario. The other project in the SQLRSS solution is TestClientApplication which is a very simple application for invoking the SQLRSS web service and viewing the results in a console application. Outlook 2007 or Internet Explorer 7 would make more interesting and useful client applications for browsing this feed.

In the SQLRSS\ReportProject1 directory is a solution called ProductReviewReports which contains a sample reporting services report. This report illustrates how syndication feed items can link to a Reporting Services report. This solution would not be deployed in a production environment, although something similar to it might be useful.

Prerequisites

In order to use the SQLRSS web service the following components need to be installed and available:
  • The SQLRSS source code. Click on the Releases table and then click on SQLRSS.zip to download the code.
  • Microsoft.Net 3.5
  • Microsoft SQL Server 2008 RTM
  • Either Visual Studio 2008 SP1 or the Microsoft.Net Framework SDK 3.5
  • A SQL Server 2008 database which contains at least one table to be tracked and published by the web service.

Configuration and Installation

The first step in configuring the SQLRSS web service is to enable the Change Tracking feature in the database or databases which contains the table or tables to be tracked and published, and also enable Change Tracking for the table or tables. See MSDN for details. For the samples provided you can use the following statements after installing the AdventureWorks2008 OLTP database from Sample Databases at CodePlex.

ALTER DATABASE AdventureWorks2008
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

USE AdventureWorks2008
GO

ALTER TABLE Production.ProductReview
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = OFF);


There are many options available for Change Tracking and there is no substitute for carefully reading the documentation and understanding the implications of the settings you decide to use.

The second step in configuring the SQLRSS web service is to create an XML file which complies with the SQLRSS\RSSDataService\FeedMap.xsd schema. There are extensive comments in the schema file which explain the meaning of the elements and attributes of the schema. There is also an example of what such an XML file would look like in SQLRSS\RSSDataService\FeedMap.xml.

The third step is to create a host for SQLRSS web service and pass the path to the XML file you created in step two to the service during its construction. The TestServiceHost project in the SQLRSS solution provides an example of how to do that for a console application.

Once the service has been launched in the desired host, changes since Change Tracking was enabled for tables described in the XML configuration file will be available as syndication feeds based on the conditions and configuration information contained in the XML configuration file.

If you start the test host with the provided configuration file then the RSS feed will be located at http://localhost/SQLRSS/GetDataChanges?rssFormat=atom&connection=local&table=Production.ProductReview.

Vista UAC Notes

Some aspects of this sample code will not run correctly on Vista with UAC enabled unless you take the following steps:

Unimplemented Configuration Options

The XML schema describes some features of the application which are not yet implemented. Here is a list of the current restrinctions:
  • DataViewSource and DataViewSourceMode are not yet implemented. The data source is always the table being monitored, combined with whatever reference tables you provide.
  • Only Report is implemented for DataDisplayMode. HTML and XML modes are not yet implemented.
  • Only the Top ChangeFilterMode is implemented. TimeInterval is not yet implemented.

If there is sufficient interest these options could be implemented in a future release.

Areas for Future Expansion

There are many possible ways to expand the breadth or depth of the sample. Here are some ideas:
  • Support for images in syndication feed items
  • Example code for packaging and creating a Windows Service to run the SQLRSS web service, or for running SQLRSS under IIS7.
  • Completing the unimplemented configuration options listed above.
  • Building interesting web site templates using this technology, such as a blog web site template.

Both users who are interested in using this technology and developers interested in extending this technology are welcome to create items in the Discussion and Work Item areas of CodePlex for this project and begin the discussion of how this software should grow and evolve. If you are interested in contributing code to this project please contact the owner of this project at CodePlex.com and send along your interests in expanding it and some background information about your skills and projects you’ve worked on in the past.

Last edited Feb 11, 2009 at 10:15 PM by bonniefe, version 7