Using MSDE 2000 in a Web Application

 

Brian A. Randell
MCW Technologies, LLC

March 2004

Applies to:
   Microsoft® .NET Framework SDK
   Microsoft® ASP.NET version 1.1
   Microsoft® ASP.NET Web Matrix 0.6 (Build 812)
   Microsoft® SQL Server™ 2000 Desktop Engine (MSDE 2000)
   Microsoft® Visual C#® .NET
   Microsoft® Visual Basic® .NET
   Microsoft® Visual Studio® .NET

Summary: Build dynamic, data-driven Web applications with Microsoft ASP.NET and MSDE 2000 using the Workload Governor to limit the number of concurrent operations that the database engine can perform. Then, adjust your ASP.NET application's database connection string to modify the default connection pool size used by ADO.NET. (16 printed pages)

Contents

Executive Summary
Introduction
Building Your Application the Right Way
Related Samples
Summary
Appendix A: Miscellaneous Issues
Appendix B: References
Related Books

Executive Summary

Building dynamic data-driven Web applications with Microsoft ASP.NET and the Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) is easy. When it comes to scalability however, MSDE 2000 has specific limitations compared to the full-blown versions of SQL Server 2000. One key limitation is that MSDE 2000 contains a Workload Governor that throttles the number of concurrent operations the database engine can perform. The solution is pretty simple: adjust the size of the default connection pool used by ADO.NET by adjusting your ASP.NET application's database connection string.

Introduction

The Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) is a data engine built on the same core SQL Server technology in Microsoft SQL Server 2000 Standard and Enterprise editions. MSDE 2000 provides all the necessary components to build a full-featured data-driven website. It is important to understand how MSDE 2000 differs from the full version of SQL Server 2000 when designing your Web application. This white paper provides design guidance to help you get your Microsoft ASP.NET-based website up and running smoothly when using MSDE 2000 as a data source.

MSDE 2000 Specifics

At its core, MSDE 2000 is SQL Server 2000. The relational engine, query processor, and transaction manager are all the same. The main differences are in the tools packed in the product, licensing, and scalability.

Unlike the other versions of SQL Server 2000, MSDE 2000 does not include any graphical tools such as Enterprise Manager or Query Analyzer. To manage a deployed instance of MSDE, your application needs to provide its own management tools or use the OSQL.EXE console application. Microsoft provides a free ASP.NET tool, the Web Data Administrator, which allows you to manage both MSDE 2000 and SQL Server 2000 applications via a Web interface. Visual Studio .NET 2002 Professional and higher include graphical tools for designing tables and views. Visual Studio .NET 2002 Enterprise Developer and higher include additional support for creating stored procedures, triggers, functions, and so forth. In addition, there are numerous third-party solutions that provide graphical management tools for MSDE 2000. Visit the MSDE 2000 section of the SQL Server 2000 product site for more details.

Licensing MSDE 2000 has evolved over time. Until recently, you could only redistribute MSDE 2000 if you had a valid license for any of several Microsoft development tools. As of November 2003, you can download MSDE 2000 for free.

When it comes to scalability, MSDE 2000 has specific limitations compared to the full-blown versions of SQL Server 2000. First, on a multi-processor system, MSDE 2000 only supports two processors, in contrast to SQL Server 2000 Standard Edition, which supports up to four, and SQL Server 2000 Enterprise supports up to 32 processors. Second, MSDE 2000 supports up to 2 GB of RAM, the same amount as SQL Server 2000 Standard Edition, but less than SQL Server 2000 Enterprise Edition, which can support up to 64 GB of RAM on properly outfitted servers running the Data Center server editions of Microsoft Windows 2000 or Microsoft Windows 2003. Another difference related to scalability is that MSDE 2000 (as well as SQL Server 2000 Personal Edition) contains a Workload Governor.

You can also see additional differences between MSDE 2000 and SQL Server 2000.

Understanding the Workload Governor

