Senior Database Consultant

We are seeking a seasoned Senior Database Consultant to lead the end-to-end design, consolidation, and optimization of our mission-critical SQL Server environments!

JOB OVERVIEW:

• Role: Senior Database Consultant

• Experience: 10+ Years

• Type: Full-Time / Contract

• Location: Remote (Lahore, Pakistan)

• Department: Data Engineering

CORE COMPETENCIES:

Database Consolidation & Schema Design | OLTP Performance Tuning & Isolation High Availability (HA/DR) & Always On AGs | OLAP & Data Warehouse Strategy | Indexing & Query Optimization | CDC & Data Migration Strategy | tempdb & Resource Governance | SSIS / T-SQL ELT Pipelines Backup & Recovery Automation | Security & Compliance Hardening

RESPONSIBILITIES & TECHNICAL SCOPE:

1. Database Consolidation & Schema Redesign:

• Dependency Mapping: Audit cross-db queries, linked servers, and 3/4-part object references (database.schema.object); build dependency graphs and refactor tight coupling before cutover. (Tools: Dependency Viewer, RedGate SQL Dependency Tracker, DMVs).

• Schema Unification: Resolve collation mismatches via ALTER TABLE/COLUMN staging; unify schemas, merge lookup tables, resolve naming conflicts, transition to contained DB users, and normalize data models.

• Data Migration: Execute minimal-downtime migrations (blue-green, parallel batching), implement CDC for live sync, and validate via row-count, hashing, and exception workflows. (Tools: SSIS, bcp, BCP API, BULK INSERT, Polybase, Azure Data Factory).

2. OLTP Engine Optimization:

• Concurrency & Isolation: Implement RCSI/SI isolation levels; analyze blocking/deadlocks via Extended Events, sys.dm_exec_requests, and sys.dm_os_wait_stats; prevent lock escalation via partitioning, locking hints (NOLOCK/UPDLOCK/ROWLOCK), and transaction profiling.

• Enterprise Indexing: Design clustered, nonclustered covering, filtered, and sliding-window partitioned indexes; audit via sys.dm_db_index_usage_stats and maintain index health via scheduled rebuild/reorganize jobs.

• Resource Tuning: Configure tempdb (1 file per core up to 8, TF 1117/1118); tune MAXDOP/CTFP; manage memory via Resource Governor; implement In-Memory OLTP (Hekaton).

3. OLAP & Reporting Strategy:

• Workload Isolation: Configure Always On AG Read-Only Routing, listener failover logic, and monitor redo queue lag/sync health.

• Operational Analytics: Implement Clustered & Nonclustered Columnstore Indexes (CCI/NCCI); manage delta store/tuple mover/compression; tune batch mode execution and Adaptive Query Processing.

• Data Warehousing: Architect star/snowflake schemas (SCD 1/2/3, conformed dimensions); build T-SQL/SSIS ELT pipelines; deploy SSAS cubes/tabular models. (Tech: Azure Synapse, MS Fabric, Power BI DirectQuery).

4. HA/DR Architecture:

• Local HA (WSFC + Always On AGs): Deploy synchronous-commit AGs, design topology/listeners/health policies, monitor via Extended Events/SolarWinds DPA/SentryOne, and perform chaos failover testing.

• Disaster Recovery: Configure asynchronous-commit multi-region AGs, multi-subnet clustering (RegisterAllProvidersIP=1), Log Shipping, and maintain version-controlled DR runbooks.

• Backup & Recovery: Tiered backup strategy (Full/Diff/Log), validation via RESTORE VERIFYONLY and DBCC CHECKDB, automation via Ola Hallengren/PowerShell, and quarterly production-scale RTO/RPO recovery drills.

QUALIFICATIONS & SKILLS:

• Experience: 10+ years dedicated enterprise SQL Server DBA / Architecture experience (1,000+ GB DBs, high-concurrency OLTP), leading consolidation projects, and managing Always On AGs/WSFC/DR across SQL Server 2012–2022.

Technical Skills:

• SQL Server: Advanced T-SQL, Query Store, Plan Guides, Extended Events, DMVs, Internals.

• Platform: Windows Server Admin, PowerShell, Active Directory, SAN/NAS alignment.

• Cloud & Tooling: Azure SQL MI, Synapse, SSMS, Azure Data Studio, Redgate SQL Toolbelt, SolarWinds DPA.

Certifications (Preferred): DP-300, MCSE: Data Management & Analytics, MCSA: SQL Server.

What we offer:

• Paid Time-Off (Annual, Sick and Personal)

• Maternity and Paternity Leaves (As per the company's policy)

• Medical Insurance

• Life Insurance

• Fuel Allowance (As per the company's policy)

• Company Transportation (As per the company's policy)

• Recognition programs (employee shoutouts, awards, anniversaries, etc.)

• Collaborative and supportive team culture