By Gail Seymour
In this series, we’re looking at the Windows operating system (OS) as a Web hosting platform. In the series overview, we looked at how Windows was developed and at its popularity. We’ve also looked at the scripting languages used on the Windows OS platform for serving Web pages. Later we will look at, Microsoft's current position in the Server OS market and its plans for the future. In this article, we’re focusing on SQL Server and MSADO database software.
Many Web sites provide Web applications that rely on dynamic information, such as user accounts, but a standard HTML browser like Internet Explorer can’t gather and manipulate that data without help. On a Windows platform, the Web server Internet Information Services (IIS) includes Active Server Pages, which provides a way to find, filter and present the data, but sites still need a way to store that information.
This can be achieved using Microsoft ActiveX Data Objects (MSADO) to connect to an Open Database Connection (ODBC) source, where the backend database already exists, for example in an Access database. However it’s usually more practical to use Microsoft SQL Server to manage the data in the form of a relational database on the server. The functions of IIS, SQL Server and ASP on a Linux platform are generally performed by the Apache Web server, MySQL database and either PHP, Perl or Python installed as the scripting language, which is why they are referred to as LAMP installations.
What happens when a user visits one of your dynamic pages on the Windows system is:
- The Web browser requests the page from IIS
- Where the page has the .asp file extension, IIS passes this request to the ASP Engine which executes the code
- The ASP engine connects to the SQL Server database and requests the content
- The data returns down the path, from SQL Server to the ASP Engine, where it is stored as variables, used to create output and returned to IIS as HTML. This is then served back to the browser in a format it can understand.
What Is SQL Server?
SQL Server is Microsoft’s relational database management system (RDBMS) a free Express version of which can be downloaded from Microsoft.com. A relational database stores sets of information in tables, where column headings are field names, and each row represents a data set. Multiple tables can be connected by creating relations between fields, and data sets selectively pulled from the tables for inclusion in Web pages using Structured Query Language (SQL).
SQL Server Express is limited to one processor and up to 10 GB data storage, but provides a way for smaller sites to sample the SQL server and later upgrade to the Standard, Web or Enterprise Editions, or any other edition of SQL Server.
This enables developers to create complex dynamic sites, such as sites with user accounts, without having to create individual pages for each user. Instead, a single user page template can pull in only the current users’ information. In addition, pages can be created to add, edit and delete user information without the administrator having to add them manually to the database.
SQL server has two main advantages over MySQL. The first is that it supports transactions and performs checks on data before committing changes to the database, making loss of critical data and data corruption less likely. The second is that it is fully featured, it supports foreign keys and stored procedures, which some MySQL version don’t. The cost of all this is that it tends to be resource heavy, and can quickly become sluggish.
SQL Server Management Studio
SQL Server editions all come with a graphical management tool, Management Studio, or Management Studio Express, which can be used to interact directly with the database. Using Management Studio, you can connect to a database on the same computer, and provided you have configured the database engine for external connections, you can connect to a database on another computer as well.
You can also use Management Studio to create SQL Server authentication logins, so that user credentials are stored in SQL Server, and set SQL Server to listen for incoming requests, enabling SQL Server to support Web applications.
SQL Server and Visual Studio.NET
SQL Server is designed to work within Visual Studio.NET, meaning you can connect to, or even create a new SQL database without leaving the Visual Studio Environment. By clicking “View” and the “Server Explorer,” you can access the Data Connections node. From there you can open the “OLEDB” folder to browse local connections, or add one by right clicking on it. Similarly, by right clicking on the “SQL Servers” folder, you can add a SQL Server connection. You can then expand these connections to open context menus, where you can add and create tables, views or stored procedures.
If you then double click on a table you can view its contents, and you can open a SQL pane where you can type SQL queries, which can be stored to external files or loaded from them. This makes development of Web applications much quicker.
You can also download the SQL Server Driver for PHP for use on either a PHP enabled Windows server, or to replace MySQL on a Linux/Apache platform.
Although the boundaries between Windows and Linux have begun to blur, with Linux machines able to connect to Microsoft SQL Server and even to run ASP, this should only be attempted by experienced administrators. For the most part SQL Server and ASP are best used on a Windows OS. Similarly, Windows servers are now capable of running MySQL, PHP and Apache, but the transitions are less than perfect, with some features either not fully supported or not implemented at all. As such, the MySQL/PHP combination remains a solution ideally suited to Linux servers.
About the Author
Gail Seymour has been a Web site designer for more than 10 years. During that time she has won three design awards and has provided the content and copy for dozens of Web sites and more than 50,000 Web pages.