Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Database Replication (2k)

    Does anyone have any experience with database replication with Access 2k?
    White papers on Microsoft's site make is seem relatively painless and, dare I say it, easy. What are the caveats and challenges?
    At this point we would expect to have the original database and one other one with which to synch/replicate.
    Is it time to seriously consider SQL or will database replication meet meager needs?

    FYI - database to be replicated has about 80 tables, and 2500 records with expected addition of 500-1000 records per year.

    Any experience and recommendations is GREATLY appreciated.

    Ken

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Database Replication (2k)

    You need to supply more information on the reasons for considering replication. Given the size of the database and the number of records being added, SQL Server sounds like total overkill. However, replication requires a certain amount of maintenance. Are the machines on a network, is one a laptop, or what? Is the database split into front and back end files? I wouldn't recommend replicating a front end, which requires much more maintenance than simple back end replication.
    Charlotte

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Database Replication (2k)

    In addition to Charlotte's comments, what problem do you hope to solve with replication? There are a number of downsides to replication, not the least of which is the bloat in database size that it causes. Other issues are the difficulty in removing it if you discover you don't really need it down the road, the added complexity if you discover you need to make table design changes, and the managment of the synchronization process when you have conflicts. Just a few of the things they don't tell you in the marketing material!

    All that aside, there are situations where it really is useful. The one we've seen most frequently is where a person travels with a laptop and needs to carry a database with them. We have a client who has used that situation, as well as having one remote employee 1000 miles away, but from time to time we get called in to resolve problems. You might also take a look at the tutorial on replication on our website.

    As to SQL Server, replication works somewhat differently, and is far more complex than it's Access cousin, although you can synchronize Access and SQL Server databases. I would base any decision on moving to SQL Server on the basis of the number of simultaneous users, desired response times, and the criticality of your database. Hope this helps.
    Wendell

  4. #4
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Database Replication (2k)

    Thank you Charlotte! I agree on the "overkill", but these corporate IT folks only like to talk SQL and Oracle.

    Better defining the "system".
    We have an excellent custom built Access 2k based system for tracking incidents and occurrances. Each workstation has an mde front end linked to an mdb backend on a Windows 2000 server. We now want to get corporate folks out of the data entry mode, and more in the role of oversight and reporting. In turn allow the remote sites to enter their own data. The remote sites have access to a common server, but not to the corporate server where the existing database resides. The idea is to have a means of synchronizing the two databases. The maximum number of expected concurrent users would be 5 on the remote system with normally just one person in the system. The maximum expected concurrent users in the corporate system is 3, with one person expected in the corp system most of the time.

    There is no plan to replicate front end mde's, as they work fine as is.

    Does this help? Again, I am looking for challenges that others have experienced in using the replication feature, and an idea of what to expect in the overall scope of the project.

    Thanks VERY much Charlotte.

    Have a great day!

    Ken

  5. #5
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Replication (2k)

    I can add a little more detail on this situation (since I'm involved.... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>). Ken's client has an Access 2000 database, which works just fine and dandy on it's own. I built an .asp page, which allows them to view/edit/add data to a portion of this database, from the web. The problem lies in the old 'IT distrust' of Access. The setup required (currently) for the .asp page, is to have the database ON the web server. This allows for the tightest possible IIS security, and also allows for virtually unlimited web users. (theoretically speaking, of course).

    The IT folks don't want to give the web server access to their network. Duh. Why they don't want to give their internal users access to a share on the webserver is beyond me, in fact, I am going to be contacting their IT department in a bit, to find out. That leaves SQL Server (which gives IT that false warm fuzzy), or replication, where the db would be on both the webserver, and the network.

    Personally, I think it should stay on the webserver, and then just create a share that the users at the corporate site can see. I have a strong feeling though that they are going to want to push it into SQL Server though.

    I hope this explains things a bit more.

    Ken, I really recommend we stay away from Replication. Yes, the white papers make it sound easy. But like it has been pointed out, there are a lot of pitfalls, which Microsoft is more then happy to gloss over! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Database Replication (2k)

    Unfortunately, that is true of SQL Server as well, Drew. And unlike Access, SQL Server really requires a DBA to administer is. I assume the IT people are going to provide that?? <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20>
    Charlotte

  7. #7
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Replication (2k)

    I believe they already have a SQL Server. They just want the tables put into it. We have an Oracle database where I work, and it actually requires very little 'maintenance', per se. Our boss is supposed to be the 'expert', but my co-worker and I have figured everything out so far, that needs to get done with it.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Database Replication (2k)

    All I can say, Drew, is that the SQL Server databases I've worked with and the Oracle databases I encountered in my last contracting job were not maintained by anyone except a trained dba who knew the products and understood how to program and manipulate them. Doing it on a seat of the pants basis may work ... for awhile or for a long time. It isn't when it works that is the problem, it's when it doesn't! <img src=/S/flee.gif border=0 alt=flee width=25 height=25>
    Charlotte

  9. #9
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Database Replication (2k)

    I may not be a certified DBA, but I like to think I can hold my own! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> I know what you mean. Our Oracle db is a backend to our production database, which has a frontend written in a language called PROIV. It's called Glovia, and is an 'off the shelf' production database. Personally, I think it's a pain in the <img src=/w3timages/censored.gif alt=censored border=0>. We don't use it's reporting engine, instead, we have a HUGE Access database called Glovia Reports, which runs the various reports that the upper echelon want. I have written a handful of utilities to automate processes also. Quite frankly, the Oracle db requires no maintenance, the only issues we have ever run into are user locks, when a user forcefully closes the front end (which communicates through telnet).

    We have a SQL Server too, but have never used it, because, quite frankly, I have yet to have a project land on my lap, that I couldn't handle with Access, VB, and/or ASP. In all honesty, getting into the web based front ends, with an .mdb as the backend practically eliminates any need for a server side db. In my not so humble opinion. Any who, I've contacted Ken's client's IT guy, and hopefully will get this situation sorted out.

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Database Replication (2k)

    I have to agree with Charlotte on SQL Server. We work with it frequently, and yes, you may get by for a long time (years) without needing any maintenance. Then you get something like the Blaster worm that some clever chap devises, and the wheels fall off and you NEED a trained DBA. Besides, the IT folks are likely to decide in this case that they need to have a SQL Server replicated database for local people to work on since they won't let users look at the one on the IIS.

    When do you need SQL Server (or Oracle, DB2, or whatever)? <UL><LI>When you have a mission critical database that needs to be available 24/7.<LI>When you need recovery right up to the point of a crash (logging).<LI>When the size exceeds Access limits.<LI>When you need the engine to enforce complex data validation or relationship rules.<LI>When you want to track changes with an audit trail.<LI>When you have performance needs that Access can't handle - i.e. 100 users hammering at a database.[/list]
    Wendell

  11. #11
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Database Replication (2k)

    Wendell and Charlotte,

    Thanks for the excellent feedback. You have prompted a few questions.

    RE:Maintenance of SQL
    The client has a well trained staff of IT professionals, have SQL running for several applications. My question now is "is the maintainence issue of SQL" that you mention relating to SQL, or to the specific database? In this instance if we "upsize" the access tables to SQL what are the primary considerations? If the situation demands either an Access database replication model, or an SQL table conversion (assuming the client has inhouse SQL expertise) which is the better route?

    RE: Wendells criteria
    W>When you have a mission critical database that needs to be available 24/7.
    Mission Critical? Probably not. 24/7 absolutely

    W> When you need recovery right up to the point of a crash (logging).
    Not a huge issue, considering the limited amount of data at this point.

    W>When the size exceeds Access limits.
    Not in my lifetime.

    W>When you need the engine to enforce complex data validation or relationship rules.
    Not in the existing Access database. Security and server permissions is the primary reason client has given for requiring SQL. They got hit hard during the latest blaster and SOBIG episode and have changed their policies and permissions.

    W>When you want to track changes with an audit trail.
    Not required for this app.

    W>When you have performance needs that Access can't handle - i.e. 100 users hammering at a database.
    Not expected.


    FYI - in another life, I used to sell and help develop niche market Foxpro apps. In those days, corporate IT gurus wanted Oracle or they didn't want to play. It didn't have anything to do with whether their database needs required Oracle. They only wanted Oracle and that was that. These days, they want Oracle or SQL, of which the extent of my knowledge on either does not exceed how to spell them. Though at one time I think I knew what the SQL acronym meant. <grin>

    Obviously Oracle and SQL offer these folks something they need. My question is, do they ALWAYS _need_ these high end databases for every application or is it a matter of convenience?

    Thanks again for ideas and opinions.

    Ken

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Database Replication (2k)

    If the client has an IT staff that includes SQL Server dbas, then that base is covered. However, with SQL Server, don't expect to be able to "tweak" the structure or change things at short notice because the IT people will require time to schedule the changes and perfrom all the arcane rituals that go with getting anything done by IT.

    The answer to your NEED question is NO, they don't. However, they FEEL like they do, never mind that SQL Server had a huge security hole that was hammered by a worm. Desktop databases simply don't need SQL Server, but IT people like the sense of control they get from having the critical part of the application under their authority for security reasons. While I can understand that, it makes it tough on people who just need to get the job done NOW, not in a couple of months when a SQL programmer becomes available or the IT department can schedule creation of a new server for the back end and allocate the server space.
    Charlotte

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Database Replication (2k)

    In addition to Charlotte's comments, which are spot on, if you have to choose between replication and SQL Server, I would opt for SQL Server since they already have several other SQL apps. The care and feeding of one more database isn't a big deal in that situation. But I still think a single Access database on the IIS server is the best answer if you need to make occasional design changes at the table level.
    Wendell

  14. #14
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Database Replication (2k)

    Charlotte and Wendell,

    You are guyes are awesome. I do appreciate your comments. I was afraid that in the myst that is my ignorance of the high end databases that I was overlooking something. To me it seemed like overkill when these IT folks required Oracle or SQL. It is reassuring to hear from well respected and admired professionals like you that I was not full of it.

    It seems everyone is in agreement on this. SQL it is, unless we can convince client that Access will meet their needs. Don't hold your breath.

    Is converting the tables to SQL a no brainer? Or, are there challenges to address? For example, I seem to recall reading someplace that converting Access tables to SQL does not bring the indexes with them. Are there any limitations that apply to SQL that don't apply to Access? I mean Access seems designed for the masses and the common person, allowing a lot of nonstandard "stuff". SQL designed by professionals for professionals (who have lots of money). Fear of the unknown here I think.

    Have a great day!

  15. #15
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Database Replication (2k)

    Actually, the Upsizing Wizard does a pretty good job most of the time - but be sure you download the latest version if you are going to SQL 2000. It will tell you if a tables doesn't upsize, and will sometimes give you a reason. In general you should get the indexes. Another alternative is to use the DTC utility with SQL Server - it can read and import Access .mdb files. The only gotcha's I know about are pretty arcane. For one thing, autonumbers work a bit differently in that the number isn't assigned until you actually save the record. That can be a problem in code if you are trying to do things on BeforeInsert events and need the autonumber value. Another is that Date/Time values in SQL Server aren't quite as precise from a time perspective as Access values. But we frequently just upsize, and then use the SQL Server ODBC driver with linked tables, and that seems to work well.
    Wendell

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •