Reporting Services using AlwaysON Availability Group

Environment

Blog28_1.PNG

->SQL Server Reporting Services present in JBSERVER4 is configured using Database Server JBSERVER1 initially.

-> SQL Server Reporting Services databases are later placed on the Availability Group JBS_SSRSAG.

-> Database [ReportServerTempDB] recovery model will be changed to Full from Simple.

-> Adding the [ReportServer] and [ReportServerTempDB] database to the Availability group JB_SSRSAG.

Blog27_3

Blog28_2.PNG

Blog28_3.PNG

Blog27_6

Blog27_7

Blog28_4.PNG

Blog27_9.PNG

Blog28_5.PNG

-> Checking the new Availability group in SQL Server management Studio,

Blog28_6.PNG

-> Reconfiguring the SQL Server Reporting Services on JBSERVER4 to use the Availability group listener,

Blog28_7.PNG

Blog28_8.PNG

Blog28_9.PNG

Blog28_10.PNG

Blog28_11.PNG

-> Executing a Sample report that uses Availability group Listener JBS_APP that has read-only routing configured.

-> Please refer article “Configuring Read Only Routing List for AlwaysON Availability Group” to get more details on Availability group JBS_AG and its Listener JBS_APP.

-> The data source on the report doesn’t use Application Intent,

Blog28_12.PNG

-> Executing the report and checking the profiler trace. The queries are executed on the Primary JBSERVER1.

Blog28_13.PNG

Blog28_14.PNG

-> The data source on the report uses the Application Intent to ReadOnly,

Blog28_15.PNG

-> Executing the report and checking the profiler trace. The queries are executed on the Secondary JBSERVER3.

Blog28_13

Blog28_16

SQL Server Reporting Services Subscription Jobs

-> Execute the below query only once on database server JBSERVER1, JBSERVER2 and JBSERVER3. The table stores the details whether that particular replica is a primary or secondary replica.

create table AG_role (Role int)
insert into Ag_Role select sys.fn_hadr_is_primary_replica(N’ReportServer’)

-> Save the below script as a powershell script file on Server JBSERVER1, JBSERVER2 and JBSERVER3. Please make sure that $AGReplica is changed appropriately in the script on each of the server before saving.

$AGReplica = “JBSERVER1”;
$AG_ROLE_Previous = Invoke-Sqlcmd “select * from AG_role;” -ServerInstance $AGReplica;
$AG_ROLE_Current = Invoke-Sqlcmd “select sys.fn_hadr_is_primary_replica(N’ReportServer’) as Role;” -ServerInstance $AGReplica;
If ($AG_ROLE_Previous.Role -eq $AG_ROLE_Current.Role)
{
“No Failover occurred”
}
else
{
“Failover occurred”
#Delete the SQL Server agent jobs related to Subscription

Invoke-Sqlcmd “DECLARE @TSQL NVARCHAR(MAX) = N”;
SELECT
@TSQL = @TSQL + ‘EXEC msdb.dbo.sp_delete_job @job_name = N”’ + j.name + ”” + CHAR(13)
FROM msdb.dbo.sysjobs AS j
JOIN msdb.dbo.syscategories AS c
ON j.category_id = c.category_id
WHERE c.name = N’Report Server’;
EXEC sp_executesql @TSQL;” -ServerInstance $AGReplica;

If ($AG_ROLE_Current.Role -eq 1)
{
“Failover Occurred”
$SSRSServers = Invoke-Sqlcmd “If (SELECT sys.fn_hadr_is_primary_replica(N’ReportServer’)) = 1 SELECT MachineName FROM [ReportServer].[dbo].[Keys] WHERE MachineName IS NOT NULL” -ServerInstance $AGReplica;
Foreach ($SSRS in $SSRSServers)
{
Get-Service -Name “ReportServer” -ComputerName $SSRS.MachineName | Restart-Service;
}
}

}

Invoke-Sqlcmd “delete from AG_Role;” -ServerInstance $AGReplica;
Invoke-Sqlcmd “insert into AG_Role SELECT sys.fn_hadr_is_primary_replica(N’ReportServer’);” -ServerInstance $AGReplica;

->On the Secondary replica, the script basically deletes all the jobs related to SSRS subscription in the SQL Server Agent when it detects a failover.

->On the primary replica, the script deletes all the jobs related to SSRS subscription in the SQL Server Agent and restarts the SQL Server Reporting Services on JBSERVER4 if it detects a failover. This ensures that the subscription jobs are recreated on the primary replica.

-> Create a SQL Server agent job to call the Powershell script saved in the previous step under a job step with type PowerShell. The job should run every 1 minute and also when the SQL Server Agent starts.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

 

 

 

