Tutorial: Design a relational database in a single database within Azure SQL Database using SSMS
Azure SQL database is a relational database-as-a-service (DBaaS) in the Microsoft Cloud (Azure). In this tutorial, you learn how to use the Azure portal and SQL Server Management Studio (SSMS) to:
- Create a single database using the Azure portal*
- Set up a server-level IP firewall rule using the Azure portal
- Connect to the database with SSMS
- Create tables with SSMS
- Bulk load data with BCP
- Query data with SSMS
*If you don't have an Azure subscription, create a free account before you begin.
For the purpose of this tutorial, we are using a single database. You could also use a pooled database in an elastic pool or an instance database in a managed instance. For connectivity to a managed instance, see these managed instance quickstarts: Quickstart: Configure Azure VM to connect to an Azure SQL Database Managed Instance and Quickstart: Configure a point-to-site connection to an Azure SQL Database Managed Instance from on-premises.
To complete this tutorial, make sure you've installed:
Sign in to the Azure portal
Sign in to the Azure portal.
Create a blank single database
Follow these steps to create a blank single database.
Click Create a resource in the upper left-hand corner of the Azure portal.
On the New page, select Databases in the Azure Marketplace section, and then click SQL Database in the Featured section.
Fill out the SQL Database form with the following information, as shown on the preceding image:
Setting Suggested value Description Database name yourDatabase For valid database names, see Database identifiers. Subscription yourSubscription For details about your subscriptions, see Subscriptions. Resource group yourResourceGroup For valid resource group names, see Naming rules and restrictions. Select source Blank database Specifies that a blank database should be created.
Click Server to use an existing database server or create and configure a new database server. Either select an existing server or click Create a new server and fill out the New server form with the following information:
Setting Suggested value Description Server name Any globally unique name For valid server names, see Naming rules and restrictions. Server admin login Any valid name For valid login names, see Database identifiers. Password Any valid password Your password must have at least eight characters and must use characters from three of the following categories: upper case characters, lower case characters, numbers, and non-alphanumeric characters. Location Any valid location For information about regions, see Azure Regions.
Click Pricing tier to specify the service tier, the number of DTUs or vCores, and the amount of storage. You may explore the options for the number of DTUs/vCores and storage that is available to you for each service tier.
After selecting the service tier, the number of DTUs or vCores, and the amount of storage, click Apply.
Enter a Collation for the blank database (for this tutorial, use the default value). For more information about collations, see Collations
Now that you've completed the SQL Database form, click Create to provision the single database. This step may take a few minutes.
On the toolbar, click Notifications to monitor the deployment process.
Create a server-level IP firewall rule
The SQL Database service creates an IP firewall at the server-level. This firewall prevents external applications and tools from connecting to the server and any databases on the server unless a firewall rule allows their IP through the firewall. To enable external connectivity to your single database, you must first add an IP firewall rule for your IP address (or IP address range). Follow these steps to create a SQL Database server-level IP firewall rule.
The SQL Database service communicates over port 1433. If you are trying to connect to this service from within a corporate network, outbound traffic over port 1433 may not be allowed by your network's firewall. If so, you cannot connect to your single database unless your administrator opens port 1433.
After the deployment completes, click SQL databases from the left-hand menu and then click yourDatabase on the SQL databases page. The overview page for your database opens, showing you the fully qualified Server name (such as yourserver.database.windows.net) and provides options for further configuration.
Copy this fully qualified server name for use to connect to your server and databases from SQL Server Management Studio.
Click Set server firewall on the toolbar. The Firewall settings page for the SQL Database server opens.
Click Add client IP on the toolbar to add your current IP address to a new IP firewall rule. An IP firewall rule can open port 1433 for a single IP address or a range of IP addresses.
Click Save. A server-level IP firewall rule is created for your current IP address opening port 1433 on the SQL Database server.
Click OK and then close the Firewall settings page.
Your IP address can now pass through the IP firewall. You can now connect to your single database using SQL Server Management Studio or another tool of your choice. Be sure to use the server admin account you created previously.
By default, access through the SQL Database IP firewall is enabled for all Azure services. Click OFF on this page to disable for all Azure services.
Connect to the database
Use SQL Server Management Studio to establish a connection to your single database.
Open SQL Server Management Studio.
In the Connect to Server dialog box, enter the following information:
Setting Suggested value Description Server type Database engine This value is required. Server name The fully qualified server name For example, yourserver.database.windows.net. Authentication SQL Server Authentication SQL Authentication is the only authentication type that we've configured in this tutorial. Login The server admin account The account that you specified when you created the server. Password The password for your server admin account The password that you specified when you created the server.
Click Options in the Connect to server dialog box. In the Connect to database section, enter yourDatabase to connect to this database.
Click Connect. The Object Explorer window opens in SSMS.
In Object Explorer, expand Databases and then expand yourDatabase to view the objects in the sample database.
Create tables in your database
Create a database schema with four tables that model a student management system for universities using Transact-SQL:
The following diagram shows how these tables are related to each other. Some of these tables reference columns in other tables. For example, the Student table references the PersonId column of the Person table. Study the diagram to understand how the tables in this tutorial are related to one another. For an in-depth look at how to create effective database tables, see Create effective database tables. For information about choosing data types, see Data types.
You can also use the table designer in SQL Server Management Studio to create and design your tables.
In Object Explorer, right-click yourDatabase and select New Query. A blank query window opens that is connected to your database.
In the query window, execute the following query to create four tables in your database:
-- 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 ) )
Expand the Tables node under yourDatabase in the Object Explorer to see the tables you created.
Load data into the tables
Create a folder called sampleData in your Downloads folder to store sample data for your database.
Right-click the following links and save them into the sampleData folder.
Open a command prompt window and navigate to the sampleData folder.
Execute the following commands to insert sample data into the tables replacing the values for server, database, user, and password with the values for your environment.
bcp Course in SampleCourseData -S <server>.database.windows.net -d <database> -U <user> -P <password> -q -c -t "," bcp Person in SamplePersonData -S <server>.database.windows.net -d <database> -U <user> -P <password> -q -c -t "," bcp Student in SampleStudentData -S <server>.database.windows.net -d <database> -U <user> -P <password> -q -c -t "," bcp Credit in SampleCreditData -S <server>.database.windows.net -d <database> -U <user> -P <password> -q -c -t ","
You have now loaded sample data into the tables you created earlier.
Execute the following queries to retrieve information from the database tables. See Write SQL queries to learn more about writing SQL queries. The first query joins all four tables to find the students taught by 'Dominick Pope' who have a grade higher than 75%. The second query joins all four tables and finds the courses in which 'Noe Coleman' has ever enrolled.
In a SQL Server Management Studio query window, execute the following query:
-- Find the students taught by Dominick Pope who have a grade higher than 75% SELECT person.FirstName, person.LastName, course.Name, credit.Grade FROM Person AS person INNER JOIN Student AS student ON person.PersonId = student.PersonId INNER JOIN Credit AS credit ON student.StudentId = credit.StudentId INNER JOIN Course AS course ON credit.CourseId = course.courseId WHERE course.Teacher = 'Dominick Pope' AND Grade > 75
In a query window, execute the following query:
-- Find all the courses in which Noe Coleman has ever enrolled SELECT course.Name, course.Teacher, credit.Grade FROM Course AS course INNER JOIN Credit AS credit ON credit.CourseId = course.CourseId INNER JOIN Student AS student ON student.StudentId = credit.StudentId INNER JOIN Person AS person ON person.PersonId = student.PersonId WHERE person.FirstName = 'Noe' AND person.LastName = 'Coleman'
In this tutorial, you learned many basic database tasks. You learned how to:
- Create a single database
- Set up a server-level IP firewall rule
- Connect to the database with SQL Server Management Studio (SSMS)
- Create tables
- Bulk load data
- Query that data
Advance to the next tutorial to learn about designing a database using Visual Studio and C#.