Data Ingestion Made Easy: Moving On-premises SQL Data to Azure Storage

On-premise SQL data to Azure (1)

Data ingestion from different on-premises SQL systems to Azure storage involves securely transferring and storing data from various on-premises SQL databases into Azure data storage solutions like Azure Data Lake Storage, Azure Blob Storage, or Azure SQL Data Warehouse. This data movement is essential for organizations looking to centralize, analyze, and leverage their data within the Azure cloud environment.

Business Scenario

The demand for swift, informed decision-making is paramount in the contemporary business landscape. Organizations seek tools capable of swiftly generating insightful reports and dashboards by consolidating data from diverse, critical aspects of their operations.

Envision a scenario where data from multiple pivotal systems seamlessly converges into a readily accessible hub. Enter Azure’s robust Data Integration service—Azure Data Factory. This service excels at aggregating data from disparate systems, enabling the creation of a comprehensive data and analytics platform for businesses. Frequently, we deploy this solution to fulfill our customers’ data and analytics requirements, providing them with a powerful toolset for informed decision-making.

Business Challenges

Below are some challenges that may be faced during the data ingestion process to Azure.

  • If SQL servers are outdated and change, the data capture mechanism doesn’t support incremental loads. Additional efforts are needed to implement gradual data change functionality, like creating control tables.
  • The data format will have some challenges if data is stored in storage accounts instead of databases on Azure. The parquet format helps fix this problem.

Solution Strategy

  • Identify the source entities\views\tables from the database system. Also, identify the column that needs to be used for incremental changes (mostly date column preferred in table\view).
  • Install and configure the self-hosted integration run time on an on-premises server with access to SQL servers.
  • Create a Key Vault to store credentials. These credentials are useful during link services creation in Azure Data Factory.
  • Create a source file and add all the source system tables into the tab for each source. Future table additions\deletions\updates will happen through this file only.
  • Create a similar type of file for incremental loads. This file will include a column name that refers to incremental changes.
  • Create source and destination link services.
  • Create source and destination datasets for associated tables\views in the database.
  • Create a watermark table and store procedure in a Serverless Azure SQL table. It is required for incremental loads.
  • Create an entire load pipeline. The pipeline uses previously created source and destination link services and datasets. It also uses lookup and filter activity only to collect the data from mentioned tables in the source file.
  • Follow similar instructions for the incremental load pipeline with additional steps to get the data difference from the previous copy to the current one using watermark column values.
  • Schedule the pipelines and add a monitor to notify upon failures.
  • Validate data by counting rows and sample row data on both sides.
  • Validate watermark table updates upon incremental load pipeline execution.

Moving On-premises SQL Data to Azure Storage

Fig 1: Full Load Sample Pipeline Structure

On-prem sql to Azure

                    Fig 2: Incremental Loads Sample Pipeline Structure

SQL Server to Azure

         Fig 3: Look up


Outcome & Benefits

  • Design the entire solution with parameterization. It can be replicated in multiple projects to reduce repetitive efforts.
  • ADF supports automated and scheduled data ingestion.
  • A robust system for monitoring and logging errors, facilitating seamless troubleshooting.
  • ADF supports 100+ connectors as of today.


Are you ready to transform your data management and unlock valuable insights within your organization? Take the first step towards a more data-driven future by exploring our data ingestion solutions today. Contact our data and analytics experts to discuss your needs and embark on a journey towards enhanced data utilization, improved business intelligence, and better decision-making.

Elevating Dynamics 365 Finance and Operations with Generative AI and Copilot

Generative AI for Dynamics 365 Finance Operations

We are thrilled to introduce Cambay Solutions’ new Generative AI and Copilot practice, engineered to enhance Microsoft Finance & Operations (F&O) with cutting-edge technology. This follows on the heels of Microsoft’s recent announcement about the next generation of AI and Dynamics 365 Copilot capabilities for ERP systems.


In Microsoft’s announcement, a plethora of innovations designed to improve ERP systems were covered. However, one aspect that stands out, particularly relevant to Microsoft F&O, is the powerful blend of Machine Learning (ML) & natural language processing (NLP) capabilities to offer predictive analytics and actionable insights. Cambay Solutions has honed in on this area and developed solutions that bring exceptional advantages to your Microsoft F&O implementation.


