SQL Server 2005 2008 Administration by JWN19Nb


									                        SQL SERVER ADMINISTRATION

1. Introduction to Microsoft SQL Server
       a. What is a Database?
       b. Why to use database?
       c. What is RDBMS?
       d. Types of RDBMS
       e. Advantages of SQL Server
       f. Requirements and scope of SQL Server

2. Roles and Responsibilities of DBA
       a. What is Administration?
       b. Roles of DBA
       c. Licensing and Pricing of SQL Server

3. System Design and Architecture
       a.   RAID Configuration
       b.   Capacity Planning
       c.   Installation
       d.   Upgrades and Backward Compatibility

4. SQL Server Database Design
       a.   Logical and Physical architecture of database
       b.   Creating Database
       c.   Modifying structure of Database
       d.   Files and File Groups
       e.   Data Placement and Audit Placement

5. SQL Server Objects
       a.   Data Types
       b.   Tables
       c.   Constraints
       d.   Views
       e.   Indexes and performance Considerations
       f.   Triggers
       g.   Stored Procedures
       h.   User Defined Functions
       i.   Cursors
       j.   Synonyms

6. Transactions
       a.   ACID Properties
       b.   Commit Modes and Rollbacks
       c.   Savepoints
       d.   Transaction Locks and Locking Mechanism
       e.   Isolation Levels
       f.   Blocking and Deadlocks

7. Database Snapshots
      a. What is a DB Snapshot
      b. Why we use Snapshots
      c. How to design Snapshots
      d. Advantages and disadvantages
      e. Precautions when designing Snapshots

8. SQL Server Partitions
       a.   Partitioning Fundamentals
       b.   Designing Partitions
       c.   Creating Partitions
       d.   Viewing Partition Information
       e.   Maintaining Partitions

9. Backup Fundamentals
       a. System Failures and need of Backups
       b. Use of Transaction Log
       c. Microsoft SQL Server Automatic Recovery
       d. Recovery Models
               i. Simple Recovery Model
              ii. Full Recovery Model
             iii. Bulk – Logged Recovery Model
       e. Types of Backups
               i. Data( Full) Backups
              ii. Differential Backups
             iii. Log Backups
             iv. Copy-Only Backups
              v. Full-Text Catalog Backups
       f. Media Devices and Media Sets
       g. Overview of Backup History Tables
       h. Real world Backup Strategy

10. Restoring Data
       a. Restore and Recovery Concepts
       b. Restoring Data from Backups
               i. Complete, Differential and Log Restores
              ii. Point-In-Time Restores
             iii. File and File Group Restores
             iv. Page Restores
              v. Piecemeal and Online Restores
             vi. Reverting to Database Snapshots

11. Replication
       a. Replication Fundamentals
       b. Use of Replication
               i. Data Warehousing
              ii. Distributing and Consolidating Data
             iii. Offloading Report Processing
       c. Replication Components
       d. Types of Replication
               i. Snapshot Replication
              ii. Transactional Replication
             iii. Merge Replication
       e. Configuring Replication
               i. Configure Distributor
              ii. Configure Publications
       f. Managing Replication
               i. Publisher Properties
              ii. Distributor Properties
             iii. Disable Publishing and Distribution
             iv. Replication Monitor
              v. Update Replication Passwords
             vi. Monitoring and Tuning Replication
12. Disaster Recovery Solutions
       a. Log Shipping
               i. Configuring Log Shipping
              ii. Tuning and Monitoring Log Shipping
             iii. Log Shipping Failover
             iv. Removing Log Shipping
       b. Database Mirroring
               i. Configuring Database Mirroring
              ii. Planning Considerations
             iii. Monitoring Database Mirroring
             iv. Snapshots and Mirroring for Replication
       c. Replication as a Disaster Recovery Entity
       d. SQL Server Clusters
               i. Concept of Cluster
              ii. Basic Concepts
             iii. Cluster Components
             iv. Cluster Application Types
              v. MSCS Modes
             vi. Planning and Installation

13. Managing Security
       a. Authentication
               i. Windows Authentication
              ii. Mixed Authentication
       b. Creation and using Logins
       c. Creation and maintaining Users
       d. Roles
               i. Database Level Roles
              ii. Server Level Roles
             iii. User Defined Roles
             iv. Application Roles
       e. Permissions
               i. Server Permissions
              ii. Data Base Permissions
             iii. Statement Permissions
       f. Creating and using Certificates
14. Performance Tuning and Troubleshooting
       a. Query Optimization
               i. Fine-tuning Queries
              ii. Use of Query Hints
             iii. Analyzing of Execution Plans
                      1. Estimated Execution Plan
                      2. Actual Query Plan
       b. Need for Documentation
       c. Monitoring and Tuning Hardware
       d. Tuning Database Layout
       e. SQL Server Tools
               i. Profiler
              ii. SQL Trace
             iii. SSMS
             iv. Database Engine Tuning Advisor
       f. Dynamic Management Views
               i. Understanding DMVs
              ii. DMVs for each SQL Server Object
             iii. Creating Performance Data Warehouse

15. Linked Servers
        a. Remote Servers
        b. Linked Servers
        c. Distributed Transactions (MSDTC)

16. Data Movement
       a. DB Attach and DB Detach
       b. SELECT INTO statement
       c. BCP
       d. Import and Export of SQL Data using SSIS Packages

To top