Command to execute CHECKDB

DBCC CHECKDB (DBNAME); — This Option use a SNAPSHOT
GO

Command to avoid creating a SNAPSHOT and saving the space on disk by using the option WITH TABLOCK .
By using this option there will be Exclusive locks on database which will prevent accessing database by users
Also dont run this command on Bussiness hours as it will impact the Production. This showuld be run only on Non-Bussiness hours.

DBCC CHECKDB (DBNAME) WITH TABLOCK; — This option does NOT use a SNAPSHOT
GO

The other alternative command to get CHECKDB runs faster and this will reduce the Contention
This Option DBCC CHECKDB with PHYSICAL_ONLY checks physical structure of the page and it will detect the torn pages ,common hardware failures which can compromises the users data.

DBCC CHECKDB (DBNAME) WITH PHYSICAL_ONLY

TempDB Usage and Intensity of I\O

We all know that DBCC CHECKDB is an I\O Intensive operation and major concern is related I/O caused by spooling to TEMPDBDB Database
The process of CHECKDb uses TEMPDB Database heavily and also we may get Performance Impact
To reduce the Impact of Performance we may have to configure disks Properly .So, that we get better throughput

Today we are going discuss about DBCC CHECKDB and its usage .

Database Console Command DBCC checkdb is to check the integrety of Database in SQL Server .This command can also be used in databases which has memory optimized tables but dbcc repair option is not available for memory optimized tables if there is any corruption.In any case if we have an issue in memory optimized tables we may have to restore the data from last known good backup .

when we run DBCC CHECKDB internally it will run various operations which are dicussed below :

DBCC CHECKALLOC

DBCC CHECKTABLE

DBCC CHECKCATALOG

As DBCC checkdb runs all the above three commands it will not require to run CHECKALLOC, CHECKTABLE and CHECKCATALOG command s individually

Syntax of DBCC CHECKDB

DBCC CHECKDB
[ ( database_name | database_id | 0
[ , NOINDEX
| , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
) ]
[ WITH
{
[ ALL_ERRORMSGS ]
[ , EXTENDED_LOGICAL_CHECKS ]
[ , NO_INFOMSGS ]
[ , TABLOCK ]
[ , ESTIMATEONLY ]
[ , { PHYSICAL_ONLY | DATA_PURITY } ]
[ , MAXDOP = number_of_processors ]
}
]
]

Parameters used in above syntax signify the following:

database_name | database_id | 0

means you need to provide Database Name or Database ID . If 0 is specified this means it will use current database to run the integrity checks

NOINDEX

Means it will not check non clustered indexes for User Tables . By using this option we can decrease the execution time of job .

REPAIR_ALLOW_DATA_LOSS

Means this repairs the error which were found in database but this will lead to loss of DATA
So, use this as Last option

REPAIR_FAST

Means maintains backward compatibility and does not perform any repairing.

REPAIR_REBUILD

Means repairs the database faster also it does not impose any loss of data

ALL_ERRORMSGS

Means this option displays all error messages of objects.By default error messages are dispayed in output so, speficifyng or without specifying this option is same

EXTENDED_LOGICAL_CHECKS

Means runs a logical consistency checks on views and indexes if the compatability Level is 100 or higher.

NO_INFOMSGS

Means all informational messages are supressed

TABLOCK

This applies execlusive locks on database instead of using an internal snapshot .Running DBCC CHECKDB with TABLOCK runs faster when we have heavy work loads but this will decrease the availability of database when CHECKDB is running

ESTIMATEONLY

This option tells us the estimated amount of space require on database for checkdb to run.

PHYSICAL_ONLY

checks the integrity of physical structure of the page, record headers and the allocation consistency of the database
And it can also detect torn pages, checksum failures, and common hardware failures of Users data.

Note :

Running Full DBCC CHECKDB command may take longer time to execute because it will do logical checks which are more complex
So, using PHYSICAL_ONLY option will reduce the execution time of CHECKDB on large databases.
It is also recommend to use PHYSICAL_ONLY option when runnign DBCC CHECKDB Frequently on PRODUCTION servers.
But it is still necessary to run FULL DBCC CHECKDB command at least once in a week .
This all will depends on individual environments.

DATA_PURITY

Checks the database for column values that are not valid

Error Summary :

When CHECKDB Command detects corruption dump file named SQLDUMPNNNN.txt is created in the log directory of SQL server.

Restore Database :

When we have a scenario of error in sql server . MS recommended to restore the Database from Last known Good Backup instead of repairing the Database.
If there is no backup available , then go for repair option but using the repair with REPAIR_ALLOW_DATA_LOSS will lead to DATA LOSS.

When we perform Side-by-Side Migrations there are times where we need to have a look at Windows Firewall Rules for troubleshooting the Issues.

Also if we want to check what FW rules are currenlty in Placed or Enabled

By using Get-NetFirewallRule command we can get list of FW rules which are already enabled

Almost a year back I saw the script online and saved in my folder but I used this Script now in my current orgnaisation and helped this script today for me .So, wanted to share it online and can have others if they don’t have it.. Sorry for not providing the Author Name here as I was not noted at that time

