Trying to Design a Split Database Access 2007/SQL Server Express
Hi,
This post will be long, as I have several questions related to this project, but please feel free to only answer any piece you want, however small. Every bit of knowledge will be helpful. One of the programs at my office is in need of a database. I've been researching this for awhile, and going to different forums for advice. One forum suggested a VB.Net Front End and SQL Server Back End. I thought that was a great idea and would love to learn VB.Net, but since I have such limited experience with it, I thought trying to learn in the fairly short amount of time I have would be a challenge I could not responsibly meet.
I'm running Access 2007. I'm very comfortable with Access and have designed databases in it. I can also do a modest amount of stuff with VBA, though my skills in that area are limited. I've never built a database outside of Access, and due to my level of comfort, it is the first resource I turn to. I'm thinking that for my project database, an Access front end, and SQL server express back end would be the best way to go. If you disagree, please tell me. Here are the needs of my program: a range of 5-7 concurrent users, and a maximum of 30,000 records.
This doesn't seem like much, but another database I built had only 400 records, and slowed to a crawl with 4 concurrent users. I think part of the reason is because it was on a network share and not split. From what I've read on this forum, that's an absolute no-no, and I would split any future databases I create. I know splitting the database would improve performance across the network, and I could probably design the database better. In the past, I would do whatever got the job done without giving any thought to performance issues. I'll have to pay more attention to good design in the future.
Do you think if I created a split database and designed it better, I could get away with continuing to use JET? Would it meet the needs I've outlined above? Or do you think I need SQL server? If I need SQL Server, could I use the Express version? I hear that edition is free and very robust and would probably meet my needs. My concern is would Microsoft allow it to be used in a functioning program database, or is the free version to just test out the product? The last thing I need is to have license or copyright violation issues. Also, please don't laugh at me for this, but I don't even really know what SQL Server is. I know we're on a network server, so would I need SQL server in addition to that?
I've only ever worked with the integrated fe/be of Access and JET, so having another back end scares me. Is it really difficult to set up? If I thought I could get away with it, I'd just use JET, but I've read that it's less secure than SQL Server (and the database does need to be secure), but it would be on a secure network, so does that take care of that problem? I will have more questions in the future, but I want to be respectful of everyone's time. Thank you.
Software/Hardware used:
Access 2007
This post will be long, as I have several questions related to this project, but please feel free to only answer any piece you want, however small. Every bit of knowledge will be helpful. One of the programs at my office is in need of a database. I've been researching this for awhile, and going to different forums for advice. One forum suggested a VB.Net Front End and SQL Server Back End. I thought that was a great idea and would love to learn VB.Net, but since I have such limited experience with it, I thought trying to learn in the fairly short amount of time I have would be a challenge I could not responsibly meet.
I'm running Access 2007. I'm very comfortable with Access and have designed databases in it. I can also do a modest amount of stuff with VBA, though my skills in that area are limited. I've never built a database outside of Access, and due to my level of comfort, it is the first resource I turn to. I'm thinking that for my project database, an Access front end, and SQL server express back end would be the best way to go. If you disagree, please tell me. Here are the needs of my program: a range of 5-7 concurrent users, and a maximum of 30,000 records.
This doesn't seem like much, but another database I built had only 400 records, and slowed to a crawl with 4 concurrent users. I think part of the reason is because it was on a network share and not split. From what I've read on this forum, that's an absolute no-no, and I would split any future databases I create. I know splitting the database would improve performance across the network, and I could probably design the database better. In the past, I would do whatever got the job done without giving any thought to performance issues. I'll have to pay more attention to good design in the future.
Do you think if I created a split database and designed it better, I could get away with continuing to use JET? Would it meet the needs I've outlined above? Or do you think I need SQL server? If I need SQL Server, could I use the Express version? I hear that edition is free and very robust and would probably meet my needs. My concern is would Microsoft allow it to be used in a functioning program database, or is the free version to just test out the product? The last thing I need is to have license or copyright violation issues. Also, please don't laugh at me for this, but I don't even really know what SQL Server is. I know we're on a network server, so would I need SQL server in addition to that?
I've only ever worked with the integrated fe/be of Access and JET, so having another back end scares me. Is it really difficult to set up? If I thought I could get away with it, I'd just use JET, but I've read that it's less secure than SQL Server (and the database does need to be secure), but it would be on a secure network, so does that take care of that problem? I will have more questions in the future, but I want to be respectful of everyone's time. Thank you.
Software/Hardware used:
Access 2007
ASKED:
September 21, 2010 5:07 PM
UPDATED:
September 30, 2010 3:24 PM
Answer Wiki:
There are lots of people out there that use SQL for the backend and Access for the front end. Once you do this, as long as the access front end is designed correctly there should be no performance problems.
Data security is always important. You need to protect the data from outside threats as well as inside so you should always assume that there is a threat that you need to be protected from.
From what you have described the Express edition will work just fine, and yes it is free to use for production use.
You could probably just install the SQL Express database on the existing server that hosts the Access database (unless you have a more powerful server available).
The reason for the performance problems when Access gets more than 4 users at a time is because Access isn't really designed to be a multi-user database. The way it works it takes a lot of locks and eats up a lot of memory on the file server and transfers a lot of data back and forth between the file server and the users PC that it doesn't always need to.
To see all answers submitted to the Answer Wiki: View Answer History.
If more questions come up feel free to post new questions. That’s what the site is here for.
From the SQL Server 2008 Express Datasheet
“SQL Server Express is free for development and production for all users, including developers, ISVs, and organizations…”
But It has some limitations that you need to consider.
Take a look at this:
SQL Server 2008 express edition
Mrdenny and Carlosdl,
I can’t thank you enough for taking the time to answer. I’ve been really pulling my hair out about this for a couple of months. Right now, we’re using a proprietary database to meet our needs, and the powers that be thought we might do better with a custom designed solution. That way I could design it exactly to the specs, make free and immediate customizations, etc. I’m very good at Access, but afraid I’m in over my head.
If no database existed already, I’d be fearless because anything would be better than nothing, When there’s an actual database already, though, I’m terrified of desiging something worse so I’ve kind of paralyzed myself. I’m not sure if that makes sense. Would either of you happen to know of any good resources for learning the SQL server part of this? Do you believe it’s something that can be learned in a timely manner, or would it take years to get it to do what I want it to do?
Another thing I was thinking about was building the database fully in Access to get it started, then upgrading to SQL Server when I learned it well enough. Would you advise against this? Right now, I feel like I’m spinning my wheels because I don’t know enough and I don’t know where to start.
If you are going to start working with SQL Server take a look at the sqlpass.org website. This is a free org which has all sorts of training options, some are free (24 hours of pass which just happened) and some aren’t (the PASS Summit which is coming up in November). Also check out SSWUG which has a virtual conference coming up for a fee (less than the PASS Summit) where you can get some great info about SQL Server.
Also check out the PASS Virtual Chapters and for all sorts of free training.
Thank you for suggesting PASS. I’ve been looking through its forums, and even though every question is over my head, it’s good to know that I’ll have resources for when I have a better understanding of SQL Server.
I was thinking of picking up SQL Server Step by Step. I find the Step by Step books good for beginners, and it will give me a good foundation for understanding the more complicated stuff. My question is, I want to install SQL Server Express 2008 on my work computer so I can practice. As a general rule, do you think I’m allowed to do that on my own, or would I need IT’s permission? If I eventually get this database built will I be able to launch it on my own, or would I have to bring IT into the mix?
Thank you!
You should tell them (IT) about your plans.
Thank you Carlos for the advice. I’m kind of in a Catch-22, in that I don’t really know enough about SQL Server without practicing it. And to your point, I was kind of afraid to download it without IT knowing, but without any hands on experience, I’ll never be able to understand enough to intelligently discuss it with them. I was thinking of downloading the express version onto my home computer, and practicing until I at least understand the basics. Do you think that’s okay, or would you advise against it. Thanks again.
It is definitely a good idea to download it onto your home computer, to practice at home.
On the other hand, I don’t think you need to have experience/knowledge to be able to tell IT what you are planning to do. However, I have been wondering why you are planning to do something that should be done by IT.
Hi Carlos,
To answer your question:
While this project may seem huge to me, I know that in the general scheme of the institution I work for, it’s very small potatoes, and probably not something IT would want/have time to bother with. As limited as my skills are right now, I’m continually taking classing and trying to learn as much as I can, so I can do stuff like this and add value
. Thanks so much for all of your advice.