reporting services for sql 2019

Microsoft Power BI Blog

• Announcements
• Report Server

What’s new for SQL Server 2019 Reporting Services RC1

We are pleased to announce the first release candidate of SQL Server 2019 Reporting Services is now available in the download center.  While the team has previously released several updates for SQL Server customers via Power BI Report Server , this is the first feature update to the core SQL Server Reporting Services product for the 2019 release wave.

Download SQL Server 2019 Reporting Services

With this release, we’ve added new features to help customers meet their changing business needs around cloud-readiness and accessibility.  These include –

• Support for hosting your database catalog with Azure SQL Managed Instance
• Support for Power BI datasets hosted in Power BI Premium
• Using Azure AD Application Proxy with Reporting Services
• A new version of Microsoft Report Builder updated for use with SSRS 2019
• AltText (alternative text) in support of WCAG and 508 compliance for your report elements
• PDF/UA support for PDF documents (coming soon)
• Rollup of several security updates and bug fixes previously released in Power BI Report Server

Azure SQL Managed Instance support

Managed instance is a new deployment option of Azure SQL Database, providing near 100% compatibility with the latest SQL Server release.  Now, for the first time, you may use a Managed Instance to host your database catalog used for SQL Server Reporting Services that is hosted either in a VM or in your data center.  Please note that support is limited to using database credentials for the connection to SQL MI.

Power BI Premium dataset support

In March, the Power BI team announced support for connectivity via the XMLA endpoint to Analysis Services models hosted in Premium workspaces.  For the first time, customers can connect to those models using either Microsoft Report Builder or SQL Server Data Tools and publish those reports to SSRS 2019 using the SQL Server Analysis Services connectivity option.  Users will need to use a stored Windows Username/Password to enable the scenario.

Microsoft Report Builder update

Later this week, we’ll have an update to the SQL Server 2016 Report Builder release.  This release updates all existing installs of that product.  It is fully compatible with the 2016, 2017, and 2019 versions of Reporting Services, along with all released and supported versions of Power BI Report Server.  New features include the ability to connect to a Power BI Premium dataset and enter data directly in a report .

AltText (alternative text) support for report elements

Meeting customers accessibility needs is an important part of the software development process here at Microsoft.  We’ve enhanced several accessibility items for report authors and viewers in this release, including official support for Alternative Text for report elements.  Using tooltips, authors may specify text for each element on the report and have this properly identified by screen reader technology as such.

Beyond what we’ve called out here, some additional features will be available prior to our GA release, including  PDF/UA support for PDF documents.  We will announce those as soon as they are available.

As many of you know, we recently released paginated reports in Power BI , and are continuing to add more features there.  We continue to see Reporting Services as a key part of the SQL Server value proposition, and much like the Analysis Services team has done in this 2019 release, we’ll look to bring new RDL capabilities introduced in the cloud to the on-premises product in future releases.

Thanks for reading, and don’t forget to leave us your feedback in the comments below.

• Report Builder
• Reporting Services

08 April 2019

33,256 views

• Reporting Services Basics: Overview and Installation

SQL Server Reporting Services has changed quite a bit since it was introduced in 2004. Despite new analytic services like Power BI, SSRS is still a popular tool for paginated reports. In this article, Kathi Kellenberger explains the architecture of SSRS and walks you through installing an SSRS development environment on your workstation or laptop.

The series so far:

• Reporting Services Basics: Creating Your First Report
• Reporting Services Basics: Data Sources and Datasets
• Reporting Services Basics: Parameters
• Reporting Services Basics: Adding Groups to Reports

SQL Server Reporting Services (SSRS) is a server-based reporting platform that allows you to create and manage a wide variety of different types of reports and deliver them in a range of formats. You can create basic reports containing tables and graphs, or more complex data visualizations, using charts, maps and sparklines. You can even create dashboards that will run on your phone! Reports can draw their data from SQL Server databases, but also from other relational database such as Oracle or Azure SQL Database, and other types of data sources such as Analysis Services.

You can present your finished reports from the Reporting Services website typically running on premises, called the Web Portal, or users can view them within web- or Windows-based applications. Reports can also be viewed in SharePoint with a special webpart. (In earlier versions of SSRS, there was also a SharePoint integrated mode.) End users can run reports on demand or schedule them as subscriptions. This article is the first in a series that will provide in-depth coverage of the basics of report development with SSRS.

The report development examples in this series will work for SSRS 2017 and later. Many of the examples will also work for earlier versions. If you are still using SSRS 2005, please refer to Steve Joubert’s original SSRS series . If you are working with 2008 to 2012, look at the series I wrote a few years ago.

SSRS Through the Ages

