# MS SQL Server Setup for Weather Connect

This directory contains setup scripts for configuring Microsoft SQL Server to work with the Weather Connect application. The schema mirrors the Oracle implementation with a two-table structure.

## Prerequisites

- Microsoft SQL Server 2017 or later
- SQL Server Management Studio (SSMS) or Azure Data Studio (optional but recommended)
- Appropriate permissions to create databases and users
- For Always On Availability Groups: SQL Server Enterprise Edition

## Database Schema Overview

The database consists of two tables:

1. **weather_data** - Main table storing weather measurements
   - `id` (BIGINT, IDENTITY, PRIMARY KEY)
   - `timestamp` (DATETIME2(6))
   - `topic` (NVARCHAR(255))
   - `location` (NVARCHAR(100))
   - `temperature` (DECIMAL(5,2))
   - `humidity` (DECIMAL(5,2))
   - `pressure` (DECIMAL(7,2))
   - `wind_speed` (DECIMAL(5,2))
   - `wind_direction` (DECIMAL(5,2))
   - `rainfall` (DECIMAL(6,2))
   - `created_at` (DATETIME2(6))

2. **raw_data** - Table storing raw JSON data
   - `weather_data_id` (BIGINT, PRIMARY KEY, FOREIGN KEY)
   - `raw_data` (NVARCHAR(MAX) with JSON validation)

## Setup Instructions

### Standard SQL Server Setup

Run the scripts in order:

#### 1. Create Database

```bash
sqlcmd -S your_server -U sa -P your_password -i 01_create_database.sql
```

Or in SSMS/Azure Data Studio, open and execute `01_create_database.sql`.

This script:
- Creates the `WeatherDB` database
- Sets the recovery model to FULL (required for Always On AG)
- Takes a full backup

#### 2. Create User and Grant Permissions

```bash
sqlcmd -S your_server -U sa -P your_password -i 02_create_user.sql
```

This script:
- Creates SQL Server login `weather_user` with password `Weather123!`
- Creates database user in WeatherDB
- Grants necessary permissions (db_datareader, db_datawriter, CREATE TABLE, etc.)

**Security Note:** Change the default password in production!

#### 3. Create Tables

```bash
sqlcmd -S your_server -U weather_user -P Weather123! -d WeatherDB -i 03_create_tables.sql
```

This script:
- Creates `weather_data` table with indexes
- Creates `raw_data` table with foreign key constraint
- Adds JSON validation constraint on `raw_data.raw_data` column

Alternatively, the application can create tables automatically on first run if the user has appropriate permissions.

## Always On Availability Group Setup

If you're using SQL Server Always On Availability Groups for high availability:

### Prerequisites
- Windows Server Failover Clustering (WSFC) configured
- Always On Availability Groups enabled on all SQL Server instances
- Full backup of the database taken (handled by `01_create_database.sql`)

### Steps to Add Database to AG

1. **Take a transaction log backup** (after running the setup scripts):

```sql
USE master;
GO
BACKUP LOG WeatherDB 
TO DISK = '/var/opt/mssql/data/WeatherDB_Log.trk'
WITH FORMAT, INIT, NAME = 'WeatherDB Log Backup';
GO
```

2. **Add database to Availability Group** (on primary replica):

```sql
ALTER AVAILABILITY GROUP AAGONE ADD DATABASE WeatherDB;
GO
```

3. **Restore on secondary replicas**:

For each secondary replica, restore the database with NORECOVERY:

```sql
-- On each secondary replica
RESTORE DATABASE WeatherDB 
FROM DISK = 'WeatherDB_Full.bak'
WITH NORECOVERY;
GO

RESTORE LOG WeatherDB 
FROM DISK = '/path/to/WeatherDB_Log.trk'
WITH NORECOVERY;
GO

-- Join the database to the AG
ALTER DATABASE WeatherDB SET HADR AVAILABILITY GROUP = AAGONE;
GO
```

### Connection String for AG

When connecting to an Always On Availability Group, use the listener name:

