As with installing the SQL Server or Oracle software, you should spend some time learning the purpose of a SQL database before you create the database itself. You should take a two-step approach for any new databases you create. First, understand the purpose of the database, and then create the database with the appropriate parameters. Consider what the database will be used for and how much data it will contain. You should understand the underlying hardware that you'll use—the number and type of CPUs, the amount of memory, the number of disks, the controllers for the disks, and so on. Because the database is stored on the disks, many tuning problems can be avoided with proper capacity and I/O subsystem planning.
Planning your database and the supporting hardware requires insights into the scale or size of the workload and the type of work the system will perform. Some of the considerations that will affect your database design and hardware configuration include the following:

How many users will the database have?
How many users will connect simultaneously and how many will concurrently perform transactions or execute queries?
Is the database supporting OLTP applications or data warehousing?
This distinction leads to different types and volumes of activity on the database server. For example, online transaction processing (OLTP) systems usually have a larger number of users performing smaller transactions, while data warehouses usually have a smaller number of users performing larger queries.
What are the expected size and number of database objects?
How large will these objects be initially and what growth rates do you expect?
What are the access patterns for the various database objects?
Some objects will be more popular than others. Understanding the volume and type of activity in the database is critical to planning and tuning your database. Some people employ a so-called CRUD matrix that contains Create, Read, Update, and Delete estimates for each key object used by a business transaction. These estimates may be per minute, per hour, per day, or for whatever time period makes sense in the context of your system. For example, the CRUD matrix for a simple employee update transaction might be as shown below with the checkmarks indicating that each transaction performs the operation against the object shown. This matrix shows access patterns for database objects:



How much hardware do I have now, and how much will I add as the database grows?
Disk drives tend to get cheaper and cheaper. Suppose you're planning a database of 100 GB that you expect to grow to 300 GB over the next two years. You may have all the disk space available to plan for the 300 GB target, but it's more likely that you'll buy a smaller amount to get started and add disks as the database grows. It's important that you plan the initial layout with the expected growth in mind.
What are the availability requirements?
What elements of redundancy, such as additional disk drives, do you need to provide the required availability?
What are my performance requirements?
What response times do your users expect, and how much of that time can you give them? Will you measure performance in terms of average response time, maximum response time, response time at peak load, total throughput, or average load?
What are my security requirements?
Will the application, the operating system, or the SQL database (or some combination of these) enforce security?
Even if you are unsure of things such as sizing and usage details, take your best guess as to initial values and growth rates, and document these estimates. As the database evolves, you can compare your initial estimates with emerging information to react and plan more effectively. For example, suppose you estimate that a certain table will be 5 GB in size initially and will grow at 3 GB per year, but when you are up and running you discover that the table is actually 3 GB, and six months into production you discover that it has grown to 8 GB. You can now revise your plans to reflect the higher growth rate and thereby avoid space problems. Comparing production measures of database size, growth, and usage patterns with your initial estimates will provide valuable insights to help you avoid problems as you move forward. In this way, documented guesses at an early stage are useful later on.
The same is true for key requirements such as availability and performance. If the exact requirements are not clear, make some assumptions and document them. These core requirements will heavily influence the decisions you make regarding redundancy and capacity. As the system evolves and these requirements become clearer, the history of these key decision criteria will be crucial in understanding the choices that you made and will make in the future.

Only registered users can write comments.
Please login or register.