SSRS has been around since 2004, first shipping as an “add-on” for SQL Server 2000, and then fully integrated into SQL Server 2005. I first saw it demonstrated at the 2003 PASS Summit and had the Beta version installed and running at work as soon as I could get my hands on the media. Back in 2003, the SQL Server community were very excited about SSRS as it made available, for the first time, built-in reporting capabilities for SQL Server. If you owned SQL Server, you owned SSRS.

Since its integration into SQL Server 2005, Microsoft has made a number of improvements over the years and gave it a complete overhaul in 2016. One big change in 2016 was the renaming of the website from Report Manager to Web Portal. In 2017, SharePoint integrated mode was discontinued, but a special version for hosting Power BI dashboards called Power BI Reporting Services became available.

SSRS Architecture

An SSRS deployment must be associated with a SQL Server instance. On the instance will be two databases, named by default:

ReportServer – contains the report definitions, configuration, history, security of deployed reports and more

ReportServerTempdb – much like tempdb, it is used as a workspace for building reports and doesn’t maintain any objects permanently.

You will also need a location for the Report Server Web Service , which can be on the same server as the databases, as in the simple deployment architecture shown in Figure 1, or on a different server. On whichever server you choose, you will have access to the Web Portal that allows you to deploy and manage the reports. The data sources will typically be found on other servers throughout the network. End users can run reports from the Web Portal, create subscriptions, and publish their own reports if they have permission.

Figure 1: A simple SSRS deployment

The end user sends an HTTP request for a report, providing any required parameters. The SSRS server finds the metadata of the report and sends a request for data to the data sources. The data returned by the data sources is merged with the report definition into a report. As the report is generated, it is returned to the client. There are two layers of security to get past before viewing the report.

SSRS Security

You have many options for securing the reports. The first layer of security is built into the Web Portal. You can control access to folders and individual reports based on network groups or user accounts. The second layer of security is to the database. You can either base the security on the individuals running the report or store an account within the data source that can be passed on to the database system. Security will be covered in more detail in a later article on deploying reports.

Tools for Building SSRS Reports

SSRS reports are XML files with the extension RDL. If you were a robot instead of a human, you might be able to just create the RDL files with a text editor. Instead, there are a number of tools from Microsoft for building reports that can be hosted in the SSRS Web Portal depending on the type of report and who is building the report.

Traditional SSRS reports, which also might be called paginated reports, can be build using an extension for Visual Studio (VS) or Report Builder. Developers will be more comfortable working in the VS environment. VS is based on a solution/project model and integrates with version control software such as Azure DevOps formerly known as Visual Studio Team Services. This is the tool you will use for this series of articles.

Report Builder is meant more for power users to create their own reports, but developers in some shops use this tool in place of VS. Instead of a project model, each report is independently created. One advantage of Report Builder is that it has an Office look and feel which makes it more comfortable for non-developers to use. It also has more wizards to help beginners get started. Users of Report Builder can take advantage of published report parts to make building a dashboard simple. Figure 2 shows what Report Builder looks like when it’s launched:

Figure 2: Report Builder

A new type of report available with SSRS 2016 is called Mobile Reports. Mobile Reports must be created with the Mobile Report Publisher which is launched from the Web Portal. These reports are dashboards which can run on phones and tablets as well as the Web Portal. (If you are a previous developer or user of Datazen reports, these will look familiar to you as Microsoft acquired the company and brought the technology into SSRS). Figure 3 shows what the Mobile Report Publisher looks like:

Figure 3: Mobile Report Publisher

Power BI dashboards can also run in a special type of SSRS Web Portal called Power BI Report Server. The tool for creating Power BI dashboards is called Power BI Desktop. Power BI is out of scope for this series, but if you would like to learn more about this topic, take a look at the article series written by Robert Sheldon.

Installing and Configuring SQL Server and Reporting Services

Since this series is focused on report development, it will cover just enough about installation and configuration to help you get things set up on your development computer. This section is not intended to provide information for installing SQL Server or SSRS in a production environment. 

To follow along with this series of articles, you will need to install the following:

• A SQL Server instance
• SQL Server Management Studio (SSMS)
• Visual Studio (SSDT with VS 2017 or an extension with VS 2019)
• Report Server 2017 

You should be running a Windows 10 computer, making sure that there is at least 50 GB free on the hard drive. You will also need to restore the AdventureWorks sample database to follow along with the examples.

At the time of this writing, SQL Server 2019 is available as a Community Technology Preview (CTP), which is a fancy way of saying Beta version. The following instructions are based on SQL Server 2017, the latest generally available version. One big change made in the installation of SSRS in 2017 is that the SSRS media must be downloaded and installed separately.

SQL Server Instance Installation

If you already have a SQL Server instance in place, version 2008 or later, you can skip this section. The instance is used to host the two databases used by SSRS and the sample databases used in the examples.

To install the SQL Server instance, you will need to first download the media. I will not provide a link here, because invariably it will change, so just search for “SQL Server 2017 downloads.” The page that was current as of this writing had links for a free trial and for a Developer Edition shown in Figure 4. Choose the Developer edition and save the downloaded file.

