# Lab: Local Time Zone support in Azure SQL Database (public preview)

Hands-on scripts that test every claim in the newsletter post about `ALTER DATABASE SCOPED CONFIGURATION SET TIME_ZONE` and `SET TIME ZONE`.
The step-by-step runbook (what each step does, why, the results and recommendations) is at `runbook/index.html`.

> **Disclaimer:** these scripts are provided "as is", without warranty of any kind. Use them only in non-production environments (development, test or lab), review them before you run them, and run them at your own risk. You are responsible for any changes they make to your systems and for any costs they incur.

## What you need
- An Azure SQL logical server with **two** databases created from the `AdventureWorksLT` sample:
  - `DB_UTC`: stays on UTC; only read-only tests run here.
  - `DB_CST`: switched to `'Central Standard Time'`; the lab creates schema `tzlab` in it.
- A Microsoft Entra admin on the server, and your client IP allowed in the server firewall.
- Azure CLI signed in (`az login`). Authentication goes through `--authentication-method ActiveDirectoryAzCli`.
- [go-sqlcmd](https://learn.microsoft.com/sql/tools/sqlcmd/go-sqlcmd-utility) 1.10+ (`brew install sqlcmd` or `winget install sqlcmd`).
- [sqlpackage](https://learn.microsoft.com/sql/tools/sqlpackage/sqlpackage-download) (only for step 15: `dotnet tool install -g microsoft.sqlpackage`).

Create the two databases (serverless, auto-pause after 60 minutes):

```bash
az sql db create -g <rg> -s <server> -n lab-utc --sample-name AdventureWorksLT --edition GeneralPurpose --compute-model Serverless --family Gen5 --capacity 1 --auto-pause-delay 60
az sql db create -g <rg> -s <server> -n lab-cst --sample-name AdventureWorksLT --edition GeneralPurpose --compute-model Serverless --family Gen5 --capacity 1 --auto-pause-delay 60
```

## Run it

```bash
export SERVER=<server>.database.windows.net DB_UTC=lab-utc DB_CST=lab-cst
./run_all.sh
```

Each scenario writes `results/<step>__<db>.txt` with every statement echoed, followed by its output and any error messages.
The run takes about 3 minutes, including a deliberate 30-second timeout in step 12a.

## Files
| File | Runs on | What it tests |
|---|---|---|
| `scripts/00_discovery.sql` | both | Feature surface and starting state |
| `scripts/01_function_surface.sql` | both (CST before/after) | Which date/time functions follow the setting |
| `scripts/02_cst_setup_legacy_data.sql` | CST | Creates `tzlab.Orders` with UTC-era rows |
| `scripts/03_set_db_timezone.sql` | CST | Valid and invalid `ALTER DATABASE SCOPED CONFIGURATION SET TIME_ZONE` |
| `scripts/05_brownfield_mixing.sql` | CST | Old UTC rows + new local rows in one `datetime2` column |
| `scripts/06_session_override.sql` | both | `SET TIME ZONE` precedence, errors, DMV |
| `scripts/07_module_scope.sql` | CST | Procs, nested procs, dynamic SQL, triggers, functions |
| `scripts/08_at_time_zone_and_dst.sql` | CST | `AT TIME ZONE` and DST gap/overlap |
| `scripts/09_sargability.sql` | CST | Index seek vs scan, correct vs wrong counts |
| `scripts/10_system_objects_utc.sql` | CST | DMVs, catalog views, Query Store stay UTC |
| `scripts/11_temporal_tables.sql` | CST | System-versioned tables and `FOR SYSTEM_TIME AS OF` |
| `scripts/12a/12b_waitfor_time_*.sql` | CST | `WAITFOR TIME` with local vs UTC targets |
| `scripts/13_migration_etl.sql` | CST | Migrating `datetime2` to `datetimeoffset` |
| `scripts/14_hybrid_utc_db.sql` | UTC | "UTC at rest, local in session" pattern |
| `scripts/15_bacpac_dacpac_known_issue.sh` | CST/UTC | DACPAC/BACPAC known issue (nothing is deployed) |
| `scripts/15e_bacpac_without_set_time_zone_modules.sh` | CST | Isolates the BACPAC export failure |
| `scripts/99_cleanup.sql` | CST | Removes `tzlab` and resets `TIME_ZONE = LOCAL` (run by hand) |

## Clean up

```bash
sqlcmd -S "$SERVER" -d "$DB_CST" --authentication-method ActiveDirectoryAzCli -i scripts/99_cleanup.sql
az sql db delete -g <rg> -s <server> -n lab-utc --yes
az sql db delete -g <rg> -s <server> -n lab-cst --yes
```

Tested on 2026-10-15 at 8:49 PM US Central (2026-10-16 01:49 UTC) against Azure SQL Database engine 12.0.2000.8 (build of Sep 17 2026), go-sqlcmd 1.10.0 and sqlpackage 170.5.96. The feature is in preview, so results may change.