Chapter 8: Data Storage Design and SQL Server

This chapter will explore the most common storage technologies available to you. It will also present some benefits and concerns about different storage designs so that you can more easily choose what suits your solution. We will finish the chapter with a presentation of SQL Server-examining its architecture and how it fits into your storage policy.

Database Design

Designing a database means more than just jotting down some objects on a piece of paper and trying to create tables from them. It requires an understanding of the business functions you want to model, as well as how you can implement them by using database concepts and features. As you saw in Chapter 1, Object Role Modeling (ORM) can be a great way of achieving your database design.

When you are beginning to design your database, you should consider performance issues. Even though you can change the database design once it is implemented, this is not a desirable way of doing things. It costs both time and money to go back and redesign a database at a later stage.

During database design, you should consider the purpose of the database. An Online Transaction Processing (OLTP) database will not be normalized the same way that you would normalize a decision support database, for instance. The more update-intensive processing of the transactions in an OLTP database might often require a more normalized design than a decision support database, since the latter often has less redundant updates. The decision support database might actually be more efficient if you do not normalize it too much. By having fewer joins, performance is better and data will be easier to understand.

You must always create a database plan that will fit your purpose. You should also get an overview of the security requirements for the database. Do you need to set up Active Directory Application Mode, or ADAM, (as discussed in more detail in the section "Windows Authentication"), or can you manage with your existing Active Directory? If you aot going to have many users, you can perhaps use SQL Server authentication and skip a directory altogether.

Another aspect you should consider is how much data you will store, and how much you believe this data will grow over time. This affects how you plan your hardware. You also need to address scalability and reliability when designing your database. Do you need to cluster the database, or can you live with some outages of your SQL Server? You should also start planning a disaster recovery plan as early as possible.

TIP A good rule of thumb, as always, is to dedicate enough time for the design so the project later on will not have any performance or security issues. The time needed will have to be estimated based on the kind of application you are going to build, so it is hard to give exact advice here.In the best-case scenario, your company has a policy for data storage so you do not have to make these decisions for every new project, since they take some time to work through. Database design involves two things:

• Logical design
   
• Physical design

We will explore these in more detail in the following sections.

Logical Design

During the logical design phase, you model your business requirements and data using tables, constraints, and other database components (see Figure 8-14). You do this preferably by using ORM. (At this stage, you do not yet think about how you will store data physically.)



Figure 8-14. A logical database design

One of the first things you do during logical design is to remove as much redundancy as you can from the database. This process, called normalization, breaks down the data into smaller parts; for example, the name of an employee can be broken down into first name and last name. You also need to consider the integrity of your data during this phase. By using primary keys, check constraints, foreign keys, default values, and other database features, you can make sure your data is not compromised, and these features also help you maintain your business requirements.

TIP Use fixed-size columns when you need to store only small values in such columns. If you have fewer than 5 characters per cell in a column, you should use a fixed column size like char(4). This enhances the way a data row is read and hence increases performance.

If a column is to include more than 20 characters in each cell, you should use a variable size like varchar(100). Anything in between is difficult to advise on, so try different sizes in a test lab before deciding.

Please remember that a bad logical design most often results in a bad physical design, which in the end negatively affects performance.

Physical Design

The physical database design phase involves mapping the logical design to your physical hardware. You do this by taking advantage of the hardware and software features available to you. In other words, this is where you allow the data to be physically accessed and maintained as quickly as possible.

NOTE Indexing your data is also part of this design phase.We will cover indexing later on in this chapter in the section "Index Tuning."

When you do your physical design, you often benefit if your company has a data storage policy. Otherwise, you must decide where to store your data: on a DAS, a NAS appliance, or a SAN. Depending on the business requirements for your solution, you perhaps must deviate from corporate standards, and during the design stage is the time to determine these things.