Setting up SSRS Availibilty groups on SQL 2016 Enterprise

Question

Setting up SSRS Availibilty groups on SQL 2016 Enterprise

mpoulter35

SSC-Addicted

Points: 403
More actions
January 17, 2018 at 6:57 am

#327202

Are you able to add reportserverdb and reportservertempdb to an existing always on group or do they have to be in a separate group?
Does anyone have any step by step instruction on how to setup SSRS to fail over properly?

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

mpoulter35 - Wednesday, January 17, 2018 6:57 AM
Are you able to add reportserverdb and reportservertempdb to an existing always on group or do they have to be in a separate group?
Does anyone have any step by step instruction on how to setup SSRS to fail over properly?

You can put them in an availability group but the service isn't going to automatically start using any other replica's - it's a process you would need to manage yourself.
Reporting services is also not cluster aware so there is no automatic failover. The following documentation provides more information:
Reporting Services with Always On Availability Groups (SQL Server)
High availability in SQL Server Reporting Services

You can find some of the processes you would need to manage discussed in the following article:
Implementing Automatic Failover of SQL Server Reporting Services on Availability Groups

Sue

mpoulter35 SSC-Addicted Points: 403 More actions · Answer 2

Thank Sue,

The last step say "Update the database connection to use the Lister DNS Name. for natve mode report servers, change the Report Server Database Name in Reporting Services configuration manager. For SharePoint mode, change the Database server name for the Reporting Services service application(s)."

How do I do this, do I have to create a new database with the listeners name?

Sue_H SSC Guru Points: 90860 More actions · Answer 3

mpoulter35 - Wednesday, January 17, 2018 12:39 PM
Thank Sue,
The last step say "Update the database connection to use the Lister DNS Name. for natve mode report servers, change the Report Server Database Name in Reporting Services configuration manager. For SharePoint mode, change the Database server name for the Reporting Services service application(s)."
How do I do this, do I have to create a new database with the listeners name?

It's referring to the server name in the top text box when you open Reporting Services Configuration Manager.

Sue

mpoulter35 SSC-Addicted Points: 403 More actions · Answer 4

Thanks Sue,

I have changed the server name to the dns listener but getting an error when clicking service account.

There was an error while switching panels. This the most likely cause is an error retrieving WMI properties. The exception details are below: The network path not found. Cant seem to find any info an this error.

Any Thoughts?

Thanks

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

mpoulter35 - Wednesday, January 17, 2018 6:57 AM
Are you able to add reportserverdb and reportservertempdb to an existing always on group or do they have to be in a separate group?
Does anyone have any step by step instruction on how to setup SSRS to fail over properly?

If you put them into an AG then you'll need to configure the SSRS instances as a scaled out deployment, this will require enterprise edition for both features. You could employ standard edition for the database engine and use a basic AG configuration.

When configuring the SSRS instances to connect to the SSRS database you need to supply the listener detail to ensure that you connect to the AG primary at all times.
Microsoft have step by step instructions in the links posted above.
Read through these and post back if you're still stuck

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

mpoulter35 SSC-Addicted Points: 403 More actions · Answer 6

Wouldn't doing a scale out deployment mean I would need to license both servers? Plan is to have the secondary as a passive SQL server

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

mpoulter35 - Thursday, January 18, 2018 9:12 AM
Wouldn't doing a scale out deployment mean I would need to license both servers? Plan is to have the secondary as a passive SQL server

Yes, both SSRS instances need licensing. Unless of course you only install SSRS to one node, how many instances do you plan to have, do they sit on separate servers to the WSFC?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Michael L John One Orange Chip Points: 27055 More actions · Answer 8

mpoulter35 - Thursday, January 18, 2018 9:12 AM
Wouldn't doing a scale out deployment mean I would need to license both servers? Plan is to have the secondary as a passive SQL server

It appears you have a couple things confused. Using my current environment as an example, here's how we have it set up:

There are 3 servers in the AG, two set up as synchronous commit with automatic failover, and the third at a co-lo with manual failover and asynchronous commit. These contain the SSRS database, as well as a few others.
Regardless of which of these servers is the primary in the AG, everything connects to the listener.

There are 2 separate SSRS web servers, installed and configured in a scale out deployment. They are configured to point to the Availability Group Listener. Both of these can be access via the same URL. That's handled at the network level.

You referred to a couple things that make no sense. "DNS Listener". Describe that please. A listener is created when you configure an availability group. Certainly this needs to be registered in DNS, but it's not normally handled by DNS.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

mpoulter35 SSC-Addicted Points: 403 More actions · Answer 9

