Upsizing to SQL Server

Topic

New Reply

Hi,

I have already done a key word search on this forum and haven’t found what I’ve been looking for. I was hoping some of you might be able to give me some general information:

1. At what point should someone upsize to SQL Server?

2. Does the performance increases noticeably with SQL Server as opposed to a networked Access 97/2K database?

3. If Yes to question 2, why is that exactly?

4. If No to question 2, what’s the point of upsizing (other reasons)?

5. Any other concerns or comments?

TIA

Dave

Reply | Quote

Replies

Dave,

I don’t think there are any right answers to your question. Every situation is different. To get you started, here is the MS opinion:

When to upsize a Microsoft Access database to Microsoft SQL Server

There are two obvious reasons that could prompt you to upsize, such as:

Reaching DB size limits (1 GB in Access 97 / 2 GB in Access 2000)
Desire for a feature that is not present in Access

I think most other reasons would be subjective to the project you are upsizing.

To look at your other questions:
2. It is not an absolute that performance will increase, in many situations, yes, and noticeably, but it is very possible to have a decrease; again, it really depends on the project, and the layout of your db.

3/4. The general reasons listed in the MS document are the reasons to upsize, but it all falls back to what specific needs and features you require. If you were to outline your database here, and how it generally operates, it might be possible to give further guidance to you.

5. SQL Server is a pretty big beast. The learning curve is high, and the maintenance is far greater than an Access Db. It is not always possible to just install SQL Server, upsize your db, and then run it. Settings need to be tweaked, and code/procedures/queries may need to be changed. Upsizing to SQL Server can therefore be a major undertaking, not to mention a big financial one. Again, if you outlined your needs, the forum may be able to guide you further.

Reply | Quote

Just to elaborate on James’ point 5, if you continue to run Access queries against a SQL Server back end, you may not see any performance benefit at all. In fact, the queries may time out. If you create stored procedures and views in SQL Server, you’ll get the benefit of server-side performance, but you’ll have to learn to write T-SQL and you’ll have to modify your queries to suit SQL Server. Furthermore, the way you call those queries from your application can undo the benefits of the SQL Server backend. Issues are different depending on whether you are using an Access 2000 ADP or an MDB with linked SQL Server tables. There is a significant learning curve in upsizing to SQL Server, not only with respect to SQL Server itself, but also in the differences in the way Access behaves.

Reply | Quote

Thanks James and Charlotte. Your information has been very helpful.

Dave

Reply | Quote

Mind if I butt-in on this thread? I have a situation right now that I am trying to work through and it matches this thread to a T.

For several months I have been working for an agricultural specialty-crop-sorting company who put in place a MS Access 2000 database to track product as it flowed through their plant. It’s up nearly 24/7 and it uses a wireless connection on several forklifts using ASP to update the location of products in the database in real-time, and a MS Access frontend that is linked to the database for reporting and real-time status updates. The whole setup really seems to push the envelop of what Access is capable of.

My part of the project is the MS Access front-end reporting and status modules. Everything has worked great for quite awhile and this client is very pleased with the work I have done. But last week they suffered their first database crash and realized that the regular daily backups were not effective (I had no idea that they were not shutting down the system to perform their backups). They lost three hours of production data and had to rebuild it from paper trails, etc – took them two days to do this. I don’t think they want to bring the system down once a day to perform a backup, although I believe that they are doing just that since the crash.

Their tech coordinator now wants to implement SQL Server, which I originally thought was an excellent idea (I pushed the idea months ago), but I have no experience with the product. I’ve tried to get up to speed and I think I understand the issues, but I’m not exactly sure how I should go about migrating my existing Access front-end to link with SQLServer.

I see two options:

1) Continue using .MDB front end
2) Change to ADP front end

I have experimented with both and I like (2) best because it’s use of OLE DB makes it run faster. Using (1) seems to slow the beast down even more than it was to begin with. Another problem however, is that I am rarely onsite for this client, and performing mods to stored procs, if I am not mistaken, would almost certainly require work to be performed onsite, whereas with MDB files, I can simply send an update via email and discuss issues via phone. I have a day job and cannot be on location daily – luckily the tech coordinator works around the clock and never sleeps .

Anyways, my main concern right now is that my existing Access applications make heavy use of queries both as Access objects and in VBA. There are a lot of situations in which I have included VBA functions directly in the queries (i.e. Format, iif, isnull, and my own creations). In one particular instance, I have the following line of code to assign row data to a listbox:

Forms(“frmDumpLoadHistory”).Controls(“listDumpHistory”).RowSource = “SELECT * FROM qryDumpHistory WHERE [CurrentBatch] = ” & bmPrimary1BatchNum

This effectively filters qryDumpHistory by the CurrentBatch field where CurrentBatch = the current batch number for the primary batch process.

I expect that in an ADP alternative, I would have to create a parameterized stored proc and pass the batch number as a parameter to the stored proc. But I don’t know how to pass parameters to a stored proc within VBA code. Can anyone help me out here? I’m quite certain I can figure out workarounds for the imbeded VBA functions in my queries but passing parameters is my shortcoming.

This client wants to move to SQLServer in as little time as possible, and I’m quite certain I will have to tell them it won’t happen as quickly as they hope for. Any ideas on how to manage this change?

Are there any other major issues that I haven’t mentioned that I should be aware of in my predicament?

Thanks, I’ve always found good information here at Woody’s.

R

Reply | Quote

