The series so far:
- The SQL Server Features that Time Forgot: Data Quality Services, Master Data Services, Policy-Based Management, Management Data Warehouse and Service Broker
- More SQL Server Features that Time Forgot: Auto-shrink, Buffer-pool extension, Database Diagrams, Database Engine Tuning Advisor, and SQL CLR
- Even more SQL Server Features that Time forgot: In-Memory OLTP, lightweight pooling, the sql_variant data type, stretch databases, transaction savepoints, and XML indexes
- Still More SQL Server Features that Time Forgot: Active Directory Helper Service, Data Transformation Services, DBCC commands, English Query, Native XML Web Services, Northwind and pubs databases, Notification Services, SQL Mail, SQL Server Distributed Management Objects, Surface Area Configuration Tool, utilities, and Web Assistant
SQL Server has been, if nothing else, an evolving data platform, full of features that come and go like celebrity headlines. With each release, components get added, removed, enhanced, forgotten. Many shine bright and stay that way. Others burn out long before their prime. Some barely see the light of day.
In celebration of SQL Server’s vibrant and colorful history, driven in part by innovation and in part by playing catch-up, we’re launching a series of articles that provide a birds-eye view of various SQL Server components that have been quietly forgotten, have disappeared or should have disappeared. We’ll include those that in one way or another have garnered enough controversy that they’re noteworthy enough to dedicate a few lines.
We’re starting with five features that are still part of the SQL Server package, at least as of the 2016 release. Like many components no longer with us, these five have their fan bases and their critics, those who both love them and hate them, those who turn to them because they have no viable alternatives or because resource limitations dictate their use. Some would argue that the features are under-utilized and under-appreciated. Others wish only for their demise.
Labeling these five features as lost causes is, perhaps, a bit overstated—or premature. Although many might agree, there are likely just as many who would not. For them, the only thing that matters is that a tool work in their favor, no matter how disparaged it might be by the community. And that’s exactly how it should be. Not every organization has a Microsoft-sized budget, and most of us have to make choices based on available time and resources. That’s not to say a feature cannot be improved, but until that happens, we make the best of what we have on hand. With that in mind, let’s get started.
Data Quality Services
We begin with Data Quality Services (DQS), which, as the name suggests, is all about data quality. With DQS, you can build your own knowledge base and then use that knowledge to cleanse and match data. Cleansing is the process of modifying and enriching data that is incorrect or incomplete. Matching refers to identifying and removing semantic duplicates.
You can also retrieve data from a service provider to use as a definitive source for verifying your own data. In addition, you can profile data sources to determine the quality of data, and you can monitor data-cleansing operations.
DQS includes two main components: Data Quality Server and Data Quality Client. Microsoft implements the server as three SQL Server catalogs that you can manage within SQL Server Management Studio (SSMS). The catalogs include the DQS engine, DQS-related stored procedures, published knowledge bases and other data that supports DQS projects.
The client is a stand-alone app that DQS administrators and data stewards can use to carry out data quality operations, without having to be database experts. Microsoft also includes the DQS Cleansing component in SQL Server Integration Services (SSIS) for performing data cleansing as part of a SSIS package. In addition, Microsoft has integrated DQS into Master Data Services (MDS), allowing you to de-duplicate source and master data using the MDS add-in for Excel.
Microsoft includes DQS only with the SQL Server Enterprise and Developer editions and has made no significant updates to DQS since it was introduced in SQL Server 2012.
From the start, DQS has had mixed reviews, and even those who use DQS do not come across as over-the-top evangelists. In fact, Gartner barely takes notice of DQS. In its 2015 Magic Quadrant for Data Quality Tools, DQS is missing from the quadrant altogether and is mentioned later in the report only briefly in a “list of some vendors.”
In general, DQS does not appear to be a highly-implemented feature, although numbers are difficult to come by. That said, it has not been completely abandoned, at least by users. I’m not sure about Microsoft. Perhaps DQS would be more popular if it were to receive greater exposure—or given a thorough make-over. Features relegated to the shadows have a tough time grabbing any limelight, no matter what their quality.
Master Data Services
Microsoft introduced MDS in SQL Server 2008 R2, mostly in reaction to competitive forces. The service provides a structure for managing master data such as customer or product information.
When working with MDS, you organize related data into models and then define entities within those models, similar to how you might create a database and then define tables. You can then add members to the entities, just like you would add data records to a table. You can also define attributes that describe the entity members.
Perhaps the easiest way to think of MDS is as a front-end application that lets you store, organize and manage related data in a SQL Server relational database. You define the model, which is made up a single domain a data, and then add the entities and attributes. You can also define hierarchies, collections (member subsets), business rules, and subscription views. The MDS application automatically generates the necessary database components, such as tables, relationships, constraints, and permissions.
After you define the initial structure, you can then add members to the entities. You start by loading the data into staging tables, where it’s validated against business rules. From there, you can load the data into the main MDS tables.
As with DQS, Microsoft offers MDS only with the SQL Server Enterprise and Developer editions. Unlike DQS, Microsoft has made significant improvements to MDS since its debut. Some might say Microsoft had little choice. Except for a few select company insiders, the first release was pretty much panned by the entire world.
Microsoft acquired the original MDS technology from Stratature in 2007 and did little more than repackage the EDM+ product in order to provide MDS with SQL Server 2008 R2. The service lacked important functionality, performed poorly, was full of bugs, and offered a notoriously clunky UI. Microsoft made a significant number of improvements in SQL Server 2012 and has implemented additional updates since then, but continues to battle the conclusions drawn when the component was first released.
That said, MDS has gained a steady following, even if the numbers are small. The service is still no match for sophisticated master data management products offered by such vendors as IBM and SAP, but MDS has come a long way, and if you’re already invested in SQL Server Enterprise, you might as well give it a try.
Policy-Based Management
Policy-Based Management (PBM) has a PR problem, even worse that DQS and MDS. A lot of people don’t know what it is, or if they do, are not sure if it’s still supported. Microsoft doesn’t even mention the feature in its list of Editions and supported features of SQL Server 2016. The PR problem is so bad, in fact, that I’ve come across community forum postings asking whether PBM still exists.
Despite its silent role, PBM is still a supported feature and appears to be available to all SQL Server editions, although I suspect it caters primarily to organizations with a large number of similar SQL Server implementations.
Microsoft introduced PBM in SQL Server 2008 to provide a policy-based system for managing one or more SQL Server instances. You can, for example, use policies to grant permissions on user databases, disable lightweight pooling, or choose an authentication mode. You can also target policies at specific SQL Server instances, databases, tables, or indexes.
You have to be careful with PBM, however, because you can inadvertently set a policy that has unexpected consequences on other SQL Server features. For example, if you enable a policy that specifies that all tables must have an index, you can impact transactional replication because it uses a table that does not have an index.
When PBM first arrived on scene, it was met with mixed reviews, along with a smattering of exuberance. Many felt that the feature suffered from a number of limitations and did not live up to its promises. Others found it to be useful only in certain circumstances, usually when a third-party alternative wasn’t an option. And still others believed that PBM was one of the most significant features ever added to SQL Server.
Perhaps the biggest concern with PBM is that Microsoft does not appear to have updated it since its initial release. Not only is this an ongoing issue for anyone using the feature, but also for those who had a lousy first impression and are being offered no incentive to give it a second chance. It is possible that Microsoft has been implementing PBM improvements behind the scenes. I just can’t find any evidence of it. If the company has done any updating, they’ve been awfully quiet about it. But as I said, PBM has a PR problem.
Management Data Warehouse
Management Data Warehouse (MDW), another feature introduced in SQL Server 2008, is a relational database that contains system-related data collected from a SQL Server instance. MDW uses the SQL Server data collector to gather performance data about that instance, which is then stored in the warehouse, where you can view the collected data in one of three reports.
The first report, Disk Usage Summary, provides information about disk space usage for the databases on the targeted instance. The second report is Query Statistics History, which provides query execution statistics for the entire data collection period. The third report, Server Activity History, includes resource consumption and server activity data for both the server and the SQL Server instance.
After MDW’s initial rollout, users ran into a number of issues, such as corrupt MDW cache files and objects left behind when trying to uninstall MDW. The built-in reports were also very limited, a fact that persists in SQL Server 2016. However, Microsoft does appear to have made improvements in other areas. For example, SQL Server 2012 added a stored procedure to make it easier to uninstall MDW, although it apparently still leaves objects behind.
Unfortunately, Microsoft gives about as much attention to MDW as it does PBM. For example, MDW is also missing from the list of features even though it made the SQL Server 2016 cut and is available in the Enterprise, Standard, and Developer editions. Also like PBM, MDW still has its supporters, especially among those who don’t have the budget for a proper monitoring tool.
If you plan to test MDW, you might want to try it out in an isolated environment, given that uninstalling this feature still appears to leave remnants behind. For example, you might set up a virtual machine that you can simply blow away after you check out MDW.
Service Broker
Few components in SQL Server inspire as many love/hate relationships as SQL Server Service Broker (SSB), a native asynchronous messaging and queuing service integrated into the database engine. Introduced in SQL Server 2005, SSB makes it easier to implement scalable applications that distribute workloads across multiple databases.
SSB manages communication paths in the context of conversations, while guaranteeing message sequencing and delivery, as well as message persistence in the event of a restart. Its asynchronous nature lets you decouple long running processes from the calling application, while simplifying application development.
Since SSB’s initial release, Microsoft has made several improvements, although no significant changes appear to have occurred since SQL Server 2012. Microsoft includes SSB in the Enterprise, Standard and Developer editions.
Of all the features we’ve discussed so far, SBB is perhaps the most widely implemented, with many believing it is one of the most important components ever to be added to SQL Server. Yet even among its devotees, you’ll hear complaints about SSB being overly engineered and too complicated to set up and failing to include a decent UI.
You’ll also hear a fair amount of moaning about Query Notifications, a feature built on SSB that allows apps to be notified when data changes. This feature can be helpful when an application uses cached data and must be notified when changes occur. Unfortunately, the feature was not well thought out when first implemented and does not appear to have been updated since.
It’s difficult to say how many people are actually using Query Notifications, but SSB itself seems to be holding its own, at least until something better can be implemented in the data tier that is simpler to use and architected with a lot more eloquence.
Not so lost?
As with any SQL Server component, the five features we discussed here—DQS, MDS, PBM, MDW, and SSB—have their supporters and their opponents. What the future holds for any of them is difficult to say. Much will depend on industry trends and, of course, Microsoft’s long-term strategies and knee-jerk reactions. If the features do stick around, Microsoft should seriously consider investing resources in both improving each one and in evangelizing their merits.
As we progress through this series, we’ll look at more SQL Server components that have generated their fair share of controversy and frustration, regardless of the degree they might have been embraced over the years. Some of the components are still with us. Many have passed into database history, added to the chain of lost causes. Can we also assume that our five current features are also lost? Probably not—or at least not yet. But then, with Microsoft and SQL Server, anything is possible.
Load comments