Advertisements

Configuring Read Only Routing List for AlwaysON Availability Group

Environment

Blog27_1.PNG

-> SQL Server instances as seen in the SQL Server Management Studio,

Blog27_2.PNG

-> Adding the databases to Availability group,

Blog27_3.PNG

Blog27_4.PNG

Blog27_5

Blog27_6.PNG

Blog27_7.PNG

Blog27_8.PNG

Blog27_9.PNG

Blog27_10.PNG

Blog27_11.PNG

-> Availability group as seen in SQL Server Management Studio,

Blog27_12.PNG

-> Configuring ReadOnly Routing List using the below query,

ALTER AVAILABILITY GROUP JBS_AG
MODIFY REPLICA ON
N’JBSERVER1′ WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP JBS_AG
MODIFY REPLICA ON
N’JBSERVER1′ WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N’TCP://JBSERVER1.JBS.COM:1433′));
ALTER AVAILABILITY GROUP JBS_AG
MODIFY REPLICA ON
N’JBSERVER2′ WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP JBS_AG
MODIFY REPLICA ON
N’JBSERVER2′ WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N’TCP://JBSERVER2.JBS.COM:1433′));
ALTER AVAILABILITY GROUP JBS_AG
MODIFY REPLICA ON
N’JBSERVER3′ WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP JBS_AG
MODIFY REPLICA ON
N’JBSERVER3′ WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N’TCP://JBSERVER3.JBS.COM:1433′));
ALTER AVAILABILITY GROUP JBS_AG
MODIFY REPLICA ON
N’JBSERVER1′ WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(‘JBSERVER3′,’JBSERVER2’)));
ALTER AVAILABILITY GROUP JBS_AG
MODIFY REPLICA ON
N’JBSERVER2′ WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(‘JBSERVER3′,’JBSERVER1’)));
ALTER AVAILABILITY GROUP JBS_AG
MODIFY REPLICA ON
N’JBSERVER3′ WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(‘JBSERVER2′,’JBSERVER1’)));

-> The Read-Routing list order is as below,

Blog27_13.PNG

-> Checking if Read-Routing List is working using SQLCMD. After we use the switch -KReadOnly, the servername returned is JBSERVER3 which proves that the readonly queries are routed to the secondary.

Blog27_14

-> Verifying Read-Only list using SQL Server Management Studio,

Blog27_15.PNG

Blog27_16.PNG

Blog27_17.PNG

Blog27_18

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Disaster Recovery Using AlwaysON Availability Group – Scenario 2

Environment

Blog25_1-> Disaster Recovery scenario is as below,

  • PRIMARY DATA CENTRE goes down.
  • The databases on Server JBSERVER2 should be made online and have the application connect to Database Server JBSERVER2.
  • Failback Availability group back to JBSERVER1 when PRIMARY DATA CENTRE comes online.
  • The changes made by the Application on JBSERVER2\IN2014 should be discarded.

-> Checking the current AlwaysON setup in SQL Server Management Studio,

Blog26_2

-> The Database JB_DB contains a table named Table5, which we will use for testing.