Figure 4: The SQL Server media download page

I’m not going to show you every step of installing SQL Server because that information can be found elsewhere. Here are the important things to note:

• The page mentioned above allows you to download the actual media. Do that first.
• Once you have downloaded the media, run setup.exe to launch the SQL Server Installation Center . You’ll need to click New SQL Server stand-alone installation on the Installation page shown on Figure 5.

Figure 5: Launch the installation

Make sure to choose the Developer Edition shown in Figure 6.

Figure 6: The Developer edition

The only instance feature you will need for learning SSRS is the Database Engine Services shown in Figure 7.

Figure 7: Features

To make things simpler, just install the Default instance shown in Figure 8. If you have already installed an instance, you’ll see it listed. If that’s the case, you may want to just cancel out of the wizard at this point and use the previously installed instance.

Figure 8: Install the Default instance

Be sure to click Add current user to make your account an administrator. You may also want to set the security to Mixed mode . Figure 9 shows these options.

Figure 9: Security settings

Review the summary shown in Figure 10 and go make yourself a cup or coffee or tea after clicking Install because it could take 10 or 20 minutes for the installation to run.

Figure 10: The Summary

Figure 11 shows that the installation was a success!

Figure 11: Success!

SSMS Installation

SQL Server Management Studio (SSMS) is the tool you will use to connect to the SQL Server instance to restore the sample databases and run queries. If you already have this tool in place, you can skip to the next section.

Microsoft is now releasing new versions of SSMS on a frequent basis, so it is no longer installed during the database engine installation. You can search for “SSMS download” to find the latest version. You can also find a link on the Installation page of the SQL Server 2017 Installation Center, if you used this to install your instance. You can launch it again from the Windows Start menu. Figure 12 shows where you can find the link.

Figure 12: Launching the download page for SSMS

Once you have downloaded the media for SSMS, just run the installation wizard accepting the defaults.

SSRS Service Installation and Configuration

Previous versions of SQL Server allowed you to include SSRS during the installation of the database engine, but that is no longer the case beginning with 2017. Installing them together was quite convenient since the installation took care of the SSRS configuration for you automatically. Now, you must download the media separately and configure SSRS yourself. It’s not difficult, but I’ll walk you through the steps.

Begin by searching for “Download SSRS” or you can also find a link to the download page from the SQL Server 2017 Installation Center shown in Figure 13.

Figure 13: A link to the download page for SSRS

Save and run the downloaded file. To get the installation started, click Install Reporting Services shown in Figure 14.

Figure 14: Install Reporting Services

Click through the installation wizard. The most important question for you to answer is to specify the edition. Make sure that you select the Developer edition.

Once the installation is complete, you’ll be asked to configure the report server. If you are required to restart, do that first. After you restart, you will need to launch the Report Server Configuration Manager. You can do this from the item found in the Windows Start menu or from the final page of the SSRS installation shown in Figure 15 if you didn’t have to restart.

Figure 15: The final page of the SSRS installation wizard

Either way you launch Report Server Configuration Manager, you will need to connect as shown in Figure 16.

Figure 16: Connect to SSRS

There are three items that must be set up when configuring SSRS just to get it running:

• Web Service URL
• Web Portal URL

Click Database on the left menu. Click Change Database shown in Figure 17.

Figure 17: Click Change Database

This will launch the Report Server Database Configuration Wizard . Leave Create a new report server database selected and click Next as shown in Figure 18.

Figure 18: Create a new database

On the following screen, make sure that the local computer name is filled in. If you are using a named instance, then the Server Name will be computer\instance . If you’re not sure, take a look at the “Connecting to Your SQL Server Instance” section later in this article. Leave the Authentication type set at Current User – Integrated Security and click Next as shown in Figure 19.

Figure 19: The server name

On the next page, you will specify the SSRS database name. The default database name is ReportServer . Figure 20 shows that you can leave it at the default and click Next .

Figure 20: The database name

Leave the Authentication Type set at Service Credentials and click Next as shown in Figure 21.

Figure 21: The authentication type

That’s the last question you’ll be asked about the database. Complete the wizard to create the database. Once the database is in place, click Web Service URL .

Leave the defaults in place and click Apply as shown in Figure 22.

Figure 22: The Web Service URL

Click Web Portal URL . Again, leave the defaults in place and click Apply as shown in Figure 23.

Figure 23: The Web Portal URL

Make a note of the URL. This is what you’ll use to connect to the Web Portal after deploying reports. Once done, exit out of the Report Server Configuration Manager. Now that the SSRS service is in place, the next step is installing the development tool.

Installing in Visual Studio

NOTE: At this time, you can install in one step with VS 2017 using SSDT. If you have VS 2019 installed, add the SSRS extension. The instructions in this section cover the 2017 instructions.

