microsoft/sqldb-authoring-cli

Update Check — ONCE PER SESSION (mandatory) The first time this skill is used in a session, run the check-updates skill before proceeding.

• GitHub Copilot CLI / VS Code: invoke the check-updates skill.
• Claude Code / Cowork / Cursor / Windsurf / Codex: compare local vs remote package.json version.
• Skip if the check was already performed earlier in this session.

CRITICAL NOTES

1. To find the workspace details (including its ID) from workspace name: list all workspaces and, then, use JMESPath filtering
2. To find the item details (including its ID) from workspace ID, item type, and item name: list all items of that type in that workspace and, then, use JMESPath filtering

SQL Database Authoring — CLI Skill

Create and manage SQL database in Fabric items, author T-SQL DDL/DML, deploy schema via SqlPackage, configure source control, CI/CD, and GraphQL APIs using sqlcmd and az rest.

Table of Contents

| Topic | Reference | |---|---| | Finding Workspaces and Items in Fabric | COMMON-CLI.md § Finding Workspaces and Items in Fabric — read first | | Authentication & token acquisition | COMMON-CORE.md § Authentication and COMMON-CLI.md § Authentication Recipes | | SQL / TDS Data-Plane Access (sqlcmd) | COMMON-CLI.md § SQL / TDS Data-Plane Access | | CLI Gotchas (audience, escaping, expiry) | COMMON-CLI.md § Gotchas & Troubleshooting | | SQL Database item definitions (dacpac/sqlproj) | ITEM-DEFINITIONS-CORE.md § Per-Item-Type Definitions | | SQL Database vs DW capability matrix | SQLDB-AUTHORING-CORE.md § SQL Database vs Data Warehouse | | Database Lifecycle, Table DDL, Temporal, Views, DML, BCP, Procs/Functions/Triggers, SqlPackage, Source Control, GraphQL, Mirroring, Auditing, CMK, Permissions, Gotchas, Patterns, Decision Guide | SQLDB-AUTHORING-CORE.md (all sections) | | T-SQL Anti-Patterns | SQLDB-AUTHORING-CORE.md § T-SQL Anti-Patterns | | Schema Design Guidance | SQLDB-AUTHORING-CORE.md § Schema Design Guidance | | Limitations Reference (unsupported features, resource limits) | SQLDB-AUTHORING-CORE.md § Limitations Reference | | Endpoint Selection | SQLDB-CONSUMPTION-CORE.md § Endpoint Selection | | Metadata and Schema Discovery | SQLDB-CONSUMPTION-CORE.md § Metadata and Schema Discovery | | Deep Performance Diagnostics | sqldb-operations-cli |

For Fabric topology, capacity, OneLake, auth, control-plane REST, jobs see COMMON-CORE.md and COMMON-CLI.md.

---

Tool Stack

| Tool | Role | Setup Reference | |---|---|---| | sqlcmd (Go) | Primary: Execute DDL/DML T-SQL. Standalone binary, no ODBC, built-in Entra ID auth. | See COMMON-CLI.md for installation and authentication prerequisites. | | sqlpackage | Schema deployment (.dacpac), database portability (.bacpac). | See COMMON-CLI.md for installation and environment setup prerequisites. | | az CLI | Fabric REST for endpoint discovery and database creation. | See COMMON-CLI.md for Azure CLI authentication and token setup. | | jq | Parse JSON from az rest. | See COMMON-CLI.md for shared CLI/tooling prerequisites. |

Agent check — before first operation, confirm the required CLI tools and authentication are already configured by following the shared setup guidance in COMMON-CLI.md. Do not duplicate install or az login steps in this skill.

---

Connection

Discover the SQL Database TDS Endpoint

For workspace/item resolution, SQL Database endpoint discovery, and CLI connection patterns, use the shared guidance in common/COMMON-CLI.md and common/COMMON-CORE.md instead of embedding az rest or sqlcmd recipes here.

Follow the SQL Database endpoint discovery steps in COMMON-CLI.md, then bring the discovered server FQDN and database name back into this skill for SQLDB-specific authoring and deployment tasks.

