Microsoft SQL Server 2005 Programming Course Overview:

Part 1 Installing Microsoft SQL Server 2005.

• Meeting the Prerequisites.

• Preparing to Install.

• Choosing Default Instances or Named Instances.

• Choosing Service Accounts.

• Selecting an Authentication Mode.

• Choosing a Collation Setting.

• Upgrading from a Previous Version.

• Installing SQL Server 2005.

• Installing a Second Instance.

• Troubleshooting the Installation.

Part 2 Creating and Configuring Databases.

• Planning Your Database.

• Introducing Database Files.

• Introducing Filegroups.

• Deciding on Database File Placement.

• Introducing RAID-0.

• Introducing RAID-1.

• Introducing RAID-5.

• Introducing RAID-10.

• Creating Data Storage Structures.

• Introducing Extents.

• Introducing Pages.

• Estimating Storage Requirements.

• Estimating Table Storage Requirements.

• Estimating Index Storage Requirements.

• Creating and Configuring Databases.

• Creating a Database.

• Gathering Information about Your Database.

• Setting Database Options.

Part 3 Working with Tables and Views.

• Planning Tables.

• Introducing Built-in Datatypes.

• Introducing Computed Columns.

• Creating Tables.

• Restricting the Data.

• Introducing Constraints.

• Partitioning Tables.

• Understanding Views.

• Modifying Data through a View.

• Working with Indexed Views.

Part 4 Performing Indexing and Full-Text Searching.

• Understanding Index Architecture.

• Understanding Heaps.

• Understanding Clustered Indexes.

• Understanding Nonclustered Indexes.

• Creating Indexes.

• Using Primary Keys.

• Using Full-Text Searching.

Part 5 Introducing More Database Objects.

• Introducing Stored Procedures.

• Understanding the Types of Stored Procedures.

• Creating Stored Procedures.

• Recompiling Stored Procedures.

• Introducing Triggers.

• Understanding the Types of Triggers.

• Understanding DML Triggers.

• Understanding DDL Triggers.

• Understanding Trigger Recursion and Nesting.

• Understanding Disabling Triggers.

• Understanding Event Notifications.

• Introducing Functions.

• Understanding the Types of Functions.

• Using Scalar Functions.

• Introducing Table-Valued Functions.

• Introducing Built-in Functions.

• Introducing CLR Functions.

• Introducing Deterministic and Nondeterministic Functions.

• Introducing User-Defined Types.

• Creating T-SQL User-Defined Types.

• Creating CLR User-Defined Types.

• Getting More CLR Functionality.

Part 6 Implementing Security in SQL Server 2005.

• Understanding Security Modes 190

• Using Windows Authentication Mode 190

• Using Mixed Mode 191

• Setting the Authentication Mode 191

• Understanding SQL Server Logins 193

• Using Standard Logins 193

• Using Windows Logins 196

• Understanding the Items Common to All Logins 199

• Understanding Fixed Server Roles 199

• Creating Database User Accounts 202

• Understanding Permissions 204

• Applying Statement Permissions 205

• Applying Object Permissions 207

• Understanding Database Roles 210

• Using Fixed Database Roles 210

• Using Custom Database Roles 212

• Using Application Roles 215

• Understanding Permission States 217

• Granting a Permission 217

• Revoking a Permission 217

• Denying a Permission 217

• Introducing Ownership Chains 221

• Introducing Linked Server Security 223

• Introducing Encryption 224

• Creating a Security Plan.

Part 7 Working with Relational Data.

• Understanding and Using Transactions 242

• Executing Implicit and Explicit Transactions 243

• Committing and Rolling Back 244

• Executing Distributed Transactions 246

• Populating Tables 247

• Importing Data Using Bulk Insert 247

• Importing Data Using the bcp Utility 248

• Copying Data Using SSIS 250

• Bulk Inserting XML Data 252

• Supporting the Bulk-Logged Recovery Model 253

• Supporting Different Collation Types and Orders

• When Querying Data 255

• Formatting and Converting Datatypes 256

• Casting and Converting 261

• Understanding Datatype Precedence 264

• Understanding Collations 265

• Introducing Error Handling 266

• Using RAISERROR.

• Using @@ERROR

• Using Error Messages 267

• Using

• TRY…CATCH

• Blocks.

• Part 8 Working with XML Data.

• Understanding XML Data 282

• Using the xml Datatype 283

• Using Untyped XML 284

• Using Typed XML 285

• Working with XML Schema 285

• Querying XML Data 288

