🗄️ Azure SQL Database Setup Guide

This folder contains all SQL scripts required to set up the Azure SQL Database for the Resource Management Dashboard.

📋 Files in This Folder

File	Purpose
`01_create_tables.sql`	Creates all database tables
`02_insert_default_data.sql`	Inserts default theme configuration
`03_create_admin_user.sql`	Creates default admin user
`04_sample_resources.sql`	Inserts sample resources (optional)
`backup_and_restore.sql`	Backup and restore commands

🚀 Quick Setup (Step-by-Step)

Step 1: Create Azure SQL Database

Login to Azure Portal: https://portal.azure.com
Search for "SQL databases"
Click "+ Create"
Fill in details:
- Resource Group: Create new or select existing
- Database Name: resource-dashboard-db (CHANGE ACCORDINGLY)
- Server: Create new
  - Server name: your-unique-server-name (CHANGE ACCORDINGLY)
  - Location: Southeast Asia (or your preferred region)
  - Authentication: SQL authentication
  - Admin login: dbadmin (CHANGE ACCORDINGLY)
  - Password: Strong password (CHANGE ACCORDINGLY and SAVE IT!)
- Compute + Storage: Basic or Standard tier
Networking:
- ✅ Allow Azure services
- ✅ Add current client IP
Click "Review + Create" > "Create"

⏱ Wait: 5-10 minutes for deployment

Step 2: Get Connection String

Go to your database resource
Click "Connection strings" (left menu)
Copy the ADO.NET connection string
Note it down - you'll need it for .env configuration

Format for PyMSSQL (Backend .env):

mssql+pymssql://USERNAME:PASSWORD@SERVER_NAME.database.windows.net:1433/DATABASE_NAME

Example (CHANGE ACCORDINGLY):

mssql+pymssql://dbadmin:MySecure@Pass123@resource-server.database.windows.net:1433/resource-dashboard-db

Step 3: Run SQL Scripts

Option A: Using Azure Portal Query Editor (Easiest)

Go to your database in Azure Portal
Click "Query editor" (left menu)
Login with:
- Authentication type: SQL authentication
- Login: dbadmin (or your admin username)
- Password: Your database password
PLEASE NOTE THAT, IN YOUR SQL SERVER FIREWALL, CLIENT IP AND BACKEND IP SHOULD BE ALLOWED, FOR PRACTISE PURPOSE ONLY
Run scripts in order:
- Copy content of 01_create_tables.sql → Paste → Click "Run"
- Copy content of 02_insert_default_data.sql → Paste → Click "Run"
- Copy content of 03_create_admin_user.sql → Paste → Click "Run"
- (Optional) Copy content of 04_sample_resources.sql → Paste → Click "Run"

✅ Done! Database is ready.

Option B: Using SQL Server Management Studio (SSMS)

Download SSMS: https://aka.ms/ssmsfullsetup
Connect to Azure SQL:
- Server name: your-server.database.windows.net
- Authentication: SQL Server Authentication
- Login: Your admin username
- Password: Your admin password
Open each .sql file and execute in order

Option C: Using Python Script (From Backend)

bash
# Make sure backend .env is configured with DATABASE_URL
cd backend
source venv/bin/activate  # or venv\Scripts\activate on Windows

# Run migration
python -c "from app.db.database import engine; from app.models.user import Base; Base.metadata.create_all(engine)"

Step 4: Verify Tables

Run this query in Query Editor:

sql
-- Check all tables
SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE';

Expected Output:

users
theme_config
resources

🔐 Default Admin User

After running 03_create_admin_user.sql, you'll have:

Email: admin@example.com (CHANGE ACCORDINGLY in the SQL file)
Password: Admin@123 (CHANGE ACCORDINGLY in the SQL file)
Role: admin
Protected: Yes (cannot be deleted)

⚠️ IMPORTANT: Change these credentials in production!

🔄 Backup and Restore

Backup Database

Azure Portal:

Go to SQL database
Click "Export"
Choose storage account
Enter admin credentials
Click "OK"

Automated Backups:
Azure SQL automatically backs up databases. You can restore to any point in last 7-35 days.

Restore Database

Go to SQL database
Click "Restore"
Select restore point
Enter new database name
Click "OK"

🔍 Troubleshooting

Cannot connect to database

Check:

✅ Firewall rules include your IP
✅ Connection string is correct
✅ Username/password are correct
✅ Server name has .database.windows.net

Add IP to Firewall:

SQL Database > Networking
Add your current IP address
Save

Query timeout

Solution:

Increase connection timeout in connection string
Check database tier (Basic might be slow)
Upgrade to Standard tier

Permission denied

Check:

Using admin credentials?
User has proper permissions?

📊 Database Schema

Users Table

Stores user accounts
Includes authentication info
Role-based access control

Resources Table

