ankur patel

SUMMARY:

Well experienced Database Administrator with 12 years of IT experience in Database Administration, Design, Development, Installing and Support of MS SQL Server 2016-2000 and MS SQL Azure (IssS, PaaS, Saas) in Production, Testing and Development Environments.

Heavy Experience in Database Migration from MS SQL server 2000-2008 to 2014-2016.

Experience in Implementing, Upgrading, Migrating to SQL Server from versions 2005 up to version 2016 and SQL Server on Azure virtual machines

Heavy experience in developing the variety of proof of concepts for SQL Server features testing and future implementations specific to the existing environments and limitations

Implemented AlwaysOn availability groups and replicas for reporting, backup and performance

Expert in managing SQL Server in Physical and Virtual Environments VMWare vSpheare 6.0

Experience in implementing different types of Replication topologies involving Snapshot, and Transactional replication.

Expert in SQL Server Clustering, implementing Active/Active and Active/Passive clustering

Expert in monitoring SQL Server with Diagnostic Manager, FOGLIGHT tools and native tools

Expert in Installing and configuring SQL Server Cluster in Active/Passive and Active/Active environments.

Implemented Sync and Async MIRRORING for Disaster Recovery Implementation

Implemented Backup and Restore in SQL Server using Native SQL Backups and also using Third party tools like Veritas NetBackup, Sandpiper, TSM and LITESPEED.

Expert in using Ticketing and Change Management software.

Expert in Using extensive SQL Server DMV's for performance tuning and Monitoring

Experience in Performance Tuning, Query Optimization, Database Consistency Checks using DBCC Utilities, Linked Servers.

Expert in implementing Full, Differential, Log and Compressed backups

Experience in using different Transformation tools like DTS Packages, SSIS and also, BCP and BULK INSERT.

Expert in Creating, Deploying, Scheduling SSAS objects in SQL server 2008 and higher versions

Experience in creating reports in SSRS and deploying in various servers

Has good knowledge in implementing Log Shipping, Data Mirroring Techniques.

Experience in Software Development Life Cycle (SDLC) and configuring Alerts using SQL Mail.

Expertise in planning and implementing MS SQL server Security and database Permissions.

Expert in T-SQL writing and developing Database Objects, Triggers, stored procedures, Functions and Views.

Sound experience of writing PowerShell scripts

Experienced in Loading data into multiple tables using DB Management Tool like SQL*LOADER, Import /Export.

Experienced in providing quality operational Support for Production Environments

Provided 24/7 database maintenance and production support.

EDUCATION AND CERTIFICATIONS

Bachelors earned in 2001, Commerce and Applied Computer Science

Masters earned in 2003, E-Commerce and Management

MICROSOFT CERTIFICATIONS:

Microsoft® Certified Solutions Associate: SQL Server 2008R2

Microsoft Certified Professional

Microsoft® Certified Technology Specialist

SQL Server 2008R2, Business Intelligence Development and Maintenance

Microsoft® Certified Technology Specialist

SQL Server 2008R2, Implementation and Maintenance

TECHNOLOGIES:

Operating Systems: Windows Server 2003/2008/2012, VM Windows XP/7, Citrix Xen Server, Terminal Server

Database: MS SQL Server 2005/2008/2012/2014, 2016, Azure, 2000, DB2, oracle 10/11g

Database Tools: Enterprise Manager, SQL profiler, Power shall, Nagios,

Reporting tools: SSRS, Tableau and Microsoft Reporting Server

SQL server Tools: SQL profiler, Microsoft Reporting Server, MS Visio and XML, Avamar, LightSpeed

Software & Application tools: Light Speed, Idera, Tivoli

System/Network monitoring: HP Open View, March NT, Bind view, BMC monitoring tools IDERA.

PROFESSIONAL EXPERIENCE:

UNIVERSITY OF PENNSYLVANIA, PHILADELPHIA PA JULY2015-PRESENT

LEAD DATABASE ADMINISTRATOR

