20.1. Should You Switch to SQL Server?
As you learned in Section 18.1.1, there's no absolute rule that decides who can use Access's built-in sharing feature successfully and who can't. You find extremesa Fortune 500 company with thousands of workers probably can't use Access sharing, while a five-person interior design team won't face any problemsbut for many people, many factors need to be considered . Successful sharing depends on how many people need to make changes at once, how extensive their changes tend to be, how long they keep their records in edit mode, and how much data you're storing (for example, ginormous memo and attachment fields are much harder to manage in a multiuser database than plain text and number fields).
Some factors are beyond the control of Accesslike a slow or unreliable company networkthat can derail database sharing. Section 18.1.1 has some good guidelines that describe when sharing may fall short. However, if you're setting up an Access database for a small business, you may need to test out database sharing yourself.
If you go ahead with database sharing, some typical symptoms will alert you if it's not working out. Here are some key danger signs:
Your edits often overlap with someone else's . In this situation, Access keeps asking you what to do about the conflict (Section 18.3.2). This problem is one of the most common, and while it won't crash your database, conflicting edits can lead to legitimate changes getting blown out by someone else's work.
You can't edit the records you want . This problem occurs when Access uses locks (Section 18.3.3) to prevent simultaneous changes. Locks let Access dodge the overlapping edit problem but at a price: Every other process that uses the record grinds to a halt. Again, this occurrence isn't dangerousjust a triple-Aspirin aggravation.
Data gets corrupted . This occurrence is both the least common and the most serious. But even though it's rare (and it gets rarer with each new release of Access), it can still happen. You'll know you have a problem when garbled data appears in a field, or Access gives you a bizarre error message (like "too many indexes") when you try to open a database object. Section 18.4 explains the problem and gives some safety tips.
Note: Data corruption can happen through no fault of Access. For example, if Zoe loses her network connection while she's in the middle of saving a change, Access can leave the shared database in an inconsistent state. The only way that you can prevent problems like these is to have a powerful database program running on the server that does all the work. (That's the idea behind SQL Server. When you use SQL Server, no one changes the database directly. Instead, people make polite requests to the always-running SQL Server engine, which then does the work in a safe and controlled manner.)
So what can you do if you find that database sharing just doesn't work for you? One of the best options is to use a high- powered server-side database product, like SQL Server. Using SQL Server obviously adds more complexity (meaning you'll spend more time setting it up and making sure it's running properly), but it gives you ironclad support for sharing data safely and efficiently .
20.1.1. How SQL Server Works
Before you prance into the land of SQL Server, you need to know a little bit more about how it works. Figure 20-1 shows how SQL Server and Access interact. In this example, there are several people using the SQL Server database at once, each with their own copy of Access to help them out.
Note: This is actually how any server-side database works, including competing database products like Oracle and DB2. However, other server-side databases don't have the nifty Access integration that you'll learn about in this chapter, so clients need to use another front-end (usually, a custom-built application).
| || |
Figure 20-1. SQL Server hosts the database with its tables (and, optionally , some queries). Access runs the front end, which contains all other types of objects ( reports , forms, macros, and code modules).
This figure just may look familiarafter all, this is more or less the same way that database sharing works. Each person gets a copy of the front end with the forms and reports, and the back end (which actually stores the data) is placed on another computer (the server) and made available to all.
However, there's also a significant difference here. In SQL Server Land, the individual front ends do less work. Instead of modifying the database on their own, they contact SQL Server (which is really just a Windows program that runs in the background on the server computer). Essentially, you've given Access a demotion. It's now responsible for eye candy , macros, and printouts, but it doesn't do the heavy lifting (like inserting, updating, and deleting records) anymore.
20.1.2. A Cheaper SQL Server
At this point, you're probably wondering how much that part in the center of Figure 20-1the SQL Server database engineactually costs. Microsoft sets the price using a complex licensing scheme that ratchets up the cost depending on how many people use the database at once. Usually, it runs into thousands of dollars, and it's not uncommon for a big business to shell out $20,000 or more annually.
| UP TO SPEED |
The Top Reasons to Switch to SQL Server
You have a lot of reasons to like SQL Server. But when diehard Access fans switch over, they usually have one of the following reasons in mind:
Lots and lots of people . As you've learned, Access doesn't do so well if you need to share one database file with a few hundred people.
Lots and lots of data . Access doesn't let you make a database bigger than 2 GB (gigabytes). The full version of SQL Server lets you create databases that swallow entire hard drives whole.
Performance . As your database grows, you'll probably find that it doesn't retrieve your data quite as speedily as it used to. Although indexes can help to a certain extent (see Section 6.2.2), SQL Server is able to do much more. It keeps recently used information in a vast pool of memory, and doles that information out to whomever needs it. This technique alone saves oodles of time.
Real security . As you learned in Section 18.5, Access doesn't offer a fine-grained security model that lets you lock specific people out of specific database objects. (It used to, but Microsoft pulled that feature out of Access 2007 because it wasn't secure enough.) But SQL Server has bulletproof security that can be as fine-grained as you want.
Transactions . In complex database systems, many tasks consist of several separate database operations that happen one after the other. For example, a money transfer of $500 involves two correlated actionsone account gets a $500 credit, and the other gets a $500 debit. SQL Server lets you put this sequence of steps in a transaction , which ensures that if any one of these actions fails, the whole shebang is cancelled. In other words, even if lightning strikes and your server reboots in the middle of its work, SQL Server can restore the system to the moment just before the account transfer (and you'll never wind up with $500 winking out of existence).
Although these features are great, most of them are beyond the scope of this book. To learn more, you need to track down a dedicated book about SQL Server. One good choice is Beginning SQL Server 2005 Programming by Robert Vieira (Wrox). (Don't be put off by the word "programming," as this book covers the essentials of database design, optimization, and maintenance.)
But before you skip this chapter in disgust, there's something you should know: A completely free version of SQL Server is out there waiting for you. Amazingly enough, it's almost as powerful as the one that costs thousands of dollars and requires you to pledge your first-born child to Microsoft.
This version is called SQL Server 2005 Express Edition , and you'll learn how to download and install it in the next section. If you compare it with the full version of SQL Server, you'll find it has three limitations:
It supports just one CPU (computer processor ). More CPUs make for more powerful computers, and this limit prevents SQL Server Express from being quite as powerful as its big non-Express brother.
It can use only 1 GB of RAM memory . If your server has more, use it for something else.
Each database you create tops out at 4 GB . No problem hereAccess itself tops out at 2 GB database files. If you avoid storing pictures and other large content in the database, you'll be good for a while.
What's more noteworthy is all the stuff that isn't left out. SQL Server Express is a full-fledged version of SQL Server, with exactly the same high-powered engine under the hood. If you need to upsize your Access database, it's a great deal.
Note: SQL Server Express also leaves out fancy tools to help you create tables and manage your databases. Although you can download a free management tool from Microsoft (see Figure 20-2), Access has already got you covered. It's a capable front end that can help you do everything you need to do.
| FREQUENTLY ASKED QUESTIONS |
Can You Trust Microsoft?
Why would Microsoft give anything away for free?
Savvy computer users are suspicious of anything that seems too good to be true. They're worried that Microsoft's SQL Server deal may be a crafty bait-and-switch tacticin other words, just enough to entice you to use SQL Server but not enough to meet your needs.
Fortunately, you have no reason to worry. If you decide to use SQL Server Express, you can stick with it happily for years without ever finding a reason to upgrade to the retail version.
So why does Microsoft offer a product they can't make any money on? It's simplethey're after the big fish. They know that a small company may start out with SQL Server Express and then grow into a large enterprise that's happy to pay the extra money to get a version that's even more powerful. This is particularly true if that company is using SQL Server to power a so-called Web application (an online shopping site, for example). If that Web site becomes the next eBay, the company running it will need some serious database horsepower (like a server computer that has multiple CPUs and a ton of memory). To support this hardware, they'll need the full version of SQL Server.
Finally, offering a free version of SQL Server helps Microsoft by enticing more people to design fancy database systems using SQL Server. A techie may come to know and love SQL Server Express, and then recommend the full version to a big-pocketed company.