To learn more about the Azure SQL database, today we’ll learn how to create a database using Azure Portal and SSMS.
Database management is one of the key concerns in the computing world presently. As the options for public cloud adoption increase consistently, enterprises want to adopt better alternatives for storing their data in an organized fashion on the cloud. In addition, it is also essential to ensure the availability, scalability, and security of the cloud database management solutions.
Prominent public cloud players such as Microsoft Azure provide their native SQL Database services as reliable options for database management. Azure SQL Database has been one of the promising database management platforms due to its various functionalities. If you know how to create a database using SSMS (SQL Server Management Studio), then you would find the Azure SQL database as a promising choice.
Also Read: Factors Affecting Disk Space Requirements for SQL Server
The following discussion dives into an introduction of the Azure SQL database with an outline of its functionalities. Then, the discussion would present a detailed outline of steps to design your first database within the Azure SQL database.
What is Azure SQL Database?
As mentioned already, let’s start with the definition of the Azure SQL database before proceeding towards creating a database. Azure SQL Database is the fully-managed Platform as a Service (PaaS) Database Engine for the administration of many database functions such as upgrades, backups, monitoring and patching without the involvement of users.
Azure SQL database always runs on the latest stable version of the SQL Server Database Engine. In addition, you can also find the patched OS to provide 99.99% availability. The in-built PaaS capabilities in the Azure SQL database helps in focusing on domain-specific database administration and optimization processes which are critical for enterprises.
You can create a database using Azure Portal and SSMS on the Azure SQL database for creating data storage layers for applications and solutions on Azure with high performance and availability. Furthermore, the Azure SQL database also serves as the right choice for various modern cloud applications with support for processing relational data and non-relational structures.
Why is Azure SQL Database Preferable?
Some of the other factors that can drive you to design your first database within the Azure SQL database focus on the additional benefits it has. Based on the latest stable version of the Microsoft SQL Server database engine, users can access advanced query processing features. Users don’t have to incur overheads for patching or upgrading to obtain the latest SQL Server capabilities.
Another noticeable highlight of Azure SQL Database is the facility of two distinct pricing models. Therefore, users can access different functionalities for easier definition and scalability of performance in the different pricing models. The two models are the DTU-based purchasing model and the vCore-based purchasing model.
Users should think to create a database using Azure Portal and SSMS on Azure SQL database with the facility of in-built high availability, common maintenance operations, and backups. Therefore, users don’t have to worry about patching and updating of the operating system and SQL code or management of underlying infrastructure. So, you can clearly notice how Azure SQL Database is the right pick for your database management needs.
AWS and Azure are the biggest competitors in their offerings related to data management. Let’s look into the AWS vs Azure Database Comparison and see who wins.
Creating a Database Using Azure Portal and SSMS
Now, it is important to find out the process to design a database using Azure Portal and SQL Server Management Studio (SSMS). The foremost requirements for the process are SQL Server Management Studio’s latest version and latest versions of BCP and SQLCMD.
We shall explore the processes for creating a database with Azure Portal and connecting to the database with SSMS. In addition, let us reflect on the process of creating tables with SSMS. You start by signing in to the Azure Portal, and then you can go on with the following steps.
Users should note that a single Azure SQL Database comprises of information regarding a particular set of storage and compute resources. You have to create the database within an Azure resource group with the management being suitable for a database server. You can create a database using Azure Portal and SSMS on Azure SQL Database by following the simple steps as mentioned below.
- Select the “Create a resource” option on the Azure portal menu or the Home page.
- Then, on the “New” page, you have to select “Databases” in the Azure Marketplace page. Now click on “SQL Database” in the “Featured” section.
- Users will have to fill the SQL Database form with information about database name, subscription, and the resource group and select source. It is important to refer to rules for database identifiers, subscriptions and the naming rules and restrictions.
- Now, click on “Server” for using an existing database server or for the creation and configuration of a new database server. Click on “Create a new server” and then fill the required information in the “New server” form. The required information includes server name, password, and location and server admin login.
- Click on “Select” and select the “Pricing tier” for specifying the amount of storage, service tier, and number or vCores or DTUs. Now, click on “Apply”.
- Input a “Collation” for the blank database, preferable the default value.
- After compilation of the “SQL Database” form, you can click on “Create” for provisioning the single database.
- Users can click on “Notifications” on the toolbar for monitoring the deployment process.
Connecting to Database Using SSMS
After creating the database using the Azure portal, you need to understand the role of SSMS. You can create a database using SSMS to establish a connection to the database. Here are the important steps that can help you connect with a single database effortlessly.
- First of all, access the SQL Server Management Studio (SSMS).
- Find the “Connect to Server” dialogue box and enter information about server type, server name, SQL Server authentication, server admin account, and password.
- Then, click on “Options” in the same dialogue box. You have to enter the name of the database to connect to the database in the “Connect to database” section.
- Now, you have to click on “Connect” to open the “Object Explorer” window in SSMS.
- Here, you have to expand the “Databases” and then the selected database for viewing objects in the sample database.
Create a Table with Azure SQL Database
Now, the last highlight of this discussion would the process to create table with Azure SQL Database. Let us assume the example of creating a student management system with the help of Transact-SQL. The database schema would include four tables for Person, Course, Student and Credit.
- Open the “Object Explorer” and then right-click the concerned database and then select “New Query” option. You can find a blank query window connected to your database.
- In the query window, you have to execute the following query for creating four tables. Therefore, you can also create tables easily when you create a database using Azure Portal and SSMS.
— Create Person table
CREATE TABLE Person
(
PersonId INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(128) NOT NULL,
MiddelInitial NVARCHAR(10),
LastName NVARCHAR(128) NOT NULL,
DateOfBirth DATE NOT NULL
)
— Create Student table
CREATE TABLE Student
(
StudentId INT IDENTITY PRIMARY KEY,
PersonId INT REFERENCES Person (PersonId),
Email NVARCHAR(256)
)
— Create Course table
CREATE TABLE Course
(
CourseId INT IDENTITY PRIMARY KEY,
Name NVARCHAR(50) NOT NULL,
Teacher NVARCHAR(256) NOT NULL
)
— Create Credit table
CREATE TABLE Credit
(
StudentId INT REFERENCES Student (StudentId),
CourseId INT REFERENCES Course (CourseId),
Grade DECIMAL(5,2) CHECK (Grade <= 100.00),
Attempt TINYINT,
CONSTRAINT [UQ_studentgrades] UNIQUE CLUSTERED
(
StudentId, CourseId, Grade, Attempt
)
)
- Now, you have to expand the “Tables” node in the selected database in the “Object Explorer” window for viewing the tables you have created.
With so much data generated every day, cloud data management comes as an effective solution for data management. Check my previous article to know more about Cloud Data Management.
Bottom Line
Now, you can go ahead and start with the process of creating a database using Azure Portal and SSMS. Most important of all, you can learn to create a table as well as connect with the database with the above-mentioned information. It is very easy to follow step by step instructions to create and manage databases. In addition to the simplicity of the Azure portal and the SQL Server Management Studio (SMSS), Azure SQL Database also offers other simple tools for database management.
The SQL Server Data Tools in Visual Studio and Visual Studio Code can help in the flexible database management for enterprises. Most important of all, you can also consult with the SQL Server engineering team through DBA Stack Exchange, MSDN Forums, or Reddit discussions on SQL Server. Start right now to explore more information!