Why This Matters to Microsoft Finance & Operations

Finance and Operations are often considered the backbone of a business. These functions require data accuracy, efficiency, and the ability to foresee challenges and opportunities, all areas where AI and advanced analytics can make a meaningful impact. Predictive analytics and actionable insights, driven by machine learning and NLP, can act as a “finance copilot,” assisting you in decision-making, spotting trends, and issuing alerts for anomalies.


For instance, ML algorithms can analyze historical purchase data, market trends, and other relevant metrics to predict future inventory needs, thereby saving costs and enhancing efficiency. The natural language capabilities allow for easy and intuitive interactions with the system, enabling professionals at all tech-skill levels to retrieve detailed financial reports, forecasts, and risk assessments through simple voice or text commands.


Cambay Solutions’ Generative AI and Copilot Practice: A Deep Dive

Here’s what our new Generative AI and Copilot practice brings to the table for Microsoft Finance & Operations:


Advanced Predictive Analytics

Our custom algorithms analyze historical and real-time data to forecast sales, expenditures, and potential fraud or compliance issues. These predictive models improve over time as they ingest more data, increasing their accuracy and reliability.


User-Friendly Interactions

With the incorporation of NLP, users can interact with Microsoft Finance & Operations using natural language queries like “Show me the profit and loss statement for Q2” or “Give me the expenditure forecast for the next quarter.”


Anomaly Detection and Alerts

In addition to providing routine insights, our Copilot feature is trained to identify anomalies in financial records or operations data. This ensures that unusual activities, such as sudden spikes in expenditure or irregularities in accounting, are promptly flagged for review.


Seamless Integration

We’ve designed our solutions for seamless integration with your current Microsoft Finance & Operations framework, ensuring minimal disruption while maximizing the value you get from your ERP system.


Adaptive Learning

The system can learn from user interactions, continually refining its responses and recommendations, thus becoming a more effective tool.


Transform Your Dynamics 365 Finance & Operations with Cambay Solutions

Cambay Solutions’ new Generative AI and Copilot practice offers an opportunity to elevate the capabilities of Microsoft Finance & Operations substantially. By implementing this technology, you’re not just following the industry trend but leading it, offering your organization a substantial competitive advantage in financial planning, operations, and decision-making.


We invite you to reach out to us to learn more about how we can transform your Finance and operations landscape with the power of generative AI and Copilot capabilities. Welcome to the future of ERP with Cambay Solutions.

Step-by-Step Guide: Building an Active-Passive DR Site on Azure for Hyper-V Infrastructure.

DR Site on Azure for Hyper-V

Ensuring uninterrupted business operations is paramount in today’s rapidly evolving digital landscape. Building upon this imperative, an active-passive Disaster Recovery (DR) site on Azure for Hyper-V based infrastructure emerges as a strategic safeguard. This article delves into setting up a robust DR architecture and unravelling the steps and insights required to establish a fail-safe solution that guarantees business continuity in the face of unexpected disruptions. Discover how to fortify your organization’s resilience with Azure’s capabilities and Hyper-V’s reliability in our comprehensive guide.


Business scenarios:

Below are some business scenarios where implementing Hyper-V based infrastructure disaster recovery to Azure can be beneficial:

  • Data Center Outage: On-premises data center experiences a hardware failure, power outage, or natural disaster that disrupts your IT operations.
  • Ransomware Attack: Organization falls victim to a ransomware attack that compromises your on-premises systems and data.
  • Application Failure: A critical application in your on-premises environment experiences a failure that impacts business processes.
  • Geographic Redundancy: The organization requires geographic redundancy for compliance or business continuity reasons.
  • Testing and Development Environments: Organizations must create isolated testing, development, or training environments without impacting production systems.
  • Compliance and Reporting: Compliance requirements mandate data retention and secure storage for a specified period.