Upgrade and migration are two major definitions of this assignment at the University of Pennsylvania. Responsible for performing phase-by-phase migration of the SQL Server 2000 legacy databases and instances to the latest SQL Server version 2014 in addition to the Windows OS upgrade to 2012R2. This slow moving project is underway due to its complex nature of databases tied up with the outside of the world and involves many external dependencies to resolve on the way of the migration. Environment is physical servers on two separate sites with DR implementations.

Design, document, and promote security "best practices", policies, standards and protocols.

Migrating application code and objects from development/test environments to production.

Working with development and systems teams to perform application and database tuning.

Engaged with development teams to define and maintain database and code standards and naming conventions for the organization to ensure consistency in database and code development.

Migrated legacy applications and databases from SQL Server 2005 to SQL Server 2014 in a standalone servers

Setup and maintenance of appropriate database and application monitoring.

Setup, test and maintain business continuity/disaster recovery infrastructure database and applications.

Providing highly available database and application infrastructure according to Business Continuity requirements using SQL clustering, load balancing, etc.

Established and configured the AlwaysOn with two secondary readable replicas AAG

Evaluating data modeling, database administration, and database backup and recovery tools and make recommendations.

Setting up and Monitored transactional replication, Database mirroring on Production and QA servers.

Consolidated databases and removed redundant databases from the old inventory

Designing and developing SQL Server Database, Tables, Indexes, Stored procedures, Views, Triggers.

Design/Build Extract Transform Load procedures in SQL Server Integration Services

Worked on creating SSIS packages to upgrade SQL Server to SQL Server 2012

Troubleshooting ODBC and OLEDB connection issues in SSIS packages.

Took part in the preparation of Project plan, user acceptance testing. Got a detailed understanding of business functionality and took the responsibility of preparing the Functionality.

Developed, deployed and monitored SSIS Packages including upgrading DTS to SSIS.

Got a detailed understanding of business functionality and took the responsibility of preparing the Functionality Test Plan which was used by testing team while doing their testing process.

Designed the packages in order to extract data from SQL db, flat files and loaded into Oracle database.

Environment: Windows Server 2003-2008, SQL Server 2005-2014

DEPARTMENT OF EMPLOYMENT SERVICES, WASHINGTON DC SEPTEMBER 2015 – APRIL 2016

LEAD DATABASE ADMINISTRATOR

Datacenter migration and Consolidation for the agency’s critical applications managed by the vendors hosted in the agency’s data centers involving IRS and SSN agency data.

Managing over 12 TB of production data and over 75 SQL Server VMs hosted in two data centers.

Production support for SQL server environment.

Managed SQL Servers in HA and DR environments using Mirroring and AlwaysOn features

Installing and configuring of SQL Server 2014-2012 in stand alone or cluster environments

Performed upgrade and Migration of lower version SQL Servers to SQL 2014-2016

Configured SQL Server 2012 AlwaysON availability groups for high availability

Configured and maintained database mirroring for true DR purpose.

Implemented different types of Replication Models like Snapshot and Transactional.

