Request new standard SQL database maintenance jobs to be used by all GE applications that use Microsoft SQL server.

Request adoption of new standardized SQL maintenance jobs for Edison TruePACS sites

We request all GE applications that use a Microsoft SQL server database (ETP, EA application, UV, CCG, etc) deploy and use the same standard SQL maintenance jobs for database backups, integrity check, reindex, and update statistics. This will result in easier deployment, reduced training time, reduced support time, and reduced errors. All jobs need to be made SQL Always On aware so they do not run on secondary server causing an error. We have filed several ALM failures (see list at bottom) with GE ETP engineering due to several errors with the current ETP SQL maintenance jobs.

GE support team is temporarily replacing most ETP SQL maintenance jobs at customer sites. Engineering was notified February 15, 2023. Due to ALM failures like ETP ALM399 (missing transaction log backups caused customer down time) and other failures the GE support team will replace all SQL database maintenance jobs for backups, integrity check, reindex, and update statistics with 1 set of standard SQL jobs at all ETP application sites. The jobs will be similar to Hallengren jobs already deployed by the EA application except they will now cover all databases on the server. We expect engineering will adopt the 6 new SQL jobs created by GE Support team or something similar in a later release version of the ETP and EA applications. From agreement with Vijay Arlagadda on April 14, 2023 we hope for standardized SQL jobs from engineering team as of ETP version 8.0 SP3.

Details (GE)

After replacing the SQL jobs the result will be:
1 Full backup job for all databases. Runs daily.
- Replaced 12 jobs that may go to 2 different backup locations (that was a problem).
1 Differential backup job for all databases. Runs every 6 hours.
- Replaced 7 jobs
1 Transaction log backup job for all databases. Runs every 1 hour.
- Replaced 2 jobs that may go to 2 different backup locations. Old jobs missed 3 databases causing 2 customer down times.
1 Integrity check job for all databases. Runs weekly.
- Replaced 3 jobs. Prevents 2 jobs fighting for Tempdb database.

1 Weekly (weekend) reindex and update statistics job.

Replaces all reindex/update statistics jobs.

1 Nightly job for update statistics. Runs every weeknight.

Replaces all update statistics jobs.

The 6 new SQL jobs (see attached jobs) have temporary names like:
AA- Integrity Check All Dbs Performs integrity check of all databases.
AA-Backup Full ALL Databases. Backs up all user and system databases.
AA-Backup Diff All User Dbs
AA-Backup Tlogs All User Dbs
AA - Weekly Reindex Update Stats User Dbs Weekly reindex and update statistics.

AA- Update Statistics User DB – Daily Nightly update statistics.

Thank you to Mike Arnold for creating the new jobs. Hari Cheppela (of ETP engineering Motley Crue team), Jim Rainville, and Sue Waters modified and tested the jobs for use on SQL cloud deployment. The jobs are SQL Always On aware so they will not attempt to run on the secondary database server until that server takes over the primary server role in the cluster.

Version of the SQL backup jobs intended for Amazon cloud servers and able to back up to Amazon S3 storage are:

AA-Backup Full ALL Databases-Cloud S3-Steps

AA-Backup Diff All User Dbs-Cloud S3-Steps

AA_Backup Tlogs All User Dbs-Cloud S3-Steps

Install procedure:
See Ola Hallengren web site for install instructions of Hallengren jobs.
https://ola.hallengren.com/

Engineering team can use/modify the attached 6 standard SQL jobs or engineering can create their own jobs. Like the GE Support team jobs any replacement job should be simple, streamlined, and standardized across all GE applications. Prevent redundancy, overlap, and conflict from SQL jobs deployed by multiple GE applications whose jobs perform the same basic function. Version check of the Hallengren stored procedure should show current version at least 2022-12-03 17:23:44

Attaching a check version script as well.

All jobs must be SQL Always On aware so they first check to make sure this database server is the primary cluster server else don’t run.

Process to install:

If EA SQL jobs are already installed then run 2 scripts to install 2 stored procedures, then create the 6 new jobs. See the 6 attached scripts.
If you need assistance contact the Platform Support team. Email: HCITCCOEPlatformTeam@ge.com

Best Practices for SQL Backups:
SQL backups should be configured to write to an off server location like a secured remote file share. This prevents data loss from historical scenarios where the database drives fail and the backups were on the same local server drives or Ransomware virus attack encrypts the database drive.
Requirements:
1.) The SQL Server (MSSQLServer) Service should be set to use a domain user account whenever possible.
2.) That SQL domain user account plus 1 GE Support account plus 1 customer user account must be given privileges to read/write to the remote file share where backups will be stored. The file share should be secured against access by other user accounts.
3.) Edit the SQL backup jobs to change "@Directory" line to point to the remote file share (Example: \\BackupFileShare\Edison_PACS\Backup). Then test the backup jobs and confirm backups write to file share.

Note: This new backup job no longer appends to 1 EA backup file for all full and differential backups. Instead each database creates its own subdirectory and stores full backups, differential backups, and transaction log backups in this folder and subfolders. This was done to fix a failure in the EA backup at 3 customer sites NYP, ValleyHealth, and Huron where frequent and sporadic network interruptions corrupted the 1 backup file 70% of the time. It was not a problem with the Hallengren jobs. It was a problem due to GE had chosen to write all backups appending to 1 backup file. This has been corrected in these new backup jobs.




Troubleshooting the new SQL jobs:
The 6 new SQL jobs have "log to table" property checkmarked. They save job history output to the MSDB table sysjobstepslogs. Query the table for extended information from your job like:

SELECT TOP (1000) [log_id]
,[log]
,[date_created]
,[date_modified]
,[log_size]
,[step_uid]
FROM [msdb].[dbo].[sysjobstepslogs]
where log like '%AA-Tlog%' --insert your job name here

ALM failures created for engineering team on the old SQL jobs deployed with ETP application version 8.0:
EA ALM 16681 Corrupted SQL backups due to network interruption.

ETP ALM 399 3 databases missing transaction log backups. Caused customer down time.
ETP ALM 378 80 SQL maintenance jobs. Need to simplify and streamline.

ETP ALM 406 ETP reindex job failing due to SQL page level lock setting.

ETP ALM # ? Another failure is in process of creation. ETP SQL jobs were not made SQL Always On aware. New jobs fix this.

GE Support disabled and replaced all other update statistics jobs and reindexing jobs at GE ETP sites where they were erroring. These can be temporary steps until GE engineering standardizes and simplifies the SQL maintenance jobs so 1 set of SQL jobs can be used for all GE SQL databases (applications EA, ETP, UV, CCG, etc). Example sites are Grand River and HCA Meadows.