Azure

Azure cloud SQL database single database PaaS vs Elastic Pool EP vs SQL on Virtual machine IaaS

Advertisements

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.

 

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

%d bloggers like this: