Still More SQL Server Features that Time Forgot

As the final entry in this series, Robert Sheldon leads you through a group of forgotten features that have been removed from recent versions of SQL Server. In some cases, the features were widely used and often loved, while others had lost their usefulness over the years or were replaced with something much better. In this article, he remembers Data Transformation Services (DTS), a handful of DBCC commands, a few utilities, Active Directory Helper Service, English Query, Web Assistant, SQL Mail, Native XML Web Services, Notification Services, SQL Distributed Management Objects, Surface Area Configuration Tool, and the Pubs and Northwind databases.

The series so far:

  1. The SQL Server Features that Time Forgot: Data Quality Services, Master Data Services, Policy-Based Management, Management Data Warehouse and Service Broker
  2. More SQL Server Features that Time Forgot: Auto-shrink, Buffer-pool extension, Database Diagrams, Database Engine Tuning Advisor, and SQL CLR
  3. 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
  4. 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

In the previous articles of this series, we focused on SQL Server components that are, for better or worse, still part of the product. We covered such features as Service Broker, auto-shrink, database diagrams, XML indexes, and a variety of others. I picked these features because of the buzz they’ve generated over the years and the landslide of opinions that went with it.

Despite all the brouhaha, Microsoft seems determined to keep these components in play, at least in the foreseeable future. Not all features have been so lucky. SQL Server’s history is checkered with memories of features past, components deprecated or dropped during one of the product’s many release cycles, sometimes with little fanfare. Many of these features have generated their own fair share of controversy, either because of how they were implemented or because they were removed. Other components have barely been missed.

Here we look at a number of features that were once part of SQL Server and have since been removed or deprecated, with some being dismissed many years back. For the most part, I’ve listed the features in alphabetical order to avoid prioritizing them or editorializing too much on their departure. You can think of this article as a trip down memory lane, without the nostalgia or remorse that often accompanies such reflection. Mostly it’s just a way to have some fun as we finish up this series.

Active Directory Helper Service

The Active Directory Helper Service, MSSQLServerADHelper, was introduced in SQL Server 2000 to help integrate SQL Server with Active Directory (AD). The service made it possible for the SQL Server service to register itself in an AD domain. In this way, the SQL Server service could run under a domain account with local administrative rights, while being able to add or remove AD objects related to the SQL Server instance.

Only one instance of the Helper Service ran on a host server, regardless of the number of SQL Server instances installed on that host. The service ran only when the SQL Server service needed to access AD. The Helper Service also played a role in replication and SQL Server Analysis Services (SSAS). To support the service, SQL Server included three system stored procedures: sp_ActiveDirectory_Obj, sp_ActiveDirectory_SCP and sp_ActiveDirectory_Start.

Microsoft discontinued the Helper Service in SQL Server 2012, removing the service and its associated stored procedures from the product. The company provided few specifics for why the service was removed, but it appears that the service was simply no longer being used.

Data Transformation Services

Anyone who’s been around SQL Server for any length of time will no doubt remember Data Transformation Services (DTS), that loveable collection of features and tools for carrying out data extract, transform and load (ETL) operations.

First introduced in SQL Server 7, DTS provided the components necessary connect to SQL Server and other data sources in order to import or export data and transform it along the way. Prior to that, database developers had to rely on utilities such as bcp to move data from one place to another, with few useful tools for efficiently transforming the data.

With DTS developers could define savable packages that connected to heterogeneous data sources and performed ETL operations. They could then run the packages on demand or schedule them to run at regular intervals.

Unfortunately, DTS had a number of limitations, especially when considered against the backdrop of a rapidly changing data culture. For this reason, Microsoft effectively ditched DTS in SQL Server 2005 and offered in its place SQL Server Integration Services (SSIS), a far more robust ETL tool that included advanced control flow, error handling, and transformation capabilities, along with a number of other new and improved features.

DBCC gang

