Patching stand-alone MSSQL Server

a)Add servers to maintenance window of Zabbix to suppress alerts

b)Check data directory of registry

 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.<instance_name>\Setup\SQLDataRoot

c)Backup database with copy_only mode 

select 'backup database ['+name+']
to disk = ''F:\20240701\backup\'+name+'.bak'' with copy_only,COMPRESSION'
from sys.databases
where database_id not in(2);
  • 1.
  • 2.
  • 3.
  • 4.

d)Stop application if needed (confirm with application owner)

e)Pathing MSSQL Server and restart server if needed

f)Start application if needed(confirm with application owner)

g)Remove servers from maintenance window of Zabbix to active alerts

h)Check status of database and data synchronization

i)Contact application owner to check status of application

Patching Mirror-topology MSSQL Server (Primary:Node_A,Mirror:Node_B)

a)Add Node_A and Node_B to maintenance window of Zabbix to suppress alerts

b)Check data directory of registry

 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.<instance_name>\Setup\SQLDataRoot

c)Backup databases on Node_A with copy_only mode 

select 'backup database ['+name+']
to disk = ''F:\20240701\backup\'+name+'.bak'' with copy_only,COMPRESSION'
from sys.databases
where database_id not in(2);
  • 1.
  • 2.
  • 3.
  • 4.

d)Stop application if needed (confirm with application owner)

e)Suspend mirror from Node_A

select 'use [master]
ALTER DATABASE ['+db_name(database_id)+'] SET PARTNER SUSPEND' from sys.database_mirroring where mirroring_state is not null;
  • 1.
  • 2.

f)Pathing Node_B and restart server if needed

g)Resume mirror from Node_A

select 'use [master]
ALTER DATABASE ['+db_name(database_id)+'] SET PARTNER RESUME' from sys.database_mirroring where mirroring_state is not null;
  • 1.
  • 2.

h)Failover mirror to Node_B

select 'use [master]
ALTER DATABASE ['+db_name(database_id)+'] SET PARTNER FAILOVER' from sys.database_mirroring where mirroring_state is not null;
  • 1.
  • 2.

i)Suspend mirror from Node_B 

select 'use [master]
ALTER DATABASE ['+db_name(database_id)+'] SET PARTNER SUSPEND' from sys.database_mirroring where mirroring_state is not null;
  • 1.
  • 2.

j)Patching node_A and restart server if needed

k)Resume mirror from Node_B 

select 'use [master]
ALTER DATABASE ['+db_name(database_id)+'] SET PARTNER RESUME' from sys.database_mirroring where mirroring_state is not null;
  • 1.
  • 2.

l)Failove mirror to Node_A  

select 'use [master]
ALTER DATABASE ['+db_name(database_id)+'] SET PARTNER FAILOVER' from sys.database_mirroring where mirroring_state is not null;
  • 1.
  • 2.

m)Start application if needed(confirm with application owner)

n)Remove Node_A and Node_B from maintenance window of Zabbix to active alerts

o)Check status of database and data synchronization

p)Contact application owner to check status of application


Patching Mirror&log shipping-topology MSSQL Server(Primary:Node_A,Mirror:Node_B, Logshipping:Node_C)

a)Add Node_A ,Node_B and Node_C to maintenance window of Zabbix to suppress alerts

b)Check data directory of registry

 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.<instance_name>\Setup\SQLDataRoot

c)Backup databases on Node_A with copy_only mode 

select 'backup database ['+name+']
to disk = '' F:\20231209\backup\'+name+'.bak '' with copy_only,COMPRESSION'
from sys.databases
where database_id not in(2);
  • 1.
  • 2.
  • 3.
  • 4.

d)Stop application if needed (confirm with application owner)

e)Change log shipping mode from read_only to norecovery on Node_C 

EXEC sp_change_log_shipping_secondary_database @secondary_database = 'XXXX',
@restore_mode = 0,@disconnect_users = 0;

SELECT secondary_database,restore_mode,
disconnect_users,last_restored_file
FROM msdb.dbo.log_shipping_secondary_databases
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.

f)Patching Node_C and restart server if needed

g)Stop log backup job on Node_A

h)Suspend mirror from Node_A 

select 'use [master]
ALTER DATABASE ['+db_name(database_id)+'] SET PARTNER SUSPEND' from sys.database_mirroring where mirroring_state is not null;
  • 1.
  • 2.

i)Pathing Node_B and restart server if needed

j)Resume mirror from Node_A 

select 'use [master]
ALTER DATABASE ['+db_name(database_id)+'] SET PARTNER RESUME' from sys.database_mirroring where mirroring_state is not null;
  • 1.
  • 2.

k)Failover mirror to Node_B 

select 'use [master]
ALTER DATABASE ['+db_name(database_id)+'] SET PARTNER FAILOVER' from sys.database_mirroring where mirroring_state is not null;
  • 1.
  • 2.

l)Suspend mirror from Node_B 

select 'use [master]
ALTER DATABASE ['+db_name(database_id)+'] SET PARTNER SUSPEND' from sys.database_mirroring where mirroring_state is not null;
  • 1.
  • 2.

m)Patching node_A and restart server if needed

n)Resume mirror from Node_B 

select 'use [master]
ALTER DATABASE ['+db_name(database_id)+'] SET PARTNER RESUME' from sys.database_mirroring where mirroring_state is not null;
  • 1.
  • 2.

o)Failove mirror to Node_A  

select 'use [master]
ALTER DATABASE ['+db_name(database_id)+'] SET PARTNER FAILOVER' from sys.database_mirroring where mirroring_state is not null;
  • 1.
  • 2.

p)Change log shipping mode backup from norecovery to read_only 

EXEC sp_change_log_shipping_secondary_database
@secondary_database = 'XXXX',
@restore_mode =1, @disconnect_users = 0  

SELECT secondary_database,
restore_mode,
disconnect_users,
last_restored_file
FROM msdb.dbo.log_shipping_secondary_databases
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.

q)Start application if needed(confirm with application owner)

r)Remove Node_A,Node_B and Node_C from maintenance window of Zabbix to active alerts

s)Check status of database and data synchronization

t)Contact application owner to check status of application