Installing SQL Server 2005 Express Edition

If you intend to build Access project files (.adp file name extension) that link directly to a database defined in SQL Server, you should install SQL Server 2005 Express Edition on your desktop computer to facilitate building and testing your application. SQL Server 2005 Express Edition is a special version of SQL Server 2005 configured to run on any operating system supported by the 2007 Office release-Windows Vista, Windows XP with Service Pack 2, or Windows Server 2003 with Service Pack 1. With SQL Server 2005 Express Edition installed, you can use an Access project file to create databases and define tables, views, diagrams, and stored procedures that you can later move to SQL Server on a network. You normally install SQL Server 2005 Express Edition on your desktop computer. However, you can also install SQL Server 2005 Express Edition on a server. If you need to install SQL Server 2005 Express Edition on a server, you must install from that server’s console.

SQL Server 2005 Express Edition has its own setup program-you can’t install SQL Server 2005 Express Edition using the setup program in the 2007 Office release. You can download SQL Server 2005 Express Edition from Microsoft’s Web site at this location: http://msdn.microsoft.com/vstudio/express/defaulLaspx. Be sure to download the .NET Framework 2.0 if you haven’t installed it yet. Also download both SQL Server 2005 Express Edition and SQL Server Management Studio Express.

After you download the SQL Server 2005 Express Edition setup file to your computer and run the program, it shows you the license agreement that you must accept in order to unpack the installation files to your hard drive. (Note, if you see the User Account Control dialog box and you’re logged on as a nonadministrative user, specify the user name and password for an administrative account, and click Continue. If you’re logged on as an administrator, click Continue to see the license agreement.) After you accept the license agreement by selecting the “I Accept The Licensing Terms And Conditions check box and clicking Next, the program displays a dialog box listing the prerequisite components that will be installed, as shown in Figure A-9. Click Install to begin the installation process.

Figure A-9: Click Install to install the prerequisite components needed for SQL Server 2005 Express Edition.

After the prerequisite components are installed, click Next to continue. The setup program then performs a scan of your current computer configuration. After the scan is complete, the setup program displays the Microsoft SQL Server 2005 Setup Wizard, as shown in Figure A-10. Click Next to continue.

Figure A-10: Click Next to begin the installation process for SQL Server 2005 Express Edition.

The wizard displays the System Configuration Check page, as shown in Figure A-11. The wizard checks to see that all necessary components and system requirements are met before proceeding. If any problems exist, such as not having the .NET Framework 2.0 installed, the wizard displays an error message in the Status column. You’ll need to review what components or system requirements need to be met in order to install SQL Server 2005 Express Edition. If your computer passes all the requirement tests, click Next to proceed.

Figure A-11: If your computer meets all the prerequisites for installation, you can click Next to proceed.

The wizard next displays the Registration Information page, as shown in Figure A-12. Enter your name in the Name text box (this is a required field) and your company or organization name in the Company text box. If you clear the Hide Advanced Configuration Options check box (selected by default), you see a number of additional pages along with the Authentication Mode and Error And Usage Report Settings pages shown later in this section. Click the Help button in the lower-left corner of the wizard for more information on each page. Click Next to proceed.

Figure A-12: Enter your name and company on this page of the setup wizard.

The next page of the setup wizard, Feature Selection, displays the components you can install with SQL Server 2005 Express Edition, as shown in Figure A-13. The wizard shows you all the available options for SQL Server 2005 Express Edition in a hierarchical view. By default, the wizard does not select Client Components, but you should install those options. Click the plus sign next to any category to expand it and see the options in subcategories. When you see a category that interests you, click the arrow next to the disk drive icon to choose options for all items in that category and its subcategories. We selected the Will Be Installed On Local Hard Drive option for all components. The installation path for the program is listed near the bottom of this page. If you want to change this location, click the Browse button, and browse to a different location to install SQL Server 2005 Express Edition. Click the Disk Cost button to display each volume drive on your computer, the amount of total disk space for each drive, the amount of free disk space available on each drive, the amount of required space needed to install the components, and the difference in size remaining. Click Next to proceed.

Figure A-13: Select the components you want to install on the Feature Selection page.

