[RESOLVED] Should I upgrade Access Database to SQL Server

[dataempress]

Hi All,

I want to ask the forum's advice on upgrading to SQL server from Access 2007. I'm in the stage of skill building now. I've taken several coures (including Access and Visual Basic) and I know just enough to be dangerous. Though luckily I recognize the only thing I really know is how much I don't know. That's why I'm asking for advice from more experienced professionals. I work at a college and I've built several Access databases for our programs. They work wonderfully on my computer, but most of our data entry people work from laptops with wireless connections. All the databases are located on our department server, which means they are accessing them through the school network. A couple of our data entry people have complained that the databases are slow, sometimes very slow. I've done compact and repair, turned off the name autocorrect feature, changed the subdatasheet properties on the tables to none, and worked with the performance analyzer, which suggested I add indexes. I did this and it works very fast on my computer, but my computer isn't the problem. The problem is the databases are being utilized by several users concurrently across the network. I know this practically begs for performance issues, but there's nothing I can really do about it. I began to research SQL server and wondered if it might be a solution to my problem. What I could possibly do is convert the back end with the data to SQL server and leave the front end as Access. Right now it's just one mdb file. I don't want to be hasty though because I know nothing about how to do this and whether it would even help. The databases I design will never have more than 20,000 records and will never have more than 10 concurrent users. In fact, both will probably have significantly less. I'm just giving you an extremely high limit scenario. Given those limits is splitting the database and moving the back end to SQL server something worth considering, or am I better off keeping it all in Access and using other fixes. Also, twice a year I have to send one of the databases across an FTP file to another site. Would splitting it mess up that process? I'd appreciate any insight you can give me. Thank you!

[Hack]

I was all for converting until I read the part about 20,000 records.

That is less than insignificant for a database. I'd like to know what this means: "our data entry people have complained that the databases are slow, sometimes very slow" - what is "slow" to them? Have you, with your own eyes, experienced this "slowness"?

[Tom.Net]

Agree with Hack. I definitely think you can get additional perfomance & functionality using Sql Server instead of Access but that aside there shouldnt be any problems with a database that small even in Access. My first guesses would be that either the tables are not indexed and/or your querying more records and fields at a time then you actually need.

[dataempress]

Thank you Hack and Tom for the replies. At this point I feel like an ant looking at a puddle and thinking it's an ocean. A recordset of 20,000 seems huge to me. I'm relieved to hear that in the database world it's considered almost insignificant. What would be considered an unsustainable amount of records, just for my curiousity? I've never had any trouble with any access database on my own computer. Where it really seems to break down is with multiple users across the network. Sometimes, there will be 2-3 people entering data at a time, and I want to make sure it stays functional. To answer Hack's question, I did monitor the slowness and it was significant. The reason I thought of SQL server is because multiple users entering data with a wireless network connection isn't something I can change and I didn't know if making the back end SQL server would do any good in that area. When I ran performance analyzer it suggested indexing some tables like Tom mentioned, and it ran beautifully on my computer. I just need to test it out with the multiuser network environment. I've read that an Access database can support an upper limit of 255 concurrent users, but having had problems with just 3-4 I find that very hard to believe. Would anyone have insight on that? Again, thanks for your help!

[si_the_geek]

Please use paragraphs in your posts - it is hard to read a wall of text like that.


A table with 20,000 records is certainly fine, but a recordset with 20000 records is heading towards obscene (but occasionally justifiable), because it will slow things down dramatically - for the user who loaded it, and any others who are working with the database while it is loading.

The Access DB I've got open here has about 600,000 records across several tables, and could easily handle 5 million - but that is with my table design and data. The size of your data depends on what columns you have got (and for Text/Binary/etc based fields, what they contain).

The overall DB size limit is supposedly 2GB, but in the real world it is nearer to 1GB.


In terms of concurrent users, 255 is completely theoretical... in the real world you can generally only have about 30 if you design the system really well, and less if you don't (many get problems with more than 5 users).


Due to the wireless network, I suspect that SQL Server would give you a benefit... but how much depends on how your system is designed.