Michael L John - Thursday, January 18, 2018 9:57 AM
mpoulter35 - Thursday, January 18, 2018 9:12 AM
Wouldn't doing a scale out deployment mean I would need to license both servers? Plan is to have the secondary as a passive SQL server
It appears you have a couple things confused. Using my current environment as an example, here's how we have it set up:
There are 3 servers in the AG, two set up as synchronous commit with automatic failover, and the third at a co-lo with manual failover and asynchronous commit. These contain the SSRS database, as well as a few others.
Regardless of which of these servers is the primary in the AG, everything connects to the listener.
There are 2 separate SSRS web servers, installed and configured in a scale out deployment. They are configured to point to the Availability Group Listener. Both of these can be access via the same URL. That's handled at the network level.
You referred to a couple things that make no sense. "DNS Listener". Describe that please. A listener is created when you configure an availability group. Certainly this needs to be registered in DNS, but it's not normally handled by DNS.

Sorry i meant AG listener.
So there is no way to have reports working in a AG group unless and license both servers?

From the link below from microsoft it doesn't mention scale out deployment.
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/reporting-services-with-always-on-availability-groups-sql-server

Sorry if i am not understanding, been thrown into setting up this new SQL environment and don't have any experience with SQL

Perry Whittle SSC Guru Points: 234013 More actions · Answer 10

mpoulter35 - Thursday, January 18, 2018 1:17 PM
Michael L John - Thursday, January 18, 2018 9:57 AM
mpoulter35 - Thursday, January 18, 2018 9:12 AM
Wouldn't doing a scale out deployment mean I would need to license both servers? Plan is to have the secondary as a passive SQL server
It appears you have a couple things confused. Using my current environment as an example, here's how we have it set up:
There are 3 servers in the AG, two set up as synchronous commit with automatic failover, and the third at a co-lo with manual failover and asynchronous commit. These contain the SSRS database, as well as a few others.
Regardless of which of these servers is the primary in the AG, everything connects to the listener.
There are 2 separate SSRS web servers, installed and configured in a scale out deployment. They are configured to point to the Availability Group Listener. Both of these can be access via the same URL. That's handled at the network level.
You referred to a couple things that make no sense. "DNS Listener". Describe that please. A listener is created when you configure an availability group. Certainly this needs to be registered in DNS, but it's not normally handled by DNS.
Sorry i meant AG listener.
So there is no way to have reports working in a AG group unless and license both servers?
From the link below from microsoft it doesn't mention scale out deployment.
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/reporting-services-with-always-on-availability-groups-sql-server
Sorry if i am not understanding, been thrown into setting up this new SQL environment and don't have any experience with SQL

Do you intend to have a single SSRS instance?
If yes, then you do not need a scaled out deployment of SSRS and can use an Availability Group for your SSRS databases.

However, functional AGs require Enterprise edition of SQL Server.
You can implement a basic Availability Group in Standard Edition but it is as the name suggests "basic" and can only have 1 database per group

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

mpoulter35 SSC-Addicted Points: 403 More actions · Answer 11

Perry Whittle - Friday, January 19, 2018 4:51 AM
mpoulter35 - Thursday, January 18, 2018 1:17 PM
Michael L John - Thursday, January 18, 2018 9:57 AM
mpoulter35 - Thursday, January 18, 2018 9:12 AM
Wouldn't doing a scale out deployment mean I would need to license both servers? Plan is to have the secondary as a passive SQL server
It appears you have a couple things confused. Using my current environment as an example, here's how we have it set up:
There are 3 servers in the AG, two set up as synchronous commit with automatic failover, and the third at a co-lo with manual failover and asynchronous commit. These contain the SSRS database, as well as a few others.
Regardless of which of these servers is the primary in the AG, everything connects to the listener.
There are 2 separate SSRS web servers, installed and configured in a scale out deployment. They are configured to point to the Availability Group Listener. Both of these can be access via the same URL. That's handled at the network level.
You referred to a couple things that make no sense. "DNS Listener". Describe that please. A listener is created when you configure an availability group. Certainly this needs to be registered in DNS, but it's not normally handled by DNS.
Sorry i meant AG listener.
So there is no way to have reports working in a AG group unless and license both servers?
From the link below from microsoft it doesn't mention scale out deployment.
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/reporting-services-with-always-on-availability-groups-sql-server
Sorry if i am not understanding, been thrown into setting up this new SQL environment and don't have any experience with SQL
Do you intend to have a single SSRS instance?
If yes, then you do not need a scaled out deployment of SSRS and can use an Availability Group for your SSRS databases.
However, functional AGs require Enterprise edition of SQL Server.
You can implement a basic Availability Group in Standard Edition but it is as the name suggests "basic" and can only have 1 database per group

