 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Windows Server Forum / Small Business Server / SBS 2000 / February 2004Access Database to SQL
All, I have a client where we have a SBS 2000 server with a second w2k server in Terminal Service Application mode. It is on this second server where we have our MS Access database applications and data. Our in-house and remote user access these applications/data using Terminal Services. I was wondering, since our databases are starting to get larger, and the backup procedure is NTBackup to the SBS 2000 server, would it be an improvement if I started up SQL server on the SBS 2000 box and convert my Access data files to SQL databases? I'd still use the Access forms as a front-end to the SQL server database (maybe move to VB in the future). We have roughly 12 users at a time accessing the data from the same Access application. This is a very low transaction environment. Each user might add 5 - 10 transactions a day. Not much. So, would it be a improvement using SQL for the data or would I just be opening up a can of worms? How are other folks running large database applications? I'm interested in improving the stability of our Access data and hoping to also improve the backup procedures. Thank you, Thomas J. Kroljic Ok. You say your two concerns are Stability and Backup, although you haven't mentioned what your concerns are with Stability. In my experience MS Access, with the Jet Database are extremely stable if correctly handled. ( Primary to this is having a Split Front-End Application / Back-End Data structure, and the use of the appropriate Dataset Types and Transactions in data processing.), But this is a question you should post to the MS Access Newsgroups where you'll find many a Guru and MVP ( sometimes embodied in the same being ) who will be more than willing to enlighten you. Migrating the Application to Client / Server, Access / SQL Server or MSDE will do -nothing- to alleviate any Backup problems you have. (...and by the way, what specifically are they ??? Are you doing regular Compact and Repairs on the jet DB ??? and or on the Access Front-end - necessary if you're using temp tables ... ) Your DB size is not going to magically shrink because you port it to SQL Server. ...and you do realise of course that it will require re-coding of the Access Front-end (?). From your description, unless your Jet DB is approaching the 500Mb+ Size, I'd leave it. Jet, properly indexed, and accessed is faster than SQL Server in many respects anyway. Just make sure you schedule a compact of the Jet DB regularly prior to your backup. If you're having problems backing up SBS 2000 then post the exact problems you have, and we'll try to help. If it's about MS Access / SQL Server, Porting, Performance, Coding or stability, then that's more appropriately a matter for the MS Access Newsgroups, and the place where you'll get the best help and opinions.  SignatureHenry Craven. SBS-MVP ============ Post It Appropriately: ========= SBS 4/4.5 : microsoft.public.backoffice.smallbiz SBS 2000 : microsoft.public.backoffice.smallbiz2000 SBS 2003 : microsoft.public.windows.server.sbs News Server : news.microsoft.com ===================================== > All, > I have a client where we have a SBS 2000 server with a second w2k server [quoted text clipped - 18 lines] > Thank you, > Thomas J. Kroljic Henry, Thank you for responding to my posting. I will try to answer some of your questions and give you more insight into what I am thinking. <<what your concerns are with Stability?>> I am no expert when it comes to SQL Server, but I was thinking that the SQL Server database would be easier to maintain and would be less prone to corruption. Over the last year, the Access database (Jet Database) hasn't caused me any problems. I occasionally go in an manually compact the database. I just thought SQL would be even better. The main application that everyone uses is still setup as a single DB. It is not split. <<Backup problems you have. > (...and by the way, what specifically are they ??? >> Right know I am using NTbackup to back up the Access databases from the Terminal Server to the SBS 2000 box. The SBS 2000 server gets backed up nightly using Veritas Backup software <<unless your Jet DB is approaching the 500Mb+> Size, I'd leave it.>> Currently, the main database is about 125Mb. Henry, I guess aside from getting advice on whether or not to convert my Access Databases (data) into SQL Server tables, and I agree, I should post a question like this on an Access forum, I guess what I really wanted to know is if people using SBS 2000 are using SQL server for data in an environment like mine. Or are folks with SBS 2000 not using SQL Server. Thomas J. Kroljic > Ok. You say your two concerns are Stability and Backup, although you > haven't mentioned what your concerns are with Stability. [quoted text clipped - 71 lines] > > Thank you, > > Thomas J. Kroljic > SQL Server database would be easier to maintain and would be less prone to corruption. As SQL Server 2000 has borrowed much of the Maintenance interface fro MS Access in the first place it's pretty much horses for courses as far as maintenance goes. To the extent that you'll be using SQL server means that it is one more server you have to Learn, Run and Maintain. Jet is certainly more prone to corruption, but 90% of the time that is due to the construction and coding ( 10% due to problems with packet corruption over the hardware, NICs, Wiring, Switch, RFI, Disk Drive and I/O). SQL server by it's very nature enforces a stricter regimen. Apply the same to Jet and the corruption issue goes away. I have some serious multi-user databases running out there on jet ( we're talking 500mb+ and > 20 users in heavy multi-user environments ) and I see a corruption perhaps once in 2 years, and that usually when a PC or the LAN goes down 1/2 way through a dataentry. > The main application that everyone uses is still setup as a single DB. It is not split. That is definitely -not- the way to write a Multi-user application, and the likely cause of many of your problems. Splitting the App, using MSDN or SQL Server as the DB will force you into a better code base. Apply the same coding practices to Jet, and you get similar stability. > Right know I am using NTbackup to back up the Access databases from the Terminal Server > to the SBS 2000 box. The SBS 2000 server gets backed up nightly using Veritas Backup software So is this a problem ? You could simplify it by splitting the Access App and storing the Jet Db on the Server. ( Access has the DB Splitter wizard that will do the split for you. ) You then only need to backup the Server if it's the only data on the TS. > I guess what I really wanted to know is if people using SBS 2000 > are using SQL server for data in an environment like mine. > Or are folks with SBS 2000 not using SQL Server. Speaking for us, and those I see and hear of around the place it's a mix. even may of the VB Apps are Jet Backended, and you'd be surprised at how many commercial apps use a jet backend even if they have re-named .mdb the extension to hide the fact. So, it depends on what you know, where you want to go with your learning/development, and how much time and money you and your client/employer are willing to put into this. SignatureHenry Craven. SBS-MVP ============ Post It Appropriately: ========= SBS 4/4.5 : microsoft.public.backoffice.smallbiz SBS 2000 : microsoft.public.backoffice.smallbiz2000 SBS 2003 : microsoft.public.windows.server.sbs News Server : news.microsoft.com ===================================== > Henry, > Thank you for responding to my posting. I will try to answer some of [quoted text clipped - 108 lines] > > > Thank you, > > > Thomas J. Kroljic Henry, Thanks again for your detailed reply. I do appreciate it. <<one more server you have to Learn, Run and Maintain>> I agree with your statement. It would be another server to learn. I was and still am interested in learning SQL Server at some point. <<and I see a corruption perhaps once in 2 years>> I can't complain. The Access database has not crashed in over two years. Every two weeks or so I go in and manual compact the database. The only problem that crops up once in a while is a VBA error code, which is my fault, not the database. So I agree with you that the Jet database is pretty damn stable. <<not the way to write a Multi-user application>> Not sure why we didn't split this database. It's been running great for the last two years. I guess my thinking is, if it's working, don't screw with it. But I agree with you, it should be split. <<You could simplify it by splitting the Access App and storing the Jet Db on the Server.>> I did not think of this. Your suggestion is excellent. I will definitely talk this over with the client. Since this is my first SBS 2000 environment, I didn't have the experience to feel comfortable in splitting and moving the database from the Terminal Server to the SBS 2000 server. As it turns out, my client just ordered another server to hang off the SBS server. Plus he ordered three more disk drives (18gb) for the SBS 2000 (it was running low on disk space). I believe the client is planning on using the new server just for his corporate version of Quickbooks. Again, thank you for your detailed reply. I think forums like this one and the folks who participate in it our great. Thomas J. Kroljic > > SQL Server database would be easier to maintain and would be less > prone to corruption. [quoted text clipped - 198 lines] > > > > Thank you, > > > > Thomas J. Kroljic Just an additional note/curiosity. Are your users remote users ? Curious as to why you have 12 users accessing the App via TS, particularly if they are local uses. There is no Saving in licensing, so local users on the LAN should have the app front-end on the Local PC talking to the Data Backend on the server. SignatureHenry Craven. SBS-MVP ============ Post It Appropriately: ========= SBS 4/4.5 : microsoft.public.backoffice.smallbiz SBS 2000 : microsoft.public.backoffice.smallbiz2000 SBS 2003 : microsoft.public.windows.server.sbs News Server : news.microsoft.com ===================================== > Henry, > Thank you for responding to my posting. I will try to answer some of [quoted text clipped - 32 lines] > > Thomas J. Kroljic Henry, You are perceptive! We have about 6 remote stores with 1 or 2 PC's setup to remotely access the Terminal Server via VPN (dsl connection). Then there are about 6 administrative users working in the same office as the equipment. They are currently using a Terminal Server session to access the Access application and their Quickbooks data. Your suggestion about about placing the frontend on the local PC which access the backend database on the server makes sense. I think we did it this way to eliminate having to install Access on each PC. We do have the appropriate number of licenses. Do you feel there a improvement by putting the frontend on the local drive (PC) compared to having the in-house users start a terminal session to access the application on the server? Thanks, Thomas J. Kroljic > Just an additional note/curiosity. > Are your users remote users ? [quoted text clipped - 61 lines] > > > > Thomas J. Kroljic Unless the Terminal Server is Extremely well resourced, you should see a definite performance boost in running Local front-ends. The Local users get the benefit of their power desktops, while the remote users get more TS Resources to share. SignatureHenry Craven. SBS-MVP > Henry, > You are perceptive! We have about 6 remote stores with 1 or 2 PC's setup [quoted text clipped - 14 lines] > Thanks, > Thomas J. Kroljic Henry, Thanks again for your input. I will definitely review our setup. Whatever is going to make my life easier and give the endusers a better experience/performance is the way to go. Thanks. Thomas J. Kroljic > Unless the Terminal Server is Extremely well resourced, you should see a > definite performance boost in running Local front-ends. The Local users [quoted text clipped - 30 lines] > > Thanks, > > Thomas J. Kroljic Henry, As a follow up, by splitting the database and moving the front-end to the local PC, wouldn't we see a significat increase in the amount of data communicating on the network? Wouldn't all queries tried to a datasheet form type send the entire data table from the server to the local PC for resolution? If this is true, wouldn't I have to evaluate this into the equation when deciding to move the front-end to the local PC and off the Terminal Server? Thanks, Thomas J. Kroljic > Unless the Terminal Server is Extremely well resourced, you should see a > definite performance boost in running Local front-ends. The Local users [quoted text clipped - 30 lines] > > Thanks, > > Thomas J. Kroljic That's a Myth about MS Access Thomas. If your Database is properly Relational with the correct use of Indexes and Query Structures, then Access will pull back "only" the indexes in the first instance, determine the DataSet, and then return Only those records. ( You can use the Table and Query Optimizer tool to help you here. ) 6 Users, even on a 10Mb LAN shouldn't have any noticable effect on network Performance, Unless of course you are pulling back multi megabites of data with each query instead of pre filtering ( e.g. The Whole 500,000 item inventory instead of pre-filtering by Type, or Name Characters first .- I'd strongly recommend you look in on the MS access Newsgroups. There is a lot to be learned even while "Lurking" - reading the archives and posts of others. ) So, the network hit you'll take is dependant on how well you've designed and programmed the Application, but as per previously mentioned example, there are a Multitude of Apps with 30+ Users hitting a 500Mb+ jet Backend without noticeable effect on the LAN. SignatureHenry Craven. SBS-MVP > Henry, > As a follow up, by splitting the database and moving the front-end to [quoted text clipped - 8 lines] > Thanks, > Thomas J. Kroljic > That's a Myth about MS Access Thomas. > If your Database is properly Relational with the correct use of > Indexes and Query Structures, then Access will pull back "only" the > indexes in the first instance, determine the DataSet, and then return > Only those records. ( You can use the Table and Query Optimizer tool > to help you here. ) Fundamentally, Jet is file-based. For the local instance of Access to "pull only" the indexes, it still has to read large chunks of MDB file over the LAN. No way around that. Want proof? Look how Access suffers over VPN links. SignatureSteve Foster [SBS MVP] --------------------------------------- MVPs do not work for Microsoft. Please reply only to the newsgroups. > Ok. You say your two concerns are Stability and Backup, although you > haven't mentioned what your concerns are with Stability. [quoted text clipped - 6 lines] > many a Guru and MVP ( sometimes embodied in the same being ) who will > be more than willing to enlighten you. Access doesn't cope with multi-user usage particularly well, especially if the database is not split into front and back parts. It does depend to a great extent on the kind of shared use it's put to, of course... > Migrating the Application to Client / Server, Access / SQL Server or > MSDE will do -nothing- to alleviate any Backup problems you have. [quoted text clipped - 4 lines] > ...and you do realise of course that it will require re-coding of the > Access Front-end (?). Not actually much work to move from split front/back MDBs to front MDB/SQL (assuming good table design practice). Of course, this doesn't leverage the power of SQL very much... > From your description, unless your Jet DB is approaching the 500Mb+ > Size, I'd leave it. Jet, properly indexed, and accessed is faster than > SQL Server in many respects anyway. Just make sure you schedule a > compact of the Jet DB regularly prior to your backup. Jet only beats SQL if it's all local. As soon as Jet is run over a network, SQL almost always wins. Plus of course, with SQL and Access combined, work-load can be shared between client and server. SignatureSteve Foster [SBS MVP] --------------------------------------- MVPs do not work for Microsoft. Please reply only to the newsgroups. Steve, I think I need to sit down and outline several scenarios and discuss them with my client. I can see benefits with each scenario. I'd like to extend a big Thanks to everyone who gave me invaluable feedback. I always learn something from forums like this, especially when I talk through problems with other professionals such as yourself. Thank you, Thomas J. Kroljic > > Ok. You say your two concerns are Stability and Backup, although you > > haven't mentioned what your concerns are with Stability. [quoted text clipped - 39 lines] > --------------------------------------- > MVPs do not work for Microsoft. Please reply only to the newsgroups. Thomas, I have pretty much the same situation as you do, and am migrating the Access app to SQL Server. Backups are easier, stability is greater, speed appears to be faster for no apparent reason other than harware (maybe), and security is enhanced. One of the biggest benefits my client has seen is that the users are no longer corrupting the Access BE (Since it is gone) 3 times a day like they were. It was so bad they had to reboot their server multiple times, which had 15 people not working for 15 minutes each time, and they had to copy a new FE each time as well. Ugh. Porting Access over to SQL Server is not a simple task, unless the tables are perfectly designed and upsize correctly. I know what I am doing and it took me almost 60 hours to get it done on a fairly simple table structure. Please post back if you have other questions. SignatureKevin Hill President 3NF Consulting www.3nf-inc.com/NewsGroups.htm > All, > I have a client where we have a SBS 2000 server with a second w2k server [quoted text clipped - 18 lines] > Thank you, > Thomas J. Kroljic Kevin, Thanks for replying. At this point in time, I'm still researching the idea of moving my Access databases to SQL Server on the SBS 2000. I'm real curious as to the impact on the SBS 2000 Server. Like I stated before, we have roughly 12 users at a time accessing the Access Application on the Terminal Server. <<users are no longer corrupting the Access BE (Since it is gone) 3 times a day>> I've been extremely fortunate, I haven't had any problems with corrupted database over the last two years. My headache is when my VBA code craps out and leaves the user hanging... So, what kind of impact did the SQL Server have on your SBS 2000 box? Was it easy to install the SQL Server on an existing SBS 2000 box? Does it slow any of the services that are provided to the enduser? Thanks, Thomas J. Kroljic > Thomas, > [quoted text clipped - 50 lines] > > Thank you, > > Thomas J. Kroljic I installed all of the apps at the same time (except ISA) as I moved up from a pIII 800 server to a 2.4 Xeon server with RAID 5...the client was not using Exchange or SQL Server before I showed up. They are about 12 users local and 3 remote through a TS box on win2k...all have their own FE hitting the SQL backend with some local tables still in the FE. I am moving this to an Access ADP for even more speed. No corruptions since the migrate to SQL, and the server is rarely over 10-15% CPU usage. From what you are saying, it sounds like your app is working. Aside from splitting it, I say leave it alone. :-) SignatureKevin Hill President 3NF Consulting www.3nf-inc.com/NewsGroups.htm > Kevin, > Thanks for replying. At this point in time, I'm still researching the [quoted text clipped - 83 lines] > > > Thank you, > > > Thomas J. Kroljic Kevin, Thanks for your response and suggestions. I guess if things are running smoothly, why screw around with it! I'll probably leave it as it is. Thanks, Thomas J. Kroljic > I installed all of the apps at the same time (except ISA) as I moved up from > a pIII 800 server to a 2.4 Xeon server with RAID 5...the client was not [quoted text clipped - 117 lines] > > > > Thank you, > > > > Thomas J. Kroljic > Kevin, > Thanks for replying. At this point in time, I'm still researching [quoted text clipped - 17 lines] > services that are provided to the > enduser? Here's a hint on my opinion on SQL on SBS... I got into SBS as a cheap way to buy SQL Server. SignatureSteve Foster [SBS MVP] --------------------------------------- MVPs do not work for Microsoft. Please reply only to the newsgroups. Hi Thomas, I agree with everything that Kevin and Henry said. I'd split the database, plase the front-end on each workstation and use group policy to push out front-end updates. If you are not an Access guru, I'd have someone look at the tables to make sure they are in at least third normal form. Properly designed tables is the key to performance and stability in Access. I've seen many databases that were designed in 2nd normal form that was constantly getting corrupted. SQL migration isn't simple and I'm assuming once you split the data out, your size of data will be around 200mb. That really isn't worth the head-ache of migration to SQL. Once you reach 25 simultaneous users, more transactions per day, and 500mg of data, then I'd think hard about SQL. So, in summary: perform a database split, make sure the tables are designed in at least 3rd normal form, and get educated about SQL--but don't move to SQL. That was my 2 cents worth. If you want me to take a quick glance at tables, I'd be willing. Let me know and I'll take it out of the newsgroup. Good luck. Marcia > All, > I have a client where we have a SBS 2000 server with a second w2k server [quoted text clipped - 18 lines] > Thank you, > Thomas J. Kroljic > Hi Thomas, > [quoted text clipped - 20 lines] > at tables, I'd be willing. Let me know and I'll take it out of the > newsgroup. Good luck. hehe. I routinely start my database projects in SQL these days, even if they'll only ever be 10-20Mb... Mind you, I do a lot of mixed front-end work - Web, Access, Excel, etc. - which IMHO makes SQL the best choice. SignatureSteve Foster [SBS MVP] --------------------------------------- MVPs do not work for Microsoft. Please reply only to the newsgroups. I agree, but Thomas didn't start with SQL--that's the issue. Marcia > > Hi Thomas, > > [quoted text clipped - 26 lines] > Mind you, I do a lot of mixed front-end work - Web, Access, Excel, etc. > - which IMHO makes SQL the best choice. Steve, I think in the very near future, I am going to install SQL on my home server and begin to work with it on future projects. Thanks, Thomas J. Kroljic > > Hi Thomas, > > [quoted text clipped - 31 lines] > --------------------------------------- > MVPs do not work for Microsoft. Please reply only to the newsgroups. Marcia, I've been away for the last few days that is why I haven't responded soon. First off, thanks for responding. Second, I'll probably take up Kevin and Henry's suggestion on splitting the database and placing the back end on the SBS 2000 server. Overall, I feel good about the existing table design and normalization. Thank you on your offer to review the tables/design. I do appreciate. Your thoughts on when to upgrade to SQL server are well noted. Not sure if this client will ever grow to that size. Overall the Jet database has been rock solid. So, I'll keep the Jet database/engine. Thanks again for your valuable input. Thomas J. Kroljic > Hi Thomas, > [quoted text clipped - 49 lines] > > Thank you, > > Thomas J. Kroljic |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.