Decommission a server or database server is a crucial activity that has to be carefully planned and carried out to protect the security and integrity of your data. The correct procedures must be followed whether you’re replacing an old server, moving to the cloud, or retiring an old system. This in-depth manual will show you how to decommission a database server using T-SQL queries and recommended procedures.
Table of Contents
Introduction
A database server must be decommissioned in addition to being turned off. To protect confidential information, reduce downtime, and ensure a smooth transfer, a methodical strategy is needed. This post will first go through the criteria needed for a successful decommissioning, then it will break down each step in depth and include crucial T-SQL queries to help you along the way.
Prerequisites to Decommission a server or db server
To maintain data protection and a seamless transition, decommission a server or db server is a painstaking procedure that needs careful preparation and execution. Make sure you have finished the following requirements before beginning the decommissioning process:
Want to know about “Detect & Repair Database Errors with DBCC CHECKCATALOG“.
1. Dependency Analysis Before Decommission a server
Recognize Dependencies
Find out if the databases that are hosted on the server are used by any other systems, applications, or processes. A smooth transition depends on being aware of these dependencies.
Application Integration prior to decommission a server
Make sure that the new environment or systems they will be moved to are compatible with the applications that interface with the databases.
2. Communication with user to decommission a server or db server
Notify All Stakeholder prior to decommission a server or db server
Notify all parties/stakeholders/owners before proceeding to decommission a server, including users, IT personnel, and pertinent departments. Communicate clearly about deadlines, expectations, and any potential effects on services.
3. Data Evaluation and Record-Keeping
Database Evaluation prior to decommission a server
Analyse all of the server’s databases. Establish their significance to the company and any dependencies they could have.
Documentation
Make thorough documentation that describes each database, its function, related applications, and contact information. For reference and auditing reasons in the future, this information is priceless.
4. Security and Regulatory Compliance
Compatibility Check
Ascertain that the decommissioning procedure conforms with all applicable industry standards, corporate guidelines, and legal obligations regarding data security and privacy.
Encryption of Data
Decrypt any encrypted data or files, if necessary, to guarantee that data is still available after migration or archival. Securely store encryption keys.
5. Data Archive and Backup
Complete Backups prior to decommission a server
Make thorough backups of every database that is stored on the server. These backups provide a safety net, enabling you to recover data if any problems develop when decommissioning.
6. Inventory and configuration of servers:
Inventory of hardware and software
Create a list of the hardware requirements for the server, including its network configuration, CPU, RAM, and storage. List every piece of software and service that is installed on the server.
Configuration of a network
Keep track of firewall setups, subnets, IP addresses, and network settings. To move or reuse components, this knowledge is essential.
To know more about Recovery Models in SQL Server, click on the link.
Steps to Decommission a Database Server
A database server must go through several steps for the decommissioning process to go smoothly. A detailed tutorial with T-SQL queries for every step is provided below. Note that these actions are predicated on the assumption that you have the necessary backups and have notified the pertinent parties about the decommissioning procedure.
Step 1 : Drop the mail to the Application Owner to inform about the decommission of a server/ a database server. The mail should contain all necessary details like:
- No. of SQL Instances on the server.
- How many databases are available on each SQL instances with current status.
- Details related to high availability, if available.
- Current active sessions, if any.
Step 2: Assuming you have all necessary approvals to decommission a server and you have raised change request for this activity. This is best practice and is as per ITIL process.
Step3: Connect the SQL Instance & check whether any active connection is available or not. If available, capture the screenshot & forcely kill the session.
SELECT @@ServerName AS [ServerName],
DB_Name(DBID) AS [DBName],
Loginame AS [LoginName],
Count(*) AS [NoOfActiveConnections]
FROM sys.SysProcesses WHERE SPID>50
GROUP BY DB_Name(DBID),Loginame
Step 4: Now we need to capture a full backup of all databases. So rather than using the backup command one by one, we’ll use a generalized piece of code to take the full backup of all databases in one go.
DECLARE @DBName VARCHAR(200)='', --To store database name
@BackupFilePath VARCHAR(500)='', --To store db backup file path
@BackupFileName VARCHAR(500)='', --To store db backup file name
@DateFormat VARCHAR(40)='' --To store date & Time format for new backup file
SET @BackupFilePath = 'E:\Database\DBBackupFile\'
--We can also use centralzed shared location\network path
--'\\DBBackupFileSharedLocation\DBBackupFile\'
SELECT @DateFormat = Replace(Replace(CONVERT(VARCHAR(20),GETDATE(),121),'-','_'),':','_')
DECLARE cBackupCursor CURSOR READ_ONLY FOR
SELECT name FROM master.sys.databases WHERE name NOT IN ('tempdb')
AND state = 0 AND is_in_standby = 0
OPEN cBackupCursor
FETCH NEXT FROM cBackupCursor INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @BackupFileName = @BackupFilePath + @DBName + '_' + @DateFormat + 'BAK'
BACKUP DATABASE @DBName TO DISK = @BackupFileName WITH STATS=5,COMPRESSION
FETCH NEXT FROM cBackupCursor INTO @DBName
END
CLOSE cBackupCursor
DEALLOCATE cBackupCursor
Step 5: Now generate script of all SQL Agents Jobs for future reference.
Step 6: Now we need to generate script of all logins which are currently available in the instance. For this, we’ll use script which is available on Microsoft official web site.
EXEC [dbo].[sp_help_revlogin]
Step 7: Now we need to generate database wise all users script. We can use below script:
SET NOCOUNT ON;
SELECT 'Generate Script of all Users for ['+DB_Name()+'] database on [' + @@ServerName + '] Server'
DECLARE @SQLQuery varchar(8000),@sort INT
DECLARE curDBWiseUsers CURSOR FOR
/**************************************************************************/
/*******************STATEMENT OF DB CONTEXT***********************/
/**************************************************************************/
SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENT --],
1 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENT --],
1 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT '' AS [-- SQL STATEMENT --],
2 AS [-- RESULT ORDER HOLDER --]
UNION
/**************************************************************************/
/**************************DB USER CREATION**************************/
/**************************************************************************/
SELECT '-- [-- DB USERS --] --' AS [-- SQL STATEMENT --],
3 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT 'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [Name] + '''' +') BEGIN CREATE USER '+ SPACE(1) + QUOTENAME([default_Schema_name]) + SPACE(1) + 'END; ' AS [-- SQL STATEMENT --],
4 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_principals AS rm
WHERE [type] IN ('U','S','G') --U=windows users,S=sql users,G=windows groups
UNION
/**************************************************************************/
/**************************DB ROLE PERMISSIONS**********************/
/**************************************************************************/
SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENT --],
5 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT 'EXEC sp_addrolemember @rolename ='
+ SPACE(1) + QUOTENAME(USER_Name(rm.role_Principal_id),'''')+'@membername = ' +
+ SPACE(1) + QUOTENAME(USER_Name(rm.member_Principal_id),'''') AS [-- SQL STATEMENT --],
6 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_role_members AS rm
WHERE USER_Name(rm.member_Principal_id) IN
(
--Get user name on the database
SELECT [name] FROM sys.database_principals
WHERE [principal_id] > 4 --0 to 4 are system users/schemas
AND [type] IN ('U','S','G')
)
UNION
SELECT '' AS [-- SQL STATEMENT --],
7 AS [-- RESULT ORDER HOLDER --]
UNION
/**************************************************************************/
/******************OBJECT LEVEL PERMISSIONS************************/
/**************************************************************************/
SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENT --],
8 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT CASE
WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' +
QUOTENAME(obj.name)
+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
+ SPACE(1) + 'TO' + + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_ID)) COLLATE Database_Default
+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END
AS [-- SQL STATEMENT --],
9 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_permissions AS perm
INNER JOIN sys.objects AS obj ON perm.major_id = obj.[object_id]
INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_ID = usr.principal_ID
LEFT JOIN sys.columns AS cl ON cl.column_ID = perm.major_id AND cl.[object_id] = perm.major_id
UNION
SELECT '' AS [-- SQL STATEMENT --],
10 AS [-- RESULT ORDER HOLDER --]
UNION
/**************************************************************************/
/**************************DB LEVEL PERMISSIONS*********************/
/*************************************************************************/
SELECT '-- [-- DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENT --],
11 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT CASE
WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
+ SPACE(1) + perm.permission_name + SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default
+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END
AS [-- SQL STATEMENT --],
12 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_permissions AS perm
INNER JOIN sys.database_principals AS usr ON perm.grantee_principal_ID = usr.principal_ID
WHERE perm.major_id = 0
AND usr.principal_ID >4 AND [usr].[type] IN ('U','S','G')
UNION
SELECT '' AS [-- SQL STATEMENT --],
13 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT '-- [-- DB LEVEL SCHEMA PERMISSIONS --] --' AS [-- SQL STATEMENT --],
11 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT CASE
WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default
+ QUOTENAME(SCHEMA_NAME(Major_ID)) + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_ID)) COLLATE database_default
+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1)+ 'WITH GRANT OPTION' END
AS [-- SQL STATEMENT --],
15 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_permissions AS perm
INNER JOIN sys.schemas s ON perm.major_id = s.schema_ID
INNER JOIN sys.database_principals AS dbprin ON perm.grantee_principal_ID = dbprin.principal_ID
WHERE class=3
ORDER BY [-- RESULT ORDER HOLDER --]
OPEN curDBWiseUsers
FETCH NEXT FROM curDBWiseUsers INTO @SQLQuery,@sort
WHILE @@FETCH_Status = 0
BEGIN
PRINT @SQLQuery
FETCH NEXT FROM curDBWiseUsers INTO @SQLQuery, @sort
END
CLOSE curDBWiseUsers
DEALLOCATE curDBWiseUsers
SELECT 'Generate Script of all Users for ['+DB_Name()+'] database has been completed on [' + @@ServerName + '] server.'
Step 7: Generate script of all linked server objects.
Step 8: Now remove all linked server objects from the instance.
USE master;
EXEC sp_dropserver 'LinkedServerObjectName', 'droplogins';Step 9: In this step, we can follow any process like detaching the database & then dropping the database technique or changing the database state from Online to ReadOnly & stopping the SQL services. It depends on the process which we are following in the current project.
Option 1:-
--To get DB file location
SELECT DB_Name(Database_ID) AS [DatabaseName],name as [FileName], physical_name AS [FilePath]
FROM sys.master_files WHERE DB_Name(Database_ID)='AdventureWorks2016'
--To detach the database on the server
USE master;
EXEC sp_detach_db 'DatabaseName', 'true';
-- To remove database related files from the disk
EXEC xp_cmdshell 'DEL E:\Database\AdventureWorks2016_Data.*';
Option 2:-
--To make a database as ReadOnly.
ALTER DATABASE AdvantureWorks SET READ_ONLY WITH ROLLBACK IMMEDIATE;
OR
--To make all databases as ReadOnly in one go.
EXEC sp_msforeachdb ' ALTER DATABASE [?] SET READ_ONLY WITH ROLLBACK IMMEDIATE;'Step 10: Disable All SQL related services.
Step 11: Stop all SQL related services.
Step 12: Copy all script files on the shared location.
Step 13: Update the Inventory table which is available on CMS server.
Step 14: Drop the mail to Tools Team to disable all OS + SQL related alerts for this server / SQL instance.
Drop the mail to Backup Team to disable all DB backups if they are capturing the DB backups using 3rd Party Tools Like Veeam, Networker, Cohesity etc.
Also to disable LUN backup if it is scheduled for the server.
Step 15: Finally drop the mail to all stakeholders/owners for task completion & to validate the related applications.
Also if we have raised any change task, we need to drop the mail to all teams to validate & close their respective task.
Conclusion
A crucial part of IT management is to decommission a server or database server. It demands careful planning and execution to protect data security and organizational integrity. To guarantee that no critical data is lost, this process starts with a thorough examination to determine the server’s role. Next, data migration is done with extreme care. Secure data sanitization is essential to abide by data protection laws and prevent unwanted access. The server is methodically disconnected from the network and physically disassembled after data has been securely migrated and deleted. Environmental responsibility is ensured by properly disposing of hardware or recycling it. To guarantee accountability and openness, thorough documentation and stakeholder communication are maintained throughout the process. Throughout the process, extensive documentation and stakeholder communication are maintained to promote transparency and accountability. By following these steps, enterprises can successfully decommission database servers while avoiding disruptions, ensuring operational continuity, and protecting sensitive data, resulting in a more efficient and secure IT infrastructure.
FAQs
Q: Why we need to decommission a server or database server ?
Ans: The process of retiring or taking out of service a database server that is no longer required is known as decommissioning. It entails archiving or transferring data, securely shutting down the server, and making sure that all related resources are managed correctly.
Q: When is the right time to think about retiring a database server?
Ans: In response, database servers might be decommissioned if they are outdated, no longer needed, or if improvements in hardware or software or adjustments to the organization’s infrastructure render the current server superfluous. In such cases, we can plan to decommission a server/database server.
Q: How does one go about decommissioning a database server?
Ans: Decommission a server/ database server usually entails the following actions:
Notify interested parties and schedule the decommissioning procedure.
Archive and back up your data.
Close the server down.
Take the server out of any network settings.
Record pertinent information and keep it up to date.
Hardware should be disposed of or reused as needed.
Q: How can I guarantee the integrity of my data while it’s being decommissioned?
Ans: The completion of comprehensive backups is essential to decommission a server. Check the backups and make sure the data is stored safely. Verify the archived data’s integrity to ensure that it can be restored if it becomes necessary.
Q: What data security factors need to be taken into account when decommissioning?
Ans: To avoid unwanted access, securely remove sensitive data. Observe data retention guidelines and make sure all applicable laws and regulations are followed. For an extra degree of security, think about encrypting data while it’s being archived.
Q: How can I inform stakeholders about the decommissioning process?
Ans: Provide detailed information about the decommissioning plan, the timeline, and any potential effects on services to stakeholders well in advance of their involvement. During the process, respond to any queries or concerns and maintain open lines of communication.
Q: Can I use the decommissioned server’s hardware for something else?
Ans: If the hardware remains functional, it can be repurposed for alternative purposes within the organization. Before repurposing, make sure that any sensitive data is safely erased.
Q: Which records need to be updated to decommission a server process?
Ans: The documentation about the decommission a server, including inventory records and system configuration files, should be updated. Ensure that the changes are appropriately reflected in any upcoming reference materials.
Q: Regarding dependencies on the decommissioned server, what should I do?
Ans: Determine dependencies and let stakeholders know about them. Provide alternatives or help with the transition to new solutions if applications or services are dependent on the server.
Q: When decommissioning a server, what environmental factors need to be taken into account?
Ans: Make sure the hardware is recycled or disposed of in an environmentally friendly way. Respect local ordinances about the disposal of electronic waste.
Q: How can I make sure the information on the decommissioned server is disposed of safely?
Ans: You should safely get rid of the data kept on the decommissioned server to guarantee data security. There are several ways to accomplish this, including physically demolishing the hard drives or safely wiping them out with specialist software. For information on the best approach, refer to the data security policies and guidelines of your company.
Q: Do I need to preserve any records or paperwork related to the decommissioned server?
Ans: It is crucial to preserve the decommissioned server’s records and documentation for future use, and auditing needs, and to trace the decommissioning process’s history. Information like the decommissioning date, the reason for the decommissioning, any migrated data or services, and any dependencies or services impacted by the decommissioning can all be found in this documentation.
Q: Can we decommission a server or database server without compromising other systems?
Ans: Yes, the impact on other systems can be reduced with careful planning and dependencies analysis.
Q: In what way do T-SQL queries aid in the process of decommissioning?
Ans: To decommission a server or database server, T-SQL queries assist in separating databases and guaranteeing data integrity.
Q: What safety precautions must be taken while decommissioning?
Ans: Preventing unwanted access to confidential data is achieved by taking away access privileges and deactivating user accounts.
Q: Do we need to prepare document to decommission a server or database server?
Ans: Certainly, comprehensive documentation acts as a guide for upcoming audits and decommissioning projects.
Q: What environmental factors should be taken into account when disposing of server hardware?
Ans: Companies should recycle properly to reduce their impact on the environment.
Review the below articles also
Dbcc Freeproccache: A powerful command
Extended Events in SQL Server: A Deep Dive
I think the admin of this web pzge is truly working hard.
In support off his web page, for the reason that here every
material is quality based data.