Enter Slide Title Here

Document Sample
Enter Slide Title Here Powered By Docstoc
					                             IT-004

                           SQL Server
                           Management
                             Tools &
David Cutten
dcutten@commandalkon.com
205-879-3282 ext 1715
                           Maintenance
                                                              INTRODUCTION

                                                     IT-004


          SQL Server Management
           Tools and Maintenance

Instructor:                                              David Cutten

Course Length:                                           2 Hours


IT-004   SQL Server Management Tools & Maintenance
                                                                             2
                                                     OVERVIEW

OVERVIEW
•   Maintenance, Performance and Tuning
     – What are they?
     – Benefits of Performance Tuning.

•   Microsoft SQL Tool Overview
     – Query Analyzer
     – Microsoft SQL Management Studio
     – SQL 2005 Profiler

•   Microsoft SQL Management Studio
     – Introduction to SQL Management Studio




IT-004   SQL Server Management Tools & Maintenance
                                                           3
                                                     OVERVIEW

•   Windows Performance Tuning
    – Server roles
    – Hardware
    – General Windows Tuning
    – Memory management
    – Hard Drives and the Database
    – Disk Defrag

•   Event Logs
     – Windows Server Logs
     – SQL Logs

•   SQL Activity and Error Logs
     – Alert Logs
     – Setting Alert Events
     – Getting Alert Events

IT-004   SQL Server Management Tools & Maintenance
                                                           4
                                                     OVERVIEW
•   T-SQL
     – What is Transact SQL?
     – Using T-SQL to Identify Problems
     – Using T-SQL to Resolve Problems

•   Database Emails and Alerts
     – Database Mail Configuration Wizard
     – Defining the Operator
     – SQL Server Agent
     – Create Alerts

•   SQL Triggers

•   Performance Monitor
     – Using Windows Performance Monitor

•   SQL Profiler

IT-004   SQL Server Management Tools & Maintenance
                                                           5
                                                     OVERVIEW

•   SQL Backup
     – Failure Types
     – Backup Choices
     – Recovery Models
     – Backing up Database
•   Maintenance Plans for Optimization
     – Building a Maintenance Plan
        • Full Maintenance Plans
        • Plans for Optimization
        • Backups as a part of a Maintenance Plan
•   Restoring Data
•   Keeping it all Up to Date
     – Windows Update
     – Service Packs
     – Software Updates



IT-004   SQL Server Management Tools & Maintenance
                                                           6
                           Maintenance,
                           Performance
                            and Tuning


David Cutten
dcutten@commandalkon.com
205-879-3282 ext 1715
                         Maintenance, Performance and Tuning

• What Are they?




main·te·nance:
             1. The act of maintaining or the state of being maintained.
             2. The work of keeping something in proper condition; upkeep.
             3.       a. Provision of support or livelihood
                      b. Means of support or livelihood
             4. Law The unlawful meddling in a suit by providing either party
             with the means to carry it on.




IT-004   SQL Server Management Tools & Maintenance
                                                                                8
                         Maintenance, Performance and Tuning

• What Are They?
  Maintenance tasks are those items that need to be
  performed on Daily, Weekly and Monthly Basis

             Maintenance Tasks:
             1. Daily Tasks
                   •    Daily Backup


             1. Weekly Tasks
                   •    Full Weekly Backup


             2. Monthly Tasks
                   •    Check for Windows Updates
                   •    Check for patches for SQL



IT-004   SQL Server Management Tools & Maintenance
                                                          9
                         Maintenance, Performance and Tuning
 • What Are they?




 per·for·mance:
              1.   The act of performing or the state of being performed.
              2.   The act or style of performing a work or role before an audience.
              3.   The way in which someone or something functions
              4.   A presentation, especially a theatrical one, before an audience.
              5.   Something performed; an accomplishment.
              6.   Linguistics One's actual use of language in actual situations.




IT-004   SQL Server Management Tools & Maintenance
                                                                                       10
                         Maintenance, Performance and Tuning

• What Are they?

Performance Tasks are items that need to be run from
  time to time to maintain performance of your SQL
  Server.
             Performance Tasks:
             1. Windows Performance Tasks
                   •    Disk Defrag


             1. SQL Server Performance Task
                   •    Statistics Updates
                   •    Index Rebuilds




IT-004   SQL Server Management Tools & Maintenance
                                                          11
                         Maintenance, Performance and Tuning

• What Are they?




tun·ing:
             1. a. Music To put into proper pitch: tuned the violin.
                b. Archaic To utter musically; sing.
             2. To adopt or adjust, especially in order to bring into harmony.
             3. a. To adjust (a receiver) to a desired frequency.
                b. To adjust (a circuit) so as to make it resonant with a given input
                      signal.
             4. To adjust for maximum usability or performance.




IT-004   SQL Server Management Tools & Maintenance
                                                                                        12
                         Maintenance, Performance and Tuning

• What Are they?

Tuning Tasks are items that need to be run
  proactively to identify any potential issues.

             Tuning Tasks:
                   •    System Monitoring
                   •    SQL Reporting
                   •    Error Logs
                   •    Event Logs
                   •    Alerts




IT-004   SQL Server Management Tools & Maintenance
                                                          13
                               Benefits of Performance and Tuning


               1.Decrease in Problems
               2.Less Down Time
               3.Better Performing Database
               4.Faster Data Access
               5.Happier End Users
               6.Happier IT Support!

IT-004   SQL Server Management Tools & Maintenance
                                                               14
                           Microsoft SQL
                                Tool
                             Overview


