SQL Training Overview
Developing a database, getting the database to work with your applications, and getting it to production is just the beginning, but can often be where the story ends. This course examines other SQL Server 2005 essential application, support, and maintenance topics—features you can use for high-availability, stability, reliability, and scalability of databases, plus enhancements for processing large amounts of data, handling complex data structures, implementing asynchronous messaging, and working with XML.
SQL Training Audience:
Database developers and administrators
SQL Training Prerequisites
A basic understanding of Windows operating systems, security, networking technologies, SQL Server, T-SQL, Visual Studio.NET, and XML.
SQL Training Course duration
3 days
SQL Training Course outline
Disaster Recovery
- Planning a Backup Strategy
- Your Business Needs and Capabilities
- Considering Data Recovery Needs
- Availability Demands
- Data Usage
- Size of Database(s)
- Database Server Configuration
- Disaster Recovery Testing
- Recovery Time Considerations
- Backing Up Databases
- Backup Devices
- Creating Backups
- Recovery Models
- Types of Backup
- Scheduling Your Backups
- Scheduling System Database Backups
- Scheduling Simple Backups
- Scheduling Full and Bulk-Logged Backups
- Backing Up Related Databases
- Verifying Backup Media
- Using Checksums
- Using RESTORE VERIFYONLY
- Restoring Databases
- Roll Forward Set
- Restoring with the Simple Recovery Model
- Restoring with Full/Bulk-Logged Recovery Models
- Online Restore
- Using Database Snapshots
- Creating a Database Snapshot
- Reverting to a Database Snapshot
- Deleting a Database Snapshot
Failover Clustering
- High Availability Overview
- Levels of Availability and Downtime
- Estimating Availability Requirements
- SQL Server 2005 Failover Cluster
- Preparing a Server Cluster
- Hardware and Software Requirements
- Special Considerations
- Creating a New Cluster
- Set Up Domain Service Accounts
- Prepare Static IP Addresses
- Configure Computer Servers
- Prepare Shared Disks
- Create the New Cluster Server
- Configure the Cluster
- Add the Distributed Transaction Coordinator
- Installing SQL Server 2005 Failover
- What to Expect
- Before Installing SQL Server Setup
- System Configuration Checks
- After Installing SQL Server
- Adding SQL Server Nodes
- Add a New Node to the Cluster
- Add a Node to the SQL Server Virtual Server Cluster
- Remove a SQL Server Virtual Server Node
Database Mirroring
- Understanding Mirroring
- How Mirroring Works
- The Mirroring Sessions
- Database Mirroring Operating Modes
- Role Switching (Failover)
- Other Mirroring Details
- Database Mirroring Endpoints
- Client-Side Reections
- Setting Up Database Mirroring
- Prerequisites
- Configuration Recommendations
- Configuring the Recovery Model
- Configuring Mirroring with SQL Server Management Studio and Transact-SQL
- Configuring the Endpoints
- Configuring the Operating Mode
- Setting Up the Mirror Server Database
- Backing Up the Principal
- Restoring Full Backup on the Mirror
- Restoring Transaction Log Backups on the Mirror
- Starting the Mirroring Session
- Working with a Mirror Session
- Accessing Mirror Data with Snapshots
- Monitoring Database Mirroring
- Viewing the Mirror, Witness, and Endpoints
Log Shipping
- Overview
- Log Shipping Requirements
- Security Requirements
- Configuring Log Shipping
- Transaction Log Shipping Property Page
- Configuring the Primary Database
- Configuring the Secondary Database
- Configuring the Monitor Server
- Saving the Log Shipping Configurations
- Removing Log Shipping
- Monitoring Log Shipping Jobs
- Viewing the Agent Job History
- Transaction Log Shipping Status Report
- Querying Log Shipping System Tables
- Querying with Log Shipping Stored Procedures
- Configuring Log Shipping in Transact-SQL
- Configuring the Primary Database
- Configuring the Primary Backup Job
- Configuring the Secondary Database
- Configuring the Secondary Copy Job
- Configuring the Secondary Restore Job
- Removing Log Shipping
Peer-to-Peer Replication
- High-Availability
- Requirements and Limitations
- Protecting Data with Partitioning
- Special Identity Columns Handling
- Configuring Peer-to-Peer Replication
- Security Planning
- Configuring the Distributor
- Configure Distribution Wizard
- Adding a Publisher Server
- Changing a Distributor’s Properties
- Securing the Distributor
- Creating the Publisher
- Specify the Distributor
- Publication Database
- Publication Type
- Articles
- Filter Table Rows and Snapshot Agent
- Agent Security
- Wizard Actions
- Complete the Wizard
- Creating Publication
- Allowing Peer-to-Peer Subscriptions
- Securing the Publisher
- The Replication Monitor
- Configuring Peer-to-Peer Subscribers
- Initializing the Subscriber Database
- Peer Distributor Preparation
- Securing the Distributor
- Configuring Peer-to-Peer Topology
- Publication Specification
- Peers
- Log Reader Agent Security
- Distribution Agent Security
- New Peer Initialization
- Completing the Wizard
- Securing the Publisher
- Configuring the PAL
- Identity Columns
- Adding a Node to an Existing Topology
- Well-Connected Topology
Monitoring and Troubleshooting
- Diagnosing and Resolving Database Server Errors
- Using the Dedicated Administrator Connection (DAC)
- SQL Server Error Logs
- Setting Up Alerts in SQL Server Agent
- Advantages of Using Alerts
- Configuring the Alert
- Monitoring SQL Server Agent Job History
- Job Activity Monitor
- Gathering Performance and Optimization Data Using DMVs
- DMV Permission Requirements
- Inspecting a Server with DMVs
- Troubleshooting Bottlenecks
- Collecting Information Using DMVs
- SQL Server 2005 Performance Dashboard Reports
- DMVStats
Implementing Service Brokers
- Understanding Service Brokers
- Service Broker Advantages
- Service Broker Applications
- Service Broker Messaging
- Service Broker Uses
- Designing Service Broker Applications
- Message Types
- Service Broker Activation
- Transaction Life Cycle
- Handling Errors
- Handling Poison Messages
- Service Broker Security
- Programming Service Broker Applications
- Enabling Service Broker
- Creating Message Types
- Creating Contracts
- Creating the Queue
- Creating the Service
- Creating Routes
- Creating Service Broker Endpoints
- Implementing Applications
Implementing SQL Server Partitions
- Overview of Table-Based Partitioning
- Why Partition?
- What’s New in SQL Server 2005?
- Range Partitions
- Partition Key and Number of Partitions
- Using Multiple Filegroups
- Index Partitioning
- Creating Partitioned Tables
- Creating a Partition Function
- Creating a Partition Scheme
- Partitioning Tables and Indexes
- Querying Partitions
- Using the $PARTITION Function
- Catalog Views
- Managing Partitions
- Modifying Partitioned Tables or Indexes
- Modifying a Partition Function
- Modifying a Partition Scheme
- Backing Up Partitions
- Performance Considerations
Complex Querying
- Working with NULL Values
- SqlTypes and CLR Types
- About the ANSI_NULLS Option
- NULLs and SqlBoolean
- Assigning NULL Values
- Ranked Grouped Data
- The ROW_NUMBER Function
- Using the RANK Function
- Introducing the DENSE_RANK Function
- The NTILE Function
- Writing Correlated Subqueries
- Subquery Basics
- What is a Correlated Subquery?
- Using the WHERE Clause
- Including the HAVING Clause
- Correlated Subqueries and Updates
- Comparing to Joins and Temp Tables
- Using Common Table Expressions
- When to Use CTEs
- CTE Syntax
- Recursive CTEs
Advanced Techniques
- Complex Data and Structures
- Issues with Data Types
- Working with Multinational Data
- Working with Hierarchical Data
- Writing More Efficient Queries
- The EXISTS Clause
- Joins vs. Subqueries
- One-Pass Queries
- Using Temporary Tables
- Table Variables
- User-Defined Table-Valued Functions
- Common Table Expressions
- Worktables
- Working with Complex Queries
- Keeping it Simple
- Maintaining Query Files
- Using Source Control and Versioning
- Using Visual SoruceSafe
Working with XML
- The xml Data Type
- Declaring XML Objects
- Loading Data into an XML Instance
- Indexing XML Columns
- XML Schema Collections
- Typed and Untyped XML
- Schema Basics
- Registering Schemas
- Viewing Stored Schemas
- Querying XML
- XQuery
- FOR XML
- OPENXML
- Best Practices
- Relational vs. XML Data Model
- Storing XML in SQL Server 2005
- Reasons to Index XML Columns
- Querying
|
