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.