Help Center> Relational Database Service> Best Practices>
View PDF
How Can I Deploy the Offline SSRS Report Service on RDS for SQL Server?

How Can I Deploy the Offline SSRS Report Service on RDS for SQL Server?

You can use the report service to make various simple or complex reports. In addition, the system provides the subscription function for users to subscribe to reports. This document describes how RDS uses the Reporting Services (SSRS) report service.

Background

Microsoft SQL Server contains server components such as the SQL Server database engine, Reporting Services (SSRS), and Analysis Services (SSAS). As a standard relational database component, the SQL Server database engine provides standard PaaS (Platform-as-a-Service) services on HUAWEI CLOUD in the form of RDS for SQL Server database products. However, other components, such as SSRS, are not provided as PaaS services on HUAWEI CLOUD. To use the SSRS service on HUAWEI CLOUD, you need to create a Windows ECS instance and install and configure the SSRS service component.

SQL Server Reporting Services in the Microsoft SQL Server component package has been separated from SQL Server 2017 and becomes an independent component service. You can download it from the Microsoft official website and install it on the ECS Windows system on HUAWEI CLOUD, in addition, RDS for SQL Server is used as the backend database of the SSRS report service, perfectly migrating the SSRS report service to the cloud.

Prerequisites

  • An RDS for SQL Server DB instance has been created. An RDS for SQL Server DB instance has been created.
  • A Windows ECS has been created. (The ECS and RDS DB instance must be in the same VPC, security group, and subnet.)

Procedure

  1. Download Reporting Services on the ECS instance and follow the wizard to complete the installation.
  2. After the installation is complete, click Configure Report Server.

    Figure 1 Configuring the Report Server

  3. In the Report Server Configuration Manager software, confirm the name of the report server and click Connect.

    Figure 2 Configuring the connection to the report server

  4. In the navigation pane on the left, set Service Account and Web Service URL based on your service requirements.

    For details, see the official document.

  5. Configure the report server.

    1. In the navigation pane on the left, choose Database and click Change Database to create a report server database on the ECS instance.

    2. In the Change Database dialog box, select Create a new report server database and click Next.

      If a local report database is available, you can use DRS Backup Migration ServiceDRS Backup Migration Service to migrate the full backup files of the local report database to the destination RDS SQL Server instance.

    3. Complete the connection information of the remote RDS Microsoft SQL Server DB instance. 服务器名称请填写RDS SQL Server的地址，格式为ip,port，ip和port之间用逗号分隔，账号请填写“rdsuser"。Click Test Connection. After the connection test is successful, click Next.

    4. Enter the name of the report server database, select the language used by the script, and then click Next.

    5. 设置账户连接报表服务器rdsuser用户的凭据，单击“下一步”。

    6. Confirm the report server information and click Next.
    7. After the report server database is configured, click Finish.

    For details, see the official document.

  6. In the navigation pane on the left, choose WEB Portal URL and click Apply. After the application is complete, click URL to access the web management page of the Report server.

  7. In the upper right corner, choose New > Data Source.

  8. Set the parameters of the new data source, as shown in the following table.

    Table 1 Parameters for creating a data source

    Level

    Parameter

    Description

    Parameters

    Name

    Name of the data source to be created. The value cannot contain the following characters: / @ $& * + = < >: ',? | \

    Description

    Description of a data source, which is used to differentiate services.

    Hide this item.

    If this parameter is selected, the data source is hidden.

    Enable the data source.

    After this parameter is selected, the data source is enabled.

    Connection

    NE type

    Indicates the data source type. Select Microsoft SQL Server.

    Connection String

    Domain name and database name of the RDS Microsoft SQL Server DB instance.

    格式：Data Source=<RDSSQLServer实例内网IP地址，RDSSQLServer实例端口>;

    Initial Catalog=< Database name >

    Example:

    Logging in

    Log in to the data source.

    Select Use the following credentials.

    Credential Type

    Select Database user name and password.

    Account Name

    Specifies the database account of the RDS for SQL Server instance.

    Password

    Specifies the password of the RDS Microsoft SQL Server database account.

  9. Click Test Connection. After the connection test is successful, click Create.

  10. After the data source is created, you can use the Report Builder and Visual Studio to design reports.

    For details, see Report Builder in SQL Server.