How we create a simple database in Azure SQL Database
Certifications:
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
In previous article we had seen what one is Azure SQL Database and the differences between SQL managed instances and SQL elastic pools.
In the article we will analyze how we create one Simple Azure SQL Database step by step and what options we have when creating it.
But before you continue reading the article if you are not familiar with Azure you should read first this article which is dedicated to Azure infrastructure. It will be needed because it explains various concepts that we will encounter below such as Subscriptions and Resource Groups.
The example
To begin with, we connect to Azure Portal . There we choose it Menu, SQL Databases (from favorites) and Create.
Either way Menu, Create a resource, Databases, SQL database.
We define it Subscription that we have the charge (in my case I had one so it put it on itself).
The Resource Group it's basically a bucket of our infrastructure so, we put one we already have or build a new one. For new we choose Create New simply filling in the Data Center to which it will belong (in our case West Europe).
We put the name we want the database to have.
If we haven't already made one logical SQL Server we choose Create New. There we should give him a unique name, him local admin and like Location we will again put the Data Center we want it to belong to (ie West Europe again).
The SQL elastic pool we leave it No, since in this particular case we do not want it to manage many bases with shared resources.
Time to choose the resources we want our database to have.
Choosing Configure in the Compute + storage the following image will appear. There we can choose a classic billing model with DTU (e.g. basic for a base up to 2gb with minimum resources), Business Critical if we want to have a service with an infrastructure equivalent to Availability Group or leave it General Purpose which is infrastructure like Failover Cluster Instance.
In more detail, their differences exist in the article which we mentioned at the beginning.
Beyond the model we will see that we can choose the size of the base and the Cores it will have. In the compute tier a very good solution if our base will be accessed only during certain hours of the day is to choose the Serverless model. With this particular model if for some time there is no activity with active connections, we will not be charged as the base will be down*, but we will be charged by the hour (by the second to be exact) that the base is online.
*When we try to reconnect to the base, after about 1 minute it will turn itself on.
Then we choose how we will access the base. With the Private endpoint we will only have by creating a private node at the base of specific applications, VPN, etc. In our case I wanted to have access via Internet so I chose Public endpoint.
By choice Add current client IP address it basically adds the IP we are on Azure Portal at this moment as an exception in the Firewall so that we can connect.
In the next tab we put it collation that we want. In our case Greek_CI_AS.
We may add tags so we can search if it is something for Development or is it for a production system.
At the end it shows us one Review with what we have selected before pressing it Create. It also gives us the option to download it as a template so that we can upload a corresponding base any time we want in the future (DevOps).
After the creation of the SQL Database is complete, we will be able to go to the database resource. There we can see the servername which will be the hostname where we will connect. Also choosing it set server firewall we can see where we allow access.
There we will see that from the options we gave when we created it as we said before, the IP of the machine we were connected to was added Azure Portal. If we would like any device to be able to connect via Internet directly to the base to put Start IP 0.0.0.0 and End IP 255.255.255.255.
How do we connect to Azure SQL Database?
So now we can connect via SQL Server Management Studio ή Azure Data Studio. To connect to the database, simply put the local admin that we created when we created the logical one SQL Server which hosts the database (in create sql database, in figure 2).
If we want to make one login on the basis of which he is entitled read only we should simply run a query based on the following:
CREATE LOGIN [xristis] WITH PASSWORD=N'kwdikos' GO CREATE USER xristis FROM LOGIN xristis GO EXEC sp_addrolemember 'db_datareader', 'xristis';
We could use this login for example in an odbc connection. The connection strings we will see that there are ready in the tab Connection strings of the resource. The only thing we should change is to put the corresponding username and password.
But we can also connect with an account we have created at Azure Active Directory. To grant permissions (RBAC) in accounts we set it to Access Control (IAM). But I can also set my account directly as Active Directory Admin as we see in the image below:
This time let's try connecting from Azure Data Studio (we can also with SSMS). We will choose as Authentication type Azure Active Directory – Universal with MFA support and we simply log in with our account at Azure AD.
Finally, let's try to connect from command prompt with sqlcmd directly with the following command:
sqlcmd -S dpgr-srv.database.windows.net -ddpgr_db -Usmatzouranis@dataplatform.gr -G
