Installing SQL Server

While some of you will use established SQL Server installations, far more of you will install SQL Server on your local development machine or (better yet) on a dedicated system configured to host the SQL Server service. I often resurrect an old clunker to host SQL Server. Because the system might not have a lot of performance, I often get a more realistic idea of how a query performs under load. I also access SQL Server the same way I would in production (over a network), and my connection parameters and security configuration are virtually identical to the way the production server is accessed. It also means that if I barbeque the server, it won't make the DBA (and the rest of the team) mad.

IMHO

Your DBA won't mind nearly as much if you melt down your personal SQL Serverinstead of the production system.

This section walks you through the process of installing SQL Serverin this case, the Express edition, as its setup is very similar to the Workplace, Standard, and other "paid-for" versions. It's actually a bit more complicated, as, in order to get all of the "free" functionality, you have to run setup at least three timesonce for the SQL Server Express, once for the Advanced Features options, and again for the SQL Server Express Edition toolkit (which contains Reporting Services and SQL Server Management Studio Express).

Unlike MSDE, which does not support any form of a GUI Setup utility, SQL Server permits both command-line setup and an interactive GUI version that will be familiar to developers who use the Workgroup or Standard (or better) editions. The Express edition is a 53.8MB download available from Microsoft's MSDN downloads areas on the web. At this same location, you'll find separate downloads for the SQL Server Express Management Studioits custom toolset, as well as the Advanced Services SKU that includes everything (SQL Server Express, the tools, Reporting Services, and full-text search). The Advanced Services edition's download is 234MBconsiderably larger and more difficult to download on demand.

Once you start the SQLEXPR.EXE (or the SQLEXPRE_ADV.EXE) setup program, your system is examined to see if you've paid your light bill and if you have the 2.0 .NET Framework installedyup, it's a prerequisite. At this point, the Microsoft SQL Native Client and setup support files are loaded (or validated). All other dependencies and system minimum configuration requirements are then scanned, a system configuration and conformity check is made, and any missing pieces are installed. This is all done invisibly behind the scenes in the "quiet" install. Quiet installs are launched from a batch or command-line prompt using syntax similar to that shown in Figure 2.2 (no, this is not necessarily the name of your setup executable).

Figure 2.2. Launching the setup program using the "quiet" option.

You'll also want to revisit the SQL Server downloads page and pull down the latest Books Online for SQL Server Express (and other editions).

