MS Azure : Demo of SQL Database Changing Pricing Tier

Demo of SQL Azure Database Changing Tier

Today in this demo, we cover changing pricing tier and the performance by executing query in iteration through one of the application.

Demo Details and Highlights
Database Name : t1
Current Pricing Tier : Basic (5 DTUs)
Changing to Pricing Tier : Standard S1 (20 DTUs)

As you can see in below, We have one database name t1 which is set to pricing tier Basic limited to 5 DTUs.

Performance at Database t1 with Basic 5 DTUs Pricing Tier
Checkout the time taken by simple SELECT query's 4 iteration with 50 no. of threads.
Total Elapsed Time = 21.448 Seconds

Azure SQL Database Changing Pricing Tier
To change the pricing tier, we have to select/search the database (in this case i.e. t1), from the settings click on the option Pricing tier (scale DTUs) it will show all the options of pricing tier available for Single Database model to scale up or down service tier and the performance levels within the service tier.

Note : Change may cause a downtime, according to utilization of the database and also depends on the size of database.

So in this case we are changing database t1 pricing tier from Basic (5 DTUs) to Standard S1 (20 DTUs).
Once you select the desired pricing tier, it will start the process of changing tier and duration of the change is depends on the amount of data and once new selected tier is effected then only charges will change.
You can check the change in the azure portal as shown in above figure or by running below SELECT T-SQL command in SQL Server Management Studio.

SELECT
db.name [Database],
ds.edition [Edition],
ds.service_objectives [Service Objective]
FROM
sys.database_service_objectives ds
JOIN sys.databases db ON ds.database_id = db.database_id

Performance at Database t1 with Standard S1 20 DTUs Pricing Tier
Now lets again run that simple SELECT query's 4 iteration with 50 no. of threads and Checkout the time taken by the database.
Total Elapsed Time = 7.848 Seconds

Result : Just by scaling up from Pricing Tier from Basic 5 DTUs to Standard S1 20 DTUs, performance of the database has been increased from 21 to 8 Seconds.

MS Azure : Firewall and Authentication

Firewall and Authentication

Firewall

There are two ways to connecting to a database: 1. Go through the firewall 2. Authenticate yourself
Now through Firewall, there are two type of Firewall:
1. Server level Firewall: Server level firewall rules for administrator and when you have many database that have the same access requirements.
2. Database level Firewall : Use database level firewall rules to enhance security and to make your database more portable. (You can specify IP range)

Authentication Workflow

First Step where user is attempting to made a connection at Server level, If requested IP is in range as per firewall then it is authenticated by server level firewall it goes to the database level and authenticate yourself.

If IP is not in range then it is not authenticated by server level firewall then goes to next level firewall which is database level and again it checks that IP is within given firewall range of the database then it goes to authenticate yourself. If NO then request to connect with database got failed.

Authentication

There are two type of authentication:
1. SQL Authentication : SQL Authentication is the alternate to directly gets authenticate to Azure SQL Database.
- Individual accounts Authentication Only.
- Multi-Server access requires multiple users.
- No built-in password expiration.

2. Azure Active Directory : Azure Active Directory authentication is a mechanism of connecting to Microsoft Azure SQL Database and SQL Data Warehouse by using identities in Azure Active Directory (Azure AD). With Azure AD authentication, you can centrally manage the identities of database users and other Microsoft services in one central location.
- Individual and group accounts Authentication.
- One Identity can be granted access to multiple Server.
- Password expiration follows the ADD policy.

In next post will be cover the demo of firewall and authentication module.

MS Azure : Create Elastic Pool

Steps to Create Elastic Pool

Today we will cover the demo to create Elastic Pool.
As you know Elastic pools are well suited for large no. of databases with pattern of low average utilization with relatively infrequent utilization spikes.
Fixed cost per eDTUs, eDTUs are tied to storage.

Lets understand the benefits of Elastic Pool in terms of Pricing Tier through Example:
- 16 S3 Standard Databases : 16X$150 per month = $2400, each db has static 100 DTU. Maximum 250GBX16 Storage = 4TB Storage Total.
- One Standard Pool : (800 eDTU X$2.23) per month =$1784, each db can go 10-100 DTU. 800 GB Storage.
As per above calculations, you can see it is only beneficial for no. of database which are small in size and low utilization.

