Simon's SQL

Title : Can SQL server run on Kuberentes?

https://passdatacommunitysummit.com/sessions/268901

/***********************************************************************************************
**Object Name: AG_Monitor
**
**Description: Check AG Status
**
**Input Parameters: 
**
**Return Value: N/A
**
**Return Result Set: AG Monitoring result
**
**Creator: Simon Cho
**
*************************************************************************************************/

--<ReplicaLevel>

SELECT replica_server_name
	 , d.role_desc
	 , d.connected_state_desc
	 , d.replica_id
	 , d.role_desc
	 --, endpoint_url
	 --, availability_mode_desc
	 --, failover_mode_desc
	 --, session_timeout
	 --, backup_priority
	 --, secondary_role_allow_connections_desc AS Secondary_Readable
	 , Pri_Check.pri_Status
	 --, Pri_Check.Sec_Status
	 --, Pri_Check.Sync_Status
	 , d.operational_state_desc
	 , d.recovery_health_desc
	 , d.synchronization_health_desc AS Sync_Status
	 , d.last_connect_error_description AS ErrorMsg
	 , DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()), d.last_connect_error_timestamp) AS ErrorDateTime
  FROM sys.availability_replicas r
  OUTER APPLY (
  SELECT 1 AS IsPrimary
	   , c.ip_address AS listnerIP
	   , c.state_desc AS listnerStatus
	   , a.primary_recovery_health_desc AS pri_Status
	   , a.secondary_recovery_health_desc AS Sec_Status
	   , a.synchronization_health_desc AS Sync_Status
	   
    FROM sys.dm_hadr_availability_group_states a
	JOIN sys.availability_group_listeners B
	  ON A.group_id = B.group_id
	JOIN sys.availability_group_listener_ip_addresses c
	  ON b.listener_id = c.listener_id
    WHERE primary_replica = r.replica_server_name
	) Pri_Check
	JOIN sys.dm_hadr_availability_replica_states d
	  ON r.replica_id = d.replica_id
 --WHERE d.role NOT IN (1,2) -- 1:Primary, 2:Secondary, 0:Resolving
 --   OR d.operational_state <> 2 --0 = Pending failover, 1 = Pending, 2 = Online, 3 = Offline, 4 = Failed, 5 = Failed, no quorum
	--OR d.recovery_health <> 1 --0:In progress. At least one joined database has a database state other than ONLINE ( database_state is not 0).1- Online. All the joined databases have a database state of ONLINE ( database_state is 0).
	--OR d.synchronization_health <>2 -- 0 = Not healthy. At least one joined database is in the NOT SYNCHRONIZING state. 
	--								-- 1 = Partially healthy. Some replicas are not in the target synchronization state: synchronous-commit replicas should be synchronized, and asynchronous-commit replicas should be synchronizing. 
	--								-- 2= Healthy. All replicas are in the target synchronization state: synchronous-commit replicas are synchronized, and asynchronous-commit replicas are synchronizing.
 --   OR d.connected_state <> 1 --0 Disconnected. The response of an availability replica to the DISCONNECTED state depends on its role, as follows:
	--						  --		On the primary replica, if a secondary replica is disconnected, its secondary databases are marked as NOT SYNCHRONIZED on the primary replica, which waits for the secondary to reconnect.
	--						  --		On a secondary replica, upon detecting that it is disconnected, the secondary replica attempts to reconnect to the primary replica.
	--						  -- 1 Connected
 --   OR d.last_connect_error_number IS NOT NULL

--<DBLevel>
SELECT r.replica_server_name
	 , r.replica_id
	 , DB_NAME(DRS.database_id)
	 , DRS.is_primary_replica
	 , drs.synchronization_state
	 , drs.synchronization_state_desc
	 , drs.synchronization_health_desc
	 , drs.database_state_desc
	 , drs.suspend_reason_desc
	 --, drs.is_suspended
	 --, DRS.suspend_reason_desc
	 --, drs.suspend_reason
	 , drs.last_sent_time
	 , drs.last_received_time
	 , drs.log_send_queue_size
	 , drs.log_send_rate
	 , drs.redo_queue_size
	 , drs.redo_rate
	 
	 --, drcs.is_failover_ready
	 --, drcs.is_pending_secondary_suspend
	 --, drcs.recovery_lsn
	 , DATEDIFF(ss, drs.last_sent_time, DRS.last_received_time) AS Last_Log_commit_Duration_sec
  FROM sys.availability_replicas r
  JOIN sys.dm_hadr_database_replica_states DRS
	ON r.replica_id = DRS.replica_id
 --WHERE r.replica_id ='E0CCAF57-F7B8-46D0-BDE4-0EDF1FECE310'
  WHERE drs.synchronization_state NOT IN (2) 
  --WHERE 1=1
    --and DB_NAME(DRS.database_id)='abce'
 --   OR drs.synchronization_health <> 2
	--OR DRS.is_suspended=1
	--OR drs.suspend_reason IS NOT NULL
	--OR DATEDIFF(ss, drs.last_sent_time, DRS.last_received_time)>30 -- longer than 30 sec.
	
