{"id":364,"date":"2019-12-18T13:15:06","date_gmt":"2019-12-18T03:15:06","guid":{"rendered":"http:\/\/www.mrmarkyoung.com\/oracle\/?p=364"},"modified":"2019-12-18T13:15:06","modified_gmt":"2019-12-18T03:15:06","slug":"calculate-rto-from-availability-group-sqlserver","status":"publish","type":"post","link":"http:\/\/www.mrmarkyoung.com\/oracle\/2019\/12\/18\/calculate-rto-from-availability-group-sqlserver\/","title":{"rendered":"Calculate RTO from Availability Group SQLServer"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code>if object_id(N'proc_calculate_RTO', 'p') is not null\n       drop procedure proc_calculate_RTO\n   go\n   \n   raiserror('creating procedure proc_calculate_RTO', 0,1) with nowait\n   go\n   --\n   -- name: proc_calculate_RTO\n   --\n   -- description: Calculate RTO of a secondary database.\n   -- \n   -- parameters:\t@secondary_database_name nvarchar(max): name of the secondary database.\n   --\n   -- security: this is a public interface object.\n   --\n   create procedure proc_calculate_RTO\n   (\n   @secondary_database_name nvarchar(max)\n   )\n   as\n   begin\n \t  declare @db sysname\n \t  declare @is_primary_replica bit \n \t  declare @is_failover_ready bit \n \t  declare @redo_queue_size bigint \n \t  declare @redo_rate bigint\n \t  declare @replica_id uniqueidentifier\n \t  declare @group_database_id uniqueidentifier\n \t  declare @group_id uniqueidentifier\n \t  declare @RTO float \n\n \t  select \n \t  @is_primary_replica = dbr.is_primary_replica, \n \t  @is_failover_ready = dbcs.is_failover_ready, \n \t  @redo_queue_size = dbr.redo_queue_size, \n \t  @redo_rate = dbr.redo_rate, \n \t  @replica_id = dbr.replica_id,\n \t  @group_database_id = dbr.group_database_id,\n \t  @group_id = dbr.group_id \n \t  from sys.dm_hadr_database_replica_states dbr join sys.dm_hadr_database_replica_cluster_states dbcs \ton dbr.replica_id = dbcs.replica_id and \n \t  dbr.group_database_id = dbcs.group_database_id  where dbcs.database_name = @secondary_database_name\n\n \t  if  @is_primary_replica is null or @is_failover_ready is null or @redo_queue_size is null or @replica_id is null or @group_database_id is null or @group_id is null\n \t  begin\n \t  \tprint 'RTO of Database '+ @secondary_database_name +' is not available'\n \t  \treturn\n \t  end\n \t  else if @is_primary_replica = 1\n \t  begin\n \t  \tprint 'You are visiting wrong replica';\n \t  \treturn\n \t  end\n\n \t  if @redo_queue_size = 0 \n \t  \tset @RTO = 0 \n \t  else if @redo_rate is null or @redo_rate = 0 \n \t  begin\n \t  \tprint 'RTO of Database '+ @secondary_database_name +' is not available'\n \t  \treturn\n \t  end\n \t  else \n \t  \tset @RTO = CAST(@redo_queue_size AS float) \/ @redo_rate\n   \n \t  print 'RTO of Database '+ @secondary_database_name +' is ' + convert(varchar, ceiling(@RTO))\n \t  print 'group_id of Database '+ @secondary_database_name +' is ' + convert(nvarchar(50), @group_id)\n \t  print 'replica_id of Database '+ @secondary_database_name +' is ' + convert(nvarchar(50), @replica_id)\n \t  print 'group_database_id of Database '+ @secondary_database_name +' is ' + convert(nvarchar(50), @group_database_id)\n   end<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[71],"tags":[],"class_list":["post-364","post","type-post","status-publish","format-standard","hentry","category-ms-sql"],"_links":{"self":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/364","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/comments?post=364"}],"version-history":[{"count":1,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/364\/revisions"}],"predecessor-version":[{"id":365,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/364\/revisions\/365"}],"wp:attachment":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/media?parent=364"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/categories?post=364"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/tags?post=364"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}