Yes only one instance of SSRS.
I have an AG group setup with both reportserver db and reportservertempdb. I have updated the database connection to use the Lister DNS Name. Its setup as synchronouscommit and automatic fail over

I follow the below steps from microsoft about stop agent that was primary and start agent on new primary which is already started. I stop and start the report service on both the new primary and secondary but not working
When trying to go to reports page show page cant be displayed and when going to reportsserver page i get below message
"The report server was unable to validate the integrity of encrypted data in the database. (rsCannotValidateEncryptedData)"

mpoulter35 SSC-Addicted Points: 403 More actions · Answer 12

Sue_H - Wednesday, January 17, 2018 12:57 PM
mpoulter35 - Wednesday, January 17, 2018 12:39 PM
Thank Sue,
The last step say "Update the database connection to use the Lister DNS Name. for natve mode report servers, change the Report Server Database Name in Reporting Services configuration manager. For SharePoint mode, change the Database server name for the Reporting Services service application(s)."
How do I do this, do I have to create a new database with the listeners name?
It's referring to the server name in the top text box when you open Reporting Services Configuration Manager.
Sue

So when launch report configurtion I need to change the server name to the listener and then connect?

Perry Whittle SSC Guru Points: 234013 More actions · Answer 13

mpoulter35 - Friday, January 19, 2018 6:08 AM
Perry Whittle - Friday, January 19, 2018 4:51 AM
mpoulter35 - Thursday, January 18, 2018 1:17 PM
Michael L John - Thursday, January 18, 2018 9:57 AM
mpoulter35 - Thursday, January 18, 2018 9:12 AM
Wouldn't doing a scale out deployment mean I would need to license both servers? Plan is to have the secondary as a passive SQL server
It appears you have a couple things confused. Using my current environment as an example, here's how we have it set up:
There are 3 servers in the AG, two set up as synchronous commit with automatic failover, and the third at a co-lo with manual failover and asynchronous commit. These contain the SSRS database, as well as a few others.
Regardless of which of these servers is the primary in the AG, everything connects to the listener.
There are 2 separate SSRS web servers, installed and configured in a scale out deployment. They are configured to point to the Availability Group Listener. Both of these can be access via the same URL. That's handled at the network level.
You referred to a couple things that make no sense. "DNS Listener". Describe that please. A listener is created when you configure an availability group. Certainly this needs to be registered in DNS, but it's not normally handled by DNS.
Sorry i meant AG listener.
So there is no way to have reports working in a AG group unless and license both servers?
From the link below from microsoft it doesn't mention scale out deployment.
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/reporting-services-with-always-on-availability-groups-sql-server
Sorry if i am not understanding, been thrown into setting up this new SQL environment and don't have any experience with SQL
Do you intend to have a single SSRS instance?
If yes, then you do not need a scaled out deployment of SSRS and can use an Availability Group for your SSRS databases.
However, functional AGs require Enterprise edition of SQL Server.
You can implement a basic Availability Group in Standard Edition but it is as the name suggests "basic" and can only have 1 database per group
Yes only one instance of SSRS.
I have an AG group setup with both reportserver db and reportservertempdb. I have updated the database connection to use the Lister DNS Name. Its setup as synchronouscommit and automatic fail over
I follow the below steps from microsoft about stop agent that was primary and start agent on new primary which is already started. I stop and start the report service on both the new primary and secondary but not working
When trying to go to reports page show page cant be displayed and when going to reportsserver page i get below message
"The report server was unable to validate the integrity of encrypted data in the database. (rsCannotValidateEncryptedData)"

To be clear, when you create the availability group you then create the listener, then when you configure the SSRS instance you specify the listener name for the database connection.
What port number did you specify for your listener you created?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

mpoulter35 SSC-Addicted Points: 403 More actions · Answer 14

