Home > Forms and Procedures > Procedures > Business Continuity Planning > Business Continuity Plan - SQL Server Farm

Business Continuity Plan - SQL Server Farm

Synopsis: 

The SQL Server Farm is a collection of virtual instances using embedded VMWare.  The physical servers involved are virtual hosts VMSRV7, VMSRV8, and VMSRV9.  Although not actually in the SQL Server Farm NAVY and GRAPHITE are machines using SQL applications and are covered under this discussion.  Other SQL servers are SUNSTONE, 2VM9, 2VM11, FAVM, MELON, CALL COPY, NAVYBLUE, TANZANITE, SQL1-14 and SALENITE.   IVORY is another SQL server considered outside of UIT (Health Center).  This is soon to be replaced by ROSE.  ORICHD2 and PLUM2 are soon to be replaced by ORCHID3 and PLUM3

Information:

SERVER NAME:                                              VMSRV7

SERVER LOCATION:                                       Maxwell Building

SERVER PURPOSE:                                        SQL Server Farm Server

RECOVERY PROCEDURE:

               Short Term Recovery Plan:          Verify that VMWare instances are running on alternate server.                                                                        The virtual hosts are not backed up.

               Long Term Recovery Plan:           The future plan is for DPM (Data Base Protection Manager) to                                                                       be put into place which allows snapshots every 15 minutes of                                                                       the SQL database.  Nightly backups will then be run to MINT.

               Recovery Point Objective:           24 hours

               Recovery Time Objective:            24 hours

SERVER INFORMATION:                             

  • Manufacturer:                                 Dell
  • Model #:                                           PowerEdge R900
  • Service Tag#:                                   87X8PH1
  • Server Warranty:                            Silver
  • Type of Warranty:                          24/7
  • Warranty Expiration:                     11/06/2011
  • Ship Date:                                         11/06/2008
  • Manufacturer Support #:            
  • Manufacturer Support site:          service express

HARDWARE:      

               CPU:

               Memory:

                                                            VMWare ESX 3.5.0 embedded in hardware

DATABASES: (Cluster)

  • IWU_cube                         analysis portion
  • MS_dpm                            database protection manager backup plan
  • MS_dpm_SQL                  database protection manager backup plan part 2
  • MS_OPM_SQL                  part of 2 instances of Operations Manager
  • MS_Project                       MS Project Manager
  • SQL_Manage_dw            Microsoft Central Management Server for all SQL servers
  • SQL1                                 Faculty showcase database
  • SQL2                                   iSupport database, COGNOS, Image Now component
  • SQL3
  • WIKI                                    Potential future WIKI

 

NETWORK:

  • NIC #1:
    • IP Address:                         10.7.1.126
    • Firewall (Y/N):
  • External IP:
    • IP Address:
    • Firewall (Y/N):
  • Default Gateway:
  • DNS:                                                   10.7.1.13; 10.7.1.16
  • Other:

ATTACHED TO SAN:

  • HBA(Model/WWPN)
  • Alias of HBA:
  • Switch/Port
  • Array:
  • Assigned Storage:

BACKUP PROCEDURES:

  • Backup Policy:   

Stephen Swan has a script on the WIKI.  The script creates self maintaining jobs which backup the databases.  The script has to be run manually the first time. Backups for the databases in  Warehouse 2.0, MS Project, SQL1 and SQL management vmware instances run every night at midnight on ICE.   In the event of a failure on VMSRV7, VMSRV8 or VMSRV9, the vmware instances on the failed server may be brought back online on the other.  If a single instance fails the instance would need to be recreated, OS and applications reinstalled and SQL backups restored onto the new instance.

VCB_SQL_Weekly-ICE

Weekly backup run on Thursday nights

 

  • Product/Version:              SQL 2008, SQL 2005
  • Media (Disk/Tape)
  • Location of Media:          \\opal\SQL Backups\,servername>\<instancename>

 

RESTORE PERSONNEL:

  • Lead Person/Team:                         Systems Admin
  • Technical Support:                          Stephen Swan    
  • Location of Software

____________________________________________________________________________________________________________

SERVER NAME:                               VMSRV8

SERVER LOCATION:                        Maxwell Building

SERVER PURPOSE:                         SQL Server Farm Server (failover for VRMSRV7)

RECOVERY PROCEDURE:

               Short Term Recovery Plan:          Verify that VMWare instances are running on alternate server.

               Long Term Recovery Plan:          

The future plan is for DPM (Data Base Protection Manager) to be put into place which allows snapshots every 15 minutes of the SQL database.  Nightly backups will then be run to ICE.

Note:  Development is on GRAPHITE and data from SQL8.  Both are currently backed up.

               Recovery Point Objective:           24 hours

               Recovery Time Objective:            24 hours

