Introduction

---

SQL Server is a very robust database management system. SQL Server was designed to have many thousands of users accessing it at any point in time. On the other hand Microsoft Access, doesn't handle this type of load very well.
This makes SQL Server perfectly suited for database driven websites. You should never use Access for a database driven website - unless it is having very small amount of traffic (like your friends). Even then, you may find yourself getting errors due to multiple users trying to access the database at the same time.
SQL Server also contains some advanced database administration tools that enable organizations to schedule tasks, receive alerts, optimize databases, configure security accounts/roles, transfer data between other disparate sources, and much more.

System Requirements

---

The 32bit editions are different from the 64 bit editions
32 Bit
Minimum processor: PIII 600 (1 GHZ or faster recommended) Minimum OS:

Enterprise
Windows Server 2003 with SP1 or later Windows Small Business Server 2003 with SP1 io later Windows 2000 with SP4 or later.
In Short: All OS except Win XP

Standard
All of Enterprise, but also non server windows OS.
Windows Professional with SP4 and Windows XP with SP2 or later

Workgroups
All of standard and Window?s XP Media and Tabled Edition

Express
All of the above + Windows Home Edition with SP2 or later and Windows Server 2004 Web Edition with SP1 or later

Developer
1+2+3 + 4.5 Windows Home Edition with SP2

Memory Requirements
Enterprise, Development Standard and Workgroup: 512 min, 1 GB more recommended Express: 192 min, 512 or more recommended

Hard disk requirement
350 MB full and 390 for sample

