orbifold think. visualize. understand.

SSRS, SSIS, SSNS…SQL Server as an application platform

SQL 6.5 nostalgyIf you're an old-timer like me you remember the days when SQL Server was still a database and less a platform. In moving from v6.5 (released in 1996) to v7.0 Microsoft upgraded the engine and the totally ugly Windows 3.1 interface, they deviated from the original Sybase code (every MS product starts with an acquisition...) and made an RDBMS manageable. That is, you did not need to study for years in order to setup a database server or to backup a database as was the case with Oracle. When they shifted to SQL2000 a lot was going on in the IT world; XML came up, the millenium problems, the globalization and multitude of data types, the internet and web applications rather than win application were connecting to the data. Microsoft launched various tools and addons to the biotope which eventually got integrated into SQL2005; notification service, reporting server and so on. The XML format became just another data type and they integrated the CLR into the core, which was and still is a real benefit in comparison to other vendors. I am not so sure many developers and DB admins use CLR data types and .Net function in SQL but anyway, the question now has become how to use all this beauty? When do you use SQL Server Integration Services (SSIS) rather than  a custom made NT service? Why would one use the Notification Services (SSNS) when in fact you can Email from Reporting Services (SSRS) or include mail functions in your stored procedures? By the way, what happened to English query...according to an official statement:
English Query was discontinued.. due to lack of interest or an inability to provide accurate translations. But as research into natural language processing continues, we may see it resurface again in another product by another company.
Concerning SSNS my point of view is simple: the product is horribly complicated to use, both as a developer and as a DB admin. The lack of UI to design the actual service is unforgivable and creating in by hand (i.e. editing a big XML file) is error-prone. While it my be true that the service is more scalable than, say, a custom Windows service in practice I have not found the need to use the SSNS technology in any project. In fact, it seems Microsoft concluded that SSNS was not a success and in SQL Server 2008 (from Tibor Karaszi):
SQL Server Notification Services will not be included as a component of SQL Server 2008, but will continue to be supported as part of the SQL Server 2005 product support life-cycle. Moving forward, support for key notification scenarios will be incorporated into SQL Server Reporting Services. Existing Reporting Services functionality, such as data driven subscriptions, addresses some of the notification requirements. Features to support additional notification scenarios may be expected in future releases.
And so the message is: forget about SSNS and focus on custom .Net solutions or use reporting services as a notification system. Admitted, in comparison to SSNS the Reporting service is indeed a nice and useful piece of technology which was extended in SQL2K8. In Materialise we extensively use SSRS to mail project managers (I'd almost say, spam them) and partners about development progress and order status. The fact, in particular, that one can attach Excel sheets to Emails is very much appreciated; PM's and decision makers as you know just love spreadsheets ;) A typical request, however, is to have reports and Word documents created on the basis of in-house templates, something which has been improved in SQL2K8. Another piece of technology that non-geeks love is the online report builder which allows you to build reports and create queries without much knowledge of technology or the SQL language. This, together with the subscription system, covers a wide range of reporting requirements you find in every company and so, yes, my advice is to invest time and energy in embedding SSRS in your own business. In SQL2K one had the DTS (Data Transformation System) which boasted a visual designer and a whole world on its own. Both in trainings I gave and in various companies I was always surprised that people were unaware of this wonderful piece of software or worse, they found the whole machinery confusing. To some a visualization is a blessing and to others it's confusing and they rather prefer command-line interfaces (the Un*x guys, you know). In any case, Microsoft took DTS to a new level in SQL2K5 and created out of it the Integration Service (SSIS). The new level being the shift to a whole development environment inside the Visual Studio shell, the integration with .Net (which in SQL2K8 is very complete) and a better aministration of existing packages. However, much like SSNS there is a rather big learning curve here. Before being prolific in SSIS you'll have to dig into many intricacies and techniques which on the long run is a benefit but you might need to put a dedicated developer on it from quite some time. My experience is that a typical dev will take many weeks before he/she has control over the development and deployment process. Hence, the advice here is; consider SSIS as a long term investment in your enterprise and fix the criteria when to use SSIS or when to use a custom made solution. By the latter statement I mean the typical question: when to use SSIS and when to use Workflow Foundations (WF)?  Indeed, both technologies have much in common. My list consists of the following:
  • WF is independent of SQL Server and as such does not need to be deployed on it. How do you see the deployment of your integration solution?
  • Scheduling is easier inside SSIS than outside with WF but on the other hand, a WF solution is easier to access from an application than SSIS. Especially if you wrap a WF in a WCF service you can trigger a data process from everywhere while this is more tricky with SSIS.
  • The WF solution allows you to create reusable WF activities which might be a good thing on the long term while the SSIS system somewhat restricts you to the box.
Of course, the question prior to this might be as well: if you need data integrations maybe your data storage and proliferation of data sources is the problem you need to tackle rather than comlexifying the data flow even more...? As much as normalization problems inside a database cannot be solved by using triggers, one shouldn't use SSIS to solve the bad data distribution across a company. Bu yes I know, the world is a complicated place. Finally, concerning the analysis service and the whole OLAP stuff I have little to say. I think the data mining and cube-stuff is a world on its own. If you are in need of OLAP you need specialists and a whole lot of theory before using the analsysis services of SQL Server. Don't assume an easy road and think twice before going there, I've seen companies with big ambitions and wasting heaps of money without any return. In any case, after fifteen years of living with SQL Server in the back of my mind I can honestly say that the system is really fun and amazingly stable. In fact, I have seen SQL crash only once in all this time, which is impressive considering it's an MS product! Things got better though more complicated, one could spend a lifetime doing nothing but getter better at understand SQL Server. In particular, tuning and performancing the server is an art and a job on its own. Few people are capable of telling you when and how to use indexes, how to use SQL hints in stored procedures. Even less people can tell you how to use fail-over, replication or balancing systems with SQL Server. But then again, maybe this is the strength of SQL Server; it's easy to use and affordable for the average consumer/developer/admin while still offering an immense richness for the experienced and high-end customer. (Did you notice SQL Server finally has its own logo now?)