Perry Whittle - Monday, January 22, 2018 10:10 AM
mpoulter35 - Friday, January 19, 2018 6:08 AM
Perry Whittle - Friday, January 19, 2018 4:51 AM
mpoulter35 - Thursday, January 18, 2018 1:17 PM
Michael L John - Thursday, January 18, 2018 9:57 AM
mpoulter35 - Thursday, January 18, 2018 9:12 AM
Wouldn't doing a scale out deployment mean I would need to license both servers? Plan is to have the secondary as a passive SQL server
It appears you have a couple things confused. Using my current environment as an example, here's how we have it set up:
There are 3 servers in the AG, two set up as synchronous commit with automatic failover, and the third at a co-lo with manual failover and asynchronous commit. These contain the SSRS database, as well as a few others.
Regardless of which of these servers is the primary in the AG, everything connects to the listener.
There are 2 separate SSRS web servers, installed and configured in a scale out deployment. They are configured to point to the Availability Group Listener. Both of these can be access via the same URL. That's handled at the network level.
You referred to a couple things that make no sense. "DNS Listener". Describe that please. A listener is created when you configure an availability group. Certainly this needs to be registered in DNS, but it's not normally handled by DNS.
Sorry i meant AG listener.
So there is no way to have reports working in a AG group unless and license both servers?
From the link below from microsoft it doesn't mention scale out deployment.
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/reporting-services-with-always-on-availability-groups-sql-server
Sorry if i am not understanding, been thrown into setting up this new SQL environment and don't have any experience with SQL
Do you intend to have a single SSRS instance?
If yes, then you do not need a scaled out deployment of SSRS and can use an Availability Group for your SSRS databases.
However, functional AGs require Enterprise edition of SQL Server.
You can implement a basic Availability Group in Standard Edition but it is as the name suggests "basic" and can only have 1 database per group
Yes only one instance of SSRS.
I have an AG group setup with both reportserver db and reportservertempdb. I have updated the database connection to use the Lister DNS Name. Its setup as synchronouscommit and automatic fail over
I follow the below steps from microsoft about stop agent that was primary and start agent on new primary which is already started. I stop and start the report service on both the new primary and secondary but not working
When trying to go to reports page show page cant be displayed and when going to reportsserver page i get below message
"The report server was unable to validate the integrity of encrypted data in the database. (rsCannotValidateEncryptedData)"
To be clear, when you create the availability group you then create the listener, then when you configure the SSRS instance you specify the listener name for the database connection.
What port number did you specify for your listener you created?

SSRS was already configured

Perry Whittle - Monday, January 22, 2018 10:10 AM
mpoulter35 - Friday, January 19, 2018 6:08 AM
Perry Whittle - Friday, January 19, 2018 4:51 AM
mpoulter35 - Thursday, January 18, 2018 1:17 PM
Michael L John - Thursday, January 18, 2018 9:57 AM
mpoulter35 - Thursday, January 18, 2018 9:12 AM
Wouldn't doing a scale out deployment mean I would need to license both servers? Plan is to have the secondary as a passive SQL server
It appears you have a couple things confused. Using my current environment as an example, here's how we have it set up:
There are 3 servers in the AG, two set up as synchronous commit with automatic failover, and the third at a co-lo with manual failover and asynchronous commit. These contain the SSRS database, as well as a few others.
Regardless of which of these servers is the primary in the AG, everything connects to the listener.
There are 2 separate SSRS web servers, installed and configured in a scale out deployment. They are configured to point to the Availability Group Listener. Both of these can be access via the same URL. That's handled at the network level.
You referred to a couple things that make no sense. "DNS Listener". Describe that please. A listener is created when you configure an availability group. Certainly this needs to be registered in DNS, but it's not normally handled by DNS.
Sorry i meant AG listener.
So there is no way to have reports working in a AG group unless and license both servers?
From the link below from microsoft it doesn't mention scale out deployment.
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/reporting-services-with-always-on-availability-groups-sql-server
Sorry if i am not understanding, been thrown into setting up this new SQL environment and don't have any experience with SQL
Do you intend to have a single SSRS instance?
If yes, then you do not need a scaled out deployment of SSRS and can use an Availability Group for your SSRS databases.
However, functional AGs require Enterprise edition of SQL Server.
You can implement a basic Availability Group in Standard Edition but it is as the name suggests "basic" and can only have 1 database per group
Yes only one instance of SSRS.
I have an AG group setup with both reportserver db and reportservertempdb. I have updated the database connection to use the Lister DNS Name. Its setup as synchronouscommit and automatic fail over
I follow the below steps from microsoft about stop agent that was primary and start agent on new primary which is already started. I stop and start the report service on both the new primary and secondary but not working
When trying to go to reports page show page cant be displayed and when going to reportsserver page i get below message
"The report server was unable to validate the integrity of encrypted data in the database. (rsCannotValidateEncryptedData)"
To be clear, when you create the availability group you then create the listener, then when you configure the SSRS instance you specify the listener name for the database connection.
What port number did you specify for your listener you created?

SSRS was already configured when I set up the the availability group. I did go into report configuration and change the SQL server name to what I used for the listener.
Port number i used was 1433 for listener