Over the years, Microsoft has introduced and then removed an assortment of SQL Server DBCC statements. (DBCC is short for Database Console Commands.) One of these statements was DBCC DBREPAIR, which provided a quick way to drop a damaged database. In SQL Server 2005, Microsoft gave this statement the boot, informing customers that they should instead use the DROP DATABASE statement going forward.

Another DBCC statement that Microsoft finally ousted was DBCC NEWALLOC, which could be used to verify data and index page allocation within the extent structure. Starting with SQL Server 2000, Microsoft included the statement only for backward compatibility, removing it altogether in SQL Server 2014.

A couple other DBCC statements that have been laid to rest are DBCC PINTABLE and DBCC UNPINTABLE. The first was used to mark a table as pinned, and the second to mark it as unpinned. If a table were pinned, the database engine would not flush the table’s pages from memory.

Microsoft introduced the ability to pin a table in SQL Server 6.5 as a way to boost performance. Unfortunately, pinning a table resulted in adverse effects, such as damaging the buffer pool or causing the server to run out of memory. It wasn’t long before Microsoft disabled these statements, although they’re still part of the T-SQL lexicon. They just don’t do anything.

The DBCC ROWLOCK is another statement that goes back to SQL Server 6.5. The statement enabled Insert Row Locking (IRL) operations on a database’s tables. However, this capability became unnecessary because Microsoft soon automated row locking. In fact, by SQL Server 2000, the statement was included for backward compatibility only, although it wasn’t until SQL Server 2014 that Microsoft finally removed the statement.

Microsoft also removed the DBCC TEXTALL and DBCC TEXTALLOC statements from SQL Server 2014. The DBCC TEXTALL statement verified the integrity of the text, ntext, and image columns for all tables in a database. The DBCC TEXTALLOC statement did the same thing, but only for a specified table. Both statements originated with SQL Server 6.5 and by SQL Server 2000 were included for backward compatibility only.

No doubt, plenty of other T-SQL statements have come and gone, many without leaving a paper trail, but SQL Server 2014 seemed particularly hard on DBCC statements. Perhaps Microsoft saw that as a good time to do a bit of house-cleaning.

English Query

Introduced in SQL Server 6.5, English Query made it possible to automatically transform a question or statement written in English into a T-SQL statement. Microsoft offered English Query as part of SQL Server and as a standalone product.

English Query included a development environment and runtime engine to support the query transformation process. Ideally, an end user could type a question into an application’s text box, English Query would interpret the question and generate the T-SQL query, and the database engine would return the results, just like any other query.

In SQL Server 2005, Microsoft ditched English Query altogether. From then on, customers could no longer install or upgrade the product. However, if they upgraded a SQL Server 2000 instance to SQL Server 2005, and English Query had been implemented in the original installation, the English Query component would still work. In addition, customers with a SQL Server 2005 license could apparently install SQL Server 2000 and then use English Query against a SQL Server 2005 database, but those days are long gone.

Like many SQL Server features, English Query received an assortment of mixed reviews. Some developers liked it and made use of it. Others did not. At some point, Microsoft must have determined there was not enough interest in the feature to bother, so English Query got the axe, which came as a surprise to a number of users.

Perhaps in this case, Microsoft had been ahead of its time. When you consider how far we’ve come with technologies such as Siri, Google Assistant, and even Cortana, the potential for English Query was certainly there.

Native XML Web Services

In SQL Server 2005, Microsoft added Native XML Web Services to provide a standards-based structure for facilitating access to the database engine. Using these services, an application could send a Simple Object Access Protocol (SOAP) request to a SQL Server instance in order to execute T-SQL batch statements, stored procedures, or scalar-valued user-defined functions.

To carry out these operations, a SOAP/HTTP endpoint had to be defined on the server to provide a gateway for HTTP clients issuing SOAP requests. The T-SQL modules (batch statements, procedures, and functions) were made available as web methods to the endpoint users. Together these methods formed the basis of the web service.

