Is it time to think about SQL Server?

Topic

New Reply

I constructed a database (with a LOT of help and hints from here – Big thanks to all) for work back in 2006 that has become an integral part of our day to day running. We are currently running Access 2007 in a front/back end setup with the backend being stored on a NAS box. There are 7-8 users and my 5 main tables having 10-15,000 records with one at 76,000 and the largest at 116,000 as well as several smaller tables. Our internal network is all gigabit connections but recently we have been noticing longer processing times in opening forms and manipulating data.

When I am developing (forms in design mode) I find that some forms take an age (up to 2mins) to switch from field to field or mainform to subform or back again. It definitely gets worse when everyone is connected so I try and do developing in the morning before the office staff gets in.

I am not a professional programmer but I do understand basic coding. I don’t do it enough to remember proper syntax but I normally am able to read and understand what the code is doing and make small edits to achieve what I want. We don’t have a central server, just a workgroup setup as the backend is the only shared file.

Are we getting to the point where it would be worth looking at SQL Server or would splitting the backend into 2 (2006-2011 and 2012-current) and having code to switch the linked tables when needed be a better way to go?

This is way above my computer knowledge (call me a knowledgeable enthusiast) and I am looking for serious advice without anyone trying to sell me anything.

Thanks in advance!

Reply | Quote

Replies

Hi,

It’s a good question, and a good time to ask it.

Access has a bit of a reputation among computer pros, to the point that some think it should be banned or something. I don’t agree, and here’s why. Access is a great little database for personal use. It tends to be more approachable than the larger traditional databases (SQL Server, Oracle, DB2).

Where Access gets into trouble is that it becomes useful in an organization. And people start sharing the database. This works up to a point too, but slowly the Access weaknesses start to manifest themselves. Those weaknesses include limited database size scaling, multi-user support and database corruption. My rule of thumb is that most problematic Access databases started out as great personal databases. They get too popular and that’s their downfall.

However the downfall blame is larger than Access, and must be shared by the developers and management who did not plan to replace Access in time. So it’s great that you are thinking about this. It does concern me though about your skill level. Nevertheless, let’s continue.

When replacing an Access database for scaling reasons, there are 2 basic scenarios to consider (with some additional complications further in).

1). Access supports an explicit up-scaling design. The basic concept is, you split the database in a very specific way. The data itself all goes into SQL Server, but the logic (code) remains in Access. The Access database becomes a code-only construct, and all data requests are passed through to a linked SQL Server database;

2). You abandon Access entirely. The data gets converted to (let’s say) SQL Server, and all the logic is re-written in something else. C# is one popular choice, Delphi used to be popular, Ruby and many other newer languages have come to the fore. If this were a conversion to Oracle you might use something like Oracle Forms. You could also consider MySQL or PostgreSQL or MariaDB as one of the many newer databases, but the choices can quickly become overwhelming.

What I’ve hinted at thus far is a traditional “client-server” system. This is actually a bit dated as a design, but the whole concept is that the database is the server and the code is on the client. There are minor variations on this, so for instance SQL Server allows for code on the server in what are called Stored Procedures. This is not considered different enough to merit a new name other than client-server.

What a lot of people do instead, is to put the client itself on a server. A web server, to be specific. In the SQL Server world this web server is normally Microsoft’s own Internet Information Server (IIS). That way the actual clients only need a web browser to access the system, and of course web browsers are essentially standard equipment on any PC or equivalent.

However I’m only mapping out the potential architectures that could be in your future. Mainly to give you an idea of what the choices are. Since your skills are a bit limited, I think I’d spend the bulk of your time researching option #1. That allows you to preserve your Access skill set and the time invested in whatever Access forms and code you have developed.

The thing is, becoming a full-fledged database developer is not for the faint of heart. I’ve seen many, many user-developed databases start up. Usually the person (and the skill set) depart in time, and the database loses it’s prime mover. Also, database technologies are often sufficiently complicated that the users expect a fairly professional level of polish and functionality. It’s not the sort of thing you turn an untutored user loose on, just a large set of tables and a bunch of developer tools. Most can’t handle the learning curve or else they don’t have the time.

As an additional comment. Your suggestion of splitting your data set by date? I’m not usually a fan of doing this. Databases are built to get large and there’s no inherent reason why almost any database could not handle the database size you’ve talked about. The bigger issue is that of user access. Once you split a database this way, there’s additional work to query the older data and this increases the barriers to users actually doing so. I’m always wary of putting artificial barriers in the way of users and this is a textbook artificial barrier. There are enough barriers in systems design without adding artificial barriers.

