Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Database Decommissioning Check List


By:   |   Read Comments (3)   |   Related Tips: More > Database Administration

Problem

In one of our past tips we provided a new database questionnaire request form to help you gather information required for a new database creation.  But what are steps to decommission or migrate a database?

Solution

Different companies have various requirements and processes for database or database server decommissioning. In this tip we will provide steps that will help you decommission a database and make sure you have a good back-out plan (for example, in case there is a need to bring the database back online). We will also provide scripts that will help you identify dependencies in case there is a need to migrate the database instead of decommissioning.

You may already have a process documented, but this list will help you review most of the dependencies and make sure you are prepared for either a migration or decommissioning.

Preparation steps performed before decommissioning/migration

  1. Get business owner, IT manager or other applicable approvals for the database decommissioning.
  2. Have an agreement on warranty period (time frame when business may request to bring the database back online). During this period the database will be offline, logins disabled, any related jobs disabled, etc. The infrastructure will be in a "disabled" state, but nothing is deleted.
  3. Have an agreement on final dates - how long the database will be offline, how long backups have to be retained, other archival/decommissioning requirements. Some financial database backups have to be retained up to 7 years due to government regulations.
  4. For critical applications that will be decommissioned make sure the SQL Server software media is available in case auditors request a restore of the database. For example, you can't restore SQL Server 2000 database directly to SQL Server 2014. You have to be sure you can restore the database to the SQL Server 2008 first, change the database compatibility mode and then backup it and restore it to the SQL Server 2014.
  5. If you are migrating the database make sure that the new SQL Server version is supported by a vendor. You may proceed with an unsupported migration (where the database will be running under compatibility mode) if you have technical approvals for this scenario or if your practice allows you to do this.
  6. If you are migrating from SQL Server Enterprise Edition, for example, to the Standard Edition make sure features are compatible:
  7. USE [_Demo_Decomm_1]
    GO
    -- Enterprise Edition features used
    select * from sys.dm_db_persisted_sku_features
    
  8. Make sure all dependencies are taken into consideration (Reporting Services, Linked Servers, Replication, SSIS packages, etc.). Involve all stakeholders to identify potential dependencies. See some of the scripts used for dependency checks below.
  9. Make sure that all licenses are up to date (correspond to the new version if this is migration, freed up if this is decommissioning). Communicate and plan licensing changes with vendor and internal resources responsible for the licensing (including application, SQL Server and other dependent software, if applicable).
  10. Have a back-out plan for the migration that has all of the steps to bring the database/application back online.
  11. Create relevant Change Management documents if you have a formal Change Management process.

Phase 1 - Initial Decommissioning/Migration Tasks

  1. Disable all jobs related to the database after the final backup (do not delete jobs until Phase 2 of the decommissioning is complete):
  2. USE [_Demo_Decomm_1]
    GO
    -- disable related jobs (jobs that have T-SQL step with a database name selected for the step)
    SELECT j.* FROM msdb.dbo.sysjobsteps js
     JOIN msdb.dbo.sysjobs j ON js.job_id = j.job_id
    WHERE j.[enabled] = 1 AND js.database_name = DB_NAME()
    -- disable every job found with the query above EXEC msdb.dbo.sp_update_job @job_name = N'Export Some Data', @enabled = 1 ;
    -- there could be other jobs that are related to the database. -- For example, scheduled SSIS packages.
  3. Disable logins on the old SQL Server if users do not have access to other databases and if they are not members of any server role:
  4. USE [_Demo_Decomm_1]
    GO
    CREATE TABLE dbo.##temp_logins (sid VARBINARY(200), db NVARCHAR(128));
    EXEC sys.sp_MSforeachdb 'INSERT INTO dbo.##temp_logins SELECT sid, ''?'' FROM [?].sys.database_principals WHERE type IN (''S'', ''U'', ''G'') -- SQL Logins, Windows Logins and Windows Groups AND authentication_type > 0 --excludes schemas and users without logins in contained databases AND principal_id > 1 -- dbo';
    SELECT sp.name as Login_Name, CASE WHEN all_dbs.db IS NOT NULL THEN '-- Exists in ' + all_dbs.db + ' database' WHEN sr.member_principal_id IS NOT NULL THEN '-- Is member of the ' + r.name + ' server role ' ELSE 'ALTER LOGIN [' + sp.name + '] DISABLE ; ' END as "Keep/Disable Login" FROM tempdb.dbo.##temp_logins c_db -- current database (to decommission) JOIN sys.server_principals sp ON c_db.sid = sp.sid LEFT JOIN sys.server_role_members sr ON sp.principal_id = sr.member_principal_id LEFT JOIN sys.server_principals AS r ON sr.role_principal_id = r.principal_id LEFT JOIN tempdb.dbo.##temp_logins all_dbs ON c_db.sid = all_dbs.sid and all_dbs.db != DB_NAME()
    WHERE c_db.db = DB_NAME() ;
    DROP TABLE dbo.##temp_logins;

    Here is an example of the query output:

    Logins review - query results

    Make sure that all disabled logins from step 2 have been migrated to the new server if this is a database migration. Here is a script from Microsoft that you can use to copy logins including SQL Server login passwords.

  5. Update statistics, rebuild indexes and check database before the migration. Check the database after migration.
  6. Make sure that all application services have been disabled and there are no connections to the database:
  7. USE [_Demo_Decomm_1]
    GO
    -- check DB connections
    SELECT login_time, [host_name], [program_name] 
    FROM sys.dm_exec_sessions WHERE database_id = DB_ID();
    
  8. Backup the database and use the latest backup for the migration/decommissioning. Validate the backup. Make sure that the final database backup is available for the warranty or compliance period. Check with your Backups Administrator for retention policies and make sure the backup will be preserved as long as required.
  9. Review and schedule disabling of Database Snapshots, Replication, Log Shipping, Availability Groups, Mirroring, etc. if applicable. To check if the database is a publisher or a distributor for a replication topology run the following script:
  10. SELECT name, is_published, is_merge_published, is_distributor FROM master.sys.databases
  11. Check if the FILESTREAM is enabled on the database:
  12. USE [_Demo_Decomm_1]
    GO
    -- FILESTREAM
    SELECT DB_NAME(database_id), non_transacted_access, non_transacted_access_desc
        FROM sys.database_filestream_options;

    If you migrate the database you will need to enable FILESTREAM on the new server.

  13. Find if there are FileTables in the database (FILESTREAM dependency):
  14. USE [_Demo_Decomm_1]
    GO
    -- FileTables
    SELECT * FROM sys.filetables;
    GO
    SELECT * FROM sys.tables WHERE is_filetable = 1;
    GO
  15. Check if the database has Full Text catalogs:
  16. USE [_Demo_Decomm_1]
    GO
    -- full-text catalogs
    SELECT name FROM sys.fulltext_catalogs

    If you migrate the database you will need to make sure that the Full Text Service is running on the new server.

  17. Check if the database is referenced by T-SQL code (procedures, views, etc.) in other databases:
  18. -- cross-db dependencies
    CREATE   TABLE #tmp_dependencies (
       referencing_db_name NVARCHAR(128),
       referencing_object NVARCHAR(128),
       referenced_database_name NVARCHAR(128),
       referenced_schema_name NVARCHAR(128),
       referenced_entity_name NVARCHAR(128))
    GO
    EXEC sys.sp_MSforeachdb 'INSERT INTO #tmp_dependencies SELECT ''?'' AS referencing_db_name, OBJECT_NAME (referencing_id) AS referencing_object, referenced_database_name, referenced_schema_name, referenced_entity_name FROM [?].sys.sql_expression_dependencies WHERE referenced_database_name IS NOT NULL AND is_ambiguous = 0 AND referenced_database_name = ''_Demo_Decomm_1'';'
    SELECT * FROM #tmp_dependencies;
    DROP TABLE #tmp_dependencies; GO

    If there are dependencies make sure that appropriate code changes/decommissioning is performed to avoid broken dependent applications.

  19. Check if there are synonyms in other databases that reference the database:
  20. -- synonyms
    CREATE   TABLE #tmp_synonyms (
       referencing_db_name NVARCHAR(128),
       synonym_name NVARCHAR(128),
       base_object_name NVARCHAR(1035))
    GO
    EXEC sys.sp_MSforeachdb 'INSERT INTO #tmp_synonyms SELECT ''?'' AS referencing_db_name, name AS synonym_name, base_object_name FROM [?].sys.synonyms WHERE base_object_name LIKE ''[[]_Demo_Decomm_1].%'';'
    SELECT * FROM #tmp_synonyms;
    DROP TABLE #tmp_synonyms; GO

    Investigate synonym usage to avoid broken dependent applications.

  21. Remove (if exists) linked servers that might be used to connect to the old database (you will need to run this on different SQL Servers):
  22. USE [master]
    GO
    SELECT name, product, provider, data_source, provider_string, [catalog]
     FROM sys.servers WHERE is_linked = 1
  23. Check the database's files locations:
  24. USE [_Demo_Decomm_1]
    GO
    SELECT file_id, type_desc, data_space_id, name, physical_name FROM sys.database_files

    If the database had a dedicated LUN that is not shared with other databases work with Sysadmins to decommission/repurpose the LUN as well.

  25. Check if the database has CLR dependency (has assemblies):
  26. USE [_Demo_Decomm_1]
    GO
    -- CLR
    SELECT name, clr_name, is_user_defined FROM sys.assemblies 

    If this was the last database on the server that was using CLR you may consider disabling this feature on SQL Server (to reduce the attack surface by disabling unused features). If this is a database migration then make sure that the new SQL Server has CLR enabled.

  27. Check Service Broker dependencies:
  28. USE [_Demo_Decomm_1]
    GO
    -- Service Broker dependencies
    SELECT * FROM  sys.service_queues WHERE is_ms_shipped = 0
  29. Check the database Audit specifications:
  30. USE [_Demo_Decomm_1]
    GO
    -- DB audit
    SELECT name, is_state_enabled FROM sys.database_audit_specifications

    Read this tip if you need to move the database that has audit enabled to a different SQL Server.

  31. Check if you need to disable any server level DDL triggers that might be related to the database:
  32. USE master
    GO
    -- Server Level DDL triggers
    SELECT t.name, m.[definition] 
     FROM sys.server_sql_modules m
      JOIN sys.server_triggers t ON m.object_id = t.object_id 
     WHERE [definition] LIKE '%Demo_Decomm_1%'
  33. Make sure that database monitoring has been disabled if monitoring is performed by a third party company. Disable the monitoring in other tools, if applicable.
  34. Understand how credentials and proxy accounts work and make sure that there are no dependencies (for example SSIS proxy account that has access to the database as well). Cross check with Step #1 (SQL Server Agent Jobs that might be related to the database).
  35. USE [master]
    GO
    SELECT  j.name AS job_name, js.step_name, p.proxy_id, 
      p.name AS proxy_name, p.credential_id, l.name AS login_name,
      c.name AS credential_name 
     FROM msdb.dbo.sysproxies p 
      JOIN sys.syslogins l ON p.user_sid = l.sid 
      JOIN sys.credentials c ON p.credential_id = c.credential_id
      JOIN msdb.dbo.sysjobsteps js ON p.proxy_id = js.proxy_id
      JOIN msdb.dbo.sysjobs j ON js.job_id = j.job_id
    ORDER BY  j.name
  36. Verify if the database is encrypted or has a certificate or has symmetric/asymmetric keys:
  37. USE [_Demo_Decomm_1]
    GO
    SELECT * FROM sys.dm_database_encryption_keys
    WHERE database_id = DB_ID();
    SELECT name, key_length, algorithm_desc, create_date, modify_date FROM sys.symmetric_keys;
    SELECT name, algorithm_desc FROM sys.asymmetric_keys;
    SELECT name, subject, start_date, expiry_date FROM sys.certificates;

    Create a backup of the Database Master Key if required as described here.

    Backup the certificate if required using an example provided here.

    Read more tips about encryption here.

  38. Check if there are Resource Governor dependencies:
  39. --- Get the classifier function Id and state (enabled).
    SELECT * FROM sys.resource_governor_configuration
    GO
    --- Get the classifier function name and the name of the schema
    --- that it is bound to.
    SELECT * ,
          object_schema_name(classifier_function_id) AS [schema_name],
          object_name(classifier_function_id) AS [function_name]
    FROM sys.dm_resource_governor_configuration
  40. Review Extended Events that are filtered by database name or by the database ID:
  41. USE [master]
    GO
    SELECT e.event_session_id, s.name, e.predicate 
     FROM sys.server_event_session_events e
      JOIN   sys.server_event_sessions s ON e.event_session_id = s.event_session_id
    WHERE predicate LIKE '%source_database_id%' OR  predicate LIKE '%database_name%';

    Disable the sessions, script them (if required for the database migration).

  42. Review databases maintenance plans/scripts. Make sure that there is no database specific tasks or hard coded database names.
  43. Review and disable database specific alerts:
  44. USE [_Demo_Decomm_1]
    GO
    -- alerts
    SELECT * FROM msdb.dbo.sysalerts 
    WHERE database_name = DB_NAME() -- SQL Server event alerts for DB
     OR performance_condition LIKE '%' + DB_NAME() + '%' -- performance condition alerts
  45. Run the sp_helpdb procedure to record the current database owner:
  46. USE [_Demo_Decomm_1]
    GO
     -- db_owner 
    EXEC sys.sp_helpdb

    Some applications require the application account be mapped to the dbo built-in user.

  47. Note the SQL Server Service Pack or Cumulative Update level:
  48. SELECT @@VERSION;

    If you migrate the database to a SQL Server that has higher version you may need to perform additional application testing before you migrate the database.

  49. On the Central Management Server run the following script to find policies or conditions that might be related to the database:
  50. SELECT name, facet, obj_name, expression  FROM msdb.dbo.syspolicy_conditions 
    WHERE obj_name = DB_NAME()
     OR expression LIKE '%' + DB_NAME() + '%'

    Read more tips about Policy Based Management here.

  51. Set the database offline, keep for the warranty period or standard decommissioning period. Afterwards drop the database or just keep offline:
  52. ALTER DATABASE [_Demo_Decomm_1] SET OFFLINE;

