SSRS Report Server Configuration

SSRS Report Server Configuration on Azure Virtual Machines

Introduction

SQL Server Reporting Services (SSRS) provide a full range of ready-to-use tools and services to help users/organisations create, deploy, manage reports and deliver them to the right viewers in different ways i.e. viewing them in a web browser, on a mobile device or as an email. It also helps to program features that enable extendibility and customisation of reporting functionality.

Hosting SQL Servers on Azure Virtual Machines (Azure VM’s) enables organisations to meet their unique and diverse business needs through a combination of on-premises and cloud-hosted deployments, while using the same set of server products, development tools, and expertise across these environments.

Mitra Innovation likes to share knowledge and experience, and so we thought it would be helpful to discuss SQL Cloud hosting in Azure, Azure Virtual Machines and their benefits, as well as provide a step-by-step guide on how to create a virtual machine on Azure and configure the SQL server to be run within.

This tech how-to guide is intended to help developers and technical decision makers (architects, database administrators and IT managers) understand how to set up an environment for hosting SQL servers on Virtual Machines within the Azure cloud platform.

Azure Cloud SQL Server Hosting Options

Azure offers two options for hosting SQL Server workloads in Microsoft Azure:

The Azure SQL Database is one option that is native to the cloud. It is also known as a Platform as a Service (PaaS) database or a Database as a Service (DBaaS) that is optimised for Software-as-a-Service (SaaS) app development. It is compatible with most SQL Server features.

Below is the bird’s view of the entire system. The report server is responsible for providing reports to clients with the Azure PaaS database.

ssrs-img-1

The second option is the SQL Server on Azure Virtual Machines, in which the SQL Server is installed and hosted in the Cloud on Windows Server Virtual Machines (VMs) running on Azure, also known as an Infrastructure as a Service (IaaS).

Hosting SQL Servers on Azure Virtual Machines

When hosting an SQL Server on Azure VM’s (IaaS) the SQL Server runs inside a virtual machine in the Cloud. This VM is built on standardised hardware that is owned, hosted, and maintained by Microsoft.

Running an SQL server on Azure VM is best when there is a need to rapidly migrate existing applications to Azure or extend existing on-premises applications to the Cloud in hybrid deployments. In this case, the SQL Server hosted in the VM can be used to develop and test traditional SQL Server applications without the need to purchase on-premises non-production SQL Server hardware, thus eliminating hardware costs.

It is also ideal if a customised environment with full administrative rights is needed. With SQL Server on Azure VMs, users or organisations have full administrative rights over a dedicated SQL Server instance and a cloud-based VM.

Lastly, it is an appropriate choice when an organisation already has IT resources available for configuration and management of the VM’s. This enables organisations to build highly customised systems to address their application’s specific performance and availability requirements.

Azure Virtual Machines

Azure Virtual Machines (VM’s) are virtual machines hosted on the public Cloud. They have the latest ‘hot-fixes’ for SQL and Windows and can be created within 10 minutes. SQL Servers running on Azure Virtual Machines can be connected via RDP, Powershell, ADO.NET, OLEDB or any other driver that the user prefers. Azure VM’s are pay-per-use machines that can be paused and resumed as needed. The cost of the VM depends on its size and the version of the SQL Server that is being run. Azure VM’s are very elastic in nature, meaning that even if you start with a very small VM, for example 1 core/2 GB memory/1 TB, you can easily increase your VM to 32 cores/512 GB memory/64 TB.

Azure provides an extremely secure environment with several layers of security available. This includes physical security where all data centers are monitored continuously, and access is restricted to specific personnel. There is also infrastructure security for virtual networks and storage. Additionally, various certifications are available for security. Azure VM’s also provide easy and secure connectivity over public endpoints and over site-to-site tunnels. Furthermore, there is reliable storage with three synchronous local copies which ensure that no data is lost while also providing reliable automated verification and disk decommissioning. There are also three more asynchronous remote copies that guarantee recovery in case of any disasters. Storage is also very fast which is important for SQL Servers.

Azure offers a number of powerful VM’s in different sizes that can be selected according to needs. Azure VM’s also have high availability through Azure Service Level Agreement (SLA) which provides less than 22 minutes of downtime per month. This includes any downtime that Azure plans in order to host OS patching as well as any unplanned downtime due to physical failures. Azure provides very easy configuration in comparison to other Cloud providers by helping users configure their SQL servers with the Azure VM’s. Azure helps configure SQL Server Connectivity, SQL Server performance best practices, automated patching, automated backup, Database encryption, and SQL Server AlwaysOn. This allows users to focus more on their applications. Lastly, Azure provides easy monitoring by allowing users monitor all the SQL performance counters in the Azure portal. This can be done through the Operations Insights portal that helps to monitor best practices.

