Installing and Configuring SQL Server 2005

The first step in preparing to use SQL Server is to properly install Microsoft SQL Server 2005. Not so coincidentally, this is also the first exam subtopic. Before installing SQL Server, you need to select of the appropriate hardware and operating system configuration. You should expect to see questions that not only pertain specifically to the Microsoft SQL Server 2005 software but also the surrounding environment.

Within the broader topic of installing SQL Server, there are really two separate issues: performing a new installation and upgrading an existing installation. In performing a new installation, you have to consider the hardware compatibility, hardware configuration, operating system, and software requirements. When upgrading an existing installation, you must also consider the compatibility issues surrounding existing data and applications.

As explained in this book's Introduction, many of the topic areas overlap between the set of SQL Serverrelated exams. This particular exam focuses on the basics of hardware layout, operating system integration, and related requirements for installation.

This chapter focuses on the installation topics covered on the 70-431 exam, but for the complete picture, particularly if you plan on taking other SQL Serverrelated exams, you will need to know more about how to install SQL Server. In Appendix A, "Suggested Readings and Resources," you can find a list of additional resources that provide more in-depth coverage.

Installation Requirements

The Microsoft SQL Server 2005 software dictates a number of requirements for installation. Appropriate hardware, operating system, and support software are needed to successfully deploy the product.

Exam Alert

On all Microsoft exams, watch for questions that provide a choice of configurations for appropriate deployment. In most instances, all but one of the answers will include something that is not possible. On this exam, you are unlikely to see any questions regarding 64-bit deployments.

There are six different editions of SQL Server, each with varying requirements:

• Express Edition

• Workgroup Edition

• Developer Edition

• Standard Edition

• Enterprise Edition

• Mobile Edition

The exam primarily focuses on the high-end Enterprise Edition, but you should review the other editions to become familiar with their requirements, features, and appropriate applications. All editions have similar hardware requirements, except, of course, for the mobile edition, which is a separate platform for use on mobile devices. You need not worry about studying the Mobile Edition for the exam as its application is out of the exam scope. (The Mobile Edition is also beyond the scope of this book and therefore is not mentioned from this point forward.)

Hardware Requirements

The Express and Workgroup Editions do not support the 64-bit platform, and the requirements for those editions relate solely to a 32-bit deployment. The requirements that follow would not represent the typical configuration for most production servers. Most production equipment exceeds not only the absolute minimum requirements but for performance capacity and availability reasons contains hardware far beyond the recommended minimums.

All editions require a minimum 600MHz Pentium III processor (1GHz or better recommended) for the 32-bit deployment. The 64-bit editions require a minimum 1GHz AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, or Intel Pentium IV with EM64T support processor. The Developer, Standard, and Enterprise Editions also support deployment on 1GHz Itanium processors.

The Express Edition sets its minimum memory levels at 192MB, with the recommended being 512MB or higher. All other editions require 512MB minimum, with 1GB or more memory recommended. The disk space needed to install SQL Server (not including user databases) is the same for all editions: 350MB minimum or 425MB if Books Online and sample databases are installed.

Operating System Requirements

The first of the software requirements for installing SQL Server involves the host operating system, which varies slightly from edition to edition. Table 1.1 shows the editions and their correlating supported platforms.

Table 1.1. Microsoft SQL Server 2005 Operating System Support

SQL Server 2005 Edition	Windows XP, SP2	Windows XP X64, SP2	Windows 2000 Professional, SP4	Windows 2000 Server, SP4	Windows Server 2003 Standard, SP1	Windows Server 2003 Enterprise, SP1	Windows Server 2003 Datacenter, SP1	Windows Server 2003 Web, SP1	Windows Small Business Server 2003, SP1	Windows Server 2003 Standard X64, SP1	Windows Server 2003 Enterprise X64, SP1	Windows Server 2003 Datacenter X64, SP1
Express	x		x	x	x	x	x	x	x
Workgroup	x		x	x	x	x	x		x
Standard 32-bit	x		x	x	x	x	x		x
Standard 64-bit		x								x	x	x
Developer 32-bit	x		x	x	x	x	x		x
Developer 64-bit		x								x	x	x
Enterprise 32-bit				x	x	x	x		x
Enterprise 64-bit										x	x	x