All versions of SQL Server 2000 (including MSDE 2000) support up to 32,767 connections per instance, assuming you have sufficient system resources. Each instance supports up to 32,767 databases. Although the full version of SQL Server 2000 supports databases up to 1,048,516 TB in size, MSDE 2000 databases are limited to 2 GB each. More importantly for your application designs, MSDE 2000 employs what is known as a concurrent Workload Governor. The effect of the governor is to slow certain operations down by stalling user connections for a few milliseconds whenever there are more than eight concurrent operations. Some system-generated events in the database engine count against this eight-operation limit, so the governor may kick in even when your application code requests fewer than eight operations. The key is concurrent operations, such as executing a query. This is not the same as concurrent users. In most applications, there is a certain amount of user "think time," where a live connection to the server exists, but the user is not actually performing a task that accesses the database. The actual number of concurrent users can be much higher. For some applications, MSDE 2000 is not able to produce performance figures that satisfy your users. At that point, you'll need to consider upgrading to SQL Server Standard or Enterprise Edition.

You can get specific details about the Workload Governor.

Building Your Application the Right Way

One of the challenges of building any application is figuring out the right way to build it. Following good design practices and understanding the impact that the Workload Governor can have on your application can significantly impact performance and scalability.

Sample Application

The sample application that accompanies this article is designed to be a simple data-driven picture site used by an individual, in a shared-hosting environment. The application supports serving images from the file system and their supporting metadata from an MSDE 2000 database. Figure 1 displays the database schema.

Figure 1. The sample application's database schema showing its tables and relationships

As you can see, only the image metadata is stored in the database. To reduce the total size of the database and to make it easier to store the images anywhere on the server you want, only the location of the images and their thumbnails are stored in the database. Images are grouped into image groups, such as "Pictures from our summer vacation". The application is designed to support anonymous and authenticated access to the images. Authenticated users have access to images based upon their roles. Image groups are assigned a minimum role required to view a group of images. In addition, each image can have its own unique minimum role. For example, let's say you have 50 pictures from your last vacation to Hawaii. Five of those 50 pictures show you looking a little pale from all those hard days at the office under florescent lights. You can assign the image group a minimum role that allows friends and family to see your vacation pictures. However, those five special pictures could be assigned a different minimum role that allows only you to see the pictures.

To build your MSDE solution, I encourage you to acquire a copy of SQL Server 2000 Developer Edition. This release, which is licensed for development only, gives you all the graphical tools to design your database and test your implementation. It is available via various MSDN subscriptions as well as on its own. MSDE 2000 itself only provides the command-line OSQL utility.

You can get more information on licensing SQL Server 2000 Developer Edition.

Normalization, Relationships, Indices, and Stored Procedures

The first step to building successful data-driven websites with MSDE 2000 is to design a proper database schema. The schema represents the objects that are used to store and manipulate the data stored in the database. To start, you normalize your data model. All too often, applications work with redundant data due to using the relational database as a glorified spreadsheet. Normalization is the process of breaking your data into logical groups. These groups are often known as tables, a collection of data arranged in rows and columns. For example, the sample application contains a table named Images. Each column contains a specific type of information about the images. For example, each image has a unique ID and a description that describes what the picture displays. Each row contains all the data about a particular image. Each table should have a primary key—a column or set of columns—that uniquely identify a row within the table.

You can create relationships among your tables to link related information. For example, each image in the sample application belongs to an image group, such as Vacation Pictures or Baby's First Birthday. First instinct would be to include the actual group title as a column in the main image table. However, this would lead to redundant data. If you wanted to change the title of an image group, you would need to update multiple rows. A better solution is to create a separate table that contains image groups and link it to the main images table. The sample database has an ImageGroups table that contains a primary key named ImageGroupId and a column ImageGroup for the image group's title. The Images table in turn has an ImageGroupId column, known as a foreign key. Using a one-to-many relationship, the two tables are linked using the two common columns. One image group can be associated with many images.