The wizard next displays the Authentication Mode page, as shown in Figure A-14. We strongly recommend that you use Windows Authentication Mode. When you do this, only Windows-authenticated users who have been granted permission on your server can gain access to it. (The members of the Administrators group on your computer automatically have permission.) If you use SQL Server security, anyone who discovers the password to the sa user ID can gain access to your server. If you choose to use Mixed Mode (Windows Authentication And SQL Server Authentication), the password for the sa user account should be at least six characters and can be as long as 128 characters. A password should also contain some combination of letters, numbers, and symbols such as # or) to make it more difficult to discover. Click Next to continue.

Figure A-14: Select an authentication mode to use with SQL Server 2005 Express Edition.

The wizard next displays the Configuration Options page, as shown in Figure A-15. On this page, you can create separate instances for users who do not have administrator permissions. Select Enable User Instances (selected by default) to allow separate instances or clear this check box to disable the functionality. Select Add User To The SQL Server Administrator Role (cleared by default) to add the user installing SQL Server 2005 Express Edition to the administrator role. If you’re installing SQL Server 2005 Express Edition on a Windows Vista computer, we recommend selecting this option because the installation wizard does not add the user running the installation to the administrator role. Click Next to proceed.

Figure A-15: Select user and administrator options on the Configuration Options page.

The wizard next displays the Error And Usage Report Settings page, as shown in Figure A-16. If you select the first check box (cleared by default), SQL Server 2005 Express Edition automatically sends error reports to either Microsoft or an internal corporate IT department. If you select the second check box (also cleared by default), SQL Server 2005 Express Edition automatically sends usage reports to Microsoft on what features you are using in the program. Microsoft uses this data to make improvements in future releases. Click Next to proceed.

Figure A-16: These two options allow you to report errors and usage data to either Microsoft or your company’s IT department.

The last page of the setup wizard, Ready To Install, displays the components that will be installed on your computer, as shown in Figure A-17. If you want to change any of your installation options, click the Back button to return to previous setup wizard pages. If you want to cancel the installation process, click the Cancel button. Click the Install button to proceed with the installation.

Figure A-17: Click Install to complete the installation process.

The wizard displays a dialog box that shows you the progress and time remaining for the installation. On a fast computer, the installation should complete in two to three minutes. You can click Cancel in this dialog box at any time to cancel the installation. On the Setup Progress page, shown in Figure A-18, the Status column should display Setup Finished next to each component that the setup wizard installed. Click Next to continue when the installation completes.

Figure A-18: The setup wizard displays a status message next to each installed component.

Figure A-19 shows the last page of the setup wizard, Completing Microsoft SQL Server 2005 Setup. Click the Summary Log link to open a Summary file in Notepad detailing the success or failure of installing each component. If you encountered errors during the installation process, you can use this log file to diagnose where the error occurred. Click the Surface Area Configuration Tool link to install a utility that helps configure the surface area of your SQL Server installation. You can use this utility to start or stop unused components after the installation. By managing the surface area of your SQL Server 2005 Express Edition installation, you can improve the security on your system by reducing potential entry points for attacks. Note, you’ll need to download and install SQL Server 2005 Service Pack 2, to use this utility. The text box at the bottom of this page displays additional information and links concerning SQL Server 2005. Click Finish to close the setup wizard.

Figure A-19: You can review any errors that might have occurred during the installation on the last page of the setup wizard.

After the installation completes, you can start the SQL Server Configuration Manager by clicking the SQL Server Configuration Manager option on the Configuration Tools submenu of the Microsoft SQL Server 2005 program group. (If you don’t find the program on your Microsoft SQL Server 2005 menu, you can also start it from C:\Windows\System32\SQLServerManager.msc.) In the Configuration Manager, select SQL Server 2005 Services, and be sure the SQL Server (SQLEXPRESS) service is marked as Running. If it is not running, right-click the service name, and click Start on the shortcut menu. The Configuration Manager presents options, as shown in Figure A-20. You can close the SQL Server Configuration Manager program window, but the service continues to run.

Figure A-20: The SQL Server Configuration Manager displays the status of your SQL Server services.

To be able to manage your databases, we recommend that you also install the SQL Server Management Studio Express program. This program provides a graphical interface to create, modify, and delete databases, tables, views, stored procedures, functions, security settings, and more.