Azure SQL database is a service from Azure. It is one of the example of PAAS. It's one of ready made service offering from Microsoft. where, no need to look into any kind of licensing or setting up the infrastructure on our own. There are two flavours of Azure cloud SQL Database for example Azure SQL database (PAAS), it's complete readymade service where we no need to maintain any kind of licensing or hardware, but in SQL virtual machine (IAAS), azure provide VM we need to install SQL server and upgrade them periodically by us if any latest updates from Azure releases.
Assume that, I would like to establish a startup and want to create a beautiful website for my company. Now, to make this, I need to look into some of the resources like, I need a server to host my webpage, I need a proper storage to store my data, I need a dedicated staff to monitor and maintain the infrastructure and also i need to concentrate on security for my infrastructure. If I want to do this everything in on premise or my own data center I have many disadvantages like as below
1. Higher initial cost to setup
2. I need a dedicated staff for my data center monitoring and maintainanace
3. Less flexible to upgrade my software and no automatic updates of my software
4. Also we cannot access our data from remote sources
In order to overcome above drawbacks from on premise data center, cloud computing is came into the market to setup the infrastructure quickly and easily with pay as you go business model. In cloud computing it is not needed upfront cost. We just need to create our resources as we need them and pay according to their usages. All the software updates are going to happen automatically here without any dedicated staff to maintain it. Even one can access the data remotely from different places of the world.
What are the different types of clouds ?
Public Clouds : These clouds are owned by third party providers and we can take the relevant services from them for a rent and pay as we use them. Here, services are going to be rented based on three services like Infrastructure as a service (IAAS), Platform as a service (PAAS) and software as a service (SAAS). Examples of such providers are like Microsoft Azure Cloud, Amazon AWS Cloud and Google cloud etc. Azure SQL database is a good example for PAAS service.
Private Clouds : These type of cloud are completely owned by you. There is a no third party involves here and you yourself will be the whole responsible for your changes and controlling the data withing your organization premises. Here data security is treated as a most important element.
Hybrid Clouds : In this type of clouds we will be having the provision for both public and private clouds. Many companies currently using this type of cloud to protect there confidential data within their on premises and rest of the services were used from public cloud providers where, there will be no confidential or sensitive datawere storing or using.
What is Azure ?
Azure is a public cloud provider maintained by Microsoft. Here users can choose their servers, data storage, databases and pay them as per usage by using its web portal. Maintainance and security protection will be handled by Microsoft. Azure is one of the most popular public cloud, more than 90 % of fortune 500 companies are using Azure cloud. Moreover, Azure cloud has a second largest market cap in the world in terms of cloud. Good thing to know is that, Microsoft spends every year more than 1 billion dollars to protect cyber threats.
Features of Azure SQL database
1. High availability and continuous business i.e if in case of any disaster in the region where we are deployed this SQL database will get automatically available in other region without any loss.
2. This can be also used for auditing purposes, for example, if you are an Data administrator you can have a track of all the activities on this database by creating some logs.
3. Even Azure SQL database has a benefit to connect directly to Power BI for data visualization purpose.
4. We can also mask our sensitive data fields by creating rules on those fields.
5. Elastic pools is also one of the benefit. It's an efficient way of load balancing. By creating a multiple SQL databases and having a common point to share the memory and storage between them.
6. Hybrid benefit, if you already have an existing sql database, you can use that license to get a benefit of more than 50% in your pricing.
7. One can also benefit from flexible pricing like we can choose any kind of pricing option like (DTU or vCore) and get best pricing for your usage.
How to create Azure SQL Database
1. Click on create resource and search sql database and click on create
2. Enter the details subscription, resource group, databasename, server (name, server admin login, password, location) , configure storage size and few more additional settings you can do and then click on create
Modes of configuration for SQL Database
1. DTU : It is an internal measure of calculation the storage configuration
Basic : In basic we have default 5 DTU, but we can adjust the data storage capacity
Standard : Both DTUs (Max 3000) and data storage capacity (Max 1TB) can be adjusted according to requirement.
Premium : Both DTUs (Max 4000) and data storage capacity (Max 1TB) can be adjusted according to requirement. Additionally, we can also select zone redundant option (To keep copies of the same within the zone), read scale out option (to keep read only feature instead of read write)
DTU : Database Transaction Unit
2. Vcore :
General purpose : We will be having different configurations like (Gen4, Gen5, FSv2 Series, M-series). Each of them have a seperate capacities of (Max vCores, Max memory, Max storage, Computecost/vcore/month)
In General purpose, we also have an options like (Provisioned, Serverless). In provisioned type we get charged irrespective of usage, but in Serverless we get charged as per the use. Also compute resources will get auto scalled in serverless
pricing is done based on the amount of Vcores we use.
Hyperscale : Apart from the features of General purpose here we have additional option to choose amount of replicas and autoscaling. This is designed for very big OLTP (Transactional) databases
Business Critical : This is also specially designed for OLTP applications with high transaction rate (Ex amazon) and low latency. It offers highest resilence to failures by using several isolated replicas. It is also highly expensive mode when compare to other types this we need to keep in our mind.
How to create a Virtual Machine in Azure
1. Search virtual machine from main page of Azure cloud platform and click on it.
2. Enter the name for your VM, subscription, select resource group, image (Ex: SQL server VM or Ubuntu etc), size of VM, ports, choose existing SQL DB if you have or you can also choose pay as you go with your desired VM, choose your disk type.
3. After that just click on review + create. Your new virtual machine will get created for you.
Connect and execute queries on Azure SQL Database
1. Open your Database which you have created.
2. From left side we can see the menu of some features. select Query editor and enter the login credentials for your database.
3. Since we don't have any tables in our DB we cannot see anything, but we can create one by typing "create table dbo.DemoTable" -> "(col1 float)" and run the query. This time a new table will get created under your tables menu.
4. We need to install SQL server management studio to connect/disconnect to our database in our virtual machine.
5. After installed, copy and paste the server name of of DB. Now, enter the same username and password. Now, click on connect.
Note : When we login into SQL server management studio, we basically login into sql server i.e we can see all the objects inside it, but when we login into our DB we can see only objects inside the DB. Finally, before connecting our DB to server we need to add our client IP in the firewall section of SQL server. If we miss this step will get error while connecting.
Also, enable "Allow Azure services and resources to access this server" inside firewall section .
Sample query:
Ex : select * from demotable (With this command we can see all the columns of table)
What is Elastic Pools ?
We can create Elastic pool to share DTU's within various databases and avoid overprovisioning or underprovisioning of resources. As I already explained above that, we are going to be charged based on the amount of DTU's rather than the amount of databases. so, when we share the DTU's within DB's we can use them productively. Even in case of peak times this will be supporting us.
How to create Elastic Pools :
Inside SQL server we have an option to create new elastic pool just like option to create new database. Click on that create new elastic pool -> give a name -> configure elastic pool then click on create .
Now a new elastic pool will be create under the name you have given. Lets click on this newly created elastic pool -> click on add database and enter the DB's you want to add and save it. That's it, you have successfully added your DB's to elastic pool.
What is Active Geo Replication ?
Active Geo Replication is a feature inside database to create a replica of our DB into some other region. So that, if in case of any disaster happens to our pimary DB still we have an option to recover our data from this replica DB. Now, let's see how to create one for our DB.
Go inside your DB -> on left side menu click on Geo-Replication -> select target region (your choice) -> click on server -> choose your existing server or create new one
Note : We cannot change the configuration of secondary database. It should be same as your primary database.
Whatever we did for replication of DB till now is manual, in order to make it automated when a failover happen is by using failover group
Go to your server and under settings we can find at the left side "failover group". Click on failover group -> give a name -> select secondary server (esisting or new one) -> choose readwrite policy -> select database and click on create. That's it you are done
What is Dynamic Data Masking ?
When we want to mask our sensitive information (Ex email id, credit card number etc) this is one of the important feature offering by azure. In your database when you entered under the setting you can find option called "Dynamic Data Masking" just select it and click on Add mask . Before we add mask make sure what kind of data do we have in your table. one way you can create a masking rule, just click on add mask and then enter name for our rule, table name and column after this just click on add.
Now we are successfully added a mask to our sensitive data. When we make a query on that table, we can see our data is hidden. Please note that, if you login to your database as a admin, However, you created masking for it you can see that data. Make sure this when you are using data masking feature.
Thank you for reading this article, stay in touch for our upcoming articles where we are going to write follow up articles on Azure Data Factory, Azure Data Lake Storage, Azure Cosmos DB, Azure Data Bricks, Azure Synapse Analytics, Power BI etc