Below are some of the key challenges to Setup DR on Azure:

  • Data Transfer and Initial Replication: Transferring large volumes of data from on-premises to Azure can be time-consuming and resource-intensive. The initial data replication to Azure can impact network bandwidth and take time to complete, affecting recovery time objectives (RTOs).
  • Network Bandwidth and Latency: Limited network bandwidth and high latency can affect the speed at which data is replicated between on-premises and Azure. This can impact data synchronization and recovery point objectives (RPOs).
  • Complexity of Configuration: Setting up a disaster recovery solution in Azure involves configuring networks, virtual machines, storage, and replication settings. This complexity can lead to configuration errors if not managed carefully.
  • Application Compatibility: Not all applications are easily portable to Azure. Legacy applications or those with specific hardware requirements might need adjustments or reconfiguration for seamless operation in the cloud.
  • Failover and Failback Complexity: Failing over to Azure and returning to the on-premises environment can be complicated, mainly if data changes occur during the failover period.
  • Application Dependencies: Applications often have complex interdependencies. Ensuring all dependent systems and services are correctly configured and replicated can be complex.
  • Skill and Knowledge Gap: Implementing and managing disaster recovery in Azure requires specialized skills and knowledge. Organizations might need to invest in training or seek external expertise.


Solution Strategy – DR Site on Azure for Hyper-V Infrastructure

To overcome the challenges mentioned above, organizations should thoroughly plan their disaster recovery strategy, test it regularly, and consider leveraging specialized tools and services provided by Azure to simplify the process. Collaborating with experienced professionals or consulting Azure experts can help you navigate those challenges effectively.


DR Site on Azure for Hyper-V


Understand the current digital state of your organization.

Understanding how your Hyper-V and other service infrastructures are configured is imperative. Below are standard services that need to be assessed before setting up Disaster recovery in Azure.

  1. Domain and authentication services.
  2. Infrastructure services.
  3. Data center architecture.
  4. Remote sites and how they relate to the Primary data center.
  5. Existing network configuration should be configured on Azure.
  6. Network address spaces will not conflict with Azure network address spaces.
  7. Network firewall components.
  8. Load balancers.
  9. Security and compliance.
  10. Monitoring and alerting.


Prerequisites to set up Disaster recovery in Azure.

Below are the requirements you should consider:

  1. Azure Site Recovery Service: Azure Site Recovery (ASR) is the service used to replicate and manage the failover of virtual machines from Hyper-V to Azure. Ensure that your Azure Site Recovery service is enabled in your Azure subscription.
  2. Hyper-V Environment: Ensure your Hyper-V hosts are correctly configured and running compatible versions of Hyper-V. Also, additional storage is available to store replication metadata and snapshots.
  3. Network Connectivity: Reliable network connectivity between your on-premises environment and Azure is crucial for replication and failover. Consider elements such as bandwidth, latency, and network security.
  4. Hyper-V Site-to-Site VPN or ExpressRoute: To establish secure communication between your on-premises Hyper-V environment and Azure, set up a site-to-site VPN or Azure ExpressRoute connection.
  5. Azure Virtual Network: Set up a virtual network in Azure to which your replicated virtual machines will connect. This network should be configured appropriately to match your on-premises network settings.
  6. IP Address Management: Plan for IP address management during failover. This includes ensuring that IP addresses used by your on-premises virtual machines can be assigned appropriately in Azure.
  7. Network Firewall: The firewall should be configured appropriately to match your on-premises firewall.
  8. Azure Storage Account: An Azure storage account is required to store replicated data. Configure a storage account in Azure to store the replicated virtual machine data.
  9. Azure Active Directory: Your Azure subscription should be associated with an Azure Active Directory tenant. This is necessary for user authentication and access control.


