We have created a single database in Azure SQL. While creating Single Database, there were three options –

• to start with blank database,
• OR to restore the database
• OR to create sample database.

In previous article, we started with simple database. In this article we will see how to create blank database. Then we can use SQL Server Management Studio (SSMS) to create the database.

Create Single Database in Azure SQL

You can refer to the one of my previous articles to create the single database in Azure SQL for detailed steps. Below are snapshots which will help you to know what inputs I have provided while creating single database. The important inputs, I have highlighted in yellow.

Basics

On first screen, you need to provide database name, database server name, admin username and admin password. You need to remember these details as you will need these details to login using SSMS.

Networking

On Networking step, select public access. Then enable next two toggle buttons.

First toggle button is to enable access of Azure services to the database. This is not required for this sample. But this is required if, let’s say, you have a web application hosted as App Service, then you need this to be toggled on. In this article, we will just connect with SSMS, hence it is not mandatory.

The second toggle button is for adding IP firewall rule. For accessing database you need to whitelist the IP addresses. If you are going to create Azure SQL database from one machine and you are going to access it from same machine, then you must toggle this on. If you want to use from any other machines, you will need to add IP firewall rules. This part is explained in my previous article about Single Database.

Addition settings

On Next screen, you need to ensure that you select None as data source to create blank database.

You can add Tags if you want on next step. Then you can click on Create button. The database will get created in few seconds.

Connect with Management Studio

For connecting, you need to enter database server name, username and password.

When you open SSMS, below popup comes. You need to enter below details:

• Server Type: Database Engine
• Server Name: <db-server-name>.database.windows.net
• Authentication: SQL Server Authentication
• Login: admin username you entered during creation of Single Database
• Password: password of admin user account

Then you can click on Options button to specify the database name.

When you click on Options button, the popup expands and it shows additional options. Then you can click on connection properties tab and enter the database name.

Creating Database and Tables

Once you are connected in SSMS, you should be able to see the database in database explorer view. Right click on database and select New Query window. Then you can then copy the SQL Script from below snippet to new query window and run it from the SSMS.

This script creates basic tables for managing students information at universities. Couple of table to store student’s information and couple tables to store the credits information about students.

You can also use the table designer in SQL Server Management Studio to create and design your tables.

-- 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
    )
)

I hope you enjoyed this article. Let me know your thoughts.