David Cutten
dcutten@commandalkon.com
205-879-3282 ext 1715
                                                     SQL Querry Analyzer




IT-004   SQL Server Management Tools & Maintenance
                                                                      16
                                                     SQL Management Studio




IT-004   SQL Server Management Tools & Maintenance
                                                                        17
                                                     SQL Profiler




IT-004   SQL Server Management Tools & Maintenance
                                                               18
                           Microsoft SQL
                           Management
                               Studio

David Cutten
dcutten@commandalkon.com
205-879-3282 ext 1715
                                                     SQL Management Studio

• Microsoft SQL Server 2005 and 2008
• An integrated environment for accessing, configuring, managing,
  administering, and developing all components of SQL Server.
• Combines a broad group of graphical tools with a number of rich script
  editors to provide access to SQL Server to developers and
  administrators of all skill levels.
• Combines the features of Enterprise Manager, Query Analyzer, and
  Analysis Manager, included in previous releases of SQL Server, into a
  single environment.
• Works with all components of SQL Server such as Reporting Services,
  Integration Services, SQL Server Compact Edition, and Notification
  Services.
• Database administrators get a single comprehensive utility that
  combines easy-to-use graphical tools with rich scripting capabilities.



IT-004   SQL Server Management Tools & Maintenance
                                                                        20
                                              Integrated Management Environment

                                                                          Template
                                                                           Explorer
                                                                         and Solution
                                                                           Explorer




 Object Registered
        Explorer
         Servers




                                  Summary
                                    Pane



IT-004   SQL Server Management Tools & Maintenance
                                                                                 21
                          Server
                         Dashboard



IT-004   SQL Server Management Tools & Maintenance
                                                     22
                            Microsoft
                             Windows
                           Performance
                              Tuning

David Cutten
dcutten@commandalkon.com
205-879-3282 ext 1715
                                                     Server Roles


             Dedicated Database Server
             • Server is dedicated to SQL Service Only
                   –This is the best performing server


             Shared Application Servers
             •This Server has Multiple Applications
                   –Fine for Small Database Server




IT-004   SQL Server Management Tools & Maintenance
                                                               24
                                                     Server Roles


             It is Strongly Recommended that you
             only run your Command Series Database
             on a Dedicated Server.

             Running other applications could:

             •Cause Memory Problems
             •Excessive Paging
             •Fragmentation Issues

IT-004   SQL Server Management Tools & Maintenance
                                                               25
                                                     Hardware Tips

             1. Ensure that all the hardware and the related drivers
                have been tested with Windows OS.
             2. Install Windows Server as a stand-alone server.
             3. Defragment the drives or arrays regularly using the
                built-in Disk Defragmenter or 3rd Party Defrag Program.
             4. Format all the disk arrays on your server using NTFS.
             5. Avoid using NTFS data file encryption (EFS) and
                compression on SQL Server database and log files.
             6. Dedicate your SQL Server's to a physical server of their
                own.
             7. For best performance of SQL Server running under
                Windows Server, turn off any operating system services
                that aren't needed.
             8. Remove any unnecessary network protocols from your
                server.

IT-004   SQL Server Management Tools & Maintenance
                                                                       26
                                                     System Storage System
             Types of Storage Systems
             • Raid 1
                   • Mirroring
                   • Requires a Minimum of 2 Drives
                   • Excellent for System Partitions
             • Raid 5
                   • Stripping with Parity
                   • Requires a Minimum of 3 Drives
                   • Excellent for Data Storage
             • Raid 1+0
                   • Very Fast
                   • Requires a Minimum of 4 Drives
                   • Very Expensive
             • SAN Storage
                   • Usually used in Large Operation
                   • Very Large Storage Capacity
                   • Excellent Data Protection
IT-004   SQL Server Management Tools & Maintenance
                                                                        27
                                                     Raid 5 Server


                                                           SYSTEM




                                                           DATA1


                     RAID 5 DISKS
   SETUP EXAMPLE:
             SYSTEM = C:\
             DATA1 = D:\         SQL DATA                   DATA2
             DATA2= L:\         SQL LOG FILES
IT-004   SQL Server Management Tools & Maintenance                  28
                                 Raid 1 System, Raid 5 Data Server


                                                              SYSTEM


                                               RAID 1 DISKS



                                                              DATA1

                                        RAID 5 DISKS
SETUP EXAMPLE:
          SYSTEM = C:\
          DATA1 = D:\           SQL DATA
                                                               DATA2
          DATA2= L:\            SQL LOG FILES
IT-004   SQL Server Management Tools & Maintenance
                                                                       29
                                                     Windows Settings

             For the Maximum Performance
             Set the Performance options:

             Processor Scheduling:
             Background Services

             Memory Usage:
             System Cache

             Both will dedicate the maximum
             Resources to the SQL Server




IT-004   SQL Server Management Tools & Maintenance
                                                                   30
                                                     System Page File


             You should have a Page File
             That is at least 2X the Size
             of your System Memory.

             It is Recommended that you
             also have Page Files on
             Multiple Drives

             It is also best if you can
             place these files on a non
             Raid drive(s) if possible. They
             do not require Fault Tolerance.



IT-004   SQL Server Management Tools & Maintenance
                                                                   31
                                                                     Services

           • In most Application Servers, There are services that are not used
             or needed.

           •Many of these can be turned off to create more resources for the
            server and make available to the Application.

           • Before turning off any Service, verify that the service is not
             needed in your environment.

           •The following list assumes the server is running SQL server
            exclusively.

           Here is a list of recommend services to modify:




IT-004   SQL Server Management Tools & Maintenance
                                                                              32
                                                                            Services
Service Default                                   Startup Type   Recommended Setting
Application Management                            Manual         Disabled
Alerter                                           Automatic      Disabled
Clipbook                                          Disabled       Disabled
Computer Browser                                  Automatic      Disabled
Distributed file system                           Automatic      Disabled
Distributed link tracking client                  Automatic      Disabled
Distributed transaction coordinator               Automatic      Manual
Error Reporting Service                           Automatic      Disabled
Fax Service                                       Manual         Disabled
File Replication                                  Manual         Disabled
Help and Support                                  Automatic      Disabled
HTTP SSL                                          Manual         Disabled
License Logging                                   Manual         Disabled
Logical Disk Manager                              Automatic      Manual
Messenger                                         Automatic      Disabled
Portable Media Serial Num Service                 Manual         Disabled
Shell Hardware Detection                          Automatic      Disabled
Windows Audio                                     Automatic      Disabled
Wireless Configuration                            Automatic      Disabled
 IT-004   SQL Server Management Tools & Maintenance
                                                                                  33
                                                                                                         Services

   Application Management
   Description:
   Is used to provide software installation services such as Assign, Publish and Remove. It handles deployment of software
   for computers joined to a domain.

   Alerter
   Description:
   Makes it possible to automatically sent messages to registered users, about certain system events or alerts when they
   happen. The registered users needs to have the Messenger service started to receive the messages.

   Clipbook
   Description:
   Is used to access the machine's clipboard remotely using the NetDDE service.
   It is an old service from the NT 3.X days.
   Note Acrobat Reader has a menu option Window -> "Show Clipboard", which starts the Clipboard Viewer in Windows
   (Can be installed through Add/Remove Programs in Control Panel). Windows Clipboard Viewer uses this service and will
   fail to operate if it is disabled.

   Computer Browser
   Description:
   Enables the computer to participate in the election for maintaining the browser list.




IT-004   SQL Server Management Tools & Maintenance
                                                                                                                           34
                                                                                                            Services
   Distributed File System
   Description:
   Distributed File System (DFS) manages logical volumes distributed across a local or wide area network. It maintains a
   large catalog of all the files with their physical location. The catalog is usually replicated on multiple machines and being
   synchronized using File Replication Service. The catalog enables clients to requests a file using a logical name, without
   needing to specify the exact machine that stores the actual file.

   Distributed Link Tracking Client
   Description:
   Maintains shortcuts and Object Linking and Embedding (OLE) links to target files

   It performs these services by monitoring activity on the NTFS volumes and store maintenance information in a file called
   Tracking.log, which is placed in the root folder "System Volume Information".

   Distributed Transaction Coordinator
   Description:
   Distributed Transaction Coordinator (MSDTC) coordinates transactions that are distributed across two or more databases,
   message queues, file systems, or other transaction protected resource managers.
   The service listens on TCP port 3372 along with a ephemeral TCP port (Above 1023).

   Error Reporting Service
   Description:
   The Error Reporting Service provides an infrastructure for collecting, storing and reporting kernel mode, operating system
   and application faults to Microsoft.


IT-004   SQL Server Management Tools & Maintenance
                                                                                                                              35
                                                                                                           Services

   FAX
   Description:
   Helps you to send & receive faxes.
   This service gets installed if fax capable modem is installed in your machine.

   Directory Replicator / File Replication Service
   Description:
   Directory Replicator or File Replication Service (FRS) provides control replication / synchronization of directories and
   files among multiple servers. It is used by the Distributed File System (DFS) to automatically synchronize file catalogs. It
   is used by the Active Directory to automatically synchronize Domain Controllers.

   Help and Support Service
   Description:
   The service supports the Help and Support client application and enables requests from the client application to
   Microsoft's Help and Support Center.

   HTTP SSL / SSL for HTTP.SYS
   Description:
   This service implements the secure hypertext transfer protocol (HTTPS) for the HTTP service, using the Secure Socket
   Layer (SSL). SSL is a proposed open standard for establishing a secure communications channel to prevent the
   interception of critical information, such as credit card numbers.




IT-004   SQL Server Management Tools & Maintenance
                                                                                                                             36
                                                                                                        Services

  License Logging Service
  Description:
  License Logging Service provides license tracking on a server or Domain Controller (DC).

  It will post a warning in the Application Event Log with Event ID 221, if there are more users accessing the machine than
  there are licenses.

  Logical Disk Manager
  Description:
  Logical Disk Manager Watchdog Service that detects the appearance/disappearance of hard drives and the partitions they
  contains.

  Messenger
  Description:
  Is used to send/show messages and alerts on the local machine or to remote machines.

  It has lately been used by spammers to send popup messages with advertising to internet users who doesn't have this service
  disabled or haven't enabled a firewall.

  WMDM PMSP Service / Portable Media Serial Number
  Description:
  The service supports the Secure Digital Music Initiative(SDMI) and enables the WMDM (Windows Media Device
  Manager) to retrieve the serial number from portable music devices using Pre-Message Security Protocol(PMSP), so media
  content can be copied securely to the device.
IT-004   SQL Server Management Tools & Maintenance
                                                                                                                           37
                                                                                                      Services

  Shell Hardware Detection
  Description:
  Supports Autoplay which is launched when inserting a USB storage device, CD or other removable storage (Like scanner or
  camera), and gives you the option to select what application to use with the device. Autoplay is an extension of AutoRun.

  Windows Audio
  Description:
  Manages audio devices for Windows-based programs.

  Wireless Zero Configuration (WZC)
  Description:
  Provides automatic configuration for the 802.11 adapters.




