SQL Server/Azure SQL Managed Instance Engineer

Seeking an expert SQL Server/Azure SQL Managed Instance Engineer to rebuild a legacy CMS solution using the latest technology stack. The role involves assessing the current database structure, proposing improvements, and implementing a new database model based on functional and non-functional requirements.

• Scope of Work
• 1. Schema Creation – Create SQL scripts for signed off proposed DB model for new CMS solution.
• 2. SSDT Project – Create an SSDT SQL Database Project (.sqlproj) as the single source of truth:
• o Import from source DB or DACPAC.
• o Set correct target platform (SQL Server 2019/2022, MI-compatible).
• o Resolve all build warnings and unresolved references.
• 3. Row-Level Security (RLS) – Implement isolation on companyId:
• o Predicate function using SESSION_CONTEXT('CompanyId').
• o Apply security policies to tenant-scoped tables (all tables containing with CompanyId.
• o Provide a short integration note/snippet for the app to set SESSION_CONTEXT.
• 4. Post-Deployment.sql – Author with:
• o Idempotent lookup seeds (MERGE).
• o Roles/users/grants (contained users/AAD preferred).
• o Optional DB-scoped settings.
• 5. Publish Profiles – Provide Dev/Test/Prod .publish.xml profiles with safe defaults (e.g., block data loss by default).
• 6. CI/CD Setup – Configure pipeline (GitHub Actions):
• o Build .sqlproj to DACPAC.
• o Generate DeployReport (preview).
• o Publish to MI Dev with secure authentication (AAD/Managed Identity preferred).
• o No data seeding or migration of real users required.
• o Create placeholder contained users so the RLS policies can be applied and tested. (Later to be swapped out with AD groups)
• o Roles and Grants set up so that the structure is in place.
• o Optional database-scoped settings (if relevant)
• 7. First Deployment – Deploy to MI Dev and perform smoke tests on key stored procedures/views.
• 8. Runbook & Guide – Deliver a short runbook covering:
• o Connectivity/authentication setup.
• o Pipeline usage.
• o Rollback guidance.
• Constraints
• Azure SQL Managed Instance is the definite target.
• No cross-database calls, CLR, linked servers, or SQL Agent jobs.
• No legacy data or login migration.
• Stored procedures remain intact.
• Deliverables
• Clean SQL Database Project (.sqlproj) in our Git repo.
• Post-Deployment.sql (with roles, grants, seeds, optional DB settings).
• RLS function and policies applied to agreed tables.
• Dev/Test/Prod publish profiles (safe defaults).
• CI/CD pipeline YAML (GitHub Actions).
• Short runbook: connectivity/auth, pipeline usage, rollback guidance.
• Timeline
• Strict: 1–2 business days (8–14 hours for an experienced engineer).
• Due to downstream dependencies, timely completion is essential.
• Required Experience
• SQL Server & Azure SQL MI in production.
• SSDT/sqlproj, DACPACs, and SqlPackage deployments.
• RLS implementation on SQL Server.
• Database CI/CD (GitHub Actions).
• Writing idempotent T-SQL seeds (MERGE).