6
Sep

SQL Server migration and consolidation project

Pengele staff were contracted to assess, improve and execute a plan to upgrade and consolidate SQL server instances throughout the corporate environment. Pengele was brought on and given almost no turnover from the retiring DBA staff. Pengele staff met with the CIO to understand need and vision of the department, from these meetings project plans were created and published.

Technologies used

  • SQL Server 2012 with AlwaysOn Availability Groups (AAG)
  • SQL Server 2008 R2 Fail Over Clustering (FOC)
  • SQL Server 2005 FOC (legacy versions to be upgraded)
  • Idera SQL Safe, Idera Diagnostic Manager
  • ESX vSphere and ESX hosts (data center license purchased for hosts)
  • SQL Server Reporting Services (SSRS)
  • Microsoft GreatPlains / Dynamics

Below is a list of goals and the approach Pengele staff took to meet the goals

Upgrade all SQL server instances to the most current version the connecting front end application will support

Pengele met with all the power users / application owners from the business side to create a list of software applications being used. From this list we either called the vendor directly to obtain current software contracts or got the information from the power users. We established application support contacts and started verifying target SQL Server versions.

Consolidate as many SQL instances as possible while maintaining the appropriate segregation 

Pengele worked with information security, network and infrastructure to identify and create zones for each SQL cluster. Example: public zone – these databases are accessible to the public facing web applications, enterprise zone – these databases are accessible to the company staff and trusted external vendors, corporate zone – these databases are only used to run the back office operations and are only accessible to onsite staff.

The environment started with 30 SQL instances on a mixture of physical and virtual servers. Pengele worked with infrastructure to create new VMs in the appropriate zones, we worked with the network team to create firewall rules from the appropriate web applications to the new SQL server destinations, we worked with systems team to get the appropriate DNS entries for the cluster names and AAGs. Pengele consolidated 2008 and 2012 into the same clusters in each appropriate zone, migrated the databases and all dependent objects to the new destinations and worked through all technical issues that came up from migrations. Pengele coordinated database move schedule with business units and performed work during acceptable planned outages.

At the end of this project SQL 2005 was successfully removed from the environment and all applications were using the most current version of SQL Server supported.

Create policy and procedure for managing and maintaining SQL Server instances

Pengele documented all SQL installations and configurations, these were used as the standard install procedure if / when more SQL clusters need to be introduced to the environment. We introduced the concept of managing instances through one central location (mgmt server) and not RDP’ing to each production server desktop. Idera SQL Safe was used to manage all SQL sever backups. Idera Diagnostic Manager (DM) was used to diagnose and report on SQL server internal contention if when it occurred. During the implementation of DM Pengele identified several slow running stored procedures and tuned the code or created the appropriate indexing to eliminate the deadlocking alerts being generated.

Interview and make hiring recommendations for new DBA staff

During this project Pengele staff worked with the business to identify the appropriate DBA candidates for their environment. We developed a set of interview questions relevant to the environment to ensure the staff being hired were the right fit. At the end of the project all database technologies; MySQL, Oracle and SQL Server were consolidated to one team. (staff of 4 DBAs)