• Use the SQL Database endpoint values returned by Fabric for the target item; do not hardcode endpoint domains or database names.
• When generating deployment or authoring steps, treat connectivity details as inputs discovered via the shared common guidance.
• Focus this skill on schema authoring, DDL/DML design, constraints, indexing, source control, SqlPackage deployment, and GraphQL/API behaviors rather than connection bootstrapping.

Connection guidance

If the user needs help connecting interactively or from CI/CD, refer them to common/COMMON-CLI.md for the shared sqlcmd setup, authentication, and invocation patterns.

For SQLDB-specific authoring in this skill, assume the connection inputs are already known:

• serverFqdn
• databaseName

---

Agentic Workflows

Schema Discovery Before Authoring

Before any write operation, discover the target schema:

# 1. List tables
$SQLCMD -Q "SELECT table_schema, table_name FROM information_schema.tables WHERE table_type='BASE TABLE' ORDER BY 1,2" -W

# 2. Check columns
$SQLCMD -Q "SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_name='Orders' ORDER BY ordinal_position" -W

# 3. Sample data
$SQLCMD -Q "SELECT TOP 5 * FROM dbo.Orders" -W

# 4. Check constraints
$SQLCMD -Q "SELECT tc.constraint_name, tc.constraint_type, kcu.column_name FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name=kcu.constraint_name WHERE tc.table_name='Orders' ORDER BY tc.constraint_type" -W

# 5. Check indexes
$SQLCMD -Q "SELECT i.name, i.type_desc, STRING_AGG(c.name,', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS cols FROM sys.indexes i JOIN sys.index_columns ic ON i.object_id=ic.object_id AND i.index_id=ic.index_id JOIN sys.columns c ON ic.object_id=c.object_id AND ic.column_id=c.column_id WHERE i.object_id=OBJECT_ID('dbo.Orders') GROUP BY i.name, i.type_desc" -W

# 6. Row counts
$SQLCMD -Q "SELECT s.name AS [schema], t.name AS [table], SUM(p.rows) AS row_count FROM sys.tables t JOIN sys.schemas s ON t.schema_id=s.schema_id JOIN sys.partitions p ON t.object_id=p.object_id AND p.index_id IN (0,1) GROUP BY s.name, t.name ORDER BY row_count DESC" -W

# 7. Programmability objects
$SQLCMD -Q "SELECT name, type_desc FROM sys.objects WHERE type IN ('V','FN','IF','P','TF','TR') ORDER BY type_desc, name" -W

Workflow

1. Discover → Run steps 1–5 to understand available tables/columns.
2. Sample → SELECT TOP 5 on relevant tables.
3. Formulate → Select pattern from SQLDB-AUTHORING-CORE.md (Table DDL, Temporal Tables, Data Virtualization, Views, DML, etc.).
4. Execute → $SQLCMD -Q "..." or $SQLCMD -i file.sql for multi-statement.
5. Verify → Query affected table (SELECT COUNT(*), SELECT TOP 5).
6. Deploy (optional) → Extract with sqlpackage /Action:Extract for .dacpac; set up source control.

---

Gotchas, Rules, Troubleshooting

For full authoring gotchas: SQLDB-AUTHORING-CORE.md Authoring Gotchas and Troubleshooting. For CLI-specific issues: COMMON-CLI.md Gotchas & Troubleshooting (CLI-Specific).

MUST DO

• Verify workspace has capacity before creating database — call GET /v1/workspaces/{id} and check capacityId.
• Always -d <DatabaseName> — FQDN alone is insufficient.
• Always -G or --authentication-method — SQL auth not supported on Fabric.
• Follow COMMON-CLI.md authentication guidance first — ActiveDirectoryDefault depends on an authenticated CLI session.
• Specify collation at creation — cannot be changed after the database is created.
• Use SqlPackage for repeatable deployments — extract .dacpac → publish for incremental schema changes.
• Use -i file.sql for multi-statement batches (CREATE PROCEDURE, transactions with GO separators).
• SET NOCOUNT ON; in scripts — suppresses row-count messages that corrupt output.