Extensively worked on Dynamic Management Views (DMV's).

Migrated SQL Server 2005/2008 to SQL Server 2012/2014.

Patched server with latest SQL Server service packs and Cumulative updates.

Managed Microsoft Active\Active and Active\Passive Clustered SQL Server 2008/2012 environments.

Installed and Implemented Async and Sync Mirroring / Log Shipping for Disaster Recovery

Performed DBCC utilities and fixed data corruption in User databases.

Worked on monitoring and tuning of SQL scripts. Created upgrade scripts for production database and supported it.

Created and managed schema objects such as tables, views, indexes, procedures, and triggers & maintained Referential Integrity.

Extensively used the PowerShell Scripts for daily Database Administration

Performed Server & Application fine-tuning & troubleshooting.

Monitored MS SQL Server performance and network issues.

Developed, deployed and monitored SSIS Packages tool of SQL Server to populate data from various data sources, created packages for different data loading operations for application including upgrading DTS to SSIS.

Helped Development Team in deploying and testing the application, which uses SQL Server as a database.

Monitored Databases for lock escalation, dead Lock, resource utilization by sessions, package caching, buffer pool hit-ratios, Captured long running SQL/runaway queries.

Maintained Log Shipping, Mirroring and SRDF for disaster recovery.

Scheduled the backups for Databases and Transaction log. Performed restoration operations. Suggested backup strategies.

Controlled user access rights, and managed data security.

Involved in trouble shooting, Performance Tuning, Query Tuning, Index Tuning using DMV's, Wizards and SQL Profiler.

Implemented Backup and Restore in SQL Server using Third party tool VERITAS Net Backup, SRDF, TSM and LITESPEED.

Involved in 24x7 on call support.

Environment: MS SQL Server 2005/2008/2008R2/2012/2014, Windows 2008R2/2012, VERITAS Net Backup, TSQL, Litespeed, SQL Job Manager, SQL Compare.

METLIFE, CARY NC JANUARY 2015 - JULY 2015

LEAD SQL DBA AND MICROSOFT DSE

The project involves the re-platforming and migrating one of the largest application called Metlife.com into SQL Server 2014. Alongside environment wide massive SQL Server 2014 upgrade, we also evaluate, analyzed and identified the existing candidates (the current applications, secondary servers holding Logshipping, existing SQL replications, mirroring, SRDF) who can be a potential candidate for the Always ON AAGs from the manageability, performance and cost prospective.

Responsibilities:

Involved in migration from MS SQL Server 2000 and SQL Server 2005 to MS SQL Server 2014.

Migrated SQL servers on Standalone and Clusters 64 bit, SQL Server 2014 installed on Clusters 64bit four node clusters. Applied SQL Server Service packs, Hot fixes (Cumulative updates) on production/DR/QA servers.

Responsible for configuration, tuning and provide 24X7 dedicated support, done maintenance windows to all Production, development, QA and clustered servers.

Implemented and maintained Failover clustering (Active/Passive node) and Replication (Snapshot and Transactional, Transactional with Updateable) as databases high availability solution.

Creating a virtualized Failover Cluster, create the virtual machine, install windows on the VMs, create a domain controller and ISCSI target, and configure the cluster nodes.

Involved in documentation and implemented a disaster recovery system.

Performed all aspects of database administration, including data modeling, backups and recovery.

Worked on Security Issues such as creation and granting permissions on various Objects. Assign permission to user (s), role (s), and object.

Analyzed long running slow queries and tuned the same to optimize applications and system performance and Performed DBCC Checks.

Develop standards for data warehouse operations, programming, and security.

Develop database architectural strategies at the modeling, design and implementation stages to address business or industry requirements.

As a gatekeeper to production, reviews, coordinates and implements changes, assess the effectiveness of the environment, recommends and follows through on implementation of improvements.

Design databases to support business applications and develop database applications, designing, coding, testing, and implementing complex non-standard programs and scripts.

Applying data fixes based upon the incident request by App team members.

Handled Table and Index partitioning for 1TB size of databases.

Diagnosed and tuned the server optimal performance using SQL Server Dynamic Management Views (DMV), SQL Profiler and Index Tuning Wizards.

Rebuild Indexes and Tables as part of performance tuning exercise

Successfully migrated data to new location using Snapshot Replication, and implemented Merge Replication to support up to date record of Inventory Information.

Overall database design, such as creating normalized databases, authentication, query tuning, assigning proper indexes, creating stored procedures, functions, triggers and data types using T-SQL Statements.

Hands on experience with Bulk Insert, BCP and Database consistency check using DBCC commands.

Configured Transactional replication between Primary Server and three Read-Only servers, with separate Distributor server and experience in troubleshooting replication problems.

Developed ETL packages with different data sources (SQL Server, Flat Files, Excel source files, XML files etc) and loaded the data into target tables by performing different kinds of transformations using SQL Server Integration Services (SSIS)

Monitored and report generation for the database infrastructure and these reports are submitted to management on regular basis.

Reconfigured the default locations of the data files, log files and error log files onto the SAN environment for all the servers.

Designed and implemented comprehensive Backup plan and rewriting the code to use SQL Native commands for SQL Server 2008 R2 versions

Monitoring Double-take console to check the status of mirroring/replication of backup files cross the network

Reviewing DBA Daily status report to troubleshoot the low disk space, consistency checks, server configuration and DB growth in proactive and submitting to Manager.

Participated in Primary On-Call rotation, responsible for the prod releases, supporting app teams in case of assistance, applying patches, server verification and monitoring maintenance jobs

Responded to issues that have been escalated with Change Control Processes and adherence to standards and documentation.

Environment: SQL Server 2000/2005/2008/2008R2/2012, MS Access, Windows 2003/2008/2008R2, SSRS, SSAS, SSIS, Commvault, Control-M, Idera monitoring tool, HPSM ticketing system, SharePoint 2007 and 2010.

OHIO DEPARTMENT OF INSURANCE, COLUMBUS, OH AUGUST 2014 – FEBRUARY 2015

LEAD SQL SERVER DATABASE ADMINISTRATOR

The migration of multiple instances of SQL servers to an MS SQL Server 2014 along with the updating Windows Server to 2012R1, consolidation of SQL servers, databases and services. Evaluating current performance issues on SharePoint and other instances on a network. Neutralize the server performance to an acceptable level as defined by the SLA and the client IT amendment. Consolidating the existing servers with the unified environment along with the other OH state agencies on a one centralized datacenter.

Responsibilities:

Managing SQL Servers in development and production and QA environment.

Install SQL Server Management tools using SQL Server Setup Program. Test the Installation of SQL Server.

Automation of data exports/imports using SSIS.

Created Database Maintenance Planner for the Performance of SQL Server, which covers Database Integrity Checks, Update Database Statistics and Re-indexing.

Maintained production database, along with supporting development and testing environment.

Troubleshoot production database server, user administration, configure SQL Server, creating databases for development and testing environments, capacity planning, performance tuning and database monitoring.

Have worked on Log shipping to create Stand-by Server.

Database and Log Backups and restores, backup strategies, scheduling backups.

Remote configuration, fine tuning of database objects and server

Job Monitoring and making sure all jobs ran successfully every night.

Managed MS SQL Servers in Virtual Environments using VMWARE.

Creating and managing schema objects such as tables, views, indexes, procedures, triggers.

Perform recovery of the database. Import tables schema as requested.

Migrated the entire SQL server pool host from SQL Server 2000 to SQL Server 2012.

Creating tables, views, indexes, stored procedures, triggers and constraints.

Used Change Data Capture to record inserts, updates and deletes.

Monitoring and fixing replication issues.

Security Implementation. Administering the MS SQL server by creating users logins with appropriate roles, dropping and locking the logins, monitoring the user accounts, creation of Groups, Granting the Privileges to Users and Groups.

Testing of applications for performance, data Integrity.

Used import\export wizard to import and export data.

Designing SSIS packages for the data transfer from the customer feedback to databases for various purposes.

Deployed Functions, Triggers, Stored Procedures, Databases, and views

Environment: SQL Server 2000/2005/2008/2008R2/2014, MS Access, Windows 2003/2008/2008R2, ASP.Net, ADO.Net, VB Script, SSRS, SSAS, SSIS, NetBackup,, Team foundation server (TFS), Windows 2007R2 Communicator, BMC Ticketing System, ER Studio, SharePoint 2007 and 2010.

SCOTTS LAWN SERVICES, COLUMBUS, OH SEPTEMBER 2013 – JULY 2014

LEAD MS SQL SERVER DBA and SME

Responsibilities:

Providing 24/7 support for critical production databases.

Providing Primary DBA responsibilities for around 10 applications.

Migrated the SQL Server 2000/2005 to SQL Server 2008 and 2008 R2 versions.

Managed 200 databases and few huge databases which are more than 5TB.

Installed of MS SQL 2012 on production, Test and Development boxes.

Working on Projects to provide DBA support for building SQL Servers for new applications begin to end support

Monitoring the data files, log file space issues, blockings, Tempdb space issues.

Planning for backup and recovery of database information and maintaining Hot Backups, Cold Backups.

Working with BMC Patrol Monitoring tool and lite speed Backup tool.

Administering the MS SQL Server 2012, 2008, 2005 2000 by creating user logins with appropriate roles, dropping and locking the logins, monitoring the user accounts, creation of groups, granting the privileges to users and groups.

Involved in General Database Administration, Query Performance tuning.

Manage SSIS packages, stored procedures, jobs, and utilize Server Side tracing to identify performance issues with deadlocks other locking issues. Performed Database consistency checks with DBCC Utilities like DBCC DBREINDEX, DBCC INDEXDEFRAG, Index tuning and monitored error logs.

Developed few reports using MS SQL Reporting Services.

Refreshes from Production backup to Stage and Development environments.

Developed DR plans and automated failsafe backup remediation modules for accurate backup monitoring and reporting / Ad hoc reporting. Worked closely with development team for performance enhancements, code reviews and provided vital inputs in terms of index optimization, schema structures, data changes, file layouts etc.

Extracted data from various sources like SQL Server 2005/2008/2012, Oracle, Teradata, .CSV, Excel and Text file from Client servers and through FTP.

Environment: SQL SERVER 2005/2008R2, SSIS, SAP, OS-Windows 2003 Advanced Server, DB2, Tableau, Oracle 11g.

EXELON ENERGY, KENNETT SQUARE, PA JULY 2012 – AUGUST 2013

SR. MS SQL SERVER 2005/2008R2 DBA

Responsibilities:

Installing and configuring of SQL Server 2012 /2008R2 in newly configured stand-alone and Cluster Environments.

Configuring SQL Server 2012 Always-on availability groups with one secondary replication windows server 2008R2/2012.

Installing SQL Server 2014 on test servers for testing purpose.

Implementing and managing the clustering environment like Active/Passive.

Working on side by side migration, migrated SQL server 2008r2 databases and ETL packages to SQL Server 2012 using team foundation server.

Working on high availability SQL Server solutions, including Log shipping, Mirroring.

Implementing and maintaining Replication (Transactional, Snapshot).

Working on rebuilding SQL Server due to unexpected crash by Rebuilding Master Database in SQL Server as a part of recovery process and also working on moving system databases from existing drive to new drive as per the requirement.

Configuring Database Maintenance Plans for Backups, Re-indexing, and Reorganization of Indexes, Cleanup History, Check DB and Update Statistics. Scheduling weekly jobs and Maintenance tasks for better performance and created alerts with Database Mail SMTP.

Creating proxy accounts to lock down the permissions to the users were the SSIS packages runs under the proxy account.

Installing and configuring master data services (MDS), SQL Server Data Quality Services (DQS) in SQL server 2012.

Creating new integration service catalog (ssisdb) used for monitoring and managing SSIS projects.

Involving in Capacity Planning and adding new storage space as needed.

Working on purge process as a part of database maintenance.

Using native tools like SQL Server Profiler, Performance Monitor, Activity Monitor, index tuning wizard, database tuning advisor for performance analysis to optimize queries and enhance the performance of database servers.

Working on tuning SQL queries by adding the missing indexes.

Pro-actively monitoring and managing SQL Server performance like disk space issues, Log file full and backup failures.

Monitoring Event Viewer, SQL Error logs and Log File Viewer for Software and hardware related errors.

Also working on Powershell scripts.

Developing Powershell scripts for pulling out audit information from SharePoint site.

Involved in the developments of SharePoint custom solution and deploy solution using PowerShell scripts and in creation of custom migration solution for small & medium sized Lotus applications to SharePoint.

Working on call 24/7 Support on weekly basis.

Environment: MS SQL Server 2005/2008/2008R2, SSRS, SSIS, Citrix Xen Server, XenApp Client, Nagios, Oracle 10g/11.0, Idera.

HEALTH DATA MANAGEMET SERVICES, IL FEB 2011 – JUNE 2012

MS SQL SERVER SENIOR DBA

Responsibilities:

Automating the invoice processing system and eliminating manual process. This project involved in implementing a COTS product called model-N. This system is used by Sales, Finance and Accounts Receivable staff and is integrated with SAP system.

Supported 300 plus databases in clustering environment (A/P & A/A) including database mirroring/Log shipping on critical databases having 2+ TB in size.

Performed successful Migrations SQL 2008 to SQL 2012 and Stand alone to cluster server.

Managed successful migration of critical production and contingency servers with no impact to business.

Optimized Data center migration & consolidation by employing various migration strategies; such as virtualization, migration and server consolidation.

Implement Disaster Recovery drills and Document.

Build and maintain SQL scripts, indexes, and complex queries for data analysis and extraction.

Setup and configured Database mirroring in High Performance, High availability mode as standard DR strategy.

Troubleshooting high availability and disaster recovery scenarios involving Clustering, Database Mirroring, Log Shipping and Replication.

Automated daily database refreshes using Power shell from production to staging and on-demand refreshes from production to test environments, involving SQL versions […]

Create and Develop Admin Reports of SQL inventory, Objects modified Logins and Error reports.

Implemented data partitioning to improve the performance of large table reduces contention and increases availability of data.

Implement and test Database Backup to a Data duplexing device and confirm RPO and RTO to implement retention of backup and storage.

Environment: MS SQL Server 2005, SSIS, SSAS, SSRS, T-SQL, DTS, Win2003, Tableau, SAS, Quest, SQL Sentry performance advisor

WILMINGTON FINANCIAL, PLYMOUTH MEETING, PA JUNE 2007 – DEC 2010

MS SQL SERVER DBA

Responsibilities:

Involved in upgrading the instance of development environment of SQL Server 2005 service to SQL Server 2008.

Created logical and physical database design, data structures, and implemented databases.

Developed, designed and documented tables, view, stored procedures, advanced queries, triggers, indexes and functions.

Experience in Database Design activities including Data Files and File group organization, Capacity Planning, Data Partitioning and Data Normalization.

Implemented Database Mirroring in SQL 2005/2008.

Successfully implemented Log Shipping in SQL Server 2005/2008.

Analyzed business recovery requirements and maintained strategies and plans for critical databases.

Used Performance Monitor and SQL Profiler to optimize queries and enhance the performance of database servers.

Wrote T-SQL stored procedures and functions to efficiently perform complex business logic to summarize data.

Migrated databases and database objects between multiple servers across production, QA and development platforms.

Involved in Performance Tuning, Indexing, Query Optimization.

Involved in resolving locking and blocking issues.

Environment: SQL Server 2005/2008 Enterprise Edition, Windows Server

2003/2008 Enterprise edition, Dell Power-Edge M710

INFORMATICS TECHNOLOGIES PVT LTD, INDIA JUNE 2003 – MARCH 2007

DATABASE ADMINISTRATOR

Responsibilities:

Developing T-SQL queries, triggers, functions, cursors and stored procedures.

Responsible for logical and physical design of SQL Server databases.

Used DDL and DML for writing triggers, stored procedures, and data manipulation.

Created views to restrict access to data in a table for security.

Expert in extracting data using SSIS from OLTP to OLAP.

Created linked server to Oracle 10G and imported data to MS SQL Server 2005.

Performed Bulk data migration using DTS and SSIS from flat files.

Designed and optimized all the SSIS packages.

Created SSIS packages to Export data from text file to SQL Server Database.

Designed and implemented stylish report layouts.

Implemented full and differential backups and also created and managed DB objects.

Make recommendations for File group Creation and Placement.

Scheduling backups and automating weekend maintenance tasks.

Created users and roles for login authentication and validating permissions.

Administering MS- SQL Server, performing day-to-day tasks such as Database tuning.

Upgraded SQL Server 2000 to SQL Server 2005.

Assisted developers in tuning Stored Procedures, Triggers and Functions over performance issues.

Environment: MS SQL Server 2005/2000, Oracle 10G, T-SQL, MS Integration,

Analysis and Reporting Services, Query Analyzer, Windows 2003 Server.

Contact this candidate