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:
Development - Front end, middle tier, back end
Database design and development
Infrastructure setup and configuration
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
Bandwidth between the users and infrastructure
Hardware specifications (desktop, notebook, etc.)
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
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.
Anticipated growth rate or capacity planning
Character set and sort order
Determine how the data will be accessed for index selection
Uploads or downloads from an existing system
Data entry by customers
Data entry by internal users
Separate SQL Server hardware, instance, license, separate data model, etc.
Real time, dashboard, analytical, trending, detailed, etc.
Number, location, operating hours
Detailed, rolled up, geographical, departmental, process related, etc.
24X7 or Monday to Friday 9:00 AM to 5:00 PM, etc.
Transactions per second
Number of sustained users
Database auditing - Third party, triggers, etc.
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.
Type - Backup, differential, transaction log, third party solution, etc.
Potential data loss
Determine the types of failures that should be prevented
Hardware, software, administrative error
Amount of acceptable downtime
Native vs. third party solution
Determine the types of failures should be recoverable
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!
Last Update: 3/17/2008
About the author
Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as a SQL Server Consultant at Edgewood Solutions, co-founder of MSSQLTips.com as well as Baltimore SSUG co-leader.