IT-004   SQL Server Management Tools & Maintenance
                                                                                                                        38
                            Windows
                           Event Logs

David Cutten
dcutten@commandalkon.com
205-879-3282 ext 1715
                                                                               Event Logs

         Window Security Event Log



                          Windows System Event Log


                                               Windows Application Event Log




IT-004   SQL Server Management Tools & Maintenance
                                                                                       40
                           SQL Activity and
                             Error Logs

David Cutten
dcutten@commandalkon.com
205-879-3282 ext 1715
                                                     Monitoring


• Supports multiple logs
     – SQL Server Database
       Engine Information
     – SQL Server Agent
       Information
     – Windows Event Viewer
       Application Log
     – Database Mail Logs
• Across all logs, allows:
     – Searching
     – Filtering
     – Exporting

IT-004   SQL Server Management Tools & Maintenance
                                                            42
                           T-SQL


David Cutten
dcutten@commandalkon.com
205-879-3282 ext 1715
                                                                 Transact-SQL


                          What is T-SQL?
• Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary
  extension to the SQL language. Microsoft's implementation
  ships in the Microsoft SQL Server product.
• In order to make it more powerful, SQL has been enhanced
  with additional features such as:
         – Control-of-flow language
         – Local variables
         – Various support functions for string processing, date processing,
           mathematics, etc.
         – Improvements to DELETE and UPDATE statements




IT-004    SQL Server Management Tools & Maintenance
                                                                               44
                                                       Transact-SQL


             • Many of the Functions of the Management Studio can be
               Scripted using the SQL query language.

             • Scripts can be written and scheduled for regular tasks.

             • The database can be monitored using T-SQL commands

             • Regular maintenance can be completed using T-SQL.

             • Just about every function in Microsoft SQL can be
             completed via a SQL Command.



IT-004   SQL Server Management Tools & Maintenance
                                                                         45
                           Microsoft
                           Windows
                           System
                           Monitor

David Cutten
dcutten@commandalkon.com
205-879-3282 ext 1715
                                                      System Monitor



         The tool of choice for diagnosing the cause of a
          server’s performance is the System Monitor.

               Formally known as Performance Monitor.

You can access the System Monitor by either selecting
  Performance from Windows’ Administrative Tools
menu or by entering the PERFMON.MSC command at
                  the Run prompt.
IT-004    SQL Server Management Tools & Maintenance
                                                                  47
                                                     System Monitor




IT-004   SQL Server Management Tools & Maintenance
                                                                 48
                                                     Add Counters


    You should add key counters to monitor you server
                and your SQL Application.

Suggested Counter to Start with:
Memory: Pages/sec
Memory: Available Bytes
Physical Disk: % Disk time
Physical Disk: Avg. Disk Queue Length
Processor: % Processor Time
System: Processor Queue Length
SQL Server Buffer: Buffer Cache Hit Ratio
SQL Server General: User Connections


IT-004   SQL Server Management Tools & Maintenance
                                                               49
                                                     Creating a Baseline

                                  Build a Baseline
Ideally, you should use System Monitor to create a log
        of key counters for a period of 24 hours.

You will want to select a "typical" 24 hour period when
  it comes to deciding when to create your System
                      Monitor log.

         For example, pick a typical business day, not a
                      weekend or holiday.


IT-004   SQL Server Management Tools & Maintenance
                                                                      50
                                                           Creating a Baseline


Counter Name                             Average     Minimum      Maximum

 Memory: Pages/sec

 Memory: Available Bytes

 Physical Disk: % Disk time


 Physical Disk: Avg. Disk Queue Length



 Processor: % Processor Time



 System: Processor Queue Length


SQL Server Buffer: Buffer Cache Hit
Ratio


 SQL Server General: User Connections




IT-004   SQL Server Management Tools & Maintenance
                                                                            51
                                                     Memory:Pages/Sec

    This counter measures the number of pages per
  second that are paged out of RAM to disk, or paged
                 into RAM from disk.

 The more paging that occurs, the more I/O overhead
 your server experiences, which in turn can decrease
          the performance of SQL Server.

 Your goal is to try to keep paging to a minimum, not
                      to eliminate it.

IT-004   SQL Server Management Tools & Maintenance
                                                                   52
                                                     Memory:Pages/Sec

     Assuming that SQL Server is the only major
  application running on your server, then this figure
                should ideally average
                 between zero and 20.

 You will most likely see spikes much greater than 20,
                   which is normal.

                    The key here is keeping the average
                      pages per second less than 20.

IT-004   SQL Server Management Tools & Maintenance
                                                                   53
                                                     Memory: Available Bytes



  Another way to check to see if your SQL Server has
    enough physical RAM is to check the Memory
           Object: Available Bytes counter.

               This value should be greater than 5MB.

    If not, then your SQL Server needs more physical
                         RAM.


IT-004   SQL Server Management Tools & Maintenance
                                                                          54
                                                     Physical Disk: % Disk Time

         This counter measures how busy a physical array is.

     It provides a good relative measure of how busy your
                           arrays are.

  As a rule of thumb, the % Disk Time counter should run
                       less than 55%.

 If this counter exceeds 55% for continuous periods (over
 10 minutes or so during your 24 hour monitoring period),
     then your SQL Server may be experiencing an I/O
                         bottleneck.
IT-004   SQL Server Management Tools & Maintenance
                                                                             55
                        Physical Disk: Avg. Disk Queue Length

 If it exceeds 2 for continuous periods (over 10 minutes or
 so during your 24 hour monitoring period) for each disk