Once your schema is normalized and relationships identified, indices are added. An index is a structure that orders the values of one or more columns in a database table. Indices are essential for helping the query processor find data efficiently. Each table's primary key has an index. In addition, indices are added to columns that are used in relationships or used for filtering and sorting data. During the development and testing phase, you will find the tools provided in SQL Server 2000 Developer Edition, such as the Query Analyzer and the Profiler, helpful in determining where to add, change, or remove indices.

Once the schema is defined, you create stored procedures for manipulating and accessing data in the database. For the sample application, there is only a partial set defined: those that were necessary to get the site up and running. There are three logical groups of stored procedures in the sample application: those related to core data management (no prefix), those related to session management (prefixed with sm_), and those related to user authentication (prefixed with ws_). This is a factoring technique only; it in no way affects application performance or usability. All data access and manipulation for the sample application is performed using stored procedures. This serves two purposes. First, the application never accesses any of the tables or data directly, reducing the total attack surface of the database. Only the stored procedures are exposed. Second, it separates interface from implementation. The code in the Web application focuses on the presentation layer, while the stored procedures are responsible for data access and manipulation.

Performance and Scalability

It's important to differentiate between performance and scalability. Performance is how fast the application can perform some task. Scalability is how many concurrent operations the application can perform. Although the two are related, they are not equivalent, and finding acceptable levels for both can be difficult. For Web applications, a common measurement of performance and scalability is how many requests per second (RPS) the application can process. Figures 4 and 5 give you an idea of what can be accomplished. Of course, you need to do your own testing and gather your own figures in your environment on your computers. Remember, you should program first for correctness and then for performance and scalability. Basic stress tests should be applied as soon as possible to detect bad application design and incorrect assumptions. For the sample application, the Microsoft Application Center Test tool that comes with the Visual Studio .NET 2003 Enterprise Developer Edition and the Enterprise Architect Edition was used. See the Testing The Sample section later in this article for more information.

Connection Pooling

Connection pooling is a feature supported by the ADO.NET SqlClient library that allows code to reuse connections from a pool rather than having to create and open a new connection to the database each time. This can increase your application's scalability and performance.

The key to increasing scalability in an MSDE 2000 application is to reduce the number of concurrent operations to avoid invoking the Workload Governor. When the Workload Governor detects more than eight simultaneous operations, it injects a pause. The more concurrency violations detected, the more pauses injected by the governor. These pauses by the governor can be detected by running the DBCC CONCURRENCYVIOLATION statement with the DISPLAY option (see the SQL Server Books Online for more information).

Note   You can download the latest edition of the SQL Server Books Online.

Figure 2 displays the results of a DBCC CONCURRENCYVIOLATION(DISPLAY) check, run using OSQL.EXE, and performed using an instance of MSDE.

Figure 2. The results of a DBCC CONCURRENCYVIOLATION(DISPLAY) statement using OSQL.EXE

This is the type of output you want to see when you run the check on your own server. The output shows that there have been no concurrency violations since either the instance was started or since a server administrator issued a DBCC CONCURRENCYVIOLATION(RESET) statement. The following notes (adjusted for the data displayed in Figure 2) from the SQL Books Online explains the output:

The first line indicates how long the counters have been accumulating statistics.
The second line is built of headings indicating which counter is being reported in that field of the message. Each heading indicates how far over the five-batch limit each violation was. The 1 represents the count of the number of times six batches (five-batch limit + one violation) were executing concurrently, the 2 represents the count of the number of times seven batches (five + two) were executing concurrently, and so on. The heading 10-100 represents the count of the number of times the system was between 10 and 100 batches over the limit, and the heading >100 indicates the number of times the system was more than 100 batches over the limit.
The third line reports how many times the indicated number of batches executed concurrently. In the example line above, there were no violations.
Note   While the above prose from the SQL Books Online lists five for the batch limit, the updated information is eight, which is the correct number from the Microsoft white paper entitled Understanding When the Workload Governor Is Activated.

In contrast, Figure 3 shows the results of a DBCC CONCURRENCYVIOLATION check with multiple violations.

Figure 3. The results of a DBCC CONCURRENCYVIOLATION(DISPLAY) statement using OSQL.EXE with multiple violations