Phase 2 - Final Steps performed after decommissioning/migration

  1. Backup database on the new server after migration (before users start using it).
  2. Review the logs.
  3. Setup the database audits/monitoring (if database has been migrated and if a special database audit/monitoring is required). Start monitoring only after all issues have been addressed.
  4. If this was a database migration make sure application configuration/SQL Server alias/DNS records have been updated.
  5. Delete on the old SQL Server all disabled jobs identified in Step 1 of Phase 1 that are related to the database.
  6. Delete old disabled logins on SQL Server related to the database. See the Step 2 of Phase 1 to get the list of logins.
  7. Delete other components identified in Phase 1 above (for example, extended events sessions, audits, etc.).
  8. Delete database after warranty period. Bring it online and drop the database (make sure files on disks are deleted as well):
  9. ALTER DATABASE [_Demo_Decomm_1] SET ONLINE;
    GO
    DROP DATABASE [_Demo_Decomm_1];
  10. Update your Database Inventory.
  11. Update other database related documentation (SQL Server Agent Jobs, backups, maintenance. etc.).
  12. Delete old backup files (after monthly backup to the tape completion), remove database files (if you did not bring the database online before dropping the database).

If this is the last database on the server

  1. Remove SQL Server from Utility Control Point.
  2. Remove SQL Server from the Central Management Server and from other monitoring scripts if this is the last database on the server.
  3. Remove the server from other monitoring tools (for example third party tools, SCOM, etc.).
  4. Remove the server from automated patching software.
  5. Remove DNS aliases id required.
  6. Release IP address (or addresses).
  7. Remove firewall rules.
  8. Disable and delete service accounts (make sure they are not shared with other SQL Servers installations).
  9. Decommission/shred disks.
  10. Decommission/repurpose LUNs if SAN storage was used.
  11. Disable tape backups.
  12. Remove the server from Antivirus server (if applicable).
  13. Perform other cleanup tasks that are applicable to your environment.
  14. Disable and delete computer account from Active Directory.

