HOUR 2. SQL Server Basics

You should know some basics when getting started with SQL Server 2005 Express. Knowledge of these basics will help you to make decisions about whether the product is right for you, and will help you with the installation process. In this hour you’ll learn:

• The versions of SQL Server 2005 that are available

• How to install SQL Server Express

• How to get started with the SQL Server Management Studio Express

Versions of SQL Server 2005 Available

Microsoft recognizes that there is a plethora of database users with disparate needs. They therefore have released the following six versions of SQL Server 2005:

• SQL Server 2005 Express Edition

• SQL Server 2005 Workgroup Edition

• SQL Server 2005 Developer Edition

• SQL Server 2005 Standard Edition

• SQL Server 2005 Enterprise Edition

• SQL Server 2005 Mobile Edition

SQL Server 2005 Express Edition

SQL Server 2005 Express provides a great means of getting started with SQL Server. It offers a robust, reliable, stable environment that is free and easy to use. It provides the same protection and information management provided by the more sophisticated versions of SQL Server. Other advantages of SQL Server Express include

• Easy installation

• Lightweight management and query editing tool

• Support for Windows authentication

• “Secure by Default” settings

• Royalty-free distribution

• Rich database functionality, including triggers, stored procedures, functions, extended indexes, and Transact-SQL support

• XML Support

• Deep integration with Visual Studio 2005

SQL Server 2005 Express has a few disadvantages that make it unusable in many situations. These include

• Support for only one gigabyte of RAM

• Support for a four-gigabyte maximum database size

• Support for only one CPU

• Absence of the SQL Agent Job Scheduling Service

• Absence of the Database Tuning Advisor

SQL Server 2005 Workgroup Edition

SQL Server Workgroup Edition provides a great solution for small organizations or workgroups within larger entities. It includes a rich feature set, but is affordable and simple to work with. Other valuable features include the fact that there is no limit on database size and that it supports the SQL Agent Job Scheduling Service. The disadvantages of SQL Server 2005 Workgroup Edition include

• Support for only three gigabytes of RAM

• Support for only two CPUs

• Absence of the Database Tuning Advisor

SQL Server 2005 Developer Edition

SQL Server 2005 Developer Edition is designed specifically for developers who are building SQL Server 2005 applications. It includes all functionality of SQL Server 2005 Enterprise Edition, but with a special license that limits its use to development and testing. Its license specifies that you cannot use it for production development. For more details about the features of SQL Server 2005 Developer Edition, see the section “SQL Server 2005 Enterprise Edition” later in this section.

SQL Server 2005 Standard Edition

SQL Server Standard Edition provides an affordable option for small- and medium-sized businesses. It includes all functionality required for non-critical e-commerce, data warehousing, and line-of-business solutions. The advantages of SQL Server 2005 Standard Edition include

• RAM limited solely by operating system RAM

• No limit for database size

• Full 64-bit support

• Database mirroring

• Failover clustering

• Inclusion of the Database Tuning Advisor

• Inclusion of the full-featured Management Studio

• Inclusion of the Profiler

• Inclusion of the SQL Agent Job Scheduling Service

The disadvantages of SQL Server 2005 Standard Edition include

• Support for only four CPUs

• No support for online indexing

• No support online restore

• No support for fast recovery

SQL Server 2005 Enterprise Edition

SQL Server 2005 Enterprise Edition includes all the tools that you need to manage an enterprise database management system. It offers a complete set of enterprise management and business intelligence features, and provides the highest levels of scalability and availability of all the SQL Server 2005 editions. It supports an unlimited number of CPUs and provides all the features unavailable in the other versions of SQL Server 2005.

SQL Server 2005 Mobile Edition

SQL Server 2005 Mobile Edition enables you to easily port corporate applications over to mobile devices. SQL Server 2005 Mobile Edition offers many advantages. They include

• Inclusion of SQL Workbench (a tool that replaces SQL Server 2005 Management Studio)

• Full integration with SQL Server 2005

• Inclusion of synchronization functionality

• Excellent reliability and performance due to a revamped storage engine and an improved query processor

• Multi-user support via multi-user synchronization and row-level locking

• Full integration with Visual Studio 2005

• Increased device support

Installing SQL Server Express

Although installing SQL Server Express is a fairly simple process, you are asked a few questions during the installation process that might need some clarification. The text that follows walks you through the process of installing the SQL Server 2005 Express database engine, and provides an explanation of the various options available to you. Follow these steps:

1. When you launch the setup program, an end-user license agreement appears (see Figure 2.1). You must accept the agreement before proceeding. After clicking to accept the agreement, click Next. The Installing Prerequisites step of the installation process runs (see Figure 2.2).

Figure 2.1. You must accept the end-user license agreement before installing SQL Server 2005 Express.

Figure 2.2. The Installing Prerequisites step of the installation process verifies that all the required components were installed successfully.

2. After verifying that all the required components were installed successfully, click Next. The Microsoft SQL Server 2005 Setup welcome screen appears (see Figure 2.3).

Figure 2.3. The Microsoft SQL Server 2005 Setup welcome screen commences the actual installation process.

3. Click Next. SQL Server 2005 Setup performs a System Configuration Check. The results appear as in Figure 2.4).

Figure 2.4. It is important to note the results of the System Configuration Check.

4. Click Next to proceed to the next step of the wizard. The Registration Information step of the installation process appears (see Figure 2.5).

Figure 2.5. You must provide registration information as part of the installation process.

