SQL Server Database Requirements

By: Jeremy Kadlec   |   Comments (2)   |   Related: > Testing

Problem

In our organization, I have noticed that database requirements are never included as a portion of the system requirements.  The requirements always focus on the interface and we derive the database design from the interface as well as fill in some of the gaps.  For some developers that process seems to yield a decent product, but not always.  I think if we requested database requirements from both the business and technical management our overall offerings would be much better and we would have less patch\fix cases.  For the requirements in our environment I have a couple of ideas in mind, but I am hoping you can give me a broader view of the situation with respect to overall SQL Server database requirements.

Solution

It is unfortunate that SQL Server database requirements are not included in your requirements document.  Depending on the organization and internally how things work with new projects, it would be a good idea to outline a baseline set of requirements with the caveat that every project is different so new or more requirements may be needed.  If you are the SQL Server DBA responsible for the systems moving forward it would be wise to speak to your management to find out how you can have input at the beginning of process to ease your tasks at the end of the process.  In some organizations that is easier said than done, but it is worth outlining some of the potential issues either historically or theoretically and discussing them with your management in a professional manner.

As mentioned above, requirements differ from project to project.  As an example, the requirements for building an OLTP system versus a disaster recovery solution vary widely, so consider taking these steps in an effort to build requirements for your SQL Server database project at hand:

• Build a baseline set of requirements for all projects that can help get the requirements questions formulated
• Think about what is needed for the system when it is released to production from a business, technology, database and user perspective then build requirements around those areas
• Educate yourself on the project\technology so you are aware of requirements needed specific to the project

In terms of requirements for an SQL Server database (OLTP) project consider the following items as a baseline set of requirements:

• Make vs. Buy - One of the first requirements that should be outlined is the make versus buy decision.  Depending on your organization this could be a non decision because either you have internal developers, you have a contract with a development organization or your organization purchases everything off the shelf.  It is just a key step in many circumstances that is sometimes overlooked, but depending on the project can sway the direction of the requirements one way or another. 
  • Keep in mind with the recent news related to SQL Server Data Services this offering could change the equation related to some SQL Server infrastructure decisions.
  • Hosting your SQL Server externally can also be a key decision in this phase of the project and can change budgets and resource needs.
• Project Budget - Depending on the project and your roll in the organization, finances may or may not be discussed.  The reality is that everything has a cost, it just depends on if a hard dollar figure is used versus out how many internal resources are used to complete a project for a specific duration. 
  • Even if overall project costs are not defined, it is imperative to find out if additional hardware and licensing is needed and the overall budget for that portion of the project if you are going to be responsible for making a decision on the hardware platform.
• Project Team - Knowing your team members and what they can bring to the table is important when determining deadlines and setting general expectations.  Having a gap in any one skill set should be identified early in the process and alternatives should be determined.  One or many people should be responsible for:
  • Project management
  • Development - Front end, middle tier, back end
  • Database design and development
  • Infrastructure setup and configuration
  • Testing
  • Documentation
  • Support - Infrastructure, application, database, etc.
  • Sign-off - User, business, technical, etc.
• Deadlines - Agreeing on the deadlines ahead of time can offer a great deal of value for both the user and technical team as well.  The users will know when they can expect phases of the project completed and the technical team should be able to plan for the project deadlines while balancing the remainder of their projects or daily tasks.
• Business, Technical, User Value - Understanding what the business, users or technical team is trying to solve can be the biggest help in truly resolving the issue.  Be sure the problem is clearly understood then consider offering a few different general approaches to resolve the problem followed by the design for the final solution.
• User Requirements - The more you know about the users the better, here are some items to keep in mind:
  • Number of users
  • Working hours
  • Location
  • Bandwidth between the users and infrastructure
  • Hardware specifications (desktop, notebook, etc.)
  • Technical expertise
  • Expected training on the application
  • Historical successful or unsuccessful applications
    • Having some history behind what has or has not worked in the past may give you some good insight for the current application
  • How the problem is resolved today via people, process and technology
• Application type
  • Web, desktop, mobile, etc.
  • OLTP vs. Reporting vs. Analytical
• SQL Server Technical Requirements
  • SQL Server Name\Instance Name
  • SQL Server Services Needed
    • Relational engine, SQL Server Agent, Full Text Search, Analysis Services, Reporting Services, Integration Services, etc.
  • Database
    • Name
    • Size
    • Anticipated growth rate or capacity planning
    • Database configurations
    • Storage configurations
  • Data
    • Data elements
      • Tables
      • Columns
      • Data types
      • Accept null
      • Defaults
      • Language support
        • Character set and sort order
    • Data access
      • Determine how the data will be accessed for index selection
    • Source
      • Uploads or downloads from an existing system
      • Data entry by customers
      • Data entry by internal users
  • Reporting
    • Infrastructure
      • Separate SQL Server hardware, instance, license, separate data model, etc.
    • Report type
      • Real time, dashboard, analytical, trending, detailed, etc.
    • Users
      • Number, location, operating hours
    • Data
      • Detailed, rolled up, geographical, departmental, process related, etc.
  • Operating hours
    • 24X7 or Monday to Friday 9:00 AM to 5:00 PM, etc.
  • Performance requirements
    • Transactions per second
    • Number of sustained users
    • Response time
  • Security
    • Security Model
    • Database auditing - Third party, triggers, etc.
  • Automated Processes
    • Frequency - Hourly, Daily, weekly, monthly
    • Duration - Every hour for 1 minutes or every day for 3 hours, etc.
    • Source - Data source could be a partner, service provide or another internal application
    • Destination - Data source could be a partner, service provide or another internal application
    • Technology - SSIS, web service, custom application, etc.
  • Documentation
    • Data model
    • Database dictionary
  • Maintenance Schedule
    • Frequency
    • Duration
  • Backups Schedule
    • Type - Backup, differential, transaction log, third party solution, etc.
    • Potential data loss
    • Recovery time
  • High Availability
    • Determine the types of failures that should be prevented
      • Hardware, software, administrative error
    • Amount of acceptable downtime
    • Native vs. third party solution
  • Disaster Recovery
    • Determine the types of failures should be recoverable
      • Hardware, software, administrative, natural disaster
    • Amount of acceptable downtime
    • Native vs. third party solution
• Testing Requirements
  • Who is responsible for testing
    • Traditional tester
    • User
    • Technical testing
  • Building test plans
  • Sign-off on testing
• Application Pilot Requirements
  • Number of users
  • Duration
  • Use cases
  • Infrastructure
  • Success, failure or enhancement reporting
• Production Support Requirements
  • Production support team
  • Escalation procedures
  • Enhancement requests

Next Steps

• Building a core set of requirements is a wise step for all of your projects.  The requirements may be generic, but they should be able to kick start the requirements for the project at hand in order to flush out all of the items that are needed.
• We have heard a variety of figures related to the amount of time that is saved over the course of a project with proper planning based on a complete set of requirements.  One figure that sticks out is, for every hour spent planning, five hours are saved over the course of the project.  So if you are faced with many patch\fix situations, see if you can break the mold, gather complete requirements, make plans based on those requirements and see if the project is more manageable, shorter and accurate.
• Please add your additional requirements to the forum below in order to continue to grow this list!

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips