MS SQL SERVER 2005 User Manual Part 1

Installation Overview

  • Common task for DBAs: Installing SQL Server 2005 instance.

  • Select correct SQL Server edition and best hardware/software.

  • Understand installation types: default, named, or multiple instances.

SQL Server 2005 Editions

  • Enterprise Edition: Supports large OLTP, complex analytics, and data warehousing.

  • Standard Edition: Essential for small/medium organizations e-commerce and data warehousing.

  • Workgroup Edition: For small organizations with back end support for web servers.

  • Developer Edition: Full functionality for development/testing, not for production.

  • Express Edition: Free, limited database size, suitable for small applications or replacements for Access.

Installation Types

  • Default Instance: Takes server name as instance name, only one allowed per server.

  • Named Instances: Allows multiple instances with unique names; useful for testing or different customer environments.

  • Advantages of multiple instances include supporting different versions and user databases.

Installation Decisions

  • Key decisions during installation include:

    • Choosing service accounts for SQL Server and SQL Server Agent.

    • Selecting authentication modes (Windows vs. Mixed Mode).

    • Setting collation options for character data sorting and comparison.

Service Accounts

  • Different accounts increase security. Use domain user accounts over local/system accounts for production environments.

  • Network Service and local system accounts are not recommended due to excessive privileges.

Authentication Modes

  • Windows Authentication: Recommended for its security benefits.

  • Mixed Mode: Required for legacy applications that cannot use Windows authentication.

Configuring Collation

  • Default collation is set during installation; important for language and data handling.

  • Choose based on Windows locale unless special conditions apply.

Configuring Data Files

  • Data Files: Primary (.mdf) and Secondary (.ndf) files exist in SQL Server 2005.

  • Log Files: Keep separate for transaction recovery, recommended extension is .ldf.

  • Use filegroups to manage files for performance, allowing back up and restoration of individual files.

RAID Configurations

  • Common RAID levels:

    • RAID 0: Fastest but no fault tolerance.

    • RAID 1: Offers mirroring, better read performance.

    • RAID 5: Provides fault tolerance via parity, most popular.

    • RAID 10: Best for write-intensive applications combining striping and mirroring.

Database Security Configuration

  • Hash out SQL Server security model through permissions assigned to principals, including Windows logins and SQL Server logins.

  • Leverage fixed server roles to simplify permission management.

Managing Logins & Roles

  • Create logins using CREATE LOGIN, and control permissions using role memberships to streamline management.

  • Configure roles for easier permission control for groups of users.

Configuring Linked Servers

  • Use linked servers to access external data sources; define using OLE DB providers.

  • Set security models for linked servers to ensure secure connections, choose between self-mapping, delegation, and remote credentials.