1 / 36

Chapter One

Chapter One. An Introduction to Microsoft SQL Server 2000 Administration. Objectives. Discuss the functionality of relational database management systems Define SQL Understand the important features of SQL Server 2000. Brief History of SQL. Structured Query Language (SQL)

plato-larsen
Télécharger la présentation

Chapter One

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter One An Introduction to Microsoft SQL Server 2000 Administration

  2. Objectives • Discuss the functionality of relational database management systems • Define SQL • Understand the important features of SQL Server 2000

  3. Brief History of SQL • Structured Query Language (SQL) • Used to insert, retrieve, modify and delete data in a relational database • The language supported by most relational databases • Originally developed by IBM in mid 1970s

  4. Brief History of SQL • American National Standards Institute (ANSI) • Primary group responsible for publishing SQL standards • Transact-SQL (T-SQL) • Used in Microsoft database management systems

  5. Database Architecture • At its simplest, a database is a file used to store information • The term database usually refers to a software application consisting of two parts: • Files used to store physical data • Application for managing and accessing these files

  6. Database Architecture • The software that manages the data files is what makes a database a database management system (DBMS) • This application is responsible for handling data requests, committing changes to the data and enforcing the structure of the database including: • Ensuring data is stored properly and that rules for defining data are not violated • Providing a level of disaster recovery by which data can be restored to a consistent state • Maintaining relationships between data entities residing in the database

  7. Relational Database Concepts • In abstract terms, a database is a collection of various entities that may or may not be related • Entity • Object comprised of various pieces of data • Attribute • Piece of information (data) that describes an entity

  8. Relational Database Concepts Figure 1-1: Simple table structure from a database in SQL Server 2000

  9. Microsoft SQL Server 2000 • Robust, feature laden database • Can support the most demanding applications • The administrative tools and simple installation make it one of the easiest database servers to configure and deploy

  10. Scalability and Availability • Availability • Ability of a system to be constantly accessible by its users • Scalability • Ability to easily grow in size to support a growing user base

  11. Scalability • Federation • Group of computers that cooperate to process a single workload • Distributed partition view • Single queriable entity that is horizontally partitioned and housed on multiple physical computers

  12. Scalability Figure 1-2: Federated database servers

  13. Availability • SQL Server 2000 provides reliability through features like online backups, failover clustering and log shipping • Log Shipping • New feature of SQL Server 2000 • Allows transaction logs to be backed up and automatically sent and applied to another server

  14. Database Features • Locking • Used to restrict access to a resource in a multiuser environment • SQL Server 2000 locks users out of a specific row, column or table automatically to prevent concurrent data modification problems • Does not prevent a user from accessing data if that data is being affected by another query

  15. Database Features • Distributed transactions • Transactions that group queries that affect data on multiple servers • Replication • Allows specified data sets to be mirrored to multiple locations

  16. Administrative Features • Important administrative tools included with SQL Server 2000: • Enterprise Manager • SQL Query Analyzer • SQL Profiler • Import/Export Data (DTS)

  17. Enterprise Manager • Allows an administrator to: • Register individual servers in a group • Configure all SQL Server 2000 options for each registered user • Create and administer all SQL Server 2000 databases, objects, logins, users and permissions in each registered server • Define and execute all SQL Server 2000 administrative tasks on each registered server

  18. SQL Query Analyzer • Provides the following functionality: • Free-form text editor for typing out T-SQL statements • Object browser and templates to speed up the process of developing T-SQL scripts • Graphical showplan diagram that presents the logical steps of an execution plan for a particular T-DQL statement

  19. SQL Profiler and Import/Export (DTS) • SQL Profiler • Captures events that occur for a particular instance of a SQL server 2000 • Import/Export (DTS) • The Import and Export Data item in the SQL Server 2000 program activates the Data Transformation Services (DTS) Wizard • Allows administrator to easily create import or export routines for a given SQL Server

  20. New Security Features • SQL Server 2000 security enhancements: • Role-based security for server, database and application profiles • Security auditing tools • Tracking of 18 different security events and additional subevents

  21. New Security Features • SQL Server 2000 security enhancements (cont.): • Secure Sockets Layer (SSL) • Protocol designed to enable encrypted, authenticated communication across the Internet • Kerberos • System developed by MIT that lets two parties on a network communicate securely • Provides both user authentication and mutual client server authentication

  22. New Security Features • SQL Server 2000 has achieved the U.S. government C-2 level security certification • This is the highest level of security available in the high-tech industry

  23. Internet Enabled Features • Extensible Markup Language (XML) • Language for creating documents containing structured information • XML has a similar syntax to HTML in that it is tag based

  24. Data Warehousing Features • Data warehouses • Usually built from a variety of data generated by various systems within an organization • Can be used to analyze large volumes of historical data in complex ways • Analysis Services in SQL Server 2000 provides full data warehousing and Online Analytical Processing (OLAP) capabilities

  25. Components of SQL Server 2000 • Almost all SQL Server 2000 functionality is provided by the four primary components of the product: • SQL Server database engine (MSSQLServer service) • SQL Server Agent (SQLServerAgent service) • Microsoft Distributed Transaction Coordinator (MS DTC service) • Microsoft Search service

  26. Components of SQL Server 2000 • Each of the four primary components is implemented as a Windows service • Service • Application usually launched when a computer is booted up and continues to run while that computer is on

  27. Components of SQL Server 2000 • SQL Server Database Engine (MSSQLServer Service) • Responsible for the basic operation of the database itself • Manages all of the files that house data and transaction logs for the database • MSSQLServer service handles all T-SQL requests sent to the server from client applications

  28. Components of SQL Server 2000 • One of the biggest challenges when implementing a large scale database system is administration • Certain people in an organization must be alerted when problems requiring human interaction occur • SQL Server Agent service (SQLServerAgent) • Supports features allowing the scheduling of periodic activities (e.g., backups, index rebuilds) or the notification to system administrators of problems that have occurred with the server

  29. Components of SQL Server 2000 • Objects SQL Server Agent Service allows administrators to define: • Jobs • Consist of one or more steps to be performed and can be scheduled to execute at regular intervals • Alerts • User-defined response to a SQL Server event • Operators • People that can be defined by their e-mail address or pager number in the database

  30. Components of SQL Server 2000 • Microsoft Distributed Transaction Coordinator (MS DTC service) • Manages transactions across multiple data sources • MS DTC provides services that applications can use to provide transaction management for particular operations, especially situations where data in multiple systems must be updated at the same time

  31. Components of SQL Server 2000 • Microsoft Search service • Provides full text editing and search engine capabilities • Supports indexing of database tables and documents on a network • Full text indexes comprised of specific tables and columns in a SQL Server 2000 database are defined and constructed by configuring the Search service

  32. Chapter Summary • Structured Query Language (SQL) is a standardized language of accessing data • The version of the language implemented by Microsoft SQL Server 2000 is known as T-SQL • T-SQL adheres to the ANSI-92 standard for SQL and offers functional extensions specific to Microsoft SQL Server 2000

  33. Chapter Summary • A database is comprised of files that house data and applications that manage and access these files • Relational databases allow data entities to be defined in tables • Each attribute of an entity is defined as a column within a table • A set of all attributes for a given entity is defined as a row in a table (a collection of column values)

  34. Chapter Summary • Relational databases perform the following functions: • Ensuring data is stored properly and that rules for defining data are not violated • Providing some level of disaster recovery • Maintaining relationships between data entities residing in the database

  35. Chapter Summary • Most of SQL Server 2000’s core functionality is provided by the following components: • SQL Server database engine (MSSQLServer service) • SQL Server Agent (SQLServerAgent • Microsoft Distributed Transaction Coordinator (MS DTC service) • Microsoft Search service

  36. Chapter Summary • SQL Server 2000 supports up to 32 processors and 64 gigabytes of RAM • SQL Server 2000 is capable of high availability and scalability through clustering and improved backup functionality

More Related