Microsoft deprecated the Native XML Web Services in SQL Server 2008, with the recommendation that any SOAP/HTTP endpoints be converted to ASP.NET or Windows Communications Foundation (WCF) endpoints. These newer technologies were considered more robust, scalable, and secure than SOAP. Microsoft eventually removed the Native XML Web Services feature altogether.

Northwind and pubs databases

Who among us does not remember the pubs and Northwind databases? Even today, you can find references to them strewn across the web (mostly in outdated resources). They certainly deserve a mention as we stroll down memory lane.

The pubs database was developed by Sybase and came to Microsoft as part of the Sybase-Microsoft partnership in the early ’90s. The database included about 10 or so tables, based on a bookstore model. For example, the database contained the Titles, Authors, and Publishers tables, among several others. The pubs database provided a clean and simple example for demonstrating such concepts as many-to-many relationships and atomic data modeling.

But the pubs database was too basic to demonstrate more complex data modeling concepts and SQL Server features, so with the release of SQL Server 2000, Microsoft also introduced the Northwind database, which had its origins in Microsoft Access. The SQL Server team coopted the database to provide a more useful example of database concepts, without having to do a lot of the work themselves.

The Northwind database was based on a manufacturing model and included such tables as Customers, Orders and Employees. The database was still relatively simple, offering only a few more tables than the pubs database, but it helped to demonstrate slightly more complex relationships, such as hierarchical data. With the release of SQL Server 2005, the Northwind database was usurped by the now infamous AdventureWorks database.

Notification Services

Microsoft introduced Notifications Services in SQL Server 2000 to provide a platform for developing and deploying applications that generated and sent notifications to subscribers. Notification Services allowed developers to build applications that could send critical information to customers, employees, or other types of users when data changed in a specified way.

Developers could set up the service to generate and send notifications whenever triggering events occurred. In addition, subscribers could schedule notifications to be generated and sent at their convenience. The service could be configured to send notifications to a subscriber’s email account, cell phone, personal digital assistant (PDA), or Windows Messenger account.

Microsoft pulled the plug on Notification Services in SQL Server 2008 because the feature was not being implemented enough, which I doubt surprised many. Notification Services had a reputation for being inflexible, confusing, and difficult to implement, requiring a great deal of patience just to get a solution up-and-running. That said, some developers were able to make Notification Services work and thought it could do some cool stuff, but they seemed to be the exception. For most, getting to that point wasn’t worth the effort.

After pulling Notification Services from the product, Microsoft recommended that users turn to SQL Server Reporting Services (SSRS) and take advantage of such features as data-driven subscriptions.

SQL Mail

I’m not sure when Microsoft introduced SQL Mail, but it was there in the early days of SQL Server, providing users with a tool for sending, receiving, deleting, and processing email messages. Best of all, the service could send messages that included T-SQL query results.

SQL Mail used the Extended Messaging Application Programming Interface (MAPI) to communicate with an external email server and process email messages. However, to make this possible, an application that supported Extended MAPI also had to be installed on the server that hosted the SQL Server instance. The application would then provide SQL Server with the Extended MAPI components needed to communicate with the email server.

Microsoft introduced Database Mail in SQL Server 2005 as a replacement to SQL Mail because Database Mail was more robust and secure and offered better performance. Database Mail was also based on the Simple Mail Transfer Protocol (SMTP), rather than MAPI, and did not require that a local email application be installed. Microsoft finally dropped SQL Mail in SQL Server 2012.

SQL Server Distributed Management Objects

SQL Server Database Management Objects (SQL-DMO) were a collection of programming objects that facilitated database and replication management. The objects made it possible to automate repetitive administrative tasks as well as create and manage SQL Server objects and SQL Server Agent jobs, alerts, and operators. Developers could create SQL-DMO applications using any OLE Automation controller or COM client development platform based on C or C++.

