SQL Server 2005 Database Administration potx

SQL Server 2005

Database Administration

Agenda

• Introduction

• Installation

• Data Storage Architecture

• Database Physical Architecture

• Tuning and Monitoring

• Security

• High Availability

• Backup-Restore

• Administration Overview

SQL Server 2005

SQL Server 2005 – Components Overview

Relational Database

Engine

.NET CLR

Integration Services

SQL Server Agent Analysis Services

Reporting Services

Notification Services

Replication

SQL Server 2005

Service Broker

SQL Server 2005- Components

SQL Server 2005 – Relational Database Engine

Database Engine

• The primary component of SQL Server 2005

• The Online Transaction Processing (OLTP) engine for

SQL Server

• Responsible for the efficient storage, retrieval, and

manipulation of relational and Extensible Markup

Language (XML) formatted data

• Highly optimized for transaction processing

SQL Server 2005 – Analysis Services

Analysis Services

• Provides Online Analytical Processing (OLAP) and Data

Mining functionality

• Analytical processing done through multidimensional

data structures containing de-normalized and

aggregated data, often from diverse data sources

• Data Mining allows analysis of large quantities of data

for relationships and patterns

SQL Server 2005 – Integration Services

Integration Services

• Supports data extraction and loading to and from

heterogeneous data sources

• A complete change from its predecessor, Data

Transformation Services (DTS)

• Supports data extraction and loading to and from

heterogeneous data sources

SQL Server 2005 – Reporting Services

Reporting Services

• Provides solution for designing, deploying, and

managing flexible, dynamic Web-based reports

• Can pull information from various data sources

• Implemented as a Web service

• Supports user defined adhoc report

SQL Server 2005 – Notification Services

Notification Services

• Helps building applications that support the generation

and sending of data-driven notifications

• Notifications are personalized, timely messages that

can be sent to a wide range of devices according to

subscriber preference

• Enables sending critical information to customers,

partners, and employees

SQL Server 2005 – SQL Server Agent

SQL Server Agent

• Essential tool for automating database administration

• Executes scheduled administrative tasks through jobs

• Jobs can perform various database and non database

activities

SQL Server 2005 – Service Broker

SQL Server Service Broker

• Is a new technology in Microsoft SQL Server 2005

• Provides a robust asynchronous programming model

• Provides queuing and reliable messaging for SQL

Server between SQL Server instances or inside single

SQL Server instance

SQL Server 2005 – Replication

Replication

• Provides the ability to automate and schedule the

copying and distribution of data and database objects

from one database or server to another

• Ensured data integrity and consistency

• Powerful technology can also be used for maintaining

high database availability

SQL Server 2005

SQL Server 2005 Editions

SQL Server 2005 Editions

• SQL Server 2005 comes in 5 different editions

• The highest editions is “Enterprise”, where as the lowest is

“Express”

• The various editions are

Enterprise

Developer

Standard

Workgroup

Express

SQL Server 2005

SQL Server 2005 Installation