As a general rule, it is a bad idea to load more than a few records (about 200 at a time is about the most a person can deal with), especially if they are shown in an editable way (because it will block other users, at least temporarily).

[techgnome]

you'll probably get even more benefit by pulling the UI out of access as well and using VB (or C# if you're so inclined) to create a new front end. If you keep your front end in Access, then you'll end up with linked tables from Access to the SQL Server, which is one more layer, and could negate any performance you're going to get out of SQL Server (I could be wrong about this though). One thing you could do, is move the data to SQL Server, continue to use the Access front end, meanwhile build a new front end as a desktop app using VB.NET and ADO.NET to talk to the SQL Server, then when it's ready, shut down the Access front end, and deploy the new app.

-tg

[dataempress]

Please use paragraphs in your posts - it is hard to read a wall of text like that.

Sorry, it's the English major in me. I'll do better from now on.

A table with 20,000 records is certainly fine, but a recordset with 20000 records is heading towards obscene

I'm not sure what the difference between record and recordset is. I actually thought they were synonyms and starting saying recordset because it sounded more technical. Could you please tell me the difference?

The Access DB I've got open here has about 600,000 records across several tables, and could easily handle 5 million - but that is with my table design and data.

Normalization is my Achille's heel. No matter how much I study it, I just don't understand. The way I normalize things is to have one main table with a primary key of MainTableID and then a few additional tables with MainTableID as the foreign key.

I've heard of junction tables and studied them, but I have no idea how to get them to populate. I've heard that combo boxes are a way, but the way I design my UI's is to keep one main form, designed so that no scrolling is required and have the user click a button to get to other forms, which will open to the same record. I don't know that a different design model would be able to accommodate that ease of use.

In terms of concurrent users, 255 is completely theoretical... in the real world you can generally only have about 30 if you design the system really well, and less if you don't (many get problems with more than 5 users).

How do you design a system really well? I really wish there were classes. Does it start with table structure and relationships. Does it matter if let's say you know a text box on a form will only use a max of 10 characters, but you leave the default 255. Does stuff like that decrease performance?

Due to the wireless network, I suspect that SQL Server would give you a benefit... but how much depends on how your system is designed.

As a general rule, it is a bad idea to load more than a few records (about 200 at a time is about the most a person can deal with), especially if they are shown in an editable way (because it will block other users, at least temporarily).

How do you avoid loading all the records, because when a form and table opens, don't the records get loaded anyway?



Forgive me if these questions seem elementary. I feel like a toddler with this sort of stuff. I'm very excited and eager to learn, but there's a lot I don't know.

[Hack]

I'm not sure what the difference between record and recordset is. I actually thought they were synonyms and starting saying recordset because it sounded more technical. Could you please tell me the difference?

The difference between records and a recordset is the difference between how many records are physically stored in the database and how many records you ask to be retrieved from the database at any one given time via a database query.

How do you avoid loading all the records, because when a form and table opens, don't the records get loaded anyway?

That is probably true if you are using bound controls. Such controls should be avoided.

Use programming code in the form of SQL queries to get ONLY those records from the table that you need for any particular task at any particular point in time.

[dataempress]

The difference between records and a recordset is the difference between how many records are physically stored in the database and how many records you ask to be retrieved from the database at any one given time via a database query.That is probably true if you are using bound controls. Such controls should be avoided.

Use programming code in the form of SQL queries to get ONLY those records from the table that you need for any particular task at any particular point in time.

So you're saying that forms should be bound to queries not tables? Controls on forms should be bound to tables though, right? I want to make sure I'm following correctly. Also, if I have a couple hundred records that are inactive, can I prevent them from loading? Would that help performance?

[si_the_geek]

Normalization is my Achille's heel. No matter how much I study it, I just don't understand. The way I normalize things is to have one main table with a primary key of MainTableID and then a few additional tables with MainTableID as the foreign key.

It sounds to me as if you have made your tables more complex without getting any benefit - but I could be wrong.

The idea of normalisation is to avoid repetition, so if one of the fields (or a group of fields) in your main table has the same data multiple times, you would use a separate table for that field (or fields), and store the ID of that table in the main table. This not only means less space, but faster queries too.

In the "Design" section of our Database Development FAQs/Tutorials (at the top of this forum) is a simplified guide to normalisation, including example data that gets reduced by it.

... I don't know that a different design model would be able to accommodate that ease of use.

It is rare for the database design to have a massive effect on the capabilities of the user interface - there are usually ways to re-arrange things appropriately.

How do you design a system really well? I really wish there were classes. Does it start with table structure and relationships.

Yes, because that can dramatically reduce the amount of data that gets edited (and thus reduces the damage of typos), the amount that gets sent to the users (thus increases speed), and various other benefits.

Does it matter if let's say you know a text box on a form will only use a max of 10 characters, but you leave the default 255. Does stuff like that decrease performance?

That has almost no effect on performance, and can be ignored.

If however you are completely certain that valid data for that field can never be more than 10 characters, you should not allow more than that to be entered - because at some point a user will make a mistake, or intentionally put extra data (such as notes) in there.

How do you avoid loading all the records, because when a form and table opens, don't the records get loaded anyway?

They do, but do you really need to have all of them loaded? (if not, why are you loading them all?)

So you're saying that forms should be bound to queries not tables?

Queries are definitely the way to go (as you limit the data, perhaps dramatically), but whether they should be bound is debatable.

What the queries should be depends on the system. For example, if the data is something to do with people, the query might be a search on surname - which only gets run after the user types the surname they are looking for.

Controls on forms should be bound to tables though, right?

Binding at all is generally bad for multi-user systems, as any editable recordsets block other users from editing (and sometimes even reading) the same data - so that slows things down, and it rises exponentially with the number of concurrent users.

It may have improved in recent versions of Access, but that is doubtful as it is designed to be a single-user database system.

The usual way to deal with it is to not bind at all, and instead read the data using a recordset variable which is read-only, and use code to place the data into the controls. When you want to edit/delete the data, use SQL statements (such as Update) or an editable recordset which contains only the relevant record.

If the number of concurrent users you have is low, this part may not be necessary (or a simpler version may be fine) to get the speed up significantly - the queries etc may be enough.

Also, if I have a couple hundred records that are inactive, can I prevent them from loading? Would that help performance?

You can (by adding relevant criteria to your query), and it is likely to help performance slightly.

[dataempress]

Thank you all for the great advice!

I decided to go ahead and split my access database, due to having multiple simultaneous users across the network. I will work on using your advice for improving performance to make the application even better.

I do have another question though, that's a followup to my split question. Including myself, there will be about seven other users who will need to have the front end on their computers.

I make updates to the front end regularly, whether it's tweaking the forms, or adding new reports, etc. I'm the only one who ever makes updates to the database. Everytime I make an update, do I have to reinstall the front end on all seven computers, or is there a way to make the update across all the front ends? Or is there a simpler solution I haven't mentioned?

Thank you!

[Tom.Net]

Yes if you make changes to the front end you need to recompile & redistribute the exe to each of the computers (unless your running the exe from a shared drive). How else would anyone see the differnt versions?

[dataempress]

Yes if you make changes to the front end you need to recompile & redistribute the exe to each of the computers (unless your running the exe from a shared drive). How else would anyone see the differnt versions?

I was really hoping there was some code or something. I know there's all sorts of magic out there that I would never have dreamed possible a couple of years ago.

It just seems so messy to have to redistribute the front end to all computers every time there's a change. This isn't my situation, but if users were spread across a large geographic area, it would almost seem counterproductive to split the database.

Then again, my situation is that I'm the informal guru of our program database, so I get asked to make changes quite often. Some of these only take a couple of minutes, but I can't imagine having to redistribute my front end every time I do something small like that. I'd hoped there would be a way around or a work around.

Thank you for your reply!

[techgnome]

The application I work on is used by users all over the North American Continent... and then some. From Canada, down to Hawaii, to Alaska and to Florida. And I'm sure there's points in between I'm not aware of. We house the database in a central location. The application itself is actually deployed over the company extranet, using ClickOnce. So the users, login to the VPN, click a link on a web page. If there is a new copy of the app, it is downloaded and installed on the user's machine, and then automatically starts. If there isn't a newer copy, the app simply starts up. Easy peasy. But, I should note that our app is a .NET app.... and we're not using Access forms as our front end.

