Data Valley & SQL

Data Valley can now, optionally be run on an SQL server.

An SQL Server is a client/server relational database management system. It is designed to deliver high performance, sophisticated security and mission critical reliability.

This offers many advantages over a standard Microsoft Access Database including:

• Greater Resilience
• Faster Processing
• Less Network Traffic
• Improved Reliability
• Tighter Security
• Guaranteed Recoverability

The database engine is server-based (no database engine resides on the client). All read/write operations to an SQL Server database are done via calls from the client to the server. The client machines have no direct I/O with the database file system. This eliminates the multi-user issues with traditional desktop database engines, which must synchronise with one another. Since the Server Engine processes all read/write operations it knows the status of all current/pending requests.

An SQL Server database will usually result in less network traffic than a desktop database engine in a multi-user environment since much less data is transferred over the network and so the database application (Data Valley) uses less bandwidth. This is because only the records requested by the application are transferred.

There are many features affecting performance, integrity and security that can only be achieved via server-based engines.

Below are a few examples:

• Triggers: Perform actions automatically in response to events such as adding, inserting, or updating data. These events are defined centrally at the database level rather than in the application. This means that the events only need to be defined once and cannot be disabled by the application or user interface. Triggers are especially useful for ensuring business rules are taken care of at the database level. This actually enables a server database to be less proprietary than a client-based database since client-databases typically must have the business rules hard-coded in the application and thus require interfacing through a particular application to ensure no violation of business rules.
• Stored Procedures: These are similar to triggers and allow control of the data retrieval and update processing. Stored procedures have performance advantages over direct data-binding, since values can be passed to the procedure in the form of parameters and the procedure can limit the return of data to just certain parameters or row sets. This relieves the client application from having to marshal data definition information and perform filtering of each row of data across the network. In addition, stored procedures allow the definition of prescribed interfaces and secure data from ad-hoc access thus ensuring business rules are not compromised.
• Authenticated Data Security: Security is defined at the server level and integrated with operating system security. In this manner, additional usernames and passwords do not need to be defined in order to control access from the database level all the way down to the row/column level through view/column level security.
• Automated Server Jobs: Tasks defined at the server level can be used to ensure that the server executes database backups and other maintenance tasks at prescribed intervals.
• Guaranteed Recoverability: Client-based databases depend on manual backup procedures and typically cannot be backed up or have other maintenance performed on them unless the all connections to the client database have been closed. Access database backups frequently turn out to be corrupt and unusable due to the database being copied while activity is still occurring on the database. Server-based database backups do not have this problem since the server engine takes a snapshot of the database and uses transactional control to ensure a logically consistent database backup.
• Transactional Consistency: Server-based engines support transactions at the server level through a database logging facility. This ensures that all transactions either complete entirely or are rolled back entirely. All transactions on a server-based engine are atomic (they must complete either entirely or not at all), isolated (shielded from the impact of other concurrent transactions), durable (persist regardless of other transactions), and consistent (not compromised by intervening transactions). No such guarantee is given for client-based engines. Although Jet has a transactional API, there is no guarantee that transaction rollback will be successful if any problems are encountered with the underlying network connection, application, or file system. Through database logging all operations in a server-based RDBMS, the database can be kept in a consistent state regardless of application, network, or operating system failures. Server-engines allow mirroring and continuous backup of database files and log files to even withstand hard-drive failures. In addition to transactional consistency on the server, most server-based RDBMS products have a transaction dispenser that supports two-phase commit. This allows server-based databases to participate in distributed transactions that span multiple servers and allows guaranteed consistency where multiple (both heterogeneous and homogeneous) databases or servers are involved.
• Connection Pooling: Server-based engines have the capability to monitor connections and automatically reuse connections rather than requiring opening and closing of network pipes. Client-based databases do not have this facility since each connection is done through the underlying file system and is not centrally controlled. This feature is especially critical for heavily used web databases since there may be thousands of users connecting and disconnecting in a given day.

Hardware Requirements:

We would need to liaise with your hardware and systems technicians to ensure that your SQL server is correctly configured and that the necessary facilities are in place to enable you to take immediate advantage of the improvements in the operation of your Data Valley System but to give you an idea of the minimum Hardware requirements:

The Server:

A Machine dedicated to running SQL Server