• 360 likes • 446 Views
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)
E N D
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) • 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
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
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
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
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
Relational Database Concepts Figure 1-1: Simple table structure from a database in SQL Server 2000
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
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
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
Scalability Figure 1-2: Federated database servers
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
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
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
Administrative Features • Important administrative tools included with SQL Server 2000: • Enterprise Manager • SQL Query Analyzer • SQL Profiler • Import/Export Data (DTS)
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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)
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
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
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