/*
<synchronization_state>
0 Not synchronizing.
	For a primary database, indicates that the database is not ready to synchronize its transaction log with the corresponding secondary databases. 
	For a secondary database, indicates that the database has not started log synchronization because of a connection issue, is being suspended
							, or is going through transition states during startup or a role switch. 
1 Synchronizing.
	For a primary database, indicates that the database is ready to accept a scan request from a secondary database.
	For a secondary database, indicates that active data movement is occurring for the database. 
2 Synchronized.
	A primary database shows SYNCHRONIZED in place of SYNCHRONIZING.
	A synchronous-commit secondary database shows synchronized when the local cache says the database is failover ready and is synchronizing.
3 Reverting. 
	Indicates the phase in the undo process when a secondary database is actively getting pages from the primary database.Caution noteCaution
	When a database on a secondary replica is in the REVERTING state, forcing failover to the secondary replica leaves the database in a state in which it cannot be started as a primary database.
	Either the database will need to reconnect as a secondary database, or you will need to apply new log records from a log backup.
4 Initializing.
	Indicates the phase of undo when the transaction log required for a secondary database to catch up to the undo LSN is being shipped and hardened on a secondary replica. Caution noteCaution
	When a database on a secondary replica is in the INITIALIZING state, forcing failover to the secondary replica leaves the database in a state in which it be started as a primary database.
	Either the database will need to reconnect as a secondary database, or you will need to apply new log records from a log backup.

<database_state>

0 Not healthy.
	The synchronization_state of the database is 0 (NOT SYNCHRONIZING). 
1 Partially healthy. 
	A database on a synchronous-commit availability replica is considered partially healthy if synchronization_state is 1 (SYNCHRONIZING). 
2 Healthy. 
	A database on an synchronous-commit availability replica is considered healthy if synchronization_state is 2 (SYNCHRONIZED)
	, and a database on an asynchronous-commit availability replica is considered healthy if synchronization_state is 1 (SYNCHRONIZING). 
*/

This is honor to speak at SQL PASS Summit 2019
Please come in the session at 11AM, 11/8 Fri. Room: 608

Here are the detail information.
https://www.pass.org/summit/2019/Learn/SessionDetails.aspx?sid=92365

Presentation download is here.
Git Repository : https://dev.azure.com/xcloudapp/_git/PASS2019

10 years ago, DBAs typically maintained a few critical databases. These days, it’s not strange anymore that a DBA maintains several hundred databases. Without automation CICD pipeline, a DBA would be the bottleneck for the faster deployment. Industry pretty much required who has the ability to automation of database deployment and operation. This session will delivery the automation of database operation including deployment and design and architecture of environment.Prerequisites: Who has Database deployment experience.

Simon Cho is one of the founders of SQLAngeles.com, which is a Los Angeles Korean Tech PASS community group. As a Local Group leader, he is a Microsoft SQL community speaker. He has presented many times at SQLSaturdays and to PASS Local Groups. In the past, he lead a database team and managed hundreds of SQL Servers and DWs on VM environment in the gaming industry in Nexon America. He moved his passion to VISA Inc, the largest credit card provider in the world, where he used database technology to build a strong solution and a secure environment, with new strategies and features to maintain thousands of SQL Servers. He has now joined the Nowcom Corp, as a Director of DevOps, where he leads the DevOps team and DBA team.

General Session (75 minutes):

• Database CICD (Continuous Integration and Deployment) DevOps

https://www.linkedin.com/pulse/how-fix-sql-server-configuration-manager-cannot-connect-mohamed-fekry/