Section 20.2. Getting Started: SQL Server 2005 Express

20.2. Getting Started: SQL Server 2005 Express

Before you can start using SQL Server Express, you need to install it. This process is fairly straightforward, but it's long and it involves downloading some very big filesone that's 22 MB (megabytes) and another that's 54 MB. If you're still using a dial-up connection, it could take you a few mind-numbing hours.

Note: Everything you learn in this chapter about Access and SQL Server Express also applies to the full version of SQL Server. However, if you have that version, you obviously don't need to follow the down-load steps described hereinstead, you can pop in your setup DVD and get going right away.

20.2.1. Installing SQL Server Express

You install SQL Server on the computer where you plan to place the shared database. Typically, this is a computer on the network that no one uses for anything else. (If someone's using the computer, there's the risk that they'll shut it down, restart it, tie it up with other work, or do something else that will affect everyone's ability to get the information they need.) You don't need to install SQL Server on the computers that run the Access front end, although obviously they all need to have a copy of Access.

However, if you're still in the process of designing and fine-tuning your database, you may decide to try it out on your own computer first. In that case, you install SQL Server right on your own PC. Then, when you're ready to start sharing, you install SQL Server on the server computer and move your database there (as described in Chapter 21). In fact, if you're new to SQL Server it's probably best if you try it out on your computer first . That's because you need to tweak a few finicky settings in order to let other people access SQL Server from other computers. You'll probably prefer to see how everything works before you start messing around with those details.

