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

 

POA(Plan of Action) for Database Migration in SQL Server

	Pre Migration Checklist
Sr.No.
1	Analyze the disk space of the target server for the new database.
2	Confirm the data and log file location for the target server.
3	Collect the information about the Database properties (Auto Stats, DB Owner, Recovery Model, Compatibility level,etc).
4	Collect the information of dependent applications, make sure application services will be stopped during the database migration.
5	Collect the information of database logins, users and their permissions.
6	Check the database for the Orphan users if any.
7	Check the SQL Server for any dependent objects (SQL Agent Jobs and Linked Servers).
8	Check, if the database is part of any maintenance plan.
	Database Migration Checklist
Sr.No.
1	Stop the application services.
2	Change the database to single user mode.
3	Take the latest backup of all the databases involved in migration.
4	Stop the SQL Services on live server.
5	Copy the backup from live to destination server.
6	Restore the databases on the target server on the appropriate drives.
7	Cross check the database properties as per pre-migration checklist output.
8	Execute the output of Login transfer script on the target server, to create logins on the target server.
9	Check for Orphan Users and Fix Orphan Users.
10	Execute DBCC UPDATEUSAGE on the restored database.
11	Rebuild Indexes ,As per the requirement.
12	Update statistics.
13	Recompile procedures.
14	Configure Full backup, Log backup, integrity check, rebuild index jobs.
	Post Migration Checlklist
Sr.No.
1	Check the integrity of database.
2	Start the application services, check the application functionality.
3	Check the SQL Server Error Log for login failures and other errors.

ISSUE 4: FIND OUT TABLE & INDEX SIZE

Create the temp table for further querying

CREATE TABLE #temp (

            rec_id               int IDENTITY (1, 1),

            table_name      varchar(128),

            nbr_of_rows    int,

            data_space      decimal(15,2),

            index_space     decimal(15,2),

            total_size         decimal(15,2),

            percent_of_db decimal(15,12),

            db_size            decimal(15,2))

 

2.      Get all tables, names, and sizes

EXEC sp_msforeachtable @command1="insert into #temp (no_of_rows, data_space, index_space) exec sp_mstablespace '?'", @command2="update #temp set table_name = '?' where rec_id = (select max(rec_id) from #temp)"

3.      Set the total_size and total database size fields

UPDATE #temp SET total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #temp)

4.      Set the percent of the total database size

UPDATE #temp SET percent_of_db = (total_size/db_size) * 100

5.       Get the data

SELECT *FROM #temp ORDER BY total_size DESC

6.       Comment out the following line if you want to do further querying

DROP TABLE #temp

ISSUE 3: SHRINKDATABASE (DIFFERENT PROCESS)

If you ever want to transfer a large DB to a new one with more than one file, here is the way I am going to use (tested and approved)

1. Create a file which is as large as the data in your primary file (call it "buffer")

2. Empty the primary file (DBCC SHRINKFILE (<FILENAME>, EMPTYFILE))

3. Restart SQL Server Engine

4. Shrink the primary file to the Data size divided by the number of files you're gonna create (DBCC SHRINKFILE (<FILENAME>, NEWSIZE))

5. Create all the new files with the size of data divided by the number of files

6. Restrict their growth in order to fill the primary file in the next operation

7. Empty the buffer file (DBCC SHRINKFILE (BUFFER, EMPTYFILE))

8. Delete the buffer file (ALTER DATABASE REMOVE FILE (NAME=BUFFER))

9. Set final size of data files and unrestrict their growth according to the final configuration needed

ISSUE 2: MOVING MASTER DATABASE

 

1. Create two folders and grant read write permissions to service account

            d:\master_data

            e:\master_log

2. Find the current path

            sp_helpdb   master

3. Stop SQL Server

4. Move the files (master.mdf, mastlog.ldf) into new folders

5.  Go to SSCM --> R.C on respective instance SQL Server Service -- properties -->   Advanced --> Startup Parameters--> Change the    path of data and Log file

            -dd:\master_data\master.mdf;-e....

            -le:\master_log\mastlog.ldf

6. Apply --> OK

7. Start the service. Go to SSMS --> check the new path

            sp_helpdb master

 

Recovering a SQL Server Database Marked as Suspect

When a SQL Server database enters suspect mode, it becomes inaccessible, indicating potential data corruption or resource issues. This guide explains why databases enter suspect mode and provides actionable recovery steps for common scenarios.

Why Does a Database Enter Suspect Mode?

A database may be marked as suspect due to:

Missing or unavailable database files (e.g., accidental deletion).

Full transaction log or data file (no free space for operations).

Corrupted database files (hardware failure, unexpected shutdown).

Resource locks (OS or another process holding files hostage).

How to Recover a Suspect Database

Below are recovery methods for three common scenarios:

Reason for suspect mode:

If one or more database files are not available.

2.   If the entire database is not available.

3.   If one or more database files are corrupted.

4.   If a database resource is being held by the operating system.

How to recover?

Ø  Scenario 1: If the file is full

Execute sp_resetstatus.

Syntax: sp_resetstatus database_name

Use ALTER DATABASE to add a data file or log file to the database.

Stop and restart SQL Server.

With the extra space provided by the new data file or log file, SQL Server should be able to complete recovery of the database

Ø  Scenario2: If the data file was damaged.

            * Take T.Log backup

            * Restore last Full backup

            * Restore T.Log backup

            * Database comes online

Ø  Scenario3: If the T.Log file was damaged

            * Take any user defined db for example: MyDB

            * Check the current location of files

                        sp_helpdb MyDB

            * Stop server

            * Move the T.Log file into different folder

            * Start server --> DB goes into suspect mode

               Select databasepropertyex ('mydb','status')

 Steps to Recover:

Step1: Make the db into single user

1) Alter database mydb set Single_User

Step2: Set the db into emergency mode

2) Alter database mydb set Emergency

Step3: Run checkdb with required repair level

3) DBCC CheckDB ('mydb', REPAIR_ALLOW_DATA_LOSS)

Step4: Set the db into multi user mode

4) Alter database mydb set Multi_User