jeddah database servers
TRANSCRIPT
Database Servers at KFSHRC
Jeddah
Background
• There are total 13 database server managed by Technical Support Team in Jeddah.
• They are consist of MS SQL servers and Oracle database servers.
• All the database servers are virtual servers.
• Database backup infrastructure is consist of Local backup, Veeam backup and IBM TSM.
Database Server List
S. No. Server IP Server Name DatabaseInstance
Name
1172.16.37.
75 JEDMHS-SQL75AMicrosoft SQL Server
2008 R2MSSQLSERVE
R
210.11.20.7
9 JEDMHS-HVINTF79Microsoft SQL Server
2008MSSQLSERVE
R
310.11.20.2
14 JEDMHS-VSQL214AMicrosoft SQL Server
2012MSSQLSERVE
R
410.11.241.
36 J-SMSSQL-APP-01Microsoft SQL Server
2012MSSQLSERVE
R
510.11.20.2
17 J-MYCR-ORA-01 Oracle 11G ORACLE
610.11.20.1
27 J-QMAT-DB-01Microsoft SQL Server
2008 R2MSSQLSERVE
R
710.11.20.1
28 J-QMAT-DB-01Microsoft SQL Server
2008 R2MSSQLSERVE
R
8 10.11.32.5 J-VMW-SQL01Microsoft SQL Server
2008 R2MSSQLSERVE
R
910.11.20.2
46 J-ENDOB-APP-01Microsoft SQL Server
2005MSSQLSERVE
R
1010.11.20.1
55 J-PESRMicrosoft SQL Server
2008 R2MSSQLSERVE
R
1110.11.20.1
57 J-PESDBMicrosoft SQL Server
2008 R2MSSQLSERVE
R
1210.11.20.2
48 J-BSTAR-DB01Microsoft SQL Server
2012MSSQLSERVE
R
1310.11.20.2
43 J-BLDBK-DB-01 Oracle 11G HEMO
Database Server Details• JEDMHS-SQL75A: This Server is consist of 35 production databases. Most
of the database are in-house application database.
S. No Database Name Details1 ABACUS DB for Inpatient Pharmacy2 AIMSDatabase DB for BIOMED application3 aspnetdb DB for development4 CARDIOLOGYSQL Cardiology Patient Information5 CMS Portal Application 6 DISTMODEL Venus7 DolphinPlatform DB for Dental Department8 ELIGIBILITY-SQL MCE9 EVENT Event Registration10 FUSION HLA Fusion application11 GOVERMENTRELATION Government Relation12 HO Hospital Operations13 HRSQL Recruitment14 INFECTIONCONTROL Infection Information15 ITA_INVENTORY DB for Inventory system (ITA)16 iVisionMT3 DB for Attendance system17 JRO Jeddah Royal Orders18 KFSHMCD DB for Communication department19 KFSHMESSAGE DB for ITA Quick Message Center20 LNSSScanResults10 GFI lan Guard21 MDictionary DB for Oncology22 MEDICALRECORDSQL Tracking Doctors Progress23 MRegistry DB for Oncology24 ORDATABASE OR Application25 PHONEDIR Contact Information26 PHYSICALTHERAPY Physical Therapy software27 PROTOCOLDBSQL VIP Patient Information28 PROTOCOLDIR VIP Patient Contact Information29 RRTCOMMITTEE Record Review System Database30 SHARIADATABASE DB for Sharia System (Patient Affairs Dept.-Sharia Section)31 SOCIALSERVICES DB for Social services32 Store Database for store application33 Takhassusi DB for Takhassusi application34 RRA DB for Records35 FOODSERVICES DB for Food Services application
Database Server Details• JEDMHS-HVINTF79: This Server is consist of 4 production databases.
These databases are used for ETL process between Riyadh and Jeddah databases.
• J-SMSSQL-APP-01: This server has 4 production databases. This server is used by the Awalnet SMS notification application.
• J-MYCR-ORA-01: This is server is consist of one instance named ORACLE. The server is used to extract old Mycare Images. It has two schemas.
S. No Database Name Details1 INTFACE DB for Cerner Venus interface2 NEWINTFACE DB for Cerner Venus interface
3INTERFACE_MODIFIE
D DB for development4 ApolloIntface DB for Apollo interface
S. No Database Name Details1 SMSTest DB for SMS test2 aspnetdb DB for development3 SMSCentral DB for SMS application4 SMS_Alerts DB for SMS web application
S. No Schema Name Details1 pbcibill DB for mycare images application2 PBCIIMG DB for mycare images application
Database Server Details• J-QMAT-DB-01(Default instance): This Server is consist of 3 production
databases. These databases are used for Qmatic application.
• J-QMAT-DB-01(Qmatic instance): This Server is consist of 3 production databases. These databases are used for Qmatic application.
• J-VMW-SQL01: This is server is consist of one production database and its is dedicated for VMware Vcenter server.
S. No Database Name Details1 elkdb DB for Qmatic application2 jbossdb DB for Qmatic application3 wookiedb DB for Qmatic application
S. No Database Name Details1 elkdb DB for Qmatic application2 jbossdb DB for Qmatic application3 wookiedb DB for Qmatic application
S. No Database Name Details1 VCDB DB for Vmware Center server
Database Server Details• J-ENDOB-APP-01: This Server is consist of one production databases. This
server is dedicated for Endobase application.
• J-PESR: This Server is consist of 2 production databases. These databases are used for Pyxis reporting application.
• J-PESDB: This is server is consist of 4 production database and its is used for Pyxis application.
S. No Database Name Details1 ENDOBASE3 DB for Endobase Application
S. No Database Name Details1 DSServerReporting DB for Pyxis reports application2 DSServerReports DB for Pyxis reports application
S. No Database Name Details1 DSServerOLTP DB for Pyxis application2 CFUpdateService DB for Pyxis application3 DSRF DB for Pyxis application4 CfnSchedulingServices DB for Pyxis application
Database Server Details• J-BSTAR-DB01: This Server is consist of one production database. This
server is dedicated for Biostar attendance application.
• J-BLDBK-DB-01: This is server is consist of one instance named HIIG. The server is used by Blood Bank application. It has two schemas.
• JEDMHS-VSQL214A: This Server is consist of 11 production databases. This server is dedicated for Venus application.
S. No Database Name Details1 BioStar DB for Biostar attendance Application
S. No Schema Name Details1 ADMINISTRATEUR DB for Blood bank application
S. No Database Name Details1 Apollo_Interface DB for Apollo interface2 HOS2000_KFSHJ Venus Production Database3 HOS2000_KFSHJ_Images Venus Production Images Database4 Hos2000_KFSHJ_test Venus test database
5Hos2000_KFSHJ_test_Ima
ges Venus test Images database6 ICIS_INTERFACE DB for ICIS and Venus integration 7 ICIS_Interface_test Test DB for ICIS and Venus integration 8 INTERFACE DB for Cerner - Venus integration 9 mycare DB for mycare application
10 mycare1 DB for mycare test application 11 ORACLE_Interface DB for oracle sub store integration
Cerner – Venus Integration
Overview
• Orders and diagnosis extracts transfer the data from Cerner to Venus.
• Currently the integration between Cerner and Venus system is done by using Extract, transform and load (ETL)process.
• Microsoft SQL Server Integration Services (SSIS) component is used to implement the ETL process.
Current Process
Cerner
FTP Interface
10.11.20.79
Venus10.11.20.2
14
1. Orders and diagnosisFiles sent from CernerTo FTP Interface serverusing FTP protocol
1. Orders and diagnosisFiles Arrived.2. MS ETL Tool extracts thedata from CSV files and insert them into Venus database server
Data
Detailed ETL Process
Oracle – ID Badge system Integration
Overview
• Employees, Non-employees and dependents data is transfer from Oracle ERP to ID Badge system is Jeddah.
• Currently the integration is done by using Extract, transform and load (ETL)process.
• Microsoft SQL Server Integration Services (SSIS) component is used to implement the ETL process.
Current Process
Oracle ERP
FTP Interface
ID Badge System
10.11.136.146
1. Data files sent from oracle ERP to FTP InterfaceServer using FTP protocol
1. Data Files Files Arrived.2. MS ETL Tool extracts thedata from CSV files and insertthem into ID Badge systemdatabase server
Data
Detailed ETL ProcessEXTRACT
Employees Files VALID FILES
T_PRECORD_STG
Y
Move to Error FolderD:\Id_Badge\Error Folder
Move file to Archive FolderD:\Id_Badge\Archive Folder
N
INSERT NEW RECORD INTO T_PRECORD
New Records
COMPARE DATA IN T_PRECORD_STGWITH T_PRECORD
Y
Records Update
N
EXTRACT Non-Employees Files VALID FILES
T_PRECORD_STG
Y
Move to Error FolderD:\Id_Badge\Error Folder
Move file to Archive FolderD:\Id_Badge\Archive Folder
N
EXTRACT Dependents
Files VALID FILES
T_PRECORD_STG
Y
Move to Error FolderD:\Id_Badge\Error Folder
Move file to Archive FolderD:\Id_Badge\Archive Folder
N
UPDATE EXISTING
RECORDS INTO T_PRECORD
BioStar Attendacne system – Oracle Riyadh Integration
Overview
• Employees attendance data is transfer from BioStar attendance system in Jeddah to Oracle ERP in Riyadh.
• Currently the integration is done by using Linked Server feature from MS SQL Server.
Current Process
Biostar Database
Oracle Riyadh
Data
Detailed Integration Process
NTNAEVENT IS IN 0,1,2,3
CONVERT NTNAEVENT FROM 0,1,2,3
TO1,3,2,4
Y
ExitN
MOVE THE DATA TO ORACLE
Convert the date and time to
DDMMYYYY and HH:MM format.
START
EXTRACT DATAFROM
BIOSTAR