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.