Although a few concurrency violations are acceptable, having too many (like those listed in Figure 3) is unacceptable. The obvious solution is to reduce the total number of concurrent operations. By default, when a connection is opened using the SqlConnection object provided by the .NET Framework, a connection pool with a minimum size of one connection and a maximum size of 100 connections is created. All connections in a pool must have exactly the same values for each setting. These include the database name, pooling specifications, and user credentials. Connection pools are created for connections using either integrated security or standard security. The key is that the entire connection string must be the same, and when integrated security is in effect, the user must be the same. Otherwise, additional pools are created. By adjusting the connection pool's maximum number to five, you significantly reduce the possibility of having any concurrency violations.

Here's a connection string from the sample application:

Server=localhost;Database=Pics2Share;Trusted_Connection=
True;Connection Timeout=60;Pooling=True;Min Pool Size=1;
Max Pool Size=5

This is in contrast to what a default connection string looks like:

Server=localhost;Database=Pics2Share;Trusted_Connection=
True;Connection Timeout=15;Pooling=True;Min Pool Size=1;
Max Pool Size=100

The default Connection Timeout setting is 15 seconds. Increase the timeout to a reasonable amount (in testing 60 seconds was used, which proved reasonable) to reduce exceptions raised by timeouts waiting for a free connection.

It's important to note that the query governor employed by MSDE 2000 is per instance, not per database. Increased scalability can only be achieved when one Web application uses one instance of MSDE 2000. This would work fine in a shared hosting environment where each account/application can have its own MSDE 2000 instance to manage and use.

Note   While more than 16 full versions of SQL Server 2000 can theoretically be installed, the MSDE 2000 installation routines only allow 16 instances (1 default and 15 named or 16 named). Regardless, the total number of tested and supported instances for any version of SQL Server 2000 is 16.

If you find that you need multiple databases on a single instance, upgrading to either SQL Server 2000 Standard or Enterprise Edition is the proper solution. Although creative solutions—such as using COM+ object pooling, MSMQ, or custom .NET objects hosted out-of-process from the Web server—could be created, the time, energy, and effort far outweigh the licensing costs of SQL Server 2000.

Security

One of the goals of the sample application is to restrict access to images and their metadata as specified by the application owner. In addition, there is a need to restrict who has access to the database and the server instance itself.

The security issues involved are:

  • ASP.NET worker process identity and access rights
  • Application authentication
  • User access rights to data
  • Managing and storing user names and passwords
  • SQL Server 2000 process identity

ASP.NET Worker Process Identity and Access Rights

Any code executed under a Microsoft® Windows NT® kernel-based version of Windows is associated with a user identity. This could be a local user, a domain user, or a system account. The ASP.NET runtime, by default, runs under one of two possible identities. On Windows® 2000 Server and IIS 5.0, the ASP.NET runtime runs in a worker process known as aspnet_wp.exe. Its identity is configured in the main machine.config file for the .NET Framework. The default account is a low-trust local user account named ASPNET, with limited rights on the server. On Windows Server™ 2003, the execution model has changed under IIS 6.0. IIS 6.0 supports application pools where ASP.NET applications can be mapped to either a shared process or their own unique process. These application pools, by default, are configured to run under the NETWORK SERVICE account. Once again, this is a low-trust account. It is the identity of one of these accounts that the application code runs under. The application code can only perform actions that are authorized for the ASP.NET account.

By default, these accounts do not have access to the application's MSDE 2000 instance and do not have the ability to write to the file system, a privilege the sample application needs for image uploading. Giving the ASP.NET user account access to the file system is straightforward. The sample application, by default, places newly uploaded images in a folder named Uploads underneath the root directory of the Web application. Navigate to this directory using Windows Explorer. Right-click the directory, select the Security tab, and add the appropriate account to the list of users who have rights to the directory. This account needs Modify rights. Providing access to the database is a bit more complicated.

