We know that Single Database is one of the deployment option provided in Azure SQL. Single database has its own dedicated set of resources. You have two options:
- DTU Based – You can calcuate DTUs required for your application through some testing on dev/test environments. Then you can use those DTUs on Production.
- vCore Based – you can calculate the virtual cores required for your application. If you already have DTUs, then you can calculate the vCores from it and use the vCores.
- Serverless – You do not need to worry about finding DTUs or vCores. Just find configure the minimum/maximum vCores and autopause delay. The advantage here is Azure will automatically scale the resources based in load.
In this article, let’s have a look at how to create a Single Database.
Create Single Database using Portal
On Azure Portal, click on “Create a resource” and then search for Azure SQL. It will open below panel for you. Click on create.
It will show you deployment options panel. As we want to create single database, just click on Create button under SQL database.
It will open below panel for you. This Create SQL Database panel is a multi step wizard. The first step of wizard asks basic information about the SQL database:
- Subscription, under which the resource should be created
- Resource Group, under which the resource should be placed. You can either select existing group or can “create new” by clicking on Create new.
- Database name, the name we want to give to Single Database
- Server, the server which will host this single database. Assuming you do not have any other server created, you can click on Create new button. It will open new server button which will ask details about server name, user name, password, etc as shown in the next snapshot. For server, you also need to provide physical location (i.e. Azure region) where the server will be created.
- Want to use elastic pool? , keep this default as we are creating Single Database.
- Compute + Storage, which specifies what are the memory and compute requirements.
Under Compute + Storage, if you click on configure database, it brings below screen. It asks how many virtual cores are required and what is the data size in GB. As you can see the handy thing is, you have approximate monthly bill calculation on the extreme right side, helping to understand how changing vCore will affect your monthly bill.
On this screen, you can select general purpose or hyperscale options.
There are two additional questions on this screen:
- Read Scale out, asking if the server should scale for read operations which is enabled by default.
- Making the server redundant in other Azure regions, this option is disabled by default.
Keep both of them default for purpose of this article.
Alternatively, If you do not want to provide vCore, then you can select the top left hyperlink from above snapshot, which will open below screen. It will allow you to enter DTUs and Disk size. You can also select pricing tier (Basic, Standard, Premium).
Then you need to click on Apply to go back to Create SQL Database wizard.
This is second step in wizard, which asks which connectivity method should be used. You can select any of three methods.
The first radio buttons means no access to the database server. Often you will select 2nd option. But to understand this setting better let’s keep it default. We will see how it affects under networking section of this article.
On this section, you can select database collation and whether to use existing data in the database or not.
We are selecting to have Sample database when single database instance is created. Also, it will take default collation for that database.
Tags and Create Resource
Next is Tags page, where you can specify key-value pairs to annotate your Azure resource. If you do not wan to apply tags, you can click on “Review + create” button to start creation of single database. The database creation will take few seconds to complete.
That’s it We have created the single database in Azure SQL.
You can now go to Query Editore (preview) and it will show you login screen where you can enter your admin credentials you provide while creating the database server. If you enter right user name and password, even then your login will fail. Can you guess why ?
This is because under Networking step of wizard, we selected no access. That’s why our IP does not have access to the database.
Let’s try to change this using the overview panel of the database we created. You can find the button Change Firewall Settings on overview panel if you navigate to SQL database.
It will open below panel.
You can do two important things from below panel:
- Toggle the access of other Azure services to this database. I have toggled it on.
- Add firewall rules to allow traffic from specific client IPs. If you are logged in to Azure portal from the same machine, you can directly click on Add client IP button, which will add rule for current machine’s IP address.
Once these changes are done, you can click on Save button.
Now again if you go to query editor and enter credentials, you should be able to login. After log in, you can run SQL queries as shown in below snapshot.
I hope you enjoyed this article. Let me know your thoughts.