What's Not to Love About the Speed and Safety of SQL Servers?

For those of you who don't know, SQL is a standard language for building a relational database and easily getting valuable information out of it.  We often talk to IT professionals that don't know much about it and many small businesses that won't even consider it because they believe it is very expensive.  They should be more afraid of the numerous threats to their data -- disk corruption, natural disaster, human error and server failure, among others. There's nothing to be afraid of--the SQL Express version is free!  Here, I've compiled a list of reasons why SQL is a superior choice for  your business.

SQL Management Studio allows a variety of interaction with the database - can monitor performance, backups, security permissions and manage queries.

Besides this being the best tool for developers and database administrators, its user-friendly interface allows even those with moderate technical skills to easily create maintenance plans, backup, restore and migrate databases.  It also provides a single access point to query multiple databases using the widely accepted transact-SQL language.  I like to point and click whenever I can, but that's not so handy when there may be thousands of records.

Performance

Get faster processing of your time, expenses, and billing data.

With Microsoft Access, all tables involved in a form, report or a query are copied across the network from the server to the client's machine. The tables are then processed and filtered to generate the required record set. So maybe you only want to see your timecard for last week, but BillQuick has to pull over the entire Access database over the network just to view it! Contrast this with SQL Server where the filtering takes place on the server and only 1 record is transmitted over the network.

This can affect performance as SQL Server is highly optimized and can usually perform the required filtering much more quickly than the client machine and the amount of data sent across the network link is vastly reduced. For most databases the main performance bottleneck is data transmission over the network hence reducing this can give a really dramatic improvement in performance.  Predicting likely performance improvements is very difficult but an average overall speed improvement of 3 to 5 times, and possibly much more, would not be unexpected.

Scalability and Multiple Users

Your database can grow as you do and is the perfect match for organizations of all sizes.  Regardless of whether our customers are big or small, those that use SQL have the best possible backend for our software.

Performance with a Microsoft  Access database will 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.   You can expect robust performance in a multiple-user environment.

With Access each client reads and writes directly to the raw data tables. If a client machine crashes while writing data this will usually cause the back-end database to also crash and become corrupt. The same thing will occur if the network fails, has a glitch or temporarily becomes overloaded. This problem becomes more apparent as the amount of data or the number of users increases.

With SQL Server the clients do not talk directly with the tables but with an intelligent data manager on the server. This in turn reads and writes data from and to the tables. If a client machine crashes, or the network hiccups, this will not affect the underlying tables; instead the data manager realizes that the transaction has not been completed and does not commit the partially transmitted data to the database. The database therefore continues to run without problem.

Access databases may have problems with any more users than this logged into BillQuick. Some businesses may be OK with Access if their usage is light and it between only one or two people using BillQuick, then of course a SQL server is not necessary.  Much like Microsoft Word is not necessary if you just want to type a memo!

Reliability and Data Integrity

SQL is also a more reliable option with almost no chance of data corruption.  In tech support, we come across our share of databases that require our intervention because someone's computer crashed which then locks out all the other users from accessing their standard database.  This would not happen using SQL.

The client/server system also maintains an automatic 'transaction log'. 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 is designed for mission critical systems and is more reliable than a file server system. On one system that we support the client used to experience around one to two crashes per year (admittedly their network was not exactly state of the art!) when running with an Access database. After we converted it to SQL Server two years ago the system has not experienced a single crash.

Data integrity in SQL Server is enhanced by the use of 'triggers' which can be applied whenever a record is added, updated or deleted.  These can add greater functionality to BillQuick as this occurs at the table level and cannot be skipped over from the client machine.  Our consultants have created triggers that provide specialized capabilities for some customers needs.

Network Traffic/Speed and Low Bandwidth Networks

We often receive calls from IT staffs who are usually surprised to hear they already have a system that can effectively run SQL.  Although we wouldn't recommend an ordinary desktop run as a SQL Server, we have encountered many who have had no problems at all with it!.  Everyone one can benefit without upgrading their hardware.  As can be seen from the previous section, network traffic is greatly reduced in a client/server scenario.

This both improves network reliability (by reducing collisions, etc.) and also improves the performance of the network for other software (as there is less traffic on the network). Where there is a slow connection, such as over a telephone dial-up, Access is usually so slow as to be all but unusable (obviously this does depend upon the amount of data) whereas a SQL Server application, if designed for this environment, can still be perfectly useable.

Network Traffic Graph in Windows

This occurs when you are accessing your database over a connection that only supports low data speeds, which, for all practical situations, means anything other than a LAN. In all low bandwidth situations Access/JET usually performs so slowly as to be unusable whilst a correctly designed SQL Server system can be similar in speed to running it over a LAN.  Many small businesses rarely test their network cards and routers  for reliability and packet loss and other causes of data corruption.

Disaster Recovery:

SQL Server is very easy to restore after a disaster (proper backup and recovery strategy needs to be implemented) as compared to other RDMS. All you need is File system restore followed by system state and then restore databases from backup. I have been involved in DR for a lot systems and doing DR on a SQL Server is much easier than any other RDMS system. Other benefits include Failover Clustering and Transaction Replication.

There are numerous threats to your data -- disk corruption, natural disaster, human error and server failure, among others.

For instance, one if the many services provided by BQE's own 10 Point Tune-Up is Secure Storage—we back up and store your database on our secure server, so your data is always safe and accessible.

So in summary, we believe that all BillQuick (Professional and Enterprise version) users who want the most from their time & project management software should upgrade to SQL if they haven't done so already.  The standard Access database that comes with BillQuick is great for evaluation and testing as there is nothing for the user to do except to install one setup file.  They can begin entering their first time entry within minutes.  However, to unlock the full capability of your software, we recommend our customers use Microsoft SQL Server.

If your business is ready to move away from the slower MS Access, we can help you with the installation of the free SQL express for a nominal fee. Contact your BQE Account Manager.