In a Windows 2000 environment where multiple ASP.NET applications are hosted, it is unreasonable to give the ASP.NET worker account access to the database. The MSDE 2000 instance should be configured to support standard SQL Server authentication. Create a login account for the application and then assign the login as a member of the Public role for your database. This login only needs to be granted Execute rights to all of the stored procedures defined in the database.

In a Windows 2003 environment where more than one Web application shares an application pool, follow the same directions for Windows 2000. If your Web application is configured to have its own application pool, it is possible to use integrated security when accessing MSDE 2000. To do so, the system administrator creates a local user account with rights to the directories where your Web application is deployed and any directories containing your application's images. This account can then be given access to your MSDE 2000 instance as a valid login. This is only reasonable if the account provided is only used by your application. Otherwise, standard security is better.

Note   The recommendation to use standard security is specific to a hosted solution. In general, you are encouraged to use integrated security whenever possible. It's more secure and reduces the amount of user management.

Visit the ASP.NET website for additional details on configuring ASP.NET on Microsoft® Windows Server™ 2003.

Application Authentication

An assumption of the sample application's design is that most users will host their websites on a server they do not control. This immediately removes Windows NT integrated security as an option for application authentication. The solution is to use ASP.NET Forms Authentication for users.

Note   It is critical when using ASP.NET Forms Authentication that you use an HTTPS connection to the server to prevent user name and password interception. This requires installing a server-side certificate issued by a trusted Certificate Authority. For intranet applications, you can choose to use the Microsoft Certificate Server. For Internet applications, purchase a certificate from a well-known and trusted third party.

The sample application uses an embedded login form in the main page. When first accessing the site, the user is listed as an anonymous user and only has access to those images marked with anonymous access. To access restricted content, the user enters a name and password and clicks the Login button. At this point, the application attempts to validate the user's identity using the e-mail address and password stored in the Users table in the database. If the validation is successful, the FormsAuthentication class's SetAuthCookie method is called to mark the user as authenticated and to issue a temporary authentication ticket to the user. For the duration of the session, the application can check whether the current user is authenticated by checking User.Identity.IsAuthenticated and can restrict access to pages and data for unauthenticated users.

User Access Rights to Data

As mentioned earlier, the sample application is designed to allow users to see different images based upon the level of trust the owner of the application has for a particular user. The sample uses numeric role IDs. The assumption is that low-trust users (such as anonymous users) are assigned a role ID with a low number (0 is the default for anonymous users). More trusted users have a larger role number. Images and image groups are assigned a minimum role ID required to view those images. This makes filtering data from the database easy and efficient. An alternative would be to use the roles architecture in ASP.NET, which allows you to assign a user to a set of roles and to check the user's membership in a particular role.

In the sample application, only authenticated users should be able to view restricted images. By default, image files such as JPG, GIF, and PNG files are not managed by the ASP.NET runtime. It would be necessary to either modify the IIS configuration to map these image files to the runtime, or use NTFS ACLs to restrict access to those particular files. Neither is a particularly good solution in a hosted environment. The solution employed by the sample application is to use an HTTP Handler that streams the files down to the user without exposing the image's end-point. This reduces possible configuration headaches and prevents unauthorized users from accessing images. In addition, the application provides the owner with the flexibility to deploy the actual images and thumbnails in locations that are outside the Web application's main or child directories.

Get additional information in Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication.

Managing and Storing User Names and Passwords

Because the sample application uses ASP.NET Forms-based authentication, it is the application's responsibility to store the user names and passwords. The first issue is ensuring that the passwords are not stored in clear text. Thankfully, the ASP.NET runtime provides a method to handle that: HashPasswordForStoringInConfigFile in the FormsAuthentication class. This function takes a string and cryptographic algorithm (such as SHA1) and returns a hashed value of the password. Although this is good, it leaves the passwords subject to a dictionary attack. Once one password is figured out, all passwords with the same value return the same hash. The solution is to "salt" the password with a random value for each user. This means you need both the original password and the salt to match the hash. The WebSecurity class in the sample application provides functions for managing users and passwords.

SQL Server 2000 Process Identity