Internet
Explorer SP1 required for all installations (Required for Microsoft Management Console (MMC) and HTML Help.
IIS Required for XML applications and for Reporting Services
TCP/IP must be enabled

64-Bit Editions of SQL Server 2005

Processor
IA64 Minimum: 1 GHZ or faster Itanium X64 minimuM. 1 Ghz or faster AMD Opteron, AMD Athlon 64, Intel Xenon with Intel EM64T support or PIV with EM64T support

Installing

---

There is a choice between a default or named instance. You can install only one default instance, but more named instances.

Understanding SQL Server 2005 Editions

There are five different Editions of the SQL Server 2005. Three of them have 64 bit native versions, the rest is 32 bit only.

Enterprise Edition
Largest, 32 bit and 64 bit, supports the largest online transaction processing (OLTP)

Standard Edition
32bit and 64bit, small and medium enterprises

Workgroup Edition
Small Enterprises, no limits on size of users, for small servers

Developer Edition
Same as Enterprise without production license

Express Edition
MSDE for SQL Server 2005; free(also for clients access); replacement for Access

Some hints to remember Features

Express; Workgroup; Standard; Enterprise

Number of CPUs: 1;2;4;unlimited. Think of a formula such as CPUs of a Edition == Previous Edition *2 expect for Enterprise.

Memory 1GB;3GB, Limit based on OS, Limit based on OS
64 bit support: Express und Workgroup can only have 64 bit support via WOW
Max DB Size: Express has 4 GB limit

Partitioning is only supported by Enterprise Edition
Database Mirroring is supported by Standard and Enterprise
Fail Over Clustering is supported by Standard(two Nodes) and Enterprise
PDF Rule: Remember Partitioning, Database Mirroring, Failover P == 1 only one edition supports

Log Shipping: All but Express
Management Studio All but Express
Database Tuning Advisor: Standard and Enterprise
Full Text Search: All but Express
SQL Server Agent Job Scheduling: All but Express
Best Practives Advisor: All
Notification Services Standard and Enterprise
Service Broker: All (Express Subsriber only)
Merge Replication: All (Restriction on Express and Workgroup)
Transactional Replication: Restriction on Express and Workgroup
Oracle Replication: Enterprise
Web Services (Http Endpoints): Standard and Enterprise
Report Server: All
Report Builder All but Express
BI Development Studio Express and Workgroup have Report Designer only
Enterprise Management Tool: All but Express
Native Support for Web Services: Reporting Services for Express and Workgroups only
Analysis Services for Standard and Enterprise only

Rules of thumb:

• Express Edition is limited nearly everywhere: It has only Report Server and Best Practices Analyzer and in some only a feature as subscriber only
• Enterprise Edition has everything
• Standard does not support Oracle Replication and Partitioning and has some Restrictions on Fail Over clustering
• Workgroup: hardest to Remember. It does not include the features to maintain large databases

Connecting to MS SQL Server

---

Downloading
To install both freetds and the drivers for iODBC and unixodbc use apt-get, i.e.,

apt-get install tdsodbc

If you plan to use iODBC, you'll want to run

apt-get install libiodbc2 libiodbc2-dev

If you're using Perl, after you install FreeTDS you'll want to install the DBD for Sybase:

export SYBASE=/etc
cpan DBD::Sybase

Instances

---

On one machine, there can be more editions

Default Instance: Only one. Please note some of the MS products using the SQL Server request the default instance

Determining Multiple or Single Instances

Multiple Instances mean more administration overhead. Additional Instances require additonal resources: Memory and processor capacity-

Advantages of multiple instances

• testing multiple versions
• testing service packs, dev databases and applications
• different customers require their own system and user databases with full administrative control or their instance
• desktop engine is embedded in the applications, because every application can install each own instance

Installing Instances

---

Decide on Security and collation
SQL Server Agent and SQL Server run as Windows Services.
They run in a context of a user account. Determining which account is an important decision.

Questions:

• Should you use a separate account for the SQL Server service and the SQL Server Agent service, or should you use the same account for both?
• Should you use a built-in system account or a domain user account?

Remember: The SQL Server jobs required to interact with the SQL Server Agent service. When the SQL Server Agent service must interact with different servers, DBAs create different accounts for these two services to avoid giving the SQL Server service more permission than it needs.

Build-in System account or Domain user account

You can choose between: Network service account, local system account, or a dedicated domain user account.

The Network service account is a special built-in system account that is similar to authenticated user accounts. This account has the same level of access to system resources and objects as members of the Users group. Services that run under this account will use the credentials of the computer account to access network resources. Not recommended to use.

The local system account is a Windows OS account that has full administrative rights on the local computer but has no network rights. You can use this account for development or testing of servers that you do not integrate with other server applications or to interact with any network resources. Not recommended.

Recommended: Create and use one or two dedicated domain user accounts for the SQL Server and SQL Server Agent services.

Configuring Files

---

data files contain data and objects and log files contain transaction log. filegroups are grouped datafiles for easier admin.

Data Files
There are primary and secondary Data Files.

The primary has the extension mdf and contains data and all information regarding data (such as information on the secondary data files). For optimal performance do not store data there.
The secondary has the extension ndf. No administrative data is stored here. There is a maximum of 32,766 secondary data files.

Log Files
Extension ldf. Each db needs at least one log file. You can create more than one

Filegroups
Logical structure of data files.
Also there are primary and secondary filegroups. The primary contains the primary data file and every secondary data file not stored in a specific filegroup. There can be up to 32,766 secondary file groups.

Filegroups can be configured as read only.
The filegroups are created with the SSMS or with the CREATE DATABASE command.

Five parameters have to be considered: Name, Filename, Size, Maxsize and Filegrowth

Configuring Raid Systems
There are Raid Levels 0,1,5 and 10. It has to be considered to be used in high performance environments. Configuring Database Files with RAID Systems RAID systems are arrays of disk drives that provide fault tolerance, more storage capacity, and better performance for the disk subsystem, depending on the configuration. Although RAID hardware systems are not part of the SQL Server configuration, they directly affect SQL Server?s performance. There are a variety of RAID levels, each of which uses a different algorithm for fault tolerance. The most common RAID levels used with SQL Server are 0, 1, 5, and 10.

• RAID 0 is also known as disk striping because it creates a disk file system called a stripe set. RAID 0 gives the best performance for read and write operations because it spreads these operations across all the disks in the set. However, RAID 0 does not provide fault tolerance; if one disk fails, you lose access to all the data on the stripe set.
• RAID 1, also known as disk mirroring, provides a redundant copy of the selected disk. RAID 1 improves read performance but can degrade the performance of write operations.
• RAID 5, the most popular RAID level, stripes the data across the disks of the RAID set as does RAID 0, but it also adds parity information to provide fault tolerance. Parity information is distributed among all the disks. RAID 5 provides better performance than RAID 1. However, when a disk fails, read performance decreases.
• RAID 10, or RAID 1+0, includes both striping without parity and mirroring. RAID 10 offers better availability and performance than RAID 5, especially for write-intensive applications. The RAID configuration that is best for your database files depends on several factors, including performance and recoverability needs. RAID 10 is the recommended RAID system for transaction log, data, and index files. If you have budget restrictions, keep transaction log files in a RAID 10 system, and store data and index files in a RAID 5 system.

Configuring Mail

---

SQL Server Mail does not depend on Extended Mapi

Prerequisites for Database Mail:

• Database Mail has to be enabled via the Surface Area Configuration Tool, Database Mail Configuration Wizard or the sp_configure.
• Service Broker needs to be enabled in the Database mail host database msdb
• The Database Mail externals executables need access to the SMTP Server

Architecture Four Components: Configuration Component, Messaging Component, Database Mail executable, Logging and auditing component.

The configuration components consist of a database Mail account and a Database Mail profile. The Database Mail account contains the information that SQL Server uses to send e-mail messages to the SMTP server, such as the SMTP server name, the authentication type, and the e-mail address.