Benefits of Moving from V2 Jet to SQL

As you are aware the Access database is becoming a dated technology and TSG will not be developing MRM on this older platform. All future development of the AUP Roadmap is being written in SQL, which may mean in the longer term you will miss out on some very useful EnterpriseMRM enhancements. More importantly by moving to SQL your database will be more reliable, with increased performance.

The recent releases of the product contained the following major enhancements, together with other enhancements, which are only available in the SQL version:

Recording Inbound / Outbound Emails: Add-in to integrate with Microsoft Outlook, allowing sent or received emails to be easily recorded in an activity record. (For Outlook 2007 only)

Statistical Report - New module allowing statistics on key data to be generated for a defined period, e.g. New/former members, analysis field summaries, etc.

Email Renewals and Reminders
An upgrade to SQL will involve a day onsite for the following:

• Installing SQL Server software on to your server
• Converting the Access Database to SQL – this is called ‘upsizing’
• Testing
• Updating MRM to give you the latest version, and giving you an overview of the new features

The daily rate for our services is £750 + VAT.

If you have any technical questions, please feel free to use the support team for this.

Lastly please find set out below information relating to both SQL and Microsoft Jet Databases.

Microsoft Jet and SQL Server

This document describes the principal differences between the two underlying database technologies available to host the EnterpriseMRM range of software.
The features of Enterprise are identical irrespective of the underlying database technology and no additional software training would be required.

The Technologies
The two technologies are referred to as Microsoft Jet and SQL Server. Microsoft Jet is a file-based system that uses the network sharing technology in the same manner as you would share a Word document or an Excel spreadsheet.
SQL Server employs a Client/Server approach which essentially shifts the workload to the server after which only the results are returned to the client requesting the action.

Microsoft Jet
With the Jet engine each machine reads and writes directly from and to the raw database tables and this is happening concurrently with each and every other user sharing the application. With a small number of users, this is not problematic, but as the number of users increases, the chance for corruption also increases.

By accessing the data tables direct, the database becomes susceptible to problems arising from client machine problems. A machine crash whilst writing to the database will cause corruption which can generally be repaired but sometimes may be too severe to be recoverable after which, it is a requirement to restore to the last known good backup and regenerate the work that has been lost. The scale of corruption that may occur as a result of such an event is an unknown quantity as it very much depends on what process was occurring when the machine crashed.

Similar experiences will be had with network failures or glitches or even if the network becomes temporarily overloaded as data, which may be vitally important to the database, can be discarded. Such issues become more prevalent as the amount of data increases.

SQL Server
With a dedicated database server such as SQL, access to the raw database tables is handled entirely by the database server. The client machines simply talk to an intermediary system (data manager) that will only commit the action if it has all of the information. Therefore, if a machine were to crash before the database server had received all of the data required, the server would simply ignore the partially submitted data and the rest of the database would continue unaffected.
 

The database server also maintains an automatic 'transaction log' and if a backup has to be restored, the transaction log can be run and should restore all completed transactions up to the time of the crash.

SQL Server is designed for mission critical systems and is many more times more reliable than a file-based database server and is scalable regardless of whether the number of users or the size of the data increases over time.
Microsoft themselves have commented on the differences and suitability of the different database technologies in their Knowledgebase article Q300216.
 

"Microsoft Jet is a file-sharing database system. A file-sharing database is one in which all the processing of the file takes place at the client. When a file-sharing database, such as Microsoft Jet, is used in a multiuser environment, multiple client processes are using file read, write, and locking operations on the same shared file across a network. If, for any reason, a process cannot be completed, the file can be left in an incomplete or a corrupted state. Two examples of when a process may not be completed are when a client is terminated unexpectedly or when a network connection to a server is dropped.

Microsoft Jet is not intended to be used with high-stress, high-concurrency, 24x7 server applications, such as Web, commerce, transactional, and messaging servers. For these types of applications, the best solution is to switch to a true client/server-based database system such as Microsoft Data Engine (MSDE) or Microsoft SQL Server. When you use Microsoft Jet in high-stress applications such as Microsoft Internet Information Server (IIS), customers have reported database corruption, stability issues such as IIS crashing or locking up, and also a sudden and persistent failure of the driver to connect to a valid database that requires re-starting the IIS service."

Performance
With Microsoft Jet, all tables used in a query are copied from the server to the client machine making the request and then processed to generate the required set of records. Queries are used extensively throughout Enterprise for selecting records from the Find window to reporting. Renewal processing is also heavily dependent on queries and whilst we have optimised the queries used within the software, the nature of the Jet engine still causes unwanted data to be transmitted to the client machine.

SQL Server however, handles all of the management and execution of the queries and returns to the client only what is required. This reduces dramatically the load on the network and for most databases the biggest bottleneck is the transmission of data from the server to the client now that workstations are equipped with processors as equally powerful as those found in servers.

A file server system such as Jet is designed for small workgroups and is scalable to perhaps 10 concurrent clients. Above this level performance starts to degrade rapidly as more users are added. With the SQL Server client/server architecture many hundreds or even thousands (with the appropriate infrastructure) of concurrent users can be supported without significant performance degradation.

Other Considerations
Sharing a file on a network share is a whole different beast to managing a dedicated database server platform. Although it has improved in terms of management and user friendliness, it is less suitable for an organisation with no internal or external IT support staff.
 

SQL Server also carries and increased cost from the point of view of the Enterprise software and also from the software licensing viewpoint for SQL server.
 

Summary
Benefits;

• Improved reliability
• Better performance
• Reduced network traffic
• Increased scalability