(I) XXXX MSSQL Server Details
(a) Hardware Configuration
| 1.1 | Server Type | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 1.2 | Computer Model | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 1.3 | RAM Memory | 3840 MB | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 1.4 | No. of CPU’s | 4 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 1.5 | Processor Type & Speed | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 1.6 | Hard Disk Space | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 2.1 | Operating System | Windows NT 4.0 service pack 6.0 |
| 2.2 | RDBMS | (a) MS SQL Server 7.0 Standard Edition (server version)
(b) Product Version – 7.00.961 |
| 2.3 | General (SQL Server) | (a) Autostart SQL Server, SQL ServerAgent & MSDTC
(b) OS Memory – 3840 MB (c) Root Directory – “d:MSSQL7” |
| 2.4 | Memory (SQL Server) | Dynamically configure SQL Server Memory (0 to 3840 Mb) |
| 2.5 | Processor (SQL Server) | 4 Nos. |
| 2.6 | Security (SQL Server) | (a) Authentication (SQL Server & windows NT)
(b) Audit level (Failure) |
| 2.7 | Connections (SQL Server) | (a) Allow other SQL Servers to connect remotely to this SQL Server using RPC.
(b) Maximum concurrent user connections = 0 (unlimited) |
| 2.8 | Server Settings (SQL Server) | (a) Default language – English
(b) Allow triggers to be fired which fire other triggers (nested triggers) |
| 2.9 | Database Settings (SQL Server) | (a) Default Index fill factor to use when rebuilding indexes – Default (optimal)
(b) Backup/restore time-out period – Wait indefinitely |
(c) Database Details
| 3.1.1 | DBPays | General | File Name | dbpays-test2_Data | ||
| File Path | D:mssql7dataDBPAYS.mdf | |||||
| File Group | PRIMARY | |||||
| File Properties | (a) Automatically grow file (checked)
(b) File growth – By percent 10 % (c) Maximum file size – Unrestricted file growth |
|||||
| Transaction Log | File Name | dbpays-test2_Log | ||||
| File Path | D:mssql7dataDBPAYS_log.ldf | |||||
| File Properties | (a) Automatically grow file (checked)
(b) File growth – By percent 10 % (c) Maximum file size – Unrestricted file growth |
|||||
| Options | (a) Torn page detection (checked)
(b) Auto Create Statistics (checked) (c) Auto Update Statistics (checked) |
|||||
| Permissions | None | |||||
| User Rights | bb001384 | Public, Db_datareader, db_datawriter | ||||
| bb001445 | Public, Db_datareader, db_datawriter | |||||
| bb009026 | Public | |||||
| bb009511 | Public | |||||
| BEXADM | Public | |||||
| Dbpays | Public, Db_datareader, db_datawriter | |||||
| Dbpays-qry | Public, Db_datareader | |||||
| 3.1.2 | Chqatpar | General | File Name | chqatpar_data | ||
| File Path | D:mssql7datachqatpar.mdf | |||||
| File Group | PRIMARY | |||||
| File Properties | (a) Automatically grow file – checked
(b) File growth – by percent 10 % (c) Maximum File Size – Unrestricted file growth |
|||||
| Transaction Log | File Name | chqatpar_Log | ||||
| File Location | D:mssql7datachqatpar_log.ldf | |||||
| File Properties | (a) Automatically grow file – checked
(b) File growth – by percent 10% (c) Maximum File Size – Unrestricted file growth |
|||||
| Options | (a) Auto Shrink (checked)
(b) Auto Create Statistics (checked) (c) Auto Update Statistics (checked) |
|||||
| Permissions | None | |||||
| User Rights | bb009026 | public | ||||
| bb009511 | public | |||||
| BEXADM | public | |||||
| Chqatpar | public, db_datareader, db_datawriter | |||||
| db_cap_query | public, db_datareader, | |||||
(d) Server Name & IP Address
| 4.1 | Server Name | xxxxxx |
| 4.2 | IP Address | xxx.xxx.xxx.xxx |
| 4.3 | Server Physical Location | KD House |
(e) DR / Standby Server
| 5.1 | Server Name | xxxxxx |
| 5.2 | IP Address | xxx.xxx.xxx.xxx |
| 5.3 | Server Physical Location | DB House |
| 5.4 | Physical MSSQL Files Location | D:mssql |
(f) Impacted Audiences & Application
| 6.1 | Impacted Audiences | All the Users using xxxx & xxxx application |
| 6.2 | Impacted Applications | xxxx & xxxx |
| 6.3 | Application Co-ordination | user name / user name |
(g) Priority order of Database recovery
| 7.1 | Priority Order | (1) DBPays
(2) Chqatpar |
(g) Backup Details (Location of backup, types of backup, backup schedules)
| 8.1.1 | Database Name | DBPays |
| 8.1.2 | Types of backup | Full & Transactional backups |
| 8.1.3 | Location Backup | Full backups (Tapes & d:mssql7backup on xxxx)
Transactional backups (on xxxx) |
| 8.1.4 | Backup Schedules | Full Backups – Once at 1.15 am
Transactional Backups – Twice at 2.00 PM & 7.00 PM |
| 8.2.1 | Database Name | Chqatpar |
| 8.2.2 | Types of backup | Full & Transactional backups |
| 8.2.3 | Location Backup | Full backups (Tapes & d:mssql7backup on xxxx)
Transactional backups (on xxxx) |
| 8.2.4 | Backup Schedules | Full Backups – Once at 1.15 am
Transactional Backups – Twice at 2.00 PM & 7.00 PM |
(h) Permissible period of acceptable loss
| 9.1 | xxxx | Approximate of 8 hrs |
| 9.2 | xxxx | Approximate of 8 hrs |
(i) Permissible acceptable period of downtime for database
| 10.1 | xxxx | Approximate of 5 hrs |
| 10.2 | xxxx | Approximate of 5 hrs |
(j) Disaster Recovery Testing schedule
| 11.1 | xxxx | Once in every 2 months |
| 11.2 | xxxx | Once in every 3 months |
(k) Disaster Recovery System
| 12.1 | Scenarios of System Failures | (a) Hardware Failure / Operating System failure / Database failure / Natural Disasters / Major Human errors / Computer Viruses –——> Results in failure of the complete database
(Failure of all the tables & objects) |
| (b) Soft corruption / Minor human errors / program or application malfunction ——> Results in failure of partial database
(Failure of one or few tables or objects) |
||
| 12.2 | Kinds of Recovery | (a) Recovery for the complete failed databaseà
(I) Recovery procedure – Due to some reason if the database (xxxx & xxxxx) is lost or corrupted, the complete database is required to restore which will include all the objects. Xxxx server will act as a Disaster Recovery server for both the databases. Latest one number full & two number transactional backup files will have to be transferred from xxxx hard disk (or the tapes where daily full backup is been taken) to the DR Server (xxxx) which is currently having a balance space of around 27 GB. Once one *.bak & two *.trn files are shifted to the DR server, the same can be restored in the following steps :- –Right click on databases select “All tasks” & then “restore databases”. Fill in the name as xxxx or xxxx at “Restore as database”. –Select the restore option as “From device” & Restore backup set as “Database – complete”. –Click on “select devices”, then click “add” & give the exact path of the backup (*.bak) file & then click OK. –Next go to the “Options” tab & in order to use “NO RECOVERY” option select “Leave database non-operational, but able to restore additional transactional logs” in the Recovery completion state section. –Finally click OK to start the full backup restoration. –After completion of full backup restoration, transactional backup has to be restored. –Again right click on databases & select “All tasks” & then “restore databases”. Select in the newly created database such as DBPays or Chqatpar at “Restore as database”. –Select the restore option as “From device” & Restore backup set as “Transaction log”. –Click on “select devices”, then click “add” & give the exact path of the backup (*.trn) file & then click OK. –Next go to the “Options” tab & in order to use “NO RECOVERY” option select “Leave database nonoperational, but able to restore additional transactional logs” in the Recovery completion state section. –Finally click OK to start the first transactional backup restoration. –Similarly after restoring the first transactional backup & the second & the final backup too needs to be restored in same steps mentioned above except for restoring the last backup “RECOVERY” option needs to be enabled. –Go to the “Options” tab & in order to use “RECOVERY” option select “Leave database operational, No additional transactional logs can be restored” in the Recovery completion state section. –This final step completes the complete process of disaster recovery of the required database on the DR Server. (II) State of Database after Recovery – The database recovered on the DR Server using the above mentioned method will be recovered till the time last transactional backup was taken on the main production server i.e. xxxx. All the records or data entered after the last transactional backup will be lost. |
| (b) Recovery for the partial failed databaseà
(I) Recovery procedure – Due to some reason if one or few tables of a database gets corrupted then the whole database need not be replaced. The following options needs to be tried before going for complete: –If possible, try taking a backup of the damaged table database & if it is successful then restore the same backed up database. –Second option is to try exporting all the possible records from the damaged tables to a dummy database on the DR server if successful then drop the damaged table & reimport the exported table from the DR server. –If none of the above mentioned options are working successfully then the final option is to restore the last taken full backup & thereafter taken all the transaction backup’s on the DR server as per the steps mentioned above. Identifying the tables which are damaged & the tables which are dependent on these damaged tables are re-exported & overwritten the old records. –If the record consistency between the tables in the database is not been able to be maintained then the complete backup needs to be restored as per the steps mentioned above for “Recovery for the complete failed database”. |