-tg

[Tom.Net]

I have the same problem here. Two ideas (but not limited to this); 1st you can make an additional app (update program) that can delete the existing exe from the client pc's and copy the new exe file from a shared drive to replace. Or you can set the exe on a shared drive with a short cut that points to it on each of the clients. This way you only have to replace the exe in the one spot. The problem with this is that you have to make some security changes to allow for connecting to the exe on a shared drive.

[Pradeep1210]

From what I've known Access allows only 10 concurrent connections. My knowledge might be outdated though, as I've not used it for several years now.

So if you are working in a multiuser environment open connection get the data and close it as soon as possible so that it is available to other users.

[Tom.Net]

Ive seen so many different quotes on the connection limit (20-255) and not sure what is actually the truth. I would think that less then 10 users wouldnt be a problem but if its a concern thats one more reason you may want to start with Sql Server instead. Additionally with Sql Server you will benefit by connection pooling. Its a great advantage to allow a user to create multiple connections at the same time as needed and definitely helps with performance.

[dataempress]

Ive seen so many different quotes on the connection limit (20-255) and not sure what is actually the truth. I would think that less then 10 users wouldnt be a problem but if its a concern thats one more reason you may want to start with Sql Server instead. Additionally with Sql Server you will benefit by connection pooling. Its a great advantage to allow a user to create multiple connections at the same time as needed and definitely helps with performance.

I've had problems with three concurrent users across a network. That's why I started this thread asking for help. So as far as the truth goes, I would put the number of users as smaller rather than larger. That's just my own experience, though. I'm sure there are a lot of factors that play into it.

[Tom.Net]

Any reason your not trying Sql over Access? Sql Server will give you all the connections ya need, plus as I said above connection pooling is extremly benificial to performance. It will (if coded so) allow each individual user to open as many connections as needed to perform a task. So if you have hundreds of records to insert by a single user, you can give them the permission to open as many connections as needed for the individual task.

[Pradeep1210]

Any reason your not trying Sql over Access? Sql Server will give you all the connections ya need, plus as I said above connection pooling is extremly benificial to performance. It will (if coded so) allow each individual user to open as many connections as needed to perform a task. So if you have hundreds of records to insert by a single user, you can give them the permission to open as many connections as needed for the individual task.

And why should I need more than one connection (at-most two) at a time?? Isn't that an extremely bad design if I'm needing to do that anyways?

[Tom.Net]

And why should I need more than one connection (at-most two) at a time?? Isn't that an extremely bad design if I'm needing to do that anyways?

I think your misunderstanding. Connection pooling isnt say giving each user a hundred connections for the life of the program that are just sitting there. Instead say you have something like an import that needs to insert hundreds/thousands of records at a time. Rather then having a single connection and waiting for each of the hundreds of records to insert individually, you can set that command to open connections as needed and it can then transfer the hundreds of records at once and then close the connection. The end result should be a single connection for each user but gives the ability to open additional connections at specific times when needed

[dataempress]

Any reason your not trying Sql over Access? Sql Server will give you all the connections ya need, plus as I said above connection pooling is extremly benificial to performance. It will (if coded so) allow each individual user to open as many connections as needed to perform a task. So if you have hundreds of records to insert by a single user, you can give them the permission to open as many connections as needed for the individual task.

That's an excellent question. The answer is simple: lack of knowledge in how to do it.

[Pradeep1210]

That's an excellent question. The answer is simple: lack of knowledge in how to do it.

Hmmm... So download SQL Server Express Edition (its free) and get started today.

[Tom.Net]

Connection pooling is simple, in fact its enabled by default in Sql Server in you connection string and your using it without even knowing it. To change the limits of connections per user, its as simple as adding a specifier in your connection string.

From the MSDN

Pooling connections can significantly enhance the performance and scalability of your application. Connection pooling is enabled by default in ADO.NET. Unless you explicitly disable it, the pooler will optimize the connections as they are opened and closed in your application. You can also supply several connection string modifiers to control connection pooling behavior.

