Develop your own Database Software

In this ebook, I will run you through the important steps involved in developing client/server database application software. Most of the applications we see today, technically speaking, are client/server application softwares.

Before we go further, let us know more about client/server applications.

Client/Server applications have become predominant in enterprise wide computing. This is because Client/Server computing provides an open and flexible environment where mix-and-match is the rule.

A Client/Server network is used to accomplish many business needs. They are:

a. To record accounting transactions like purchases, sales, receipts, payments in a company. Note that, different users enter these transactions simultaneously using the client computer. The client computer may be running a windows application or a web application. The Client/Server application, should support recording of these transactions into a central Database server confirming to all security and database integrity requirements of an accounting application.

b. To extract business intelligence reports from the data in the database server. c. To enable users to share resources like printers and fax machines.

As enterprise wide computing becomes more sophisticated, the CIO of a company has a challenging job involving maintenance of hardware and networks, upkeep issues like registry clean up, setting up and updating policies, regular backups, etc., ensuring that the Client/Server business application is up and running. A complex job like this requires an understanding and a grasp of all the issues like hardware, networks, network operating systems, protocols and more.

Client / server application development can be broadly categorized as:

Installing and setting up windows operating system for development and production environment Installing and setting up database server - sql server
Front end design and development
Back end database design and development

Understanding the Client/Server architecture

On the client side of this architecture, we find a fairly simple Front-end application. On the server side, we find an intelligent engine. The server is designed to accept queries from the front-end application – usually in the form of calls to stored procedures and return the requested information. We will see now in detail what are the responsibilities of client and a server.

Client Responsibilities

 

The client application is responsible for connection management, data capture, data retrieval, data presentation, and error management.

Connection Management: Client/Server applications establish a connection with the server to submit queries, retrieve result sets. In this architecture, the client makes a hard connection with the server over a local area network (LAN) or a wide area network (WAN) – even if the connection is with a SQL Server, running on the same system. The client application’s connection is authenticated by the LAN and SQL server with a user-supplied or application-generated login ID and password. In some cases, Microsoft Windows provides a degree of connection security management. That is, using domain-managed security, client applications need not provide additional user names and passwords to SQL Server—once they are authenticated by windows. The client must also deal with connection problems caused by trouble on the server, on the network, in the application itself.

Data Capture: The client presents forms for the user to fill in with data. The client validates data values before they are sent to the database. This pre-validation often involves cross-checking form fields against other fields, either on the form or in the database. Validation can take place as fields are filled in or completed, or as the form is committed. These validation criteria are often referred to as client-side business rules.

Data retrieval: The client submits queries to the database engine for processing and retrieves the result sets, as required. Another responsibility of the client is to manage data returned to the application.

Data Presentation: The client application is responsible for displaying results from queries, as needed. This task might involve filling a Grid or Listbox control.

 

Error Management: The client is also responsible for trapping and dealing with the errors. Effective and comprehensive error management is the sign of a successful client application.

 

The Server’s responsibilities

In any client/server implementation, the server is not just a data dumping ground. The server is also responsible for intelligent resource management, security management, data management, query management and database system management.

Resource management: The server is responsible for managing its own resources. These resources include RAM, connections, disk space, CPU time, threads, and a set of caches or queues. If the server has to compete for resources with other Windows 2003/ NT services, its job is made that much harder. For example, if the server must also act as a print server or domain controller, the SQL Server operations will become slow.

Security management: The server prevents unauthorized access to itself and the database while permitting guarded access to those with valid permission.

 

Data management: The server is also responsible for the validity and integrity of the data sent to the database system from the client application.

 

Query management: The server processes SQL queries from the clients, which involves syntax and object checks and compilation of a query into a valid and efficient processing plan.

Database system management: In managing the database system, the server manages all connections to the database. The server also maintains tables, indexes, procedures, rules, triggers, data types, list of valid users, and other database objects.

Let us list and discuss the important topics we should be familiar with concerning the windows operating system. Here my focus will be on discussing topics, which are more relevant and useful for a programmer who is into application software development.
Windows provides two models of network administration. They are workgroup (peer-peer) and domain model (client/server). The domain model, which involves usage of active directory services, is more prevalent and suitable for large networks and where centralized administration is must.

Setup a domain model for a Client / Server Network

A domain model provides centralized administration because user’s information is stored centrally. In the workgroup model to access resources on two machines, it is necessary to have two user accounts one on each computer. The domain model provides a single logon process for users to gain access to network resources.

Active Directory Service is the service used to implement the domain model. With the ADS, all the information needed to use and manage printers, shared folders and other resources are stored in a centralized location and the process of locating and managing these resources is simplified. The term directory in ADS refers to a database, which stores information of network objects or resources.

The ADS can be installed on the computer, which is running Server OS windows 2002 or 2003. The process of installing Active Directory on a Windows Server 2003 server computer begins by launching the Active Directory Installation Wizard often referred to by its executable file, DCPROMO. Using dcpromo.exe, we can install and remove Active Directory from a Windows Server 2003 computer.

The computer on which Active Directory Services is installed is called domain controller. It is from this computer that the network administrator manages the domain.

 

How to setup a DNS server

DNS is a service, which translates computer names (host names) to IP addresses. The DNS server is a machine on which DNS service is installed. The DNS service helps to identify the computers on the network. The Domain Name System (DNS) is the standard name resolution strategy used on Windows Server 2003 systems.

In a practical scenario, when an application is running on the network, the client frequently requests data from the SQL server residing on the server. Whenever a request is issued there is a necessity for the client’s identity. The DNS server helps to resolve this identity. At the application level, the DNS server is not involved in the scheme of things. The DNS server operates only at the OS level and helps to open a channel between the server and a client. Once the IP addresses are set and the application is installed, there is nothing to be done on a day-to-day basis. DNS is most commonly associated with the Internet. However, private networks use DNS extensively to resolve computer host names and to locate computers within their local networks. If a DNS solution is not available or installed when you set up domain controller, we won’t be able to create the domain. Therefore, you need to have a working DNS server before we can install Active Directory. If not, Active Directory Installation Wizard will offer to create one for you.

How to setup a DHCP Server

In a network, if the number of clients are more, setting up IP addresses manually, is not practical. For such cases, an additional service called the DHCP (Dynamic Host Control Protocol) service is provided by Windows operating system. This service allocates an IP address to each computer when the system boots. The advantage is that, when there are more number of computers on the network, the need to give one unique IP address to each one of the computer is eliminated.