1 / 11

Module 7: Basic Administration

Module 7: Basic Administration. Overview. Planning and preparatory steps for a SQL Server environment Creation and configuration of an instance of SQL Server and databases Characteristics of a database Basic database maintenance tasks. Installing SQL Server 2008.

beryl
Download Presentation

Module 7: Basic Administration

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. Module 7:Basic Administration

  2. Overview • Planning and preparatory steps for a SQL Server environment • Creation and configuration of an instance of SQL Server and databases • Characteristics of a database • Basic database maintenance tasks

  3. Installing SQL Server 2008 Stages in SQL Server 2008 installation • Planning—software edition, file locations, naming conventions, installation options (SQL Server Versions, OFA recommendations: file locations) • Resource procurement—hardware, operating system, utilities • Installing software and components • Network configuration—choice of protocols, load balancing, etc., using SQL Server Configuration Manager • Client connectivity and configuration—choice of protocols, server details using SQL Server Configuration Manager

  4. Demonstration 1: Installation of SQL Server In this demonstration you will learn how to: • Choose components • Apply service accounts • Define settings for installation • Finish installation

  5. Database System • Creating instances – Initial and subsequent instances can be named; one default, unnamed instance • Configuration – Database System configuration parameters can be set using sp_configureor Management Studio • Configuration parameters can be basic or advanced – Certain parameters are self-configuring and can also be manually set

  6. SQL Server Databases • Database name — constrained by the same rules as identifiers • Database/Schema owner — user that owns database and objects

  7. Database Configuration • Database configuration option set using ALTER DATABASE and sp_dboptionscan be classified as: • Auto options – auto_close, auto_shrink, … • Cursor options – address cursor restrictions and scope • Recovery options – recovery full | bulk_logged | simple • SQL options – ansi_padding, … • State options – offline | online, single user, …

  8. Database Maintenance • Information on the database can be obtained using sp_helpdb • Database maintenance operations in SQL Server and their Oracle equivalent are: • Shrinking database – equivalent to shrinking all the files in a tablespace • Changing default filegroup – same as changing default tablespace but set for the database rather than the user login • Attaching & detaching databases – similar to transporting tablespaces • Changing database name – similar in function to Oracle’s tablespace renaming feature • Changing database owner – not found in Oracle

  9. Data Dictionary • SQL Server term for data dictionary is system catalog • System catalog is decentralized with instance system objects in the Master and Resource databases and database-specific objects in individual databases • INFORMATION_SCHEMA views provide an interface to schema related system tables • SQL Server provides system catalog views to obtain metadata – equivalent to Oracle’s DBA_ / ALL_ / USER_ views

  10. System Stored Procedures System Stored Procedure categories include: • Active Directory • Catalog • Cursor • Database Engine • Database Mail and SQL Mail • Distributed Queries • Full-Text Search • Log Shipping • Automation Notification Services Replication Security SQL Server Profiler SQL Server Agent Web Task XML General Extended

  11. Review • We have seen the choices in SQL Server editions and components and how to install them • We have seen default instance created as part of installation and how named instances can be created • We have learned how to create and configure user databases • We have learned that there are various instance states (start, stop, and pause) and database states (read-only, read-write, single user, restricted user, online and offline) • We have gained insight into the location and composition of the system catalog

More Related