Early in the setup process, the SQL Server Express GUI Setup program exposes a dialog (shown in Figure 2.3) that asks if the user wants to hide the "advanced configuration" options. (Don't confuse these with the "Advanced Services" SKU.) These advanced services refer to alternative security configurations. By default, Setup configures the SQL Server Express instance being installed to use Integrated Security and disable all access to the TCP ports and external protocols. This means you won't be able to access the SQL Server Express instance from other systems or by using SQL Server credentialsunless you change the advanced configuration options. This also means you won't be able to use SQL Server Express as a network SQL "server"just as a locally accessible DBMS that only the client application can use. Note that setup problems are logged to the system event log.

Figure 2.3. Enabling advanced configuration options.

Choosing Advanced Services Options

If you're installing SQL Server Express with Advanced Services, you're confronted with another dialog. It's wise to drill into all of the grayed options to ensure that all of the options you need are checked, including Replication, Full-Text Search, and the shared tools. Notice that Reporting Services is not shown in this listit's included when you install the SQL Server 2005 Express Edition Toolkit (SP1)a separate download. No, for the life of me, I don't know why these weren't combined. Note as well that you have the option of installing selected tools, including the SDK and the new custom SQL Server Management Studio Express, as shown in Figure 2.4.

Figure 2.4. SQL Server Express with Advanced Services options selection dialog.

Setting the Instance Name

If you chose to enable Advanced Configuration options, the next option dialog you'll see prompts for the SQL Server Express instance name. Figure 2.5 illustrates this dialog. No, I don't recommend that you change this name, but yes, with a little luck, it can be changed later.

Figure 2.5. Choosing the instance namethe default is SQLExpress.

Choosing the Service Account

The next dialog prompts you to name the Service Account, which determines which credentials are used when SQL Server support services (like full-text search and Reporting Services) logs into SQL Server. I usually create a dedicated machine account (SQLE) for this purpose. It gives me the flexibility of granting or denying specific rights to these operations. Setup automatically alters the rights on this account to accommodate its needs.

Figure 2.6. Setting the Upgrade Login Account.

Setting the Authentication Mode

The next dialog you'll see (if you chose the Advanced Configuration options) determines whether the SQL Server Express instance should recognize SQL Server authentication credentials"Mixed Mode" security, as shown in Figure 2.7. This option is disabled by default, so if you want to use SQL Server user-id/password authentication, you must enable it here. If you choose to enable mixed mode, you'll also have to specify a "strong" (non-blank) SA password. Again, choosing mixed-mode authentication opens your server to further points of attack. In any case, SQL Server always accepts validated Windows accounts when connection attempts are madeassuming there are appropriate SQL Server login IDs created. I discuss this further in Chapter 9.

Figure 2.7. Setting the SQL Server Express Authentication mode.

It's a good idea to leave Mixed Mode disabled until you understand the security implications of using SQL Server security, as it forces developers to figure out ways to hide the SQL Server credentials used by their application to prevent their use by unscrupulous hackers. Even then, it's still a good idea to stick with the SSPI (Windows authentication) unless your design makes this configuration impossible.

IMHO

Where do hackers come from? A recent study said that most "hackers" (those trying to gain unauthorized access to protected data) were individuals within the corporate firewalland most (by far) were on the company payroll.

While the SQL Server Express Setup utility permits you to set the type of security used by the SQL Express instance, you can also alter this or other settings by using the new SQL Server 2005 Surface Area Configuration Tool. As I discuss in more depth later, the default security mode is "Windows Authentication", which validates user credentials against the Domain's Active Directory database.

Setting Collating Sequences

Unless you live outside the U.S., you won't have to configure the collating sequences, but if you do, now's your chance. These settings determine how SQL Server sorts and locates values when making string comparisons. It also determines whether or not SQL Server's T-SQL syntax is case-sensitive. I heartily recommend that you not change the default (non-case-sensitive) setting unless you have a letter from your mom and the entire team understands the implications.

Enabling User Instances

The User Instance feature that I briefly mentioned earlier is enabled by default. If, for some reason, you don't want to permit applications to create user instances of SQL Server Express (and only SQL Server Express can create these unique user-specific instances), you can disable it with the next dialog. Frankly, this is a big step, so don't enable User Instances until you're sure that it meets your setup configuration and security requirements.

Finishing SQL Server Express Setup

Several more (intuitive) dialogs prompt you for feedback and other less-important information that I'm sure you can figure out. After that, the setup program takes off on its own and won't ask any more questions until it's done. That alone is a great improvement over older versions that would stop well into the setup process and ask to proceed so they could finish.

Once your SQL Server Express instance is installed, the SQL Server Surface Area Configuration Tool (shown in Figure 2.8) or SQL Server Configuration Manager (MMC snap-in) can enable the TCP ports or appropriate network protocols. These tools are very intuitive, so I won't bore you with a house-by-house tour. If you want to make your SQL Server Express edition visible to the network (you probably should think about this before doing so), you'll also have to start the "SQL Browser" service to provide server name resolution and enable the needed protocols.

Figure 2.8. Using the SQL Server 2005 Surface Area Configuration Tool.

IMHO

Remember, the "Slammer" worm exploited the fact that most SQL Servers are exposed on UDP port 1434. This means SQL Server Express won't be prey to this type of attack unless you enable the SQL Browser service.

Yes, there's also the (fairly crude) SQL Server Configuration Manager, which is designed to perform many of the same operations as the Surface Area tool and a few more (like inspect and configure specific TCP ports). This tool is an easy way to discover which TCP/UDP ports have been assigned to each SQL Server instance installed. As you can see from Figure 2.9, it can be used to start, stop, pause, and restart/resume any instance of a SQL Server or the support services.

Figure 2.9. Using the Computer Manager MMC snap-in to manage installed services.

Once Setup is completed, the setup files (which can contain plaintext or weakly encrypted credentials and other sensitive configuration informationbasically, the keys to your server) should be deleted or secured.