SQL Server has rapidly become the Web database of choice. A recent study by Zona Research (http://www.zonaresearch.com) revealed that more Web servers access SQL Server than any other database.
Web applications are entirely different animals from standard client/server applications. First, Web applications are subject to public exposure. Second, companies usually know how many people use an internal application. But businesses usually don't know how many users access their Web applications, and the number can vary tremendously. Furthermore, internal applications run on systems that have some degree of physical and logon security, whereas you have no control over the systems that might access Web applications.
To deal with these types of problems, you should think about simplicity, scalability, and security. For example, simplifying the development process by reining in an overly ambitious Web application might result in an application with fewer bells and whistles but one that end users can understand and use.
Likewise, you need to keep scalability in mind when you design Web applications. Scalability is the Holy Grail of Web applications, but following some basic practices will help you attain a scalable application. For starters, put SQL Server on a different box from the Web server and give the SQL Server system enough RAM. The Web application should use connection pooling and avoid session-level variables. Run SQL Profiler and the Index Tuning Wizard during the development process to make sure your database access calls are doing what you intended. Before you deploy the application, stress-test it under various loads by using a Web application testing and scalability tool.
Finally, take precautions to secure your SQL Server database. You don't want your company to end up on the wrong end of one of those MSNBC exposés, such as the recent story in which the helpful news crew hijacked credit card numbers and other personal information from 2500 accounts on unsecured SQL Server databases. In this unfortunate story, the databases were on the same system as their Web servers, and the database passwords were either blank or easily accessible in the Active Server Pages (ASP) source code. By simply moving the database from the Web server to a separate system, you reduce the database's accessibility. To further limit unwanted access, consider connecting the Web server and SQL Server systems by using the NetBEUI protocol instead of TCP/IP. Also, use basic security mechanisms such as password protection, and never hard-code database passwords. Paying attention to simplicity, scalability, and security in Web applications can help keep customers secure and happy—and can keep your company off the nightly news.
