Identifying SQL Server 2005 Enterprise Edition features in Use

SQL Server Administration
1

Hi all,

recently I need to downgrade the SQL 2005 version from enterprise to SQL 2008 Standard version.
is there any way to keep trace any enterprise features is using?

in 2008, we able to use the script as below:

SELECT * FROM sys.dm_db_persisted_sku_features

I found from SQL2005:
http://www.trycatchfinally.net/2013/04/identifying-sql-server-2005-enterprise-edition-features-in-use/

anyway to identify all the Enterprise features is using by SQL 2005?

2

anyone know how to check??
I need to solve it urgently.
thanks!!!

3

Confused. You're running SQL 2005 Enterprise and you want to convert to SQL 2008 Standard? That's half a downgrade and half an upgrade. Why not (at least) SQL 2008 R2? but preferably SQL 2014?

If you look here: Features Supported by the Editions of SQL Server 2008 R2 you can browse the list of differences. Most are not concerned with T-SQL though, if that's your concern.

4

yes... I want to migrate it from SQL 2005 Enterprise version to SQL 2008 Standard version.

we will purchase SQL 2014 standard or SQL2014 Enterprise.
due to compatibility issue, we downgrade it to SQL 2008 standard if we purchase SQL 2014 standard.
we will install SQL 2008 Enterprise if we purchase SQL 2014 Enterprise.

if we are purchase SQL 2008 Standard,
my concern is existing is using SQL 2005 Enterprise features in database. when I downgrade to SQL 2008 standard, some features cannot be function in SQL 2008 Standard.
therefore, I need to run a script to check whether existing SQL 2005 whether using any Enterprise version or not.
do you clear of it?

5

Not sure what that question means. (Sounds like Dutch, "ben je klaar?") But if you're asking if I understand the problem, yes, I do. I think that such a script would be quite long and very tricky since most of the features are in the setup info, not in any particular instance. Not only that, but what would you scan? e.g. suppose you have an application that issues ad-hoc queries that use the SQL MERGE command, for example. No SQL script can find those unless that script can also read and understand the source program (VB? C#, C++? Java? JavaScript? PHP? etc).

6

I think, but I am not sure, that the purchase only includes a license for one version prior - so if you buy SQL 2014 you can install either SQL2014 or SQL2012, but I don't think that licenses you to install SQL2008.

I appreciate that isn't the question you were asking! but it might be something worth double-checking if that is important to you.

7

Here's the main features you'd need to worry about, and some code to check if most of them are being used:

--check for database mirroring
select * from sys.database_mirroring where mirroring_guid is not null
--check for database snapshots
select * from sys.databases where source_database_id is not null
--check jobs for Online Index Operations :: !!not guaranteed, results will still need reviewed!!
select * from msdb.dbo.sysjobsteps where command like '%index%online%=%on%'

--checks below !!MUST BE DONE SEPARATELY FOR EACH USER DB!!
--Table and Index Partitioning
select * from [db_name].sys.partitions where partition_number > 1
--check procs for Online Index Operations :: !!not guaranteed, results will still need reviewed!! :: !!MUST BE DONE SEPARATELY FOR EACH USER DB!!
select * from [db_name].sys.sql_modules where definition like '%index%online%=%on%'

--SSRS: Data-driven subscriptions
-- ?? don't have time for this now

1 Like
8

thanks for all,..
you give me the answer I want..