How to Save License Costs by Checking Whether Enterprise Features are Used in Your SQL Server Databases

It is very common that many machines in your company might be using Enterprise editions of SQL Server while none of those enterprise features were ever used. This will heavily impact the license cost and should be fixed.

Find servers

If you are already maintaining an inventory of SQL servers, then it is an easy task to identify machines that are running Enterprise editions of SQL server. The next step is to identify if the applications hosted on these machines require an enterprise edition and if enterprise features are being used.

Create .SQL

Copy Script1 and save it as .sql file. Script 1 is to collect database names and find enterprise features on each database. Update the path of this .sql file in the script2 and run it from a server. Script 2 will collect data and save it in the log table. Please make sure that you have updated script2 with proper SQL account with which linked servers would be created.

Collect and review the log

Before running the script2 make sure you have listed all servers in the TBL0. For each server listed in this table, a linked server will be created and then for each database in the linked server, the script will identify enterprise features being used and will store it in TBL1.

Consult with Application owners

With the log, speak with concerned application owners and identify if it is okay to remove these listed enterprise features from the database. If they approve that the server does not needs Enterprise edition then you can switch to standard edition. This will save several dollars for your company. In addition to switching from Enterprise to Standard, you can also use SQL Express if the total size of databases of an application is less than 10 GB.

However you would not be able to find the enterprise features if the database has corrupted mdf file. In such a case, do not hesitate to use recovery tools such as DataNumen SQL Recovery tool.

Script1

CREATE TABLE TBL2 (
    cservername VARCHAR(200)
    ,dbname VARCHAR(200)
    ,cfeature VARCHAR(200)
    )

DECLARE cur2 CURSOR
FOR
SELECT NAME
FROM sysdatabases

DECLARE @name VARCHAR(200)

OPEN cur2

FETCH NEXT
FROM cur2
INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @cmd NVARCHAR(200)

    SET @cmd = 'use [' + @name + '] select @@servername,db_name(),feature_name FROM sys.dm_db_persisted_sku_features'

    INSERT INTO TBL2
    EXEC (@cmd)

    FETCH NEXT
    FROM cur2
    INTO @name
END

CLOSE cur2

DEALLOCATE cur2

Script2

SET NOCOUNT ON
SET ANSI_NULLS ON

CREATE TABLE TBL0 (cservername VARCHAR(200))

INSERT INTO TBL0
VALUES (‘SERVER1’)

CREATE TABLE TBL1 (
    cservername VARCHAR(200)
    ,dbname VARCHAR(200)
    ,cfeature VARCHAR(200)
    )

SELECT *
FROM TBL1

DECLARE @cmd0 NVARCHAR(2000)
DECLARE @vsvrname VARCHAR(200)
DECLARE @vuser VARCHAR(100)
DECLARE @vpwd VARCHAR(100)

SET @vuser = 'linkedserveruser'
SET @vpwd = 'linkedserveruser'

DECLARE cur1 CURSOR
FOR
SELECT cservername
FROM TBL0

OPEN cur1

DECLARE @server VARCHAR(200)

FETCH NEXT
FROM cur1
INTO @server

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @cmd VARCHAR(2000)

    SET @cmd = 'SQLCMD -S ' + @server + ' -E -i "full path of .sql file"'

    PRINT @cmd

    EXEC xp_cmdshell @cmd
        ,no_output

    SET @vsvrname = @server
    SET @cmd0 = 'EXEC sp_addlinkedserver @server=''' + @vsvrname + ''''

    PRINT @cmd0

    EXEC (@cmd0)

    SET @cmd0 = 'EXEC sp_addlinkedsrvlogin ''' + @vsvrname + ''', ''false'', NULL, ''' + @vuser + ''', ''' + @vpwd + ''''

    PRINT @cmd0

    EXEC (@cmd0)

    SET @cmd0 = 'insert into TBL1 select * from [' + @vsvrname + '].[master].[dbo].[TBL2]'

    PRINT @cmd0

    EXEC (@cmd0)

    PRINT 'Completed'

    SET @cmd0 = 'EXEC [' + @vsvrname + '].master..sp_executesql N''drop table TBL2'''

    EXEC (@cmd0)

    FETCH NEXT
    FROM cur1
    INTO @server
END

CLOSE cur1

DEALLOCATE cur1

SELECT *
FROM TBL1

DROP TABLE TBL1

DROP TABLE TBL0

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Outlook pst email problem and excel recovery software products. For more information visit www.datanumen.com