Patching SQL Server
Log Shipping Environment:
Apply a SQL Service Pack or hot-fix for Primary, Secondary and Monitor servers in a Log-Shipping environment.
Primary SQL Server: DBALABS
Secondary SQL Server: DBALABS-DR
Monitor SQL Server: DC2008\SQLMonitor
a) On Monitor SQL Server: Apply the required SQL Service Pack or Hot-fix on the LS Monitor SQL Server.
1) Post SP/hot-fix installation completion, restart the Monitor windows server if prompted.
b) On Secondary SQL Server:
1) Run all LS Copy & Restore Jobs of all databases involved in Log-Shipping and ensure no log backups are pending to
restore.
2) Disable LS Copy & Restore jobs of all databases involved in Log-Shipping.
3) Apply the SQL Service pack or hot-fix on LS Secondary SQL Server.
4) Post SP/hot-fix installation completion, restart the Secondary windows server if prompted.
c) On Primary SQL Server:
1) Disable LS Backup jobs of all databases involved in Log-Shipping.
2) Apply the SQL Service pack or hot-fix on LS Primary SQL Server.
3) Post SP/hot-fix installation completion, restart the Primary windows server if prompted.
Finally, Enable LS jobs on both Primary & Secondary as follows:
Primary Server -- All LS Backup Jobs of log shipped databases.
Secondary Server -- All LS Copy & Restore Jobs of log shipped databases.
Verify Log-Shipping Reports on both Primary & Secondary Server.
Thanks & Regards
Praveen Madupu
(Sr SQL DBA)
Mb: 91-98661-30093\91-81972-93434
Mail: praveensqldba12@gmail.com
Hyderabad, Telangana-500070.
India.
DB Mirroring Environment:
Steps to Apply a SQL Service Pack or hot-fix for Principal, Mirror and Witness SQL Servers in Database Mirroring
environment.
Principal SQL Server: DBALABS
Mirror SQL Server: DBALABS-DR
Witness SQL Server: DC2008\SQLMonitor
1. Remove the Witness SQL Server from mirroring configuration.
2. Apply the SQL Service Pack to the Mirror Server. (Mirror SQL Server: DBALABS-DR)
3. Post SP/hot-fix installation completion, restart the Mirror Server if prompted.
4. Failover the mirrored databases from Principal to Mirror server.
Post Failover:
New Principal SQL Server: DBALABS-DR
New Mirror SQL Server: DBALABS
5. Now apply the SQL Service pack to the new Mirror Server. (New Mirror SQL Server: DBALABS)
6. Post SP/hot-fix installation completion, restart the New Mirror Server if prompted.
7. Failback the Mirrored databases from Mirror to Principle server.
Post Failback:
Principal SQL Server: DBALABS
Mirror SQL Server: DBALABS-DR
8. Apply the SQL Service Pack to the Witness SQL Server. (DC2008\SQLMonitor)
9. Post SP/hot-fix installation completion, restart the Witness Server if prompted.
10. Finally add the Witness SQL Server back to the DB Mirroring Configuration.
Thanks & Regards
Praveen Madupu
(Sr SQL DBA)
Mb: 91-98661-30093\91-81972-93434
Mail: praveensqldba12@gmail.com
Hyderabad, Telangana-500070.
India.
Replication Environment:
Apply a SQL Service Pack or hot-fix for Publisher, Subscriber and Distributor servers in DB Replication environment.
Publisher SQL Server: DBALABS
Subscriber SQL Server: DBALABS-DR
Distributor SQL Server: DC2008\SQLMonitor
a) On Distributor SQL Server: Apply the required SQL Service Pack or Hot-fix on Distributor SQL Server.
b) On Publisher SQL Server:
1) Apply the SQL Service pack or hot-fix on Publisher SQL Server.
2) Post SP/hot-fix installation completion, restart the Publisher windows server if prompted.
c) On Subscriber SQL Server:
1) Apply the SQL Service pack or hot-fix on Subscriber SQL Server.
2) Post SP/hot-fix installation completion, restart the Subscriber windows server if prompted.
Finally verify Replication Status/health check using Replication Monitoring.
Thanks & Regards
Praveen Madupu
(Sr SQL DBA)
Mb: 91-98661-30093\91-81972-93434
Mail: praveensqldba12@gmail.com
Hyderabad, Telangana-500070.
India.
SQL Cluster Environment:
Apply a SQL Service Pack or hot-fix in a 2 node Active/Passive SQL Cluster environment.
Active SQL Server: DBALABS
Passive SQL Server: DBALABS-DR
1. First apply the SQL Service Pack on Passive Server. (DBALABS-DR)
2. Post SP/hot-fix installation completion, restart the Passive Server if prompted.
3. Failover the SQL Cluster Service/Instance from Active to Passive Server.
Post Failover:
New Active Server: DBALABS-DR
New Passive Server: DBALABS
4. Now apply the SQL Service pack on New Passive Server. (DBALABS)
5. Post SP/hot-fix installation completion, restart the New Passive Server if prompted.
6. Failback the SQL Cluster Service/Instance from Active to Passive Server.
Post Failback:
Active Server: DBALABS
Passive Server: DBALABS-DR
7. Finally verify the SQL Cluster Services and Cluster Disk Health Check in Failover Cluster Manager.
Thanks & Regards
Praveen Madupu
(Sr SQL DBA)
Mb: 91-98661-30093\91-81972-93434
Mail: praveensqldba12@gmail.com
Hyderabad, Telangana-500070.
India.
SQL Always On Environment:
Apply a SQL Service Pack or hot-fix in SQL Server Always-On environment.
Primary SQL Replica: DBALABS
Secondary SQL Replica : DBALABS-DR
Apply SP/Hot-fix on all Secondary AG Replica's
Finally Apply SP/Hot-fix on Primary AG Replica.
1. On Secondary AG Replica: (DBALABS-DR)
Ensure AG Databases are in Synchronized Status.
Apply the SQL Service Pack on Secondary Replica Server. (DBALABS-DR)
Post SP/hot-fix installation completion, restart the Secondary Replica Server if prompted.
2. On Primary AG Replica: (DBALABS)
Failover the SQL AG Group from Primary Replica to Secondary Replica.
3. Post AG Group Failover:
New Primary Replica : DBALABS-DR
New Secondary Replica: DBALABS
4. On New Secondary AG Replica: (DBALABS)
Apply the SQL Service pack on New Secondary Replica Server. (DBALABS)
Post SP/hot-fix installation completion, restart the New Secondary Replica Server if prompted.
5. On New Primary AG Replica: (DBALABS-DR)
Failback the SQL AG Group from Primary to Secondary Replica.
6. Post AG Group Failback:
Primary Replica : DBALABS
Secondary Replica: DBALABS-DR
7. Finally verify the SQL AG Group Health Check in AG DashBoard Reports.
Thanks & Regards
Praveen Madupu
(Sr SQL DBA)
Mb: 91-98661-30093\91-81972-93434
Mail: praveensqldba12@gmail.com
Hyderabad, Telangana-500070.
India.