```
Server=AG_Listener_Name,1433;Database=WeatherDB;User Id=weather_user;Password=Weather123!;
ApplicationIntent=ReadWrite;MultiSubnetFailover=True;
```

## Application Configuration

Update your `.env` file to use MS SQL Server:

```env
# Database Configuration - MS SQL Server
DB_TYPE=mssql
DB_HOST=your_sql_server  # Or AG listener name
DB_PORT=1433
DB_NAME=WeatherDB
DB_USERNAME=weather_user
DB_PASSWORD=Weather123!

# MS SQL specific
MSSQL_DRIVER=ODBC Driver 17 for SQL Server
```

### ODBC Driver Installation

#### Ubuntu/Debian:
```bash
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list > /etc/apt/sources.list.d/mssql-release.list
apt-get update
ACCEPT_EULA=Y apt-get install -y msodbcsql17
```

#### Red Hat/CentOS:
```bash
curl https://packages.microsoft.com/config/rhel/8/prod.repo > /etc/yum.repos.d/mssql-release.repo
yum remove unixODBC-utf16 unixODBC-utf16-devel
ACCEPT_EULA=Y yum install -y msodbcsql17
```

#### Windows:
Download and install from: https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server

## Verification

After setup, verify the tables were created:

```sql
USE WeatherDB;
GO

-- Check tables
SELECT * FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME IN ('weather_data', 'raw_data');

-- Check foreign keys
SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_NAME = 'fk_raw_data_weather_data';

-- Check indexes
SELECT * FROM sys.indexes
WHERE object_id = OBJECT_ID('weather_data');
```

## Testing

Test the connection from the application:

```bash
# Start the application
python src/wxconnect/main.py
```

The application should:
1. Connect to MS SQL Server
2. Create tables if they don't exist (or verify existing tables)
3. Start receiving MQTT messages
4. Insert data into both `weather_data` and `raw_data` tables

## Troubleshooting

### Connection Issues

1. **Check SQL Server is listening on TCP/IP:**
   - SQL Server Configuration Manager → SQL Server Network Configuration → Protocols
   - Enable TCP/IP protocol
   - Restart SQL Server service

2. **Firewall rules:**
   ```bash
   # Linux
   sudo firewall-cmd --add-port=1433/tcp --permanent
   sudo firewall-cmd --reload
   
   # Windows
   New-NetFirewallRule -DisplayName "SQL Server" -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow
   ```

3. **Test connection with sqlcmd:**
   ```bash
   sqlcmd -S your_server,1433 -U weather_user -P Weather123! -d WeatherDB
   ```

### Permission Issues

If the application can't create tables, grant explicit permissions:

```sql
USE WeatherDB;
GO
GRANT CREATE TABLE TO weather_user;
GRANT ALTER ON SCHEMA::dbo TO weather_user;
GO
```

### Always On AG Issues

1. **Database not synchronizing:**
   - Check AG health: `SELECT * FROM sys.dm_hadr_database_replica_states;`
   - Verify network connectivity between replicas
   - Check SQL Server error log

2. **Automatic failover not working:**
   - Verify AG is configured with automatic failover mode
   - Check WSFC quorum configuration
   - Ensure database is in SYNCHRONIZED state

## Comparison with Oracle Schema

The MS SQL schema exactly mirrors the Oracle implementation:

| Feature | Oracle | MS SQL |
|---------|--------|---------|
| Identity column | SEQUENCE | IDENTITY |
| Timestamp | TIMESTAMP(6) | DATETIME2(6) |
| String | VARCHAR2 | NVARCHAR |
| JSON storage | CLOB with IS JSON check | NVARCHAR(MAX) with ISJSON check |
| FK cascade | ON DELETE CASCADE | ON DELETE CASCADE |
| Index | Standard B-tree | Standard B-tree |

Both implementations provide:
- Automatic ID generation
- Foreign key relationship between tables
- JSON validation on raw data
- Indexed timestamp and topic columns
- Identical application-level interface

## Support

For issues or questions:
- Check application logs: `LOG_LEVEL=DEBUG` in `.env`
- Review SQL Server error log
- Consult Microsoft SQL Server documentation