SENIOR SQL DBA

Job Responsibilities Installing, configuring, managing, monitoring and troubleshooting SQL 2005 – SQL 2019 Installing Service Packs, Cumulative Updates, Hotfixes (GDR, QFE). Patching hierarchy and clarity on SQL Build numbers and Internal Database build numbers. Upgrading/Migrating SQL Server from older (from SQL 2005) to newer (till SQL 2019). Creating and Configuring Databases as per requirement of Application Teams. Setting up, configuring, and administering SSIS, SSRS and SSAS. Writing T-SQL blocks, stored procedures, reviewing codes and recommending necessary query tuning to application team, and executing codes in respective environments after necessary approvals. Creating logins/users both Windows and SQL Server authenticated. Understanding of Server Role, Database Roles and Permissions. Handling Orphan Users and working at Impersonate permissions. Configuring Linked Servers between same/different SQL Server versions. Fixing SQL Server forgot password scenarios & recovering all logins lost situations. Correlation of Schemas-Users, changing schema ownerships & giving permissions. Setting up contained databases and utilizing them for effective migrations. Configuring recovery models and designing backup strategies. Fine tuning backups by using Backup Encryption, Compression and special parameters (like MAXTRANSFERSIZE, BUFFERCOUNT, BLOCKSIZE & CHECKSUM). Increasing Backup Buffers & Split backups to make backups run faster. Restored corrupted databases and recovered them with minimal/zero data loss. Backup Types (Full/Differential/Log) & Restore Scenarios (Point-in-Time/Piecemeal/Page) Handling Master, Model, MSDB, TEMPDB and Resource database corruptions. Handling Suspect Database state and following order of troubleshooting & recovering DB’s. Configuring Database Mail along with Alerts and Operators. Knowledge on configuring Active Directory and adding VMs/Servers to Domain. Understanding on Storage Area Networks (SANs), Data Banks, IOPS & LUNs. Log-Shipping scenarios 14420, 14421, failover, switchover, adding file & troubleshooting. Mirroring scenarios 1418, force failover, operating modes, adding file & endpoint troubleshooting. Configuring Windows Clustering and SQL Server Clustering. Hands on knowledge on Windows Cluster, Quorum, Dynamic Quorum, Voting Mechanism, Failovers through Cluster Administrator and commands. Hands on knowledge on SQL Server Cluster scenarios Failover, Adding Disk to existing instance with dependencies, Advanced Cluster Parameters like Failure Condition Level, Health check timeouts, patching clustered instances (SQL 2005 till 2019) Configuring Always-On AG on standalone instances across Windows Cluster in Synchronous mode and Asynchronous mode. Handling advanced Always-On AG scenarios like Failover, adding new data/log files, understanding of Lease Timeout, Health check timeout and Failure Condition Level parameters, configuring Read-Only Routing, listeners and backup configurations. Configuring Replication types Transactional, Snapshot, Merge & Peer-To-Peer. Troubleshooting Transaction Replication for Agent failures, primary key violation errors, adding tables to existing replication, monitor/troubleshoot through Replication Monitor. Experience in Merge & Peer-to-Peer bi-directional replication conflict detection & resolution. Replication topologies Central Distributor/Publisher/Subscriber & Publishing subscriber. Understanding of Advanced SQL Server Architecture (Relational & Storage Engines). Background process exploration of Checkpoint, Lazy writer, Eager writer, Log Write. Deeper understanding of Log Architecture (WAL, VLF’s, Log Blocks & Log Records). Understanding of SQL Server Indexes and data structure level (B-Tree) level focus towards Heap, Clustered and Non-Clustered indexes. Creating/Managing Clustered & Non-Clustered Indexes including Filtered/Column Store. Identifying/fixing Index fragmentation and resolving page splits using fill factor/pad index. Performing Online Index Rebuild and ensuring weekend Index rebuild jobs succeed. Monitoring Index Usage stats and Index physical stats using DMVs. Strong understanding of ACID, Isolation Levels (Pessimistic and Optimistic). Recommending developers appropriate isolation levels like Read Uncommitted, Read Committed, Repeatable Read & Serializable as per their requirement. Recommending special isolation levels Snapshot and Snapshot Read Committed with additional TEMPDB care and developer requirement. Resolving 100% CPU troubleshooting using appropriate Perform, Counters and DMVs considering factors like High IO, Query load, Cost Threshold & MAXDOP. Identifying/resolving Memory high consumption situations like workload, memory leaks, Out of Memory (OOM) errors. Reporting High IO Utilization and working with storage team to find out reasons behind IOPS performance drop and diagnosing. Benchmarking SQL Server IO performance using SQLIO (Microsoft’s Proprietary tool). Using Perform counters for troubleshooting major bottlenecks CPU, Memory, IO & Network Diagnosing Server slow, Application slow, Database slow performance situations. Monitoring and checking top wait types in SQL Server. Troubleshooting slow performing queries & giving tuning suggestions on Index, Statistics. Resolving Blockings and Deadlocks using Extended Events and giving recommendations on Isolation Levels/Indexes to avoid/minimize blockings/deadlocks. Understanding of Locks also enabling/disabling lock escalation on tables as per necessity. Handling 18456 errors and troubleshooting connectivity issues of Users to SQL Server. Handling space management issues like Log File Full, Data File Full, TEMPDB Full. Implementing best practices through SQL Server Hardening methods. Handling spin locks and understanding of latches. Exploring new features like Resource Governor, Policy Based Management, Auditing, Transparent Data Encryption, Always Encrypted, In Memory OLTP & Query Store.