The system requirements for SQL Server are pretty modest. (Shockingly enough, they're less stringent than the requirements for running Access.) You can find the exact specifications at www.microsoft.com/sql/editions/express/sysreqs.mspx. You'll find that any relatively modern computer can run SQL Server, but you may need to make sure your operating system has the latest updates and service packs . For example, Windows XP computers need Service Pack 2.

Tip: To make sure a computer has the latest updates, click the Start button, and then choose Windows Update.

Once you've decided where to install SQL Server and you've verified that the computer can handle it, here's what you need to do:

Open your favorite Web browser and go to the .NET download site .

Before you can install SQL Server, you need to install a component that SQL Server uses: the .NET Framework 2.0. You can find it by heading to http://msdn.microsoft.com/netframework and searching around. Or, better, use the secret shortcut URL http://tinyurl.com/drj86.

Note: If you have Windows Vista, you already have the .NET Framework 2.0. And even if you don't have Windows Vista, another application may have already installed it on your computer. If you suspect it's there, head to the Control Panel, go to the Administrative Tools section, and look for an icon named Microsoft .NET Framework 2.0 Configuration. If you find it, congratulationsyou can skip straight to step 4.
Once you find the .NET Framework 2.0, download and install it .

The setup file is pretty big: about 22 MB.
Once you finish downloading the setup file, run it, and click through all the steps in the setup wizard .

Don't worryyou don't have any decisions to make.
When the setup is finished, head to www.microsoft.com/sql/editions/express .

This page has plenty of information about SQL Server Express. Hunt around for a link that lets you download SQL Server Express, and then click it. (It was last spotted with the text "Get SQL Server Express.")

Click away to find the SQL Server Express download (Figure 20-2) .

Along the way, Microsoft will ask if you want to register with Microsoft. If you register, you'll get news from Microsoft (which is handy if you want to know about the latest SQL Server update). However, it's purely optional.

Figure 20-2. You have options. Not only can you download SQL Server 2005 Express (the circled link), you can also get a slick management tool called SQL Server Management Studio (underneath), some samples, and the information-packed help file reference that's known as the SQL Server 2005 Books Online (at the bottom).

When you find the download link for SQL Server (Figure 20-2), click to download and install it .

The SQL Server download is a whopping 54 MB.
After you finish downloading the setup file, run it .

Before the setup starts, you need to go through a few simple steps. Here's what to expect:
- Before installing anything, the setup performs a system configuration check . The setup program examines your computer, and then reports to you whether it's SQL-Server-worthy (Figure 20-3).
  
  Figure 20-3. This computer can run SQL Server, but a warning message appearsits aging Pentium III processor is a bit underpowered if you expect optimum performance.
- You're asked to fill in your name . This part is pretty typical, but keep the "Hide advanced configuration options" checkbox turned on to avoid the low-level settings you don't want to change.
- You're asked to pick the features you want to install . Keep the standard optionsthey install everything you need.
- You're asked if you want to automatically send error messages to Microsoft . No, don't expect them to help you out. This feature is designed solely to help Microsoft improve future versions of SQL Server by determining what problems are currently afflicting its users.
And the end of all this, you get to one tricky part. SQL Server will ask you what type of security you want to use.
Choose your authentication mode (Figure 20-4) .

You have two options:
- Windows Authentication Mode means that SQL Server decides whether someone's allowed to use a database based on that person's Windows user account. This approach is the best and most secure. Unfortunately, it also means you need to do a bit of extra work later on to make sure other people are allowed to use your database (Section 20.2.2).
- Mixed Mode means that SQL Server lets people use the database if they have the right Windows account (as described above) or if they can supply a user name and password combination that you've defined. If you choose Mixed mode, you need to supply a password for a special SQL Server account known as sa (system administrator). Anyone who logs in with this user name and password is given complete control of every database on the server. Mixed mode saves you the hassle of doing any extra configuration, but it's not as secure. (For one thing, you need to pass this informationthe user name and password you want people to usearound the office.)
Note: If you aren't familiar with concepts like Windows users and groups, and you don't have a network administrator to help you out, you'd better choose the Mixed mode option. It's not a very secure approach, but it's the only easy way that you'll be able to let other people log in to the database (Section 20.2.2).

Figure 20-4. Windows Authentication Mode gives you the best security. However, it also means you need to do a bit of configuration to tell SQL Server who it can trust.
After this point, the installation is long but straightforward. Now's a good time to grab a cup of coffee .

Once the setup is finished, you should run Windows Update again (click the Start button, and then choose Windows Update). That's because SQL Server Express has two service packs (at the time of this writing), and the Windows Update feature installs them automatically.

Note: The SQL Server Express service packs are critical for Windows Vista users. Without them, the super-strict Windows Vista security model will stop you from doing just about anything with your SQL Server databases.

Once you've finished this last round of updating, you're finally ready to get to work. If you decided to install SQL Server on your computer, you can start using it right away. Jump ahead to Section 20.3.3 to return to the welcoming arms of Access.

If you decided to install SQL Server on another computer, you still have more setup steps to finish. The next section has the details.

20.2.2. Putting SQL Server on the Network

When you first install SQL Server, it's usable only from the current computer that is, other computers can't log in to the server and use any of its databases.

At first glance, this appears to defeat the whole purpose of using SQL Server. (On second glance, it does too.) However, Microsoft knows that if they release a product that's wide open to the rest of the world, with its hackers, crackers, and all-around computer bad guys, someone somewhere will shout at them. For that reason, SQL Server takes the safest approachit limits itself to the current computer until you give it the go-ahead to accept outside calls.

To open up SQL Server to the outside world, you need to change two configuration settings. If you're using Windows XP or Vista, you also need to configure the Windows firewall so it lets SQL Server get through. You can find the latest setup instructions in a Knowledge Base article at http://support.microsoft.com/kb/914277.

Once you've made these changes, other people will finally be able to contact SQL Server and try to log in. However, the party's not on yet. SQL Server may still refuse them. That's because it won't let anyone in that it doesn't trust.

So who does SQL Server Express trust? Here's the lowdown:

When you first install SQL Server, it's set up to trust anyone who's an administrator of the computer where SQL Server is installed. (Technically, being an administrator means your Windows user account is a member of the Administrators group .)
If you configured SQL Server to use Mixed mode authentication (Section 20.2.1), it also lets in anyone who supplies the user name sa and the password you specified during the install.

If you want SQL Server to trust more people, you have some extra work to do. Usually, you make sure that everyone who needs to use SQL Server belongs to a single Windows group (a group is a collection of users and has a descriptive name, like Guests, Administrators, DatabaseLovers, and so on). This job is a Windows setup task, so consult your network administrator to get it done. Once you've done that, you need to tell SQL Server to trust your group. You can use several approaches to take this step, but the easiest is to download the free SQL Server Management Studio tool (shown in Figure 20-2). For more information, check out the SQL Server Management Studio Help (which is fairly technical), or look for a dedicated book about SQL Server administration.

Note: By this point, you're probably wondering why SQL Server makes life so difficult. The reason is because SQL Server is designed to be extremely flexible. Its security model seems ridiculously complex when you're just trying to let people use your database, but it's indispensable if you need to control exactly what different people are allowed to do.