Creating a Database on the Azure Cloud and connecting Resources.

Introduction

In this article we will create a database on the Azure clouds, using the Azure SQL Database Creating an account in Microsoft Azure. Next, we will connect the interface between Microsoft SQL Server locally with Azure SQL Database.

Objective

The objective is to simulate the real scenario of a cloud database environment, allowing an online data management system in the clouds. This project is part of a larger online recommendation system project in the same format practiced by companies like Amazon and Netflix that offer customized products based on the user's profile for generating customized sales.

We will also perform the connection interface test between Azure Machine Learning studio classic and Azure SQL Database in the clouds to check the possibility of importing and manipulating tables and database data from Azure Machine Learning classic, as we will use Azure Machine in the future Learning Studio in the clouds to apply the recommendation system algorithm.

The first step is to create a free account at Microsoft Azure for testing purposes. In the screen below, at the top right, we find what is included in the services when creating a free account.

Fig.1 Creating a Microsoft Azure account

Fig.2 Account creation confirmation.

Fig.3 Azure Services, environment where we see resources.

Fig.4 Creating a resource group and naming and entering the resource password

Then we click on configure database and select the standard configuration.

Fig.5 Configuring the Database

We chose the options that consume less resources only for simulation purposes, thus avoiding unnecessary expenses for laboratory and academic testing purposes.

Fig.6 Click next to continue.

Fig.7 Choosing the network configuration option in the access to configure connection method after server creation and without following click next.

Fig.8 It will be created the sample database and then click the review button.

Fig.9 Reviewing the creation of the resource before creating definitively and then click on create.

Fig. 10 Implementing and provisioning the resource group, server and database. This can take 2 to 5 minutes.

Fig.11 After deployment, click on “go to resource”.

Fig.12 Database successfully implemented

Fig.13 Accessing the database with the previously created password.

Fig.14 Display of the created database query panel.

There are some tables that were generated as a sample for testing purposes when we were setting up the creation of the database.

Fig.15 Performing a query in the database.

Important: For testing environment, we must remove all resources created to avoid unnecessary expenses.

Remember that there are other ways to create resources in Azure, which can be:

· Portal Azure

· Azure CLI

· PowerShell

These Azure CLI and Powershell options allow you to use scripts to standardize templates and thus facilitate the process of managing resources in the clouds.

The Documentation of the steps taken so far can be consulted on the link below:

https://docs.microsoft.com/en-us/azure/azure-sql/database/single-database-create-quickstart?tabs=azure-portal

Use SSMS to connect to and query Azure SQL Database or Azure SQL Managed Instance

1. Open SSMS.

2. The Connect to Server dialog box appears. Enter the following information:

Fig.16 Connecting Azure Database using SSMS.

Fig.17 SSMS connected.

Fig.18Connecting the desired database.

Fig.19 Visualization of the database hosted on the Azure SQL Database successfully.

As we already have the tables that are part of the development of our recommendation system in csv format, we will start the process of importing these tables into our cloud database, so we will have all the resources to be processed in the clouds.

Figures 20 to 26 demonstrate this process.

Fig.20 Importing tables in csv format locally.

Fig.21 Continuing the import process.

Fig.22 Selecting the local folder where the tables are.

Fig.23 Visualization of the data of the table that will be imported.

Fig.24 Verification of data characteristics.

Fig.25 Summary.

Fig.26 Visualization of tables within the local SSMS.

Below we have our new database created for this project in the clouds.

Fig.27 Creation of the RESTAURANT database within Azure SQL Database on the cloud, which will be the database for the restaurant recommendation system project that will be developed and resented in another article.

Fig.28 Viewing imported tables within Azure SQL Database.

Fig. 29 Connecting Azure Machine Learning Studio classic with Azure SQL Database in the cloud.

Fig.30 Selecting the resource for importing data.

Fig.31 Entering authentication data with Azure SQL Database.

Fig.32 Performing the table import from the created Azure SQL Database.

Fig.33 Table imported successfully.

Fig.34 Viewing data from the table in Azure SQL Database from Azure Machine Learning Studio classic.

Fig.35 Visualization of the data successfully imported successfully.

Now that we have our database created on the cloud, we will connect PowerBI to the Azure SQL Database, just as we did with Azure Machine Learning Studio classic

Fig.36 connecting powerbi directly to Azure SQL Database.

Fig.37 Entering authentication data to connect to Azure SQL Database

Fig.38 User login.

Fig.39 PowerBI successfully connected to Azure SQL Database.

Conclusion

Based on this tutorial, we present the following steps below:

• How to create an Azure account

• Create a workspace

• Create the Azure SQL Database

• Create databases within Azure SQL Database

• Manipulate tables using the query within Azure SQL Database and SSMS.

• Create the interface connection between the SSMS and the Azure SQL Database, allowing to operationalize all routines in the SSMS and automatically updating the Azure SQL Database in the cloud in real time.

• Connect Azure Machine Learning Studio Classic directly to the Azure SQL Database in the clouds, allowing data to be imported directly from the cloud.

In the documentation on the link below you have other options for demonstrating access to the database with more details.

• For information about SSMS, see SQL Server Management Studio.

• To connect and query using the Azure portal, see Connect and query with the Azure portal SQL Query editor.

• To connect and query using Visual Studio Code, see Connect and query with Visual Studio Code.

• To connect and query using .NET, see Connect and query with .NET.

• To connect and query using PHP, see Connect and query with PHP.

• To connect and query using Node.js, see Connect and query with Node.js.

• To connect and query using Java, see Connect and query with Java.

• To connect and query using Python, see Connect and query with Python.

• To connect and query using Ruby, see Connect and query with Ruby.

Note: This project does not consider data governance issues, as the purpose of this article is only to demonstrate how to create a database in the cloud.

As we mentioned earlier about the feasibility of working with templates, in the figure below we have an example of how to import a created template, this process is a facilitator that adds efficiency to the cloud management processes.