1 / 15

IT 456 Seminar 5

IT 456 Seminar 5. Dr Jeffrey A Robinson. Overview of Course. Week 1 – Introduction Week 2 – Installation of SQL and management Tools Week 3 - Creating and Using a Database Week 4 - Using the SQL Server Agent Service Week 5 - Disaster Recovery Week 6 - SQL Server Security

maggy-howe
Download Presentation

IT 456 Seminar 5

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. IT 456Seminar 5 Dr Jeffrey A Robinson

  2. Overview of Course • Week 1 – Introduction • Week 2 – Installation of SQL and management Tools • Week 3 - Creating and Using a Database • Week 4 - Using the SQL Server Agent Service • Week 5 - Disaster Recovery • Week 6 - SQL Server Security • Week 7 - Performance Monitoring • Week 8 - Performance Optimization • Week 9 - High Availability

  3. Unit 4 - Using the SQL Server Agent Service • We did not have a seminar for Unit 4 due to the 4th of July Holiday… • But we can recap some of the material that would have been covered then

  4. Data Transfer and Task Automation • The SQL Server Agent service runs the components of task automation and data transfer.  Jobs, maintenance plans, and alerts may be configured to ensure that repetitive tasks and responses to problems execute automatically. Jobs can have multiple job steps, with flow from one job step to another based on the success or failure of a step.  A job step for a Database Engine job may be a transact-SQL script, a Windows executable, or an ActiveX script.

  5. Data Transfer and Task Automation • Maintenance Maintenance plan tasks that can be configured include full, differential, and transaction log backups, database integrity checks, and the rebuilding or reorganizing of indexes

  6. Data Transfer and Task Automation • Alerts An alert may be configured to respond to and possibly correct a problem that occurs.  When a SQL Server event is written to the Windows Event Application Log, the SQL Agent service is notified.  The SQL Agent retrieves the event from the Application Log, and compares the event to configured alerts.  If the event matches an alert, the alert fires.  Events can be generated by SQL queries or batched jobs which experience problems or errors. SQL queries can be batched to look at and assess specific parameters.

  7. Data Transfer and Task Automation • SSIS SQL Server Integration Services (SSIS) is SQL Server 2008's world-class extraction, transformation, and loading (ETL) tool.  Simple data transfer can be accomplished with the Import / Export Wizard that is accessible in SQL Server Management Studio. The Business Intelligence Development Studio (BIDS) is available for transfer of objects or complex data transformations. The BIDS is a Visual-Studio-like interface named that lets you build complex objects that can be cached or stored. You launch it and select File, New, Project you'll find that you can create an Integration Services Project using a template http://www.developer.com/db/article.php/3635316

  8. Data Transfer and Task Automation • BIDS The basic organizational concept of SSIS is the package. A package is a collection of SSIS objects including: • Connections to data sources. • Data flows, which include the sources and destinations that extract and load data, the transformations that modify and extend data, and the paths that link sources, transformations, and destinations. • Control flows, which include tasks and containers that execute when the package runs. You can organize tasks in sequences and in loops. • Event handlers, which are workflows that runs in response to the events raised by a package, task, or container.

  9. Week 5 - Disaster Recovery • Disaster recovery can be simple or complex • Disaster recovery might involve correcting an unintended delete or update, or it might involve bringing operations online at a remote facility with all servers rebuilt from scratch. Disaster recovery planning means having a plan for every problem in the spectrum. • Internal to SQL Server, disaster recovery planning requires understanding the backup and restore options and procedures for user and system databases

  10. Disaster Recovery • DR often gets people to focus on MAJOR disasters. However, the DBA must attend to failures at all levels • From a single interrupted transactions • To the recovery of a lost or corrupted database • Several tools support these types of “database” recoveries

  11. Database Recovery • Chapter 9 of the text focuses on different strategies to recovery data and emphasizes the importance of planning and the Transaction Log • Types of backups • Point in Time recovery • File restoration • Rollbacks

  12. Types of Backups • Full backup • Differential backups • Transaction Log backups • Partial backups • (Scheduling backups) • Disk backups are faster than tape • Optical disks are faster to read than to write

  13. Other issues • Shadowing (transaction shipping) • RAID • Mirroring • Performance and Optimization • Caching • Compressions

  14. Unit 5 Assignment and Project • Assignment • Three questions – 10 pts each • Project - In this project, you will • perform a backup of the NWTraders database, • add rows to the Sales.Orders and Sales.[Order Details] tables, and then • restore the NWTraders database to an alternative location. • As part of the project, you will show the steps for performing backups and the restores, describe the individual steps, as well as execute SQL commands to show changes in activity.

  15. Next two weeks • Week 6 - SQL Server Security • Week 7 - Performance Monitoring There will be a substitute for the next two weeks since I will be travelling in China

More Related