Just to piggyback on Wendell’s comments, if they want SQL Server, insist that they be willing to pay a qualified SQL Server person to help you get it up and running. While the documentation makes it look pretty easy to upsize to SQL Server, the end result is a less than optimal SQL Server database. Plus, converting your action queries and parameter queries to stored procedures that actually work will take far too long if you try to do it yourself. Using triggers and constraints to enforce referential integrity has its own learning curve as well. Do NOT expect to be able to pick it up in a hurry just because you know Access.

As an aside, ADPs can be much faster than MDBs but the downside is that they also make it easier for the users to screw up your SQL Server database, since all the tables and “queries” are actually in SQL Server and you have a live and potentially dangerous connection if you haven’t properly implemented SQL Server roles and security.

Reply | Quote

Let me offer our experience and opinions.

One of our clients runx a fairly large SQL Server (2000) back-end to a host of Access front-ends, and in nearly all cases we use an mdb file rather than an ADP. For one thing, with an ADP you can only connect to one SQL Server database at a time, and we occasionally connect to two or more replicated SQL Server databases on different servers. We use ODBC data sources to do that, and find our performance is generally quite good. We routinely do fetches from several million-plus record tables, and pull up and populate a very complex form in less than a second. On the other hand if you want the absolutely best performance, then you should go to an ADP structure to eliminate the ODBC layer. I should add that recent versions of the MDAC have not worked all that well with SQL Server 2000, but MDAC 2.5 was rock solid with SQL Server 7. The database was developed with this structure beginning in 1994, and we have had one corruption issue in 8 years, brought on by some tinkering with an NT server that never should have been allowed.

The primary advantage of SQL Server is that it uses a log to record transactions, and as long as that gets backed up frequently, you can restore with minimal loss of data if the unthinkable happens. But that requires a good deal of administrative setup, and constant monitoring. We run the SQL database on a dual Pentium 800 server with 2GB of RAM and a RAID 5 hardware data bank. This lets SQL cache most of the database in RAM, which really makes it fly. I should also add that the network is pretty robust (100Mb with dedicated server paths), and we deploy the front-end to the user workstation to maximize performance and solve the problem of front-end corruption.

The not so good news: It will take a while if/when you upgrade. The MDB front-end will be much quicker, as all you really have to do is upsize the tables to SQL. Note however that the upsizing wizard is pretty picky and a little bit flakey, so you may have to do some of your own upsizing. That’s especially true if you have names that don’t jibe with SQL Server requirements. I would insist on several weeks to allow testing and make sure the server is stable, especially if you are going to run SQL Server. (I presume you aren’t considering using the MSDE – it dies in a hurry if you get more than 2 or 3 users.) In the interim, you might consider increasing the frequency of your backup to hourly or something like that. If the client has lots of disk space, you can simply copy the database every hour (to another PC if possible), and then back one or more of those off to tape, CDR or whatever each day. Not as good as SQL, but better than no backup because someone left the file open overnight, or other such things.

Hope all this rambling makes some sense.

Reply | Quote

Thanks for your suggestions Wendell and Charlotte, I made some decisions on my own, but I took your ideas into consideration. I’ve decided to go with MDB/ODBC. I found that the specific queries that caused this architecture to slow down involved a lot of Access-extensions in the SQL and/or heavy reliance on calculated fields. If I understand correctly, in situations like this both Jet and the ODBC provider will process pieces of the query and this causes a drastic reduction in performance. If I target those queries as the first to be enhanced with stored procs, my front-end should perform better overall from the migration to SQL Server. I also have a few tables with static data in the client MDB’s as well as some functions that create temporary client-locallized tables, which would not have worked as-is in an ADP setup.

I steered clear of the upsize wizard from the beginning because I didn’t think it was necessary. Since all that really needed to be converted was the Access data MDB, I used the Data Transformation Import utility in SQL Server. I haven’t revisted the database since then, except to write some stored procs, so that is surely to be where a qualified SQL Server DBA can work his or her magic.

A question about performing backups for Access MDB files: These files cannot be properly backed up unless they can be accessed in exclusive mode. In this situation, the database is in use 24 hours a day, and shutting down the system is a costly proposition (makes you wonder why this isn’t running on SQL Server already, huh?) My plan for the interim was to hook up another MDB with code that would simply create a new uniquely-named MDB and export the contents of all database tables to the new MDB, and this could run on an hourly basis. Any foreseeable problems with that?

Reply | Quote

There’s a lot of tuning to be done on a SQL Server database regardless of how you create it. BTW, temp tables work in SQL Server as well and in fact are a good method of setting up data for reporting instead of complex layered queries in Jet.

As for your question about backups, what is the point of hooking up another MDB to create your “backup”? Is that to handle the hourly timing or what?

Reply | Quote

A point I missed yesterday – one thing to avoid if at all possible is doing joins between SQL Server tables and MDB tables. Performance really goes in the toilet if you do that. On the other hand, small static lookup tables used with combo boxes and such seem to work fine either place.

As to your question about backup, what you propose should work, but will be on the slow side. Why do you have to have exclusive access to copy the mdb file? We routinely make copies of mdb files for testing or backup purposes while one or more other users have them open, and haven’t encountered problems. Most backup software will not backup mdb files that are open, but the copy/paste process does it with no complaints.

Reply | Quote

Wendell,

Did you have ever use Total Visual Agent from FMS ?
If so, what are your feelings ?

Reply | Quote

No, we have not, but it might be just the ticket for this situation. Since we typically use SQL Server as our back-end, we don’t have the same set of concerns. On the other hand, using SQL Server has a significant cost associated with it – you need a dedicated server, and you need licenses for anyone who uses the database. The FMS product would be less expensive by far, and wouldn’t require substantial design work to implement. We do use other FMS products and find them to be reliable.

Reply | Quote