Module 1: Introduction

This module illustrates the course outline and explains the order of the modules. It also describes the integration between Microsoft SQL Server and the Microsoft Dynamics NAV Object Designer.

Lessons

• An overview of the course structure.
• The scope of the course.

After completing this module, students will be able to:

• Know the course structure and the scope of the course.

Module 2: Setup and Installation

This module starts with an overview of the software requirements to implement Microsoft Dynamics NAV 2009 and SQL Server. After that the hardware requirements are explained in detail. It also explains the security and synchronization process.

Lessons

• How to evaluate the software and hardware requirements for Microsoft Dynamics NAV 2009.
• Understand the Microsoft Dynamics NAV two-tier and three-tier architectures.
• The recommended settings for a Microsoft Dynamics NAV database.
• The security models and the synchronization process.

Lab: Change the Recovery Model.

Lab: Set Trace Flags.

Lab: Create a Login Stored Procedure.

Lab: Use File groups to Change the Storage Location of a Table.

Lab: Create Users and Synchronize Security.

After completing this module, students will be able to:

• Evaluate the software requirements for Microsoft Dynamics NAV 2009.
• Evaluate the hardware requirements for Microsoft Dynamics NAV 2009.
• Review the Microsoft Dynamics NAV 5.0 architecture.
• Configure the components in a Microsoft Dynamics NAV implementation.
• Explain the security model and security synchronization.

Module 3: Advantages of the SQL Server Option

This module focuses on the available backup facilities that are included with SQL Server and how to implement a backup strategy. The module also explains how you can access the database using tools such as Microsoft Excel. This module also includes a high-level overview of several performance monitoring tools. These tools will be explained in more detail in the next module.

Lessons

• The differences between the two database platforms.
• The different types of backups in Microsoft SQL Server and the tools you can use to monitor performance for a Microsoft Dynamics NAV 2009 database on SQL Server.

Lab: Create a Backup and Restore to a Point in Time.

Lab: Transfer Data from Test to Live Database (Transact-SQL).

Lab: Transfer Data from Test to Live Database (SSIS).

After completing this module, students will be able to:

• Define an adequate backup strategy.
• Access a Microsoft Dynamics NAV database from third-party tools.
• Describe the available Performance Monitoring tools.
• Evaluate the scalability requirements of a Microsoft Dynamics NAV implementation and anticipate future growth.

Module 4: Performance Audits

This module focuses on performance and troubleshooting performance bottlenecks. In addition, it contains detailed demonstrations on the available performance monitoring tools and how and when to use them. These demonstrations can be used as a guideline or workflow on how to handle performance problems.

Lessons

• How to create a test environment that can be used to troubleshoot performance issues.
• Different tools that can be used to monitor system performance and code execution.

Lab: Set up and Schedule System Monitor.

Lab: Create a SQL Server Profiler Trace.

Lab: Correlate System Monitor and SQL Server Profiler Data.

Lab: Analyze a SQL Server Profiler Trace using DTA.

Lab: Analyze an SQL Query using DTA.

Lab: Find Missing Indexes using DMVs.

After completing this module, students will be able to:

• Create and set up a test environment for troubleshooting purposes.
• Run a performance audit using System Monitor.
• Monitor performance using the Client Monitor tool.
• Identify the clients that cause performance problems using Session Monitor.
• Create trace files using Microsoft SQL Server Profiler and analyze the trace files.
• Use the Database Engine Tuning Advisor to analyze indexes and data partitioning.
• Use Dynamic Management Views to analyze performance data.
• Collect performance-related data using additional scripts, tools, and reports.

Module 5: Improve Application Performance

This module discusses the available techniques to improve application performance and how to avoid performance bottlenecks. It goes into detail about how to optimize C/AL code, keys, and indexes. It also contains information about best practices and a workflow explaining how to conduct a performance audit.

Lessons

• The key areas in application development that are important for performance.
• How to create optimized keys and how to read data in an optimal way using FINDFIRST, FINDLAST, and FINDSET.
• How to avoid deadlocks.
• Tips, techniques, and methods to more easily perform troubleshooting and tuning exercises.

Lab: Optimize C/AL Code for Performance.

Lab: Find Index Usage.

Lab: Find Unused Indexes.

Lab: Disable Unused Keys.

Lab: Create a Deadlock Trace.

After completing this module, students will be able to:

• Write optimized C/AL code.
• Optimize SIFT tables.
• Optimize cursors by using the correct C/AL code statements.
• Optimize key design and usage in Microsoft Dynamics NAV.
• Avoid locks and deadlocks.
• Troubleshoot performance issues related to the graphical user interface.
• Set up index hinting.
• Optimize data entry using the Bulk Insert feature.
• Use some tips and tricks that are useful when optimizing Microsoft Dynamics NAV on SQL Server.

Module 6: Maintenance

This module discusses why and how to implement maintenance tasks on SQL Server. It also contains several demonstrations that display how to monitor database and transaction log growth.

Lessons

• How to set up and schedule maintenance plans to perform important maintenance tasks on SQL Server.
• How to execute specific maintenance tasks using Transact-SQL scripts.

Lab: Create a Maintenance Plan.

Lab: Change the Fill Factor for Hot Tables.

Lab: Compare Query Execution Before and After Deleting Statistics.

After completing this module, students will be able to:

• Evaluate how to optimize a Microsoft Dynamics NAV database.
• Set up a maintenance plan for a Microsoft Dynamics NAV database to optimize indexes, update SQL Server statistics, and set index fill factors.
• Maintain a SQL Server by monitoring performance, database, and transaction log growth.