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.

Connecting to the Database

There are several ways you can connect to a SQL Server, named pipes and TCP/IP being the most common. In certain situations, one of these may be preferable to the other if you want to get the best performance out of your solution.

If you have a fast LAN, not much differs between these two approaches. On a slower network, like aWAN or dial-up network, the difference becomes apparent. Named pipes is a very interactive protocol-it sends a lot of messages before it actually begins reading any data. Named pipes communication starts when a peer requests data from another, using the read command. After they have exchanged pleasantries, data is read. On a fast network, this communication will not be noticed much, but on a slower connection this costs performance because the network traffic consists of many messages. Obviously, this affects clients on the network negatively.

TCP/IP sockets have more streamlined data transmissions with less overhead than named pipes. Windowing, delayed acknowledgements, and other features of TCP/IP sockets help you improve performance. This is very useful on a slow network for providing significant performance benefits for your client applications.

Our suggestion is to use TCP/IP on WANs and dial-up connections; it will save a lot of complaints on slow data retrieval.

If the application using the SQL Server is located on the SQL Server itself, you can use local named pipes, which can result in great performance. Local named pipes run in kernel mode, making them extremely fast. Use this option if you are planning to implement the data access component on the same server as the SQL Server database. To summarize what we have just covered:

• Use TCP/IP on slower network connections.
   
• Use local named pipes when data access components are running on the SQL Server itself.
   
• Use either TCP/IP or named pipes on a fast connection.

Stored Procedures

A good way of optimizing how your data access components talk to SQL Server is through stored procedures. Because they are compiled on the server, they execute much faster than if you had used a Transact-SQL statement to retrieve data. You do not want to move business logic to the database in most cases; you only want to use the enhanced performance you get from including parameterized stored procedures.

NOTE All database interaction can benefit from using stored procedures. Your insert, select, and delete operations could, and should in most cases, be implemented as stored procedures. However, even stored procedures can negatively affect performance. Try to avoid stored procedure recompiles because they cost performance. One good thing to remember is to be careful with the use of temporary tables in stored procedures, since they cause a recompile of the stored procedure every time it is executed. In the case of temporary tables, you could try using a table variable instead. If you want to learn more about troubleshooting stored procedure recompilations, you can find a good document on this topic at http://support.microsoft.com/default.aspx?scid=kb; en-us;243586.

Another benefit is that stored procedures utilize the network more efficiently than an ordinary SQL statement does. For example, say you have an INSERT statement that inserts a large binary data value into an Image data column. If you do not use a stored procedure, the application issuing the Insert statement must convert the value to a character string. This doubles the statement's size. Only after this conversion can it send the data to the server. The server in turn converts the statement back to binary format and stores it in the image column.

If you instead use a stored procedure, the image would stay in binary format all the way to the server. This would greatly reduce overhead on the server and the client, as well as cutting down on network traffic.

Sometimes it can be of great use to move the business rules from the business logic into the database server. You must carefully consider your use of this, however, so you do not lose control of your application. The benefits of moving processing to a stored procedure are purely performance related. Instead of moving a large set of data to where the processing usually occurs, you bring the processing functionality to the data, which increases performance. The complexity of your application increases, however, so you should think twice before using this technique. The best way to see if the performance gains outweigh the increased complexity is to test the solution in the lab.

SQL Server Security

So far we have covered some important topics when it comes to data storage and SQL Server. The last topic we will cover before taking you through our demo application in the next chapter is SQL Server security.

In Chapter 4, we discussed authentication and authorization. Authentication occurs when a user or application tries to log on to your SQL Server. Authorization determines what the user is allowed to do in the database server and its tables. In the following section, we will take a look at how authentication works in SQL Server.

Choosing Your Authentication

There are two ways SQL Server can authenticate a user or group. Early in the design process, you should decide which method you are going to use, because each affects the way you set up your user accounts for your application. The two methods are

• SQL Server authentication
   
• Windows authentication

SQL Server Authentication

