The following paper outlines and compares MS SQL Server to Microsoft’s Access Package for the fictitious company CCI.

When comparing the MS SQL Server package against the Microsoft Access Package, which is powered by the “Jet” engine, many advantages appear. Eg: reliability, security, performance, scalability and supported object types.

SQL is more robust to recovery issues. As all actions are logged, this allows the user to recover a database at any particular point in a timeframe. Access is not as forgiving, it only allows recovery from a backup .mdb file. Backups are scheduled in SQL by utilizing the attendant service. Access does not have a comparative process and must use the Windows Task Scheduler. This has its own issues due to file-locking.

SQL’s security measures are second to none. Each user can be assigned different levels of access to particular resources. These users are sectioned into groups, known as roles. Active Directory can also be incorporated allowing a stream-lined setup process. SQL Server Authentication can be used to create users in a stand-alone capacity.

SQL Server’s performance is much more advanced than the Access package. For predefined procedures and other such objects, SQL creates execution plans which enable it to maximize the servers resources. It can support an unlimited amount of CPUs, not available on all packages, in comparison to Jet which only allows a single machine and no SMP Protocol. SQL’s memory capacity is far superior standing at 64-bit than the available 32-bit Jet engine.

All data in an Access database is stored in a solitary .mdb file. This must then be split into a front-end and back-end. The front end acts as the presentation layer for the user while the back-end contains the information stored in the database. The presentation layer limits the number of users to 255 and the back-end is limited to a 2GB size. All versions of SQL Server 2005 are not constricted by any of these restraints. They can handle multi-terabyte databases and the maximum number of users is determined by the network and hardware infrastructure.

There are similarities between both packages:

  • Data is organized into tables
  • “views” in SQL server and “select queries” in Access are comparable
  • SQL statements are saved as objects

There are other similarities but SQL’s superiority makes them diverse. The “views” profit from the advanced execution planning. Security measures allow different permissions than the absolute table. Many more stored procedures, segments of Transact-SQL, are available. This complex code is stored individually and is equivalent to methods or functions found in other programming languages. Access has the capacity for simple tasks such as update, insert or delete but other tasks must be implemented in VBA code which is not resourceful. The “trigger” in SQL is unique, but is “triggered” by an action such as update, insert or delete. This is effective, for example, if the user is adding a row to a table, then total will be updated accordingly in another table.

To utilize CCI satisfactorily, Access and SQL can be integrated together. The presentation layer can be handled by Access while SQL would deal as the back-end database. The report objects and forms in Access enable the user to interact with the data.

In determining which technology an organization should utilize, closer inspection of the requirements should indicate this. Both packages have their respective advantages and disadvantages depending on their necessities.

Small organizations with a limited number of users should utilize the MS Access technology as this is a cheaper and less code orientated option. There are other limitations which must be considered:

  • Not efficient for the internet,
  • Limited capacity of 2GB,
  • Does not offer the security of SQL Server,
  • Data corruption is a possibility,
  • Frequent maintenance must be performed,
  • Backups are not possible with system users,
  • Completed transactions cannot be nullified,
  • Bandwidth increases exponentially with data size,
  • Flexibility – Users must be running the same version of Access.

(Chung, 2004)

As Microsoft has developed both versions, it is worthwhile noting their comments,

“SQL Server is for more complex solutions, offering the critical back-end database that lets organizations scale their important databases securely”. ( 2005 )

Chung also mentions “SQL Server offers scalability, security, data integrity, and manageability”. ( 2004 )

Taking into consideration all of the above facts, the following conclusions are important:

  • SQL Server is designed to service a large amount of users at any particular time ,
  • It is the optimal choice for web based applications,
  • Security features offered by SQL are immense,
  • Data maintenance is not an issue with SQL,
  • SQL is more expensive and code-based, databases must be developed and maintained by a trained individual,

Chung dictates that Access is a quicker and cost effective approach to databases, whereas SQL is ideal for businesses that require security and scalability. As we intend to provide support to numerous users in 4 different locations, scalability and security, between managers and clerks, are prerequisites for our system. Taking into consideration all of the research performed, SQL Server technology is the best tactic for our application at CCI.

VN:F [1.8.8_1072]
Rating: 0.0/10 (0 votes cast)
VN:F [1.8.8_1072]
Rating: 0 (from 0 votes)