By default, when an MSDE 2000 instance is installed, it is assigned to run under the LocalSystem account, a privileged account. Have your system administrator configure the instance to use a regular local user account.

Testing the Sample

As mentioned earlier, the sample application was tested using the Microsoft Application Center Test tool that comes with Visual Studio .NET 2003 Enterprise Architect. The tool allows you to put a Web application under load by using a single computer to simulate multiple browser sessions. The tool supports collection of performance counter data that can be evaluated for various design and load scenarios.

Once the database schema was created and some sample data loaded, a bare bones version of the application was created. The application did no authentication and only retrieved data from the database; no images were displayed. There were a total of 3,108 records in the database. Tests were run first against an MSDE 2000 instance using the default connection pool settings. The second test used the modified settings (recommended earlier) that reduced the connection pool to a maximum of eight connections. Finally, the tests were run against a non-throttled version of SQL Server 2000 using the default connection pool settings. Figure 4 displays the results.

Figure 4. Test results using Microsoft ACT against MSDE 2000 and SQL Server 2000

Additional tests were performed against the MSDE instance using a connection pool of five maximum connections. A small database was loaded with data for 349 images. A large database was loaded with 38,420 images. The application either accessed the metadata from the database on each page request or it accessed the data from an in-memory cache. In both cases, the thumbnail and image files were loaded from the file system. When working against cached data, the application used ADO.NET DataView objects to filter the data. The results are pretty interesting as displayed in Figure 5.

Figure 5. Test results using Microsoft ACT against MSDE with and without caching against small and large databases

It turns out that it is more efficient to use MSDE 2000 to grab a subset of data from the database than to use a DataView to filter a large DataTable stored in the ASP.NET cache object in memory. With a small database, the results were pretty similar, showing that adding the extra logic for caching isn't really beneficial for this application.

It is important to note that while caching was not a solution for this specific application, it might be for others. The article, ASP.NET Caching: Techniques and Best Practices provides details and more links about caching. In addition, the Microsoft Patterns & Practices group has published a Caching Architecture Guide for .NET Framework Applications with over 100 pages of great information available online or in PDF format. In addition, the ASP.NET Performance Monitoring, and When to Alert Administrators article and the ASP.NET Performance Monitoring page provide more information about performance monitoring and testing.

All tests were performed on an HP LH4 quad-processor server with PIII 550 MHz Xeon processors, a RAID 5 SCSI disk array and 2 GB of RAM on a 100 megabit switched network with only a 2 GHz P4 test client and the server generating traffic. The server ran Windows 2000 Server SP4 and the test client ran Windows XP Professional SP1. SQL Server 2000 and MSDE 2000 were both installed on the same server with IIS 5.0 and ASP.NET 1.1.

Related Samples

There are 6 sample applications to illustrate best practices for using ASP.NET and MSDE. They illustrate development using the .NET Framework SDK, Visual Studio, or ASP.NET Web Matrix, and are available in both C# .NET and Visual Basic .NET. You can either build the sample application via the walkthrough provided, or download the full sample.

Visual Basic .NET and Visual Studio .NET

MSDE 2000 Walkthrough: Build a Data-Driven Website Using Visual Basic .NET and Visual Studio .NET 2003

Complete Sample

Visual Basic .NET and Web Matrix

MSDE 2000 Walkthrough: Build a Data-Driven Website Using Visual Basic .NET and ASP.NET Web Matrix

Complete Sample

Visual Basic .NET and the .NET Framework SDK

MSDE 2000 Walkthrough: Build a Data-Driven Website Using Visual Basic .NET and the .NET Framework SDK

Complete Sample

C# .NET and Visual Studio .NET

MSDE 2000 Walkthrough: Build a Data-Driven Website Using Visual C# .NET and Visual Studio .NET 2003

Complete Sample

C# .NET and Web Matrix

MSDE 2000 Walkthrough: Build a Data-Driven Website Using Visual C# .NET and ASP.NET Web Matrix

Complete Sample

C# .NET and the .NET Framework SDK

