It is a good idea to migrate your Microsoft Access applications to SQL Server if your database or database application:
This section explores the client/server paradigm, first with a metaphor, then by comparing the Microsoft Access database model to the SQL Server database model. Finally, backing up data with Microsoft Access and SQL Server is discussed.
Using the fictional marketer Jerry and his information requirements, the file server method of Microsoft Access is compared to the client/server paradigm of SQL Server.
Jerry works in the Marketing Department on the 3rd floor. He needs to review all of the March orders placed by the XYZ company, stored in the Accounting Department on the 7th floor.
Jerry takes the elevator to the 7th floor, where he presents his company badge to the security guard. The security guard sticks it in a pigeonhole behind the security desk.
Jerry checks out a copy of the orders index. He takes it to the elevator and goes back to the 3rd floor.
Jerry opens the alphabetical index to the letter X and finds XYZ. He sees that there are three orders, and that they are in file cabinet drawer 54, folder six; cabinet drawer 70, folder one; and cabinet drawer 95, folder seven.
Jerry takes the elevator to the 7th floor. He has already identified himself to the security guard so he walks in. He tells the security guard that he would like copies of those three files. The security guard walks with him to the Orders room and unlocks file cabinet drawers 546, 701, and 957. The security guard takes them to a photocopy machine and makes copies of all the contents of the drawers although Jerry needs only three records. Jerry takes his 60 pages of copies back to the 3rd floor.
Jerry looks through the stack of copies of the orders, selects the three that interest him, and places them on his desk. He discards the other 57 pages.
If Jerry wants to update these three orders, Jerry cannot get photocopies of the file drawers. Only the originals can be modified. In this case, the security guard unlocks the file drawers and gives Jerry the original orders. The security guard then throws a special padlock on the file drawer and posts a sticky note on the bulletin board behind the security desk.
Alternatively, the security guard could automatically unlock all of the drawers and not post the sticky notes. If Mary arrives at the 7th floor after the security guard has opened the file drawers for Jerry, the security guard won't let her past the front desk. Only after Jerry is finished can Mary get in. Jerry still must pull the index file and take it back to his 3rd floor office, then return to the 7th floor a second time to get the data.
Jerry has been in the elevator four times:
Additionally, he had to retrieve all 60 records himself.
Jerry still needs to review all of the orders placed by the XYZ company in March, stored in the Accounting Department on the 7th floor. Jerry still works in the Marketing Department on the 3rd floor.
When Jerry goes to the 7th floor the first time each day, he shows his company badge.
Jerry takes the elevator to the 7th floor and requests the brochure for the Orders database. Jerry takes the elevator back to the 3rd floor to read the brochure. If Jerry has been to the Orders department before, he already has this brochure so he skips this step.
Jerry tells the helpful staff at the service counter which orders he needs. After receiving Jerry's request, the service desk attendant rings a bell and an assistant fetches the records. The assistant opens the three file drawers and reads the entire contents of the drawer (all 20 records for each drawer) but copies only the records that Jerry needs. While the assistant is copying the first record, the service desk attendant sends another assistant to read the other two records. When the first assistant finishes reading the first file drawer, the second attendant shares the contents of the second file drawer with him, then hurries off to read the third file drawer. When all requested drawers have been read, and the order information extracted, the first assistant returns to the service desk and hands Jerry copies of the three requested records. Jerry takes the elevator back to the 3rd floor.
If Jerry had wanted merely the total of sales for March rather than the complete record, the assistant would have added them up for him. This would have saved Jerry the trip back to his office to perform the calculation.
Except for the often-skipped trip to get the brochure, Jerry has been in the elevator only twice—one round-trip:
If multiple users request information from the server, the Microsoft Access file server method must serialize each extraction of data pages. Each user must wait until the one security guard is available to unlock file drawers. In contrast, SQL Server's client/server model can take full advantage of the ability of Windows NT to use multiple processors. SQL Server can start multiple threads to manipulate the data and can schedule these threads to run on any available processor. In short, SQL Server can have many users acting as data runners for the service desk.
Why are people saying that a client/server system is better? It's not better, of course. It's different. It's only better if you need the features that client/server architecture provides. Microsoft Access is an Indexed Sequential Access Method (ISAM) database. To understand this, let's examine a primitive model: File Input Output.
Take an ASCII text file consisting of:
1,up ,5
2,down ,6
3,left ,7
4,right,8
What you can't see, but is present in the above contents, are carriage returns (ASCII character 13 abbreviated below as CR) at the end of each row. Each row has 10 characters in it (integer comma letter letter letter letter letter comma integer CR). With 10 characters in each of four rows, there are 40 characters plus an end of file character (EOF), making 41 characters in this file. Because each character takes one byte, there are 41 bytes in this file. A file input output system can read this primitive database by reading the 41 bytes into memory. If you want the first row, you can read bytes 0 through 9. If you want the third row you can read bytes 20 through 29. If you want to update the second row changing the 6 to a 9 you can write a new value to the 19th byte. This is still the basic system that Microsoft Access uses to read and update a text file.
If Microsoft Access wants to read the second row and prevent anyone else from changing that data while it is in use, Microsoft Access asks the network operating system to lock the file. If you try to read the file with Notepad while Microsoft Access is updating it, you will get an access denied error. The operating system's network feature locks the file.
Microsoft Access works similarly to the File Input Output model. There are three important differences:
If it requires four rows of a given table, it reads the description of the table from the "system" tables and then reads only the required bytes of the table. In other words, it gets the hard drive address of the table and reads a set number of bytes, then stops.
The granularity of this lock is in sets of 2048 bytes called "pages." It locks the page that contains the record it needs, thereby locking any other records on the same page.
When you create an index with Microsoft Access, the program adds a copy of the field or fields you are indexing to the index file. This added file or fields in the index file has a special structure called a B-Tree. Using this index, Microsoft Access can find the record containing the value "down" or "left" without searching the entire file. In a table of 5 or 50 megabytes, this functionality enables Microsoft Access to find the record in a fraction of a second.
The Microsoft Access .mdb file holds data in sequential order and uses an index to find records. Microsoft Access is an ISAM database. SQL Server is not.
For more information about the Microsoft Access index structure, see the Microsoft Jet Database Programmer's Guide.
The first difference between Microsoft Access and SQL Server databases concerns who is getting the data. In the case of Microsoft Access, the data resides on the hard drive in the file with the .mdb extension (for example, Northwind.mdb). If this file is on machine1 and you have Microsoft Access on machine2, Microsoft Access asks the operating system on machine1 to supply you with the contents of bytes X through Y of the Northwind.mdb file. If someone on machine3 also connects to machine1, they can ask the machine1 to give them the bytes of C through D from the same Northwind.mdb. The operating system keeps track of who has what and who wants which bytes locked. Unfortunately, the operating system isn't really very good at this.
The only programs that know what the data is supposed to look like and who can intelligently manage it are the Microsoft Access programs located on machine2 and machine3.
These two programs cannot talk to each other; in fact, they don't even know they are sharing this mdb file. The first computer to open the .mdb file creates a second file with the same name but an extension of .ldb, and places it on machine1 right next to the .mdb file. If you open Northwind.mdb you also see a file called Northwind.ldb. (In Microsoft Access 2.0, the file stayed there after it was created. In Microsoft Access for Windows 95, the file is deleted when the last person using the .mdb file exits.)
After the .ldb file is created, each copy of Microsoft Access using the .mdb file "posts a sticky note" in the .ldb file that it has checked out some specified range of bytes. This is referred to as an Extended Byte Range Lock. With Extended Byte Range Lock, ranges of data are either locked and no one else can see them, or they are not locked and anyone can see them. There is no compromise.
With SQL Server, the data is still kept in a file on machine1's hard drive, but this time only one program at a time can get to it. The SQL Server program, Sqlsrvr.exe says "I have an exclusive lock on the whole file and anybody who wants any data has to ask me." Then the SQL Server program running on the server implements its own sophisticated locking plan.
First, there are more types of locks:
As with Microsoft Access, each of these SQL Server locks can be a lock on a specific page or a group of specific pages or a lock on a whole table. The server can also lock an extent, a group of eight contiguous pages. But here too, SQL Server is smarter about managing them. SQL Server estimates how many page locks it needs and can escalate a group of page locks to an extent or table lock if it determines that would be faster. You can configure this lock escalation (LE) in SQL Server using the LE maximum and LE percent settings to meet the needs dictated by your data access patterns. If you want more speed for large updates, set SQL Server to escalate to a table lock more quickly. If you have many users, set SQL Server to escalate to a table lock more slowly.
The SQL Server program is optimized to run on Windows NT. This means it can open multiple threads, or start several processes to run simultaneously. The first process, or thread, can start reading the disk. At the same time another thread creates a table to hold the data. By the time the first thread has finished reading the data, a third thread may have started reading ahead on the hard disk just in case you need that data, too. If 50 people are all connected to the server, you can have a hundred mostly independent threads reading and processing data. Since Windows NT supports multiple processors, many of these threads can be running at the same time. Since Windows NT supports striped disk sets, where four or five disks act as one, the server can be reading data simultaneously at an incredible rate. Since this is scalable to more disks and more processors, if your server runs slowly because of increased use, you can pop in another disk or another processor and quickly upgrade your performance back to its previous level.
You should always back up your database. With Microsoft Access, you copy the .mdb file to tape or another disk. If your .mdb file is 50 MB, you copy 50 MB each time you back up. With SQL Server, you can make incremental backups. During the first backup, you store the entire 50 MB. The next day you only need to back up the transaction log, maybe 1 MB. In case of a failure, you can still restore. Your total time spent backing up is less than it would be if you backed up the entire database every day.
The Microsoft Access .mdb file should be copied while no one is using it. SQL Server, on the other hand, can back up the database while it is being used. This SQL Server feature is especially valuable for servers running applications that must be in service 24 hours a day, 7 days a week. SQL Server can be configured to back up a database dynamically when a specified file size or time of day is reached.