drive in an array, then you may have an I/O bottleneck for
                          that array.

 This can happen occasionally in your 24 hour monitoring
                         period.

 If it happens often, then start looking into finding ways to
         increase the I/O performance on the server.


IT-004   SQL Server Management Tools & Maintenance
                                                            56
                                                Processor: % Processor Time


    The Processor Object: % Processor Time counter, is
   available for each CPU, and measures the utilization of
                    each individual CPU.

     This same counter is also available for all of the CPUs
                             (total).

         This is the key counter to watch for CPU utilization.



IT-004   SQL Server Management Tools & Maintenance
                                                                         57
                                                Processor: % Processor Time

If the % Total Processor Time (total) counter exceeds 80%
for continuous periods (over 10 minutes or so during your
  24 hour monitoring period), then you may have a CPU
                       bottleneck.

     If these busy periods only occur occasionally, and you
            think you can live with them, that's OK.

If they occur often, you may want to consider reducing the
    load on the server, getting faster CPUs, getting more
   CPUs, or getting CPUs that have a larger on-board L2
                            cache.
IT-004   SQL Server Management Tools & Maintenance
                                                                         58
                                     System: Processor Queue Length

 If this exceeds 2 per CPU for continuous periods (over 10
  minutes or so during your 24 hour monitoring period),
          then you probably have a CPU bottleneck.

    For example, if you have 4 CPUs in your server, the
 Processor Queue Length should not exceed a total of 8 for
                     the entire server.

    If the Processor Queue Length regularly exceeds the
  recommended maximum, but the CPU utilization is not
  correspondingly as high (which is typical), then consider
      reducing the SQL Server "max worker threads"
                    configuration setting.
IT-004   SQL Server Management Tools & Maintenance
                                                                 59
                SQL Server Buffer: Buffer Cache Hit Ratio


  This counter indicates how often SQL Server goes to the
           buffer, not the hard disk, to get data.

   This ratio should exceed 90%, and ideally be over 99%.

 If your buffer cache hit ratio is lower than 90%, you need
            to go out and buy more RAM today.

   If the ratio is between 90% and 99%, then you should
  seriously consider purchasing more RAM, as the closer
 you get to 99%, the faster your SQL Server will perform.
IT-004   SQL Server Management Tools & Maintenance
                                                            60
                        SQL Server General: User Connections


    Since the number of users using SQL Server affects its
    performance, you may want to keep an eye on the SQL
      Server General Statistics Object: User Connections
                           counter.

      This shows the number of user connections, not the
     number of users, that currently are connected to SQL
                            Server.




IT-004   SQL Server Management Tools & Maintenance
                                                             61
                        SQL Server General: User Connections


  If this counter exceeds 255, then you may want to boost
the SQL Server configuration setting, "Maximum Worker
Threads" to a figure higher than the default setting of 255.

     If the number of connections exceeds the number of
   available worker threads, then SQL Server will begin to
     share worker threads, which can hurt performance.

   The setting for "Maximum Worker Threads" should be
   higher than the maximum number of user connections
                   your server ever reaches.
IT-004   SQL Server Management Tools & Maintenance
                                                             62
                                                     Other Counters


             The are many other counters available for
                           monitoring.

         Refer to the books online for a complete list of
                  available counters for Microsoft SQL.




IT-004   SQL Server Management Tools & Maintenance
                                                                 63
                           Database
                            Emails

                             and

David Cutten
                            Alerts
dcutten@commandalkon.com
205-879-3282 ext 1715
                                 Database Mail Configuration Wizard
• The Database Mail Configuration Wizard provides a
  convenient way to manage Database Mail
  configuration objects and enables Database Mail, if
  needed.
• Three Step required to Setup:
         – 1 Database Mail Configuration Wizard
         – 2 Operator Setup
         – 3 Database Mail setup for SQL Agent Alerts




IT-004    SQL Server Management Tools & Maintenance
                                                                 65
                                  Database Mail Configuration Wizard
• To start the Database Mail Configuration Wizard
  From Object Explorer, connect to an instance of SQL
  Server.
• Expand Management, right-click Database Mail,
  and select Configure Database Mail.




IT-004   SQL Server Management Tools & Maintenance
                                                                 66
                                Database Mail Configuration Wizard

• Choose the Set up Database Mail option to set up
  Database for the first time.




IT-004   SQL Server Management Tools & Maintenance
                                                                67
                                Database Mail Configuration Wizard




IT-004   SQL Server Management Tools & Maintenance
                                                                68
                                                      Defining Operators

• The SQL Server Agent service supports the
  notification of administrators through operators.
  Operators are aliases for people or groups that can
  receive electronic notification when jobs have
  completed or alerts have been raised. The primary
  attributes of an operator are:
         – Operator name
           Contact information

• It is recommended that you define operators before
  you define alerts.




IT-004    SQL Server Management Tools & Maintenance
                                                                      69
                                                      Defining Operators

• In the Object Explorer, expand the 'SQL Server
  Agent' option
         – Right click on the 'Operators' folder
         – Select the 'New Operator' option




IT-004    SQL Server Management Tools & Maintenance
                                                                      70
                                                      SQL Server Agent

• In the Object Explorer, right click on the 'SQL Server
  Agent' option
         – Select the 'Properties' option
         – Select the 'Alert System' tab




IT-004    SQL Server Management Tools & Maintenance
                                                                    71
                                                     Create SQL Alerts

• To configure SQL Server alerts:
         –   Open the SQL Server Management Studio
         –   In the Object Explorer, open the 'SQL Server Agent' option
         –   Right click on the 'Alerts' folder
         –   Select the 'New Alert' option




