Always on (AGL) Status Report

-----This script will show the status of the Alway On replication status

SELECT DISTINCT
primary_replica as 'Primary Server',
[endpoint_url] as 'End Point URL',
primary_recovery_health_desc as 'Primary Server Health Status',
secondary_recovery_health_desc as 'Secondary Server Health Status',
operational_state_desc as 'Operational State',
connected_state_desc as 'Connection State',
recovery_health_desc as 'Recovery Health',
synchronization_state_desc as 'Synchronization State',
database_state_desc as 'Database State',
JOIN_state_desc as 'Join State',
suspend_reason_desc as 'Suspended Reason',
availability_mode_desc as 'Availability Mode',
failover_mode_desc as 'Failover Mode',
primary_role_allow_connections_desc as 'Primary Connections Allowed',
secondary_role_allow_connections_desc as 'Secondary Connections Allowed',
create_date as 'Date Created',
modify_date as 'Date Modified',
[backup_priority] as 'Backup Priority',
role_desc as 'Role Type',
last_connect_error_description as 'Last Connection Error',
last_connect_error_timestamp as 'Last Connection Error Time',
last_sent_time as 'Last Data Send Time',
last_received_time as 'Last Data Recieved TIme',
last_hardened_time as 'Last Hardened Time',
last_redone_time as 'Last Redone Time',
log_send_queue_size as 'Log Send Queue Size',
log_send_rate as 'Log Send Rate',
redo_queue_size as 'Redo Queue Size',
redo_rate as 'Rate of Redo',
filestream_send_rate as 'Filestream Send Rate',
last_commit_time as ' Last Commit Time',
low_water_mark_for_ghosts as 'Low Water Mark for Ghosts'
FROM sys.dm_hadr_availability_group_states
JOIN sys.availability_replicas
ON sys.dm_hadr_availability_group_states.group_id = sys.availability_replicas.group_id
JOIN sys.dm_hadr_availability_replica_cluster_states
ON sys.dm_hadr_availability_group_states.group_id = sys.dm_hadr_availability_replica_cluster_states.group_id
JOIN sys.dm_hadr_availability_replica_states
ON sys.dm_hadr_availability_group_states.group_id = sys.dm_hadr_availability_replica_states.group_id
JOIN sys.dm_hadr_database_replica_states
ON sys.dm_hadr_availability_group_states.group_id = sys.dm_hadr_database_replica_states.group_id
WHERE operational_state_desc IS NOT NULL
AND database_state_desc IS NOT NULL
ORDER BY [endpoint_url] DESC

— Testing section
/*
— Suspend replication from primary
ALTER DATABASE [AdventureWorksLT2008] SET HADR SUSPEND
— Resume replication from secondary
ALTER DATABASE [AdventureWorksLT2008] SET HADR RESUME
— Force a manual failover of replication with data loss. MUST EXECUTE ON SECONDARY SQL SERVER
ALTER AVAILABILITY GROUP [AG-AdventureWorksLT2008] FORCE_FAILOVER_ALLOW_DATA_LOSS;
— Force a manual failover of replication with NO data loss MUST EXECUTE ON SECONDARY SQL SERVER
— YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
–Connect XXXXXX
ALTER AVAILABILITY GROUP [AG-AdventureWorksLT2008] FAILOVER;
GO
— YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
–:Connect XXXXXX
ALTER AVAILABILITY GROUP [AG-AdventureWorksLT2008] FAILOVER;
GO
*/

Unable to load user-specified certificate. The server will not accept a connection. You should verify that the certificate is correctly installed.

Today we had been asked by one of our stake holders where one of their DB server was down.Our team started investigating this issue, where we are not able to start SQL services and it keeps failing with errors. As we know error log is the one of the best place to check more details about the problem. Below is the our error log information.


2018-09-17 00:18:21.78 spid8s Server name is 'XXXXXXXX'. This is an informational message only. No user action is required.
2018-09-17 00:18:22.03 spid15s Error: 26014, Severity: 16, State: 1.
2018-09-17 00:18:22.03 spid15s Unable to load user-specified certificate [Cert Hash(sha1) "EFE72795A35AB8AC5096A929A3DE091CC3ED0A30"]. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online.
2018-09-17 00:18:22.07 spid15s Error: 17182, Severity: 16, State: 1.
2018-09-17 00:18:22.07 spid15s TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.
2018-09-17 00:18:22.07 spid15s Error: 17182, Severity: 16, State: 1.
2018-09-17 00:18:22.07 spid15s TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.


Above lines of ERRORLOG has interesting message “Unable to load user-specified certificate [Cert Hash(sha1) “EFE72795A35AB8AC5096A929A3DE091CC3ED0A30”]. The server will not accept a connection. You should verify that the certificate is correctly installed. See “Configuring Certificate for Use by SSL” in Books Online”

The certificate hash value if picked via “Certificate” registry key and once value is picked, certificate store is checked for the certificate (type, subject, thumbprint etc. would be checked)

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.\MSSQLServer\SuperSocketNetLib

Below is the registry key on our supported server .

Resolution :

If you are not using a certificate, then you can go ahead and clean up the value in the registry like below.

If you are using the certificate, then make sure it is installed correctly and it is having right thumbprint, subject etc.>/br>
Reference: https://blog.sqlauthority.com

Differene between affinity mask and max degree of parallelism

Affinity mask is instance level configuration
Degree of Parallelism is Query level configuration
----------------------------------------------------
If you have Multiple physical processors or logical Cores in your machine .It is very easy to understand the benefit of Afinitymask.For example u have 4 cores and 2 instances in your machine then ,u can share processors for each instance like 2 cores per instance.(for better resource balance).


Maxdop is for query execution purpose. By default it will be on for all processors.(All processors will work on one query execution).You can change this behavior for particular query like alter index .for ex if u give maxdop =2 while executing query ,2 processors will dedicated for this query execution where as other processors can handle server work load.

Subscriber marked as inactive/Expired

If database replication has been disconnected for too long, the subscription may become inactive/Expired/Not Running Expiring Soon. The error message may read: "The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated. The step failed."

To fix the problem, you can either delete the subscription and recreate,But this will need downtime. We can simply execute the below scripts and reactivate the subscription.

To Re-active all the subscription's :
----------------------------------------
select status,* from distribution..MSsubscriptions
update distribution..MSsubscriptions set status=2

(or)
Use Distribution
update dbo.MSsubscriptions set status=2 where status=0

After running the update statement, refresh the jobs and make sure the status on the REPL-Distribution job shows Executing.

Script to get Replication Configuration Information

My company uses SQL Server replication for moving data between servers and we have several publications with many articles being replicated. Its very difficult to find what tables are being replicated, what databases are published, what servers are subscribers, etc.... We can get it through management studio but again its time consuming. Below scripts is very helpful and it will give all the information about replication. We need to execute this script on distributed DB/server.

USE Distribution
GO
-- Get the publication name based on article
SELECT DISTINCT
srv.srvname publication_server
, a.publisher_db
, p.publication publication_name
, a.article
, a.destination_object
, ss.srvname subscription_server
, s.subscriber_db
, da.name AS distribution_agent_job_name
FROM MSArticles a
JOIN MSpublications p ON a.publication_id = p.publication_id
JOIN MSsubscriptions s ON p.publication_id = s.publication_id
JOIN master..sysservers ss ON s.subscriber_id = ss.srvid
JOIN master..sysservers srv ON srv.srvid = p.publisher_id
JOIN MSdistribution_agents da ON da.publisher_id = p.publisher_id
AND da.subscriber_id = s.subscriber_id
ORDER BY 1,2,3