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.
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.
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.
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
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
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
Leave a Reply