SQL Server 2008 Administration - PDF by pcherukumalla

VIEWS: 203 PAGES: 91

									               Welcome to Train Signal
               Train Signal, Inc.
               Mel Haynes




              You’re Watching
               Video 1
           Introduction to
           SQL Server 2008
            Administration




               About me…
               Train Signal, Inc.
               Mel Haynes




Hello and Welcome
– Mel Haynes, MCP
 • Visual Basic and ASP development
 • Developer and trainer
 • BS and MS in Computer Science
 • mel@trainsignal.com




               SQL Server 2008 Administration
               Train Signal, Inc.
               Mel Haynes




Contents

•Welcome to Microsoft SQL Server 2008
•Installing SQL Server 2008
•An Introduction to SQL Server 2008 Tools
•Creating SQL Server 2008 Databases
•Configuring SQL Server 2008 Security
•Implementing Disaster Recovery




                                                1
              SQL Server 2008 Administration
              Train Signal, Inc.
              Mel Haynes




Contents

•Optimizing and Monitoring SQL Server 2008
•Automating Administrative Tasks
•SQL Server High Availability Options
•Implementing SQL Server Replication
•Resource Governor
•Performing Data Management Tasks




              SQL Server 2008 Administration
              Train Signal, Inc.
              Mel Haynes




Contents

•Data compression
•Common SQL Server Commands
•SQL Server Database Mail
•SQL Server 2008 Certification 101




              SQL Server 2008 Administration
              Train Signal, Inc.
              Mel Haynes




The environment

• SQL Server 2008 Enterprise Server
• microsoft.com/sqlserver
• Windows 2008 or 2003
• Virtual machines
• Sample database
• Sample files on course CD or online




                                               2
               Welcome to Train Signal
               Train Signal, Inc.
               Mel Haynes




              You’re Watching


         Video 2
       Welcome to
      SQL Server 2008




               Welcome to SQL Server 2008
               Train Signal, Inc.
               Mel Haynes




Overview

•What is SQL Server 2008?
•SQL Server 2008 Solutions
•Veronica's Veronicas




               Welcome to SQL Server 2008
               Train Signal, Inc.
               Mel Haynes




SQL Server 2008: The main players

•Data engine
•Integration
 Services
•Analysis Services
•Reporting Services




                                            3
              Welcome to SQL Server 2008
              Train Signal, Inc.
              Mel Haynes




Solutions


              DW                                ETL

                                   DM
 OLAP

               OLTP                        BI




              Welcome to SQL Server 2008
              Train Signal, Inc.
              Mel Haynes




Solutions
• OLAP – Online Analytical Processing
• Data warehouse
• OLTP – Online Transaction Processing
• Data mining
• BI – Business Intelligence
• ETL – Extract Transform Load




              Welcome to SQL Server 2008
              Train Signal, Inc.
              Mel Haynes




Data engine
•Relational database
 management
 systems (RDMS)
•Structured Query
 Language
•Transact-SQL (T-SQL)




                                                      4
                Welcome to SQL Server 2008
                Train Signal, Inc.
                Mel Haynes




Data engine
•Management Studio
•Performance tools
•Automation tools
•Windows
 integration
•SQL Server Services
•Security



                Welcome to SQL Server 2008
                Train Signal, Inc.
                Mel Haynes




Analysis and Integration Services
• Analysis:
   –Making business decisions

• Integration:
   –Extract, Transform, Load (ETL)
   –Data integration and data
    transformations




                Welcome to SQL Server 2008
                Train Signal, Inc.
                Mel Haynes




Solutions
• BI, Data mining, OLAP
   –BI, SSAS, SSRS

• Data warehouse, OLTP
   –Data Engine

• ETL
   –SSIS




                                             5
            Welcome to SQL Server 2008
            Train Signal, Inc.
            Mel Haynes




            Welcome to SQL Server 2008
            Train Signal, Inc.
            Mel Haynes




Terms
• OLAP – Online Analytical Processing
• Data warehouse – Centralized data
• OLTP – Online Transaction Processing
• Data mining – Getting answers
• BI – Business Intelligence
• ETL – Extract, Transform, Load




            Welcome to SQL Server 2008
            Train Signal, Inc.
            Mel Haynes




Terms
• Data engine – Core database
• Integration Services – ETL solution
• Analysis Services – Analysis solution
• Reporting Services – Reporting package
• RDMS – Relational database
  management systems
• SQL – Structured Query Language
• T-SQL – Transact-SQL




                                           6
              Welcome to SQL Server 2008
              Train Signal, Inc.
              Mel Haynes




What we covered…

• Database technologies
• Microsoft data solutions




              Welcome to SQL Server 2008
              Train Signal, Inc.
              Mel Haynes




You should know how to…

•Pick the right SQL Server tool




              Welcome to Train Signal
              Train Signal, Inc.
              Mel Haynes




            You’re Watching

         Video 3
        Installing
     SQL Server 2008




                                           7
                         Installing SQL Server 2008
                         Train Signal, Inc.
                         Mel Haynes




Overview
• SQL Server 2008 Editions
• Requirements
• Basic SQL Server 2008 Security Concepts
• Installing SQL Server 2008 – Right Here Live!




                         Installing SQL Server 2008
                         Train Signal, Inc.
                         Mel Haynes




So many variations to choose from!
      Edition                                 Description
              • Enterprise-class scalability,
   Enterprise
              • High availability,
   (x86, x64,
              • Full business intelligence
     IA64)
                capabilities
              • Built-in business intelligence
   Standard
   (x86, x64)
                capabilities for departmental
                applications.
     Small    • SQL Server Standard
    Business    licensed for 75 or fewer
   (x86, x64)   computers.




                         Installing SQL Server 2008
                         Train Signal, Inc.
                         Mel Haynes




Specialized editions of SQL Server 2008
Edition                                 Description
SQL Server 2008                         “SQL Server 2008
Developer                               Enterprise” licensed for
(x86, x64, IA64)                        development and test only.
                                        Upgradable for production
                                        use.




                                                                     8
                      Installing SQL Server 2008
                      Train Signal, Inc.
                      Mel Haynes




Specialized editions of SQL Server 2008
Edition                              Description
Workgroup                           For running local branch
(x86, x64)                          location databases reporting
                                    remote synchronization.




                      Installing SQL Server 2008
                      Train Signal, Inc.
                      Mel Haynes




Specialized editions of SQL Server 2008
Edition                              Description
Web                                  Low total-cost-of-ownership
(x86, x64)                           option for Web hosts and
                                     Web sites.




                      Installing SQL Server 2008
                      Train Signal, Inc.
                      Mel Haynes




Specialized editions of SQL Server 2008
Edition                              Description
• Express Edition (SSE)              SQL Server Express is free
  (x86 and x64)                      and can be redistributed by
• SSE with Tools
                                     ISV's (subject to agreement).
  (x86 and x64)
• SSE with Adv. Services
  (x86 and x64)




                                                                     9
                      Installing SQL Server 2008
                      Train Signal, Inc.
                      Mel Haynes




Specialized editions of SQL Server 2008
Edition                              Description
• Compact 3.5 SP1                    SQL Server Compact 3.5 is a
  (x86)                              free, embedded database
• Compact 3.1
                                     ideal for building stand-alone
  (x86)
                                     and occasionally connected
                                     applications.




                      Installing SQL Server 2008
                      Train Signal, Inc.
                      Mel Haynes




Features Supported by the Editions



       http://msdn.microsoft.com




                      Installing SQL Server 2008
                      Train Signal, Inc.
                      Mel Haynes




SQL Server 2008 Requirements


                                  Hardware
                                  Disk space
                                  Software




                                                                      10
                              Installing SQL Server 2008
                              Train Signal, Inc.
                              Mel Haynes




     Hardware and OS


               Enterprise 32                       Enterprise 64

Processor      1.0 GHz min. 2.0 GHz rec. 1.4 GHz min 2.0 GHz rec.
OS             Windows 2003 or 2008                Windows 2003 or 2008
RAM            512 MB min. 2 GB rec.               512 MB min. 2 GB rec.




                              Installing SQL Server 2008
                              Train Signal, Inc.
                              Mel Haynes




     Hard Disk Space Requirements (32-Bit and 64-Bit)

             Feature                                 Disk space
          Database Engine and data files,                  280 MB
          Replication, and Full-Text Search

          Analysis Services and data files                 90 MB

          Reporting Services and Report Manager            120 MB

          Integration Services                             120 MB

          Client Components                                850 MB

          SQL Server Books Online and SQL Server           240 MB
          Compact Books Online




                              Installing SQL Server 2008
                              Train Signal, Inc.
                              Mel Haynes




     Software and where to get it?




  www.microsoft.com/sqlserver
  .Net Framework
  Windows Installer Update




                                                                           11
                        Installing SQL Server 2008
                        Train Signal, Inc.
                        Mel Haynes




SQL Server Security 101- “Just the facts please.”


                         Windows Authentication

                                             vs
                         SQL Server Authentication




                        Installing SQL Server 2008
                        Train Signal, Inc.
                        Mel Haynes