Azure Virtual Machine Configuration

This section covers all the steps that should be followed to help users get started with SQL Server on Azure VMs. In order to begin the process, three essential things must be readily available:
● Azure account – Everything is handled under this account
● Azure Pass DB – This database contains business data for reports
● Azure Virtual machine with SQL Server installation – This virtual machine contains

reports and SQL instance contains server configuration data.

First, the VM must be created on the Azure platform:

Step 1: Go to https://manage.windowsazure.com/ and login with your credentials to Azure portal.
Step 2: To create a virtual machine Go to New > Virtual Machine > From Gallery

ssrs-img-2

Step 3: Click on Microsoft SQL Server 2012 Evaluation Edition as the platform image in VM OS selection page. Then click on the right arrow to continue.

fig3

Figure 3. Virtual Machine Creation

Step 4: In the VM Configuration page, enter the Virtual Machine Name (i.e. “testssrsvm”) and the administrator user’s password. Select the Size as per the requirement and click the right arrow to continue.

Provide the virtual machine name as ‘testssrsvm’ and administrator password (you can provide any memorable name for virtual machine as your wish). You can select the size of virtual machine as your requirement. Then click on right arrow to proceed.

fig4

Figure 4. Virtual machine configuration

Step 5: In the VM Mode page, select Standalone Virtual Machine, enter the DNS Name (i.e. “testssrsvm”), leave the default value, use the Automatically Generated Storage Account. Select a Region (for e.g. “East US”). In case of multiple subscriptions select your subscription from the dropdown. Click the right arrow to continue.

Select Standalone Virtual Machine in VM Mode page and provide DNS Name as your wish (eg: ‘testssrsvm’). Select Automatically Generated Storage Account as Storage account and select your nearest location as the region and select the subscription as your wish.

fig5

Figure 5. Virtual machine mode selection

Step 6: Keep default values in VM Options page and click on tick button to create the virtual machine.

fig6

Figure 6. VM Options

Step 7: Then it will start creating virtual machine and it will take several minutes. Then you can see newly created virtual machine in Virtual machines section.

fig7

Figure 7. VM for SSRS Service

Step 8: Once VM is created successfully and in Running status, click on the VM Name (“testssrsvm”) to go to the Dashboard page and then click Endpoints. After Click Add Endpoint on the bottom pane. Make sure that Add Endpoint option is selected and then click the right arrow button to continue.

After creating the virtual machine it will be in running status. Then we have to create end points to the virtual machine. Go to dashboard page of the virtual machine and click on Endpoints. Make sure that Add Endpoint option is selected and then click on the right arrow button to proceed.

fig8

Figure 8. Adding a new End point

Step 9: Now you can see Specify endpoint details page. Give the following details on that page
Name: ssrsport (this can be given as your wish)

Protocol: TCP
Public Port: 80
Private Port: 80

 

fig9

Figure 9. Endpoint details

Note: Endpoint creation will take a few minutes.

Attaching Empty Disk Image

The following steps will explain how to create empty data disks and attach them to the virtual machine with Azure Management portal. They will contain SSQLdata as well as Log files.

Step 10: Select virtual machine (testssrsvm)
Step 11: Click on Attach at the bottom of the page and Attach empty disk from virtual machine dashboard
Step 12: Set size as 50 GB in attach empty disk page
Step 13: Wait until disk attachment is finished.
Step 14: You can see two disks for the virtual machine. They are for data and OS

Note: It will take a few minutes for data disks to show in the virtual machine dashboard in Azure portal.

Step 15: To access those disks, you need to format them. Connect with remote desktop connection and format the disk (Please follow the usual method to connect to the virtual machine i.e. via Remote desktop connection)
Step 16: After connecting via remote desktop connection follow Start > All programs > Administrative tools to open server manager
Step 17: Select disk management by expanding Storage section

fig10

Figure 10: Disk Management

Step 18: Follow below window to initialize the disk

fig11

Figure 11. Disk Initialisation

Step 19: Right-click the unallocated space and select New Simple Volume.

fig12

Figure 12. Disk Management

Step 20: Follow the New Simple Volume Wizard. When asked for the Volume Label use SQLData.
Step 21: It will take some time. Please wait until it is finished.
Step 22:SQLData disk will show under Disk management list as below snap shot