If you use SQL Server authentication, SQL Server matches the account and password the user supplies to a list that is stored in the sysxlogins system table. This table is found in the master database (see Figure 8-19). SQL Server authentication is quite easy to implement, and you use SQL Server Enterprise Manager to add, remove, or modify these logins (see Figure 8-20).



Figure 8-19. The sysxlogins table from the master database



Figure 8-20. The Enterprise Manager gives you an overview of the users in a database.

The logins created are local to the server on which the SQL Server resides, which is not a good solution if you have a multiserver environment. In that case, you cannot manage them very easily, and you would have to implement the accounts on several servers. This solution could work, however, if the number of users does not exceed 20 to 25. With more, you will soon discover how impractical this is, and how much administrative overhead this will cost. Imagine implementing a change to only one user on several servers.

Windows Authentication

What you should use instead in the preceding scenario is Windows authentication. When SQL Server authenticates using this method, it asks a domain controller to validate a user's credentials. A domain controller must be accessible for this to work, so you need to make sure one is available. When SQL Server authenticates a user or group against a domain controller, it receives an access token containing the user's SID and the SIDs of every group the user has membership in. SQL Server will then assign access to the database server based on these SIDs.

You can use Active Directory (AD) to store your users and groups for your applications, but in many cases you do not want to mix your internal corporate users with your application users. The SQL Server will perhaps be used only for aWeb solution and not needed by anyone but those accessing the Web site. In that case, it could present a security risk to use the same AD as for your enterprise. To solve this, you could implement a separate AD infrastructure and set up a new server and domain, but this is not something you can do just like that. Luckily, Microsoft recently released a "light" version of Active Directory, called Active Directory Application Mode, or ADAM. ADAM is a stand-alone version of AD, intended for the use as a directory for Web-based applications and other types of applications. ADAM is deployed separately from the standard AD, and its directory data is not replicated throughout the enterprise core NOS directory. It also gives administrators the flexibility to deploy a directory without having to set up an entire Windows Server operating system environment on a domain controller. Nor do they have to activate Kerberos, DNS, or PKI.

ADAM is Microsoft's answer to Web-based directories from competitors like Sun's ONE Directory Server and Novell's eDirectory. (Given Microsoft's aggressive marketing in the past, ADAM will probably be seen on a server near you rather soon.)

Windows authentication gives you central management control regardless of whether you use AD or ADAM, which will give you lower TCO over time.

There are also ways of accessing SQL Server over the Internet using IIS, but this solution is not especially relevant when it comes to building an enterprise application, so we will not cover it here.

Determining Permissions

After you have decided which authentication method you should use, you must plan what permissions you will give to your users and groups. (We will in this Table 8-1. SQL Server 2000 Editions Overview (continued) discussion consider only Windows authentication.) You should strive to give permissions to your database server only to groups. If you do, you can more easily manage who has access to the server. Say you have a group of users belonging to the accounting department. Often these users will need the same permissions in the SQL Server. You do not want to add every user from this department to the database server and give them each the same permissions. What you do want to do is create a domain global group, add the accounting users to this group, and give only the domain global group database access. So instead of granting 30 people access and permissions on the server, you only do this for the domain global group they belong to, which cuts down on administrative overhead.

When you want to give permissions in a database on your SQL Server, you can use something called roles. There are some built-in roles you can use (see Figure 8-21), but you can also create your own. You give permissions only to roles, and then make the domain global groups members of those roles.



Figure 8-21. The roles in SQL Server

You create the roles you need, name them so you understand what they are, and then add the correct domain global group to the role. After that, you assign database permissions to it.

NOTE A good practice is to name roles after the domain all groups, so as not to cause confusion.

This procedure is the same as is often used to give users and groups access to data on a server. The global groups are members of local groups, and it is only the local groups that are given permissions to the data.

Summary

This chapter has covered some important issues when it comes to data storage and how SQL Server fits into a storage policy. You have also seen some tricks you can use to improve performance in your SQL Server systems.

Now it is time to put everything you have seen in this book to work. The next chapter will cover the development of a new application, following the guidelines we have discussed so far.