What we covered…

• “31 Flavors” of SQL Server 2008
• What I need to get started
• Authentication modes
• Installation process




                        Installing SQL Server 2008
                        Train Signal, Inc.
                        Mel Haynes




Terms

1.      Authentication Mode
2.      Windows Authentication
3.      SQL Server Authentication




                                                     12
                          Installing SQL Server 2008
                          Train Signal, Inc.
                          Mel Haynes




You should know how to …

• Select the version of SQL that’s right for you.
• Determine what SQL options you need.
• Install SQL Server 2008.




                          Welcome to Train Signal
                          Train Signal, Inc.
                          Mel Haynes




                        You’re Watching

            Video 4
       An Introduction to
        SQL Server 2008
              Tools




                          An Introduction to SQL Server 2008 Tools
                          Train Signal, Inc.
                          Mel Haynes




• SQL Server 2008 Management Studio
•Loading the
Overview (a. k. a. the 50 cent tour of SQL 08)
                     Database
•Business Intelligence Development
 Studio
•Performance Tools
•Configuration Manager
•Reporting Services Manager
•Command-Line Tools




                                                                     13
                         An Introduction to SQL Server 2008 Tools
                         Train Signal, Inc.
                         Mel Haynes




System Databases

• master
• model
• tempdb
• msdb
• ReportServer
• ReportServerDB
• distribution




                         An Introduction to SQL Server 2008 Tools
                         Train Signal, Inc.
                         Mel Haynes




What we covered…

• Overview of SQL Server 2008 tools




                         An Introduction to SQL Server 2008 Tools
                         Train Signal, Inc.
                         Mel Haynes




Terms

1.      None
2.      System Database
3.      Business Intelligence
4.      Replication




                                                                    14
                           An Introduction to SQL Server 2008 Tools
                           Train Signal, Inc.
                           Mel Haynes




 You should know how to …

 • Access several of the SQL Server 2008 tools
 • Load an existing database using a script and .csv files.




                           Welcome to Train Signal
                           Train Signal, Inc.
                           Mel Haynes




                         You’re Watching


         Video 5
Creating SQL Server 2008
       Databases




                           Creating SQL Server 2008 Databases
                           Train Signal, Inc.
                           Mel Haynes




 • Database Planning
 OverviewDatabases
 • Creating
 • Database Diagrams
 • Views
 • Synonyms




                                                                      15
                           Creating SQL Server 2008 Databases
                           Train Signal, Inc.
                           Mel Haynes




Database Planning

• Where do I put it?
• How much space will I need?
• What are the performance needs?
• How will I protect and maintain it?




                           Creating SQL Server 2008 Databases
                           Train Signal, Inc.
                           Mel Haynes




Creating Databases
• Files and Filegroups
• Database options




                           Creating SQL Server 2008 Databases
                           Train Signal, Inc.
                           Mel Haynes




Database Files and Filegroups

• Primary (.mdf)
• Transaction Log (.ldf)
• Secondary (.ndf)




                                                                16
                        Creating SQL Server 2008 Databases
                        Train Signal, Inc.
                        Mel Haynes




Database Files and Filegroups

• Primary
     (.mdf, .ndf01….ndfn)
• User-defined




                        Creating SQL Server 2008 Databases
                        Train Signal, Inc.
                        Mel Haynes




Database Files and Filegroups

• Primary
     (.mdf)
• User-defined
      (.ndf01….ndfn)




                        Creating SQL Server 2008 Databases
                        Train Signal, Inc.
                        Mel Haynes




Database files

• Location
• File size
• File growth options




                                                             17
                        Creating SQL Server 2008 Databases
                        Train Signal, Inc.
                        Mel Haynes




Files and Filegroups

1.     Create additional db files if needed.
2.     Create additional filegroups.
3.     Create tables and assign to filegroups.




                        Creating SQL Server 2008 Databases
                        Train Signal, Inc.
                        Mel Haynes




Default Database Values

1.    COMPATIBILITY_LEVEL = 100
2.    ANSI_NULL_DEFAULT OFF
3.    ANSI_NULLS OFF
4.    ANSI_PADDING OFF
5.    ANSI_WARNINGS OFF
6.    ARITHABORT OFF
7.    AUTO_CLOSE OFF
8.    AUTO_CREATE_STATISTICS ON
9.    AUTO_SHRINK OFF
10.   AUTO_UPDATE_STATISTICS ON
11.   CURSOR_CLOSE_ON_COMMIT OFF
12.   CURSOR_DEFAULT GLOBAL




                        Creating SQL Server 2008 Databases
                        Train Signal, Inc.
                        Mel Haynes




Default Database Values

13.   CONCAT_NULL_YIELDS_NULL OFF
14.   NUMERIC_ROUNDABORT OFF
15.   QUOTED_IDENTIFIER OFF
16.   RECURSIVE_TRIGGERS OFF
17.   DISABLE_BROKER
18.   AUTO_UPDATE_STATISTICS_ASYNC OFF
19.   DATE_CORRELATION_OPTIMIZATION OFF
20.   PARAMETERIZATION SIMPLE
21.   READ_WRITE
22.   RECOVERY FULL
23.   MULTI_USER
24.   PAGE_VERIFY CHECKSUM




                                                             18
                        Train Signal, Inc.
                        Mel Haynes




Database Diagrams




                        Creating SQL Server 2008 Databases
                        Train Signal, Inc.
                        Mel Haynes




A DB with a View

• Presents complete picture
• Restricts data access
• Provides greater security




                        Creating SQL Server 2008 Databases
                        Train Signal, Inc.
                        Mel Haynes




Creating a Synonym

 USE flowers;
 GO
 CREATE SYNONYM FlowerName
 FOR flowers.genus.species;
 GO

FlowerName = flowers.genus.species




                                                             19
                           Creating SQL Server 2008 Databases
                           Train Signal, Inc.
                           Mel Haynes




Creating Databases

  Files and Filegroups
  Database options




                           Creating SQL Server 2008 Databases
                           Train Signal, Inc.
                           Mel Haynes




Recommendations

• A single data file and a single transaction log file is okay.
• Put the transaction log file on different physical disk from all other files.




                           Creating SQL Server 2008 Databases
                           Train Signal, Inc.
                           Mel Haynes




What we covered…

• Creating databases
     – auto_shrink
     – auto_growth
• SQL Server data files
     – .mdf, .ndf, .ldf
     – File Locations
• Creating tables




                                                                                  20
                         Creating SQL Server 2008 Databases
                         Train Signal, Inc.
                         Mel Haynes




Terms

• Filegroups
 Mechanism for managing multiple data files as one unit

• Synonyms
 Short alternative names used to reference database objects

• View
 Data perspective created via a query

• Database Diagrams
 Graphical representation of database objects and the relationships between
 them




                         Creating SQL Server 2008 Databases
                         Train Signal, Inc.
                         Mel Haynes




Terms

•auto_shrink
 SQL Server will reduce db file sizes if the space is not being
 used.

•auto_growth
 If additional space is needed SQL Server 2008 will increase
 db file size to accommodate the grow.




                         Creating SQL Server 2008 Databases
                         Train Signal, Inc.
                         Mel Haynes




You should know how to …

• Create a database
• Create a database file group
• Create database tables
• Distribute tables across multiple disks using filegroups




                                                                              21
                         Welcome to Train Signal
                         Train Signal, Inc.
                         Mel Haynes




                       You’re Watching

       Video 6
     Configuring
   SQL Server 2008
      Security




                         Configuring SQL Server 2008 Security
                         Train Signal, Inc.
                         Mel Haynes




Overview
• SQL Server security landscape
• Managing user access
• Protecting your data
• Best Practices




                         Configuring SQL Server 2008 Security
                         Train Signal, Inc.
                         Mel Haynes




SQL Server Security Landscape

• Limiting access to the database
 and within the database
• Network access security
• Creating secure database applications (CLR integration)




                                                                22
                          Configuring SQL Server 2008 Security
                          Train Signal, Inc.
                          Mel Haynes




Physical Security

• Place servers in a secure location




                          Configuring SQL Server 2008 Security
                          Train Signal, Inc.
                          Mel Haynes




Managing User Access

• Managing logins and users
• Server and database roles
• Database permissions
• Schemas




                          Configuring SQL Server 2008 Security
                          Train Signal, Inc.
                          Mel Haynes




Logins and Users

• People




                                                                 23
                         Configuring SQL Server 2008 Security
                         Train Signal, Inc.
                         Mel Haynes




 Logins and Users

 • People
 • Applications
 • SQL Server Services




                              Configuring SQL Server 2008 Security


Server-Level Role                             Database-level role
 •   sysadmin                                  •   db_owner
 •   serveradmin                               •   db_securityadmin
 •   securityadmin                             •   db_accessadmin
 •   processadmin                              •   db_backupoperator
 •   setupadmin                                •   db_ddladmin
 •   bulkadmin                                 •   db_datawriter
 •   diskadmin                                 •   db_datareader
 •   dbcreator                                 •   db_denydatawriter
 •   public                                    •   db_denydatareader




                         Configuring SQL Server 2008 Security
                         Train Signal, Inc.
                         Mel Haynes




 Schemas


 •Object permission management
 •Groups for objects
 •Part of the ANSI standards




                                                                       24
                     Configuring SQL Server 2008 Security
                     Train Signal, Inc.
                     Mel Haynes




