Troubleshooting master database corrupted in SQL Server

 One of an instance master database data file was corrupted and I was unable to start the server. How to troubleshoot this scenario?

Possible Scenarios

If the master files are corrupted or damaged, instance cannot be started. We have to rebuild master database then by running the server in single user mode we have to restore latest backup to get previous settings.

Steps

1.      Check the error log for exact reason.

2.      Rebuild master database as follows by running setup from

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release

For windows authentication:

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=<instance
name> /SQLSYSADMINACCOUNTS=<accounts>

 

For mixed mode:

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=<instance
name> /SQLSYSADMINACCOUNTS=<accounts> /SAPWD=password

3.      Once rebuilding is completed then run the server in single user mode

4.   Restore master database by replacing existing one.

Restart the server in multi user mode.

Troubleshooting host name changes in SQL Server

 When the machine name is changed where we have installed SQL Server, all the instances services are started but replication, Jobs, Alerts, Maintenance plans causes errors. Hence we have to rename the instance.

To rename instance we can use the following SP

Steps:

1.      Check the old server name as follows

SELECT @@servername

2.      Drop the server and add the new server name

             SP_DROPSERVER   <oldName>

           SP_ADDSERVER <newName>, local

3.      Restart the instance

4.      Check the server name again

SELECT @@servername

Applying a SQL Service Pack or Hotfix in a 2-Node Active/Passive SQL Cluster Environment

Applying a SQL Service Pack or Hotfix in a 2-Node Active/Passive SQL Cluster Environment

When managing a SQL Server Cluster, applying service packs or hotfixes requires careful execution to avoid downtime and ensure high availability. Follow this step-by-step guide to apply a SQL Service Pack (SP) or hotfix in a 2-node Active/Passive SQL Cluster environment.

Cluster Configuration:

Active SQL Server: SQLDBANOWDBALABS

Passive SQL Server: SQLDBANOWDBALABS-DR

Step by Step update Process:-

1. Apply the SQL Service Pack on the Passive Server

Log in to SQLDBANOWDBALABS-DR (Passive Node).

Initiate the SQL Service Pack/Hotfix installation.

Follow the installation wizard, ensuring all necessary components are updated.

Once the installation is complete, restart the Passive Node if prompted.

2. Failover SQL Cluster to the Passive Server

Open Failover Cluster Manager.

Select the SQL Server Cluster Role (SQLDBANOWDBALABS instance).

Initiate a failover to move the active role to the Passive Node (SQLDBANOWDBALABS-DR).

Post Failover:

New Active Server: SQLDBANOWDBALABS-DR

New Passive Server: SQLDBANOWDBALABS

3. Apply the SQL Service Pack on the New Passive Server

Log in to SQLDBANOWDBALABS (Now Passive Node).

Initiate the SQL Service Pack/Hotfix installation.

Follow the installation wizard and complete the update process.

Restart the server if prompted.

4. Failback SQL Cluster to the Original Active Server

Open Failover Cluster Manager.

Select the SQL Server Cluster Role (SQLDBANOWDBALABS-DR instance).

Initiate a failover to move the active role back to SQLDBANOWDBALABS.

Post Failback:

Active Server: SQLDBANOWDBALABS

Passive Server: SQLDBANOWDBALABS-DR

5. Perform Post-Upgrade Health Checks

Verify SQL Cluster Services:

Ensure all cluster resources are online.

Check Failover Cluster Manager:

Validate Cluster Nodes’ status.

Ensure Cluster Disk Health is optimal.

Review SQL Server logs for any errors.

Test application connectivity and functionality.

How to apply patches in SQL Server Always On Environment

 SQL Always On Environment:

Apply a SQL Service Pack or hot-fix in SQL Server Always-On environment.

Primary SQL Replica: SQLDBANOWDBALABS

Secondary SQL Replica: SQLDBANOWDBALABS-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: (SQLDBANOWDBALABS-DR)

Ensure AG Databases are in Synchronized Status.

Apply the SQL Service Pack on Secondary Replica Server. (SQLDBANOWDBALABS-DR)

Post SP/hot-fix installation completion, restart the Secondary Replica Server if prompted.

2. On Primary AG Replica: (SQLDBANOWDBALABS)

Failover the SQL AG Group from Primary Replica to Secondary Replica.

3. Post AG Group Failover:

New Primary Replica : SQLDBANOWDBALABS-DR

New Secondary Replica: SQLDBANOWDBALABS

4. On New Secondary AG Replica: (SQLDBANOWDBALABS)

Apply the SQL Service pack on New Secondary Replica Server. (SQLDBANOWDBALABS)

Post SP/hot-fix installation completion, restart the New Secondary Replica Server if prompted.

5. On New Primary AG Replica: (SQLDBANOWDBALABS-DR)

Failback the SQL AG Group from Primary to Secondary Replica.

6. Post AG Group Failback:

Primary Replica : SQLDBANOWDBALABS

Secondary Replica: SQLDBANOWDBALABS-DR

7. Finally verify the SQL AG Group Health Check in AG DashBoard Reports.

Step by Step Process for DB Migration in SQL Server

 

Step by Step Process for DB Migration (Side by Side)

Step 1: Once the Target VM is ready, we need to install the SQL Version based on the recommendations or SOP.

Step 2: Take a backup from the Source and Copy to the Target and restore with Recovery option

Step 3: Move all the logins (by using sp_help_revlogin), Jobs, Operators, DB Mail Configurations and Server Configurations

Step 4: Move all the linked servers and if any Proxy accounts

Step 5: Enable inbound/outbound firewall rules for SQL Server/Agent Service and Browser Service

Step 6: Handover the Server to Test their application

Step 7: Once Application team confirmed, everything looks good then will align with all the stake holders/application team and Business Users and required teams and finalize the cutover date for actual migration

On the Day of Cutover, follow the below steps

Step 1: Inform to Application team to bring down the app services

Step 2: Disable if any backup policies enabled from the Third-Party Tools like EMC, NETWORKER or Veeam or Commvault

Step 3: Disable the Backup account if any

Step 4: Initiate Full backups for all the databases to Native Drive or Shared Location, Keep the backup file name in Standard Format like ServerName_DB_Name_Type_Date.bak\trn

Step 5: Connect to the target server and map the shared location or copy the backup files from the folder

Step 6: Restore the databases with No Recovery if it is a production, because if we want to take a log backup from source and then copy those t-log backups to target server and restore with recovery option

Step 7: Once log backups are restored, verify all the databases are online or not, if not take necessary action

Step 8: Fix Orphan Users by funding the sp_change_users_login ‘Report’

Step 9: Verify the Linked Servers Connections

Step 10: Verify the DB Owner Data details and change if needed and change the compatibility

Step 11: Verify the Job owner details and take necessary action if required

Step 12: Handover to the Application for Validation

Once DB Activities are completed, App team will modify their connection strings and Network team will make the ns changes and update to app team to validate

Step 13: App team will validate and confirm whether application is working or not. If any login issues troubleshoot and fix