SQL DBA Level 2 Support

Level 2 Services will be provided for the Databases receiving L2 as listed above. L2 Services will be provided in accordance with the procedures documented in the SOPs and will include:

  • Incident Management Services

  • Change Management Services

“Incident Management Services” mean the Services to resolve (or break-fix, as applicable). An Incident which has no known resolution will include the following:

The below all comes under L2 Level support Service.

  • Resolve and document Incidents
  • Break-fix and provide workarounds acceptable to the User that reported the Incident which do not require database changes
  • As applicable, escalate incidents requiring immediate attention by using escalation guidelines
  • Maintain Incident information in the Service Management System
  • Carry out daily, weekly and other regular reporting activities for Incidents
  • Maintain documentation for Incidents
  • Update and Maintain Incident information and will responsible for maintaining & updating operation handbook / Runbooks as well.
  • Startups/Shutdowns of database servers
  • Additional Space allocation
  • Object maintenance
  • Job Purging/reruns
  • Initial troubleshooting
  • Cluster ware troubleshooting
  • Set up database auditing
  • Database refresh
  • Standby/Mirror database re-instantiation
  • End User account requests (create, delete, reset password, roles)
  • Elevated privileges requests for existing or new roles
  • Elevated privilege challenge process
  • Non-expiry profile requests
  • Vendor software installation; includes RDBMS and related add-on products
  • Database creation, clones, copies and moves
  • Database upgrades as defined by the Refresh Report, Schema maintenance
  • Patch planning scheduling and installations
  • Database decommissioning
  • Implementing backup solutions
  • Scripts/tool based backup
  • Disaster recovery testing
  • Backup and recovery via various tools(e.g. – TSM, SQL Server Native tools/SQL Server,  Ad-hoc backup and restore)

MS SQL Server Upgrade /Migration

There are many ways to migrate the databases, no matter what database version of SQL Server that we are using, below options are the solutions we can use to upgrade and migrate SQL Server instances:

In-place Upgrade: There will be a down time for the application, since this is completely an automated process and will be helpful only in case of upgrade to be perform on the same server or if we are planning to have databases in the same hardware and a must to have the same configuration and Same instance name of the SQL Server, this is completely managed by the Upgrade wizard from Microsoft upgrades for the SQL Server databases and its components. This type of upgrade is mostly used for non-prod instances, if the installation of upgrade is failed then the roll back time is very high.

Backup Restore method: Here the down time for the application is minimal, where we will be backing up the databases and restoring it to new environment in same location on the physical Windows drives as the existing ones, along with the certificates and also creating a database master key in the master database or the database itself is based on the encryption level at the source, then we would have a log shipping or database mirroring enabled from the existing to new environment, during the cutover phase, we will fail it over the new environment and bring down the existing environment, this is the best practice followed across the industry. Otherwise we will be performing a full backup and restore during the cutover phase for most of the databases, if the size of the database is large, then we will take the differential backup, tail log backup from the source server and restore them to destination server and bring the database to ONLINE state, during the cut over phase. The main disadvantage with this method is that, it needs a lot of manual intervention and the time for preparation work is more than the actual execution phase.

SSIS package: This takes a lot of time for the migration and involves a lot of effort. Create an SSIS package will do copy the data from the existing current environment to a target environment, and then it creates a copy to the new database, to compatible with application. This is a time consuming process as it include writing SSIS package to new database, and then using larger database.

Best practice would be to backup and restore the database from one server to another Server for upgrading the databases, but not all servers can be use this approach. Below are the recommendations to migrate the databases:

Build the Windows Server for SQL and install required Latest SQL server Version on the new build server

Best practices for configuration settings can be implemented while performing the SQL Server upgrade or side-by-side migration.

Restore all the User databases from the Source Server to Target Server .

Stop your SQL agent on Target machine.

Move all your logins from source server to target server using stored proc’s sp_hexadecimal and sp_revlogins

Move all jobs from source to destination server by generating the job scripts on the source server.

Move all your DTS and SSIS packages storing it as file system file and copy it to destination server and make the required configuration changes.

Move all Linked servers from source to target server.

Script out replication setup on source server and move the script to destination server and execute the script to configure replication on the destination server.

Migrate and Configure SSRS, SSAS instance databases.

Also need to understand the new features impact (like SQL 2008 Backup compression strategies, Policy Base management, Resource Governor etc..)

Check Long running queries / any performance issues etc.,

Also move Maintenance DBA jobs to the new environment