SERVER INFORMATION:                             

  • Manufacturer:                                 Dell
  • Model #:                                           PowerEdge R900
  • Service Tag#:                                   97X8PH1
  • Server Warranty:            
  • Type of Warranty:                            Service Express
  • Warranty Expiration:                     11/06/2011
  • Ship Date:                                         11/06/2008
  • Manufacturer Support #:            
  • Manufacturer Support site:        

HARDWARE:      

               CPU:

               Memory:

               VMWare ESX 3.5.0 embedded in hardware

NETWORK:

  • NIC #1:
    • IP Address:          10.7.1.127
    • Firewall (Y/N):
  • External IP:
    • IP Address:
    • Firewall (Y/N):
  • Default Gateway:
  • DNS:                                    10.7.1.13; 10.7.1.16
  • Other:

ATTACHED TO SAN:

  • HBA(Model/WWPN)
  • Alias of HBA:
  • Switch/Port
  • Array:
  • Assigned Storage:

BACKUP PROCEDURES:

  • Backup Policy:                   NONE
  • Product/Version:
  • Media (Disk/Tape)
  • Location of Media:
  • Retention Period

RESTORE PROCEDURES INFORMATION:

  • Lead Person/Team:          Systems Admin
  • Technical Support:           Stephen Swan
  • Location of Software:

____________________________________________________________________________________________________________

SERVER NAME:                               VMSRV9

SERVER LOCATION:                        Maxwell Building

SERVER PURPOSE:                         SQL Server Farm Server (failover for VRMSRV7)

RECOVERY PROCEDURE:

               Short Term Recovery Plan:          Verify that VMWare instances are running on alternate server.

               Long Term Recovery Plan:          

The future plan is for DPM (Data Base Protection Manager) to be put into place which allows snapshots every 15 minutes of the SQL database.  Nightly backups will then be run to ICE.

Note:  Development is on GRAPHITE and data from SQL8.  Both are currently backed up.

               Recovery Point Objective:           24 hours

               Recovery Time Objective:            24 hours

SERVER INFORMATION:                             

  • Manufacturer:                                 Dell
  • Model #:                                           PowerEdge R900
  • Service Tag#:                                   66Q4JM1
  • Server Warranty:            
  • Type of Warranty:                        SERVICE EXPRESS
  • Warranty Expiration:                    
  • Ship Date:                                         5/13/2010
  • Manufacturer Support #:            
  • Manufacturer Support site:         support.dell.com

HARDWARE:      

               CPU:

               Memory:             Need 48 GB rather than 24GB

               VMWare ESXi 4.0 Build 1 Enterprise Plus

NETWORK:

  • NIC #1:
    • IP Address:          10.7.1.30
    • Firewall (Y/N):
  • External IP:
    • IP Address:
    • Firewall (Y/N):
  • Default Gateway:
  • DNS:                                    10.7.1.13; 10.7.1.16
  • Other:

ATTACHED TO SAN:

  • HBA(Model/WWPN)
  • Alias of HBA:
  • Switch/Port
  • Array:
  • Assigned Storage:

BACKUP PROCEDURES:

  • Backup Policy:                   NONE
  • Product/Version:
  • Media (Disk/Tape)
  • Location of Media:
  • Retention Period

RESTORE PROCEDURES INFORMATION:

  • Lead Person/Team:          Systems Admin
  • Technical Support:           Stephen Swan
  • Location of Software:

 

____________________________________________________________________________________________________________

 

SERVER NAME:                               GRAPHITE

SERVER LOCATION:                        Maxwell

SERVER PURPOSE:                         Test server/Development box for Datateam (SQL)

RECOVERY PLAN:                           

               Short Term Recovery Plan:            Load Windows Server OS and then Netbackup client on server.                                                                              Restore all drives from tape/disk then restore System State.   

Long Term Recovery Plan:

               Recovery Point:

               Recovery Time Objective:

SERVER INFORMATION:

  • Manufacturer:                                 Dell
  • Model #:                                            PowerEdge 2950
  • Service Tag #:                                  62PP8F1
  • Server Warranty:                            SILVER
  • Type of warranty:
  • Warranty expiration:                     12/10/2010
  • Manufacturer Support #:             1-866-410-3355
  • Manufacturer Support site:         support.dell.com
  • Purchase Date:                                12/10/2007
  • Retirement Date:                            12/9/201

HARDWARE:

               CPU: 3.00GHz

               Memory: 16GB

SOFTWARE:        Operating System: Microsoft Windows Server 2003 Standard x64 Edition SP2

                              SQL 2005/2008 2 versions of SQL on the server

DATABASES:      Version: 9.0.3054

  • AUTOINDEXRECS
  • Advancement_web
  • ConferenceServices_web
  • Academic_affairs
  • Ag_counsel
  • Coll18
  • coll18_dev
  • coll18_test
  • facilities_services
  • faculty_recruiting
  • human_resources
  • imageNow
  • iwu_misc
  • legal
  • misc_webapps
  • ontime
  • r25
  • reports
  • staging
  • taskman
  • telesale

