MS SQL Server Environments

ITIS offers two environments for housing MS SQL Server databases, the Shared Environment and the Standalone Environment.  ITIS is responsible for the OS and SQL Server Database Software install, upgrading, and patching in both environments.  All database owners are required to subscribe to iti-mssql-contacts@lists.purdue.edu at https://lists.purdue.edu/mailman/listinfo/iti-mssql-contacts.  The DBA team uses this mail list for communications of outages and other pertinent information regarding availability.

Shared Environment

  • We provide MS SQL Server database servers that have been designated as available to all business units in need of a space to house their databases.  Due to the shared nature access is tightly controlled.
  • Currently there is no charge to the business units for housing a database in the shared environment.
  • The shared servers are configured as a failover cluster.  That allows for high availability.  In case of server failure the instances on the failing node will move to the stable node.  The outage is brief – less than 1 minute.
  • Access to these servers is available from the WL campus wired network.  Access is not available via PAL, ResNet, or WebVPN.
  • We provide one instance in the shared environment that is secured to manage data classified as RESTRICTED.
  • Three separate environments are available
    • DEV – used for database development work and testing
      • Database developers have access to all database elements for creation, modification, and deletion with the exception of DATABASE creation and deletion.
      • DEV is backed up once daily and these are retained for 14 days.  DEV can be restored to any of these daily backups.
    • QA – used for limited end user testing.
      • End users may be granted access to QA for testing purposes.
      • Developers are limited to db_datareader and db_datawriter.
      • No development or production work should be performed in QA.
      • QA is not backed up.  It is intended to be a testing platform only and can be refreshed from PROD or DEV as needed.
    • PROD – Production access
      • Developers are limited to db_datareader.  Data updates should be performed only thru the applications.
      • PROD is backed with a full backup daily and incremental backups hourly.  Point in time recovery is available.  Backups are retained for 14 days.
      • Updates to PROD database elements are provided to the DBA team via scripts from the Technical owner (or their designee).  The DBA team will execute the scripts per requested schedule.

Standalone Environment

  • ITIS also offers individual virtual or physical servers to house databases where the application requires heightened server privileges. Any request for standalone servers should be presented through ITIS Service Management as a project.
  • System owners will be responsible for all SQL Server licensing costs on a standalone system.
    • Developers, applications, and system owners may be granted higher server and database privileges in the standalone environment.  SQL Server level security will be the responsibility of the server owners.  Any auditing of the security will be the owners’ responsibility.
    • ITIS DBA group will back up the system and user databases and transaction logs
    • ITIS DBA group will honor requests to restore databases.
    • Three separate environments are available:
      • DEV - backed up once daily and these are retained for 14 days.  DEV can be restored to any of these daily backups.
      • QA - is not backed up.  It is intended to be a testing platform only and can be refreshed from PROD or DEV as needed.
      • PROD - backed with a full backup daily and incremental backups hourly.  Point in time recovery is available.  Backups are retained for 14 days.
    • Business units will be responsible for any ad-hoc backups.  There is limited amount of local backup space allocated to each server (10G or 1.5x the largest database).  Any AD HOC backups must be done as COPY ONLY.  Otherwise the log chain is corrupted and this will impact our ability to restore a database if the AD HOC backup is removed from the server.
    • Database developers have access to all database elements for creation, modification, and deletion including DATABASE creation and deletion.  However, ITIS reserves the right to make sure the databases adhere to their stands for database file placement, Rcovery Model, and sizing.
    • ITIS will maintain SSL Certificates.
    • Firewall access to the SQL servers will be maintained ITIS and all firewall changes need to be requested through the ITIS DBA team.
    • Requests for ITIS DBA assistance will be handled as ITIS DBAs are available.  Due to the nature of standalone servers, troubleshooting for these server becomes more labor intensive, versus the shared environment, which is much more controlled and therefore, easier to find problems quickly.