You’ll use the SSRS extension for VS to develop reports, and this is the last item to install. You can search for “SSDT Download” or launch the page from the SQL Server 2017 Installation Center shown in Figure 24.

Figure 24: The link to the SSDT download

You will need to scroll down the web page to find the link for SSDT standalone installer as shown in Figure 25.

Figure 25: The SSDT installer

Once downloaded, run the file. Make sure that SQL Server Reporting Services is selected and click Install as shown in Figure 26.

Figure 26: Install SSDT

Accept any other defaults and complete the installation.

Connecting to Your SQL Server Instance

You may have trouble connecting to your local SQL Server instance for a couple of reasons. First, you may not know the actual server name needed to connect to it, especially if you have installed a named instance. The other problem may be that it’s just not running. I’ve seen that happen quite often with laptops.

To solve both problems, launch SQL Server Configuration Manager . You may find it in the SQL Server programs section of the Windows Start menu. If not, take a look at this post for more information. With this tool, you can see the instance name as well as start up an instance that isn’t running. Click SQL Server Services and take a look at the items on the right as shown in Figure 27.

Figure 27: The SQL Server Configuration Manager

Default instances are called SQL Server (MSQLSERVER) . When connecting to the default instance locally, you just need the computer name or an equivalent:

• . (That’s just a period!)

If you see something else in parentheses after the words SQL Server, that’s a named instance. To connect to a named instance, you’ll need the computer name followed by a backslash and the instance name. Here are some examples assuming that my computer is called MyComputerName :

• MyComputerName\SQL2017
• Localhost\SQL2017
• (local)\SQL2017

Notice in the previous image that the default instance is stopped. Just right-click the instance and choose Start to get it running.

Now that you know the name of your SQL Server and have ensured that it’s running, launch SSMS and connect to it as shown in Figure 28.

Figure 28: Connect to the SQL Server

You’ll see the SQL Server and several folders in the Object Explorer window shown in Figure 29.

Figure 29: The Object Explorer

Restoring a Sample Database

To follow along with the examples in this series of articles, you’ll need to restore one or more sample databases. The main database that will be used for the examples is called AdventureWorks2017 . At the time of this writing, Microsoft is hosting the sample databases on GitHub, a well-known software repository site. Search for the AdventureWork2017.bak file. A bak file is a backup file, and that’s what you need. Figure 30 shows the download page.

Figure 30: Download AdventureWorks

Do not download the file to your Desktop , Documents , or Downloads file. Since SQL Server is running under an identity that is not you, it can’t see files in those locations. I recommend just downloading to a file in your C:\ drive such as C:\Temp .

Follow these steps to restore the database:

Connect to the SQL Server instance by launching SSMS and connecting to it. If you have trouble, review the “Connecting to Your SQL Server Instance” section.

Right-click on the Databases folder and select Restore Database… as shown in Figure 31.

Figure 31: Choose Restore Database

Select Device and click the ellipses as shown in Figure 32.

Figure 32: Click the elipses.

This brings up the Select backup devices window. Click Add shown in Figure 33.

Figure 33. Click Add

Navigate to the folder where the bak file is stored as shown in Figure 34.

Figure 34: Navigate to the backup file

Select the file and click OK . You’ll click OK two more times to fire off the restore.

Once the database has been restored, you’ll see the message shown in Figure 35. Click OK two more times to dismiss the dialogs.

Figure 35: A successful restore

If you have followed the instructions in this article, you now have a development environment for SSRS set up on your workstation or laptop. In the next article, I’ll show you how to create some basic reports.

Subscribe for more articles

Fortnightly newsletters help sharpen your skills and keep you ahead, with articles, ebooks and opinion to keep you informed.

Rate this article

Kathi Kellenberger

Kathi Kellenberger is a Customer Success Engineer at Redgate and a Microsoft Data Platform MVP. She has worked with SQL Server for over 20 years and has authored, co-authored, or tech edited more than 20 technical books. Kathi is a volunteer at LaunchCode, the St. Louis based organization providing free training and paid apprenticeships in technology. When Kathi isn’t working she enjoys spending time with family and friends, cycling, singing, and climbing the stairs of tall buildings. Be sure to check out her courses on Pluralsight .

Follow Kathi Kellenberger via

View all articles by Kathi Kellenberger

Load comments

Related articles

PowerBI premium capacity administration: dataset monitoring and tuning

• SQL Server training
• Write for us!

Configure SQL Server Reporting Services databases in SQL Server Always On Availability Groups

This is the 25 th article in SQL Server Always On Availability Groups series. In this article, we will explore the high availability configuration for reporting services databases.

Configure SQL Server Reporting Services for standalone databases

SQL Server Reporting Services (commonly known as SSRS) is a service to deploy paginated reports for web and mobile. Data representation is an art, and SSRS helps you to visualize the data in an informative way using various graphs, charts, KPI, matrix.