Exam Alert

Note that the only edition of SQL Server that Windows Server 2003 Web Edition supports is SQL Server Express. The limitations of this operating system edition and the licensing requirements prevent you from installing any other SQL Server edition.

Support Software Requirements

Besides the host operating system, a couple other software components are required before you can install SQL Server. Internet Explorer 6.0, SP1, or later is a requirement for installation. If you plan on implementing SQL Server Reporting Services, you will also need Internet Information Server 5.0 or later and ASP.NET 2.0.

Note

You can download evaluation versions of SQL Server from Microsoft's product download page, www.microsoft.com/sql/downloads/trial-software.mspx.

The network software requirements are the same for all editions. SQL Server 2005 supports the following network protocols:

• Shared memory

• Named pipes

• TCP/IP

• Virtual Interface Adapter (VIA)

SQL Server no longer supports Banyan VINES Sequenced Packet Protocol, Multiprotocol, AppleTalk, or NWLink IPX/SPX. Any existing installation that has clients connecting with these protocols must select one of the supported protocols.

Exam Alert

SQL Server no longer supports Multiprotocol installations. This is a change from previous versions of SQL Server and may appear on the exam in an attempt to catch those familiar with older versions.

Each of the protocols should be applied in different instances. The VIA protocol works specifically with VIA hardware. Named pipes is used for communications on local area networks (LANs) as an alternative to TCP/IP.

Shared memory is the simplest protocol, with no settings. Clients use shared memory to connect to an instance running on the same computer. You can use shared memory to troubleshoot when other protocols are not functioning.

TCP/IP is the most popular protocol used. It allows access to SQL Server through an associated port number that can be configured through SQL Server Configuration Manager in the SQL Server 2005 Network Configuration. By default, SQL Server uses port 1433, but you can configure it to be a different value, if desired.

The Installation Process

Installing SQL Server is pretty straightforward, but there are a few options available during the setup. It is important to go through the installation process a few times, both because of the functionality provided by having a couple installations and also to hit a couple more exam points in the installation process.

The installation process varies slightly, depending on the edition you are installing and the options you select for the editions. A significant number of additional features are available, and during the installation process, you must select whether these are available and, if so, how they are to be set up. This chapter discusses the options available in the Enterprise Edition because it is the focus of the 70-431 exam.

Besides the database engine itself, a full array of additional products ship with SQL Server:

• Analysis Services

• Integration Services

• Notification Services

• Reporting Services

• Service Broker

These products provide additional functionality to augment the data services.

Note

Microsoft has made these products optional as an additional security precaution even though they provide useful functionality. They should be implemented carefully, within the scope of network security.

Some of the more advanced exams in the certification paths deal in more depth with the attributes of the installation and configuration. For the 70-431 exam, you need to concentrate on only a few of the additional products that ship with SQL Server. For this reason, the text that follows discusses only the topics needed for this exam.

Installation Preparations

To help ensure a successful installation, you can use several tools to aid in discovering potential problems. You can use the System Configuration Checker (SCC) and SQL Server Migration Assistant to smooth the installation process in a new or upgraded installation.

Exam Alert

You are expected to know the appropriate use of both the SCC and SQL Server Migration Assistant tools for the exam. You use the SCC to pinpoint conditions that prevent installation. You use the Migration Assistant to migrate from other systems, such as Access or Oracle.

You can use the SCC to scan the computer where SQL Server will be installed. The SCC checks many parameters and then provides remedies to enable a successful installation. The SCC checks Com+, requirements for Performance Monitor, the Windows Management Instrumentation (WMI) service requirement, the Microsoft XML Core Services (MSXML) required by some SQL Server components, the minimum operating system requirements, the minimum hardware and software requirements, and whether the system has a pending reboot. The SCC also ensures that the user has sufficient privileges to perform the installation.

