This is a part of a Series “Stairway to being an Azure SQL DBA“, in which I am planning to cover everything related to working as an Azure SQL DBA.
We saw in the previous blog there are multiple options of having a SQL Server in cloud. And also touched if any of the Azure SQL option effect the DBA activities or not.
The difference between Azure SQL Database, Managed Instance and IaaS environment is as below:
| Azure SQL Database | Managed Instance | IaaS | |
| Compatibility with On-Prem databases | Supports most on-prem database level capabilities | Supports most of the on-prem Instance-level and database-level capabilitties | Supports everything that is there on-prem |
| Backups | Azure Managed | Azure Managed | You need to manage |
| Patching SQL Server | Azure Managed | Azure Managed | You need to manage |
| SQL Server Version | Latest stable Database Engine | Latest stable Database Engine | The one you install |
| High Availability | Can be done from the Portal, fixed option available | Can be done from the Portal, fixed option available | Any HA can be configured, but has to be done manually |
| Migration | This can be difficult as it has database and Instance level limitations, and lacks some features of a full-fledged SQL server | Migration in terms of compatibility is easy as many features available in On-Prem SQL Serve rare available here too | Migration is easy, as easy as taking backup and restoring it here. |
| CPU/Memory | Is configured separately for each and every database | Is configured per instance level | SQL Server can use all the resources allocated to the VM |
| Connection for On-Prem | On-Prem application can access data by whitelisting the app server IP in Azure SQL Database | Need to configure VPN or Express Route | Need to configure VPN or Express Route, can also be done by allocating a public IP to the VM and then whitelisting the On-prem server but the traffic is send over internet and so not secure enough. |
| Cost | Ranges from $5 per month to $19k per month per database | Ranges from $800 to $43k per month per instance | Depends upon the VM size and SQL Server License |
| Administration | DBA role is minimum | DBA is required for managing the SQL Server | DBA works the same way as in on-prem environment |
| SLA | 99.99% SLA provided by MS | 99.99% SLA prvided by MS | 99.95% SLA provided but only for VM infrastructure, you are responsible for SQL Server uptime. |
For the complete difference between Azure SQL Database and Managed Instance check the Power BI Report I have created Link
being a dba 