1. Open SQL Server Management Studio. 2. Connect to the NAV-SRV-01 Database Engine. 3. Click the New Query button.
4. In the new query window, enter the following SQL statement:
ALTER DATABASE [Demo Database NAV (6-0)] SET MULTI_USER WITH NO_WAIT
Quick Interaction: Lessons Learned
Take a moment and write down three Key Points you have learned from this chapter
1.
2.
Solutions
Test Your Knowledge
1. Which recovery model is required to enable point-in-time recovery? (Select all that apply)
( ) Simple (√) Bulk-Logged (√) Full
( ) All of the above
2. Which backup strategy allows point-in-time recovery? (Select all that apply) ( ) Full Backup Strategy
(√) Database and Transaction Log Backup Strategy ( ) File or Filegroup Backup Strategy
(√) Differential Backup Strategy, combined with Transaction Log Backups
3. What is true about SQL Server Native Client ODBC driver?
( ) SQL Server Native Client ODBC driver is installed automatically with the client.
(•) SQL Server Native Client ODBC driver can write to the database. ( ) SQL Server Native Client ODBC driver ensures data consistency and
validation by firing C/AL code triggers.
( ) SQL Server Native Client ODBC driver can be used to access Classic database and SQL Server database.
4. Which tools can be used to monitor performance on a classic database? (Select all that apply)
(√) Client Monitor (√) Session Monitor
( ) Performance Data Collector (√) Perfmon
5. What are Dynamic Management Views and Functions used for and how can you use them?
MODEL ANSWER:
Dynamic management views (DMV) and functions (DMF) were introduced as a new feature in SQL Server 2005. They return server state information that can be used to monitor the health of a server instance, diagnose
problems, and tune performance. Dynamic Management Views All dynamic management views and functions exist in the sys schema and follow this naming convention: dm_*. Dynamic management views can be referenced in Transact-SQL statements by using two-part, three-part, or four-part names. Dynamic management functions on the other hand can be referenced in Transact-SQL statements by using either two-part or three-part names. 6. One of your customers wants to upgrade to Microsoft Dynamics NAV 2009.
You have calculated the initial size of the database to be 110 GB, with an expected annual growth of 20%. Which server option will you recommend and why?
MODEL ANSWER:
Given the initial database size and the expected growth, SQL Server option offers better opportunities for future growth. The maximum database size with Classic database server is 132 GB, while SQL Server can scale out to bigger databases. If you recommend Classic database server, the maximum database size limit will be reached after one year already and the customer will have to switch to the SQL Server Option.
CHAPTER 4: PERFORMANCE AUDITS
Objectives
The objectives are:
• Create and setup 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.
Introduction
Monitoring server performance helps keep your servers functioning optimally and helps you identify bottlenecks in the system. You can use the performance monitoring data to identify problems and apply corrective action. You can also use the monitoring data to enhance the performance of your servers by
identifying areas that need additional resources. For example, you may need to increase your storage capacity to handle the growing number of users in your organization.
If your organization is small, or if you rely on one server for most of the
Microsoft SQL Server® operations, you may need to monitor only one server. If you have a larger organization, or if you want to monitor the performance of all servers and components in SQL Server, you can use System Monitor, which is a Windows Server 2003 component.
You can also use the Windows Performance Monitor, a Windows Server 2003 snap-in, to verify that your operating system is functioning correctly. The Windows Performance Monitor, which is made up of the System Monitor and Performance Logs and Alerts snap-ins, is the primary tool set used to analyze and maintain SQL Server and operating system performance levels. The Windows Performance Monitor is quite flexible and can be used to collect data
interactively from a single server or automated to collect data from many servers. For more information about how to use the Performance console, see the
Windows Server 2003 documentation Windows Server 2003 documentation (http://www.microsoft.com/windowsserver2003/proddoc/default.mspx).
You can also use Task Manager (Taskmgr.exe) to obtain information about the processes and programs that are running on your local computer.
There are important differences between Task Manager and the Windows Performance Monitor, such as the Windows Performance Monitor captures data to a file whereas the Task Manager can end a process. Task Manager is primarily a troubleshooting aid, and the Windows Performance Monitor is used for more detailed troubleshooting and analysis.