design-first-database-tutorial.md

title	description	author	ms.author	ms.reviewer	ms.date	ms.service	ms.subservice	ms.topic
Tutorial: Design your first relational database using SSMS	Learn to design your first relational database in Azure SQL Database using SQL Server Management Studio.	dzsquared	drskwier	wiassaf, mathoma, v-masebo	01/26/2024	azure-sql-database	development	tutorial

Tutorial: Design a relational database in Azure SQL Database

[!INCLUDE appliesto-sqldb]

Azure SQL Database is a relational database-as-a-service (DBaaS) in the Microsoft Azure. In this tutorial, you learn how to:

[!div class="checklist"]

• Connect to the database
• Create tables with T-SQL commands
• Bulk load data with BCP
• Query data with T-SQL commands

Note

For the purpose of this tutorial, we are using Azure SQL Database. You could also use a pooled database in an elastic pool or a SQL Managed Instance. For connectivity to a SQL Managed Instance, see these SQL Managed Instance quickstarts: Quickstart: Configure Azure VM to connect to an Azure SQL Managed Instance and Quickstart: Configure a point-to-site connection to an Azure SQL Managed Instance from on-premises.

Prerequisites

• Use SQL Server Management Studio (latest version) or the Azure portal Query Editor for Azure SQL Database.
  • You can also follow this same tutorial using Azure Data Studio (ADS).
• BCP and SQLCMD (latest version).
• If you don't have an Azure subscription, create a free account before you begin.
• If you don't already have an Azure SQL Database created, visit Quickstart: Create a single database. Look for the option to use your offer to Deploy Azure SQL Database for free.

Sign in to the Azure portal

Sign in to the Azure portal.

Create a server-level IP firewall rule

Azure SQL Database 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 database, you must first add an IP firewall rule for your IP address (or IP address range). Follow these steps to create a server-level IP firewall rule.

Important

Azure SQL Database 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 database unless your administrator opens port 1433.

1. After the deployment completes, select SQL databases from the Azure portal menu or search for and select SQL databases from any page.

2. Select yourDatabase on the SQL databases page. The overview page for your database opens, showing you the fully qualified Server name (such as contosodatabaseserver01.database.windows.net) and provides options for further configuration.

   :::image type="content" source="./media/design-first-database-tutorial/server-name.png" alt-text="Screenshot of the Azure portal, database overview page with the server name highlighted.":::

3. Copy this fully qualified server name for use to connect to your server and databases from SQL Server Management Studio.

4. Select Networking under Settings. Choose the Public Access tab, and then select Selected networks under Public network access to display the Firewall rules section.

   :::image type="content" source="./media/design-first-database-tutorial/server-firewall-rule.png" alt-text="Screenshot of the Azure portal, networking page, showing where to set the server-level IP firewall rule.":::

5. Select Add your client IPv4 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.

6. Select Save. A server-level IP firewall rule is created for your current IP address opening port 1433 on the server.

7. Select OK and then close the Firewall settings page.

Your IP address can now pass through the IP firewall. You can now connect to your database using SQL Server Management Studio or another tool of your choice. Be sure to use the server admin account you created previously.

Important

By default, access through the SQL Database IP firewall is enabled for all Azure services. Select OFF on this page to disable for all Azure services.

Connect to the database

Azure SQL databases exist inside logical SQL servers. Can connect to the logical SQL server's master using a login, then connect to your database. Or, using a contained user, you can connect directly to your Azure SQL database.

SQL Server Management Studio

Use SQL Server Management Studio to connect to your Azure SQL database.

1. Open SQL Server Management Studio.

2. In the Connect to Server dialog box, enter the following information. Leave other options as default.

   Setting	Suggested value	Description
   Server type	Database engine	This value is required.
   Server name	The fully qualified Azure SQL Database logical server name	For example, your_logical_azure_sql_server.database.windows.net.
   Authentication	SQL Server Authentication	Use SQL Server Authentication to enter a user name and password.
   	Microsoft Entra authentication	To connect using Microsoft Entra ID, if you're the Microsoft Entra server admin, choose Microsoft Entra MFA. For more information, see Configure and manage Microsoft Entra authentication with Azure SQL.
   Login	The server admin account	If using SQL Server Authentication, the account that you specified when you created the server.
   Password	The password for your server admin account	If using SQL Server Authentication, the password that you specified when you created the server.

   :::image type="content" source="media\design-first-database-tutorial\connect.png" alt-text="Screenshot of the Connect to Server dialog box in SQL Server Management Studio (SSMS).":::

3. Select Options in the Connect to server dialog box. In the Connect to database section, enter yourDatabase to connect to this database.

   :::image type="content" source="media\design-first-database-tutorial\options-connect-to-db.png" alt-text="Screenshot of the options tab of the connect to server dialog box in SQL Server Management Studio (SSMS).":::

4. Select Connect. The Object Explorer window opens in SSMS.

5. In Object Explorer, expand Databases and then expand yourDatabase to view the objects in the sample database.

   :::image type="content" source="media\design-first-database-tutorial\connected.png" alt-text="Screenshot of SQL Server Management Studio (SSMS) showing database objects in object explorer.":::

6. In Object Explorer, right-click yourDatabase and select New Query. A blank query window opens that is connected to your database.

Azure portal Query editor

Use the Azure portal Query editor for Azure SQL Database to connect to your Azure SQL database.

1. Navigate to your SQL database in the Azure portal. For example, visit your Azure SQL dashboard.

2. On your SQL database Overview page in the Azure portal, select Query editor (preview) from the left menu.

   :::image type="content" source="media\design-first-database-tutorial\find-query-editor.png" alt-text="Screenshot that shows selecting query editor.":::