Demo to Create Elastic Pool
1. Login to Azure Portal with your credentials.

2. Search your SQL Server and click on New Pool.

3. Once you clicked, it will prompt to provide following details to create Elastic Pool:
a. Name : Provide Elastic Pool name.

b. Pricing Tier : Select pricing tier, here we have selected Standard S.

c. Configure pool : Select the no. of Pool eDTUs and Storage Pool GBs both are directly proportional to each other, here we have selected:

Once you clicked on Configure pool, it will shows the below screen where we have to provide details for Elastic database pool Setting.

Both Pool eDTUs and Pool GBs both are directly proportional to each other, once you increase the value of one parameter, second will get auto increased.

Once provided all the configuration setting for elastic pool, then have to choose the database which will be the part of the elastic pool.

Select the databases, here we have select t1 , t2 database.

Now as you can see in below snapshot, t1 & t2 databases are the part of Elastic Pool (Elastic Pool-1).
Elastic Databases : Max limit 200 dbs, as of now 2 databases out of 200 dbs.
Pool Storage : Max size limit 100 GB, .004GB is in used out of 100 GB.

d. Summary & Cost : Once you provided above information, this label field will auto filled with summary and overall cost for Elastic pool.

That's it!! So conclusion is multiple databases can share resources through elastic database pools. The service also offer other elastic database capabilities.

MS Azure : Create Azure SQL Database using Single Database Model

Create Azure SQL Database

To create new Azure SQL Database follow the below steps:
Step 1 : Login to Azure Portal https://portal.azure.com with your credentials.

Step 2 : Once you Logged In successfully, portal will look like below snapshot.
From the left pane, you have to find out SQL databases as highlighted with red block in below snapshot.

Step 3 : Once you clicked on SQL databases, it will show you below screen and to create new SQL database, click on Add button as highlighted below.

Step 4 : Once you clicked Add button, it will prompt a new window to provide basic information to create new SQL database:
1. Database name : provide database name
2. Subscription : This is the drop box, Choose from the available subscription created by you.
3. Resource group : This is radio box, two options Create new or Use existing and Select the resource group.

4. Select source : This is drop down with 3 options, Choose from options 1. Blank database 2. Sample 3. Backup

5. Server : Select the server, once you clicked it will give you the option to choose from the list of available server or either create new server.

6. Pricing tier : Once you click, it will you all options to choose your pricing tier for the database.

7. Collation : Choose the collation, by default its SQL_Latin1_General_CP1_CI_AS.
Once provided all required information, click Create button. It will start the new SQL database deployment.

Step 5 : Once deployment successfully completed then click on Refresh button, it will shows the database under the list of SQL databases on the portal as shown below:

Choose the database to know the properties/settings of the SQL database, it will give you the screen as below

That's it!! Only in few minutes Azure SQL Database is ready to use.

MS Azure : Create Azure SQL Servers

Create Azure SQL Servers

To create new Azure SQL Server follow the below steps:
Step 1 : Login to Azure Portal https://portal.azure.com with your credentials.

Step 2 : Once you Logged In successfully, portal will look like below snapshot.
From the left pane, you have to find out SQL servers as highlighted with red block in below snapshot.

Step 3 : Once you clicked on SQL servers, it will show you below screen and to create new SQL database, click on Add button as highlighted below.

Step 4 : Once you clicked Add button, it will prompt a new window to provide basic information to create new SQL database:

1. Server name : Provide server name
2. Server admin login : Provide admin login name.
3. Password : Provide password
4. Confirm password : Confirm password
5. Subscription : This is the dropdown box, Choose from the available subscription created by you.
6. Resource group : This is radio box, two options Create new or Use existing and Select the resource group.

7. Location : This is dropdown box, Select the region which near to your application server.

Step 5 : Once deployment successfully completed then click on Refresh button, it will shows the database under the list of SQL databases on the portal as shown below:

