Hi everyone, we are back again to train you on Microsoft Azure SQL databases and will also provide you cost effective tips and tricks when dealing with multiple number of databases in azure.
Microsoft Azure is rich for multiple data storage options among which SQL databases is most suitable for web apps. SQL databases are similar to what we understand for SQL Server but Microsoft has turned it into SaaS and is running within Azure.
Creating and configuring database
Let’s get started by creating a database just like we use to do in SQL server management studio but here just click on SQL databases in Azure options pane and hit the Add button on the top as shown below:
After hitting the Add button a familiar Azure blade option will appear as shown below:
Provide a unique database name, choose your subscription and existing resource group from the dropdowns and select required source for your database which could either be blank database, Microsoft sample database or any backup database.
Now we would create a database server by selecting the Server option from the Azure blade, select Create a new server and fill the required field as shown below and you are good to go with Azure SQL database server.
Now the next question it ask, if we want to use SQL elastic pool? We choose Not now option for instance but we will explore it in Scaling Azure SQL databases portion.
Next is the pricing tier, Microsoft created the wizard for us containing Basic to Premium pricing plans for our database and can be differentiated in terms of Database Transaction Unit (DTU) which is the database performance metric basically a blended measure of CPU, memory and read/write rates assigned to the database. Please choose the well suited pricing tier as per your needs, In my case I had chosen the Standard pricing tier with 10 DTU and 250GB of storage costing 10 USD/month as shown below:
Last option is of Collation which set some predefined rules to sort and compare data in database and is set by default but cannot be changed after database creation. Now hit the Create button and after some processing you would have a database to work with in the newly created database server as shown below:
As you can see this virtual database server currently has only one database which was created as blank database but I had added some schema and data in it for operational purpose. So going into it we found a number of interesting properties such as DTU utilization graph which tell us if the database is under heavy use or not then we will get to know that the DTU offered by our database server are not sufficient and probably have to worry about growing them, write now it’s not using any DTU as it’s a brand new database as shown below:
You can also restore your database by selecting the Restore option on the top and using one of its coolest feature called Point-in-time recovery, Microsoft keeps a running log about each transaction against the database so if in case some damaged is caused to the database you are not need to worry, just picked up the date and time prior to that damaged and hit OK, the database will roll back to that point in time.
Going to the Long-term option, as Azure is doing backups of our database we can choose a specific backup if we need to go back to a particular state in case of some disaster happens to the database.
Azure SQL database features
Let’s explore one of the cool feature called Data explorer which enables to explore data within SQL database. Click on the Data explorer present in database option blade and here we can login to the database server providing username and password just like in SQL server management studio as shown below:
On successful login we could see all our tables, views and stored procedure within our database, we could also query tables using transact SQL in query editor similar to SQL server management studio as shown below:
We can also perform CRUD operations on our table by right click on the table and select Edit option, a window just as shown below will appear for performing these operations.
This virtual database server is running within Azure environment but it makes you feel that you got your own server as you can perform all operations just like on premises SQL server environment.
The other cool thing is the Geo-Replication, moving into it we could see a cool little map on the top containing Microsoft’s worldwide location with the blue checked octagon which is our database server’s region.
Right now we aren’t having any geo replication configured for our database but here Microsoft is recommending the East US region as the place to replicate our database because within Microsoft Azure there’s a concept of paired location, although the connections among all region is really fast but Microsoft has given extra priority for paired region as data synchronization between them is really fast.
Let’s say you want to keep readable copy of your database in East US, simply click on it and configure it just like you did for creating a new azure SQL database. After setting it up you will be having two database servers in two different region and your both databases will keep synchronizing on each transaction.
You can also set East US region in FAIL OVER policy just if for any reason your West US database server goes down then East US database server will be serve in its place.
If your data is really important and you want its availability every time then Geo-Replication feature is the right choice.
Azure database import and export
Now that you are comfortable taking your real database present in your local machine and installing it into Azure SQL databases.
One way of doing it is to use Import database feature present on your SQL database server just as below:
On selecting Import database button below window would appear:
Choose subscription from the dropdown, database file from container of your storage account, pricing tier same as you did for creating new database, set collation accordingly, SQL server authentication is set by default, provide your database server credentials and hit OK, your database will be imported and created as additional database into Azure database server.
Next come Azure SQL database Export feature, Click on the database you want to export and click on the Export button as shown below:
This Export button will open up the window just as below after filling all the fields and hitting OK, it will create file of the selected database and store it into your Azure storage account later which could be downloaded from there.
Scaling Azure SQL databases
One of the great thing about Microsoft Azure services is the ability to scale, similarly we can scale Azure SQL databases in two ways, manually and dynamically.
For manual scaling you can simply go to your database and choose pricing tier from the options blade. Selected database was created at a particular size and at Standard plan but to increase database performance we would simply drag a slider to 150 DTU and price increases itself.
Click Apply button, there will be a little shutdown and restart and within couple of minutes our database would be scaled.
Dynamic scaling is useful when there are numbers of databases into your Azure SQL database server and when it becomes difficult to monitor each of their usage and decide which should be scale manually as well as they are costing you individually then the concept of Elastic pool meet your requirements here.
Microsoft introduced SQL Elastic pool to share database services among all database in it but these services can grow as per database requirement in terms of DTU.
To create elastic pool just click on New pool button on the top of your SQL database server as shown below:
A window just like below would appear in which you will provide pool name, select appropriate pricing tier, configure DTU per database as per requirement and add databases you want in your elastic pool, hit OK and you are good to go.
Always use Elastic pool when you have multiple databases in your Azure SQL database server as it allows databases to grow beyond the limit of manual scaling and it really save your money.
I hope you all had learned something very informative through this blog post finding Microsoft Azure as a suitable cloud platform for managing your SQL databases.