SQL Reporting services in the clouds. Part 2: Setup

Last time, we compared prices using report services that are available as a service in Windows Azure (SQL Reporting) with the option of deploying a regular SQL Server virtual machine (SSRS).
Again, I do not presume to say that one service is better or worse. In most cases, the decision on which of the services to use in the application should be made according to the tasks that the application faces 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

Let's assume that our application works in Windows Azure and is implemented as a Cloud Service (PaaS). It uses the Azure SQL database as a data source. You need to configure report building services for use in the application. As already discussed earlier, 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's now look at the detailed process of setting up both services. However, before we begin, I assume that the SQL Azure database is already configured and hosted in Windows Azure.

PaaS Option: SQL Azure + SQL Reporting

Configure SQL Reporting Service

1. Go to Windows Azure Management Portal;
2. Go to the “Reporting” section and click “Create a reporting service”;
3. Select the subscription and region to be used by the SQL Reporting service. After that, enter the username with full service access rights 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 SQL Azure database;
5. Select “Use SQL Server Authentication” and enter the data to access the SQL Azure database ;
6. Enter the SQL Azure database name 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 the data to access the Azure SQL database . OK
   
   
10. In the context menu of the project, select “Properties”;
    
    
11. In 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 in the “TargetServerURL” field in the SQL Server Business Intelligent Development Studio report project settings.

Hybrid Solution: SQL Azure + SQL Server Reporting Services

Create 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 of “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.

SQL Server Setup

1. Connect to the created virtual machine via RDP;
2. Launch the “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” in the “SQL Server and Windows Authentication mode”;
   
   
5. Return to the “Object Explorer” window and select the “Security”, “Logins” folder;
6. Right click on the “sa” login and select “Properties”;
7. Enter the password of the user “sa”;
   
   
8. On the “Status” tab, set the value of the “Login” property to “Enabled”;
   
   
9. Now restart SQL Server using “SQL Server Configuration Manager”.
   
   
10. The last thing you need to do is open the ports in the Windows Firewall for the virtual machine. As administrator, execute 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 

    
    

')

Reporting Services Setup

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 setup, 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 the SQL Server virtual machine;
3. Click on the “Endpoints” tab;
   
   
4. Click the “Add Endpoint” button and enter the following settings:
5. Repeat step 4 for the following settings:

Conclusion

After all the actions have been completed, SQL Server Reporting Services will be available at the URL specified when creating the virtual machine:
http: // <vm_name> .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.