Image Source: Microsoft docs

Until SQL Server 2016, the reporting services comes with the SQL Server installation. If we choose the reporting services feature, it installs the reporting services and configures its databases in the local instance.

Starting from SQL Server 2017, Microsoft separated the reporting services from the standard SQL Server installation. It is a standalone application that you can download, install, upgrade independently.

In the below image, you see the SSRS architecture diagram with various components involved. You can refer to the article, SQL Server Reporting Services Architecture and Component Topology to understand these components in detail.

As per best practice, we should use the reporting services separated from the database instance. For this article, firstly, we configure the reporting services for the following SQL environment.

• Reporting Services: SQLNode3\INST1
• Reporting services database: SQLNode1\INST1

It creates the [ReportServer] and [ReportServerTempDB] database on the SQLNode1\INST1. As of now, these databases are not part of any SQL Server Always On Availability Groups.

You can download the Microsoft SQL Server 2019 Reporting Services from the Microsoft.

Launch the SSRS installation with the following welcome screen.

Choose an edition. We use the developer edition of SQL Server Reporting Services 2019.

On the next page, it shows that it installs the reporting services only. It does not install the SQL database engine for you that is required to store SSRS databases. You should install SQL Services on it or a separate instance. As highlighted earlier, we use the SQLNode1\INST1 for the SSRS databases.

Select the reporting services installation directory. We can go ahead with the default directory.

After installation, it asks you to configure the report server using the Reporting Services Configuration Manager. Click on Configure Report Server.

It opens the Reporting Services Configuration Manager. Click on Start to run the reporting services.

We do a minimum required configuration in this article. You can refer to this article, SQL Server Reporting Service Configuration Manager to understand complete SSRS configurations.

Service account configuration: In the service account page, specify a domain account to run the reporting service. It automatically restarts the reporting services once you apply the service accounts

Web Service URL: It prefills the default configuration for the SSRS web service URL

Click on Apply, and it configures the web service URL. You can click on the hyperlink to open the web URL.

Report Server Database configuration

First, we need to create the reporting services databases on the SQL instance that works as a primary replica. Currently, it does not show any SSRS database details

Click on Change Database. We do not have any existing database so choose “Create a new report server database”

Specify the SQL instance name (Current primary replica) and authentication type. I use Windows authentication for the DB connection purpose

You get the flexibility to specify the database name in the report server wizard. By default, it creates a new database [ReportServer]. You should not change the database names unless you have any specific requirements

Specify the service credentials to connect with the report server database. SSRS wizard automatically assigns the required permissions for the SQL account we specify here

Verify your configurations in the report server database configuration. You can note here that it creates two SSRS databases – ReportServer and ReportServerTempDB

On the next page, it completes the SSRS configurations, as shown below.

You can see the database name, SQL instance and the credentials in the database page.

Similar to the Web Server URL, configure the Web Portal URL. You get the hyperlink for URL after configuration

Connect to the SQL Server instance, and you can see both databases in the object explorer

Change the recovery model for SSRS databases

As shown above, reporting services databases have the following recovery models:

• ReportServer – Full
• ReportServerTempDB- Simple

As you know, for a database in SQL Server Always On Availability groups, we require databases in the full recovery model. Open the ReportServerTempDB database properties, click on the options and change the recovery model to FULL.

Our both SSRS databases are in the Full recovery model now.

SQL Server Always On Availability Groups for Reporting services database

In this article, we create a new availability group for both reporting services databases. You can add databases in an existing availability group as well. You can refer to earlier articles for detailed steps on creating a new availability group.

In the new availability group wizard, specify the AG group name.

I took full backups of both databases; therefore, it meets prerequisites to be part of an AG.

We have two nodes in the cluster. AG should be in the Synchronized mode.

Specify listener name and IP address for this SQL Server Always On Availability Group. SQL listener is a must for the reporting services database in AG configuration.

Once it creates the new SQL Server Always On Availability Group, launch the dashboard, and we see reporting services databases added successfully.

Deploy a sample report and create a subscription

Deploy a report in your new SSRS configuration. You can refer to SSRS articles on SQLShack for reference purposes.

For this article, I deployed a sample SSRS report. This report connects to the [AdventureWorks] database and pulls the required data.

I have also configured a report server subscription. This subscription export the report in PDF format and stores at a shared location on a scheduled time.

SSRS schedule creates a SQL Server agent job on the SSRS database instance. Connect to your SQL instance and verify that the job exists. You can filter the agent jobs using the Report Server category. Do not modify this job manually in SSMS. You must use SSRS reports for any changes like report schedule, frequency, shared location or email id specifications.

Reconfigure Report Server database configurations to SQL listener

At this step, we have done the following configurations so far in the article.

• Our reporting services databases are configured in the SQL Server Always On Availability Group
• We deployed a sample SSRS report

