Leveraging Storage Spaces Direct for SQL Server High Availability
Thursday, July 19, 2018 - click here to learn more
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?
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
- Get business owner, IT manager or other applicable approvals for the database decommissioning.
- 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.
- 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.
- 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.
- 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.
- If you are migrating from SQL Server Enterprise Edition, for example, to
the Standard Edition make sure
features are compatible:
- 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.
- 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).
- Have a back-out plan for the migration that has all of the steps to bring the database/application back online.
- Create relevant Change Management documents if you have a formal Change Management process.
USE [_Demo_Decomm_1] GO -- Enterprise Edition features used select * from sys.dm_db_persisted_sku_features
Phase 1 - Initial Decommissioning/Migration Tasks
- Disable all jobs related to the database after the final backup (do
not delete jobs until Phase 2 of the decommissioning is complete):
- Disable logins on the old SQL Server if users do not have access to other
databases and if they are not members of any
- Update statistics, rebuild indexes and check database before the migration. Check the database after migration.
- Make sure that all application services have been disabled and there are
no connections to the database:
- 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.
- 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:
- Check if the
FILESTREAM is enabled on the database:
- Find if there are
FileTables in the database (FILESTREAM dependency):
- Check if the database has
Full Text catalogs:
- Check if the database is
referenced by T-SQL code (procedures, views, etc.) in other databases:
- Check if there are
synonyms in other databases that reference the database:
- 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):
- Check the database's files locations:
- Check if the database has
CLR dependency (has assemblies):
Service Broker dependencies:
- Check the database
- Check if you need to disable any server level DDL triggers that might be
related to the database:
- 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.
- 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).
- Verify if the database is encrypted or has a certificate or has symmetric/asymmetric
- Check if there are
Resource Governor dependencies:
Extended Events that are filtered by database name or by the database ID:
- Review databases maintenance plans/scripts. Make sure that there is no database specific tasks or hard coded database names.
- Review and disable database specific alerts:
- Run the sp_helpdb procedure to record the current database owner:
- Note the SQL Server Service Pack or Cumulative Update level:
Central Management Server run the following script to find policies
or conditions that might be related to the database:
- Set the database offline, keep for the warranty period or standard decommissioning
period. Afterwards drop the database or just keep offline:
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.
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:
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.
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();
SELECT name, is_published, is_merge_published, is_distributor FROM master.sys.databases
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.
USE [_Demo_Decomm_1] GO -- FileTables SELECT * FROM sys.filetables; GO SELECT * FROM sys.tables WHERE is_filetable = 1; GO
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.
-- 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.
-- 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.
USE [master] GO SELECT name, product, provider, data_source, provider_string, [catalog] FROM sys.servers WHERE is_linked = 1
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.
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.
USE [_Demo_Decomm_1] GO -- Service Broker dependencies SELECT * FROM sys.service_queues WHERE is_ms_shipped = 0
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.
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%'
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
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.
--- 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
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).
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
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.
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.
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.
ALTER DATABASE [_Demo_Decomm_1] SET OFFLINE;
Phase 2 - Final Steps performed after decommissioning/migration
- Backup database on the new server after migration (before users start using it).
- Review the logs.
- 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.
- If this was a database migration make sure application configuration/SQL Server alias/DNS records have been updated.
- Delete on the old SQL Server all disabled jobs identified in Step 1 of Phase 1 that are related to the database.
- Delete old disabled logins on SQL Server related to the database. See the Step 2 of Phase 1 to get the list of logins.
- Delete other components identified in Phase 1 above (for example, extended events sessions, audits, etc.).
- Delete database after warranty period. Bring it online and drop the database
(make sure files on disks are deleted as well):
- Update your Database Inventory.
- Update other database related documentation (SQL Server Agent Jobs, backups, maintenance. etc.).
- 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).
ALTER DATABASE [_Demo_Decomm_1] SET ONLINE; GO DROP DATABASE [_Demo_Decomm_1];
If this is the last database on the server
- Remove SQL Server from Utility Control Point.
- Remove SQL Server from the Central Management Server and from other monitoring scripts if this is the last database on the server.
- Remove the server from other monitoring tools (for example third party tools, SCOM, etc.).
- Remove the server from automated patching software.
- Remove DNS aliases id required.
- Release IP address (or addresses).
- Remove firewall rules.
- Disable and delete service accounts (make sure they are not shared with other SQL Servers installations).
- Decommission/shred disks.
- Decommission/repurpose LUNs if SAN storage was used.
- Disable tape backups.
- Remove the server from Antivirus server (if applicable).
- Perform other cleanup tasks that are applicable to your environment.
- 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.
- 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: 2016-06-23
About the author
View all my tips