IT-004   SQL Server Management Tools & Maintenance
                                                                          72
                                                     Create SQL Alerts

• Response Tab – Select the operators to notify




IT-004   SQL Server Management Tools & Maintenance
                                                                    73
                                                     Create SQL Alerts

• Options Tab – Indicate the options when the text
  will be included and the delayed response parameter




IT-004   SQL Server Management Tools & Maintenance
                                                                    74
                               SQL

                            TRIGGER

                           Introduction

David Cutten
dcutten@commandalkon.com
205-879-3282 ext 1715
                                                                   SQL Triggers
• Introducing to Database Trigger
          Triggers are a kind of stored procedure which are invoked
          automatically by database server when predefined events
          occur.
          Triggers are useful for use in the database but it is
          suggested that they should be used only when necessary.
          The triggers can put the burden of interdependency on the
          database and also the burden of maintenance.


                                   Due to the way Command Series software
                                   interacts with the SQL Database, any future
                                   upgrades could potentially overwrite or delete any
                                   existing triggers placed on our Database. If you
                                   use triggers with our Database, make sure you
                                   have backups of the scripts.

IT-004   SQL Server Management Tools & Maintenance
                                                                                        76
                           SQL Profiler


David Cutten
dcutten@commandalkon.com
205-879-3282 ext 1715
                                                         Using SQL Server Profiler

• SQL Server Profiler shows how SQL Server resolves queries internally.
  This allows administrators to see exactly what Transact-SQL
  statements or Multi-Dimensional Expressions are submitted to the
  server and how the server accesses the database to return result
    sets.


• Using SQL Server Profiler, you can do the following:
         –    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
         –    Start, stop, pause, and modify the trace results as necessary
         –    Replay the trace results




IT-004       SQL Server Management Tools & Maintenance
                                                                                78
                                                     Using SQL Server Profiler

• Use SQL Server Profiler to monitor only the events in which you are
  interested.

• If traces are becoming too large, you can filter them based on the
  information you want, so that only a subset of the event data is
  collected.

• Monitoring too many events adds overhead to the server and the
  monitoring process, and can cause the trace file or trace table to grow
  very large, especially when the monitoring process takes place over a
  long period of time.




IT-004   SQL Server Management Tools & Maintenance
                                                                            79
                                                      Using SQL Server Profiler

         Creating A Trace:

         Enter Trace Name

         Select Template

         Check the Save to File Box
         If you need to keep a file

         Browse for the path of the
         File

         If the Template has what you are looking for,
         Click the Run Button

IT-004    SQL Server Management Tools & Maintenance
                                                                             80
                                                      Using SQL Server Profiler

         Profiler will record all
         Transactions that occur
         On the Database.

         You can stop the recording
         Anytime by pressing
         The stop button

         You can also rerun traces
         Previously recorded simply
         By clicking FILE and OPEN
         And Select TRACE FILE.




IT-004    SQL Server Management Tools & Maintenance
                                                                             81
                                                      Using SQL Server Profiler

         If you want to Monitor
         A Specific item that a
         Template does not cover,
         You can create your own
         Set of events.

         Select BLANK for the
         Template and Click on
         The Event Selection
         Tab

         There are many events that can be monitored. Check the box of the
         Event and Action you would like to monitor.



IT-004    SQL Server Management Tools & Maintenance
                                                                             82
                           SQL Backup


David Cutten
dcutten@commandalkon.com
205-879-3282 ext 1715
                                                     What is a Backup?


• Microsoft, in SQL Server Books Online, defines
  backups as:
  A copy of data that is used to restore and recover
  data after a system failure
• SQL Backups can be created a number of ways and
  can incorporate all or some of the data, as well as
  some part of the transaction log. We will focuse on
  2005 syntax, most of the concepts are applicable to
  2000.




IT-004   SQL Server Management Tools & Maintenance
                                                                    84
                                                     SQL Backup/Restore



                                                     Database


                                restore                         backup




             .bak file                                                   .bak file
             .trn file                                                   .trn file




IT-004   SQL Server Management Tools & Maintenance
                                                                                     85
                                                Why are backups needed?

• A well-planned backup and restore strategy helps protect
  databases against data loss caused by a variety of failures.

• To recover data from disastrous loss

• To create safeguards for recovery options during migrations
  and upgrades

• To create replicated or archived systems

• So your IT staff can sleep at night                   zzzzzzzzzzzzzzzz




          "Backups? We don‘t NEED no steenkin' backups!"
                                                     Famous Last Words of an ex-DBA
IT-004   SQL Server Management Tools & Maintenance
                                                                                      86
                                                     Types of Failures


          •   Media failure
          •   Failure of the hard disk drive
          •   Failure of the hard disk controller
          •   Motherboard failure
          •   Power outage or spike Virus attack
          •   Accidental change or deletion of data
          •   Malicious change or deletion of data
          •   Natural disasters.




IT-004   SQL Server Management Tools & Maintenance
                                                                    87
                                                     Backups




  Additionally, backups of a database
          are useful for routine
    administrative purposes, such as
      copying a database from one
      server to another, setting up
   database mirroring, and archiving.

IT-004   SQL Server Management Tools & Maintenance
                                                          88
                                             When are backups needed?

• When to backup is a Business Decision, not an IT
  Decision.
• IT staff can setup and perform backups, What and
  When to backup should be a business decision.
• Things that influince this are:
         – How long can we afford to be down
         – How much data can we afford to lose
         – How much time will it take to rebuild in the event of a
           disasster.

         All these decision will affect When and How you backup.