Hope this helps.

Reply | Quote

Thanks for your reply. After a thorough readthru of your post I agree that option 1 is the best way to go and what I had in mind.

Considering the info given, would SQL Server Express be enough for my needs?

As for server software, would either of these be suitable:

Microsoft Server 2008 Foundation 64bit R2 ($99)
Microsoft Server Essentials 2012 R2 ($595)

or would I need

Microsoft Windows Server Standard 2012 R2 64bit ($995)

My local store has this on offer as a starter.

ASUS RS300-E7/PS4 Intel Server ($1995)

I’ve seen many, many user-developed databases start up. Usually the person (and the skill set) depart in time, and the database loses it’s prime mover.

This comment truly reflects our situation and while (all going well) I expect to work here until retirement (15yrs) I would like to futureproof as much as possible. We are an engineering workshop that has benefited greatly from my interest and assisted (thanks guys) ability to grow the database from its initial premise to the wide ranging tool it has become and I would hate to see it fall over because I was gone.

Reply | Quote

I don’t think the problems you have described will be solved by going to an SQL Server backend. In my experience, as companies experience growth in their databases, the forms the worked just fine in the beginning often exhibit many of the same performance problems you have described. Typical suspects are:
– Forms that have a very large recordset as the recordsource.
– Forms that have many subforms that are automatically requeried each time a new record on the mainform is viewed.
– Subforms with large recordsets.
– Comboboxes & listboxes with large rowsources.
– Lack of indexes on important fields (ones that are frequently searched/sorted).

Most of the issues above will not be solved by using SQL Server! Using a passthrough query will help comboboxes and listboxes to load faster, but will have little affect on the other issues.

What is the size of your backend? I assume when you said you had a split frontend/backend that each user has a frontend on their local drive?

Reply | Quote

The advice from Mark and BHarder is spot on. I don’t see any reason to abandon Access completely. I’ve spent over 20 years developing client/server applications, starting with a beta version of Access 2 and SQL Server 4.5, and currently have multiple installations of Access 2007/2010/2013 running on SQL Server 2012 and 2014. Some of them are over 2000 miles distant, and I’ve never been onsite. And the Express version of SQL Server is ideal for a situation like you describe.

The first thing I would suggest is to follow Mark’s advice on installing the front-end on each workstation if you haven’t already. The challenge when you do that is deploying new versions as changes are made. Several solutions exist, ranging from a simple batch file the simply pulls down a fresh copy each time the user starts the database, to sophisticated solutions that we use.

Then I would begin by learning the basics of SQL Server – there are a number of reasons for using SQL Server as the back-end in addition to the potential performance gains. They include reliability, backup capability, and extend-ability. SQL Server is a complex product, so you might want to take a couple of introductory classes or dig through a book or two. You will find it isn’t all that different in the basics, as there are tables, indexes, and queries (that can be saved and are then called views). You might also want to employ a consultant for a short period to help you through the perils of the process of upgrading. I have seen several situations where a basic upgrade was done, and the end result was a system that was slower (in a few cases much slower) because of fundamental design issues.

Let us know what you decide to do – there are a number of resources we can point you to should you decide to upgrade the back-end.

Reply | Quote

And the one thing that (almost) all database developers forget is backup. It is essential that you understand how to create and restore backups if you are going to use any database server. My personal preference is to use the database server to backup to disk (5 to 7 days is a good target if you have space) and then let your normal file backup do the rest. Then restoration is simple because you have one or more files at your fingertips.

cheers, Paul

Reply | Quote

One other large-scale issue probably should be talked about here.

You are facing, in this one system, the same set of choices that thousands of organizations face. That choice is between Build or Buy.

Once upon a time, in the mists of computing history, there was rarely any option to buy. That is, to license an application from a vendor. In the mainframe heyday there were very few Independent Software Vendors (ISVs). Now that has completely changed, to the point that most software is purchased. Building software in-house is a long and expensive process, and often requires expertise far removed from whatever your core business is.

However in my opinion the biggest downside of building your own software? You have a customer base of one. And this is a serious enough issue that thousands of in-house developed software packages from the past wound up being marketed to other companies as a side business. In those days that was invariably phrased as an “opportunity” rather than as a “disadvantage”!

That’s not to say that building your own is always wrong. And since you’ve already done so, my guess is that you want to carry on in that direction.

However it’s not a bad idea, if you haven’t already done so, to survey the market for commercial software. The rule is, if your software fills some proprietary need, or is a vastly better organizational fit than anything on the ISV marketplace, then in-house is better.

