Results 1 to 7 of 7
  1. #1
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    'Upsizing' companion DBs to MSDE/SQL Server (2000)

    Our accounting system vendor has modified its back end from a simple Access table sitting on a file server to an MSDE/SQL Server database. The vendor will supply all the tools required to move the data over. So far, so good. However, we have a half-dozen other MDBs that pull business-critical customized reports from our accounting system using linked tables and Firm-specific data. I have read a few MS articles on the upsizing wizard and migrating databases, but I don't know how to deal with these companion databases; where do they fit in?

    (We also have made certain queries available to end-users by copying the key .MDB to our intranet server for queries. Now that there won't be an .MDB for that, I will have to migrate the Intranet.mdb as well as rewrite most or all of the web pages.)

    Can anyone give me a sense of how to get a handle on the effort involved here? We haven't really budgeted for the conversion, and need to get a sense of how hairy it might be because we can't go more than 30 days without most of these reports. Thanks in advance.

    (As a footnote, I am assuming that I need to avoid making any changes to the vendor's DB so we can still get support!)

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

    Re: 'Upsizing' companion DBs to MSDE/SQL Server (2000)

    Ouch! On the other hand, if I were your accounting system vendor, I would have done that upgrade long ago. We worked on an accounting system that had Access based tables (lots of folks still use it) - and there were all sorts of complications that were much more easily solved in SQL Server, but I digress . . . . .

    Your simplest solution would be to attach to the SQL/MSDE tables using ODBC. However, if its an MSDE, performance will go in the toilet the minute more than 2 users try to access data. Also, in order to make sure you don't accidentally modify accounting data, make sure your ODBC source uses some other login than 'sa' - as it always has full priviledges. If you are running on an NT/Win2k network and loggin into an MS domain, the integrated security feature works pretty well. Otherwise you will probably want to define a new SQL login that has read-only permissions on the tables you want to work with.

    As to the queries, you may need to modify them to deal with any subtle differences in table structures, but once you attach to the ODBC linked tables, they should pretty much work as is. On the other hand, unless you were using ASP to begin with, you will need to do a complete rewrite of you internet pages. If you were already using ASP, you would have a similar change to all of the pages - perhaps an hour of so per page. If not, it would be big. I typically spend several (5 to 10) hours per page (I'm not an ASP expert by a long stretch, but I can stumble through it).

    A final question that will need to be answered - are they planning to work with all versions of SQL Server, and are they including a license to SQL as a part of their product, or do you need to buy that separately? The MSDE that comes with Access2k is essentially SQL 7, while AccessXP (has a different name too) is a version of SQL Server 2000. In summary, I think you'll find the upsizing to be a pretty managable process, but the web pages get scary.
    Wendell

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: 'Upsizing' companion DBs to MSDE/SQL Server (2000)

    Thanks, Wendell. I do have a question about this part:

    >Your simplest solution would be to attach to the SQL/MSDE tables using ODBC.
    >However, if its an MSDE, performance will go in the toilet the minute more than 2 users try to access data.

    We are planning to start off on MSDE, as there are seldom more than 3-4 people banging on the system all day, and only about 7 who use it directly throughout the course of a month. I really can't justify the cost of SQL Server right now with this level of usage (it would be about $3000 with 15 per-seat CALs, not including "software assurance").

    (In some SQL Server 2000 documentation, it describes the performance limit on MSDE this way: "a concurrent workload governor that limits its scalability; performance degrades when more than five Transact-SQL batches are executed concurrently.")

    I did read somewhere that joins between Jet tables and MSDE/SQL Server tables is slow (I must ask, though, relative to what; it's pretty slow now). Is this only going to be a problem for the person pulling reports, or will people using the all-MSDE/SQL Server solution also get bogged down? If the latter, is it feasible to "clone" the database each night on the same server to an archival copy that we can run reports against? This might be fresh enough for most uses, would avoid me having to dedicate another server, and would keep the report users out of the "live" database. Hmmm...

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

    Re: 'Upsizing' companion DBs to MSDE/SQL Server (2000)

    Sorry for the delay in responding - an opera, a new grandson and several other things conspired to swamp me.

    We've tried the ODBC approach with MSDE a couple of times under less than optimal circumstances - i.e the MSDE running on a Win98 PC, and it really got sluggish when a third person connected. I think that's because each person ends up with two connections to SQL Server when you are running ODBC. Not sure why, but at least through SQL 7.0 we saw that behavior. I think the SQL Server 2000 docs probably refer to the version that is shipped with XP - from what I recall, the MSDE was sort of a hybrid between SQL7 and SQL2000 when shipped with Access 2000.

    Joins between the Jet tables and Access tables is generally quite slow, though they do work, when compared with all tables in Jet or all in SQL Server. It is pretty simple to take copies of the tables you want to work with and import the data into Jet. My guess is if users can stand working with day-old data, that would be a good choice. Hope it goes well.
    Wendell

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

    Re: 'Upsizing' companion DBs to MSDE/SQL Server (2000)

    The MSDE that shipped with Office 2000 was SQL Server 7.0 . A Developer's edition of SQL Server 7.0 was included in the Service Release of the the Developer's Tools as well. SQL Server 2000 came with OXP.
    Charlotte

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: 'Upsizing' companion DBs to MSDE/SQL Server (2000)

    Thanks for the response and congratulations on the grandson. Don't worry about the delay. Some things in life are more important than Access. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: 'Upsizing' companion DBs to MSDE/SQL Server (2000)

    <hr>Some things in life are more important than Access.<hr>
    <img src=/S/shocked.gif border=0 alt=shocked width=15 height=15> Why, Jefferson, how can you *say* that??? <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    Charlotte

Posting Permissions

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