A Brief Explanation of How Database Server Software Works
Microsoft SQL Server
Let's start at the bottom and work our way up:
- Data and applications are stored on a hard drive or a series of hard drives and other media.
- All of this storage is managed by the operating system.
- The operating system, Windows in this case, handles all of the low level tasks associated with running applications as well as managing storage.
- SQL Server runs on top of Windows. SQL Server is actually dozens of modules (separate pieces of software). Each module is called when needed. The process is so seamless we are seldom aware of it.
- I have simplified the diagram to show only the query processor and security. The query processor translates SQL into a form of code the other server modules can use.
- All communications with the server go through the security layer. Good security for a server is too complex a subject for now. The security layer is a blending of operating system functions and functions specific to SQL Server.
- Jump below the picture to continue with a discussion of SQL and the layers above it.
- SQL is the communications protocol between the SQL Server and the outside world. Most communications with SQL Server are in Standard Query Language (SQL).
- Look to the top-right at SQL Server Management Studio. Management Studio is a development environment that is installed on the developer's machine. It comes with SQL Server, but doesn't need to be on the same machine. A free version of SQL Server and Management Studio is available on the microsoft Web site. You are probably safe downloading the third option "Database with Management Tools". If you have 64-bit Vista or 64-bit Windows 7 you can download and install the 64-bit version. You don't have to. The 32-bit version will work on either 32-bit or 64-bit Windows.
- SQL Server Management Studio gives us a single location to manage our SQL Servers and develop and test our databases and SQL code.
- Before we can manage servers or run SQL code we must set up a connection to each server we want to work with. The servers can be anywhere. The only requirements are a network connection and security clearance.
- Look to the top-left at the Application Server. An application server runs an application. In this case, a Microsoft .NET application.
- The Application Server could be a dedicated Web server supplying a Web interface to a database, or a client application on someone's personal computer.
- The application could be bought "off the shelf", but we are more interested in custom applications we build ourselves.
- The database, development environment, and server application could all be running on a single computer. More commonly, with SQL Server projects, this is only done on developer's machine to provide easy access to everything for the developer. In production, it would be normal for the database and application to be on separate machines. Sometimes the Web server and application server are also separated.
- Microsoft Access has all of the same components, and one big difference. Access is designed to install and run all of the components on a single computer.
- Access can work with remote database such as SQL Server, but is normally used as a package on a single computer using its own database.
- The Access application server is called Form View.
- The Access developer modules for forms, tables, etc. are called Design Views.
- The modules are so closely integrated that most people don't realize that there are several different modules.
- The Jet Database Server is installed when you install Access. Whenever you open Access the database server starts. Access can also connect to SQL Server and a few other databases, but Jet is the default.
Microsoft Access is not as easy to share with other users as separate applications and databases, but it is possible to build complex and sophisticated applications with Access, especially if the application runs against a SQL Server database.
Tracing through an ASP.NET Web application
This time, from the top down.
- A Web browser on a client machine requests a page from the Web site. The request travels across the Internet based on the requested URL.
- The Web server parses (breaks down and analyses) the request, then passes the request to a specific application based on the analysis.
- The application server runs the requested application, which probably needs data from the database server.
- The application server needs to make a request to the database server, including security information.
- Once the request passes security and is processed, the database server returns the requested information to the application.
- The application creates a Web page and sends it to the Web server which sends it back to the requesting Web browser.
- The diagram shows that a single SQL Server can access multiple databases at one time.
- A single application may use data from more than one source.
- CLR is a Microsoft specific language for accessing and manipulating data without using SQL.