Some steps from this tip also could be used in the following scenarios:

  • Replacing an old version with a newer version of application (assuming this is not in-place upgrade or the new database will be created on the different SQL Server).
  • Removing an application which is obsolete and is not used any more.
  • Removing an application because it is replaced with another application.
  • Migration of databases to new hardware.
Next Steps
  • Use this tip when you decommission or migrate your databases.
  • Create formal decommissioning documentation that will be applicable to your environment.
  • Read this tip that has a database migration checklist including useful scripts.
  • Here is a tip about migrating a SQL Server instance.
  • Post comments to this tip if you perform other steps that are not listed in the tip.


Last Update:






About the author
MSSQLTips author Svetlana Golovko Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, June 24, 2016 - 12:49:55 PM - Donnie Back To Top

Thank you, Svetlana, for a great, refreshing article.  Decommissioning may not be as glamorous as a new build or deployment project, but it's nonetheless important in the IT lifecycle, and thus merits equal attention.  Just as campers should leave the wilderness as they found it when it's time to depart, so too, should IT professionals undo their creations after that purpose has been fulfilled.


Thursday, June 23, 2016 - 7:10:06 PM - Svetlana Back To Top

Thanks, Bernie


Thursday, June 23, 2016 - 9:41:16 AM - bernie black Back To Top

Great post.  Lots of scripts I'd never seen before. 

 


Learn more about SQL Server tools