The Migration Assistant is available for free download from Microsoft's website. You can perform migrations from other products by using this tool. An excellent presentation of its use is available from www.microsoft.com/sql/solutions/ssm/ssmademo.mspx.

While you are preparing for the installation, you must also consider the physical layout of the hardware on the server. You are unlikely to see the minimum requirements used on a server in a production environment. Depending on the load, you want to use a multiple-processor system for your database server. One processor is fine with a low-end server, if it is 1GHz or above, but two processors are better, and four or more processors are preferred. SQL Server is designed to work best in a symmetric multiprocessor environment.

Given the price of RAM today, it doesn't make sense to skimp in this area in order to lower costs. You should put as much RAM into the machine as your hardware and budget can handle. Increasing the memory of a server is the most cost-effective change you can make to achieve better performance on an existing machine. I am reluctant to put a low end on RAM, but I suggest 1GB for starters, and don't be afraid to move up a considerable distance from there.

The disk system is also very important. For a strong server, you should use a minimum of 5 drives. A 3-drive RAID 1 array would be used to store data, and the other 2 drives would mirror each other and store the operating system and application programs. The more drives you can add to the array, the better the performance and the greater the capacity available to store data. This peaks out at about 10 drives, which is a little overboard anyway; a 5-drive array performs very well for most implementations.

Exam Alert

You are likely to run into at least one question that deals with the concept of RAID storage devices. You must understand the differences between the varieties of RAID implementations and how the operating system interacts with the physical components.

RAID (redundant array of independent/inexpensive disks) is a technology in which two or more disk drives can be configured to provide the following:

• Larger volumes Space on multiple disks is combined to form a single volume.

• Improved performance The user can interact with more than one physical disk at a time (disk striping RAID 1).

• Safeguarding of data You can provide mechanisms (mirroring or parity) for redundant data storage (RAID 5, which is not recommended for high-performance systems).

Even though you need to understand software implementations of RAID in order to pass certification exams, and even though they are found in production systems, they are not regarded as being as reliable as hardware RAID. For any high-volume, mission-critical application, you should set up data redundancy mechanisms at the hardware level. You should configure a gigabit backbone for the network around the server. It is even worth considering multiple network cards connected to the server to increase the bandwidth available to the machine.

If you are looking at the very high end, two sets of small RAID arrays of three drives, each on two separate controllers, can provide some additional performance gain and flexibility with data and index placement. It is also often recommended that you keep the log files separated from the data in order to improve performance and reduce disk contention.

Tip

Keeping the log files separate from the data can help improve performance and reduce disk contention.

When you're considering where to put the files for the database server, you need to think about the operating system files, the application program files, and the database filesboth data files and log files.

It is also worth considering separating indexes because you can realize some performance gains if the indexes are stored on a drive other than the one on which the data is stored. You do this through the use of filegroups. When the index is created on a different filegroup, each group can make use of different physical drives and their own controllers. Multiple disk heads can then read data and index information in parallel.

In an ideal configuration, you might want to separate the operating system from its page file. You would then place the log onto its one drive, separate from the data, with the data configured over a RAID 1 volume. You would then separate the seldom-used data (column or table data) from data that will be accessed more frequently. After placing the indexes on their own volume as well, for about $150,000$200,000 you would have the optimum performance in a database server.

Remember that the database management system (DBMS) relies heavily on the file system. SQL Server uses a set of files to store the data, indexes, and log information for a database. A primary file also has some header information in it, providing SQL Server with necessary information about a database. Each database has a minimum of two files associated with it: one for the data and one for the log. It is also possible to create multiple files for each of these purposes, as described in the following paragraphs. File placement and object placement within these files play an important role in the responsiveness of SQL Server. A database consists of two or more files, with each file used for only a single database. A single file cannot be shared by multiple databases.

Additional Considerations for Installation

