You can install SQL Server Reporting Services (SSRS) on an ECS instance and create reports based on the data in an ApsaraDB RDS SQL Server instance. This topic describes how to use ApsaraDB RDS SQL Server instances as data sources to create reports.

Background information

Microsoft SQL Server contains server components such as SQL Server database engine, SSRS, and SQL Server Analysis Services (SSAS). The SQL Server database engine is a standard relational database component. ApsaraDB RDS SQL Server is a PaaS that provides this database engine. Components such as SSRS run as Windows services, and are not provided as PaaS services on Alibaba Cloud. If you need to use SSRS on Alibaba Cloud, you must create a Windows-based ECS instance before installing and configuring SSRS.

Note You cannot create the SSRS configuration database in an ApsaraDB RDS SQL Server instance.

Prerequisites

Note The version of SQL Server on the ECS instance can be different from the version of the ApsaraDB RDS SQL Server instance.

Procedure

  1. Download and install Reporting Services in the ECS instance.
  2. Start the Report Server Configuration Manager. Configure Server Name and Report Server Instance. Click Connect.
    Note Report Server Configuration Manager automatically displays all the Report Server instances that are in the ECS instance. Select an instance as needed.
  3. In the left-side navigation pane, click Service Account and Web Service URL and configure parameters based on your business needs.
  4. In the left-side navigation pane, click Database. On the right side of the page, click Change Database to create a new report server database in the ECS instance.
    1. Select Create a new report server database and click Next.
    2. Enter the server name and click Next.
    3. Enter the database name and select a language for the script. Click Next.
    4. Configure the credentials for the account to connect to the report server and click Next.
    5. Confirm the information on the Summary page and click Next. Wait for the database to be created.
    6. Click Finish.
  5. In the left-side navigation pane, click Web Portal URL and click Apply. After the application operation is finished, click the URL to go to the Web portal of the report server.
  6. In the upper-right corner of the page, choose New > Data Source.
  7. Configure the parameters as follows:
    Section Parameter Description
    Properties Name Enter the name of the data source. The name cannot contain special characters. Special characters include / @ $ & * + = < > : ' , ? | \
    Description Specify the description of the data source to identify different data sources.
    Hide Click to hide the data source.
    Enable Click to enable the data source.
    Connections Type Select a type of the data source. Select Microsoft SQL Server.
    Connection String Specify the endpoint and the database name of the ApsaraDB RDS SQL Server instance in the Data Source=<RDS SQL Server instance endpoint>; Initial Catalog=<database name> format.
    Note Make sure the IP address of the ECS instance is added to the IP whitelist of the RDS instance. For more information, see Configure an IP address whitelist for an ApsaraDB RDS for SQL Server instance.
    Credential Data Source Login Select Use the following credentials.
    Credential Type Select Database username and password.
    Username Enter the database account of the ApsaraDB RDS SQL Server instance.
    Password Enter the password of the database account.
  8. Click Create.

What to do next

After the data source is created, you can use software such as Report Builder and Visual Studio to design reports. For more information, see Report Builder in SQL Server.