Memory hook: Review the model; inspect the deployment plan.
Must remember
- SQL Database Projects represent database schema in source control; supported SDK-style projects build a deployable model. A deployment tool compares desired schema with the target, so review destructive changes explicitly.
- Version reference/static data with a deliberate deployment strategy. Migration order, data movement and backward compatibility matter beyond whether a schema build succeeds.
- Use branches, pull requests, code owners, policy checks, tests and controlled approvals. Keep pipeline identities/secrets separate from developer credentials and restrict production deployment rights.
- Unit tests validate SQL logic; integration tests validate real engine behavior; deployment tests expose drift and data-loss risks. Detect unexpected target changes before publishing.
- Copilot can draft queries, schema or project changes. Repository instruction files and scoped context improve consistency, but generated SQL still needs correctness, security and performance review.
- MCP-connected SQL/Fabric tools can expose live data or mutation capabilities. Choose model/tool settings deliberately, start with read-only access for analysis and validate every proposed write against the user’s authorization.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Production schema differs from the project | Investigate drift and inspect the deployment diff before publishing. |
| AI proposes dropping a column to fix a build | Review data/dependency impact and use a safe migration plan. |
Traps
- A successful DACPAC/project build does not prove deployment is non-destructive.
- An AI assistant’s database connection can expose sensitive data even without changing it.
Active recall
1. What should be reviewed before schema publication?
Generated changes, data-loss warnings, dependencies, permissions and rollback/recovery options.
2. Why test with realistic data?
Empty databases hide migration, constraint and performance problems.
3. What belongs in source control?
Schema, reviewed scripts, tests and appropriate reference data, excluding secrets.
4. How restrict an AI SQL tool?
Limit identity privileges, accessible objects, network paths and permitted operations.
5. Why use staged deployment?
To detect compatibility and performance regressions before broad production impact.