By SQL Server 2005, SQL-DMO could no longer keep up with the new capabilities being introduced into the database platform. The time had come to replace the aging APIs. As a result, Microsoft introduced SQL Management Objects (SMO), a more robust set of APIs for administering SQL Server. SMO offered advanced caching and scripting features, along with a number of other capabilities, such as delayed instantiation.

To support backward compatibility, Microsoft continued to include SQL-DMO until SQL Server 2012, when it was dropped unceremoniously from the product. The thinking, no doubt, was that seven years was long enough for developers to update their apps and move into the 21st century.

Surface Area Configuration Tool

Remember the Surface Area Configuration Tool? It was introduced in SQL Server 2005 and dropped in SQL Server 2008, making it one of the product’s most short-lived features. The idea behind it was to improve security by providing a centralized tool for limiting the number of ways that would-be hackers and cybercriminals could gain access into the SQL Server environment.

The Surface Area Configuration Tool made it possible for administrators to enable, disable, start, or stop SQL Server features and services, as well as control remote connectivity. The tool leveraged WMI to facilitate these capabilities. Microsoft also made a command-line version of the tool available.

After dropping the Surface Area Configuration Tool, Microsoft recommended that users turn to such tools as SQL Server Management Studio (SSMS), SQL Server Configuration Manager, and policy-based management.

Utility hit list

As with many SQL Server components that have come and gone over the years, so too have an assortment of command-line utilities. Take, for example, the isql utility, which would let users run T-SQL statements, stored procedures, and script files from a command prompt. The utility used the old DB-Library protocol to communicate with SQL Server. Microsoft stopped including the isql utility in SQL Server 2005, pointing users to the sqlcmd utility as a replacement.

A similar utility, osql, does pretty much everything the isql utility did, except that it uses the ODBC protocol. However, the osql utility has been deprecated since at least SQL Server 2012 and will likely be pulled from the product in the not-too-distant future.

The same fate is in store for the sqlps utility, which launches a Windows PowerShell session, with the SQL Server PowerShell provider and related cmdlets loaded and registered.

Another deprecated utility is sqlmaint, which is slated to be removed after SQL Server 2017. The sqlmaint utility carries out database maintenance operations, such as performing DBCC checks, backing up database and log files, rebuilding indexes, and updating statistics. Going forward, DBAs should use the SQL Server maintenance plan feature instead.

A couple other deprecated utilities are makepipe and readpipe, which are used to test the integrity of the SQL Server Named Pipe services. Both utilities will soon be removed. In fact, they’re not even installed during setup, although they can still be found on the installation media. Same goes for the odbcping utility, which tests the integrity of an ODBC data source and verifies client connectivity.

Web Assistant

The Web Assistant, which I believe was introduced in SQL Server 7, offered a wizard for generating static HTML pages that contained SQL Server data. The wizard used a set of related system stored procedures to build the pages initially and to rebuild them if the data changed. The pages were fairly rudimentary, even by late ’90s standards, but were adequate enough for simple use cases.

With the release of SQL Server 2005, Microsoft did away with the wizard and kept only the stored procedures, which finally got dumped in SQL Server 2014. Whether anyone used the procedures after the wizard was removed is hard to say. Whether they used the wizard before that is even more of a mystery. I doubt many even noticed the procedures were gone.

To the best of my knowledge, Microsoft has not tried to replace this feature, perhaps deciding that static web pages provided little value, that HTML development has gotten far too sophisticated, that SSRS is more than adequate, or that a relational database management system was not the best place to be playing at HTML development. For whatever reason, Web Assistant and all of its offspring are gone for good.

History in the making

There are undoubtedly plenty of other SQL Server features that have gone missing over the years, in addition to what we’ve covered here. Perhaps you recall a few components that have a special place in your heart. Given how SQL Server has expanded and evolved over the years, it would be difficult to catch them all, especially if you also consider SSRS, SSAS, SSIS, or various other components. Whether or not you agree with their demise is another matter altogether.