Think of a question now- If the AG failover happens and SSRS databases become active on the current secondary ( after failover – primary) what happens to my report subscriptions?

As you have seen earlier, the report subscription is a SQL agent job. Do you need to create these jobs on the new primary replica manually? I have hundreds of report subscriptions; Do I need to create all jobs?

We instructed earlier to not perform any changes to these jobs manually. If we script out jobs and configures on the new primary replica, Does it work?

Hold your horses! We have configured the databases in the high availability, but our report server is not aware of any changes? If you perform a failover, the report server won’t recognize the new primary replica instance.

Switch to the Report Server Configuration Manager -> Database -> Change Database ->Choose an existing report server database.

In the server name, specify the SQL listener that we created earlier for SSRS SQL Server Always On Availability Group. A SQL listener always points to the primary replica. Once we configure the listener in the report server configuration, in case of failover, it automatically connects to the databases on a new primary replica.

Select the report server database from the drop-down.

Specify the service credentials.

Verify your configuration. You can verify the SQL Server instance is the SQL listener name along with the port number.

It reconfigures your report server configuration.

Verify the listener’s name in the database tab.

Failover testing for SQL Server Reporting Services (SSRS) in a high availability mode

For testing purposes, perform a manual failover. It makes the SQLNode2\INST1 as the new primary replica.

Our first test passed. AG dashboard is healthy for both SSRS databases after failover as well.

Launch the SSRS portal and view the sample report we deployed. It is also working as expected. It passes the second validation.

The third validation is for the report server subscription. Select the report subscription and run it manually.

It runs fine. You can verify the last run and the result. In case of any error, you get an error message in the result column.

For the final validation, instead of running the report subscription manually, schedule it to run after 2-3 minutes from the current time. It also runs successfully.

Now it is time to recall your earlier questions. We can see the report schedule SQL agent job on the new primary replica. We have not created it manually.

SSRS automatically creates the report subscription jobs on the new primary replica. Microsoft recommends restarting the SQL Server reporting services after the AG failover as well. You should do the following tasks in case any failover happens using a SQL agent job.

• Restart SQL Server reporting services after failover
• Remove the SSRS jobs from the old primary replica. It ensures that during failback, all SSRS jobs are created with the latest configurations

In this article, we configured the SQL Server Reporting Services in a high availability solution using SQL Server Always On Availability Configuration. It ensures your reports are always running if one of your database instances has some issues. You can go through these Microsoft documentations: High availability in SQL Server Reporting Services and Reporting Services with Always On Availability Groups (SQL Server) .

Table of contents

• Recent Posts

• How to install PostgreSQL on Ubuntu - July 13, 2023
• How to use the CROSSTAB function in PostgreSQL - February 17, 2023
• Learn the PostgreSQL COALESCE command - January 19, 2023

Related posts:

• Explore failover types in SQL Server Always On Availability Groups
• Monitor and failover a Distributed SQL Server Always On Availability Group
• Explore dynamic management views for monitoring SQL Server Always On Availability Groups
• Configure SQL Server replication for a database in SQL Server Always On Availability Groups
• Install SQL Server 2019 on Windows Server 2016 with SQL Server Always On Availability Groups

SSRS 2022 Install, Setup and Configuration

By: Joe Gavin   |   Updated: 2024-02-21   |   Comments (3)   |   Related: > Reporting Services Installation

SQL Server Reporting Services (SSRS) is still one of the major components of the Microsoft Business Intelligence (MSBI) stack used to generate interactive and automated reports. It's part of Microsoft SQL Server services, along with SQL Server Analysis Services (SSAS) and SQL Server Integration Services (SSIS).

SSRS 2022 includes enhanced Windows Narrator support, security enhancements, browser performance improvements, and accessibility bug fixes.

The SSRS installation process has changed slightly since it was first released in 2004 as an add-on to SQL Server 2000. We saw in a previous tip: SSRS Install, Setup and Configuration , where the SSRS installation used to be part of the SQL Server installer up through SQL Server 2016. As of SQL Server 2017, the installation now has its own downloadable installer. There are only some minor differences between installing SSRS 2019 and SSRS 2022, but knowing where to get the installer and what to expect before you go through the installation process is helpful. This tip will walk through the various screens and options for the installation process.

We'll look at the minimum hardware and software requirements, see where to get the installer, and walk through each step of installing and configuring a new installation of SSRS 2022. The installation documented here was done on a SQL Server 2022 running on Windows Server 2022.

SSRS 2022 Requirements

You'll likely have more resources than this, but the following are the minimum requirements for installing SSRS 2022:

• 6 GB of available hard drive space
• 512 MB (1 GB is recommended)
• 1 GB (4 GB recommended)
• X64: AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, Intel Pentium IV with EM64T support
• 1.4 GHz (2 GHz recommended)
• Windows Server 2016/2019/2022, Windows 10 / 11
• SQL Server Database Engine 2014 SP3 or later
• .NET Framework 4.8 or later