5. Fill in the required information and click Next. The Feature Selection step of the wizard appears (see Figure 2.6). The wizard helps you select what features are installed on your computer and how the setup process will install them.

Figure 2.6. The Feature Selection step of the wizard enables you to determine what features are installed on your computer.

6. If desired, click Disk Cost to view the space available on each drive. The Disk Cost step of the wizard appears (see Figure 2.7).

Figure 2.7. The Disk Cost dialog shows you the space available on each disk drive.

7. Click Close to close the Disk Cost dialog and then Next to proceed with the wizard. The Authentication Mode step of the wizard appears (see Figure 2.8). Here you must determine whether your server will accept only Windows logins, or whether it will also support SQL Server logins. For more information on this topic, see Hour 21, “SQL Server Authentication.”

Figure 2.8. The Authentication Mode step enables you to determine the type of authentication you want to accept.

8. After making your selection, click Next to proceed to the next step. The Error and Usage Report Settings dialog appears (see Figure 2.9). Here you determine what happens when an error occurs (where SQL Server will send error reports), and whether you want Microsoft to receive information automatically about your feature usage.

Figure 2.9. The Error and Usage Report Settings dialog enables you to determine what happens when an error occurs.

9. Click the appropriate check boxes and click Next. The Ready to Install step of the wizard appears. The dialog shows you what components the SQL Server 2005 Express Edition Setup will install (see Figure 2.10).

Figure 2.10. The Install step of the wizard shows you what components will be installed.

10. Click Install to complete the process. After a moment the Setup Progress dialog appears (see Figure 2.11).

Figure 2.11. The Setup Progress dialog shows you what is happening during the setup process.

11. Click Next when setup is finished. The Completing Microsoft SQL Server 2005 Setup step of the wizard appears (see Figure 2.12).

Figure 2.12. The Completing Microsoft SQL Server 2005 Setup step of the wizard provides a summary of what happened during the setup process.

Installing the database engine is generally not enough. You will have no way to manage the server and its objects. This is where SQL Server Management Studio Express fits in. Microsoft provides a separate installation program for SQL Server Management Studio Express. To run it, follow these steps:

1. After the installation wizard launches, a welcome screen appears (see Figure 2.13).

Figure 2.13. The SQL Server Management Studio Express installation program begins with a welcome screen.

2. Click Next. The License Agreement step of the wizard appears (see Figure 2.14).

Figure 2.14. You must complete the License Agreement step of the wizard before proceeding.

3. Click to accept the license agreement.

4. Click Next. The Registration Information dialog appears (see Figure 2.15).

Figure 2.15. The Registration Information step of the wizard enables you to enter user and company information.

5. Fill in the registration information and click Next. The Feature Selection step of the wizard appears (see Figure 2.16).

Figure 2.16. The Feature Selection step of the wizard enables you to determine what features will be installed.

6. Select the desired features and click Next.

7. The Ready to Install the Program step of the wizard appears. Click Install to complete the process.

8. When the installation process is complete, the Completing the Microsoft SQL Server Management Studio Express Setup step of the wizard appears. Click Finish.

Getting Started with the SQL Server Management Studio Express

SQL Server Management Studio Express is the tool that you use to manage your SQL Server and its objects. Using this tool, you can create and work with databases, tables, stored procedures, indexes, and much more! Hour 3, “Getting to Know the SQL Server Object Explorer,” will go into quite a bit of detail about the workings of SQL Server Management Studio Express. In fact, I will use this very powerful tool throughout the course.

Here are some basics that you should know before proceeding with the hours that follow. To get started with SQL Server Management Studio Express, follow these steps:

1. When you launch SQL Server Management Studio Express, the Connect to Server dialog appears (see Figure 2.17). Here you provide login information.

Figure 2.17. You must provide login information before using SQL Server Management Studio Express.

2. Provide the server name and the type of authentication and then click Connect. If you are successful, you are placed in SQL Server Management Studio Express (see Figure 2.18).

Figure 2.18. SQL Server Management Studio Express enables you to manage all aspects of SQL Server.

---

By the Way

When selecting a server name, you select the name of the server, along with SQLEXPRESS. For example, in Figure 2.17 8200ALISONSQLEXPRESS is selected. You then select the type of authentication. For more information about authentication, see Hour 21.

---

3. Notice that you can use the Object Explorer to expand and contract nodes, enabling you to focus on what you want to work on (see Figure 2.19).

Figure 2.19. You can expand and contract nodes within the Object Explorer.

4. If you click to select an object in the Object Explorer, summary information about that object appears in the Summary pane (see Figure 2.20). This pane differs quite a bit depending upon what you select in the object explorer.

Figure 2.20. The Summary pane provides information about the object selected in the Object Explorer.

5. When you are modifying an object, such as a table, the Properties window becomes active, enabling you to modify object properties (see Figure 2.21).

Figure 2.21. The Properties window enables you to modify object properties.

Summary

It is always important to learn the basics of a product before forging on to more advanced techniques. This hour began by describing the versions of SQL Server 2005 available so that you are better equipped to make the decision as to which version of the product is right for you. You then learned how to install SQL Server 2005 Express Edition. Finally, you got a brief overview of SQL Server Management Studio Express.

Q&A

Workshop

Quiz

Quiz Answers

Activities

Download SQL Server 2005 Express Edition. Install both the database engine and SQL Server 2005 Express Management Studio. Launch Management Studio and practice expanding and contracting the nodes of the Object Explorer. Select different nodes, and view the summary information.