3. On the sign-in screen under Welcome to SQL Database Query Editor, provide credentials to connect to the database. You can connect using SQL or Microsoft Entra authentication.

   • To connect with SQL authentication, under SQL server authentication, enter a Login and Password for a user that has access to the database, and then select OK. You can always use the login and password for the server admin.

     :::image type="content" source="media\design-first-database-tutorial\login-menu.png" alt-text="Screenshot from the Azure portal showing sign-in with SQL authentication." lightbox="media\design-first-database-tutorial\login-menu.png":::

   • To connect using Microsoft Entra ID, if you're the Microsoft Entra server admin, select Continue as <your user or group ID>. If sign-in is unsuccessful, try refreshing the page.

     :::image type="content" source="media\design-first-database-tutorial\query-editor-entra-login.png" alt-text="Screenshot from the Azure portal showing sign-in with Microsoft Entra authentication." lightbox="media\design-first-database-tutorial\query-editor-entra-login.png":::

4. A new query window opens, ready to accept T-SQL commands. In the object explorer, you can expand folders for Tables, Views, and Stored procedures.

---

Create tables in your database

Create four tables that model a student management system for universities using Transact-SQL:

• Person
• Course
• Student
• Credit

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 normalized database tables, see Designing a Normalized Database. For information about choosing data types, see Data types. By default, tables are created in the default dbo schema, meaning the two-part name of a table will be dbo.Person, for example.

Note

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

:::image type="content" source="media\design-first-database-tutorial\tutorial-database-tables.png" alt-text="Screenshot of the table designer in SQL Server Management Studio (SSMS) showing the table relationships.":::

1. In the query window, execute the following T-SQL query to create four tables in your database:

   -- Create Person tableCREATETABLEPerson ( PersonId INT IDENTITY PRIMARY KEY, FirstName NVARCHAR(128) NOT NULL, MiddelInitial NVARCHAR(10), LastName NVARCHAR(128) NOT NULL, DateOfBirth DATENOT NULL ) -- Create Student tableCREATETABLEStudent ( StudentId INT IDENTITY PRIMARY KEY, PersonId INTREFERENCES Person (PersonId), Email NVARCHAR(256) ) -- Create Course tableCREATETABLECourse ( CourseId INT IDENTITY PRIMARY KEY, Name NVARCHAR(50) NOT NULL, Teacher NVARCHAR(256) NOT NULL ) -- Create Credit tableCREATETABLECredit ( StudentId INTREFERENCES Student (StudentId), CourseId INTREFERENCES Course (CourseId), Grade DECIMAL(5,2) CHECK (Grade <=100.00), Attempt TINYINT, CONSTRAINT [UQ_studentgrades] UNIQUE CLUSTERED ( StudentId, CourseId, Grade, Attempt ) )

   :::image type="content" source="media\design-first-database-tutorial\create-tables.png" alt-text="Screenshot from SSMS showing the create tables script has been successfully executed." lightbox="media\design-first-database-tutorial\create-tables.png":::

2. Expand the Tables node under yourDatabase in the Object Explorer to see the four new tables you created.

Load data into the tables

1. Create a folder called sampleData in your local workstation Downloads folder to store sample data for your database. For example, c:\Users\<your user name>\Downloads.

2. Right-click the following links and save them into the sampleData folder.

   • SampleCourseData
   • SamplePersonData
   • SampleStudentData
   • SampleCreditData

3. Open a new Windows command prompt window and navigate to the sampleData folder. For example, cd c:\Users\<your user name>\Downloads.

4. Execute the following bcp 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.

Query data

Execute the following T-SQL queries to retrieve information from the database tables.

This first query joins all four tables to find the students taught by 'Dominick Pope' who have a grade higher than 75%. In a query window, execute the following T-SQL query:

-- Find the students taught by Dominick Pope who have a grade higher than 75%SELECTperson.FirstName, person.LastName, course.Name, credit.GradeFROM Person AS person INNER JOIN Student AS student ONperson.PersonId=student.PersonIdINNER JOIN Credit AS credit ONstudent.StudentId=credit.StudentIdINNER JOIN Course AS course ONcredit.CourseId=course.courseIdWHEREcourse.Teacher='Dominick Pope'AND Grade >75;

This query joins all four tables and finds the courses in which 'Noe Coleman' has ever enrolled. In a query window, execute the following T-SQL query:

-- Find all the courses in which Noe Coleman has ever enrolledSELECTcourse.Name, course.Teacher, credit.GradeFROM Course AS course INNER JOIN Credit AS credit ONcredit.CourseId=course.CourseIdINNER JOIN Student AS student ONstudent.StudentId=credit.StudentIdINNER JOIN Person AS person ONperson.PersonId=student.PersonIdWHEREperson.FirstName='Noe'ANDperson.LastName='Coleman';

Tip

To learn more about writing SQL queries, visit Tutorial: Write Transact-SQL statements.

Related content

• Tutorial: Design a relational database in Azure SQL Database using Azure Data Studio (ADS)
• Deploy Azure SQL Database for free
• What's new in Azure SQL Database?
• Configure and manage content reference - Azure SQL Database
• Plan and manage costs for Azure SQL Database

Tip

Ready to start developing an .NET application? This free Learn module shows you how to Develop and configure an ASP.NET application that queries an Azure SQL Database, including the creation of a simple database.

Next step

Advance to the next tutorial to learn about designing a database using Visual Studio and C#.

[!div class="nextstepaction"] Design a relational database within Azure SQL Database C# and ADO.NET