NETWORK:

  • NIC #1:
    • IP Address(es):                                  10.7.1.97
    • Firewall (Y/N):
      • Open ports:
  • External IP:
    • IP Address(es):
    • Firewall (Y/N):
      • Open ports:
  • Default Gateway:                                          10.7.1.254
  • DNS:                                                                   10.7.1.13
  • Other:                                                               WINS 10.7.1.54

ATTACHED TO SAN:

  • HBA(Model/WWPN):
  • Alias of HBA:
  • Switch/Port:
  • Array:
  • Assigned Storage:

BACKUP PROCEDURES:

  • Backup policy:   None currently being run on this development server.  SQL portion backed up to MINT
 
  • Product/version: 
  • SQL/Exchange/Datatel:
  • Media (Disk/Tape):
  • Location of Media:
  • Retention Period:            

RESTORE PROCEDURES:

  • Lead Person/Team:
  • Technical Support:
  • Location of Software:

 

 

____________________________________________________________________________________________________________

 

 

SERVER NAME:                                              NAVY

SERVER LOCATION:                                       Maxwell Building

SERVER PURPOSE:                                        Web Development SQL, CMS, Inventory Request System, MARS                                                                            Data, Smart Track, Track-It

SERVER INFORMATION:

  • Manufacturer: Dell         
  • Model #: PowerEdge 2950
  • Service Tag #: H6CC7G1
  • Server Warranty:
  • Type of warranty:Service Express
  • Warranty expiration:
  • Manufacturer Support #: 1-866-410-3355
  • Manufacturer Support site: support.dell.com
  • Purchase Date: 5/2/2008
  • Retirement Date: 5/1/2013

HARDWARE:

               CPU: Dual 3.00GHz

               Memory: 4GB

SOFTWARE:        Operating System: Microsoft Windows Server 2033R2 Standard x64 Edition SP2

NETWORK:

  • NIC #1:
    • IP Address(es): 10.7.27.221
    • Firewall (Y/N):
      • Open ports:
  • NIC #2:
    • IP Address(es):
    • Firewall (Y/N):
      • Open ports:
  • Default Gateway: 10.7.27.254
  • DNS: 10.7.1.13; 10.7.1.16
  • Other: WINS 10.7.1.54

ATTACHED TO SAN:

  • HBA(Model/WWPN):
  • Alias of HBA:
  • Switch/Port:
  • Array:
  • Assigned Storage:

BACKUP PROCEDURES:

  • Backup policy:    SQL Backup (same as GRAPHITE) StdWindows_DXi7500 and SQL instance backed to MINT
  • Product/version:
  • SQL:
  • Media (Disk/Tape):
  • Location of Media:
  • Retention Period:

RESTORE PROCEDURES:

  • Lead Person/Team:          Systems Admin
  • Technical Support:           Stephen Swan
  • Location of Software:    

 

    

____________________________________________________________________________________________________________

 

 

 

 

BACKUP SYSTEM:                           STONEFLY

LOCATION:                                       Maxwell

PURPOSE:                                         SAN for VMSRV7, VMSRV8 and VMSRV9

SYSTEM INFORMATION:

  • Manufacturer:                  StoneFly
  • Warranty:                          1 year – next business day (spares kit in Sysadmin Director)
  • Warranty Expiration:      11/17/2009
  • Support No.:                     1-888-786-6335
  • Ship Date:                          11/18/2008

 

FURTHER INFORMATION:

CONCENTRATOR 1:                        StoneFly 4.8TB OPTISAN, Dual Storage Concentrator

               Serial No:                           D5003588

               IP Address:                         10.7.2.5

CONCENTRATOR2:                         StoneFly 5017-D (OPSNC-4.8 TBS-SCD) Dual Storage

               Serial No:                           D5003589

               IP Address:                         10.4.2.3

STORAGE #1:                                    StoneFly RAID 10 8,000GB Expansion Array (8X1,000 GB SATA)

               Serial No:                           D5003591

               IP Address:                         10.7.2.4

STORAGE #2:                                    StoneFly Raid 6, 4,800gb fs16FZ-HA RAID (16x3000gb SAS)

               Serial No:                           D5003590

               IP Address:                         10.7.2.7

 

NETWORK:

              

RESTORE PROCEDURES:

 

Review History:

Original Plan Approved:   4/21/09

Reviewed/Updated:    5/4/09; 8/11/09; 10/25/10; 5/29/13 (reflecting changes in Backup systems and maintenance

You must to post a comment.
Last modified
02:21, 17 Dec 2013

Tags

This page has no custom tags.

Classifications

This page has no classifications.