Choose the Server to know the properties/settings of the SQL server, it will give you the screen as below

That's it!! Only in few minutes Azure SQL Server is ready to deploy new SQL Database.

MS Azure : SQL Database DTUs Calculator

Azure SQL Database DTUs Calculator

If we want to migrate SQL Server Database which is running on VM Server to Azure SQL Database. How can we know, how many DTUs are required?
So for an initial estimate, there is a DTUs calculator at http://dtucalculator.azurewebsites.net/ developed by Justin Henriksen, Azure Solution Architect at Microsoft.
Once you open this DTUs Calculator, we will find both models calculator (Single Database & Elastic Pool model).

DTUs Calculator Workflow

Here are the high level steps to calculate the required DTUs to migrate the Database:
Step 1 : Run the Trace on database server atleast for an hour or when the database is highly utilized and Collect the performance trace output.

Step 2 : Upload the trace file to the DTU calculator website, mentioning the no. of cores on the current Server.

Step 3 : DTU calculator will give you the result and we have to analyze and start with estimated DTU configuration.

Note : This is just a way to identify the estimate DTUs required to migrate database, not the actual DTUs.

MS Azure : What is Database Transaction Units (DTUs) ?

Database Transaction Units (DTUs)

A DTU is a unit of measure of the resources that are guaranteed to be available to an Azure SQL database at a specific performance level.

DTU is measures how powerful is your database is, but how it measures ?
DTU is measured based upon the resource utilization by the database:
Blends CPU + Memory + Data I/O + Log I/O
It is based on a Microsoft OLTP benchmark. It is useful unit of comparison.

So basically in short, Higher DTUs provide higher throughput.

Changing Tiers :

You can move/change database from a lower tier to a higher tier or vice versa at any point in time.
There are few point which we have to keep in mind before changing the tier:
1. Duration of the change depends on the amount of data.
2. Your charges will change once the new tier is in effect.
3. You need to retry logic in your application according to DTUs usage (Resources utilization - CPU / Memory / Data & Log I/O).

For Example :

If we execute 10 Queries on both Tiers, result would be like as follows:
In Basic Tier a Database with 5 DTUs limit will complete 10 queries execution in 60 Seconds.
In Standard Tier a same Database with 100 DTUs limit will complete 10 queries execution in 3 Seconds.

Click here for the demo of changing tiers example in further posts.

MS Azure : SQL Database Service Pricing Tier Part - 2

SQL Database Service Pricing Tier

In the last post covered, SQL Database Service Pricing Tier for Single Database model.

Click here for Single Database

B. Elastic Pool

Elastic pools are well suited for large number of databases with pattern of low average utilization with relatively infrequent utilization spikes.
The more Databases you can add to a pool the greater your saving become. Elastic pools are available in Basic, Standard and Premium service tiers. Each tier is distinguished primarily by its overall performance, which is measured in elastic Database Transaction Units (eDTUs).

1. Basic : Basic is further distinguished 8 type based upon eDTUs per pool as follows:
Max Pool Size Limit : 5-156 GB
Max DBs : 100-500
eDTU per Pool Range : 50-1600
Max eDTU/db : 5
Max Size/db : 2 GB

2. Standard : Standard is further distinguished in 11 type based upon eDTUs per pool as follows:
Max Pool Size Limit : 50 GB - 2.9 TB
Max DBs : 100-500
eDTU per Pool Range : 50-3000
Max eDTU/db : 100
Max Size/db : 250 GB

3. Premium : Premium is further distinguished in 10 type based upon eDTUs per pool as follows:
Max Pool Size Limit : 250 GB - 750 TB
Max DBs : 50-100
eDTU per Pool Range : 125-4000
Max eDTU/db : 1000
Max Size/db : 500 GB

Note: Outbound data transfers are charged at regular data transfer rates. SQL DB Elastic Pools is in General Availability in all regions except India West.

Click Here , For more details and exact pricing.

MS Azure : SQL Database Service Pricing Tier Part - 1

SQL Database Service Pricing Tier

On the top layer Database pricing is classified in two models:

A. Single Database

Single databases are fully isolated databases optimized for workloads when performance demands are somewhat predictable. You can scale a single database up or down through the Basic, Standard and Premium service tiers to get the performance and features your app needs, precisely when it needs them. Each tier is distinguished primarily by performance, which is measured in Database Transaction Units (DTUs).

B. Elastic Pool

Elastic pools let you manage the collective performance of a pool of databases rather than single databases. The databases in the pool automatically scale up and down to meet demand. You can easily add and remove databases from the pool, scaling your app from a handful of databases to thousands, all within a budget you control.

In the first release of the SQL Database service pricing tier, there were two type of configuration:
1. Web DB - Web Database is basically for web sites and web apps, its limited up to 5GB.
2. Business DB - Business database is basically for data-warehouse and for big project, its limited up to 150 GB.

In the current configuration of the SQL Database Service pricing tier are distinguished in three categories:
1. Basic
2. Standard
3. Premium

These tiers distinguished based on following characteristics:
1. Size : Size limit of the database.
2. Performance : Performance of the database which is measured in DTUs ( Database Transaction Units ).
3. Concurrency : Concurrency related to login attemps, sessions and work threads, we will discuss more about this.
4. Recovery : Recovery is directly related to Point in Time Restore, which is different for each category of pricing tier.
5. Features : There are few advance features which are only available in high tiers like In-memory OLTP, Column-store Indexes.

Now Lets discuss the Current Categories of SQL Database Service pricing tier for Single Database model, which is most commonly used in comparison to Elastic Pool Model.

A. Single Database

1. Basic : There is only 1 type, which is known as B.
Size Limit : 2 GB Max storage per DB
Performance : 5 DTUs
Recovery : Point in Time Restore 7 Days.
Features : Doesn't Supports In-memory OLTP and Column-store Indexes

2. Standard : Standard is further distinguished in 4 type as follows:
Size Limit : 250 GB Max storage per DB
Performance : 10-100 DTUs
Recovery : Point in Time Restore 35 Days.
Features : Doesn't Supports In-memory OLTP and Column-store Indexes

3. Premium : Premium is further distinguished in 6 type as follows:
Size Limit : 500-1024 GB Max storage per DB
Performance : 125-4000 DTUs
Recovery : Point in Time Restore 35 Days.
Features : Supports In-memory OLTP and Column-store Indexes

Concurrency : Concurrency is based on Login Attempts, Worker Threads and Sessions per database details are different for each Tier:

Click here for Elastic Pool

Click Here , For more details and exact pricing.

MS Azure : What is Azure Database?

MS Azure Database : Cloud Database as a Service
- Azure SQL Database is a Database as a service offering on Microsoft Azure.
- It aims to reduce the cost of running database infrastructure.
- The service is billed by the hour, based on the service tier of the database.
- The service is easily provisioned through different methods.

What is Database as a Service (DaaS)?
Daas is a public cloud offering defined by 3 fundamental characteristics.
1. The Service provider owns and manage the database software, admin task like backup/restore and high availability, the operating system and hardware.
2. The service users pay according to their usage of the service. There is no upfront expense or contract lock-in unless the user wants it.
3. Users can scale up or down on demand and also create and destroy environments on demand with no provider intervention.

Billing Model
1. The Service is paid by the hour.
2. The hourly rate is based on the highest service tier selected during that hour.

As a DBA, What is your responsibility?
When SQL Database on VM:
1. Maintain the Operating System.
2. Maintain SQL Server Services and its Databases.
3. Setup Backups
4. High Availability
5. Disaster Recovery
6. Change Control, performing changes on production with minimal downtime.
7. Performance, have to ensure database is providing good performance
8. Security, database should be secure.

When SQL Database on MS Azure:
1. Choosing the right service tier.
2. Test the High Availability and Disaster Recovery Strategy.
3. Change Control, performing changes on production with minimal downtime.
4. Performance, have to ensure database is providing good performance
5. Security, database should be secure.

Advantages of MS Azure SQL Database
1. Pay for what you use by the hour.
2. No need to maintain hardware, virtualization, Operating System Or database software.
3. Scale Up Or Down the resources on Demand.