SSRS 2022 Installation

Download the installer.

Go to Microsoft SQL Server 2022 Reporting Services Installer Download

• Click Download .

Retrieve and Run Installer

• Run SQLServerReportingServices.exe from your Download folder.

Welcome Screen

The installer opens, and you're presented with one option on the Welcome screen.

• Install Reporting Services.

Choose Edition and Install

There are four edition options for SSRS. The first three do not require a Product Key:

• Evaluation Edition – Full edition that is good for 180 days.
• Developer Edition – Full edition that can be used for non-production, e.g., test, development, training, or demonstration purposes.
• Express Edition – Limited edition with fewer features than the full edition.
• Paid Edition - The fourth option requires a product key. Click Learn more , which will take you to Find the product key for SQL Server Reporting Services .

Install Paid Edition

• Click the Enter the product key radio button and enter the key in the box.
• Click Next .

Review Licensing Terms

• Read and accept license terms by checking the 'I accept the license terms' checkbox.

Install Database Engine

Specify Install Location

• Choose the drive and directory to install SSRS.
• Click Install .

The SSRS application has been installed, and now it's time to configure it.

Configure SSRS

• Click Configure report server , which runs RSConfigTool.exe to open the Report Server Configuration Manager.

Connect to the SSRS Server

• Click Connect .

Configure Service Account

If the SSRS server requires access to remote servers, a Windows domain account can be specified to run the SSRS service.

• Click on Service Account in the Report Server Configuration Manager.
• Select the 'Use another account' radio button. Enter the domain account information and password.
• Click Apply .

Configure SSRS Databases

Here, we configure the SSRS databases.

• Click Database in the Report Server Configuration Manager.
• Click Change Database .

Create New Database

The new SSRS installation can be pointed to an existing set of SSRS databases. However, since this is a new installation, the default 'Create a new report server database' is left selected, and the installer will create the new databases.

Test the Connection

We must test the connection to the SQL Server to ensure connectivity before attempting to create the SSRS databases.

• Click Test Connection .
• If the connection is successful, click OK .

If the connection is unsuccessful, verify that the SQL Server service is running and accepting connections, and then run the test again.

Name the Database

Unless a name other than the default database names must be used, leave the defaults as ReportServer and ReportServerTemp.

Specify Credentials

Review Summary

Review the install configuration on the Summary screen.

• Click Next if all information is correct.

Progress and Finish

Verify all tasks completed successfully.

• If all tasks are successful, click Finish .

At this point, we have the SSRS service installed and databases created.

It's time to create the Web Service and the Web Portal.

Create the SSRS Web Service

• Click on Web Service URL .

Validate Web Service

Open the Report Server Web Service URL to validate. There are no folders shown because no reports have been deployed yet.

Create Web Portal

• Click Web Portal URL .

Validate Web Portal

To validate, open the Report Server Web Portal URL. Again, the root folder is empty because no reports have been deployed yet.

We now have a fully functioning SSRS server that's ready for reports to be deployed to it.

Email Settings

If Subscriptions to email reports will be created, we'll need to configure the email settings.

• Click E-mail Settings .
• Enter a Sender Address in the form of [email protected] .
• Enter the SMTP Server name.
• Select the Authentication dropdown menu to enter the account and password information if your SMTP server requires authentication.

Email has been configured.

• Click Exit .

You may run into failures, sometimes sporadically, related to TLS 1.2, where subscriptions are not sending emails and generating errors like this:

ERROR: Error sending email. Exception: System.Net.Mail.SmtpException: Failure sending mail. ---> System.IO.IOException: Authentication failed because the remote party has closed the transport stream.

Adding the following registry keys will force TLS 1.2:

The following links have more information on SQL Server Reporting Services:

SSRS Install, Setup and Configuration

• SQL Server Reporting Services 2017 Installation and Configuration
• Install SSRS ReportServer Databases on Azure SQL Managed Instance

Installing SQL Server Reporting Services 2017

SQL Server Reporting Services Standalone Installation

• How to Add SSRS to Existing SQL Server Clustered Instance
• PowerShell Commands for SQL Server Reporting Services
• Visual Studio 2019 Install and Configure for the SQL Server DBA
• How to Install and Configure SSRS with Amazon RDS SQL Server
• Side by Side SSRS Install and Upgrade to Minimize Downtime

About the author

Comments For This Article

Related Content

How to add Reporting Services to an existing SQL Server Clustered Instance

Adding Reporting Services to an existing SQL Server 2005 installation

Install SQL Server Reporting Services 32 bit on a 64 bit Windows Server

Install and Configure SQL Server Reporting Services 2012 SP1 and 2014 in SharePoint Integrated Mode Part 1

Related Categories

SQL Reference Guide

Reporting Services Administration

Reporting Services Best Practices

Reporting Services Configuration

Reporting Services Installation

Reporting Services Migration

Reporting Services Monitoring

Reporting Services Network Load Balancing

Reporting Services Performance

Reporting Services Security

Development

Date Functions

System Functions

JOIN Tables

SQL Server Management Studio

Database Administration

Performance

Performance Tuning

Locking and Blocking

Data Analytics \ ETL

Microsoft Fabric

Azure Data Factory

Integration Services

Popular Articles

Date and Time Conversions Using SQL Server

Format SQL Server Dates with FORMAT Function

SQL Server CROSS APPLY and OUTER APPLY

SQL Server Cursor Example

SQL CASE Statement in Where Clause to Filter Based on a Condition or Expression

DROP TABLE IF EXISTS Examples for SQL Server

SQL Convert Date to YYYYMMDD

Rolling up multiple rows into a single row and column for SQL Server data

SQL NOT IN Operator

Resolving could not open a connection to SQL Server errors

Format numbers in SQL Server

SQL Server PIVOT and UNPIVOT Examples

Script to retrieve SQL Server database backup history and no backups

How to install SQL Server 2022 step by step

An Introduction to SQL Triggers

Using MERGE in SQL Server to insert, update and delete at the same time

How to monitor backup and restore progress in SQL Server

List SQL Server Login and User Permissions with fn_my_permissions

SQL Server Loop through Table Rows without Cursor

SQL Server Database Stuck in Restoring State

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Reporting Services developer documentation

• 9 contributors

SQL Server Reporting Services offers several programming interfaces that you can apply in your own applications. You can use the existing features and capabilities of Reporting Services to build custom reporting and management tools into Web sites and Windows applications. Or you can extend the Reporting Services platform.

Extending the Reporting Services platform includes creating new components and resources that can be used for data access, report delivery and more. You can market these components and resources to companies that are using Reporting Services in their organization.

Reporting Services include programming samples and tutorials to help you get started. For more information, see Reporting Services Samples and Developer's guide: Tutorials (Reporting Services) .

In this section

Integrate Reporting Services into applications Provides an overview of how to use Reporting Services to integrate reporting into custom applications. Describes when to use direct URL access and when to use the Web service to access the report server.

Report Server Web service for ASP.NET and traditional applications The Report Server Web service provides access to the full functionality of the report server. The Web service uses SOAP over HTTP and is designed to act as a communications interface between client programs and the report server. The Web service and its methods expose the functionality of the report server and allow you to create custom tools for any part of the report life cycle, from management to execution.

Develop with REST APIs for modern applications The Reporting Services REST APIs provide programmatic access to the objects in Reporting Services report server catalog. When using the REST APIs, you can navigate to a folder hierarchy, discover the contents of a folder, or download a report definition. You can also create, update, and delete objects.

URL access (SSRS) Reporting Services supports a complete set of URL-based requests that you can use as a quick and easy access point for report navigation and viewing. You can use this technology with the Report Server Web service to integrate a complete reporting solution into your custom business applications. URL access is useful when integrating reports as part of a Web portal or when viewing reports from a Web browser.

Reporting Services extensions The modular architecture of Reporting Services is designed for extensibility. A managed code API is available so that you can easily develop, install, and manage extensions consumed by many Reporting Services components. You can create assemblies using the Microsoft .NET Framework and add new Reporting Services rendering, security, delivery, and data processing functionality to meet your evolving business needs.

Custom report items Describes how to create Custom Report Items to add functionality to RDL or extend functionality of existing controls.

Use custom assemblies with reports Describes how to use custom assemblies with Reports by including code references within the report definition.

Access the Reporting Services WMI provider Describes how to use the Reporting Services WMI Provider to manage report server deployments.

Related content

• Reporting Services (SSRS)
• Report definition language (SSRS)
• Technical reference (SSRS)
• Secure development (Reporting Services)

Was this page helpful?

Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback .

Submit and view feedback for

Additional resources

Microsoft SQL Server 2022 Reporting Services

SQL Server Reporting Services is a server-based reporting platform that provides comprehensive reporting functionality.

Important! Selecting a language below will dynamically change the complete page content to that language.

16.0.1115.92

Date Published:

SQLServerReportingServices.exe

• Paginated reports optimized for document generation and printing.
• A modern web portal you can view in any modern web browser.

System Requirements

Supported Operating Systems

Windows Server 2019, Windows Server 2016, Windows 10, Windows 11, Windows Server 2022

• .NET Framework 4.8 or later
• SQL Server Database Engine (2014 SP3 or later), to store the report server database
• Download the Evaluation Edition of Microsoft SQL Server

Install Instructions

• Install SQL Server Reporting Services (SQLServerReportingServices.exe)
• Configure your report server using Report Server Configuration Manager