Database Administrator (DBA) on-boarding is not always as efficient as it could be. Whether this is for new full time employees or contractors. We often have to wait for system access, computers to be setup, the right software to be installed, etc. How can we be sure that the new DBAs are productive a soon as possible? How can we use highly qualified contractors in the most effective way from day one?
Different companies have various requirements and processes for database or database server decommissioning. In this tip we have provided 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 have also provided 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.
This check-list could be used in the following cases:
Decommissioning of an application which is obsolete and not in use any more.
Decommissioning of an application due to the replacement with another application.
Upgrading an application to the newer version without data migration (new database will be created).
We do not have steps here to check if database is in use before the decommissioning. This check-list is for the cases where we already know that application is scheduled for the retirement, but some steps could be applicable for the migration of a database to the new hardware.
Certain steps will be applicable to your environment and others will not (depending on activities you perform or depending on specific configurations).
Get Business and/or IT Manager approvals for the database decommissioning.
Notify business users about the database decommissioning.
Have a back-out plan.
Make sure that all software licenses terminated (not scheduled for the renewal).
Create Request For Change if you follow ITEL practices.
Disable the database monitoring.
Make sure that all application services have been disabled and there are no connections to the database. Work with desktop team to make sure that applications removed from the PCs. You may need to monitor the database’s connections for some time to make sure that nobody uses it.
For the critical (for example financial) applications that will be decommissioned make sure that software media (SQL Server installation files, optional – Application installation files) is available in case if auditors request to restore the database.
Backup the database and make sure that database backup is available on the latest tape backup and not overwritten (if you keep only monthly tapes, for example). Create separate archive tape if you need to keep decommissioned databases backup separately.
Verify the latest backup to make sure that it is valid and has no errors.
Disable tape backups after the latest backup has been verified.
Delete old backup files (after the latest backup’s verification), remove database/backup folders if applicable.
Generate scripts or make sure you have latest version of the scripts for the jobs that operate with the database. Delete the jobs.
Set the database to the Offline or Read-Only Mode for the period required by your company’s decommissioning policy.
Script logins that have access to the database and delete logins that do not have access to any other database on SQL Server.
Make sure that all dependencies taken into consideration (Reporting Services, replication, linked servers, SSIS packages etc.).
Remove SQL Server options/features that were used only by this database (for example, FILESTREAM, Full-Text, Reporting Services).
Follow SQL Server decommissioning procedures if this is the last database on the SQL Server.
Update Database Inventory and other related database documentation.