Creating New SQL Database And Table With Azure

Introduction

In this article, we will see in detail about how to create our first SQL Server database in Azure and creating a table for Azure SQL database from our SQL Server Management Studio.

Prerequisites

• Azure Account: If you don’t have free account, you can create from this link.
  
• SQL Server 2014 or above.

Create new SQL Database on Azure

Using your Azure account, login to Azure site. From the dashboard page on the left side menu, we can see SQL databases. Click menu to create our first SQL database in Azure.



We can see the Add icon at the top. To create our fist SQL database, again click Add icon.



Here, we give our new database name and select or create our resource group.



Creating SQL Server on Azure

If we don’t have any SQL Server created, first we need to create a new SQL Server to create our database.

Here, we give our new Server name, login ID and Password for login to our Azure SQL Server.



Once our Server was created, select the newly added SQL Server to create our database.



Click Create button at the bottom to create our new database on the selected Server. Now, we can see a confirmation message as Validation Successful.


We can see now our new database has been created.



We can see all the details for newly created database from the database dashboard page.



To know our Azure SQL connection, we can click on the “Show database connection strings”. We can see the connection string for ADO.NET, ODBC, PHP and JDBC. We can use this connection string in our technology Applications to connect Azure databases. For example, to work with Microsoft.NET, we can select ADO.NET for working with JSP or Java, we can use JDBC or ODBC and to work with PHP Application, we can use the PHP connection strings.



Connecting Azure SQL Serve from Local SQL Server

Now, we have created our SQL database in Azure. To create tables and insert sample records, we will use SQL Server Management studio to connect to our Azure Server.

We need to give our Azure SQL Server name with our given ID and password.



When we try to connect for the first time, we may get the error, as shown below-



This error is due to the Firewall rule, which is not set by the selected Azure Server.

Setting Azure SQL Server firewall rule

You can find “Set server Firewall” at the top. Click to set our Firewall rule.



Click “Add Client IP” to set our new Firewall rule.



Click save to add our new Firewall Setting


After saving the new Firewall setting, now again connect to our Azure SQL Server from our local SQL Server Management Studio.




Creating Tables for Azure SQL Database

Now, we have connected our Azure SQL Server. Let’s create tables and insert record in our Azure database.

In Query Analyzer, run Create SQL script to create our tables, which is given below.

IF EXISTS ( SELECT [name] FROM sys.tables WHERE [name] = 'HotelMaster' )
DROP TABLE HotelMaster
GO

CREATE TABLE HotelMaster
(
RoomID int identity(1,1),
RoomNo VARCHAR(100) NOT NULL ,
RoomType VARCHAR(100) NOT NULL ,
Prize VARCHAR(100) NOT NULL
CONSTRAINT [PK_HotelMaster] PRIMARY KEY CLUSTERED
(
RoomID ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Insert into HotelMaster(RoomNo,RoomType,Prize) Values('101','Single','50$')
Insert into HotelMaster(RoomNo,RoomType,Prize) Values('102','Double','80$')

select * from HotelMaster


IF EXISTS ( SELECT [name] FROM sys.tables WHERE [name] = 'RoomBooking' )
DROP TABLE RoomBooking
GO

CREATE TABLE RoomBooking
(
BookingID int identity(1,1),
RoomID int ,
BookedDateFR VARCHAR(20) NOT NULL ,
BookedDateTO VARCHAR(20) NOT NULL ,
BookingStatus VARCHAR(100) NOT NULL,
PaymentStatus VARCHAR(100) NOT NULL,
AdvancePayed VARCHAR(100) NOT NULL,
TotalAmountPayed VARCHAR(100) NOT NULL,
CONSTRAINT [PK_RoomBooking] PRIMARY KEY CLUSTERED
(
[BookingID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

select * from RoomBooking

Now, we can see our new tables have been created in Azure SQL database.