You’re probably aware that not everyone is running the same version of SQL Server. When I give user group presentations, there are people in the audience running their applications on everything from SQL Server 6.5 to. (I’m sure there are still applications running on even older versions, but most of the people in charge of those applications also have applications running on later versions.)
There are many reasons for not upgrading to the latest and greatest version, including not wanting to pay the software cost or the cost of testing your applications against the new version, as well as the possibly stickier problem of a reliance on third-party applications that haven’t been upgraded to work with newer SQL Server versions. If your business depends on a certain application, and that application won’t work with SQL Server 2008 databases, you have a very good reason to keep running SQL Server 2005.
But what do you do when you have a very good reason for staying with an older version, but you really want to use features available only in a newer version? In my classes and seminars, I frequently spend time discussing the wonderful things you can do with features available in the latest version, and a very common question I get from my students is "How do I do that on SQL Server <insert older version number here>?"
When I started teaching about SQL Server 2005, I frequently got this kind of question about the Dynamic Management Views (DMVs). Personally, the DMVs are my favorite enhancement in SQL Server 2005, and I’m really reluctant to do any kind of on older systems, simply because I won’t have access to the metadata provided by these tools. Some of the DMVs overlap with older stored procedures so that in SQL Server 2000 I can use sp_who or sp_who2 instead of selecting from sys.dm_exec_sessions. But a view is much easier to work with than the older stored procedures because I can select just the columns of interest, filter on only the rows I want to see, or join with another table or view to get additional information. Other DMVs provide information that was available only in older SQL Server versions using undocumented commands, such as DBCC INPUTBUFFER. But many DMVs provide information that’s not available any other way, and if you aren’t running SQL Server 2005 or later, you won’t have access to this metadata.
Related: SQL Server Terms: What's in a Name?
Other new features can be partially simulated by using existing features. For example, the table partitioning feature in SQL Server 2005 can be simulated in earlier versions of SQL Server using a feature called "partitioned views." However, SQL Server 2005’s database mirroring feature can’t be simulated, so in order to use it, you must have SQL Server 2005 or later.
We have a similar situation when we look at SQL Server 2008’s features. Some features are available in SQL Server 2005 using undocumented commands, such as the ability to inhibit lock escalation. Other features, such as data compression, aren’t available in SQL Server 2005. (The only exception is the compression of numeric data with the vardecimal property introduced in SQL Server 2005 SP2.) But the new SQL Server 2008 feature I get the most enthusiastic about, and the most questions about how to get this behavior in SQL Server 2005, is the new Management Data Warehouse (MDW). For more information about this feature, you can read SQL Server Books Online (BOL), or take a look at the recently published white paper at msdn.microsoft.com/en-us/library/dd939169.aspx.
Although there’s no way to access all the functionality of the new MDW technology on older versions of SQL Server, there is a tool (DMVStats) available on Codeplex you might want to check out at www.codeplex.com/sqldmvstats. This tool provides the three main components of SQL Server 2008’s MDW, including jobs to collect information, a database to hold the collected information, and reports to display the collected data in a useful manner. Although the DMVStats tool doesn’t collect all the data that the MDW can capture, it can get you started thinking about the usefulness of regularly collecting performance data in a specific location.
Although I can understand the desire to use the new tools and technologies available without all the cost and hassle of upgrading, I can’t understand those DBAs and developers (admittedly, the minority) who get upset when they find out there’s no way to use the new feature in an earlier version. I have heard implications that once a feature has been incorporated into SQL Server it should be available to anyone using the product! If this were so, there were be no reason for anyone to upgrade, and if no one upgraded, there would be no reason for Microsoft to add new features and enhancements. There would be no winners in this situation. If you want the new features, then upgrade!