SQL Reporting Services in clouds. Part 2: Setup

Last time, we compared the cost of using the reporting services that are available as a service in Windows Azure (SQL Reporting) with the option of virtual machine deployment with an SQL Server (SSRS).

I cannot claim that a particular service is good or bad. In most cases, the decision about the service to be used in the application must be made in accordance with the tasks the application is aimed to perform, as well as the customer’s financial requirements. In that regard, I would like to show the two ways to develop solutions using reporting services.

Use Cases

Let’s suppose that our application is running under Windows Azure and is implemented as a Cloud Service (PaaS). It uses the SQL Azure database as a data source. You must configure the report generating services used in the application. As discussed earlier, report generating 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 be used as a service.

       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 virtual machine with an SQL Server (IaaS).

Now let’s have a look at the detailed process of setting up both services. Before we begin, we will assume that the SQL Azure Database has already been configured and is available in Windows Azure.

Option PaaS: SQL Azure + SQL Reporting

Configuring the SQL Reporting Service

1. Go to the Windows Azure Management Portal.
2. Go to Reporting and click Create a reporting service.

3. Select the subscription and the region to be used by the SQL Reporting service. Then enter the credentials of a user with full access to the services.

4.    Select Create SQL reporting service.

To set up a Reporting Project:

1.    Open your reporting project in SQL Server Business Intelligent Development Studio. You can use Visual Studio 2012 to work with the reporting projects (.rptproj). To do this, you must install the Microsoft SQL Server Data Tools.

2.    Right-click Shared Data Sources in the Solution Explorer window and select Add New Data Source.

3.    Enter the new data source name and specify its 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 credentials to access the SQL Azure database.

6.    Enter the SQL Azure database name in Select or enter database name.

7.    Click Test Connection and then click OK.

8.    Go to the Credentials tab and select Use this user name and password.

9.    Enter the credentials to access the SQL Azure database and click OK.

10. Select Properties in the project context menu.

11. Go to SQL Reporting in the Windows Azure Management Portal. Select your reporting service and open the Dashboard tab.

12. Select and copy the Web Service URL field value.

13. Insert the copied Web Service URL value into the TargetServerURL field in the SQL Server Business Intelligent Development Studio reporting project settings.

Hybrid Solution: SQL Azure + SQL Server Reporting Services

Creating a virtual machine:

1.    Go to Windows Azure Management Portal.

2.    Go to New -> Compute -> Virtual Machine -> From Gallery.

3.    Select SQL Server 2012 SP1 Standard on Windows Server 2008 R2 SP1.

4.    Enter the new virtual machine’s name, size, and access credentials in the respective fields.

5.    Enter the DNS name for the new virtual machine and specify the storage account and region to be used for this virtual machine.

6.    In the next step, leave all the default values​​ as they are.

7.    Finish creating the virtual machine.

Configuring the SQL Server

1. Connect to the newly created virtual machine using Remote Desktop Connection.

2.    Start SQL Server Management Studio.

3.    In the Object explorer, right-click the server name and select Properties.

4.    Go to Security and select SQL Server and Windows Authentication mode for Server authentication.

5.    Return to the Object Explorer window and go to Security -> Logins.

6.    Right-click on the sa login name and select Properties.

7.    Specify the password for the sa login name.

8.    On the Status tab, select the Enabled radio button for Login.

9.    Restart the SQL Server using SQL Server Configuration Manager.

10. The last thing you need to do is open the ports in Windows Firewall for the virtual machine. Acting as the administrator, run the following 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

Setting up Reporting Services:

1.    From the Start menu, start Reporting Services Configuration Manager.

2.    On the starting screen, click Connect.

3.    Go to Web Service URL and click Apply.

4.    Go to Database and click Change database. In the window that appears, click Next.

5.    Leave the default values and finish the server setup.

6.    Go to Report Manager URL and click Apply.

Setting up the Windows Azure Firewall:

1.    Go to Windows Azure Management Portal.

2.    Select the SQL Server virtual machine and open the Endpoints tab.

3.    Click Add Endpoint and enter the following values:

·         Name: TDP;

·         Protocol: TCP;

·         Public port: 1433;

·         Private port: 1433.

4.    Repeat point 3 with the following settings:

·         Name: HTTP;

·         Protocol: TCP;

·         Public port: 80;

·         Private port: 80.

Conclusion

 When you’re done, the SQL Server Reporting Services will be available via the URL specified when you were creating the virtual machine:

http: // <vm_name> .cloudapp.net / ReportServer

Use this URL as the TargetServerURL value when publishing the reporting project using the SQL Server Business Intelligent Development Studio.