[dataempress]

Hmmm... So download SQL Server Express Edition (its free) and get started today.

That's all there is to it? When I was research this, I thought SQL Server was really expensive and time consuming. Before about a week ago, I knew nothing about it. I still don't know much, other than I hear it's better than Jet Engine. I just don't want to screw anything up. Could I still keep Access as my front end? I didn't build this database. It was built for our program by program headquarters.

[Pradeep1210]

Yes that's right. Connection pooling is a good thing as it allows multiple connections to work concurrently. But I've never faced a situation where any single user would need to have two or more concurrent connections to the database; nor would I ever design my application in that way unless there is absolutely there is no other way to it.
So the real place where connection pooling would be used is when two or more users connect to the database at the same time.

[Pradeep1210]

That's all there is to it? When I was research this, I thought SQL Server was really expensive and time consuming. Before about a week ago, I knew nothing about it. I still don't know much, other than I hear it's better than Jet Engine. I just don't want to screw anything up. Could I still keep Access as my front end? I didn't build this database. It was built for our program by program headquarters.

I think tg already answered this in post #6

[dataempress]

I think tg already answered this in post #6

You're right. Thank you for pointing this out.

[Tom.Net]

So the real place where connection pooling would be used is when two or more users connect to the database at the same time.

You obviously misunderstand connection pooling and should read more about it. It has nothing to do with multiple users and is automatically turned on by default.

[Tom.Net]

No you wouldnt have Access as your front end if your using Sql Server as your database, you would need to develop your front end in an application such as vb.net or C#

[techgnome]

That's not entirely true .... you can create an access front end that uses linked tables to SQL Server for the back end. Ive seen it done before. It works, and it works reasonably well enough for a temporary solution. But it's not one I'd use permanently.

-tg

[leinad31]

Tom.Net: connection pooling is not a panacea (cure-all). You also don't seem to understand the implications of opening all those connections. I'm sure if you spent some time researching on it from the DBA's/SysAd's point of view you will understand what I mean. Besides, thread starter has not committed to an MVC or web-based solution; connection pooling (one connection per insert query?!?) in a client-server environment is overkill.


dataempress: more often than not, the problem is not the technology, but rather how it was used. As mentioned earlier, maintaining copy of data in client recordsets is easy but not scalable (can you imagine scenario wherein vbforum loads all threads, from the very first to last, into a local recordset maintained in PC memory but will only display those from the last few days? Not a very good design).

If you have time to spare, read up on how you would develop your front end as web based, especially design of thin clients. That would help drive home efficient use of resources, performance (internet/network lag also taken into consideration), concurrency, and scalability issues... connection pooling will also become applicable (mid-tier to database connections).

[NeedSomeAnswers]

I thought i would chip in with my two pence worth.

You have been given some good advice on this thread, but i would just re-iterate this.

Access is a terrible database, and yes i know that with good design you can get it to work perfectly well for a fairly small user base, but that's not the point, its terrible over a network and it slows down your other network traffic as well as your system.

Also when you can get a completely free version of SQL EXPRESS 2005 why would you use it any more ?

You big problem DataEmpress is that if you move your back end to SQL Server & leave you front end in Access although this is perfectly do-able i think you would be disappointed in the speed improvements you would get.

Linked tables are not great, trust me i have had the pleasure of looking after a system where they had done just this (move the back end to SQL Server while keeping the front end in Access) and it is just not a great solution, it works but it's not great.

The best solution would obviously to have your database as SQL Server Express & then move your front end to a .Net application, however i understand that might be difficult for you to accomplish.

It might be simpler in the short term to get your "data entry people" to use wired connections. Have you tested if there is any difference in speed if you plug one of the laptops into the network against wireless ?

[leinad31]

If client-server architecture must be retained in the interim, you may also consider Windows Terminal services so DB is local to OS (imagine each user using your application via remote desktop but multiple users can connect). It will not be scalable in terms of users though (Terminal server will need lots of memory). Purpose is workaround to networked DB access overhead until a better solution can be implemented.

[techgnome]

