Microsoft SQL Server tool set
Microsoft SQL Server Tools and Microsoft Dynamics NAV
2009

Microsoft SQL Server is a database platform for online transaction processing
(OLTP), data warehousing, and e-commerce applications. It is also a business
intelligence platform for data integration, analysis, and reporting solutions.
SQL Server is a multi-component relational database management system
centered around a high-performance, highly available database engine.
The SQL Server Database Engine is the core service for storing, processing, and
securing data. The Database Engine provides controlled access and rapid
transaction processing to meet the requirements of the most demanding data
consuming applications within enterprises. The Database Engine also provides
rich support for sustaining high availability.
SQL Server is more than just a database.

It is important to understand how Microsoft Dynamics NAV integrates with SQL
Server. SQL Server offers many tools you can use to change the design of the
database tables and indexes. For example, you can use SQL Server Management
Studio (SSMS) to completely manage a database on SQL Server.

Microsoft SQL Server Management Studio, new in Microsoft SQL Server 2005,
is an integrated environment for accessing, configuring, managing,
administering, and developing all components of SQL Server. SQL Server
Management Studio combines a broad group of graphical tools with a number of
rich script editors that provide access to SQL Server for developers and
administrators of all skill levels.

SQL Server Management Studio combines the features of Enterprise Manager,
Query Analyzer, and Analysis Manager included in previous releases of SQL
Server, into a single environment.

In Microsoft Dynamics NAV objects are managed and designed using the Object
Designer. Within Microsoft Dynamics NAV you create tables, design keys, and
implement properties using the Table Designer. The Microsoft Dynamics NAV
database driver then translates these settings for SQL Server.

Although you can use SQL Server Management Studio and other SQL Server
tools to change the way the database tables and indexes are designed, you must
be careful when doing this. This is because Microsoft Dynamics NAV is always
the master of the design.

Microsoft Dynamics NAV stores metadata describing tables and indexes. This
metadata is decoupled from SQL Server metadata about tables and indexes and
could be brought out of sync if changes are made directly from management
studio. The consequence is that the next time a table is modified from inside
Microsoft Dynamics NAV all changes made from outside Microsoft Dynamics
NAV will be overwritten.

For example, Simon, the Systems Implementer, uses SQL Server Management
Studio and changes the fields contained in an index for the customer table. He
can use the SQL Server Management Studio to do this. Afterward, if Mort, the IT
Systems Developer, opens the Microsoft Dynamics NAV table designer, the
changes made by Simon will not be visible. Moreover, if Mort closes Microsoft
Dynamics NAV table designer and saves or recompiles the customer table it
might be that the customer table on SQL Server will be resynchronized with the
design specified in the Microsoft Dynamics NAV and the changes applied in
SSMS may be lost. So when Simon goes back into SQL Server Management
Studio his changes will be lost and he will have to re-implement them.
This is why it is important to understand how to use the SQL Server tools. If you
are required to make a change in the design of a table, you must do it from
Microsoft Dynamics NAV. In Microsoft Dynamics NAV there are many
properties available that influence how the database will be created on SQL
Server. For example, within Microsoft Dynamics NAV there are several
properties in tables and in keys that you can use to influence the way
corresponding indexes are created on SQL Server. Only when there is no
property available for a required change, can you make the change directly from
SQL Server.

It is also important to know that if you use the Microsoft Dynamics NAV client
to make a backup of your database, not all of the changes you performed directly
in SQL Server will be included in the backup. After you restore the backup, you
will most likely have to redo your changes.

Hardware is an important factor for high performance in any ERP system.
Selecting the correct hardware and configuring it from the beginning helps
produce optimal results for performance and will reduce the risk of encountering
future problems. Although you can choose to comply with minimal hardware
requirements, you also need to consider the future. The company may grow and
the number of concurrent users may increase causing increased demands on the
hardware. Choosing and configuring the correct hardware settings will affect the
scalability of the solution.

If the customer chooses not to invest much initially and only complies with the
minimal hardware requirements, and if the hardware you purchase is not
scalable, then you may encounter problems if the company and the corresponding
concurrent users and database size grow greater than their expected limits. At
that time you may need to expand the hardware to meet the demand. If the server
is not scalable, you will have to invest again to buy a bigger server. This kind of
investment can be avoided by selecting a server that is scalable at the initial
implementation.

The next chapter provides an overview of the advantages of SQL Server. When
selecting SQL Server as the database platform there are a number of advantages
and possibilities that were not available in the old native database.

Once the application is implemented, it is important to start monitoring
performance. By monitoring performance you can predict bottlenecks or
problems that may occur in the future and take action to avoid issues. You can
proactively monitor the database and also, for example, forecast database growth.
This chapter presents and explains the tools SQL Server and Microsoft Dynamics
NAV offer to monitor performance.

If you discover a performance bottleneck there are different ways to solve the
problem. The chapter about improving application performance goes into detail
on what you need to know when designing the Microsoft Dynamics NAV
application objects, properties, C/AL code, and keys to keep them performing
optimally. It is important to know that, although you can try to make the design
of the application as good as possible, a part of its performance will depend on
how the application is used. This is why monitoring performance is important. It
provides you with information on how the application is being used allowing you
to change the design accordingly.

For example, if the Microsoft Dynamics NAV application is in one case mainly
used for reporting and in another case it is mainly used for data entry, then these
two scenarios may have completely different requirements on indexes.
Once Microsoft Dynamics NAV is running smoothly and the design and
performance are optimal, your work is not finished. Now it is important to
maintain the database. If you do not maintain a database, performance will
decrease and may become sub-optimal.

For example indexes may become fragmented, the transaction log will continue
to grow, and so on. There are several tools you can use to maintain a SQL Server
database and this chapter explains how you can use them to keep the application
running optimally.