Database Cloud Deployment Project

Designed a normalized inventory schema with five tables: Products, Categories, Suppliers, Inventory, and OrderHistory. Applied primary/foreign keys, constraints, and indexes.

-- Schema: InventoryDB
CREATE TABLE Categories (
    category_id   INT           IDENTITY(1,1) PRIMARY KEY,
    name          NVARCHAR(100) NOT NULL UNIQUE,
    description   NVARCHAR(500)
);

CREATE TABLE Suppliers (
    supplier_id   INT           IDENTITY(1,1) PRIMARY KEY,
    name          NVARCHAR(200) NOT NULL,
    contact_email NVARCHAR(200),
    phone         NVARCHAR(20),
    country       NVARCHAR(100) NOT NULL,
    CONSTRAINT chk_email CHECK (contact_email LIKE '%@%')
);

CREATE TABLE Products (
    product_id    INT           IDENTITY(1,1) PRIMARY KEY,
    sku           NVARCHAR(50)  NOT NULL UNIQUE,
    name          NVARCHAR(200) NOT NULL,
    category_id   INT           NOT NULL REFERENCES Categories(category_id),
    supplier_id   INT           NOT NULL REFERENCES Suppliers(supplier_id),
    unit_price    DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
    created_at    DATETIME2     DEFAULT GETUTCDATE()
);

CREATE TABLE Inventory (
    inventory_id  INT  IDENTITY(1,1) PRIMARY KEY,
    product_id    INT  NOT NULL REFERENCES Products(product_id),
    warehouse     NVARCHAR(100) NOT NULL,
    quantity      INT           NOT NULL CHECK (quantity >= 0),
    last_updated  DATETIME2     DEFAULT GETUTCDATE(),
    CONSTRAINT uq_product_warehouse UNIQUE (product_id, warehouse)
);

-- Index for frequent inventory lookups by warehouse
CREATE NONCLUSTERED INDEX IX_Inventory_Warehouse
    ON Inventory(warehouse) INCLUDE (product_id, quantity);

Used Azure CLI to provision the logical SQL Server and the database with the General Purpose tier, configured geo-redundant backups, and disabled public network access.

# Variables
RG="rg-inventory-db"
LOCATION="canadacentral"
SQL_SERVER="sql-inventory-lab"
DB_NAME="InventoryDB"
ADMIN_USER="sqladmin"

# Create resource group
az group create --name $RG --location $LOCATION

# Create Azure SQL Server
az sql server create \
  --name $SQL_SERVER \
  --resource-group $RG \
  --location $LOCATION \
  --admin-user $ADMIN_USER \
  --admin-password "$(openssl rand -base64 32)"

# Create database — General Purpose, 4 vCores
az sql db create \
  --resource-group $RG \
  --server $SQL_SERVER \
  --name $DB_NAME \
  --service-objective GP_Gen5_4 \
  --backup-storage-redundancy Geo \
  --zone-redundant false

# Disable public network access
az sql server update \
  --name $SQL_SERVER \
  --resource-group $RG \
  --set publicNetworkAccess=Disabled

Created a private endpoint in the application VNet so the application VM connects to the database over a private IP, bypassing the public internet entirely.

# Create Private Endpoint for Azure SQL
VNET_NAME="vnet-app"
SUBNET_NAME="snet-db-private"
PE_NAME="pe-inventory-sql"
SQL_ID=$(az sql server show --name $SQL_SERVER --resource-group $RG --query id -o tsv)

# Disable private endpoint network policies on subnet
az network vnet subnet update \
  --name $SUBNET_NAME \
  --vnet-name $VNET_NAME \
  --resource-group $RG \
  --disable-private-endpoint-network-policies true

# Create private endpoint
az network private-endpoint create \
  --name $PE_NAME \
  --resource-group $RG \
  --vnet-name $VNET_NAME \
  --subnet $SUBNET_NAME \
  --private-connection-resource-id $SQL_ID \
  --group-id sqlServer \
  --connection-name "conn-inventory-sql"

# Create Private DNS zone for SQL
az network private-dns zone create \
  --resource-group $RG \
  --name "privatelink.database.windows.net"

az network private-dns link vnet create \
  --resource-group $RG \
  --zone-name "privatelink.database.windows.net" \
  --name "dns-link-inventory" \
  --virtual-network $VNET_NAME \
  --registration-enabled false

Implemented Row-Level Security (RLS) so warehouse managers can only see inventory records for their assigned warehouse, while the DBA role has full visibility.

-- Create application roles
CREATE ROLE warehouse_manager;
CREATE ROLE inventory_analyst;
CREATE ROLE dba_role;

-- Create a filter predicate function
CREATE FUNCTION dbo.fn_warehouse_filter(@warehouse NVARCHAR(100))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS result
WHERE @warehouse = USER_NAME()  -- username matches warehouse name
   OR IS_MEMBER('dba_role') = 1
   OR IS_MEMBER('inventory_analyst') = 1;

-- Create RLS security policy
CREATE SECURITY POLICY InventoryFilter
ADD FILTER PREDICATE dbo.fn_warehouse_filter(warehouse)
ON dbo.Inventory
WITH (STATE = ON);

-- Assign user to warehouse role
-- Example: user 'warehouse_east' can only see EAST warehouse rows
CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;
ALTER ROLE warehouse_manager ADD MEMBER [[email protected]];
-- The username '[email protected]' maps to warehouse 'warehouse_east'
-- via the filter predicate

Enabled Azure Defender for SQL for vulnerability scanning and threat detection, configured audit logs to a storage account, and set up alerts for suspicious login activity.

# Enable Azure Defender for SQL
az sql server advanced-threat-protection-setting update \
  --resource-group $RG \
  --server $SQL_SERVER \
  --state Enabled

# Enable audit logging to storage account
STORAGE_ID=$(az storage account show \
  --name "stinventoryaudit" \
  --resource-group $RG --query id -o tsv)

az sql server audit-policy update \
  --resource-group $RG \
  --server $SQL_SERVER \
  --state Enabled \
  --storage-account "stinventoryaudit" \
  --retention-days 90

# Verify connectivity from app VM
sqlcmd -S ${SQL_SERVER}.privatelink.database.windows.net \
       -d $DB_NAME \
       -G \  # Use AAD authentication
       -Q "SELECT @@VERSION; SELECT TOP 5 name FROM sys.tables;"