Note : Used this script and working fine on Windows server 2016 , I haven’t tried it in other Windows Version ..If you are trying lower version please do TEST it and then use it .

Retrieve a list of Firewall Rules

Get-NetFirewallRule
Get-NetFirewallRule -Direction Inbound | Select-Object -Property DisplayName,Profile,Enabled

Filter the options for Inbound Rule and select a few properties and sort the list

$InboundRules = Get-NetFirewallRule -Direction Inbound | Select-Object -Property DisplayName,Profile,Enabled
$InboundRules | Sort-Object -Property DisplayName

Filter the options a bit more – Inbound Rules for the Domain profile

$InboundRulesDomain = Get-NetFirewallRule -Direction Inbound | Where-Object {$_.Profile -EQ “Domain” -or $_.Profile -EQ “Any”}
$InboundRulesDomain | Select-Object -Property DisplayName,Profile,Enabled | Sort-Object -Property DisplayName
$InboundRulesDomain | Select-Object -Property DisplayName,Profile,Enabled | Export-Csv -Path “C:\FireWallReport.csv”
Import-Csv -Path “C:\FireWallReport.csv” | Out-GridView

Retrieve details about protocol and port information from the same data

$InboundDomainPorts = $InboundRulesDomain | Get-NetFirewallPortFilter | Select-Object -Property InstanceID,Protocol,LocalPort,RemotePort
$InboundDomainPorts | Sort-Object -Property InstanceID
$InboundDomainPorts | Export-Csv -Path “C:\FireWallPortReport.csv”
Import-Csv -Path “C:\FireWallPortReport.csv” | Out-GridView

In Alwayson Availability Group replicas the SQL server is installed on all the participating replicas and the SQL services will also be in running state on all replicas
Where we have One PRIMARY and multiple SECONDARY replicas depending on your environment.

Here in our environment we have 3 Node HADR Setup , the SQL Servies runs on all 3 replicas and one Replicas is PRIMARY and the other two replicas are SECONDARY ( 1 is Local replica with SYNCHRONOUS Mode and 1 is DR replica with ASYNCHRONOUS Mode).

Pre-requisites:

1)First need to check the current patch Level and target patch Level( N-1 where N is Latest Patch).
For Latest Patch Level which was available in market we can check on SQL server blogs.
2)Don’t apply patches directly to your production environment. First apply it to your lower environments, wait for atleast a week and so then schedule it to apply for Production environment.
3) In that one week the patch will be TESTED in lower environments by Application team
once validation is completed successfully , we can apply it on production environment.

Steps to be taken Before applying the Patch to SQL Instance:

1) Make sure to check we have Latest Full backup available for all SYSTEM and USER Databases on PRIMARY Replica.If the User Databases Sizes are SMALL better take fresh Full backup for all Databases else if the DB size is more then check lastest valid FULL backup is available and then take atleast Differential or Transactional Log backup before starting the SQL Patches
2) On Secondary replicas take SYSTEM Databases Backup.
3) Check the AG Dashboard, make sure it shows all GREEN and HEALTHY.

Applying Patch to SQL Instances:

As I said in above, we have 3 Node HADR Setup ( 2 local replicas with Synchronous Mode and 1 DR with Asynchronous Mode )

1) First we will apply patch to LOCAL replica of Secondary Node
2) To do that we need to change Failover Mode from AUTOMATIC to MANUAL because just to be sure that no automatic failover happens to secondary when we are applying patch incase of any issues on PRIMARY .
3) Also SUSPEND the DATA movement on currently patching secondary replica as if you do it on PRIAMRY it will SUSPEND moving the DATA for all the secondary replicas.
4) Now start applying the Patch on Secondary replica and restart the Server after applying the Patch.
5) Once the secondary replica comes ONLINE then do below check :
. Verify the SQL Services are UP and RUNNING
. Verify the SQL Version and see newly applied patch is reflecting .
. verify the SQL Error Logs for any error messages.
. Check all USER Databases are HEALTHY and ONLINE.
6) Now RESUME the DATA movement on Secondary replica and wait till the Database gets SYNCHRONIZED.
7) Now check th AG Dashboard , make sure it shows all GREEN and HEALTHY.
8) Now perform MANUAL failover from PRIMARY to newly patched Secondary replica and make sure the AG dashboard showing all GREEN and HEALTHY on newly patched replica which is currently PRIMARY.
9) Now follow above steps to proceed Patching .
10) once it is completed, Failback the AG to previous PRIMARY replica and check Dashboard is showing as all GREEN and HEALTHY.
11) Now apply patch to DR replica by following above steps.

After Patching all 3 replicas, validate the following :

. Verify the SQL Verison was updated on all 3 replicas which are participating in AlwaysON configuration.
. Verify the AG Dashboard, make sure all shows GREEN and HEALTHY.
. check the SQL Error Logs for any Error messages on all 3 replicas.
. Inform to APPLICATION Team for App Validation and functionality.