Microsoft Docs

Tutorial: Build a Sample App with Azure SQL Database

This tutorial guides you through building a simple web application that connects to and interacts with Azure SQL Database. We'll cover setting up your database, writing application code, and deploying your solution.

Prerequisites

• An Azure subscription. If you don't have one, you can create a free account.
• A local development environment configured with your preferred programming language and tools (e.g., Visual Studio Code, Visual Studio).
• Basic understanding of SQL and the programming language you choose to use (e.g., C#, Python, Node.js).

Step 1: Create an Azure SQL Database Instance

First, we need to set up a SQL Database instance in Azure. This will be the backend for our application.

1. Navigate to the Azure portal.
2. Search for "SQL databases" and select it.
3. Click "Create".
4. Select a resource group or create a new one.
5. Provide a database name (e.g., MySampleDB).
6. Choose a server. You can create a new logical server or use an existing one.
7. Configure server settings, including server admin login and password. Store these credentials securely.
8. Select a compute and storage configuration. For this tutorial, the basic tier is sufficient.
9. Review and create the database.

Step 2: Configure Firewall Rules

To allow your application to connect to the Azure SQL Database, you need to configure firewall rules.

1. Once your database is deployed, navigate to its overview page in the Azure portal.
2. Under "Settings", select "Firewalls and virtual networks".
3. Click "Add a client IP" to add your current IP address. This is convenient for local development. For production, consider more secure connectivity options like private endpoints.
4. Click "Save".

Step 3: Connect to the Database and Create a Table

Now, let's connect to the database and create a simple table to store our application data.

You can use tools like Azure Data Studio, SQL Server Management Studio (SSMS), or programmatically connect.

Using Azure Data Studio (Recommended)

1. Download and install Azure Data Studio.
2. Open Azure Data Studio and create a new connection.
3. Use the following connection details:
   • Server: Your logical server name (e.g., mysqldbserver.database.windows.net)
   • Authentication type: SQL Login
   • User name: Your server admin login
   • Password: Your server admin password
   • Database: Select your created database (e.g., MySampleDB)
4. Click "Connect".
5. Once connected, open a new query editor and run the following T-SQL script to create a TodoItems table:

CREATE TABLE TodoItems (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Description NVARCHAR(255) NOT NULL,
    IsCompleted BIT DEFAULT 0
);

INSERT INTO TodoItems (Description) VALUES ('Learn Azure SQL Database');
INSERT INTO TodoItems (Description) VALUES ('Build a sample app');
INSERT INTO TodoItems (Description) VALUES ('Deploy to Azure');
            

Step 4: Build Your Sample Application

This section provides a conceptual overview. Specific code examples depend on your chosen technology stack. We'll assume a simple .NET Core web API for demonstration.

Conceptual Steps:

• Set up your project: Create a new web application project using your preferred framework (e.g., .NET Core MVC/Web API, Node.js Express, Python Flask).
• Install database connector: Add the appropriate NuGet package (for .NET), npm package (for Node.js), or pip package (for Python) to connect to SQL Server. For .NET, this is typically Microsoft.Data.SqlClient.
• Configure connection string: Store your database connection string securely. Azure Key Vault is recommended for production. For local development, you can use a configuration file (e.g., appsettings.json in .NET). The connection string will look similar to:

  
  Server=tcp:mysqldbserver.database.windows.net,1433;Initial Catalog=MySampleDB;Persist Security Info=False;User ID={your_username};Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;
                      

• Implement data access logic: Write code to connect to the database, execute SQL queries (e.g., SELECT, INSERT, UPDATE, DELETE), and map results to your application's models.
• Create UI/API endpoints: Build the user interface or API endpoints that will interact with your data access layer.

Step 5: Run and Test Your Application

Run your application locally and test its functionality. Ensure you can add, retrieve, update, and delete data from your Azure SQL Database.

Next Steps