SQL Reporting Services in the Cloud. Part 2: Setup

The last time we compared prices using reporting services, which are available as a service in Windows Azure (SQL Reporting), an embodiment of a conventional virtual machine deployment with SQL Server (SSRS).
Again, I do not presume to say that one service is better or worse. In most cases, the decision about which of the services to use in the application must be made according to the tasks facing the application and the financial requirements of the customer. I just want to show that there are two ways to build a solution using report services.

Use cases

Suppose our application runs on Windows Azure and is implemented as a Cloud Service (PaaS). It uses an Azure SQL database as a data source. You must configure reporting services for use in the application. As previously discussed, reporting services for a Windows Azure application can be built in two ways:

1. PaaS: SQL Azure + SQL Reporting;
   SQL Azure will be used as a service;
   SQL Reporting will be used as a service.
   
2. Hybrid Solution: SQL Azure + SQL Server Reporting Services;
   SQL Azure will be used as a service;
   SQL Reporting Services must be configured on a separate SQL Server Virtual Machine (IaaS).
   

Let us now look at the detailed process of setting up both services. However, before we begin, I assume that the Azure SQL Database is already configured and hosted on Windows Azure.

PaaS Option: SQL Azure + SQL Reporting

Configure SQL Reporting Service

1. Go to the Windows Azure Management Portal;
2. Go to the “Reporting” section and click “Create a reporting service”;
3. Select the subscription and region that will be used by SQL Reporting service. After that, enter the user name with full access rights to the services and password.
   
   
4. After that, select “Create SQL reporting service”.

Report Project Settings

1. Open your reporting project in SQL Server Business Intelligent Development Studio;
   You can use Visual Studio 2012 to work with report projects (.rptproj). To do this, you must install Microsoft SQL Server Data Tools .
   
2. Right-click on “Shared Data Sources” in the “Solution Explorer” window and select “Add New Data Source”;
3. Enter the name of the new data source and set its type to “Type” in “Microsoft SQL Azure”. Then click “Edit”;
   
   
4. Enter the URL to access the Azure SQL Database;
5. Select “Use SQL Server Authentication” and enter the data to access the Azure SQL Database ;
6. Enter the name of the Azure SQL database in the “Select or enter database name” field;
   
   
7. Click “Test Connection”. OK
   
   
8. Next, go to the “Credentials” tab and select “Use this user name and password”;
9. Enter your credentials to access the Azure SQL Database . OK
   
   
10. In the context menu of the project, select “Properties”;
    
    
11. In the Windows Azure Management Portal, go to the SQL Reporting section. Select your reporting service and go to the “Dashboard” tab;
12. Copy the value of the “Web Service URL” field;
    
    
13. Paste the copied value into the “TargetServerURL” field in the settings of the SQL Server Business Intelligent Development Studio report project.

Hybrid Solution: SQL Azure + SQL Server Reporting Services

Creating a virtual machine

1. Go to the Windows Azure Management Portal;
2. Click the “New” button. Select “Compute”, then “Virtual Machine”, then “From Gallery”;
   
   
3. Select the image “SQL Server 2012 SP1 Standard on Windows Server 2008 R2 SP1”;
   
   
4. Enter the name of the new virtual machine “Virtual Machine Name”, the size “Size” and the data to access it: “New User Name” and “New Password”;
   
   
5. Enter the DNS name for the new virtual machine, then specify the storage account and the region that will be used for this virtual machine;
   
   
6. In the next step, leave all the defaults;
7. Complete the creation of the virtual machine.

Configure SQL Server

1. Connect to the created virtual machine via RDP;
2. Launch “SQL Server Management Studio”;
3. In the “Object explorer” window, right-click on the server name and select “Properies”;
4. Go to the “Security” tab and check “Server authentication” to “SQL Server and Windows Authentication mode”;
   
   
5. Return to the “Object Explorer” window and select the folder “Security”, “Logins”;
6. Right-click on the login “sa” and select “Properties”;
7. Enter the password for the user “sa”;
   
   
8. On the “Status” tab, set the “Login” property to “Enabled”;
   
   
9. Now restart SQL Server using “SQL Server Configuration Manager”.
   
   
10. The last thing to do is open the ports in Windows Firewall for the virtual machine. As an administrator, run the following two commands:
    

    netsh advfirewall firewall add rule name="SQL Server 1433" dir=in action=allow protocol=TCP localport=1433
    netsh advfirewall firewall add rule name="HTTP 80" dir=in action=allow protocol=TCP localport=80
    

    
    

Configure Reporting Services

1. Launch “Reporting Services Configuration Manager” from the Start menu;
2. When the initial screen appears, click the “Connect” button;
   
   
3. Go to the “Web Service URL” section and click the “Apply” button;
   
   
4. Go to the “Database” section and click the “Change database” button. The following window will appear. Click “Next."
   
   
5. Complete the server configuration, leaving all the default settings;
   
   
   
6. Go to the “Report Manager URL” section and click the “Apply” button.

Configure Windows Azure Firewall

1. Go to the Windows Azure Management Portal;
2. Select a SQL Server virtual machine;
3. Go to the “Endpoints” tab;
   
   
4. Click the “Add Endpoint” button and enter the following settings:
5. Repeat step 4 for the following settings:

Conclusion

After completing all the steps, SQL Server Reporting Services will be available at the URL specified when creating the virtual machine:
http: //.cloudapp.net / ReportServer

Use this URL as the value of the “TargetServerURL” property when publishing a report project through SQL Server Business Intelligent Development Studio.