how to identify sql server deprecated features of 2000 and 2005 in 2008 server

hi,

Pleanning for a enterpirse server upgrade from 2000 and 2005 servers to 2008, is there any TOOL which would run across the server to identify if there any deprecated commands used in these servers.

I have used SQL server upgrade advisor but its of not much detailed report.

Please let me know if any other tools or any other process involved in usage.

Thanks and regards,

asked Jan 07 '12 at 04:06 AM in Default

pvsrinivasrao
164 ● 21 ● 27 ● 27

(comments are locked)

3 answers:

I wrote a tip on mssqltips.com last year that should help you derive a trace to capture these events and identify where they're coming from.

http://www.mssqltips.com/sqlservertip/2334/identify-deprecated-sql-server-code-with-a-server-side-trace/

answered Jan 07 '12 at 12:10 PM

AaronBertrand
905 ● 1 ● 3

(comments are locked)

You could use Extended Events to identify deprecated features too - a good example here : http://www.mssqltips.com/sqlservertip/1857/identify-deprecated-sql-server-code-with-extended-events/

answered Jan 09 '12 at 02:06 AM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

(comments are locked)

What you could do is take a copy of your database into SQL Server 2008 R2, and capture a day's activity in the form of a trace file that you then replay against the SQL Server 2008 R2 instance. Then you can look at the contents of the system DMV sys.dm_os_performance_counters - look for deprecated feature usage in there. You'll see counts of what features have been used.

See, among others, http://technet.microsoft.com/en-us/library/bb510662.aspx, http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/find-out-if-you-are-using-deprecated-fea-2008, http://sqlblog.com/blogs/allen_white/archive/2009/05/26/tracking-deprecated-features.aspx