Setting Up SQL Server

Now that you are aware that you can use Access Projects to work with SQL Server databases, I will take a moment to cover the different versions of SQL Server and explain how to install SQL Server. You must install one of the versions of SQL Server in order to perform the examples used throughout this chapter.

SQL Server Versions

As you are probably aware by now, Microsoft SQL Server is a relational database management system for storing, analyzing, and reporting data. Currently, seven versions of Microsoft SQL Server are available with SQL Server 2005. Each is briefly described here:

• SQL Server 2005 Enterprise Edition – This is the most comprehensive version of SQL Server 2005 and supports all the SQL Server 2005 features. This version is most appropriate for large organizations that are required to manage immense amounts of data quickly and efficiently.

• SQL Server 2005 Standard Edition – This version of SQL Server 2005 supports many of the available features, but it doesn’t support the features that enable management of immense amounts of data quickly and efficiently. This version is primarily for small or medium-sized organizations that do not have the complex database requirements of a large organization. SQL Server 2005 Standard Edition is still an extremely powerful version of SQL Server and supports Analysis Services (with a few exceptions), Replication, Full-Text Search, Integration Services (Basic Transforms only), the Common Language Runtime and .NET Integration, and other advanced SQL Server features.

• SQL Server 2005 Workgroup Edition – This version of SQL Server 2005 supports basically the same features as the Standard Edition with some exceptions, most notably Notification Services and Integration Services. Additionally, Analysis Services and Full-Text Search are only available on certain operating systems with this edition. This version is most appropriate for small organizations or small business units.

• SQL Server 2005 Mobile Edition – This version of SQL Server 2005 runs on mobile devices - Windows CE. This compact version of SQL Server 2005 allows relational databases to be stored and managed on Windows CE devices for later synchronization. Plus, it gives users the capability to manage a SQL Server database remotely over the Internet from their CE devices.

• SQL Server 2005 Developer Edition – This version of SQL Server 2005 supports all the available features (just like the Enterprise Edition), except it prohibits deployment on production servers. This version is designed for developers, consultants, and solution providers to use while developing and testing their applications.

• SQL Server 2005 Evaluation Edition – This version is a fully functional version of SQL Server 2005 Enterprise Edition, but it will stop working after 120 days. It is available as a free trial to give organizations a chance to try out SQL Server 2005 Enterprise Edition before purchasing it.

• SQL Server 2005 Express Edition (formerly called “Desktop Edition”) – This version is a free and redistributable version of the SQL Server database engine. This means that you can include it in your setup programs for applications that use SQL Server to store data. This release of Express Edition is similar to prior releases in that graphical user interface tools to manipulate the database are not included, thus, other products (such as Access or SQL Server 2005 APIs) must be used to manage data stored in this version of SQL Server. Also, Microsoft has created an Express Edition of its new database management tool, SQL Server Management Studio. Finally, please note that Express Edition requires that .NET Framework Version 2.0 be installed.

Obtaining and Installing SQL Server Express Engine

If you do not have SQL Server already installed, you should obtain a copy. You have a couple of options to get it up and running. You can download or order a free trial version of SQL Server (that expires after a trial period) from Microsoft. Another option is to install SQL Server 2005 Express Edition, which is free. The setup program for the Express Edition is on the Microsoft Office CDs, as I will explain momentarily.

The Benefits of SQL Server Express Edition

You may be wondering why Microsoft would offer a free version of SQL Server. Express Edition was introduced by Microsoft to bridge the gap between Microsoft Access and Microsoft SQL Server. A large segment of existing applications use Microsoft Access as both the front end and the database platform. As those applications become increasingly popular, they start outgrowing the capabilities of Access. Many people may find themselves modifying code to port the applications to a SQL Server database. This can be a monumental task in many instances, with several data type and SQL syntax differences.

Express Edition is actually a small-scale version of SQL Server, with a 4GB limitation. If your application does not need the capability to handle large databases or many users, the Express Edition version of SQL Server is a good choice. Some exceptions to this free licensing rule can be found in the licensing agreement that accompanies Express Edition and other versions of SQL Server.

