SEI Idea Portal
|
Description
Problem: TruePACS and EA applications are deploying 80 SQL maintenance jobs. Overlapping functions. Too many jobs to deploy and support well.
Resolution: We need a standard set of SQL jobs that are simple, streamlined and used by all GE applications that have SQL databases. EA, TruePACS, etc.
Standard jobs should work for 95% of customers. If you have a huge customer only then might you customize the jobs to overcome problems. Example: There are new advances as of SQL 2016. You can set a persistent sample rate for update statistics on a table so every time SQL auto updates statistics during the day it uses this higher sample rate.
Mike Arnold created a full set of basic SQL jobs using Ola Hallengren scripts and made them all SQL Always On aware: 1 Integrity Check job for all databases. 1 Full backup job of all databases. 1 Differential backup job 1 Transaction log backup job 1 weekly reindex/update stats job 1 nightly reindex/update statistics job at 20% sample rate. Runs 6 days/week against all databases.
This is not regarding SQL jobs that are application specific such as purge/delete jobs. We understand each application needs a few application specific jobs like "UpdateAllArchives job". Those can stay. |
See ALM failures:
EA ALM 16681 Corrupted SQL backups.
ETP ALM 399
ETP ALM 378
ETP ALM 406
New SQL maintenance jobs for Edison TruePACS sites
GE ServiceCloud Knowledgebase URL describing changes requested:
https://gehealthcare-svc.lightning.force.com/lightning/articles/HCIT_Knowledge_Article/New-SQL-maintenance-jobs-for-Edison-TruePACS
Introduction
Date: 2/15/2023
Edison TruePACS deployments with EA application result in multiple SQL maintenance jobs performing the same backup and integrity check functions on the SQL database server. Due to ALM failures like ETP ALM399 (missing transaction log backups) and other ALM failures the support team at ETP sites will replace all SQL database maintenance jobs for backups and integrity check with 1 set of standard SQL jobs. The jobs will be similar to Hallengren jobs already deployed for the EA application except they will now cover all databases on the server.
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 some databases.
1 Integrity check job for all databases. Runs weekly.
- Replaced 3 jobs. Prevents 2 jobs fighting for Tempdb database.
Long term: We have filed ALM failures with GE engineering. We expect engineering will adopt these or something similar in a later release version of the application. We request all GE applications that use a Microsoft SQL server database to deploy and use the same standard SQL maintenance jobs for easier deployment, reduced training time, and reduced support time.
ALM Failures:
ETP ALM 399 Missing transaction log backups on SQL Always On sites.
ETP ALM 378
EA ALM 16681 Corrupted SQL backups.
Install procedure:
See Ola Hallengren web site for install instructions of Hallengren jobs.
https://ola.hallengren.com/
If EA SQL jobs are already installed then run 2 scripts to modify 2 stored procedures, then create the 4 new jobs. See the 6 attached scripts.
If you need assistance contact the Platform Support team. Email: HCITCCOEPlatformTeam@ge.com
Then remove 25 old jobs with function of backups and integrity checks called:
ims_OCSRI_Production.dbo.IncrementalBackup
Backup Databases Logs
ims_OCSRI_Production.dbo.FullBackup
Disk Backup Databases Complete for Friday
Disk Backup Databases Complete for Monday
Disk Backup Databases Complete for Saturday
Disk Backup Databases Complete for Sunday
Disk Backup Databases Complete for Thursday
Disk Backup Databases Complete for Tuesday
Disk Backup Databases Complete for Wednesday
Disk Backup Databases Differential for Friday
Disk Backup Databases Differential for Monday
Disk Backup Databases Differential for Saturday
Disk Backup Databases Differential for Sunday
Disk Backup Databases Differential for Thursday
Disk Backup Databases Differential for Tuesday
Disk Backup Databases Differential for Wednesday
Tape Backup Databases Complete with Eject
Tape Backup Databases Complete without Eject
Tape Backup Databases Differential with Init
Tape Backup Databases Differential without Init
Integrity Checks Job for DB Maintenance Plan 'SysDBMaint'
ims_OCSRI_Production.dbo.DBCCFull job schedule
ims_OCSRI_Production.dbo.DBCCFast job schedule
DatabaseIntegrityCheck - EA Databases
The 4 new SQL jobs will have names:
AA- Integrity Check All Dbs
AA-Full Backup ALL Databases. Backs up user and system databases.
AA-Diff Backup All User Dbs
AA-TLog Backup All User Dbs
Thank you Mike Arnold for creating the 4 new jobs.
Best Practices for SQL Backups:
SQL backups should be configured to write to 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: See screenshot below). Then test the backup jobs and confirm backups write to file share.
See the GE knowledgebase article for an example of SQL transaction log backup job writing to a directory on a remote 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 customer sites NYP and ValleyHealth 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. Backup problem resolved.
Troubleshooting the jobs:
The 4 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
Addition regarding Reindex and Update Statistics jobs. Date 3/27/2023:
Added two SQL jobs to address problem with reindexing and update statistics Jobs.
GE case 08110500
ETP ALM 406 failure to engineering.
Created:
One Weekly Reindexing Job to handle all User Databases and
One job for Daily Update Statistics
Standard Ola Hallengren Jobs are attached here in the GE KB article.
Version Check of the Ola Hallengren stored procedure should show recent version from Hallengren at least dated 2022-12-03 17:23:44.
Attaching a check version script as well in GE KB article.
Disabled all other update statistics jobs and reindexing jobs at the GE ETP sites.