At this stage your development costs are largely expended and therefore in the past. A market survey therefore, mainly does the following:

1). Reassures you that the in-house created software is still the best fit;
2). The market has not changed substantially enough to change your choice, since the last time you looked at the market;
3). Protects your organization from you disappearing, and thereby leaving the organization in a bad place with respect to supporting this database.

Reply | Quote

Thanks for your continued input. I have been a reader of this forum for almost as long as I have been building/maintaining this database and came across the frontend/backend concept early and have been running like this for many years. I used code I found along with version numbering to keep my frontends current. Each time I commit a change the version number is updated in the backend and my frontend and my frontend is copied to the NAS box. It also writes a copy of the old frontend to a storage folder in case I need it. Each frontend checks its version number against the backend on startup and if needed, writes and launches a bat file which closes the frontend, copies a new frontend version in from the NAS box and then launches it and then deletes itself on completion. (thanks to Renaud – http://blog.nkadesign.com/microsoft-access/)

I currently have backup software that copies the backend database to usb media (1 for each weekday) as well as being copied to dropbox each night (its only 86MB in size) and a portable HD once a week, so I am well into the routine of that.

One of the reasons this database exists was because we couldn’t find software that did what we wanted. The manufacturing software we looked at seemed to be geared towards large scale production and inventory where most of our stuff is one off and material is bought per job. In most cases, 6 off is a major run for us.

When I first started we were part of a larger company who used us as a cash cow. They kept raking the money out while putting only the bare minimum back in. They did introduce computers with Office 97 with Access and the 1 day access training showed me the answer to the frustrations I was having with Excel. The foreman was willing to let me have time to see what I could contrive and it went from there.

Keeping track of hours was our first reason. Being access, customer job history then just became available, then delivery dockets were added, then quote forms using a shared items table and then QODBC to give us read access to Quickbooks to be able to pull purchase orders and costings for a job and then many little tweaks along the way to create something that suits us and how we work.

Question: Wendel, do I really need dedicated server software to run SQL Server Express on? (it runs on win7) I realise I need a server machine but as all our web hosting and email is done via our IP provider and that the Backend is the only shared file we have do I need server software?

From reading elsewhere, the fact that sql server does things there and then feeds you the answers rather than grabbing the whole table and then filtering it certainly sounds like there are definite advantages. I will certainly start looking into the workings of SQL Server, I’ve even come across an Australian SQL Usergroup forum. So, onward and upward!

Reply | Quote

From reading elsewhere, the fact that sql server does things there and then feeds you the answers rather than grabbing the whole table and then filtering it certainly sounds like there are definite advantages. I will certainly start looking into the workings of SQL Server, I’ve even come across an Australian SQL Usergroup forum. So, onward and upward!

This is actually incorrect. If you don’t change your frontend, SQL Server will return the same amount of records that you are getting now. If your decision is to go with SQL Server, you should look to change the front-end to minimize the data received from SQL Server. Of course, you can also change the way some processing is done, getting SQL Server to do it, but that really depends on what your app does and it will require learning, as well.

Also, bear in mind that SQL Server will require a lot more attention to some details, such as indexing. On a similarly sized table, you should expect worse performance from SQL Server if you don’t index the SQL Server table carefully (I have seen it!).

Reply | Quote

Question: Wendel, do I really need dedicated server software to run SQL Server Express on?

You don’t need a dedicated server for a database, but you do need something that users aren’t going to wreck by installing software or turning off mid flight. It’s really a value call based on how important your data is and how much work it’s going to do.

cheers, Paul

Reply | Quote

Some further questions and thoughts based on the posts of others:

[*]It’s still not clear as to whether you are deploying the front-end to each workstation – from your description it doesn’t sound like it. Numerous articles have been written on the subject; here is our rather dated rationale Why Split a Database?.
[*]Rui is spot on in terms of performance with Access being upsized to SQL Server – we’ve seen lots of cases where things actually got alot worse. However, it turns out that recent versions of the SQL Server ODBC driver do some tricks in certain cases that actually convert queries (that’s where most problems occur) into a T-SQL statement that lets SQL Server return only the data your are after, not the entire data of all of the tables involved. Having discovered that, the strategy we adopted was to upsize to SQL Server, and then test for performance issues and make the necessary design changes. Experience has shown that most queries with only two tables will be processed in SQL Server, but more complex ones will simply get all of the data from all of the tables and then run the query in Jet or Ace (in Access).
[*]As Paul notes, you can run SQL Server Express and SQL Server Standard on most any PC with a recent OS – I have a laptop running SQL Server 2008 Express that is still on Windows Vista. And it doesn’t absolutely have to be a dedicated system, but it should be stable and not used as a test system (although you could easily run a test version of the backend on it). The real trick in getting good performance in SQL Server is to have lots of memory available for SQL Server – we typically have it reserve 1GB for what we do, and we run in environments where there are million plus row tables involved. If finding a PC to host SQL Server becomes an issue, you may want to seriously consider SQL Azure, but there are recurring charges involved, and it will require further tuning to get acceptable performance. If you do decide to seriously consider that approach, I would suggest you look at using a consultant to guide your efforts – there aren’t many developers with experience using Access in that environment.
[*]The upsizing process involves making some decisions – note that you do not want to take the ADP route as that option has been removed from the most recent version of Access. The consensus seems to be to use the SQL Server tool mentioned in one of the articles in the link you noted relating to the deployment process. Here are some useful links on the subject of upsizing – Tips for Upsizing An Access DB….[/url] and Microsoft Access to SQL Server Upsizing Resource Center – there are numerous other web articles on the subject.

Reply | Quote

weyrman,

That last post filled in a lot of blanks. It seems that you’ve got a viable systems administration strategy in place and that’s seriously deserving of a shout-out:

– you’re making multiple backups and are doing so regularly;
– you’ve got explicit versioning and the clients auto-update.

Also, the on-off manufacturing model explains a lot. You are correct, most manufacturing software presumes large production runs of mostly identical products. I’ve also seen situations where a similar situation occurs: there’s a base product which is highly standardized, but there are so many options (and customers accessing those options) that it’s almost the same as a one-off manufacturing environment. In other words standard orders hardly exist at all.

The theory of linking to SQL Server from Access is that queries get restated into T-SQL syntax, from Jet syntax. Like-for-like functionality is applied, and SQL Server does as much as possible before result sets are returned to Access. There are syntactical constructs that Jet has that T-SQL does not and, as you would expect, in those cases SQL Server simply returns enough data to Access to allow post-processing on the client.

The rule of thumb concerning intelligent feature conversion is always, Don`t Expect Too Much. The vendor tries and there are database engines in place to perform this work. However it`s often surprisingly easy to fool the query optimizers and they often do dumb things. Each version tends to get better but overall, progress is slow.

Reply | Quote

I agree with jwoods. Yours is a relatively small database. I don’t think the number of records has anything to do with your problem. One of the problems with a shared database is getting everybody out of it so you can do your routine maintenance. Compacting is in my opinion probably the most important. It gets rid of all the Access garbage that builds up over time, kind of like emptying the cache on your browser. Usually speeds up the database or maintains it’s fast performance if you do it regularly, like everytime the database closes.

Reply | Quote

Why is it that the design of the database (back end) never seems to configure in any answers on most forums, irrespective of the database in use?

Your back end design should as a minimum, conform to Cods Normal Form Three… (Google it), if it does not it will never work at its optimum speed (for any relational database) and you will end up writing excessive lines of unnecessary code to rectify.

If there are any limitations in Access it has to do with how easy it is to make cascading mistakes – or use over a (Wireless) not hard-wired connection, here SQL Server is much better.

For the front end design never use any form where all the displayed fields that users can change are connected in any way to any database, load them once when the form opens and only after the users have edited/changed the data, only the save it using transactions.

Keep an open connection to the back end database where it’s on a server (obviously on the connection object with the minimum of records say one, I have a table with one record in it specifically for this purpose) this forces the connection to stay open whilst users have their front end open and as others have said never put the front end on a server, it goes on a local workstation with the back end on a server.

This also enables you to upgrade your front end (code and forms) by merely overwriting it on each workstation. Of course you may need to update the back end path, but this is easy.

Generally speaking if any of your tables in the back end database have any repeating data, except for indexed fields, then the design is wrong, it’s that simple.

Only if you go over (say) 50 users and a million records would you need to upgrade providing your database compact and repair tools are automated within the front end.

The experts in this field are Albert Kallal and David Fenton (now deceased)

Database design whilst simple in concept, is actually extremely difficult.

Reply | Quote

Thanks for stopping by to lecture us on all the things we obviously didn’t know because we only started using Access last week.

Reply | Quote

Thanks for stopping by to lecture us on all the things we obviously didn’t know because we only started using Access last week.

Sorry if I’ve upset you but my post was intended for wayrman and to fill in some gaps not previously mentioned.

I don’t lecture people, gave that up forty years ago… just try to point anyone in the right direction, nothing more.

Reply | Quote