• Using the query Method.

• Using the value Method.

• Using the exist Method.

• Using the modify Method.

• Using the nodes Method.

• Creating XML Indexes.

Part 9 Working with Service Broker and HTTP.

• Understanding the SQL Server Service Broker Architecture.

• Working with Service Broker.

• Creating a Message Type.

• Creating a Queue.

• Creating a Contract.

• Creating a Service.

• Creating a Route.

• Using Service Broker.

• Sending Messages.

• Receiving Messages.

• Automating the Queue Processing.

• Introducing HTTP Endpoints.

• Configuring HTTP Endpoints.

• Securing HTTP Endpoints.

Part 10 Maintaining and Automating SQL Server.

• Maintaining Indexes.

• Understanding sys.DM_DB_INDEX_PHYSICAL_STATS.

• Reorganizing and Rebuilding Indexes.

• Maintaining Statistics.

• Maintaining Databases.

• Understanding DBCC CHECKDB.

• Shrinking Files.

• Understanding Automation Basics.

• Configuring Database Mail.

• Creating Operators.

• Creating Jobs.

• Creating Alerts.

• Creating Event Alerts Based on Standard Errors.

• Creating Event Alerts Based on Custom Errors.

• Creating Performance Alerts.

• Creating WMI Alerts.

• Using the Maintenance Plan Wizard.

• Copying Databases.

Part 11 Performing Backups and Restores.

• Backing Up Your Data.

• Understanding How Backups Work.

• Creating a Backup Device.

• Performing Full Backups.

• Performing Differential Backups.

• Performing Transaction Log Backups.

• Performing Filegroup Backups.

• Backing Up to Multiple Devices.

• Restoring Databases.

• Performing Standard Restores.

• Performing Point-in-Time Restores.

• Performing Piecemeal Restores.

• Devising a Backup Strategy.

• Planning for Full Backups Only.

• Planning for Full with Differential Backups.

• Planning for Full with Transaction Log Backups.

• Planning for Full, Differential, and Transaction Log Backups.

• Planning for Filegroup Backups.

Part 12 Achieving High Availability through Replication.

• Introducing Replication.

• Introducing the Publisher/Subscriber Metaphor.

• Introducing Articles.

• Introducing Publications.

• Understanding Replication Factors and Distribution Types.

• Using Distributed Transactions.

• Using Transactional Replication.

• Using Transactional Replication with Immediate Updating Subscribers.

• Using Snapshot Replication.

• Using Snapshot Replication with Immediate Updating Subscribers.

• Using Merge Replication.

• Using Queued Updating.

• Understanding Replication Internals.

• Understanding Merge Replication.

• Understanding Snapshot Replication.

• Understanding Transactional Replication.

• Considering Publication Issues.

• Considering Distributor Issues.

• Introducing Replication Models.

• Introducing Central Publisher/Central Distributor.

• Introducing Remote Distribution.

• Introducing Central Subscriber/Multiple Publishers.

• Introducing Multiple Publishers/Multiple Subscribers.

• Replicating over the Internet and to Heterogeneous Database Servers.

• Using Heterogeneous Replication.

• Using Internet Replication.

• Installing and Using Replication.

• Configuring SQL Server for Replication.

• Installing a Distribution Server.

• Adding a Publication.

• Creating a Subscription.

• Testing Replication.

• Managing Replication.

• Considering Administrative Issues.

• Considering Replication Backup Issues.

• Using the Replication Monitor.

• Working with Replication Scripts.

• Enhancing Replication Performance.

Part 13 Introducing More High-Availability Methods.

• Choosing the High-Availability Features You Need.

• Implementing Database Mirroring.

• Understanding Database Mirroring Concepts.

• Preparing for Mirroring.

• Creating Endpoints.

• Specifying Partners and Witnesses.

• Configuring the Operating Mode.

• Switching Roles.

• Implementing Log Shipping.

• Monitoring the Server.

• Configuring Log Shipping.

• Changing Roles.

• Monitoring Log Shipping.

• Managing Database Snapshots.

• Creating a Snapshot.

• Reverting from a Snapshot.

Part 14 Monitoring and Optimizing SQL Server 2005.

• Using System Monitor.

• Monitoring with SQL Profiler.

• Replaying a Trace File.

• Using the Database Engine Tuning Advisor.

• Troubleshooting SQL Server.

• Reading Error and Event Logs.

• Troubleshooting Blocks and Deadlocks.

• Troubleshooting Jobs.

• Using the Dedicated Administrator Connection.