fig13

Figure 13. Disk Management

Step 23: Now time to create an inbound rule to get access. Follow Start > All Programs > Administrative tools.
Step 24: Right click on Inbound Rule and follow new Rule path to setup a new rule.

fig-14

Figure 14. Creating an Inbound Rule

Step 25: In the Rule Type page, select Port and click Next.
Step 26: In Protocols and Ports page, set TCP selected, select Specific local ports and set its value as 80. Proceed by clicking continue.
Step 27: Verify that you have set “Allow the connection” in the action page and click next.
Step 28: Keep default values in profile page and click next
Step 29: Name Inbound rule as ‘SSRSRule’ in name page and click on Finish
Step 30: Then Close Windows Firewall with Advanced Security window.

Note: Do not close the Remote Desktop Connection.

Step 23: Now time to create an inbound rule to get access. Follow Start > All Programs > Administrative tools.
Step 24: Right click on Inbound Rule and follow new Rule path to setup a new rule.

Configure Sql Server

Here the configuration steps for the SQL Server in the VM will be covered.

Step 31: Login to the Virtual Machine via Remote desktop.
Step 32: Follow Start > All Programs > Microsoft SQL Server 2012
Step 33: Connect to the SQL Server 2012 default instance with Windows Account.
Step 34: Right click and get Properties of SQL server instance.
Step 35: Select Security from the left side list and select SQL Server and Windows Authentication mode as Server authentication.
Step 36: Select Database Settings from the left side menu..
Step 37: Locate the Database default locations section and set the default values to point to the disks attached in the previous task and click OK.

fig-15

Figure 15. Setting Database Default Locations

Step 38: Now right click on SQL Server instance and select restart to restart the instance and confirm it.
Step 39: Create following folders in F drive >F:\Data, F:\Logs and F:\Backups
Step 40: Open Sql management studio and expand the server instance and right click on logins and select new login to create new login.
Step 41: Use login name as ssrssqluser and select SQL server authentication and set a password.
Step 42: Unselect Enforce password policy checkbox to prevent popping up to change the password the first time login.
Step 43: Select sysadmin role under server roles which is available in left menu and clock Ok to confirm.

Configure Sql Reporting Service

The following steps will guide the configuration of the SSRS service.

Step 44: Follow Start >All Programs > Microsoft SQL Server 2012 > Configuration Tools to open reporting services config manager
Step 45: In Configuration Manager Connection dialog box click on Connect in reporting services
Step 46: Then Click Web service Url from the left menu and Click Apply.

fig-16

Figure 16. Configure Web Service URL

Step 47: Click Database from the left menu and hit Apply.
Step 48: In the Change Database dialog, keep the default value as “Create a new report server database” and go to Next.
Step 49: Now select the Authentication Type as SQL Server Account and give SQL user credential (testssrsvm) in the Username and Password section.
Step 50: Click on Test Connection to verify the connection and after succeeding click next to proceed.
Step 51: Select Authentication Type and use SQL user credentials (testssrsvm) in credential page and click next
Step 52: Verify the given information in the summary section and click Next.
Step 53: After completing the configuration click on Finish
Step 54: Now click on the Report Manager Url from the left menu and Click on Apply.
Step 55: Click Exit to finish the SSRS configuration.
Step 56: Now exit from the Remote Desktop Connection.

Verification: Open any web browser from any client machine and go to the following URL and enter the administrator credential created during the creation of the VM creating.

http://testssrsvm.cloudapp.net/Reports/Pages/Folder.aspx

Now the server configuration has been completed successfully. The Azure report server can now be used like a usual SSRS report server.

Conclusion

Running SQL Servers on Azure Virtual Machines is an option that is very easy to install and administer, especially if one already has SQL Server experience. The VM Machine in Azure is very secure allowing users to configure and restrict the IPs that can access the VM’s and VM in Azure is easily accessible from any RDP (Remote Desktop Protocol) Client. We recommend running SQL Server in a Windows Azure VM because it is optimised for the best compatibility with existing applications and for hybrid applications. Additionally, it provides full SQL Server box product features and gives the administrator full control over a dedicated SQL Server instance and cloud-based VM.

Lahiru Gambheera

About the Author

Lahiru Gambheera is a Software Engineer at Mitra Innovation, a technology company that specialises in product, platform and SaaS incubation services, Cloud-to-Cloud systems integration, and digital innovation and transformation. Find out how Lahiru and the rest of the Mitra Innovation team can help you with your SSRS Report Server configuration on Azure Virtual Machines.