Forum Discussion
Advise on next steps
Hello,
I have an existing Report server+Oracle database which we use in running reports and everything is fine. However, We have another database that runs on windows environment, and we intend to create a Virtual machine from the windows DB from where we will pull data and run reports on the exisitng report server.
We use report builder for creating reports. I need some advise on how to proceed with integrating the Virtual machine that run on windows environment.
Hi M_nwadibia ,
Thank you for reaching out to the Microsoft Community Forum.
1. Clarify the Role of the New VM
From your description, the VM will host a Windows-based database that needs to be queried by the existing Report Server.
The key questions to clarify are:
Will the VM host a SQL Server instance or another type of database?
Will the VM be on-premises or hosted in the cloud (e.g., Azure)?
Will the data be pulled live or via scheduled ETL processes?Note: These answers will influence how you configure connectivity and security.
2. Connectivity Between Report Server and VM
To integrate the VM with your existing Report Server:
Ensure Network Accessibility: The Report Server must be able to reach the VM over the network. If the VM is in Azure or another cloud, configure VPN or ExpressRoute.
Install Necessary Drivers: If the VM hosts a SQL Server or another database, install the appropriate ODBC/OLEDB drivers on the Report Server.
Configure Data Source in Report Builder: Define a new data source in Report Builder that points to the database on the VM. Use Windows Authentication or SQL Authentication as appropriate.You can refer to https://learn.microsoft.com/en-us/sql/reporting-services/report-server/install-reporting-services?view=sql-server-ver15 for guidance on configuring remote data sources 1.
3. Security and Authentication
Firewall Rules: Open the necessary ports on the VM to allow inbound connections from the Report Server.
Service Accounts: Use a service account with minimal privileges to connect from the Report Server to the VM-hosted database.
Encryption: Use SSL/TLS for secure data transmission between the Report Server and the VM.4. Performance and Impact
Data Volume: If large volumes of data are expected, consider caching or using intermediate data marts.
Latency: Monitor query performance, especially if the VM is cloud-hosted.
Resource Allocation: Ensure the VM has sufficient CPU, memory, and disk I/O to handle reporting workloads.5. Deployment and Maintenance
Automation: Use PowerShell or Azure CLI to automate VM provisioning and configuration if you're using Azure.
Monitoring: Set up monitoring for both the VM and the Report Server to track performance and availability.
Backup and Recovery: Implement backup strategies for the VM-hosted database.Please refer Microsoft article.
If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.
Thank you
7 Replies
Hi M_nwadibia ,
Thank you for reaching out to the Microsoft Community Forum.
Are you trying to create Virtual machine in Azure environment? Please provide more information about your requirement.
Thank you
The key questions to clarify are:
Will the VM host a SQL Server instance or another type of database? Yes.
Will the VM be on-premises or hosted in the cloud (e.g., Azure)? Yes on-premises
Will the data be pulled live or via scheduled ETL processes? Data will be pulled live
Not sure how that will be done but my IT team is handling that. I am trying to understand with the VM how this will integrate with existing Report server and Oracle DB and the impact.
Hi M_nwadibia ,
Thank you for reaching out to the Microsoft Community Forum.
1. Clarify the Role of the New VM
From your description, the VM will host a Windows-based database that needs to be queried by the existing Report Server.
The key questions to clarify are:
Will the VM host a SQL Server instance or another type of database?
Will the VM be on-premises or hosted in the cloud (e.g., Azure)?
Will the data be pulled live or via scheduled ETL processes?Note: These answers will influence how you configure connectivity and security.
2. Connectivity Between Report Server and VM
To integrate the VM with your existing Report Server:
Ensure Network Accessibility: The Report Server must be able to reach the VM over the network. If the VM is in Azure or another cloud, configure VPN or ExpressRoute.
Install Necessary Drivers: If the VM hosts a SQL Server or another database, install the appropriate ODBC/OLEDB drivers on the Report Server.
Configure Data Source in Report Builder: Define a new data source in Report Builder that points to the database on the VM. Use Windows Authentication or SQL Authentication as appropriate.You can refer to https://learn.microsoft.com/en-us/sql/reporting-services/report-server/install-reporting-services?view=sql-server-ver15 for guidance on configuring remote data sources 1.
3. Security and Authentication
Firewall Rules: Open the necessary ports on the VM to allow inbound connections from the Report Server.
Service Accounts: Use a service account with minimal privileges to connect from the Report Server to the VM-hosted database.
Encryption: Use SSL/TLS for secure data transmission between the Report Server and the VM.4. Performance and Impact
Data Volume: If large volumes of data are expected, consider caching or using intermediate data marts.
Latency: Monitor query performance, especially if the VM is cloud-hosted.
Resource Allocation: Ensure the VM has sufficient CPU, memory, and disk I/O to handle reporting workloads.5. Deployment and Maintenance
Automation: Use PowerShell or Azure CLI to automate VM provisioning and configuration if you're using Azure.
Monitoring: Set up monitoring for both the VM and the Report Server to track performance and availability.
Backup and Recovery: Implement backup strategies for the VM-hosted database.Please refer Microsoft article.
If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.
Thank youHi M_nwadibia ,
Have you resolved the issue? If Yes, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.
Thank you
