Export (0) Print
Expand All

SQL Server Business Intelligence in Azure Virtual Machines

Updated: September 12, 2013

The Microsoft Azure Virtual Machine gallery includes images that contain SQL Server installations. The SQL Server editions supported in the gallery images are the same installation files you can install to on-premises computers and virtual machines. This topic summarizes the SQL Server Business Intelligence (BI) Features installed on the images and configuration steps required after a virtual machine is provisioned. This topic also describes supported deployment topologies for BI features and best practices.

In this topic:

License Considerations

There are two ways to license SQL Server in Microsoft Azure Virtual Machines:

  1. License mobility benefits that are part of Software Assurance. For more information, see License Mobility through Software Assurance on Azure (http://azure.microsoft.com/en-us/pricing/license-mobility/).

  2. Pay per hour rate of Azure Virtual Machine, that has SQL Server installed. See the “Virtual Machines” section in Pricing Details (http://azure.microsoft.com/en-us/pricing/details/virtual-machines/ ).

For more information on licensing and current rates, see the following:

Arrow icon used with Back to Top link Top

SQL Server Images Available in Azure Virtual Machine Gallery

The Microsoft Azure Virtual Machine gallery includes several images that contain Microsoft SQL Server. The software installed on the virtual machine images varies based on the version of the operating system and the version of SQL Server. The list of images available in the Azure virtual machine gallery change frequently. For the current list of images, see Getting Ready to Migrate to SQL Server in Azure Virtual Machines (http://go.microsoft.com/fwlink/?LinkId=294721).

SQL image in azure VM gallery

  

For more information on editions and features supported by SQL Server, see the following:

Arrow icon used with Back to Top link Top

BI Features Installed on the SQL Server Virtual Machine Gallery Images

The following table summarizes the Business Intelligence features installed on the Microsoft Azure Virtual Machine gallery images for SQL Server 2012.

 

SQL Server BI Feature Installed on the gallery image Notes

Reporting Services Native Mode

Yes

Installed but requires configuration.

Reporting Services SharePoint Mode

No

The Microsoft Azure Virtual Machine gallery image does not include SharePoint or SharePoint installation files.

Analysis Services Multidimensional and Data mining (OLAP)

Yes

Installed and configured as the default Analysis Services instance

Analysis Services Tabular

No

Supported in SQL Server 2012 and 2014 images but it is not installed by default. Install another instance of Analysis Services. See the section Install other SQL Server 2012 Services and features in this topic.

Analysis Services PowerPivot for SharePoint

No

The Microsoft Azure Virtual Machine gallery image does not include SharePoint or SharePoint installation files.

  

Arrow icon used with Back to Top link Top

General Recommendations and Best Practices

  • The minimum recommended size for a virtual machine is Large when using SQL Server Enterprise Edition. The Extra Large virtual machine size is recommended for SQL Server BI deployments of Analysis Services and Reporting Services.

    For information on the current VM sizes, see the following:

  • A best practice for disk management is to store data, log, and backup files on drives other than C: and D:. For example, create data disks E: and F:.

    • The default drive C: is small in size and it is the operating system drive. Also, the C: drive caching policy is not optimal for working with data.

    • The D: drive is a temporary drive that is used primarily for the page file. The D: drive is not persisted and is not saved in blob storage. Management tasks such as a change to the virtual machine size reset the D: drive. It is recommended to NOT use the D: drive for database files, including tempdb.

    For more information on creating and attaching disks, see How to Attach a Data Disk to a Virtual Machine.

  • Stop or uninstall services you do not plan to use. For example if the virtual machine is only used for Reporting Services, stop or uninstall Analysis Services and SQL Server Integration Services. The following image is an example of the services that are started by default.

      

    noteNote
    The SQL Server database engine is required in the supported BI scenarios. In a single server VM topology, the database engine is required to be running on the same VM.

      

    SQL Server services For more information, see the following:

  • Check Windows Update for new ‘Important updates’. The Microsoft Azure Virtual Machine images are frequently refreshed; however important updates could become available from Windows Update after the VM image was last refreshed.

Arrow icon used with Back to Top link Top

Example Deployment Topologies

The following are example deployments that use Microsoft Azure Virtual Machines. The topologies in these diagrams are only some of the possible topologies you can use with SQL Server BI features and Microsoft Azure Virtual Machines.

Arrow icon used with Back to Top link Top

Single Virtual Machine

Analysis Services, Reporting Services, SQL Server Database Engine, and data sources on a single virtual machine.

bi iass scenario with 1 virtual machine

Two Virtual Machines

  • Analysis Services, Reporting Services, and the SQL Server Database Engine on a single virtual machine. This deployment includes the report server databases.

  • Data sources on a second VM. The second VM includes SQL Server Database Engine as a data source.

bi  iaas scenario with 2 virtual machines

Mixed Azure – data on Azure SQL database

  • Analysis Services, Reporting Services, and the SQL Server Database Engine on a single virtual machine. This deployment includes the report server databases.

  • Data source is Azure SQL database.

bi iaas scenarios vm and AzureSQL as data source

Hybrid –data on-premises

  • In this example deployment Analysis Services, Reporting Services, and the SQL Server Database Engine run on a single virtual machine. The virtual machine hosts the report server databases. The virtual machine is joined to an on-premises Domain through Azure Virtual Networking, or some other VPN tunneling solution.

  • Data source is on-premises.

bi iaas scenarios vm and on premise data sources

Reporting Services Native Mode Configuration

The virtual machine gallery image for SQL Server includes Reporting Services Native mode installed, however the report server is not configured. The steps in this section configure the Reporting Services report server. For more detailed information on configuring Reporting Services Native mode, see Install Reporting Services Native Mode Report Server (SSRS) (http://msdn.microsoft.com/en-us/library/ms143711.aspx).

TipTip
For similar content that uses Windows PowerShell scripts to configure the report server, see Use PowerShell to Create an Azure VM With a Native Mode Report Server (http://msdn.microsoft.com/library/dn449661.aspx).

In this section:

Connect to the Virtual Machine and Start the Reporting Services Configuration Manager

There are two common workflows for connecting to an Azure Virtual Machine:

  • To connect in the , click the name of the virtual machine and then click Connect. A Remote desktop connection opens and the computer name is automatically populated.

    connect to azure virtual machine
  • Connect to the virtual machine with Windows Remote Desktop Connection. In the user interface of the remote desktop:

    1. Type the cloud service name as the computer name.

    2. Type colon (:) and the public port number that is configured for the TCP remote desktop endpoint.

      Myservice.cloudapp.net:63133

      For more information, see What is a cloud service? (http://www.windowsazure.com/en-us/manage/services/cloud-services/what-is-a-cloud-service/).

Start Reporting Services Configuration Manager.

  1. In Windows Server 2012:

  2. From the Start screen, type Reporting Services to see a list of Apps.

  3. Right-click Reporting Services Configuration Manager and click Run as Administrator.

  1. In Windows Server 2008 R2:

  2. Click Start, and then click ALL Programs.

  3. Click Microsoft SQL Server 2012.

  4. Click Configuration Tools.

  5. Right-click Reporting Services Configuration Manager and click Run as Administrator.

Or

  1. Click Start.

  2. In the Search programs and Files dialog type reporting services. If the VM is running Windows Server 2012, type reporting services on the Windows Server 2012 Start screen.

  3. Right-click Reporting Services Configuration Manager and click Run as Administrator.

    search for ssrs configuration manager

Arrow icon used with Back to Top link Reporting Services Native Mode Configuration

Arrow icon used with Back to Top link Top

Configure Reporting Services

Service account and web service URL:

  1. Verify the Server Name is the local server name and click Connect.

  2. Note the blank Report Server Database Name. The database is created when the configuration completes.

  3. Verify the Report Server Status is Started. If you want to verify the service in Windows Server Manager, the service is the SQL Server Reporting Services Windows Service.

  4. Click Service Account and change the account as needed. If the virtual machine is used in a non-domain joined environment, the built-in ReportServer account is sufficient. For more information on the service account, see Service Account (http://msdn.microsoft.com/en-us/library/ms189964.aspx).

  5. Click Web Service URL in the Left pane.

  6. Click Apply to configure the default values.

  7. Note the Report Server Web Service URLs. Note, the default TCP port is 80 and is part of the URL. In a later step, you create a Microsoft Azure Virtual Machine Endpoint for the port.

  8. In the Results pane, verify the actions completed successfully.

Database:

  1. Click Database in the left pane.

  2. Click Change Database.

  3. Verify Create a new report server database is selected and then click Next.

  4. Verify Server Name and click Test Connection.

  5. If the result is Test connection succeeded, click OK and then click Next.

  6. Note the database name is ReportServer and the Report Server mode is Native then click Next.

  7. Click Next on the Credentials page.

  8. Click Next on the Summary page.

  9. Click Next on the Progress and Finish page.

Report Manager URL:

  1. Click Report Manager URL in the left pane.

  2. Click Apply.

  3. In the Results pane, verify the actions completed successfully.

Click Exit.

For information on report server permissions, see Granting Permissions on a Native Mode Report Server (http://msdn.microsoft.com/en-us/library/ms156014(v=sql.110).aspx).

Arrow icon used with Back to Top link Reporting Services Native Mode Configuration

Arrow icon used with Back to Top link Top

Browse to the local Report Manager

To verify the configuration, browse to report manager on the VM.

  1. On the VM, start Internet Explorer with administrator privileges.

  2. Browse to http://localhost/reports on the VM.

Arrow icon used with Back to Top link Reporting Services Native Mode Configuration

Arrow icon used with Back to Top link Top

To Connect to Remote Report Manager

If you want to connect to Report Manager on the virtual machine from a remote computer, create a new virtual machine TCP Endpoint. By default, the report server listens for HTTP requests on port 80. If you configure the report server URLs to use a different port, you must specify that port number in the following instructions.

  1. Create an Endpoint for the Virtual Machine of TCP Port 80. For more information see, the Virtual Machine Endpoints and Firewall Ports section in this document.

  2. Open port 80 in the virtual machine’s firewall.

  3. Browse to report manager using Azure Virtual Machine DNS Name as the server name in the URL. For example:

    Report manager:http://uebi.cloudapp.net/reportserver

    Report server:http://uebi.cloudapp.net/reports

    Configure a Firewall for Report Server Access (http://technet.microsoft.com/en-us/library/bb934283.aspx).

Arrow icon used with Back to Top link Reporting Services Native Mode Configuration

Arrow icon used with Back to Top link Top

To Create and Publish Reports to the Azure Virtual Machine

The following table summarizes some of the options available to publish existing reports from an on-premises computer to the report server hosted on the Microsoft Azure Virtual Machine:

  • Report Builder: The virtual machine includes the click-once version of Microsoft SQL Server Report Builder. To start Report builder the first time on the virtual machine:

    1. Start your browser with administrative privileges.

    2. Browse to report manager on the virtual machine and click Report Builder in the ribbon.

    For more information, see Installing, Uninstalling, and Supporting Report Builder (http://technet.microsoft.com/en-us/library/dd207038.aspx).

  • SQL Server Data Tools: VM: SQL Server Data Tools is installed on the virtual machine and can be used to create Report Server Projects and reports on the virtual machine. SQL Server Data Tools can publish the reports to the report server on the virtual machine.

  • SQL Server Data Tools: Remote: On your local computer, create a Reporting Services project in SQL Server Data Tools that contains Reporting Services reports. Configure the project to connect to the web service URL.

    ssdt project properties for SSRS project
  • Create a .VHD hard drive that contains reports and then upload and attach the drive.

    1. Create a .VHD hard drive on your local computer that contains your reports.

    2. Create and install a management certificate.

    3. Upload the VHD file to Azure using CSUpload tool. For more information, see CSUpload Command-Line Tool (http://msdn.microsoft.com/en-us/library/windowsazure/gg466228.aspx).

    4. Attach the disk to the virtual machine.

Arrow icon used with Back to Top link Reporting Services Native Mode Configuration

Arrow icon used with Back to Top link Top

Install other SQL Server 2012 Services and features

To install additional SQL Server services, such as Analysis Services in tabular mode, run the SQL server setup wizard. The setup files are on the virtual machine’s local disk.

  1. Click Start and then click All Programs.

  2. Click Microsoft SQL Server 2012 and then click Configuration Tools.

  3. Click SQL Server Installation Center.

Or run C:\SQLServer_11.0_full\setup.exe

noteNote
The first time you run SQL Server setup, more setup files may be downloaded and require a reboot of the virtual machine and a restart of SQL Server setup.

  

TipTip
If you need to repeatedly customize the image selected from the Microsoft Azure Virtual Machine, consider creating your own SQL Server 2012 image. Analysis Services SysPrep functionality was enabled with SQL Server 2012 SP1 CU2. For more information, see Cumulative update package 2 for SQL Server 2012 Service (http://support.microsoft.com/kb/2790947).

Arrow icon used with Back to Top link Top

To Install Analysis Services Tabular Mode

The steps in this section summarize the installation of Analysis Services tabular mode. For more information, see the following:

To Install Analysis Services Tabular Mode:

  1. In the SQL Server installation wizard, click Installation in the left pane and then click New SQL server stand-alone installation or add features to an existing installation.

    • If you see the Browse For Folder, browse to c:\SQLServer_11.0_full and then click Ok.

  2. Click Next on the product updates page.

  3. On the Installation Type page, select Perform a new installation of SQL Server 2012 and click Next.

  4. On the Setup Role page, click SQL Server Features Installation.

  5. On the Feature Selection page, click Analysis Services.

  6. On the Instance Configuration page, type a descriptive name, such as Tabular into Named Instance and Instance Id text boxes.

  7. On the Analysis Services Configuration page, select Tabular Mode. Add the current user to the administrative permissions list.

  8. Complete and close the SQL Server installation wizard.

 

Arrow icon used with Back to Top link Top

Analysis Services Configuration

Remote Access to Analysis Services Server

Analysis Services server only supports windows authentication. To access Analysis Services remotely from client applications such as SQL Server Management Studio or SQL Server Data Tools, the virtual machine needs to be joined to your local domain, using Azure Virtual Networking. For more information see, Azure Virtual Network (http://msdn.microsoft.com/en-us/library/windowsazure/jj156007.aspx).

Default Instance

A default instance of Analysis Services listens on TCP port 2383. Open the port in the virtual machines firewall. A clustered named instance of Analysis Services also listens on port 2383.

Named Instance

For a named instance of Analysis Services, the SQL Server Browser service is required to manage port access. The SQL Server Browser default configuration is port 2382.

In the virtual machines firewall, open port 2382 and create a static Analysis Services named instance port.

  1. To verify ports that are already in use on the VM and what process is using the ports, run the following command with administrative privileges:

    netstat /ao

  2. Use SQL Server Management Studio to create a static Analysis Services named instance port by updating 'Port' value in tabular AS instance general properties. For more information, see the “Use a fixed port for a default or named instance” in Configure the Windows Firewall to Allow Analysis Services Access (http://msdn.microsoft.com/en-gb/library/ms174937.aspx#bkmk_fixed).

  3. Restart the tabular instance of the Analysis Services service.

For more information see, the Virtual Machine Endpoints and Firewall Ports section in this document.

Arrow icon used with Back to Top link Top

Virtual Machine Endpoints and Firewall Ports

This section summarizes Microsoft Azure Virtual Machine Endpoints to create and ports to open in the virtual machine firewalls. The following table summarizes the TCP ports to create endpoints for and the ports to open in the virtual machines firewall.

  • If you are using a single VM and the following two items are true, you do not need to create VM endpoints and you do not need to open the ports in the firewall on the VM.

    • You do not remotely connect to the SQL Server features on the VM. Establishing a remote desktop connection to the VM and accessing the SQL Server features locally on the VM is not considered a remote connection to the SQL Server features.

    • You do not join the VM to an on-premises domain through Azure Virtual Networking or another VPN tunneling solution.

  • If the virtual machine is not joined to a domain but you want to remotely connect to the SQL Server features on VM:

    • Open the ports in the firewall on the VM.

    • Create virtual machine Endpoints for the noted ports (*).

  • If the virtual machine is joined to a domain using a VPN tunnel such as Azure Virtual Networking, then the endpoints are not required. However open the ports in the firewall on the VM.

 

Port Type Description

80

TCP

Report server Remote access (*).

1433

TCP

SQL Server Management Studio (*).

1434

UDP

SQL Server Browser. This is needed when the VM in joined to a domain.

2382

TCP

SQL Server Browser.

2383

TCP

SQL Server Analysis Services default instance and clustered named instances.

User defined

TCP

Create a static Analysis Services named instance port for a port number you choose, and then unblock the port number in the firewall.

For more information on creating endpoints, see the following:

The following diagram illustrates the ports to open in the VM firewall to allow remote access to features and components on the VM.

ports to open for bi applications in Azure VMs

Arrow icon used with Back to Top link Top

More Information and Resources

Arrow icon used with Back to Top link Top

Submit feedback and contact information through Microsoft SQL Server Connect (https://connect.microsoft.com/SQLServer/Feedback).

Community Content

Arrow icon used with Back to Top link Top

Community Additions

ADD
Show:
© 2014 Microsoft