AVOID

• ODBC sqlcmd (/opt/mssql-tools/bin/sqlcmd) — use the Go version.
• Omitting -W in scripts — trailing spaces corrupt CSV.
• Hardcoded FQDNs — discover via REST API.
• MARS — not supported. Remove MultipleActiveResultSets.
• CREATE LOGIN / SQL authentication — Entra users only.
• Modifying tables with CCI / ALTER INDEX ALL while mirroring is active — stop mirroring or alter individual indexes.
• vector / json columns on tables that must mirror — cannot be mirrored to OneLake.
• Special characters in DB / column names — see Limitations Reference.
• Modifying .sqlproj in source-controlled repo — reset on next sync.
• Unsupported features (CDC, Always Encrypted, in-memory, ledger, full-text GA, partition SWITCH, EXECUTE AS, hierarchyid/sql_variant/timestamp PKs, CTAS, COPY INTO) — see Limitations Reference for the full list.
• Exceeding resource limits (32 vCores, 4 TB, 1024 GB tempdb, 150 DBs/workspace).

PREFER

• az rest for database creation over portal — scriptable and repeatable.
• SqlPackage publish (.dacpac) over manual DDL for schema evolution — tracks incremental changes.
• Enforced constraints (PK, FK, CHECK, DEFAULT) — SQL Database supports them fully, unlike DW.
• Temporal tables for audit trails and slowly-changing dimensions — full system-versioning available. Note: history table is excluded from mirroring.
• Data virtualization (external tables / OPENROWSET) to query OneLake without ETL. Note: external tables themselves are not mirrored.
• INSERT ... SELECT for bulk data movement within the database.
• BCP for high-performance bulk import/export.
• Native AI functions / sp_invoke_external_rest_endpoint for embedding generation in T-SQL.
• sqlcmd (Go) -G over curl+token for SQL queries.
• -Q (non-interactive exit) for agentic use.
• -i file.sql over -Q "..." for anything beyond simple one-liners.
• -F vertical for exploration of wide tables.
• Env vars (FABRIC_SERVER, FABRIC_DB) for script reuse.
• Auto-tuning — let the engine manage indexes; check sys.dm_db_tuning_recommendations periodically.
• Optimized locking — enabled by default; reduces lock memory and eliminates lock escalation.

TROUBLESHOOTING

| Symptom | Fix | |---|---| | SqlPackage auth error | Use Authentication=Active Directory Default in connection string | | Login failed for user | Verify -d matches database display name exactly (case-sensitive) | | Cannot open server / Login timeout expired | Re-discover FQDN via REST API; check port 1433 / firewall | | ActiveDirectoryDefault failure | az login session may be expired — refresh Azure CLI authentication per common/COMMON-CLI.md | | Collation error after creation | Cannot change — must recreate database with desired collation | | Foreign key violation on INSERT | Check referenced table has matching row; verify column types | | Vector insert dimension mismatch | Ensure vector literal has exactly the declared dimensions | | CCI creation fails on existing table | Stop mirroring, create index, restart mirroring. CCI created inline with CREATE TABLE also prevents mirroring of that table | | Table not mirrored after DDL change | DDL changes trigger a full data reseed; table with unsupported PK type or >1000 tables are skipped | | Vector/json column prevents mirroring | Tables with vector or json columns cannot be mirrored; use OLTP endpoint to query these | | Computed columns missing from analytics endpoint | Computed columns are skipped during mirroring | | LOB data truncated on analytics endpoint | LOB columns > 1 MB are truncated in OneLake | | ALTER INDEX ALL fails | Not allowed while mirroring is active; alter individual indexes by name | | Garbled CSV / (N rows affected) in file | Add -W -s"," -w 4000; prepend SET NOCOUNT ON; | | sp_invoke_external_rest_endpoint 401 | Create/verify database-scoped credential for target URL | | sqlcmd not found | Ensure sqlcmd is installed and on PATH; follow the shared CLI setup guidance in common/COMMON-CLI.md | | Connection fails on redirect ports | Connection policy requires ports 11000–11999 open in addition to 1433 |