IT-004    SQL Server Management Tools & Maintenance
                                                                     89
                                                      Backup Choices

• Full database backups: The entire database is
  backed up
• Transaction log backups: Add all the changes in the
  transaction log to your full database backups
• Differential database backups: Back up only data
  that has changed since the last full backup
         – For example, if a person’s bank account changed 10 times
           in one day, the transaction log backup would contain all 10
           changes but the differential backup would contain just the
           final amount
• Copy Only backups: Can be used in between backup
  cycles. Does not set any Archive bits.

IT-004    SQL Server Management Tools & Maintenance
                                                                         90
                             SQL Database Backup Modes



• Three Recovery Model
      – Full recovery
      – Bulk-logged recovery
      – Simple recovery



• You can set the mode by using the Options tab of
  the database property sheet



IT-004   SQL Server Management Tools & Maintenance
                                                     91
                                                     Recovery Models


Full

• Log is only truncated when a transaction log
  backup is performed
• Not truncated when full backup is performed
• Will fill disk unless limited. If reached, no
  new transactions are allowed
• Recovery
         – Can restore to any point in time
         – Best option for data integrity in combination with
           Transaction log backups

IT-004   SQL Server Management Tools & Maintenance
                                                                  92
                                   Recovery Models


Bulk Logged

• Similar to Full but some operations are not
  recorded
      – bcp, INSERT ... SELECT * FROM
        OPENROWSET(BULK...), and BULK INSERT
      – WRITETEXT and UPDATETEXT
      – SELECT INTO
      – CREATE INDEX
      – ALTER INDEX REBUILD or DBCC DBREINDEX
      – DROP INDEX
IT-004 SQL Server Management Tools & Maintenance
                                                   93
                                                      Recovery Models


Simple

•   After a ‘checkpoint’ log is truncated
•   Smallest log files
•   Cannot backup logs
•   Recovery
         – Must restore from last full backup




IT-004    SQL Server Management Tools & Maintenance
                                                                   94
                                                     Backing Up Databases

• Highlight the target database. Open the Backup
  dialog box by right-clicking and choosing Tasks
  >Back Up

• Use the default setup to do a simple backup

• Click OK to start the backup

• After the backup completes, click OK on the
  Confirmation screen to close the Backup dialog box


IT-004   SQL Server Management Tools & Maintenance
                                                                       95
                                                     Backing Up Databases
• Backup a Database from T-SQL.

Simple Database Backup:
BACKUP DATABASE AdventureWorks
  TO DISK = 'C:\Backups\AdventureWorks.BAK‘
Differential Database Backup:
BACKUP DATABASE AdventureWorks
  TO DISK = 'C:\backups\AdventureWorks.bak'
  WITH DIFFERENTIAL;
Copy Only Database Backup:
BACKUP DATABASE AdventureWorks
  TO DISK = 'C:\Backups\AdventureWorks.bak'
  WITH COPY_ONLY;
IT-004   SQL Server Management Tools & Maintenance
                                                                       96
                                                     Scheduling Backups



• After you decide what types of backups you require
  and how frequently you have to perform each type,
  we recommend that you schedule regular backups
  as part of a database maintenance plan for the
  database.




IT-004   SQL Server Management Tools & Maintenance
                                                                     97
                            Creating a
                           Maintenance
                               Plan



David Cutten
dcutten@commandalkon.com
205-879-3282 ext 1715
                                                     SQL Server Agent

• Alerts can be triggered by:
     – SQL Server errors
     – Performance monitor thresholds
     – WMI events
• SQL Agent can use database mail
• New types of Job
     – Analysis Services
     – Integration Services
• Proxy accounts
     – Each proxy account is associated
       with a SQL Server credential
     – Proxy account can be granted to
       database user, database role, and
       fixed server role
     – Proxy account can be associated to
       subsystem


IT-004   SQL Server Management Tools & Maintenance
                                                                  99
                                                     Maintenance Plans


• Based on SQL
  Server Integration
  Services
• Flexible Wizard-
  based interface
• Options to create
  cross-database
  plans
IT-004   SQL Server Management Tools & Maintenance
                                                                    100
                                                 Database Maintenance Plans




IT-004   SQL Server Management Tools & Maintenance
                                                                         101
                                                     Maintenance Plan Designer


•   Designer for
    maintenance
    workflow
     – Add tasks
     – Define workflow using
       connectors
     – Support for concurrent
       tasks
     – Specify success and
       failure paths




IT-004   SQL Server Management Tools & Maintenance
                                                                            102
                                                     Task Editor


•   Set task properties
     – For example, select database
•   View T-SQL




IT-004   SQL Server Management Tools & Maintenance
                                                              103
                                                     Executing a Plan


•   Maintenance plans are SQL
    Server Agent jobs
     – Plan properties include
       schedule
•   Can execute immediately from
    SQL Server Management Studio




IT-004   SQL Server Management Tools & Maintenance
                                                                   104
                           Restoring
                             Data



David Cutten
dcutten@commandalkon.com
205-879-3282 ext 1715
                                                     Restoring a Database

• Restore the target database by right-clicking it and
  choosing Tasks >> Restore >> Database
• Select the proper backups
• Go to the Options tab. Make sure that the recovery
  completion state is set to Leave Database
  Nonoperational so you can restore the transaction
  log later
• Click OK to start the restoration. Click OK at the
  Restoration Confirmation screen




IT-004   SQL Server Management Tools & Maintenance
                                                                       106
                                       Restoring a Full Database

• Restoring a Database using T-SQL.