Infrastructure resources
Performance metrics
User ownership

Theme Config Table

User theme preferences
Color schemes
Dark/light mode settings

🔗 Connection String Examples

Development (Local):

env
DATABASE_URL=sqlite:///./resource_dashboard.db

Production (Azure SQL):

env
# CHANGE ACCORDINGLY with your actual credentials
DATABASE_URL=mssql+pymssql://dbadmin:YourPassword@your-server.database.windows.net:1433/resource-dashboard-db

💡 Architecture Benefits

✅ All About Our Complete Project

Frontend - React + Vite (Static build ready)
Backend - Python FastAPI with JWT authentication
Database - Azure SQL Database

📁 Project Structure

project/
├── backend/                    # Python FastAPI Backend
│   ├── app/
│   │   ├── api/               # API endpoints (auth, users, theme)
│   │   ├── core/              # Config & security
│   │   ├── db/                # Database setup
│   │   ├── models/            # SQLAlchemy models
│   │   ├── schemas/           # Pydantic schemas
│   │   └── main.py           # FastAPI application
│   ├── requirements.txt       # Python dependencies
│   ├── azure_sql_schema.sql  # Database schema
│   └── README.md             # Backend documentation
│
├── client/                    # React Frontend
│   ├── src/
│   │   ├── components/       # UI components
│   │   ├── pages/           # Page components
│   │   ├── contexts/        # Auth context (updated for Python backend)
│   │   ├── config/          # API configuration
│   │   └── lib/             # API client (updated)
│   └── .env.example         # Frontend env vars
│
└── DEPLOYMENT_GUIDE.md      # Complete deployment instructions

🔧 What Included

Backend

✅ Python FastAPI framework instead of Node.js
✅ Azure SQL Database instead of Supabase Postgres
✅ JWT Authentication with python-jose
✅ SQLAlchemy ORM for database operations
✅ Pydantic for request/response validation

Frontend

✅ Auth Context to use Python backend API
✅ API Client to call FastAPI endpoints
✅ API Configuration for backend URL

Database

✅ Azure SQL Schema created
✅ Users table with email, password, roles
✅ Theme config table for customization
✅ Default theme values inserted

🚀 OUR MAIN IMPORTANT STEPS

1. Setup Azure SQL Database

bash
# Azure Portal mein:
1. Create SQL Database
2. Note: server name, database name, username, password
3. Run: backend/azure_sql_schema.sql
4. Configure firewall rules for VM IPs

2. Deploy Backend (VM 2)

bash
# Backend VM mein:
cd backend
pip install -r requirements.txt

# .env file configure karo:
AZURE_SQL_SERVER=your-server.database.windows.net
AZURE_SQL_DATABASE=your-database
AZURE_SQL_USERNAME=your-username
AZURE_SQL_PASSWORD=your-password
SECRET_KEY=your-secret-key

# Run backend:
python run.py
# Production: uvicorn app.main:app --host 0.0.0.0 --port 8000

3. Deploy Frontend (VM 1)

bash
# Frontend VM mein:
cd client

# Update API URL in .env:
VITE_API_URL=http://backend-vm-ip:8000

# Build frontend:
npm install
npm run build

# Deploy to nginx:
sudo cp -r dist/public/* /var/www/html/

🔐 Security Notes

1. Environment Variables

Backend: Use .env file (see backend/.env.example)
Frontend: Use client/.env (see client/.env.example)

2. Production Checklist

Change SECRET_KEY to random secure value
Enable HTTPS with SSL certificates
Configure Azure SQL firewall rules
Set up Azure Key Vault for secrets
Enable CORS only for your frontend domain
Use strong passwords for database

📋 Features Implemented

Authentication

✅ User signup with email/password
✅ User login with JWT tokens
✅ Role-based access control (admin/user)
✅ Protected routes

User Management

✅ Get current user profile
✅ Update user profile (name, bio, avatar)
✅ Admin: View all users
✅ Admin: View user by ID

Theme Configuration

✅ Get theme configuration
✅ Admin: Update theme colors
✅ Default theme values

🧪 Testing

Test Backend API

bash
# Health check
curl http://localhost:8000/health

# Get theme config (public)
curl http://localhost:8000/api/theme

# Signup
curl -X POST http://localhost:8000/api/auth/signup \
  -H "Content-Type: application/json" \
  -d '{"email":"test@example.com","password":"password123"}'

# Login
curl -X POST http://localhost:8000/api/auth/login \
  -d "email=test@example.com&password=password123"

Test Frontend

bash
# Local development:
npm run dev
# Open: http://localhost:5000

# Production build:
npm run build
# Serve dist/public/ with nginx

Resource Management Dashboard • Azure SQL Database Setup Guide
Last Updated: November 26, 2025 • Built with ❤️ by Ritesh Sharma