Procedure for setting up disaster recovery for Hyper-V virtual machines to Azure

  • Prepare Azure Environment:
    • Ensure your Azure subscription is active, and all required services are registered.
    • Set up an Azure Resource Group.
    • Create an Azure Virtual Network and configure necessary subnets.
    • Set up an Azure Storage Account for replicating VM data.
    • Set up a Recovery Services vault to manage disaster recovery.
  • Configure Hyper-V Environment:
    • Ensure Hyper-V hosts are running compatible versions and have enough space to store ASR replication and metadata.
    • Install and configure the Azure Site Recovery Provider on each Hyper-V host.
  • Install and Configure Azure Site Recovery Service:
    • In the Azure portal, proceed to the Recovery Services vault.
    • Set up Azure Site Recovery and specify the source Hyper-V environment.
  • Prepare Replication:
    • Define a replication policy that determines replication frequency and retention settings.
    • Select the VMs you want to protect and enable replication for them
  • Azure Site Recovery Provider:
    • Install and configure the Azure Site Recovery Provider on your Hyper-V hosts. This agent facilitates communication between your Hyper-V environment and the Azure Site Recovery service.
  • Create Replication Policy:
    • Define a replication policy that determines how often virtual machine data is replicated to Azure and the retention settings for recovery points.
  • Create Recovery Plan:
    • Build a recovery plan that details the steps to follow during failover and failback operations. This plan defines the order in which virtual machines are brought online in Azure.
  • Configure Network Mapping:
    • Map the networks from your on-premises environment to the Azure Virtual Network to ensure seamless communication post-failover.
  • Create a Recovery Plan:
    • Create a recovery plan that defines the order of VM failover and any required scripts or actions during failover.
    • Test the recovery plan to ensure it functions as expected.
  • Perform Test Failover:
    • Initiate a test failover to validate the recovery plan and ensure VMs can be setup in Azure without affecting production.
  • Perform Failover to Azure:
    • In the event of a disaster, initiate a planned failover or unplanned failover based on your recovery plan.
      Monitor the failover process and verify that VMs are running correctly in Azure.
  • Initiating Planned failover

Planned Failover

  • Perform Failback to On-prem:
    • Once the on-premises environment is stable, plan for failback to the original Hyper-V environment.
    • Initiate the failback process using the Azure Site Recovery portal.
    • Initiating data synchronization


  • Starting virtual machine failback

Planned Failover


Best practices to maintain Disaster recovery site.

Monitor and Manage Replicated VMs:

    • Regularly review the replication status and perform necessary maintenance.
    • Use the Azure portal to monitor the health and quality of replicated VMs.
    • Configured for Azure site recovery jobs alerts.

Ongoing Testing and Maintenance:

  • Regularly conduct failover testing to ensure the disaster recovery solution remains functional.
  • Review and update your recovery plan as your environment changes.

Documentation and Training:

  • Document the disaster recovery setup, procedures, and contact information.
  • Train relevant personnel to ensure they can execute the recovery plan effectively.



Implementing on-premises Hyper-V VMs disaster recovery to Microsoft Azure offers several benefits that can significantly enhance an organization’s business continuity and disaster recovery capabilities. Here are some key benefits:

  • Reduced Downtime and Improved Business Continuity: Azure provides a platform for quick failover and failback, minimizing downtime in a disaster.
  • Scalability and Flexibility: Azure’s cloud infrastructure allows organizations to scale resources up or down as needed. This flexibility ensures that resources are available to meet demand during peak times and can be adjusted to save costs during non-peak periods.
  • Cost Savings: Traditional on-premises disaster recovery solutions often require significant capital expenditure on hardware and infrastructure. Azure offers a pay-as-you-go model, allowing organizations to only pay for the resources they use during a disaster recovery.
  • Geographic Redundancy and Data Replication: Azure’s global presence enables organizations to replicate data and applications to multiple geographic regions.
  • Automated Failover and Recovery: Azure provides automated failover capabilities, reducing the need for manual intervention during disaster recovery events. This speeds up recovery processes and ensures consistency in failover operations.
  • Reduced Maintenance Overhead: Managing on-premises hardware and infrastructure requires ongoing maintenance and updates. By utilizing Azure, organizations can offload much of the maintenance burden to Microsoft, allowing IT teams to focus on strategic initiatives.
  • Enhanced Security and Compliance: Azure provides robust security features and compliance certifications, helping organizations meet industry standards and regulatory requirements. Data encryption, access controls, and threat detection create a secure environment.


Next Steps:

Ready to implement a rock-solid Disaster Recovery plan for your Hyper-V infrastructure on Azure? Our experts are here to guide you through the process and tailor a solution to your business needs. Don’t wait for disruptions to strike – take proactive steps towards business continuity.

Schedule a consultation today to ensure a resilient future for your organization.