Grouping Users - Roles


          db_datareader, db_datawriter


     Ron                            Ray                  Kim
                                                 db_securityadmin
     Mel                            Sue                  Tom




                     Configuring SQL Server 2008 Security
                     Train Signal, Inc.
                     Mel Haynes




Grouping Objects - Schemas


 Sales Schema


          Table                 StPrc            StPrc

                                          View

          Table                                             DB
                                          View




                     Configuring SQL Server 2008 Security
                     Train Signal, Inc.
                     Mel Haynes




Schemas

                                            CREATE TABLE
                                             HR.Benefits (abc int)

                                            ALTER SCHEMA Sales
                                             TRANSFER
                                             dbo.Customers




                                                                     25
                        Configuring SQL Server 2008 Security
                        Train Signal, Inc.
                        Mel Haynes




Protecting your data

• Auditing SQL Server
    – c2 and Common Criteria Certifications
• Configuring “surface area”
• Remote access
• File security
• SQL Server data encryption




                        Configuring SQL Server 2008 Security
                        Train Signal, Inc.
                        Mel Haynes




Other stuff to know…

•The SQL Server sa Login
 The SQL Server sa login is created by default.

• Certificate-based SQL Server Logins
 Server names enclosed by double hash marks (##) are for internal system use
 only and should not be deleted.




                        Configuring SQL Server 2008 Security
                        Train Signal, Inc.
                        Mel Haynes




Other stuff to know…


• public
 Database Role
 Every database user belongs to the
 public database role. It provides
 default permissions when a user has
 not been granted or denied specific
 permissions.




                                                                               26
                       Configuring SQL Server 2008 Security
                       Train Signal, Inc.
                       Mel Haynes




Other stuff to know…

•INFORMATION_SCHEMA and sys
 Every database includes two entities that appear as users in catalog views:
 INFORMATION_SCHEMA and sys. These entities are required by SQL Server. They
 cannot be modified or dropped.

•guest User
•dbo




                       Configuring SQL Server 2008 Security
                       Train Signal, Inc.
                       Mel Haynes




Best Practices
1.     Use the NTFS file system
2.     Use RAID for critical data files
3.     Disable unnecessary protocols
4.     Run SQL Server services with the lowest possible permissions
5.     Associate SQL Server services with low privileged Windows accounts




                       Configuring SQL Server 2008 Security
                       Train Signal, Inc.
                       Mel Haynes




Best Practices

6.     Rename and assign a strong password to the sa account
7.     Only turn on what you need
8.     Use Windows authentication




                                                                               27
                           Configuring SQL Server 2008 Security
                           Train Signal, Inc.
                           Mel Haynes




What we covered…

• logins and users
• Schemas
• Database roles
• Server roles
• Built-in logins, users and roles
• SQL Server firewall configuration
• SQL Server Audits




                           Configuring SQL Server 2008 Security
                           Train Signal, Inc.
                           Mel Haynes




Terms
1.      Logins vs Users                         −   Server vs DB
2.      Server role
                                                −   Server wide rights
3.      Database role
4.      Application role                        −   Database rights
5.      Schema                                  −   Application access
6.      sa                                      −   Groups DB objects
7.      dbo
8.      Surface Area
                                                −   System Admin login
9.      c2 and                                  −   Database owner
        Common Criteria
        Certifications
                                                −   The enabled features
                                                −   Auditing
                                                    specifications
                                                    supported by SQL
                                                    Server




                           Configuring SQL Server 2008 Security
                           Train Signal, Inc.
                           Mel Haynes




You should know how to …

• Allow users and applications managed access to SQL Server resources
• Use schemas to manage
 SQL Server objects
• Track user logins and login attempts




                                                                           28
              Welcome to Train Signal
              Train Signal, Inc.
              Mel Haynes




             You’re Watching

       Video 7
  SQL Server 2008
Implementing Disaster
      Recovery




              SQL Server 2008 Implementing Disaster Recovery
              Train Signal, Inc.
              Mel Haynes




Overview
•Database Recovery Models
•SQL Server 2008 Backup Types
•SQL Server 2008 Database Backup
•Backup Options
•Backup Strategies
•Restoring Databases



              SQL Server 2008 Implementing Disaster Recovery
              Train Signal, Inc.
              Mel Haynes




SQL Server Recovery Models

•Full Recovery Model
•Bulk-Logged Recovery Model
•Simple Recovery Model




                                                               29
                SQL Server 2008 Implementing Disaster Recovery
                Train Signal, Inc.
                Mel Haynes




Demo:

•Show where recovery model is
 set or changed in existing db
•Show creating a new db and
 where recovery model is set.




                SQL Server 2008 Implementing Disaster Recovery
                Train Signal, Inc.
                Mel Haynes




Full Recovery Model
• Transactional log files are maintained
• No work is lost due to a lost or damaged data file
• Can recover to an arbitrary point in time
• Normally no work loss exposure
  (If the “tail” of the log is damaged, changes since
  the most recent log backup must be redone.)




                SQL Server 2008 Implementing Disaster Recovery
                Train Signal, Inc.
                Mel Haynes




Use the Full Recovery Model when…
1. You must be able to recover all the data
2. If the database contains multiple filegroups, and
   you want a piecemeal restore of read/write
   secondary filegroups
3. You must be able to recover to the point of
   failure
4. You are willing to incur the administrative costs
   of transaction log backups
5. You are willing to incur a performance “hit”




                                                                 30
                 SQL Server 2008 Implementing Disaster Recovery
                  Train Signal, Inc.
                  Mel Haynes




  Bulk Logged Recovery Model
• Logs are maintained, but not individual transactions
• Supports high-performance bulk copy operations
• Reduces log space for most bulk operations
• If the log is damaged or bulk-logged operations
 occurred since the most recent log backup, changes
 since that last backup must be redone.




                 SQL Server 2008 Implementing Disaster Recovery
                    Train Signal, Inc.
                    Mel Haynes




  What gets lost…
                                           Log
                                         backups



                    Time . . . . . . .



      Full                 Log            Bulk
     backup               backup          load




                 SQL Server 2008 Implementing Disaster Recovery
                  Train Signal, Inc.
                  Mel Haynes




  Bulk Logged Recovery Model
• Can recover to the end of any backup
• If a log backup contains any bulk-logged operations,
 you cannot restore to a point-in-time within that log
 backup; you can restore only the whole log backup




                                                                  31
                      SQL Server 2008 Implementing Disaster Recovery
                       Train Signal, Inc.
                       Mel Haynes




Use the Bulk-Logged Recovery Model when…
1. You are running large-scale bulk
   operations, and you do not require
   point-in-time recovery of the database
2. You must run periodic bulk operations
3. You want to conserve disk space by
   using minimal logging

As soon as a set of bulk operations
finishes, immediately switch back to the
full recovery model.




                      SQL Server 2008 Implementing Disaster Recovery
                       Train Signal, Inc.
                       Mel Haynes




Switching Between Full and Bulk-Logged Recovery
•   For a database that uses full recovery, switching to the bulk-logged
    recovery model temporarily for bulk operations improves
    performance
•   Conditions:
     1. Users are currently not allowed in the database
     2. No modifications are made during bulk processing

•   Recommends:
    1. Before switching to the bulk-logged recovery model back up
       the log
    2. After performing the bulk operations immediately switch back
       to full recovery mode
    3. After switching back to the full recovery model, you back up
       the log again




                      SQL Server 2008 Implementing Disaster Recovery
                       Train Signal, Inc.
                       Mel Haynes




Simple Recovery Model
• No log backups
• Automatically reclaims log space to keep space
  requirements small, essentially eliminating the
  need to manage the transaction log space
• Changes since the most recent backup are
  unprotected. In the event of a disaster, those
  changes must be redone
• Can recover only to the end of a backup




                                                                           32
                SQL Server 2008 Implementing Disaster Recovery
                Train Signal, Inc.
                Mel Haynes




Use the simple recovery model when…
• Point of failure recovery is unnecessary
• You are willing to lose all the updates between a
  failure and the previous backup or differential
  backup




                SQL Server 2008 Implementing Disaster Recovery
                Train Signal, Inc.
                Mel Haynes




Backup types:

  Recovery models
        vs
   Backup types



                SQL Server 2008 Implementing Disaster Recovery
                Train Signal, Inc.
                Mel Haynes




Backup types:

Recovery models:
Determine what’s
available to backup




                                                                 33
                SQL Server 2008 Implementing Disaster Recovery
                 Train Signal, Inc.
                 Mel Haynes




Backup types:

  Backup types:
 Determine what’s
    in a backup



                SQL Server 2008 Implementing Disaster Recovery
                 Train Signal, Inc.
                 Mel Haynes




Backup types: Whole or partial?
Every recovery model lets you back up a whole or
partial SQL Server database or individual files or
filegroups of the database

• Full backup
  A full backup contains all the data in a specific
• Differential backupfilegroups or files, and also
  database or set of
  enough log to allow for recovering that data




                SQL Server 2008 Implementing Disaster Recovery
                 Train Signal, Inc.
                 Mel Haynes




Backup types: Whole or partial?
• Differential backup
  A differential backup is based on the latest full
  backup of the data
   – Differential base: A full backup
   – Differential backup: The changed data
   – Can speed up the backup process
   – Restore the full backup first, then the most
     recent differential backup




                                                                 34
                SQL Server 2008 Implementing Disaster Recovery
                Train Signal, Inc.
                Mel Haynes




•Show screen with backup
 types.
•If I do that then delete the slide
 that lists the four types.




                SQL Server 2008 Implementing Disaster Recovery
                Train Signal, Inc.
                Mel Haynes




Backup types:

•Full Database Backups
•Full File Backups
•Partial Backups
•Differential Backups




                SQL Server 2008 Implementing Disaster Recovery
                Train Signal, Inc.
                Mel Haynes




Backup types:

•Full Database Backups




                                                                 35
                SQL Server 2008 Implementing Disaster Recovery
                Train Signal, Inc.
                Mel Haynes




Backup types:

•Full File Backups




                SQL Server 2008 Implementing Disaster Recovery
                Train Signal, Inc.
                Mel Haynes




Backup types:

•Partial Backups




                SQL Server 2008 Implementing Disaster Recovery
                Train Signal, Inc.
                Mel Haynes




Backup types:

•Differential Backups




                                                                 36
               SQL Server 2008 Implementing Disaster Recovery
                Train Signal, Inc.
                Mel Haynes




Recovery Requirements

•How important is it to never
 lose a change?
•How easy would it be to re-
 create lost data?




               SQL Server 2008 Implementing Disaster Recovery
                Train Signal, Inc.
                Mel Haynes




Staffing Considerations

•Does your organization use
 system or database
 administrators?




               SQL Server 2008 Implementing Disaster Recovery
                Train Signal, Inc.
                Mel Haynes




Data Usage Patterns
•Frequently changing data?
•Are some tables modified more
 frequently?
•Critical production periods?
•Risky updates or errors?




                                                                37
                 SQL Server 2008 Implementing Disaster Recovery
                 Train Signal, Inc.
                 Mel Haynes




Maintenance Plan Wizard

•Helps you set up core
 maintenance tasks
•Creates SQL Server Agent jobs




                 SQL Server 2008 Implementing Disaster Recovery
                 Train Signal, Inc.
                 Mel Haynes




Best Practices

•Carefully consider your
 business needs
•Test your backups
•Off site storage
•Secure location



                 SQL Server 2008 Implementing Disaster Recovery
                 Train Signal, Inc.
                 Mel Haynes




System databases
master – Back up after creating or
         modifying any database
         objects
model – Determines default for
         all new databases
msdb – Use the full recovery
         model
tempdb – Can not backup




                                                                  38
                 SQL Server 2008 Implementing Disaster Recovery
                  Train Signal, Inc.
                  Mel Haynes




  Other databases

 Replication databases:
 publisher, distribution, subscriber
 – Use the full recovery model




                 SQL Server 2008 Implementing Disaster Recovery
                  Train Signal, Inc.
                  Mel Haynes




  What we covered …

•Database Recovery Models
•SQL Server 2008 Backup Types
•Make backup strategy decisions
•Recovering a SQL Server
  Database




                 SQL Server 2008 Implementing Disaster Recovery
                  Train Signal, Inc.
                  Mel Haynes




   Terms
1. Recovery model
   Determines what’s available to backup
2. Backup type
   Determines what’s is in a backup
3. Full backup
   The entire database
4. Differential or partial backup
   What’s changed since the last backup
5. File backup
   Backs up specific database files
6. Log backup
   Backs up the log file




                                                                  39
             SQL Server 2008 Implementing Disaster Recovery
              Train Signal, Inc.
              Mel Haynes




You should know how to …
• Perform a full back up of your SQL Server
  database
• Perform differential backups of your SQL
  Server database
• Perform backups of key files and table
• Restore (ie roll back) a SQL Server
  database to a specific point in time
• Create automated maintenance plans




              Welcome to Train Signal
              Train Signal, Inc.
              Mel Haynes




             You’re Watching

          Video 8
      SQL Server 2008
       Performance




              SQL Server 2008 Performance
              Train Signal, Inc.
              Mel Haynes



Overview

• SQL Profiler / SQL Trace (T-SQL)
• Windows System Monitor
• Activity Monitor
• Transact-SQL
• Windows Logs
• Default Trace
• Dynamic Management Views



                                                              40
                   Train Signal, Inc.
                   Mel Haynes



SQL Profiler / SQL Trace (T-SQL)
• Shows how SQL Server resolves queries
  internally
• Allows administrators to see Transact-
  SQL statements and how the server
  accesses the return result sets
• You can:
   –   Create a trace that is based on a reusable template
   –   Watch the trace results as the trace runs
   –   Store the trace results in a table or file
   –   Replay the trace results




                   Train Signal, Inc.
                   Mel Haynes



SQL Profiler / SQL Trace (T-SQL)

• Trend analysis
• Replaying captured events
• Ad hoc monitoring
• Graphical interface
• Using within custom
  application




                   Train Signal, Inc.
                   Mel Haynes



Windows System Monitor

• Tracks resource usage
• System Monitor a.k.a.
  Performance Monitor, Reliability &…
• Collects counts and rates
• You can generate operator alerts
• SQL Server Profiler monitors Database
  Engine events…
• but System Monitor monitors resource
  usage associated with server processes




                                                             41
                   Train Signal, Inc.
                   Mel Haynes



    Windows System Monitor


    • Trend analysis
    • Generating alerts
    • Graphical interface




                   Train Signal, Inc.
                   Mel Haynes



    Activity Monitor

Section
    1. Overview - Graphical display section
    2. Processes- Shows user activities
    3. Resource Waits - Wait state
        information
    4. Data File I/O - Data and log files I/O
        information
    5. Recent Expensive Queries - Most
        expensive queries




                   Train Signal, Inc.
                   Mel Haynes



    Activity Monitor


    • Ad hoc monitoring
    • Graphical interface




                                                42
                Train Signal, Inc.
                Mel Haynes



Transact-SQL

• sp_who
• sp_lock
• sp_spaceused
• sp_monitor




                Train Signal, Inc.
                Mel Haynes



Transact-SQL


• Ad hoc monitoring
• Using within custom
  application




                Train Signal, Inc.
                Mel Haynes



Windows Logs



               • SQL Server Services
               • Audits
               • Errors




                                       43
                   Train Signal, Inc.
                   Mel Haynes



   Windows Logs

   • Ad hoc monitoring
   • Graphical interface




                   Train Signal, Inc.
                   Mel Haynes



   Default Trace

   The SQL Server “black box”

exec sp_configure ‘default trace enabled’, ‘0’
reconfigure with override

                       ‘show advanced options’, ‘1’




                   Train Signal, Inc.
                   Mel Haynes



   Dynamic Management Views

   • Under System Views folder
   • Sys.dm_…
   • Views to underlying data
   • Provides stable interface




                                                      44
                       Train Signal, Inc.
                       Mel Haynes



 Monitoring Tools Comparison

                            SQL    Windows
                                           Activity Transact-   Error
   Event or activity      Server   System
                                           Monitor     SQL      logs
                          Profiler Monitor


Trend analysis                Yes           Yes
Replaying captured
                              Yes
events
Ad hoc monitoring             Yes                 Yes   Yes     Yes

Generating alerts                           Yes

Graphical interface           Yes           Yes   Yes           Yes

Using within custom
                              Yes                       Yes
application




                       Monitoring SQL Server 2008 Performance
                       Train Signal, Inc.
                       Mel Haynes




 What we covered…

 •SQL Server 2008 monitoring
    tools




                       Monitoring SQL Server 2008 Performance
                       Train Signal, Inc.
                       Mel Haynes



 Terms
 • SQL Profiler / SQL Trace (T-SQL)
 • Windows System Monitor
 • Activity Monitor
 • Transact-SQL
 • Windows Logs
 • Default Trace
 • Dynamic Management Views



                                                                        45
              Monitoring SQL Server 2008 Performance
              Train Signal, Inc.
              Mel Haynes



You should know how to …

• Perform ad hoc performance
  checks of a SQL Server database
• Determine baseline system
  performance
• Track performance trends
• Identify areas where changes are
  needed to increase performance




              Welcome to Train Signal
              Train Signal, Inc.
              Mel Haynes




             You’re Watching

      Video 9
    Automating
 Administrative Tasks




              Automating Administrative Tasks
              Train Signal, Inc.
              Mel Haynes




Overview
•SQL Server Agent
•Security
•Tools to use
•Implementing Jobs
•Monitoring and responding to
 events




                                                       46
                  Automating Administrative Tasks
                  Train Signal, Inc.
                  Mel Haynes




SQL Server Agent

•A Windows service
•Executes scheduled
 administrative tasks
•Triggered by:
 Schedules, events, user
 requests




                  Automating Administrative Tasks
                  Train Signal, Inc.
                  Mel Haynes




The big picture
1. Identify administrative tasks
2. Define a set of jobs, schedules,
   alerts, and operators
3. Run the SQL Server Agent jobs
   you have defined




                  Automating Administrative Tasks
                  Train Signal, Inc.
                  Mel Haynes




Configuration

•SQL Server
 Management Studio
•msdb database




                                                    47
              Automating Administrative Tasks
              Train Signal, Inc.
              Mel Haynes




The SQL Server Agent account
• In SQL Server:
  – Member of sysadmin role
• In Windows grant permission to:
  – Act as part of the operating system
  – Adjust memory quotas for a process
  – Bypass traverse checking
  – Log on as a batch job
  – Log on as a service
  – Replace a process level token




              Automating Administrative Tasks
              Train Signal, Inc.
              Mel Haynes




The players

•Role                              •Roles
•Subsystem                         •Subsystems
•Proxy                              Proxies




              Automating Administrative Tasks
              Train Signal, Inc.
              Mel Haynes




              Roles



                                                 48
                  Automating Administrative Tasks
                  Train Signal, Inc.
                  Mel Haynes




   Security
 • Database roles:                         View and execute
                                           jobs they own
 −SQLAgentUserRole                         Create job steps
 −SQLAgentReaderRole                       that run as an
                                           existing proxy
 −SQLAgentOperatorRole                     account


 Search http://msdn.microsoft.com for
“SQL Server Agent Fixed Database Roles”




                  Automating Administrative Tasks
                  Train Signal, Inc.
                  Mel Haynes




   Security

                               Create, modify, and delete proxy
 • Server role:                accounts
 –sysadmin                     Create job steps that do not
                               specify a proxy




                  Automating Administrative Tasks
                  Train Signal, Inc.
                  Mel Haynes




        Subsystem
  A set of functionality available
       to SQL Server Agent




                                                                  49
                             Automating Administrative Tasks
                             Train Signal, Inc.
                             Mel Haynes


Subsystem name                           Description
Microsoft ActiveX Script                 ActiveX scripting job step
Operating System (CmdExec)               Executable program
PowerShell                               PowerShell scripting job step
Replication Distributor                  Activates the replication Distribution Agent

Replication Merge                        Activate the replication Merge Agent

Replication Queue Reader                 Activate the replication Queue Reader Agent

Replication Snapshot                     Activate the replication Snapshot Agent

Replication Transaction Log Reader Activate the replication Log Reader Agent
Analysis Services Command                Analysis Services command
Analysis Services Query                  Analysis Services query
SSIS package execution                   SSIS package




                             Automating Administrative Tasks
                             Train Signal, Inc.
                             Mel Haynes




                           Proxy



                             Automating Administrative Tasks
                             Train Signal, Inc.
                             Mel Haynes




       Proxy
       • Job steps that execute Transact-SQL
         do not use SQL Server Agent proxies.
       • A SQL Server Agent proxy defines the
         security context for a job step.
       • A proxy provides SQL Server Agent
         with access to the security
         credentials for a Microsoft Windows
         user.




                                                                                        50
                  Automating Administrative Tasks
                  Train Signal, Inc.
                  Mel Haynes




 Creating a job

 •New Job…
 •Create the job and steps
 •Use T-SQL to execute the task
 •Create a schedule for the job




                  Automating Administrative Tasks
                  Train Signal, Inc.
                  Mel Haynes




 Security guidelines


•Create dedicated user accounts
•Only grant what’s necessary
•Do not use a Windows
 Administrators group account




                  Automating Administrative Tasks
                  Train Signal, Inc.
                  Mel Haynes




 Terms
 •Job – A series of operations
  performed sequentially by SQL
  Server Agent
 •Operator – Aliases for people or
  groups that can receive electronic
  notification when jobs have
  completed or alerts have been
  raised




                                                    51
              Automating Administrative Tasks
              Train Signal, Inc.
              Mel Haynes




Terms
• Subsystem – A predefined object that
  represents a set of functionality
  available to a SQL Server Agent proxy
• Proxy – Provides SQL Server Agent with
  access to the security credentials for a
  Microsoft Windows user
• Credential – The authentication
  information required to connect to a
  resource outside SQL Server




              Automating Administrative Tasks
              Train Signal, Inc.
              Mel Haynes




What we covered…

•SQL Server Agent
•msdb and key fixed database
 roles
•Subsystems
•Proxies



              Automating Administrative Tasks
              Train Signal, Inc.
              Mel Haynes




You should know how to…
•Assigning a system account to
 SQL Server Agent
•Grant privileges to the SQL Server
 Agent account
•Creating secure users accounts to
 perform backups
•Creating SQL Server Agent jobs,
 job tasks, and schedules




                                                52
                  Welcome to Train Signal
                  Train Signal, Inc.
                  Mel Haynes




                You’re Watching


         Video 10
      High Availability




                  SQL Server 2008 High Availability
                  Train Signal, Inc.
                  Mel Haynes




The Landscape

•Failover clustering
•Database mirroring
•Log shipping
•Replication




                  SQL Server 2008 High Availability
                  Train Signal, Inc.
                  Mel Haynes




SQL Server Failover Clustering
• Built on top of Windows Server failover clusters
• Supports an entire instance of SQL Server
• One or more "nodes", or servers, and two or more shared
  disks
• Appears as a single SQL Server instance
• An application can connect by referencing the failover
  cluster instance name
• A non-disk hardware failure, operating system failure, or
  planned operating system upgrade
• A failover cluster does not protect against disk failure
• SQL Server Enterprise, SQL Server Developer, SQL Server
  Standard




                                                              53
               SQL Server 2008 High Availability
               Train Signal, Inc.
               Mel Haynes




SQL Server Database Mirroring
•A software solution
•Almost instantaneous failover
•A single standby database
• Principal server
 and mirror server




               SQL Server 2008 High Availability
               Train Signal, Inc.
               Mel Haynes




SQL Server Log Shipping
• One or more warm standby databases
• Updates secondary database at set intervals
  from log backups of the primary database
• User-modifiable delay between creating a
  log backup and restores
• You must manually applying any unrestored
  log backups before a failover can occur
• Log shipping can support multiple standby
 databases




               SQL Server 2008 High Availability
               Train Signal, Inc.
               Mel Haynes




SQL Server Replication
• Publisher-subscriber model
• Can distribute data to one or more
  secondary servers, or Subscribers
• Supports data filtering and
  partitioning
• Real-time availability
• Subscribers are online and available




                                                   54
                              SQL Server 2008 High Availability
                              Train Signal, Inc.
                              Mel Haynes




     Comparisons
                                                                 Log
                                     Clustering    Mirroring                Replication
                                                               Shipping
Automatic switch over                      Yes       Yes                       Yes
> 1 "secondary"                            Yes                     Yes         Yes
Manual switch over                                   Yes            Yes
Editions available                       E, D, S      E           E, S, W       All
Can improve performance                    Yes                                 Yes
Database level                                       Yes           Yes         Yes
Server level                               Yes
Hardware failure protection                           Yes          Yes         Yes
Distributed Data                                                   Yes         Yes
Live “secondary”                           Yes                                 Yes




                              SQL Server 2008 High Availability
                              Train Signal, Inc.
                              Mel Haynes




     Terms

     •Nodes – Servers in a SQL Server
      cluster
     •Instance – A copy of the SQL
      Server




                              SQL Server 2008 High Availability
                              Train Signal, Inc.
                              Mel Haynes




     What we covered…



         4 high availability options




                                                                                          55
              SQL Server 2008 High Availability
              Train Signal, Inc.
              Mel Haynes




You should know how to…

•Define what is SQL Server High
 Availability
•Compare the various solutions




              Welcome to Train Signal
              Train Signal, Inc.
              Mel Haynes




            You’re Watching


            Video 11
           Replication




              SQL Server 2008 Replication
              Train Signal, Inc.
              Mel Haynes




Overview
•What’s the problem to solve?
•What is SQL Replication?
•SQL Server Replication
 Concepts
•Setting up Replication




                                                  56
                   SQL Server 2008 Replication
                   Train Signal, Inc.
                   Mel Haynes
    In the beginning…




  UÉ|áx                                          c{ÉxÇ|å
                       V{|vtzÉ

 ftÇ ]âtÇ                                          atÑxÄá



                   SQL Server 2008 Replication
                   Train Signal, Inc.
                   Mel Haynes




    What’s the problem to solve?




                   SQL Server 2008 Replication
                   Train Signal, Inc.
                   Mel Haynes

In the beginning…

Boise                                   Phoenix




San Juan                                Naples




                                                            57
                     SQL Server 2008 Replication
                     Train Signal, Inc.
                     Mel Haynes




What is
SQL Replication?




                     SQL Server 2008 Replication
                     Train Signal, Inc.
                     Mel Haynes




The Solution:

Replication                                     • San Juan
                                     Eastern
                                                • Naples

                                           North
                                          Central   • Boise
                      Chicago


                                    Western     • Phoenix




                     SQL Server 2008 Replication
                     Train Signal, Inc.
                     Mel Haynes




What is SQL Server Replication?


    Distributing data and
    database objects
    Data redundancy
    Faster access




                                                              58
                  SQL Server 2008 Replication
                  Train Signal, Inc.
                  Mel Haynes




    SQL Server Replication
    Concepts




SQL Server 2008
  Replication



Server
  to
Server


SQL Server 2008
  Replication



Server
  to
Client


                                                59
                                      SQL Server 2008 Replication
                                      Train Signal, Inc.
                                      Mel Haynes




 What gets sent
Where and When?



                                      SQL Server 2008 Replication
                                      Train Signal, Inc.
                                      Mel Haynes




   Types of Replication




                 • Server-to-server               • Distributed                • Provides initial
                 • Offloading                       applications                 data
                   batch                          • Mobile                     • Complete
 Transactional




                   processing                       applications                 refresh
                 • Incremental                    • Point of sale              • Smaller data
                 • One way                        • Two-way                      sets
                                                                    Snapshot
                                         Merge




                                      SQL Server 2008 Replication
                                      Train Signal, Inc.
                                      Mel Haynes




  The SQL Server Roles

  • Publisher
  • Distributor
  • Subscriber




                                                                                                    60
                             SQL Server 2008 Replication
                             Train Signal, Inc.
                             Mel Haynes




   Publisher
    The data source




                          Source                  Publication




                             SQL Server 2008 Replication
                             Train Signal, Inc.
                             Mel Haynes




   Distributor
   •Stores replication data
   •Local Distributor vs
      Remote Distributor


Publisher                        Distributor            Publication
               Publication




                             SQL Server 2008 Replication
                             Train Signal, Inc.
                             Mel Haynes




   Subscribers
   • A database instance that receives
     replicated data




                                                        Publication
            Publication




                                                                      61
                SQL Server 2008 Replication
                Train Signal, Inc.
                Mel Haynes




The Pieces                           Subscription
  Publication                                 Western


        Article
        Article                               Central

        Article
        Article
                                              Eastern




                SQL Server 2008 Replication
                Train Signal, Inc.
                Mel Haynes




I’ll take a years subscription to the Times please…
• Article – Data and objects that
  will be shared or replicated
• Publication – A group of articles
  just like a newspaper or magazine
• Subscription – The plan for who
  gets what publication and when




                SQL Server 2008 Replication
                Train Signal, Inc.
                Mel Haynes




Article

• Tables
• Views
• Jobs
• Stored Procedures
• Plus other objects



                                                        62
                          SQL Server 2008 Replication
                          Train Signal, Inc.
                          Mel Haynes




 The Players
 •SQL Server Agent
     – The Boss
 •Snapshot Agent
     – It is used in all forms of replication
 •Log Reader Agent
    – Used in transactional replication
    – Extracts and sends transactions from publisher's log
 •Distribution Agent
    – Dispatcher
 •Merge Agent
    – Used Merge Replication




                          SQL Server 2008 Replication
                          Train Signal, Inc.
                          Mel Haynes




 Replication User Accounts
Agent               Location               Account name
Snapshot
                    Publisher              <machine_name>\repl_snapshot
Agent
Log Reader
                    Publisher              <machine_name>\repl_logreader
Agent
                    Publisher
Distribution
                    and        <machine_name>\repl_distribution
Agent
                    Subscriber
            Publisher
Merge Agent and        <machine_name>\repl_merge
            Subscriber




                          SQL Server 2008 Replication
                          Train Signal, Inc.
                          Mel Haynes




 Publication

 • A collection of articles from one database
 • Management mechanism




                                                                           63
                         SQL Server 2008 Replication
                         Train Signal, Inc.
                         Mel Haynes




Subscription

• A request for a copy of a publication
• Defines what publication
• Where - When – How often
• Two flavors: push and pull




                         SQL Server 2008 Replication
                         Train Signal, Inc.
                         Mel Haynes




Best Practices
• Replication needs should be clearly defined
• Place subscribers on separate computers




                         SQL Server 2008 Replication
                         Train Signal, Inc.
                         Mel Haynes




Best Practices

• Carefully plan your backup strategy
• Regularly validate replicated data




                                                       64
                      SQL Server 2008 Replication
                      Train Signal, Inc.
                      Mel Haynes




  What we covered …

•Benefits of replication
•Setting up replication systems accounts
•Configured transactional replication
•Server roles
•Types of replication
•SQL Server agents involved in replication




                      SQL Server 2008 Replication
                      Train Signal, Inc.
                      Mel Haynes




  Terms
1. Publisher
   Source of the replicated data
2. Distributor
   Handles distribution of the data.
   Can be on a separate computer.
3. Subscriber
   The recipient of the replicated data




                      SQL Server 2008 Replication
                      Train Signal, Inc.
                      Mel Haynes




  Terms
4. Article
   Data and objects that will be shared
   or replicated
5. Publication
   A group of articles just like a
   newspaper or magazine
6. Subscription
   The plan for who gets what
   publication and when




                                                    65
                        SQL Server 2008 Replication
                        Train Signal, Inc.
                        Mel Haynes



You should know how to …

• Create and configure system replication accounts
• Configure replication folder
• Configure publisher
• Create a publication
• Create a subscription




                        Welcome to Train Signal
                        Train Signal, Inc.
                        Mel Haynes




                      You’re Watching


         Video 12
     Resource Governor




                        Resource Governor
                        Train Signal, Inc.
                        Mel Haynes




Overview

• Troubleshooting SQL Server?
  Where to start?
• Some of the tools to use
• Managing database utilization with
  Resource Governor




                                                      66
                      Resource Governor
                      Train Signal, Inc.
                      Mel Haynes




Where do you start?

• Review configuration settings and
  setup
• Determine the general problem area




                      Resource Governor
                      Train Signal, Inc.
                      Mel Haynes




Some of the tools to use

• Windows Event Viewer
• SQL Profiler / SQL Trace (T-SQL)
• Windows System Monitor
• Activity Monitor
• Transact-SQL
• Windows Logs
• Default Trace
• Dynamic Management Views
• Database Engine Tuning Advisor




                      Resource Governor
                      Train Signal, Inc.
                      Mel Haynes




Managing Resource Utilization


• Demand for database services
  will continue to increase
• You are always on a path
  towards disaster
• The best DBAs may go
  completely unnoticed




                                           67
                        Resource Governor
                        Train Signal, Inc.
                        Mel Haynes




Resource Governor



                                    Application
       Application



                                                               Application
                     Application




                                                      Application

  Application




                        Resource Governor
                        Train Signal, Inc.
                        Mel Haynes




Key Concepts

• Resource pools
   Pools of system resources are defined and then a percentage of those
   resources is allocated to the pool.
    – Used to minimize the effect of “run away” processes
    – Groups system resources and access levels
    – Presently only CPU time and memory


            1
       Pool 2                         Pool 3


     10% CPU
    100%CPU                       50% CPU
    10% RAM
    100%RAM                       50% RAM




                        Resource Governor
                        Train Signal, Inc.
                        Mel Haynes




Creating a Resource Pool


CREATE RESOURCE POOL pool_name
[ WITH
([
     MIN_CPU_PERCENT = value ][[,]
     MAX_CPU_PERCENT = value ][[,]
     MIN_MEMORY_PERCENT = value ][[,]
     MAX_MEMORY_PERCENT = value ]
)];




                                                                             68
                             Resource Governor
                             Train Signal, Inc.
                             Mel Haynes




      Key Concepts

      • Workload groups
        Workload groups server as containers for processes running against the DB.
        These containers or groups are mapped to specific resource pools.



                                                     Adhoc
       Reporting                                     Queries

                             Production
                             Applications                     Administration
                                                                Activities
       SQL Server
       Data Engine




                             Resource Governor
                             Train Signal, Inc.
                             Mel Haynes




      Creating a Workload Group


      CREATE WORKLOAD GROUP group_name
      [ WITH
      ([
           IMPORTANCE = { LOW|MEDIUM|HIGH } ][[,]
           REQUEST_MAX_MEMORY_GRANT_PERCENT = value ][[,]
           REQUEST_MAX_CPU_TIME_SEC = value ][[,]
           REQUEST_MEMORY_GRANT_TIMEOUT_SEC = value ][[,]
           MAX_DOP = value ][[,]
           GROUP_MAX_REQUESTS = value ]
      )][
      USING { pool_name | "default" }
      ];




                             Resource Governor
                             Train Signal, Inc.
                             Mel Haynes




      Key Concepts

      • Classification
        Resource Governor supports using a user-defined classifier function for
        implementing classification rules.



                                                      Adhoc
SQL Query
SQL Query   Management                                Queries
             Reporting
                                  Production                     Administration
                                  Applications                     Activities



                     1             Resource Pools         2               3




                                                                                     69
                       Resource Governor
                       Train Signal, Inc.
                       Mel Haynes




Creating a Classifier Function


• Can classify incoming requests based on
   various criteria:
    –   Login
    –   Database name
    –   Host name
    –   Application name
    –   Connection properties such as IP address or protocol




                       Resource Governor
                       Train Signal, Inc.
                       Mel Haynes




Creating a Classifier Function


CREATE FUNCTION dbo.ClassifierFunctionName()
RETURNS SYSNAME WITH SCHEMABINDING
AS
   BEGIN
   RETURN (SELECT CASE SUSER_SNAME()
           WHEN ‘ProductionProc’ THEN ‘HighPriorityGrp’
           WHEN ‘ReportingProc’ THEN ‘MidPriorityGrp’
           ELSE ‘LowPriorityGrp’
           END );
   END
GO




                       Resource Governor
                       Train Signal, Inc.
                       Mel Haynes




Resource Governor

• Classify incoming connections and route their
   workloads to a specific group
• Monitor resource usage for each workload in
   a group
• Pool resources and set pool-specific limits on
   CPU usage and memory allocation
• Associate grouped workloads with a specific
   pool of resources
• Identify and set priorities for workloads




                                                               70
                   Resource Governor
                   Train Signal, Inc.
                   Mel Haynes




Miscellaneous

•To change the Resource Governor configuration, a user
 requires CONTROL SERVER permission (sysadmin role)

•To view the active configuration, a user requires VIEW
 SERVER STATE permission (serveradmin role)

 Enable:
            ALTER RESOURCE GOVERNOR RECONFIGURE

 Disable:
            ALTER RESOURCE GOVERNOR DISABLE




                   Resource Governor
                   Train Signal, Inc.
                   Mel Haynes




Terms

• Resource pool – Subsets of system
    resources that can be may available to
    applications
•   Workload group – A mechanism for
    grouping in coming database requests
•   Classifier function – Functions in T-SQL
    used to map or “classify” in coming request
    into workload groups




                   Resource Governor
                   Train Signal, Inc.
                   Mel Haynes




What we covered…

• Plan, plan, plan
  “An ounce of prevention…”
• Using Resource Governor to manage
  resource utilization




                                                          71
                   Resource Governor
                   Train Signal, Inc.
                   Mel Haynes




You should know how to…

• Manage CPU and RAM usage for SQL
  Server application requests
• Use Resource Governor to:
   – Create resource pools
   – Create workload groups
   – Utilize classification functions




                   Welcome to Train Signal
                   Train Signal, Inc.
                   Mel Haynes




                  You’re Watching


         Video 13
     Performing Data
    Management Tasks




                   Performing Data Management Tasks
                   Train Signal, Inc.
                   Mel Haynes




Overview

•Importing and Exporting Data
•Maintaining Indexes




                                                      72
               Performing Data Management Tasks
               Train Signal, Inc.
               Mel Haynes




Importing and Exporting

•SQL Server
•Flat files
•Microsoft Office Access
•Microsoft Office Excel




               Performing Data Management Tasks
               Train Signal, Inc.
               Mel Haynes




Permissions for Importing and Exporting
• Connect to the source and destination
• Read from the source database and write to
  the destination
• Permissions sufficient to create a new
  database and table or file.
   – CREATE DATABASE or CREATE TABLE
     permissions
• To write to the msdb database or to the file
  system.




               Performing Data Management Tasks
               Train Signal, Inc.
               Mel Haynes




Maintaining Indexes

•Index types
•Partitioned indexes
•Disabling and enabling indexes
•Rebuilding indexes
•Full-text indexing



                                                  73
                  Performing Data Management Tasks
                  Train Signal, Inc.
                  Mel Haynes




Index type              Description
Clustered               Sorts and stores the data rows.

Nonclustered            Uses a key value and a row
                        locator pointer.
Full-text               Supports word searches in
                        character string data.
Spatial                 Supports searching against the
                        geometry data type.
XML                     Supports searching against the
                        xml data type.




                  Performing Data Management Tasks
                  Train Signal, Inc.
                  Mel Haynes




              Clustered vs Nonclustered




                  Performing Data Management Tasks
                  Train Signal, Inc.
                  Mel Haynes




              Clustered vs Nonclustered
• Clustered
   – Sort and store the data rows in the table or view
   – There can be only one clustered index per table
   – The only time the data rows in a table are stored in
     sorted order is when the table contains a clustered
     index
   – The table is called a clustered table

• Nonclustered
   – Have a structure separate from the data rows
   – Contain the key values and pointers to the data row
     that contains the key value




                                                            74
                Performing Data Management Tasks
                Train Signal, Inc.
                Mel Haynes




Full-text indexing




                Performing Data Management Tasks
                Train Signal, Inc.
                Mel Haynes




Full-text indexing

•Always on in SQL Server 2008
 databases
•Auto on / off at the table level




                Performing Data Management Tasks
                Train Signal, Inc.
                Mel Haynes




What we covered…

•Index types
•Clustered vs nonclustered index
•Full-text index
•SQL Server export and import




                                                   75
              Performing Data Management Tasks
              Train Signal, Inc.
              Mel Haynes




Terms
• Clustered index – Ordered data
• Nonclustered index – Keys and
  pointers
• Full-text index – Index of text strings
• Catalogues – “Dictionary” of terms
• Partition – Segregating data for
  storage as smaller subsets




              Performing Data Management Tasks
              Train Signal, Inc.
              Mel Haynes




You should know how to…
• Export / import data from SQL server
• Import data from other data formats
• Create and manage indexes:
  –Clustered
  –Nonclustered
  –Unique
  –Full-text




              Welcome to Train Signal
              Train Signal, Inc.
              Mel Haynes




            You’re Watching


       Video 14
   Data Compression




                                                 76
               Data Compression
               Train Signal, Inc.
               Mel Haynes




Overview

•ROW compression
•PAGE compression
•Compressing Backups




               Data Compression
               Train Signal, Inc.
               Mel Haynes




ROW compression

Stores fixed length data in
variable length storage format

           C    A       R L
           A    B       R A H A M
           P    E       T E R
           A    L
           T    I      M
           S    A      N T I A G O




               Data Compression
               Train Signal, Inc.
               Mel Haynes




ROW compression

Stores fixed length data in
variable length storage format

           C     A      R L
           A     B      R A H A M
           P     E      T E R
           A     L
           T     I     M
           S     A     N T I A G O




                                     77
                Data Compression
                Train Signal, Inc.
                Mel Haynes




Page compression

Minimizing the data redundancy
                       FirstName
                          John
                          John
                          John
                          John
                          John
                          John
                          John
                            ….




                Data Compression
                Train Signal, Inc.
                Mel Haynes




What can you compress?

•Table
•Index
•Partition




                Data Compression
                Train Signal, Inc.
                Mel Haynes




Data partitions
• Use a partition function to segregate table data
  along arbitrary boundaries

• Convert a nonpartitioned table to a partitioned
 table.

• Convert a partitioned table to a nonpartitioned
 table.

• Transfer data by adding, moving, or removing
 partitions




                                                     78
               Data Compression
               Train Signal, Inc.
               Mel Haynes




Table compression via T-SQL
Row compression
CREATE TABLE T1(c1 int,c2 nvarchar(50))
WITH (DATA_COMPRESSION = ROW);
Page compression
CREATE TABLE T2(c1 int,c2 nvarchar(50))
WITH (DATA_COMPRESSION = PAGE);




               Data Compression
               Train Signal, Inc.
               Mel Haynes




    Compressing Backups




               Data Compression
               Train Signal, Inc.
               Mel Haynes




Compressing backups



What will it cost me?




                                          79
                   Data Compression
                  Train Signal, Inc.
                  Mel Haynes




Compressing Backups
• Windows I/O performance counters
• Device Throughput Bytes/sec counter
   – SQLServer:Backup Device object

• Backup/Restore Throughput/sec counter
   – SQLServer:Databases object

• Use Resource Governor




                   Data Compression
                  Train Signal, Inc.
                  Mel Haynes




Compression Ratio
Table: msdb.backupset table
Columns: backup_size, compressed_backup_size

  SELECT
  backup_size/compressed_backup_size
  FROM msdb..backupset;

• Factors:
   –   Data type
   –   The consistency of the data among rows on a page
   –   Encrypted data compresses significantly less
   –   Is the database compressed




                   Data Compression
                  Train Signal, Inc.
                  Mel Haynes




What we covered…

•Compressing SQL Server
 objects
•Compression types
•Backup compression




                                                          80
              Data Compression
              Train Signal, Inc.
              Mel Haynes




Terms

•Page compression
 Looks for data redundancy

•Row compression
 Looks for unused space
 Fixed       Variable length




              Data Compression
              Train Signal, Inc.
              Mel Haynes




You should know how to…

•Compress SQL Server backups
•Compress SQL Server objects
  –Tables
  –Indexes
  –Partitions




              Welcome to Train Signal
              Train Signal, Inc.
              Mel Haynes




            You’re Watching


      Video 15
   SQL Server 2008
 Common Commands




                                        81
               SQL Server 2008 Common Commands
               Train Signal, Inc.
               Mel Haynes




Overview

•DBCC
•sp_configure




               SQL Server 2008 Common Commands
               Train Signal, Inc.
               Mel Haynes




Database Console Commands (DBCC)
Maintenance Maintenance tasks on a database,
              index, or filegroup.
Miscellaneous Miscellaneous tasks such as enabling
              trace flags or removing a DLL from
              memory.
Informational Tasks that gather and display various
              types of information.
Validation    Validation operations on a database,
              table, index, catalog, filegroup, or
              allocation of database pages.




               SQL Server 2008 Common Commands
               Train Signal, Inc.
               Mel Haynes




DBCC CHECKTABLE ([dbo.Flowers])
DBCC CHECKDB




                                                      82
               SQL Server 2008 Common Commands
               Train Signal, Inc.
               Mel Haynes




Setting Server Configuration Options: sp_configure

Management Studio
             vs
                                    sp_configure
SELECT *
FROM sys.configurations
ORDER BY name ; GO




               SQL Server 2008 Common Commands
               Train Signal, Inc.
               Mel Haynes




sp_configure




USE master;
GO
sp_configure 'show
advanced option', 1;




               SQL Server 2008 Common Commands
               Train Signal, Inc.
               Mel Haynes




sp_configure
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'fill factor', 100;
GO
RECONFIGURE;
GO




                                                     83
               SQL Server 2008 Common Commands
               Train Signal, Inc.
               Mel Haynes




sp_configure
• Run either
 RECONFIGURE or
 RECONFIGURE WITH OVERRIDE
 after setting a configuration option

• RECONFIGURE WITH OVERRIDE
 works for all configuration options but
 should

• RECONFIGURE executes within a transaction




               SQL Server 2008 Common Commands
               Train Signal, Inc.
               Mel Haynes




What we covered…

• DBCC
  Database Console Commands
• sp_configure




               SQL Server 2008 Common Commands
               Train Signal, Inc.
               Mel Haynes




You should know how to…

•Use DBCC to perform
 administrative task

•Use sp_configure to perform
 SQL Server configuration




                                                 84
                Welcome to Train Signal
                Train Signal, Inc.
                Mel Haynes




               You’re Watching

           Video 16
        SQL Server 2008
         Database Mail




                SQL Server 2008 Database Mail
                Train Signal, Inc.
                Mel Haynes




   Overview

   •Database Mail
   •SMTP




The players…
•Stored procedures
•Mail objects
•Queues
•MSDB
•Databasemail90.exe
•SMTP mail server




                                                85
               SQL Server 2008 Database Mail
               Train Signal, Inc.
               Mel Haynes




Simple Mail Transfer Protocol (SMTP) Server

•A service provided by IIS
•Delivers outgoing e-mail




               SQL Server 2008 Database Mail
               Train Signal, Inc.
               Mel Haynes




•Database Mail Profiles
•Database Mail Accounts




               SQL Server 2008 Database Mail
               Train Signal, Inc.
               Mel Haynes




Database Mail Profiles
• A collection of Database Mail accounts
• Allow reconfigure stored procedures and
  database applications that use e-mail
• Can be configured with one set of e-mail
 accounts during application
 development and testing, and then be
 updated with a different set of accounts
 when the application moves to
 production




                                               86
                     SQL Server 2008 Database Mail
                     Train Signal, Inc.
                     Mel Haynes




    Database Mail Profiles
    Development                              Production




Development Accounts                      Production Accounts




                     SQL Server 2008 Database Mail
                     Train Signal, Inc.
                     Mel Haynes




    Public vs Private profiles
    • Public profiles
       –Available to DatabaseMailUserRole
        fixed msdb database role
    • Private profiles
       –Available to sysadmin fixed server
        role
       –Must be granted to any others




                     SQL Server 2008 Database Mail
                     Train Signal, Inc.
                     Mel Haynes




    Database Mail Accounts

    •Contains the information used
     to send e-mail
    •One e-mail server per account




                                                                87
              SQL Server 2008 Database Mail
              Train Signal, Inc.
              Mel Haynes




Database Mail SMTP authentication
                           • Uses the credentials of the
   Windows                   SQL Server Database Engine
 Authentication:             Windows service account.


     Basic                 • Uses the username and
                             password specified.
 Authentication:


  Anonymous                • The SMTP server does not
 Authentication:             require any authentication.




              SQL Server 2008 Database Mail
              Train Signal, Inc.
              Mel Haynes




What we covered…

• Configuring an SMTP server
• Configuring a Database Mail
  account
• Configuring a Database Mail
  profile
• Database Mail authentication


              SQL Server 2008 Database Mail
              Train Signal, Inc.
              Mel Haynes




Terms
•Database Mail profiles
 – Containers for accounts
•Database Mail accounts
 – Connection and communication
•SMTP
 – Simple Mail Transfer Protocol
•IIS
  – Internet Information Server




                                                           88
                      SQL Server 2008 Database Mail
                      Train Signal, Inc.
                      Mel Haynes




You should know how to…

•Create SMTP server
•Create Database Mail profiles
•Create Database Mail accounts




                      Welcome to Train Signal
                      Train Signal, Inc.
                      Mel Haynes




                    You’re Watching

            Video 17
        SQL Server 2008
        Certification 101




                      Certification 101
                      Train Signal, Inc.
                      Mel Haynes




The Certification Hierarchy

                           Microsoft
                            Certified
                          Master (MCM)
                     Microsoft Certified
                      IT Professional
                          (MCITP)

                 Microsoft Certified
             Technology Specialist (MCTS)




                                                      89
                      Certification 101
                      Train Signal, Inc.
                      Mel Haynes




     Database                                             Business Intelligence
                              Database Developer
   Administrator                                               Developer




                                       Microsoft
                                    Certified Master
                                         (MCM)

                                Microsoft Certified IT
                                Professional (MCITP)

                        Microsoft Certified Technology
                              Specialist (MCTS)




                      Certification 101
                      Train Signal, Inc.
                      Mel Haynes




Microsoft Certified Technology Specialist (MCTS)

     Database                                             Business Intelligence
                              Database Developer
   Administrator                                               Developer

• Exam 70-432               • Exam 70-433                 • Exam 70-448
  Implementation              Database                      Business
  and Maintenance             Development                   Intelligence
                                                            Development and
                                                            Maintenance

                                       Microsoft
                                    Certified Master
                                         (MCM)

                                Microsoft Certified IT
                                Professional (MCITP)

                        Microsoft Certified Technology
                              Specialist (MCTS)




                      Certification 101
                      Train Signal, Inc.
                      Mel Haynes




Microsoft Certified IT Professional (MCITP)

     Database                                             Business Intelligence
                              Database Developer
   Administrator                                               Developer
• Exam 70-432               • Exam 70-433                 • Exam 70-448
• Exam 70-450               • Exam 70-451                 • Exam 70-452
  Designing,                  Designing Database            Designing a
  Optimizing and              Solutions and Data            Business
  Maintaining a               Access                        Intelligence
  Database Server                                           Infrastructure
  Infrastructure                        Microsoft
                                     Certified Master
                                          (MCM)
                                 Microsoft Certified IT
                                 Professional (MCITP)

                         Microsoft Certified Technology
                               Specialist (MCTS)




                                                                                  90
                         Certification 101
                         Train Signal, Inc.
                         Mel Haynes




Microsoft Certified IT Professional (MCITP) -- SQL 2005 Upgrade

      Database                                                Business Intelligence
                                 Database Developer
    Administrator                                                  Developer

• Exam 70-453                  • Exam 70-454                  • Exam 70-455
  Transition Your                Transition Your                Transition Your
  MCITP SQL Server               MCITP SQL Server               MCITP SQL Server
  2005 DBA to MCITP              2005 Database                  2005 Business
  SQL Server 2008                Developer to MCITP             Intelligence
  DBA                            SQL Server 2008                Developer to MCITP
                                 Database Developer             SQL Server 2008
                                          Microsoft
                                       Certified Master         Business
                                            (MCM)               Intelligence
                                                                Developer

                                              MCITP

                           Microsoft Certified Technology
                                 Specialist (MCTS)




                         Certification 101
                         Train Signal, Inc.
                         Mel Haynes




Microsoft Certified Master (MCM)


     Database                                               Database
   Administrator +                                          Developer
MCITP and Microsoft Certified Master certification training program and
successfully complete three weekly exams and a qualifying lab exam.

                                          Microsoft
                                       Certified Master
                                            (MCM)

                                   Microsoft Certified IT
                                   Professional (MCITP)

                           Microsoft Certified Technology
                                 Specialist (MCTS)




                         Certification 101
                         Train Signal, Inc.
                         Mel Haynes




      Database                                                Business Intelligence
                                 Database Developer
    Administrator                                                  Developer

• MCTS                         • MCTS                         • MCTS
• MCITP                        • MCITP                        • MCITP
• MCM                          • MCM




                                                                                      91

								
To top