USE [JB_DB]
GO
CREATE TABLE [dbo].[Table5](
[sno] [int] IDENTITY(1,1) NOT NULL,
[sname] [char](2000) NULL,
[sname1] [char](2000) NULL,
[sname2] [char](2000) NULL,
PRIMARY KEY CLUSTERED
(
[sno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Set nocount on
insert into Table5 values (‘a’,’b’,’c’)
go 10002

-> Checking the row count for object Table5,

Blog26_3

-> The PRIMARY DATA CENTRE goes down. 2 votes are lost as Database Server JBSERVER1 and file share witness is down. Now that 2 votes out of 3 votes are lost, the cluster goes down.

-> Checking the SQL Server instance and Eventlogs on JBSERVER2 when the Primary Data Centre is down.

Blog26_4

-> Implementing Force Quorum on JBSERVER2.

Blog26_5

-> Checking the SQL Server Instance after Force Quorum,

Blog26_6

-> I will suspend the data movement now,

Blog26_7

Blog26_8

Blog26_9

-> Right-click the availability group to be failed over, and select Failover.

Blog26_10

Blog26_11

Blog26_12

Blog26_13

Blog26_14

-> Checking the SQL Server Instance after the failover with data loss.

Blog26_15

-> The application can now connect to SQL Server Instance JBSERVER2\IN2014 using the Listener and use it. Let us insert a single row to the table for testing and make sure if it can be seen later after the failback.

insert into Table5 values (‘a’,’b’,’c’)
go

Blog26_16

-> The PRIMARY DATA CENTRE comes online. Database Server JBSERVER1 and File witness are online now.

-> Connecting to JBSERVER1\IN2014 and Suspending the data movement,

Blog26_17

Blog26_18

Blog26_19

-> We will failback the Alwayson Availability group to JBSERVER1\IN2014,

Blog26_20

Blog26_21

Blog26_22

Blog26_23

Blog26_24

Blog26_26

Blog26_27

-> On SQL Server Instance JBSERVER1\IN2014, we will resume the data movement.

Blog26_28

Blog26_29

-> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement.

Blog26_30

Blog26_31

-> Checking the SQL Server Instance now,

Blog26_32

-> Checking the row count for object Table5 on JBSERVER1\IN2014 after failback,

Blog26_33

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Disaster Recovery Using AlwaysON Availability Group – Scenario 1

Environment

Blog25_1

-> Disaster Recovery scenario is as below,

  • PRIMARY DATA CENTRE goes down. 
  • The databases on Server JBSERVER2 should be made online and have the application connect to Database Server JBSERVER2. 
  • Failback Availability group back to JBSERVER1 when PRIMARY DATA CENTRE comes online. 
  • The changes made by the Application on JBSERVER2\IN2014 should be relayed to JBSERVER1\IN2014

-> Checking the current AlwaysON setup in SQL Server Management Studio,

Blog25_2

-> The Database JB_DB contains a table named Table5, which we will use for testing.

USE [JB_DB]
GO
CREATE TABLE [dbo].[Table5](
[sno] [int] IDENTITY(1,1) NOT NULL,
       [sname] [char](2000) NULL,
       [sname1] [char](2000) NULL,
[sname2] [char](2000) NULL,
PRIMARY KEY CLUSTERED
(
       [sno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Set nocount on
insert into Table5 values (‘a’,’b’,’c’)
go 10002

-> Checking the row count for object Table5,

Blog25_3

-> The PRIMARY DATA CENTRE goes down. 2 votes are lost as Database Server JBSERVER1 and file share witness is down. Now that 2 votes out of 3 votes are lost, the cluster goes down.

-> Checking the SQL Server instance and Eventlogs on JBSERVER2 when the Primary Data Centre is down.

Blog25_4

-> Implementing Force Quorum on JBSERVER2.

Blog25_5

-> Checking the SQL Server Instance after Force Quorum,

Blog25_6

-> I will suspend the data movement now,

Blog25_7

Blog25_8

Blog25_9

-> Right-click the availability group to be failed over, and select Failover.

Blog25_10

Blog25_11

Blog25_12

Blog25_13

Blog25_14

-> Checking the SQL Server Instance after the failover with data loss.

Blog25_15

-> The application can now connect to SQL Server Instance JBSERVER2\IN2014 using the Listener and use it. Let us insert a single row to the table for testing and make sure if it can be seen later after the failback.

insert into Table5 values (‘a’,’b’,’c’)
go

Blog25_16

-> The PRIMARY DATA CENTRE comes online. Database Server JBSERVER1 and File witness are online now.

-> Connecting to JBSERVER1\IN2014 and Suspending the data movement,

Blog25_17Blog25_18

Blog25_19

-> On SQL Server Instance JBSERVER2\IN2014, we will resume the data movement.

Blog25_20

Blog25_21

-> On SQL Server Instance JBSERVER1\IN2014, we will resume the data movement.

Blog25_22

Blog25_23

-> Checking the SQL Server Instance now,

Blog25_24

-> We will failback the Alwayson Availability group to JBSERVER1\IN2014,

Blog25_25

Blog25_26

Blog25_27

Blog25_28

Blog25_29

Blog25_30

Blog25_31

-> Checking the row count for object Table5 on JBSERVER1\IN2014 after failback,

Blog25_32

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Stretch database – Modifying the filter function

-> Please refer “https://jbswiki.com/2017/06/15/stretch-database-in-sql-server-2016/” if you want to setup Stretch database from scratch. If you refer the article I would have named the function “StretchByYear” in the wizard page “Select rowst  stretch”.

-> I will open the function StretchByYear to check what it contains,

Blog6_1.PNG

-> The modify script for the function is as below,

USE [JB_StretchDB]
GO
/****** Object: UserDefinedFunction [dbo].[StretchByYear] Script Date: 17/06/2017 7:59:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[StretchByYear] (@Year Int)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN SELECT 1 AS is_eligible
WHERE @Year <= CONVERT(Int, N’1982′)

-> The modify function updates us that all rows less than or equal to 1982 will be moved to the cloud.

-> Checking the stretch monitor, We see 20000 rows on On-Premise and 30000 rows on cloud.

Blog6_2.PNG

-> The current filter function moves all data less than equal to 1982 to the cloud. Now lets consider that the  requirement changes to move all data less than equal to 1983 should be moved to cloud. In order to achieve this, I will create a new function as below,

USE [JB_StretchDB]
GO
CREATE FUNCTION [dbo].[StretchByYear_1983] (@Year Int)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN SELECT 1 AS is_eligible
WHERE @Year <= CONVERT(Int, N’1983′)
GO

-> One thing to remember is, the new filter function has to be less restrictive than the previous function.

-> Enabling the filter function for table Table1,

ALTER TABLE Table1 SET ( REMOTE_DATA_ARCHIVE = ON (
FILTER_PREDICATE = dbo.StretchByYear_1983(Year),
MIGRATION_STATE = OUTBOUND
) )

-> Executed below coommand to insert an row,

insert into Table1 values(replicate(‘A’,25),1980)

-> Now checking the Stretch monitor,

Blog6_3.PNG

-> It is clear from the screenshot above that the new filter function has taken effect.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Disabling Stretch Database

-> Please refer “https://jbswiki.com/2017/06/15/stretch-database-in-sql-server-2016/” if you want to setup Stretch database from scratch. In this article, I will be disabling stretch database on the SQL server instance.

-> Executing the below query to see what all tables are part of stretch database,

select object_name (object_id),* from sys.remote_data_archive_tables

Blog5_1

-> We must first disable the stretch for each individual tables before disabling it for the database.

-> Disabling it for Table1 by migrating the data back from Azure database to On-Premise database. Please note that migrating the data from azure will involve extra cost.

Blog5_2.PNG

-> Make sure we have sufficient space on the data drive to bring back the data and then click on “Yes”.

Blog5_3.PNG

Blog5_4.PNG

-> Thats quick. Usually it takes time for migrating, if the data involved is too huge.

-> Executing the below query to see what all tables are part of stretch database. We dont see Table1 anymore,

select object_name (object_id),* from sys.remote_data_archive_tables

Blog5_5.PNG

-> Checking the execution plan after executing the below query. The “Remote Query” is no longer present and the “actual number of rows” now is 50000.

select sname, year, count(*)
from Table1
group by sname,year

Blog5_6.PNG

-> Disabling stretch feature for Table2 by selecting option “Leave Data in Azure”.

Blog5_7.PNG

Blog5_8

Blog5_9

Blog5_10

-> Executing the below query to see what all tables are part of stretch database. We dont see any tables now,

select object_name (object_id),* from sys.remote_data_archive_tables

Blog5_11

-> Checking the execution plan after executing the below query. The “Remote Query” is no longer present and the “actual number of rows” now is 10000. It is clear that all the rows in azure is not migrated back to On-Premise database.

select sname, year, count(*)
from Table2
group by sname,year

Blog5_13

Blog5_12

-> Now that the tables are done, I will disable the stretch feature for the database,

Blog5_14.PNG

Blog5_15

Blog5_16

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Adding a table to the stretch database

-> Please refer “https://jbswiki.com/2017/06/15/stretch-database-in-sql-server-2016/” if you want to setup Stretch database from scratch. In this article, I will be adding table Table3 to the existing stretch database.

-> Let’s create the required objects for this demo on the already configured Stretch database JB_StretchDB,

use JB_StretchDB
go
create table Table3(
sno int primary key identity (1,1),
sname varchar(255),
Year int)

-> Populating tables Table3 with some data,

set nocount on
insert into Table3 values(replicate(‘A’,25),1980)
go 10000
insert into Table3 values(replicate(‘B’,25),1981)
go 10000
insert into Table3 values(replicate(‘C’,25),1982)
go 10000
insert into Table3 values(replicate(‘D’,25),1983)
go 10000
insert into Table3 values(replicate(‘E’,25),1984)
go 10000

-> Looking at the data,

select sname, year, count(*)
from Table3
group by sname,year

Blog4_1

-> Select the table that will be added to stretch database and perform as shown in the screenshot,

Blog4_2

Blog4_3

-> Click on “Entire Table” under “Migrate”.

Blog4_4

Blog4_5.PNG

Blog4_6.PNG

Blog4_7

Blog4_8.PNG

-> Monitoring the status for table3,

Blog4_9.PNG

Blog4_10.PNG

-> Executing the below query and looking at the execution plan. The execution plan shows us that we are querying 10000 rows from the On-Premise database and 40000 rows from the Azure database.

select sname, year, count(*)
from Table3
group by sname,year

Blog4_11.PNG

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.