Problems you face when you do not check features not supported in SQL Server
Few developers on a client's place raised a question that why they should really be concerned about deprecated features or commands in SQL Server?
Until SQL Server 2000 version it used to be a workaround to get the old scripts executed in a way. It is good now that you have complete reference within the documentation (BOL). The question above has got 2 answers, one is yes the user must be concerned and there is a way to come out of the problem by finding the deprecated features on the existing release.
Another change (may be) of support policy surrounding deperecated features that Microsoft is no longer going to support the particular features once they are designated as deprecated features. The documentation & communication from Microsoft has been clear enough to worry the developers that particular SQL Server features\commands would no longer be available and those commands have remained in the product. It also means that once a feature is indicated/considered as deprecated that the feature will be removed in the following product release. The pre-installed tools such as PERFMON (SYSMON), PROFILER and Upgrade Advisor would help to a great extent that will reduce the problems to find out whether your existing scripts has such danger of un-supportability.
I would pick from last that Upgrade Advisor, when you run Upgrade Advisor, the Upgrade Advisor Home page appears. From the Home page, you can run the following tools: Upgrade Advisor Analysis Wizard, Upgrade Advisor Report Viewer and Upgrade Advisor Help. It is better that when you run first time of this tool always run the Upgrade Advisor Analysis Wizard to analyze SQL Server components. When the wizard finishes the analysis, view the resulting reports in the Upgrade Advisor Report Viewer. Each report provides links to information in Upgrade Advisor Help that will help you fix or reduce the effect of the known issues. Insider the Upgrade Advisor, the analysis examines objects that can be accessed, such as scripts, stored procedures, triggers, and trace files, be aware that only it cannot analyze desktop applications or encrypted stored procedures and finally the output is in the form of an XML report where you can view the XML report by using the Upgrade Advisor report viewer.
Few of the deprecated commands for your refer:
Backup Log WITH NOLOG
Backup Log with TRUNCATE ONLY
DUMP DATABASE | DUMP LOG
LOAD DATABASE | LOAD LOG
BACKUP TRANSACTION
BACKUP DATABASE with PASSWORD
BACKUP DATABASE with MEDIAPASSWORD
Configures a database to operate in an earlier compatibility level
- 60 = SQL Server 6.0
- 65 = SQL Server 6.5
- 70 = SQL Server 7.0
sp_addalias, sp_dropalias, sp_addgroup, sp_changegroup etc.
Coming to PERFMON tool you can use the SQLServer:Deprecated Features object in SQL Server provides a counter to monitor the features designated as deprecated. In each case the counter provides a usage count that lists the number of times the deprecated feature was encountered since SQL Server last started. Within the BOL on the INdex if you look for deprecateion [SQL Server] --> Performance Counters you will see a table that describes the SQL Server Deprecated Features counter instances. Also you can use Dynamic Management View (DMV) to get a list of such information, where the Feature name value appears in trace events as the ObjectName and in performance counters and sys.dm_os_performance_counters as the instance name with the Feature ID value appears in trace events as the ObjectId.
Finally the PROFILER would be handy too get the information by using the Deprecation Final Support event class occurs when you use a feature that will be removed from the next major release of SQL Server. For greatest longevity of your applications, do not use features that cause the Deprecation Final Support event class or the Deprecation Announcement event class. SO the advice is you must modify applications that use final deprecation features as soon as possible. Why it is, Depreciation Announcement will be trigger when your current database server version has depreciated feature but it is still supported on current SQL Server version based on reflected profiler version. Depreciation Final Support will be trigger when your current database server has depreciated feature that will be unsupported on next SQL Server version based on reflected profiler version. In addition to this BOL clarifies further that the Deprecation Announcement event class occurs when you use a feature that will be removed from a future version of SQL Server, but will not be removed from the next major release. For greatest longevity of your applications, avoid using features that cause the Deprecation Announcement event class or the Deprecation Final Support event class.
In the next post I will cover the DISCONTINUED features issues.