MSDE 2000 Walkthrough: Build a Data-Driven Website Using Visual C# .NET and the .NET Framework SDK

Complete Sample

Summary

Building data-driven websites with ASP.NET and MSDE 2000 is easy. Building solutions that can perform well and are secure require a bit more effort. But, with just a little sweat, it's really amazing what you can do for free with Microsoft Notepad, the .NET Framework SDK, and MSDE 2000.

Acknowledgments

As always, building solutions and exploring technologies is more fun when it's done with friends. Ken Getz, a fellow consultant at MCW Technologies, wrote the initial ASP.NET code to hook the images into the main application grid. Adam Cogan of SSW provided input on the application design and general feedback. Fritz Onion, an independent consultant and fellow instructor at DevelopMentor, reviewed all the code, provided design guidance (both in person and via his books), and did conversions from the primary Visual Studio .NET version of the sample to ASP.NET Web Matrix and .NET Framework SDK versions.

Appendix A: Miscellaneous Issues

Building sample applications is a somewhat daunting task. Budget and time are severely limited compared to a regular solution. In addition, the sample is often meant to showcase best practices or application features. As such, it might not be as feature-rich as one would like. This section points out some of the compromises made while designing this sample, as well as additional issues you could run into when building your own Web application with MSDE 2000.

Multi-User File Upload Conflicts

The assumption in the sample application is that only the owner of the website uploads images and there will be no contention. For lots of users uploading files at the same time, both the stored procedure AddImageMetaData and the btnUpload_Click handler need to be changed to either generate unique file names using GUIDs and/or data/time values or to partition the files in sub-directories by user ID.

Uploaded Files Have a Four MB Limit By Default

Out-of-the-box, ASP.NET only supports uploading files that are under 4 MB. This value can be adjusted by changing the maxRequestLength attribute of the httpRuntime element in machine.config file.

Error Handling

The application employs a top-of-stack exception handler defined in the Global.asax class. It does a bare-bones job of dealing with unhandled exceptions. Application enhancement would include adding richer procedure-level exception handling, and possibly logging exceptions to e-mail or to the Windows Event Log.

Compilation Setting

When deploying your solution, don't forget to set the debug attribute of the compilation element in your application's web.config file to false (note that it's case-sensitive) and to compile your main application assembly in release mode.

Connection String Setting (It's Cached!)

The connection string for the application is stored in the web.config file. Whenever it is needed, it is read using the ConfigurationSettings.AppSettings class. There is no need to store the connection string in application state. ASP.NET automatically caches the entire configuration file at application startup.

In-proc State and Session Ended Event

The sample application takes advantage of the Session_Ended event available in the Global.asax class. This event is used to update the Sessions table with the date and time the session ended. This event only works when the session state for ASP.NET is configured to run in process. Using the state server or SQL Server for session storage causes this event not to fire.

Session Timeout

The Session_Ended event occurs when the session times out. This occurs by default after 20 minutes of inactivity. Note that this also causes the authentication ticket to expire for users, forcing them to log in again.

Appendix B: References

For additional information, please see these references:

Technical Articles on the Web

.NET Data Access Architecture Guide

10 Steps to Help Secure SQL Server 2000

Improving Web Application Security: Threats and Countermeasures

Websites

For more information about SQL Server 2000 and MSDE 2000, visit the SQL Server page and the MSDE page on the Windows Server System™ site.

Visit the SQL Server team-supported community site.

Visit the ASP.NET team-supported community site.

Related Books

Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design, Second Edition by Michael J. Hernandez, Addison Wesley Professional, 2003. ISBN: 0201752840

Essential ASP.NET with Examples in C# by Fritz Onion Addison Wesley Professional, 2003. ISBN: 0201760401

Essential ASP.NET with Examples in Visual Basic .NET by Fritz Onion, Addison Wesley Professional, 2003. ISBN: 0201760398

Microsoft ASP.NET Coding Strategies with the Microsoft ASP.NET Team by Matthew Gibbs and Rob Howard, Microsoft Press, 2003. ISBN: 073561900X

Show: