Azure cloud SQL database single database PaaS vs Elastic Pool EP vs Virtual machine IaaS
What is Cloud?
Cloud is simply using third party data center, servers and services. You do not have to manage most of the tasks to spend your money, resources to build your own data center and maintenance of those etc.
This post will give some overview and difference of single database PaaS vs Elastic Pool EP vs Managed Instance MI vs SQL on Virtual machine IaaS.
What is on premise – It’s your local data center / server, what you are currently doing before migrating to cloud.
Azure architect will take care of the Azure infrastructure. SQL DBA needs to own PaaS and any SQL running on VMs IaaS.
In Azure Microsoft provides different type of service to have a database.
Types: IaaS and PaaS
IaaS – It is infrastructure as service, which means we need to take care of the infrastructure services, meaning VM and SQL server, patching and backups etc.
Advantages: you have full control to the SQL database engine and choosing everything like version, features etc, easy for migration and you have a private IP etc
Disadvantages: You have to manage VM and SQL, you have to implement HA/DR and SQL backup etc.
PaaS – Platform as a service, which means Microsoft will take care of everything, VM, SQL patch, backup etc.
In PaaS, we have two options ‘Single database and Elastic Pool’ as service.
Advantages: 99.9 % up-time, built in backup and HA, Patch etc
Disadvantages: Migration from on premises to Azure will be difficult, since lot of features and query are not supported. No private IP, you have use firewall access.
Managed Instance MI – It is other service given my Microsoft. It mostly fits with on- premise database. Easy to match our server with MI and can migrate without changing much.
Difference:
Differences: Single DB, Elastic Pool and Managed Instance
| Features | Single DB & EP | Managed Instance | IaaS – SQL on VM |
| CDC | No | Yes | Yes |
| Collation | Default | Anything | Anything |
| CLR | No | Yes | Yes |
| Cross DB query | No | Yes | Yes |
| DB mail | No | Yes | Yes |
| DB mirror | No | No | Yes |
| Backup statements | No | Yes- to Blob | Yes |
| RESTORE statements | No | Yes from URL | Yes |
| Linked Server | No | Yes- only to SQL | Yes |
| OLE Automation | No | No | Yes |
| Open query | No | Yes | Yes |
| SQL agent | No | Yes | Yes |
| Active geo-replication | Yes | No | Yes |
| LTR-Backup | Yes | No | Yes |
| SSAS | No | No | Yes |
| SSIS | Yes with – ADF | Yes with – ADF | Yes |
| SSRS | No-Use Power BI | No-Use Power BI | Yes |
Pricing Tier: Basic, Standard (S0, S1, S2, S3 & S4) and Premium (P1, P2, P4, P6, P11 & P15).
Other database services: Azure Data warehouse and CosmosDB
Azure Data warehouse different from Azure database, it used for warehouse to load data by tools like Azure data factory for analytics and power BI.
CosmosDB – It is a multi-model database. It is service given by Microsoft to integrate to other database like No SQL (MangoDB, Cassandra etc), Azure table etc.
It is globally distributed to more region.
Advantages: 99.999% uptime and less than 10 ms latency.
Azure database
DB Backup
It is taken care by Microsoft itself based on the tier we have.
The backup retention also based on tier. If you want you can configure longer retention.
These backups are retained for 7 days for Basic, 14 days for Standard and 35 days for Premium tier.
There is no additional cost for backup storage until it goes beyond 200% of the provisioned database storage.
DB Restore
You can do this from portal. You cannot overwrite the database, you can rename the exiting DB and can restore or restore as new name.
DB security
You can have server and database level firewall settings.
— Create server-level firewall setting for only IP 0.0.0.2
exec sp_set_firewall_rule N’Example setting 1′, ‘0.0.0.2’, ‘0.0.0.2’;
— Update server-level firewall setting to create a range of allowed IP addresses
exec sp_set_firewall_rule N’Example setting 1′, ‘0.0.0.2’, ‘0.0.0.4’;
— Create database-level firewall setting for only IP 0.0.0.4
EXECUTE sp_set_database_firewall_rule N’Example DB Setting 1′, ‘0.0.0.4’, ‘0.0.0.4’;
— Update database-level firewall setting to create a range of allowed IP addresses
EXECUTE sp_set_database_firewall_rule N’Example DB Setting 1′, ‘0.0.0.4’, ‘0.0.0.6’;
Alert
You can create an alert from monitoring tab.
You can create own dashboard for metric CPU, RAM, error etc.
Performance Tuning
You can run the report, by like taking more CPU query and find which is taking more CPU.
We have Automatic Tuning like query store:
We can enable create or drop index or force plan to use any plan etc.
License – DTU vs Vcore
Vcore– you pay for the storage you use. You can, however, scale the storage up or down at any time, as per your requirements.
DTU – It is based on IOPS (CPU, Memory) and performance.
Migration
Choose Azure DB, EP, MI or VM with SQL.
Calculating the Database transaction units (DTUs) and choosing a suitable pricing tier
Preparing an on premise SQL database for a migration to Azure
Migrating your database based on the SQL service we have taken.
Methods:
Singe Azure database
Data Migration Assistant/ Data Migration Service
Replication
DACPAC
BACPAC
Generate SQL Scripts
Managed instance
You can use backup/ restore from Blob storage
SQL Azure VM
Standard migration
How to start learn Azure database services?
You can create a free Azure account by providing ‘a phone number, a credit card, and a GitHub account or Microsoft account username (formerly Windows Live ID)’ and can play around, you will not be charged until you upgrade within the given timeline 30 days.
https://azure.microsoft.com/en-gb/free/free-account-faq/
Do I pay anything to start with the Azure free account?
No. Starting is free, plus you get a £150 credit which you can spend during the first 30 days.
Do I have to pay after 30 days?
At the end of your first 30 days, you can continue to use your free products after you upgrade your account to a pay-as-you-go pricing and remove the spending limit. If you stay within the service quantities included for free, you won’t have to pay anything. The £149.062 free credit acts as a spending limit.
What happens once I’ve used my £150 free credit or I’m at the end of 30 days?
We’ll notify you so you can decide if you want to upgrade to pay-as-you-go pricing and remove the spending limit. If you do, you’ll have access to all the free products. If you don’t, your account and products will be disabled, and you’ll need to upgrade to resume usage.