You really cannot begin to deploy a database server into a production environment without knowing a lot about the applications that are going to use the server. The physical components of the server change dramatically for different applications. It is not enough to simply apply the software to a framework that is not specifically set up for the application that will be used.

Some of the implementation aspects that may affect the physical attributes of the server include the following:

• Partitioning of tables, indexes, and views

• Data mirroring, replication, clustering, and log shipping

• Division of application load or alteration of schema to address load issues

• Physical adjustments for slow-running applications

Changes to the environment will no doubt occur over time. It is rare for a production server to maintain the same configuration over prolonged use. You should be prepared to adjust the configuration as needed.

Postinstallation Procedures

If you have used previous versions of SQL Server, one of the first things you will notice is that the SQL Server Enterprise Manager has been renamed and revamped so that it has a lot of new functionality. The new SQL Server Management Studio is a powerful tool that allows for much more than administration and data query capabilities. You can use this new integrated environment to access, configure, manage, administer, and develop all components of SQL Server. SQL Server Management Studio includes the many features to ease database development and administration.

After you have installed SQL Server, you need to perform a number of different processes to set up connections to the data sources you will be accessing. If you have multiple instances of SQL Server installed on the same computer, or if you administer multiple machines in the same company, you will likely find it easiest to connect to and register the machines within SQL Server Management Studio.

Note

Installing two instances of SQL Server on the same computer allows you to test the most possible scenarios with a minimum of hardware and effort. Many features require two instances, and having them both on one machine will make your exam preparation easier.

From SQL Server Management Studio, you can connect to any SQL server in the enterprise. You can also connect to instances of Reporting Services, Integration Services, Analysis Services, and Mobile Edition. This is helpful in an organization where more advanced features of the environment are utilized, but it is not discussed further in this book because it is beyond the scope of the 70-431 exam.

Linked Server Configuration Options

When designing applications that use multiple data sources, you need to be able to integrate these data sources. Linked servers allow you to execute commands against data sources on remote servers. They offer a number of advantages, without requiring you to migrate data to a single server. By configuring linked servers, you can access a remote server and issue distributed queries, updates, commands, and transactions against the data sources. The data sources can be created from almost any product from Microsoft or another company. By using OLE DB and/or ODBC technologies, you can access and handle diverse data sources in a similar fashion.

After you have configured linked servers, you access them in the same manner you would if the data were located locally on the server. However, you must use four-part names when querying the data. A four-part name refers to the components needed to uniquely define the location and name of an object:

ServerName.DatabaseName.Owner.ObjectName 

As you can see in Figure 1.1, when you use a linked server, you can easily query and manipulate the data when it is properly qualified in this manner.

Figure 1.1. Using four-part names when querying a linked SQL server.

When you define a linked server, you must supply a number of criteria to use for the connection to the data. When you create a linked server object, you must define the security and optionally define any special handling of the connection and/or the format of the data.

When using linked servers, you must connect to data using the security requirements of the server hosting the data. In most cases, you must use a valid login. Additional options are not tested on the 70-431 exam, but you need to know the security-related options needed to set up logins. Several of the available options utilize the login and indicate how users are related between the systems. The security context could be any of the following:

• Current local login

• Remote login same as local

• Mapped login from list

• Singular remote login for all locals

• Impersonated login

• Restricted access to nonmapped logins

Exam Alert

The 70-431 exam topics dealing with linked servers focus on security and appropriate use of four-part names. Keep in mind that remote servers, although available as objects themselves in previous versions of SQL Server, are now configured using linked server objects.

When you create a linked server that will connect to an OLE DB data source, you supply a name for the server to use inside your applications. This becomes the server portion of the four-part name. Although this is similar to an alias, there is an important differentiation. An alias can be used in a query expression as an alternative name for a table or column. An alias can also be an alternative name for a server when you configure data connectivity for clients through the SQL Server Configuration Manager at the server and ODBC DSN (Data Source Name) on client machines.

