Over the years with each release I’m often impressed by some of the features contained in SQL server but every so often, I start to use a feature that I haven’t used before and I’m blown away by just how good that particular feature is. Sometimes it is the sheer power/flexibility of the feature that impresses me or sometimes it is ease or simplicity of implementing that feature. Because of the strength of the product, we all come to expect a host of new features with each release and whilst these are all generally very good additions/enhancements to SQL server, they are normally expected or necessary enhancements to the product to address the changing user requirements and market place. We have all got our own view of what are SQL server most stand out features, but here are two of my “just wow” features in the product. |
The first of these features that really stands out was shipped with 2005 and that is Service Broker. I’ve been using it for many years in a variety of ways and it is an amazingly solid, reliable and flexible messaging/queuing engine. I’ve used it to offload DML tasks where the application does not need to know the outcome immediately other than the request was accepted and that the data will be eventually persisted in the required tables. One such implementation of service broker was to deal with the auditing of user activity within a client application. The application would send all the details of the event to audit to a normal stored proc and inside that proc an xml structure would be constructed encapsulating all the data to be audited and then sent onto to service broker. Once the service broker has accepted the message and placed that onto a queue, the proc would return control back to the application. This allowed the app to be more responsive to the users as the DML statements required to store the audit data was executed in the background by SQL server at a near future point in time. The users didn’t see or feel any contention on the underlying tables that were normally quite heavily hit. Another implementation of service broker comprised an data export feature from a web app. The user would request an export of data to file but the export could take many hours to complete. So the solution was for the web app to create a series of messages that were sent to the service broker which activated different procedures/CLR's that would control how the data was constructed, exported and finally zipped ready for the user. The service broker then processed those messages in strict order and created the data export as requested over a number of hours for the user.
I think the only thing that let service broker down and probably reduced the take-up of it was that it had no GUI. Wether intentional or not, you could only setup/reconfigure/maintain service broker by using T-SQL. Not really a problem for many I’m sure, but it may have been enough to stop a number of SQL developers from trying it out and thus causing them to evaluate other technologies. Either way, I have always thought that service broker is one of the gems in SQL server.
The second just wow feature of mine is one that I have only recently for the first time started to use. This 2008 enterprise only feature is CDC or change data capture. I’m working on a large data migration project for a new version of an application whereby there is a requirement to have the new servers running the updated app to contain all the migrated data in near real-time as it is published to production to facilitate a very quick switch over to the new version. I decided to use CDC as one of the methods to identify changed data so that it can be targeted and migrated over to the new servers very quickly. Thus having a “hot” server ready to be used by users after the switchover with very minimal other prep work. I hadn’t used CDC prior to this project, but after researching it and trying it out in dev, I was amazed at 1) just how easy it is to setup in a simple form and 2) the ease to develop solutions against the data CDC captures. To setup it up in a simple form, you just need to run one built in proc to setup the database for CDC and then another stored proc for each table that you want to start capturing data for. Once data has started to be captured for the table, you then have available a number functions to query the captured data and then it is a simple case to implement logic accordingly. In the background there are processes to purge historical data from the capture tables and prevent the capture tables from growing out of control. Awesomely simple and a brilliant feature straight out of the box... It is just a shame that this isn’t a standard feature as I would love to be able to use this more often but many of our installations are running on standard edition.
There are a other great features as well like TDE, AlwaysOn, FileTables, database mirroring and Hekaton in 2014, but service broker and CDC are two that currently really stand out for me as a feature that just seems to stand up and deliver far more than your expectations.
Enjoy!
