Senior Database Consultant

We are looking for an experienced Senior Database Consultant to lead the comprehensive design, consolidation, and optimization of our critical SQL Server environments. This role involves managing large-scale databases, improving performance, and ensuring high availability and disaster recovery across enterprise systems. The position is remote, based in Lahore, Pakistan, and offers both full-time and contract opportunities within our Data Engineering department.

Key Responsibilities

Database Consolidation & Schema Redesign:
Conduct thorough dependency mapping by auditing cross-database queries, linked servers, and multi-part object references to identify and refactor tightly coupled components before migration. Use tools such as Dependency Viewer and RedGate SQL Dependency Tracker to build dependency graphs. Resolve schema inconsistencies including collation mismatches, unify schemas, merge lookup tables, and normalize data models. Execute minimal-downtime data migrations using blue-green deployments and parallel batching techniques, implementing Change Data Capture (CDC) for live synchronization. Validate migrations through row counts, hashing, and exception workflows with tools like SSIS, bcp, BULK INSERT, and Azure Data Factory.

OLTP Engine Optimization:
Enhance concurrency and transaction isolation by implementing Read Committed Snapshot Isolation (RCSI) and Snapshot Isolation (SI). Analyze blocking and deadlocks using Extended Events and dynamic management views (DMVs). Prevent lock escalation through partitioning and locking hints. Design and maintain enterprise-level indexing strategies including clustered, nonclustered, filtered, and sliding-window partitioned indexes. Manage tempdb configuration, MAXDOP settings, and memory allocation using Resource Governor. Implement In-Memory OLTP (Hekaton) to boost transaction processing.

OLAP & Reporting Strategy:
Isolate workloads by configuring Always On Availability Groups with read-only routing and failover listeners. Monitor synchronization health and redo queue lag. Implement clustered and nonclustered columnstore indexes to optimize operational analytics, managing compression and batch mode execution. Architect data warehouses using star and snowflake schemas with slowly changing dimensions (SCD 1/2/3) and conformed dimensions. Develop ELT pipelines using T-SQL and SSIS, and deploy SSAS cubes and tabular models leveraging Azure Synapse, Microsoft Fabric, and Power BI DirectQuery.

High Availability and Disaster Recovery (HA/DR) Architecture:
Deploy synchronous-commit Availability Groups with Windows Server Failover Clustering (WSFC), designing topology, listeners, and health policies. Monitor environments using Extended Events, SolarWinds DPA, and SentryOne, and conduct failover testing. Configure asynchronous-commit multi-region AGs, multi-subnet clustering, and Log Shipping for disaster recovery. Maintain version-controlled DR runbooks. Implement tiered backup strategies including full, differential, and transaction log backups, automate processes with Ola Hallengren scripts and PowerShell, and perform quarterly recovery drills to validate RTO and RPO objectives.

Required Qualifications

- Over 10 years of dedicated enterprise SQL Server DBA and architecture experience managing databases larger than 1,000 GB with high-concurrency OLTP workloads.
- Proven track record leading consolidation projects and managing Always On Availability Groups, WSFC, and disaster recovery across SQL Server versions 2012 through 2022.
- Advanced proficiency in T-SQL, Query Store, Plan Guides, Extended Events, and SQL Server internals.
- Strong Windows Server administration skills, including PowerShell scripting, Active Directory integration, and SAN/NAS storage alignment.
- Experience with cloud platforms and tooling such as Azure SQL Managed Instance, Azure Synapse, SSMS, Azure Data Studio, Redgate SQL Toolbelt, and SolarWinds Database Performance Analyzer.

Preferred Qualifications and Benefits

Preferred certifications include DP-300, MCSE: Data Management & Analytics, and MCSA: SQL Server.

We offer a comprehensive benefits package including paid time off (annual, sick, and personal), maternity and paternity leave as per company policy, medical and life insurance, fuel allowance, and company transportation where applicable. Our culture emphasizes recognition programs such as employee shoutouts, awards, and anniversary celebrations, fostering a collaborative and supportive team environment.