Express Edition allows companies to create demo CDs of their products much more easily. In the past, many companies had to write an Access version of their SQL Server application to include on the demo CD so that they would not violate the licensing agreement of SQL Server. Alternatively, they included links or files to download trial versions of SQL Server in order for their demo to work. Because Express Edition is a free distribution version of SQL Server, you can now easily create CDs for demonstration purposes without rewriting any code.

The biggest advantage of using Express Edition is that you don’t have to make any modifications to your code (SQL statements, table structures, and so on) when you upsize later because of an increase in database size or concurrent users. You just purchase the more powerful version of SQL Server and import the prior database to the new installation without modification. Thus, when your application becomes extremely popular and needs the power of the higher, nonfree versions of SQL Server, you will be ready.

Installing SQL Server Express Edition

You can obtain the setup program for Express Edition from the Microsoft web site. At the time of publication, the URL for download is http://msdn.microsoft.com/vstudio/express/sql/download/. Further, if service packs are available it is wise to download and install them as well.

1. Download the installer program to a location on your hard drive. The download gives you an option of saving the file to your local hard drive or simply running it. It is recommended that you save it to disk. The file is in excess of 50MB, so it may take a while depending on your Internet connection.

2. Launch the Express Edition Setup Program. Navigate to the folder on your hard drive where you saved the download. From that folder, double-click on the setup program to launch the setup program. You may be prompted to update some files on your system before setup can continue. If so, follow the prompts on the screen to update your system with the necessary files. This may require that you reboot your machine and restart the setup program again.

3. The Windows Installer or some other such program will flash for a few seconds to let you know that it is launching the setup program. After you double-click on the setup program, it should just automatically install Express Edition with all the default settings. If any prompts appear on the screen, follow the instructions to complete the setup.

4. It is also a good idea to download and install the latest service pack for whatever version of SQL Server you are running.

Now that you have successfully installed SQL Server Express Edition, take a look at what actually got installed.

Understanding What Was Installed

Unlike previous versions which installed three services, SQL Server 2005 Express Edition installs two services on your machine: SQL Server Agent and SQL Server browser.

The SQL Server Configuration Manager is installed with Express Edition to enable you to manage these two services. The SQL Server Configuration Manager window, shown in Figure 10-2, displays the settings for Server, Services, and the status of the Service

Figure 10-2

To start, stop, pause, continue, or restart the service, highlight the SQL Server service and right-click and select the desired action from the pop-up menu. Note that menu items are enabled or disabled as appropriate, based on the current status. Figure 10-3 shows the status of the SQL Server Service, with Pause, Stop, Resume, and Restart disabled.

Figure 10-3

If you select the Start menu item, the SQL Server Service starts running (see Figure 10-4).

Figure 10-4

The SQL Server Service is the core of Express Edition and all other versions of SQL Server, for that matter. It consists of the SQL Server storage engine and the query processor. The storage engine is responsible for reading and writing the data to the database, and the query processor is responsible for receiving and executing SQL statements. The SQL Server Service must be running in order for data to be retrieved, inserted, updated, or deleted from Express Edition. The default installation of Express Edition sets this service to automatically run on startup of the computer where Express Edition is installed.

The SQL Server Service, shown in Figure 10-4, is running on a server called Goz_Tablet1100 and a database instance called SQLDEV. Notice how the indicator to auto-start the service when the OS starts is checked. This means that the SQL Server Service will start anytime the operating system boots. It is a good idea to have this setting turned on for the SQL Server Service so that database inserts, updates, deletes, reads, and so on will be allowed automatically. User intervention is not required to manually start the service each time.

The other service installed with Express Edition is the SQL Server Browser Service. This service receives incoming requests for resources and provides information about the database instances installed on the local machine. These examples illustrate how easy it is to manage the two services that are installed with Express Edition by using the SQL Server Configuration Manager utility. Now that I have covered the basics of setting up Express Edition and starting and stopping the services, let’s return to the discussion of using Access Projects to work with SQL Server databases.