How to Migrate SQL Server to Cloud for BI Workloads you ask? The short answer to this is “It Depends“.
On a broader level, there are primarily three different ways solutions available today.
Lift & shift or “Move”
- Migrating application with no changes
- Reducing infrastructure cost (CAPEX), not willing to invest in application
- Moving packaged app not certified for Azure
- One or small number of applications
- IT controlled and managed
- Need to control OS and instance
Solution: SQL Server in a VM on Azure
Lift & port or “Remodelling”
- Modernizing application
- Leveraging scale, reducing infrastructure management
- One or many applications
- IT controlled and managed
Solution: SQL Server in a VM on Azure or Azure SQL Database using the SAAS Model
Build for cloud or “New construction”
- New modern applications
- Large scale
- Ability to start small and grow very large
- Building SaaS applications
- Moving to devOps model
- Unpredictable growth
- Large number of databases
SQL Server in a VM on Azure or Azure SQL Database using the SAAS Model
What Common Cloud Application Patterns for development today?
Currently have the ability to disrupt traditional apps and
business models in the market.
Enterprise apps serving customers
Either Create new or re-architect existing apps with the ability to meet customer needs to augment their products and services.
ISVs going SaaS
Re-architect for cloud to transition like Sage, Salesforce and others. Transitioning from selling software licenses to
subscription based SaaS provider model.
Enterprise apps serving employees
In most cases, they are on-premise apps. Create or re-architect apps
to cloud for internal use, moistly built by internal development staff.
Azure SQL Database Use cases:
- Scales on the fly
- Redefines multi-tenancy
- Learns & adapts based on Workload patters.
- Works in your Environment with most deployment models
- Secures and protects with Row Level Security, Encryption options, Secure Endpoints Protection and much more.
Business Intelligence on Azure
- If the data is born in the cloud. it can be captured and pre-aggregated in Azure, globally at scale. Extracting the data on-premises and creating Data Model is more expensive.
- Power BI consumes data from everywhere and hosts it on the Microsoft Cloud Infeastructure.
- PolyBase in APS and SQL Server 2016 can push / pull data between Azure Blobstorage after processing in HDInsight.
- Users can connect Excel to BI data sources running Azure workloads like Blobs, Hadoop, SSAS Cubes etc.
IaaS is a great solution for lift-shift migrations and in some cases, for new builds too.
Why Azure Cloud and IaaS is a great solution for BI deployments?
- You can shutdown the ETL server do after the database is loaded and you will not be charged it anymore.
- Your customer does not need the Data Warehouse once the cube is processed. Shutdown the VM once the Data is loaded on the Cube.
- Can you hub-spoke the Data Warehouse / Data Marts between different size VM’s for performance and scale. See Azure Virtual Machines.
Use Azure Resource Manager to enable rapid development and deployment.
I hope that gives you a good enough reason to move to the cloud!