SSHC-HighAvailability.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "High Availability\r\n",
                "1.\tIf in a Failover Cluster or Availability Group, check the Failover Cluster Manager application in Windows. Report any recent cluster events.\r\n",
                "2.\tCluster Nodes must be identical SQL Server version.\r\n",
                "3.\tIf in an Availaiblity Group, use AG - Monitor.sql. Check the Availability Group node statuses and verify are all online and that all secondaries are SYNCHRONIZED (if synchronous) or SYNCHRONIZING (if asynchronous.) \r\n",
                "4.\tAfter re-creating an availability group, the owner will be the login that performed the setup. Microsoft recommends changing the availability group owner to sa. The Availability Group may fail if the owner sid is removed from SQL Server.\r\n",
                "  \r\n",
                "  *\tAG – check owner.sql\r\n",
                "  *\tALTER AUTHORIZATION ON AVAILABILITY GROUP::AGname to [sa] ;\r\n",
                "5.\tTrend and Baseline for the following counters\r\n",
                "  *\tPerfmon: AG – Perfmon Counters.sql \r\n",
                "6.\tFor multisubnet AG’s, verify via nslookup that the Listener is broadcasting an IP in each subnet.\r\n",
                "  *\tExample: nslookup listenername\r\n",
                "  *\tYou should see two IP’s in two subnets returned.\r\n",
                "7.\tAll logins used to access SQL Server must be on all the replicas. \r\n",
                "8.\tCompare select * from sys.sql_logins on all AG replicas to confirm that login names and sid's match. The sid's will be the same on each replica, because they are replicated with each database. If they do not match, sql logins may not be mapped to database users on secondary replicas and connectivity will be denied upon failover. Would need to recreate the logins with the correct SID."
            ],
            "metadata": {
                "azdata_cell_guid": "5a287310-218f-4060-a617-153aec1580a7"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "--AG - Monitor.sql\r\n",
                "--On a secondary replica, this view returns a row for every secondary database on the server instance. \r\n",
                "--On the primary replica, this view returns a row for each primary database and an additional row for the corresponding secondary database.\r\n",
                "\r\n",
                "--Updated WDA 20180209\r\n",
                "\r\n",
                "\r\n",
                "--Monitor Availability Group performance\r\n",
                "--On a secondary replica, this view returns a row for every secondary database on the server instance. \r\n",
                "--On the primary replica, this view returns a row for each primary database and an additional row for the corresponding secondary database. Recommended.\r\n",
                "PRINT('AG - Monitor Output Window')\r\n",
                "IF NOT EXISTS (\r\n",
                "SELECT @@SERVERNAME\r\n",
                "   FROM sys.dm_hadr_availability_replica_states\r\n",
                "   WHERE is_local = 1\r\n",
                "   and role_desc = 'PRIMARY'\r\n",
                ")\r\n",
                "  SELECT 'Recommend: Run This Script on Primary Replica';\r\n",
                "\r\n",
                "DECLARE @BytesFlushed_Start_ms bigint, @BytesFlushed_Start bigint, @BytesFlushed_End_ms bigint, @BytesFlushed_End bigint\r\n",
                "\r\n",
                "DECLARE @TransactionDelay TABLE\r\n",
                "(\tDB sysname COLLATE SQL_Latin1_General_CP1_CI_AS not null\r\n",
                ",\tTransactionDelay_Start_ms decimal(19,2) null\r\n",
                ",\tTransactionDelay_end_ms decimal(19,2) null\r\n",
                ",\tTransactionDelay_Start decimal(19,2) null\r\n",
                ",\tTransactionDelay_end decimal(19,2) null\r\n",
                ",\tMirroredWriteTranspersec_Start_ms decimal(19,2) null\r\n",
                ",\tMirroredWriteTranspersec_end_ms decimal(19,2) null\r\n",
                ",\tMirroredWriteTranspersec_Start decimal(19,2) null\r\n",
                ",\tMirroredWriteTranspersec_end decimal(19,2) null\r\n",
                ",\tUNIQUE CLUSTERED (DB)\r\n",
                ")\r\n",
                "\r\n",
                "INSERT INTO @TransactionDelay (DB, TransactionDelay_Start_ms, TransactionDelay_Start)\r\n",
                "select DB = pc.instance_name\r\n",
                ",\tTransactionDelay_Start_ms = MAX(ms_ticks)\r\n",
                ",\tTransactionDelay_Start = MAX(convert(decimal(19,2), pc.cntr_value))\r\n",
                "from sys.dm_os_sys_info as si\r\n",
                "CROSS APPLY sys.dm_os_performance_counters as pc\r\n",
                " where object_name like '%database replica%'\r\n",
                " and counter_name = 'transaction delay' --cumulative transaction delay in ms\r\n",
                " group by pc.instance_name\r\n",
                "  \r\n",
                "UPDATE t \r\n",
                "SET MirroredWriteTranspersec_Start_ms = t2.MirroredWriteTranspersec_Start_ms\r\n",
                ",\tMirroredWriteTranspersec_Start = t2.MirroredWriteTranspersec_Start\r\n",
                "from @TransactionDelay t\r\n",
                "inner join \r\n",
                "(select DB = pc.instance_name\r\n",
                ",\tMirroredWriteTranspersec_Start_ms = MAX(ms_ticks)\r\n",
                ",\tMirroredWriteTranspersec_Start = MAX(convert(decimal(19,2), pc.cntr_value))\r\n",
                "from sys.dm_os_sys_info as si\r\n",
                "CROSS APPLY sys.dm_os_performance_counters as pc\r\n",
                " where object_name like '%database replica%'\r\n",
                " and counter_name = 'mirrored write transactions/sec' --actually a cumulative transactions count, not per sec\r\n",
                " group by pc.instance_name\r\n",
                " ) t2 on t.DB = t2.DB\r\n",
                "\r\n",
                "select @BytesFlushed_Start_ms = MAX(ms_ticks), @BytesFlushed_Start = MAX(cntr_value) --the availability database with the highest Tdata_loss becomes the limiting value for RPO compliance.\r\n",
                "from sys.dm_os_sys_info\r\n",
                "CROSS APPLY sys.dm_os_performance_counters where counter_name like 'Log Bytes Flushed/sec%'\r\n",
                "\r\n",
                "WAITFOR DELAY '00:00:05' --Adjust sample duration between measurements\r\n",
                "\r\n",
                "UPDATE t \r\n",
                "SET TransactionDelay_end_ms = t2.TransactionDelay_end_ms\r\n",
                ",\tTransactionDelay_end = t2.TransactionDelay_end\r\n",
                "from @TransactionDelay t\r\n",
                "inner join \r\n",
                "(select DB = pc.instance_name\r\n",
                ",\tTransactionDelay_end_ms = MAX(ms_ticks)\r\n",
                ",\tTransactionDelay_end = MAX(convert(decimal(19,2), pc.cntr_value))\r\n",
                "from sys.dm_os_sys_info as si\r\n",
                "CROSS APPLY sys.dm_os_performance_counters as pc\r\n",
                " where object_name like '%database replica%'\r\n",
                " and counter_name = 'transaction delay' --cumulative transaction delay in ms\r\n",
                " group by pc.instance_name\r\n",
                " ) t2 on t.DB = t2.DB\r\n",
                " \r\n",
                "UPDATE t \r\n",
                "SET MirroredWriteTranspersec_end_ms = t2.MirroredWriteTranspersec_end_ms\r\n",
                ",\tMirroredWriteTranspersec_end = t2.MirroredWriteTranspersec_end\r\n",
                "from @TransactionDelay t\r\n",
                "inner join \r\n",
                "(select DB = pc.instance_name\r\n",
                ",\tMirroredWriteTranspersec_end_ms = MAX(ms_ticks)\r\n",
                ",\tMirroredWriteTranspersec_end = MAX(convert(decimal(19,2), pc.cntr_value))\r\n",
                "from sys.dm_os_sys_info as si\r\n",
                "CROSS APPLY sys.dm_os_performance_counters as pc\r\n",
                " where object_name like '%database replica%'\r\n",
                " and counter_name = 'mirrored write transactions/sec'  --actually a cumulative transactions count, not per sec\r\n",
                " group by pc.instance_name\r\n",
                " ) t2 on t.DB = t2.DB\r\n",
                "\r\n",
                "select @BytesFlushed_End_ms =  MAX(ms_ticks), @BytesFlushed_End = MAX(cntr_value) --the availability database with the highest Tdata_loss becomes the limiting value for RPO compliance.\r\n",
                "from sys.dm_os_sys_info\r\n",
                "CROSS APPLY sys.dm_os_performance_counters where counter_name like 'Log Bytes Flushed/sec%'\r\n",
                "\r\n",
                "declare @LogBytesFlushed decimal(19,2) \r\n",
                "set @LogBytesFlushed = (@BytesFlushed_End - @BytesFlushed_Start) / NULLIF(@BytesFlushed_End_ms - @BytesFlushed_Start_ms,0)\r\n",
                "\r\n",
                "--select * from @TransactionDelay\r\n",
                "\r\n",
                "select \r\n",
                "\tAG\t\t\t\t\t= ag.name\r\n",
                ",\tInstance\t\t\t= ar.replica_server_name + ' ' + case when is_local = 1 then '(local)' else '' end\r\n",
                ",\tDB\t\t\t\t\t= db_name(dm.database_id)\t\r\n",
                ",\tReplica_Role\t\t= CASE WHEN last_received_time IS NULL THEN 'PRIMARY (Connections: '+ar.primary_role_allow_connections_desc+')' ELSE 'SECONDARY (Connections: '+ar.secondary_role_allow_connections_desc+')' END\r\n",
                ",\tLast_received_time\r\n",
                ",\tLast_commit_time\r\n",
                ",\tdm.synchronization_state_desc \r\n",
                ",\tdm.synchronization_health_desc\r\n",
                ",\tar.availability_mode_desc\r\n",
                ",\tar.failover_mode_desc\r\n",
                ",\tSuspended = case is_suspended when 1 then suspend_reason_desc else 'NO' end\r\n",
                ",\tRedo_queue_size_MB\t\t= convert(decimal(19,2),dm.redo_queue_size/1024.)--KB\r\n",
                ",\tRedo_rate_MB_per_s\t\t= convert(decimal(19,2),dm.redo_rate/1024.) --KB/s\r\n",
                ",\tRedo_Time_Left_s_RTO\t= convert(decimal(19,2),dm.redo_queue_size*1./NULLIF(dm.redo_rate*1.,0)) --only part of RTO. NULL value on secondary replica indicates no sampled activity.\r\n",
                ",\tLog_Send_Queue_Size_MB\t\t= convert(decimal(19,2),dm.log_send_queue_size/1024.) \r\n",
                ",\tLog_Send_Queue_Bytes_flushed_per_s = NULLIF(@LogBytesFlushed ,0)\r\n",
                ",\tLog_Send_Queue_Time_Left_s_RPO\t= convert(decimal(19,2),dm.log_send_queue_size*1./NULLIF(@LogBytesFlushed ,0)) --Rate. NULL value on secondary replica indicates no sampled activity.\r\n",
                ",\tSampled_Transactions_count\t\t= (td.MirroredWriteTranspersec_end - td.MirroredWriteTranspersec_start)  \r\n",
                ",\tSampled_Transaction_Delay_ms\t= (td.TransactionDelay_end - td.TransactionDelay_start)  \r\n",
                "--Transaction Delay numbers will be 0 if there is no synchronous replica for the DB\r\n",
                ",\tAvg_Sampled_Transaction_Delay_ms_per_s\t= convert(decimal(19,2), (td.TransactionDelay_end - td.TransactionDelay_Start) / ((td.TransactionDelay_end_ms - td.TransactionDelay_Start_ms)/1000.))\r\n",
                ",\tTransactions_per_s\t= convert(decimal(19,2), ((td.MirroredWriteTranspersec_end - td.MirroredWriteTranspersec_start) / ((td.MirroredWriteTranspersec_End_ms - td.MirroredWriteTranspersec_Start_ms)/1000.)))\r\n",
                ",\tdm.secondary_lag_seconds --sql 2016 and above only\r\n",
                ",\tar.backup_priority\r\n",
                ",\tar.modify_date\r\n",
                ",\tar.endpoint_url \r\n",
                ",\tar.read_only_routing_url\r\n",
                ",\tar.session_timeout\r\n",
                "from sys.dm_hadr_database_replica_states dm\r\n",
                "INNER JOIN sys.availability_replicas ar on dm.replica_id = ar.replica_id and dm.group_id = ar.group_id\r\n",
                "INNER JOIN sys.availability_groups ag on ag.group_id = dm.group_id\r\n",
                "LEFT OUTER JOIN @TransactionDelay td on td.DB = db_name(dm.database_id) --LEFT OUTER in case the sys.dm_os_performance_counters DMV is blank because of a rare issue with SQL startup. Still returns the rest of the data.\r\n",
                "ORDER BY\r\n",
                "\tAG\t\t\t\r\n",
                ",\tInstance\t\t\r\n",
                ",\tDB\t\t\t\r\n",
                ",\tReplica_Role\r\n",
                "\r\n",
                "--Current node only, should be run on primary\r\n",
                "select \r\n",
                "\twait_type\r\n",
                ",\twaiting_tasks_count\r\n",
                ",\twait_time_ms\r\n",
                ",\tper_wait_ms = convert(decimal(19,2), (convert(decimal(19,2), wait_time_ms)/ convert(decimal(19,2),waiting_tasks_count) ))\r\n",
                "from sys.dm_os_wait_stats where waiting_tasks_count >0 \r\n",
                "and wait_type like 'HADR_%_COMMIT'\r\n",
                "\r\n",
                "--SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'operations'\r\n",
                "\r\n",
                "--Check for suspect pages (hopefully 0 rows returned)\r\n",
                "--https://msdn.microsoft.com/en-us/library/ms191301.aspx\r\n",
                "SELECT * FROM msdb.dbo.suspect_pages\r\n",
                "   WHERE (event_type <= 3);\r\n",
                "\r\n",
                "--Check for autorepair events (hopefully 0 rows returned)\r\n",
                "--https://msdn.microsoft.com/en-us/library/bb677167.aspx\r\n",
                "select db = db_name(database_id)\r\n",
                ",\tfile_id\r\n",
                ",\tpage_id\r\n",
                ",\terror_type \r\n",
                ",\tpage_status\r\n",
                ",\tmodification_time\r\n",
                "from sys.dm_hadr_auto_page_repair order by modification_time desc\r\n",
                "\r\n",
                "--Replica status (one row per replica when run on the primary)\r\n",
                "select \r\n",
                "\tag.name,\r\n",
                "\trcs.replica_server_name,\r\n",
                "\trs.last_connect_error_number, rs.last_connect_error_description, rs.last_connect_error_timestamp,\r\n",
                "\trs.operational_state_desc,\r\n",
                "\trs.recovery_health_desc,\r\n",
                "\trs.connected_state_desc,\r\n",
                "\trs.role_desc,\r\n",
                "\trs.synchronization_health_desc\r\n",
                "from sys.dm_hadr_availability_replica_states rs --https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-hadr-availability-replica-states-transact-sql?view=sql-server-2017\r\n",
                "inner join sys.dm_hadr_availability_replica_cluster_states rcs\r\n",
                "on rs.replica_id = rcs.replica_id\r\n",
                "inner join sys.availability_groups ag \r\n",
                "on ag.group_id = rcs.group_id\r\n",
                "\r\n",
                "--https://msdn.microsoft.com/en-us/library/ff877972(v=sql.110).aspx\r\n",
                "--https://msdn.microsoft.com/en-us/library/dn135338(v=sql.110).aspx\r\n",
                "--https://blogs.msdn.microsoft.com/psssql/2013/09/23/interpreting-the-counter-values-from-sys-dm_os_performance_counters/\r\n",
                "--https://msdn.microsoft.com/en-us/library/ms175048.aspx"
            ],
            "metadata": {
                "azdata_cell_guid": "76d2b880-540d-43f6-8614-bfab22ca1ce2",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "--AG- Perform Counters.sql\r\n",
                "PRINT('AG - Performance Counters Output Window')\r\n",
                "declare @tempperfmon table (\r\n",
                "[object_name]\tnchar\t(256) null,\r\n",
                "counter_name\tnchar\t(256) null,\r\n",
                "instance_name\tnchar\t(256) null,\r\n",
                "cntr_value\t\tbigint\tnull\r\n",
                ", second_value bigint null\r\n",
                ")\r\n",
                "\r\n",
                "--For databases both the primary and secondary, with send/receive counters reflecting the local replica\r\n",
                "insert into @tempperfmon ([object_name],counter_name,instance_name,cntr_value)\r\n",
                "select [object_name],counter_name,instance_name,cntr_value\r\n",
                "from sys.dm_os_performance_counters\r\n",
                " where [object_name] like '%Availability Replica%' and instance_name <> '_Total' and\r\n",
                "\t(\tcounter_name = 'Bytes Received from Replica/sec' --From the availability replica. Pings and status updates will generate network traffic even on databases with no user updates.\r\n",
                "\tor\tcounter_name = 'Bytes Sent to Replica/sec' --Sent to the remote replica. On primary, sent to the secondary replica. On secondary, sent to the primary replica.\r\n",
                "\tor\tcounter_name = 'Bytes Sent to Transport/sec' --Sent over the network to the remote replica. On primary, sent to the secondary replica. On secondary, sent to the primary replica.\r\n",
                "\tor\tcounter_name = 'Flow Control Time (ms/sec)' --Time in milliseconds that log stream messages waited for send flow control, in the last second.   \r\n",
                "\tor\tcounter_name = 'Receives from Replica/Sec'\r\n",
                "\tor\tcounter_name = 'Sends to Replica/Sec'\r\n",
                "\r\n",
                "\t)\r\n",
                "\t\r\n",
                "insert into @tempperfmon ([object_name],counter_name,instance_name,cntr_value)\r\n",
                "--Only valid for databases in the secondary replica role\r\n",
                "select [object_name],counter_name,instance_name,cntr_value\r\n",
                "from sys.dm_os_performance_counters\r\n",
                " where [object_name] like '%database replica%' and instance_name <> '_Total' and\r\n",
                "\t(\tcounter_name = 'File Bytes Received/sec' --FILESTREAM data only\r\n",
                "\tor\tcounter_name = 'Log Bytes Received/sec' --Amount of log records received by the secondary replica for the database in the last second.'\r\n",
                "\tor\tcounter_name = 'Log remaining for undo' --The amount of log in kilobytes remaining to complete the undo phase.\r\n",
                "\t)\r\n",
                "\r\n",
                "WAITFOR DELAY '00:00:05';  --5s\r\n",
                "\r\n",
                "\r\n",
                "--For databases both the primary and secondary, with send/receive counters reflecting the local replica\r\n",
                "insert into @tempperfmon ([object_name],counter_name,instance_name,second_value)\r\n",
                "select [object_name],counter_name,instance_name,cntr_value\r\n",
                "from sys.dm_os_performance_counters\r\n",
                " where [object_name] like '%Availability Replica%' and instance_name <> '_Total' and\r\n",
                "\t(\tcounter_name = 'Bytes Received from Replica/sec' --From the availability replica. Pings and status updates will generate network traffic even on databases with no user updates.\r\n",
                "\tor\tcounter_name = 'Bytes Sent to Replica/sec' --Sent to the remote replica. On primary, sent to the secondary replica. On secondary, sent to the primary replica.\r\n",
                "\tor\tcounter_name = 'Bytes Sent to Transport/sec' --Sent over the network to the remote replica. On primary, sent to the secondary replica. On secondary, sent to the primary replica.\r\n",
                "\tor\tcounter_name = 'Flow Control Time (ms/sec)' --Time in milliseconds that log stream messages waited for send flow control, in the last second.   \r\n",
                "\tor\tcounter_name = 'Receives from Replica/Sec'\r\n",
                "\tor\tcounter_name = 'Sends to Replica/Sec'\r\n",
                "\r\n",
                "\t)\r\n",
                "\t\r\n",
                "insert into @tempperfmon ([object_name],counter_name,instance_name,second_value)\r\n",
                "--Only valid for databases in the secondary replica role\r\n",
                "select [object_name],counter_name,instance_name,cntr_value\r\n",
                "from sys.dm_os_performance_counters\r\n",
                " where [object_name] like '%database replica%' and instance_name <> '_Total' and\r\n",
                "\t(\tcounter_name = 'File Bytes Received/sec' --FILESTREAM data only\r\n",
                "\tor\tcounter_name = 'Log Bytes Received/sec' --Amount of log records received by the secondary replica for the database in the last second.'\r\n",
                "\tor\tcounter_name = 'Log remaining for undo' --The amount of log in kilobytes remaining to complete the undo phase.\r\n",
                "\t)\r\n",
                "\r\n",
                "select \r\n",
                "[object_name],counter_name,instance_name\r\n",
                ", Observation = (max(second_value) - max(cntr_value)) /5.\r\n",
                "from @tempperfmon\r\n",
                "group by [object_name],counter_name,instance_name\r\n",
                "\r\n",
                "/*\r\n",
                "--For databases both the primary and secondary, with send/receive counters reflecting the local replica\r\n",
                "select object_name,counter_name,instance_name,cntr_value\r\n",
                "from sys.dm_os_performance_counters\r\n",
                " where object_name like '%Availability Replica%' and instance_name <> '_Total' and\r\n",
                "\t(\tcounter_name = 'Bytes Received from Replica/sec' --From the availability replica. Pings and status updates will generate network traffic even on databases with no user updates.\r\n",
                "\tor\tcounter_name = 'Bytes Sent to Replica/sec' --Sent to the remote replica. On primary, sent to the secondary replica. On secondary, sent to the primary replica.\r\n",
                "\tor\tcounter_name = 'Bytes Sent to Transport/sec' --Sent over the network to the remote replica. On primary, sent to the secondary replica. On secondary, sent to the primary replica.\r\n",
                "\tor\tcounter_name = 'Flow Control Time (ms/sec)' --Time in milliseconds that log stream messages waited for send flow control, in the last second.   \r\n",
                "\tor\tcounter_name = 'Receives from Replica/Sec'\r\n",
                "\tor\tcounter_name = 'Sends to Replica/Sec'\r\n",
                "\r\n",
                "\t)\r\n",
                "\t\r\n",
                "\r\n",
                "--Only valid for databases in the secondary replica role\r\n",
                "select object_name,counter_name,instance_name,cntr_value\r\n",
                "from sys.dm_os_performance_counters\r\n",
                " where object_name like '%database replica%' and instance_name <> '_Total' and\r\n",
                "\t(\tcounter_name = 'File Bytes Received/sec' --FILESTREAM data only\r\n",
                "\tor\tcounter_name = 'Log Bytes Received/sec' --Amount of log records received by the secondary replica for the database in the last second.'\r\n",
                "\tor\tcounter_name = 'Log remaining for undo' --The amount of log in kilobytes remaining to complete the undo phase.\r\n",
                "\t)\r\n",
                "*/"
            ],
            "metadata": {
                "azdata_cell_guid": "6b46d0eb-cdde-4514-a249-3b8ac9b9e15c",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "--AG Replica Login Compare\r\n",
                "PRINT('AG Replica Login Compare')\r\n",
                "select * from sys.sql_logins"
            ],
            "metadata": {
                "azdata_cell_guid": "36ab284a-e528-485c-9a32-9886c218bde1",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        }
    ]
}