Here's how connection pooling works:
* Client side code requests a connection using ABC Connection String
* Server looks in its pool to see if there was a previously used connection with ABC Connection String
* Not finding one, a new connection is spun up and passed back
* Client uses that connection to insert nine thouuuuuuuuuuuuuuuuuuuuuusand records
* Client code closes connection
* Server side puts the connection into the pool
* Another client side code requests connection using ABC Connection String
* Server says "AH-ha! I have one of those!" And returns that connection.
* If at this point, another request comes in, a second connection is spun up
* When clients close connection, the server returns them back to the pool.
* Now the server has two connections in the pool.

Connection pooling has nothing to do with what the client is doing. It is based completely off of the connection string, and is simply a way of being able to retutrn connections to the client faster. It's intended to encourage developers to open connection, do work, close connection, rather than opening connections for the entire life of the application.

-tg

[Tom.Net]

Tech, well put

[leinad31]

Here's how connection pooling works:
* Client side code requests a connection using ABC Connection String
* Server looks in its pool to see if there was a previously used connection with ABC Connection String
* Not finding one, a new connection is spun up and passed back
* Client uses that connection to insert nine thouuuuuuuuuuuuuuuuuuuuuusand records
* Client code closes connection
* Server side puts the connection into the pool
* Another client side code requests connection using ABC Connection String
* Server says "AH-ha! I have one of those!" And returns that connection.
* If at this point, another request comes in, a second connection is spun up
* When clients close connection, the server returns them back to the pool.
* Now the server has two connections in the pool.

Connection pooling has nothing to do with what the client is doing. It is based completely off of the connection string, and is simply a way of being able to retutrn connections to the client faster. It's intended to encourage developers to open connection, do work, close connection, rather than opening connections for the entire life of the application.

-tg

That is only ONE aspect/purpose of pooling; due to request-response (web) environment where opening and closing connections will take too much time otherwise.

Also, a single username in a client-server environment (just to justify use of pooling) means deployment of credentials with app; not my cup of tea.

[Tom.Net]

That is incorrect, the use of pooling increases speed & performance when sending commands to the database

[techgnome]

That is only ONE aspect/purpose of pooling; due to request-response (web) environment where opening and closing connections will take too much time otherwise.

Also, a single username in a client-server environment (just to justify use of pooling) means deployment of credentials with app; not my cup of tea.

That's the point of pooling... the connection isn't actually closed on the server... it remains open, allowing further connections to be established from the client faster.

And pooling doesn't limit you to a single username... you can use it even with specific users... it just means your pooling happens at the user level, rather than the application level.

And while pooling isn't panacea, it isn't a death sentence either. And whther you know it or not, you're using it if you're using SQL Server.

-tg

[leinad31]

That's the point of pooling... the connection isn't actually closed on the server... it remains open, allowing further connections to be established from the client faster.

And pooling doesn't limit you to a single username... you can use it even with specific users... it just means your pooling happens at the user level, rather than the application level.

And while pooling isn't panacea, it isn't a death sentence either. And whther you know it or not, you're using it if you're using SQL Server.

-tg

For thread starter's reference, points to consider in redesign http://www.sql-server-performance.co..._myths_p1.aspx.

Given a client-server setup, so instead if 500 dedicated connections you will "pool" into 500 user level pools with min 1 connection each pool to retain unique credential per client... since 1 application level pool is not applicable... since this is not a web (mid-tier to db connection) setup. Errr, benefit is minimal and should not be expected to save the application.

And you really should be asking why you need to spawn so many connections in a client-server setup in the first place (in an MVC or web app it is given) in order to address connection related performance rather than relying straight on pooling to save you. New problems will only surface, such as listener being overwhelmed.

IMO the discussion got sidetracked because of pooling... that would be akin to treating symptom instead of root cause. Front end really has to be redesigned. Again because of client-server nature of thread starter's application, pooling should not be considered as cure-all.

EDIT: Just so we're on the same page, in light of 10 concurrent users connection pooling just for the sake of maintaining a connection that isn't idle (reopened when used but will often be just 1 or two connections) will hardly differ from a dedicated connection (idle time retained) hence will hardly improve a client-server application's performance.