Logins and security are of course an important part of working with links to other data sources. Connections must be made from one server to the other, and these connections must be validated to ensure appropriate access to the data. The configuration setup for the login sets up the security context in which the connection will operate. You can configure a mapping of local logins to remote logins and then set up the method for connecting any logins that are not mapped.

You can set logins to impersonate (also known as delegation) to allow SQL Server 2005 to connect to another instance of SQL Server under the context of an authenticated Windows user. With delegation, the instance of SQL Server to which a user has connected by using Windows authentication impersonates that user when communicating with another instance of SQL Server or a SQL Server provider.

Note

For user impersonation to be successful, the instance being connected to must be on the same computer, a remote computer within the same Windows domain, or a remote computer within a trusted Windows domain.

Enabling impersonation for distributed queries may involve configuration changes within Microsoft Active Directory as well as configuration of the user mapping within SQL Server. In Windows Server 2003, you can grant rights to be trusted for constrained delegation, which allows administrators to specify exactly which services can be accessed when using an impersonated user's security context. This configuration is the preferred, more secure, configuration in domains that have full Windows Server 2003 functionality. SQL Server 2005 also supports the ability to impersonate another principal by using the EXECUTE AS statement or clause on modules; this aspect of impersonation is discussed in Chapter 3, "Implementing Database Objects."

If you do not provide a local login with impersonation, you must supply the remote login and password that is to be used to connect to the linked server. As shown in Figure 1.2, the DYNSA local login will use impersonation, and the other two accounts will be mapped to logins on the remote system.

Figure 1.2. Remote server login mappings.

Note that at the bottom of the Linked Server Properties dialog box, you indicate the security context that the non-mapped logins will use. This determines whether any non-mapped logins can connect and under what security the connections are made. Of course, you have to know the login information on both machines before you configure linked servers for operation.

SQL Server Login Security

When connecting to SQL Server, you do so using an account that allows a level of access defined by the login information stored in SQL Server. Logins are the basis for the security context of any operation you perform. Logins are stored in SQL Server and can originate from SQL Server or from Windows user accounts and groups.

A login can belong to specialized SQL Server roles at the server or database level. Each role has a specific set of tasks that define what can and cannot be done on the server and within the database. Security granularity at this level is beyond the scope of this exam and is therefore not discussed in this book. You can find more information about each of the roles in SQL Server Books Online and on the Microsoft website.

New to SQL Server are credential objects, which allow users who connect to an instance of SQL Server by using SQL Server authentication to connect to Windows or other resources outside the instance of SQL Server. A credential is a record that contains the authentication information. Most credentials are made up of a Windows login and password.

Exam Alert

Don't spend a lot of time studying security for the exam. All you really need to know about the topic is how to define a role, how to use application roles, and how to create logins based on Windows groups.

To make permissions easier to administer, you should always assign them at the group or role level. Because you can add Windows groups as logins, you can leverage groupings that the network administrator has already created. You can easily allow all employees in the company access by adding the Domain Users group, but you should do this with caution in instances if that group contains external entities. You should never assign access to the Everyone group because doing so is deemed insecure and opens a hole in which data tampering can occur.

After a login ID is created, you can assign access to a resource to the login ID. Within a database, a User object exists for each login that has access to the database. The Guest user within a database should be removed in most cases. This User object gives anyone who can connect to the server access to a database, even when that person has not been explicitly granted the access. To disable guest access, you simply remove the user from the database.

Every user becomes a member of the Public role within a database. You cannot assign or remove users from this role. You can use this role to set a base level of permissions to every user in the database. You should never deny permissions to the role because in doing so, you would be denying the permissions to all users. Instead, you should simply not assign permissions at all to the role unless it is a permission you want to give to all users.

Other database roles can be created for any desired purpose. After they are created, users can be added to the roles, and permissions can be assigned to the roles accordingly. If application roles are used and activated, other users' related permissions are not in effect while the application role is active. This prevents users from performing tasks outside an application's scope.