RESTORE DATABASE Adventureworks
  FROM DISK = 'C:\Backup\AdventureWorks.bak';




IT-004   SQL Server Management Tools & Maintenance
                                                              107
                                      Restoring a Differential DB Backup



• Restoring a Database using T-SQL.

RESTORE DATABASE AdventureWorks
  FROM DISK = 'C:\Backups\Adventureworks.bak'
  WITH FILE = 1,
    NORECOVERY,
    REPLACE;

    RESTORE DATABASE AdventureWorks
    FROM DISK = 'C:\Backups\AdventureWorks.bak'
    WITH FILE = 3;

IT-004   SQL Server Management Tools & Maintenance
                                                                      108
                     Restoring a Database Point in Time


• Restoring a Database using T-SQL
• 1 .bak backup with 3 log files.

• Step 1
RESTORE DATABASE AdventureWorks
  FROM DISK = 'C:\Backups\Adventureworks.bak'
  WITH FILE = 1,
    NORECOVERY,
    REPLACE,
    STOPAT = 'Oct 23, 2006 14:30:29.000';



IT-004   SQL Server Management Tools & Maintenance
                                                     109
                     Restoring a Database Point in Time


• Step 2
RESTORE LOG AdventureWorks
  FROM DISK = 'C:\Backups\Adventureworks_log1.ldf'
  WITH FILE = 1,
    NORECOVERY,
    STOPAT = 'Oct 23, 2006 14:30:29.000';




IT-004   SQL Server Management Tools & Maintenance
                                                     110
                     Restoring a Database Point in Time


• Step 3
RESTORE LOG AdventureWorks
  FROM DISK = 'C:\Backups\Adventureworks_log3.ldf'
  WITH FILE = 3,
      STOPAT = 'Oct 23, 2006 14:30:29.000';
Step 4
RESTORE DATABASE Adventureworks
  WITH RECOVERY;




IT-004   SQL Server Management Tools & Maintenance
                                                     111
                           Keeping it all
                            Up to Date

David Cutten
dcutten@commandalkon.com
205-879-3282 ext 1715
                                                      Updates and Patches

             Some things to Keep in Mind about Patches and Updates:
             • Don’t jump on Updates as soon as they come out.
                   • Many updates that come out at first may have issues.
                   • Let someone else find this out!


             • Be aware of the Risk!
                   • There is a risk anytime you Patch or Update ANY software.
                   • Make sure you have a good backup prior to installing ANY update.


             • Setup a Test Server if Possible prior to Patching a production
               System.
                   • It is always a good idea to test any Update or Patch if Possible.


             • Better safe than Sorry!
                   • Give Us a Call or Send an Email and Check to see if we are aware
                     of any issues with a patch you are about to install.

IT-004   SQL Server Management Tools & Maintenance
                                                                                         113
                                                      Updates and Patches
             Updating Windows:

             There are two options:
             1. Windows Update
                        This update only includes updates for Windows Product.
                        http://update.microsoft.com/windowsupdate


             2. Microsoft Update
                         This update includes Most Microsoft Products.
                         http://update.microsoft.com/microsoftupdate
                         Patches
                         Windows
                         Office Products
                         Exchange Servers
                         SQL Servers



IT-004   SQL Server Management Tools & Maintenance
                                                                                 114
                                                     Updates and Patches
             Updating Windows:

             • Either update works fine.

             • Same Update Engine on Both.

             • We Recommend that you do not use Automatic Updates.
                   • Do Not Automatically install updates.
                   • This could Cause problem if the update has Issues.
                   • If you set the updates to Auto, It could reboot the Server.


             • Set your update to Check, Download and Install Manually.




IT-004   SQL Server Management Tools & Maintenance
                                                                                   115
                                                     Updates and Patches
   Updating SQL:

   • Updates for SQL are generally in the form of Service Packs
   • Microsoft occasionally publishes patches for known vulnerabilities
   • Check their website for current patches and service packs

   Microsoft SQL Home Page:
   http://www.microsoft.com/sql/default.mspx

   Microsoft Technet SQL Page:
   http://technet.microsoft.com/en-us/sqlserver/default.aspx

   Microsoft SQL Server Support Center:
   http://support.microsoft.com/ph/2855



IT-004   SQL Server Management Tools & Maintenance
                                                                          116
                                                         Other Resources

Other Resources:
• Microsoft Books On-Line
• Microsoft Website
• Free Websites:
http://www.sqlservercentral.com/
http://www.sqlteam.com/
http://www.sql-server-performance.com
There are many other great sites out there to help.


                          Just                       It!!!!
IT-004   SQL Server Management Tools & Maintenance
                                                                     117
                                    Contacting Command Alkon for Support

CONTACT US
- Command Series Support
- Performance Problems
- Patch/Upgrade Assistance
- Problem Resolution
- Planning Optimization Strategies

• Command Alkon Dispatch Support
         US & CANADA
          1-800-624-1147
          Option 2 (Customer Service)
          Option 1 (Dispatch Support)
          Choose option for your Product Line
IT-004    SQL Server Management Tools & Maintenance
                                                                     118
                                        Please Complete Your Evaluation


• Be sure to circle the session you are evaluating on
  the back of your card
         – Information Technology Track
         – IT-004
         – SQL Server Management Tools & Maintenance




         ● All breakout session materials can be accessed at:
                       www.commandalkon.com/cc2011/update/index.htm
IT-004    SQL Server Management Tools & Maintenance
                                                                     119

				
DOCUMENT INFO
Categories:
Tags:
Stats:
views:7
posted:1/7/2012
language:
pages:119