COPY function in NAV 2009

COPY function in NAV 2009 has new parameter ShareTable.

Record.COPY(FromRecord [, ShareTable])

ShareTable specifies whether the function creates a copy of the record or creates a reference to a temporary record.

If FromRecord and Record are both temporary and ShareTable is true, then the COPY function does not create a new copy of the record. Instead, the COPY function causes Record to reference the same table as FromRecord.

The default value is false. If you specify false, all records are copied to Record from FromRecord.

If ShareTable is true, both Record and FromRecord must be temporary; otherwise an error will occur.

ListPart (Sub form) controls from the Page Header

In NAV 2009, it is not possible to get the selected subform record from the page header.

CurrPage.SubFormControl.FORM.GETRECORD(myRecord)

This will always give the first record in the subform.

Page Header functionality should be moved to the subform in the pages.

How the three-tier architecture works in NAV 2009

The Role Tailored architecture is a three-tiered architecture which contains the following levels:

• Presentation level (Role Tailored client)
  
• Business logic and communication level (Microsoft Dynamics NAV Server)
  
• Data level (SQL Server database)
  

The different tiers can be installed on different computers. You can have multiple instances of any of the components (though usually not on the same computer): multiple clients, multiple servers, and multiple database servers.

Three terms keep coming up when setting up Client, NAV Server and SQL Server on different machines.

• Delegation / Impersonation
  
• Kerberos
  
• SPN
  

Delegation / Impersonation:

When the Role Tailored client, Microsoft Dynamics NAV Server, and SQL Server are all installed on separate computers, the client interacts with the database through an intermediate computer, which is running Microsoft Dynamics NAV Server. The server is performing actions on the client's behalf. This process is known as impersonation.

Delegation is when a front-end service forwards a client request to a back-end service so that the back-end service can also impersonate the client. Impersonation is typically used to check whether a client is authorized to perform a particular action, while delegation is a way of flowing impersonation capabilities, along with the client's identity, to a back-end service.

All logins here are Windows logins. Impersonation would be a nice way for hackers to gain access, so therefore Windows requires a domain administrator specifically allows the NAV-server to impersonate users on the SQL Server.

This is only when the NAV-Server and SQL Server runs on two different machines. When they run on the same machine, then Windows has already authenticated the end-user when they connected to the NAV-Server. So when the NAV-Server logs on to the SQL Server (on the same machine), Windows has already knows the end-user. In this case there is no need to set up delegation, and hence no need to worry about Kerberos and SPNs.

But with multiple machines, delegation is needed. Delegation requires Kerberos.

Kerberos:

Connections between two users happen all the time. What Kerberos adds to such a connection is a certificate (ticket) which ensures that each of the two users can trust the identity of the other user. This is actually a kind of countermeasure against a user (hacker) impersonating another user.

Windows requires that before it allows delegation, that the connection being delegated is a Kerberos connection. In this way, Windows has assurance that the user credentials being forwarded are valid. So when RTC connects to the NAV-server, it has to do this with a Kerberos connection.

A Kerberos connection is between two Windows user accounts. A user doesn't know (shouldn't know) what user account the NAV-server runs under.

This is where SPNs come in. So, finally we get to the SPNs:

SPN:

SPN (Service Principal Name) is a simple table that maps a service to a user account. Think of it as a table with two fields: Service-name, and Windows User name.

When the NAV client wants to start a Kerberos connection to the NAV-server, it will connect to for example DynamicsNAV\Nav-Server:7046. Kerberos requiring a user name will then look this up in the SPN table and find the user name there. So, only if an SPN has been created for the account that runs the NAV-service, will Windows be able to then start a Kerberos connection. And the user will never need to know which that account the NAV-server is running under.

When setting up SPNs, make sure that:

1. The account that runs the NAV-service has an SPN that contains what the NAV client will connect to (Server name in the right format)
   
2. Quite a common problem: Make sure that there are no duplicate SPNs. If you change the NAV-service to run under a different account you must set up SPNs for the new account. Then make sure to also remove the SPNs for the old account.
   

So in short: Delegation / Impersonation require Kerberos. Kerberos connection to a service running an unknown account requires SPN. And, visa versa, if SPNs have not been set up correctly, then Kerberos won't work so then Delegation won't work either.

Creating Service Principal Names

The first step in setting up delegation is to create any necessary service principal names (SPNs). To make delegation more secure, Active Directory uses Kerberos to authenticate services. An SPN is the name by which a client uniquely identifies an instance of a service, using the account under which the service runs. To make delegation work, you must explicitly create an SPN when either the Microsoft Dynamics NAV Server service or the SQL Server service is running under a dedicated domain user account. We recommend using this configuration for the highest security. If both services are running under dedicated domain user accounts or under the same dedicated domain user account, you must create two SPNs: one for each service.

Note: You have the setspn command-line tool installed on your server. In Windows Server 2008, the setspn tool is included if you have installed the Active Directory Domain Services server role. In Windows Server 2003, you must download the Windows Server 2003 Service Pack 2 32-bit Support Tools to get the setspn tool.

To create a service principal name

1. Open an elevated command prompt. Click Start, and in the search window, type Command Prompt.
   
2. Right-click Command Prompt, and then click Run as administrator.
   
3. At the command prompt, create the SPN. The syntax is:
   

   setspn –A InstanceName/FullyQualifiedDomainNameOfServer:Port Domain \User
   

Delegating Access to the SQL Server Service

Configuring delegation means explicitly configuring the Microsoft Dynamics NAV Server service to delegate its access to the database server on behalf of the RoleTailored client. To make the access more secure, you specify delegation to a specific service on a specific server.

To delegate access to the SQL Server service

1. On any server computer in the domain, click Start, and then click Run.
   
2. In the Open field, type dsa.msc.
   

   This opens the Active Directory Users and Computers utility.
   

3. For you to configure delegation, the functional level for the domain must be Windows Server 2003 or higher. To verify the domain functional level, right-click the node for the domain where you have installed Microsoft Dynamics NAV, and then click Raise Domain Functional Level. If the level is not at least Windows Server 2003, raise it to that level.
   
4. Right-click the node for the domain where you have installed Microsoft Dynamics NAV, and then click Find.
   
5. In the Find Users, Contacts, and Group dialog box, type the name of the domain user in the Name field, and then press ENTER.
   
6. In the Search results area, right-click the domain user, and then click Properties.
   
7. On the Delegation tab, click Trust this user for delegation to specified services only, and then click Use Kerberos only.
   
8. Click Add to open the Add Services dialog box.
   
9. In the Add Services window, click Users or Computers, and then type the name of the domain user.
   
10. In the list of services for the domain user, click MSSQLSvc, which is the name of the SQL Server service.
    
11. Click OK to exit the Add Services dialog box.
    

Click OK to close all open dialog boxes.

Physical Location of Automation Server

The virtual table 2000000046 "Automation Server" shows you the physical location of the automation servers (.dll file).

Service Order-->Post-->Ship & Consume

In the Service Management-->Service Order-->Post have 4 options.

1) Ship

2) Invoice

3) Ship & Invoice

4) Ship & Consume

Ship: The program posts shipment of the items.

Invoice: The program invoices items that have already been shipped.

Ship and Invoice: The program invoices and ships the items.

Ship and Consume: The program posts shipment and consumption on the order and creates a new service shipment document.

If you fill the "Qty. to Ship" in the Service Lines and post the Service Order as Ship, program will created one positive service ledger entry with 'Entry Type' Usage and one negative Item Ledger Entry with 'Entry Type' as Sale. That means to complete the service order, system used the component that has posted as negative line in the ILE and that line has 'Entry Type' as Sale which means it has to be invoice to the customer.

If you fill the "Qty. to Ship", "Qty. to Consume" in the Service Lines and post the Service Order as Ship and Consume, program will created two service ledger entries (First line with 'Entry Type' Usage and Positive Qty. Second line with 'Entry Type’ Consume and Negative Qty.) and one negative Item Ledger Entry with 'Entry Type' as Negative Adjustment. That means to complete the service order, system used the component that has posted as negative line in the ILE and that line has 'Entry Type' as 'Negative Adjustment' which means it will not invoice to the customer and used for the internal purpose and registered in the Service Ledger Entries as 'Entry Type' consume (i.e. Second Line).

Style property in NAV 2009 Pages

Style Property

This property sets a value that determines how a field on a page is formatted. This applies to page controls that have text

Property Value:

Value Format in edit mode Format in view mode

Strong Bold Bold

Attention Red Default

Favorable Bold + Green Bold

Unfavorable Bold + Italics + Red Bold + Italics

Remarks:

If the StyleExpr Property evaluates to true, then the value of the field is formatted as specified by the Style property.

By default, this property is not set.

This property is not supported if the data type of the SourceExpr of the field is one of the following:

• Code

• Boolean

• Binary

• BLOB

• GUID

• RecordID

The Style formatting that is applied to a page in Edit mode, is different than the formatting that is applied in View mode.

StyleExpr Property

This property sets whether the Style Property is applied.

Applies To:

• Page controls that have text

Remarks:

If the StyleExpr property evaluates to true, then the value of the field is formatted as specified by the Style Property. You can use either true, false, or a variable that evaluates to true or false. The default value is false.

BLOB Fields with NAV & SQL

BLOB = [Binary] Large OBject

Well, several tables in MS Dynamics NAV use such "BLOB"-fields to store binary data like pictures. This could be quite convenient for the application, for example for creating Item catalogues displaying such pictures, etc. but from a NAV/SQL performance perspective BLOB could also be a real pain – I'd like to explain why, and what I recommend to do to avoid the problems …

 

The basics

A NAV "BLOB" data-type is actually the "image" data-type in SQL Server (SQL also features other LOB types as "text" or "ntext" but they aren't relevant for NAV).

[ Out of the box, NAV flags a "BLOB" field with the property "Compressed" = TRUE. If this is enabled, NAV uses a compression algorithm to save the data more compact. Have in mind that SQL Server does not recognize this "compression", so if you're using BLOB to transfer binary data from NAV to other SQL systems – which could be very smart, but that's a different story! – you should make sure to set "Compressed" to FALSE. But anyway … ]

By default, every table in NAV has a "Clustered Index" – and even though this is not a necessity for SQL Server, this is a very good idea! The general rule is: "Every table has a Clustered Index". Period.

An Index is an internal balanced-tree (B-tree) like structure, helping to retrieve records quickly. Every SQL Server table could have multiple indexes. The b-trees exist of "index pages" ("root nodes", "index nodes" and "leaf nodes") which store the indexes values. The difference between a "Clustered Index" (CI) and a "Non-Clustered Index" (NCI) is, that the "leaf node" level of a CI represents the table itself. Means: while all other index nodes only contain the indexed valued – e.g. "No." of table "Item" – the "leaf nodes" include all the other fields – e.g. "Description", "Item Category", etc.. Further, the CI defines how the data is physically stored and sorted within the table.

Hence, each table could have one CI and multiple NCI. By default, the CI is generated on basis of the "Primary Key" in NAV (which is mostly a good idea), all other "Keys" – if enabled and maintained - will be created as NCI.

As mentioned, the CI contains all fields of a table. Really all? No, not the "BLOB" fields. Fields of type "image" in SQL can store up to 2 Gigabytes of data (which requires about 256.000 "pages" x 8 KB). So if that LOB data would be included into the CI, this could dramatically increase the index size and severely degrade its performance is all aspects! That's why LOB fields are stored differently within the database, not as part of the "leaf nodes" of the CI (of course there are internal references).

 

The problem …

… in NAV is, that the "BLOB" fields are always read with any query on the related record. In SQL Profiler one could see those queries, for example when reading from the "Item" table:

SELECT *, DATALENGTH("Picture") FROM "Navision"."dbo"."CRONUS$Item" WHERE …

The "SELECT *" queries all fields from a table, thus, also the BLOB field(s). With "DATALENGTH" additionally the size of that "BLOB" is determined (C/SIDE uses this info to deal with the field). As the BLOB field is not part of the CI "leaf nodes", SQL Server has to perform additional internal operations to gather the LOB data. Due to the specific storage of that "image" fields, the data could be "spread" widely within the database (it cannot be granted that a LOB field is stored within one continuous array of pages), thus SQL Server has to perform a lot more "page reads" then (shown in SQL Profiler as a unusual high number of "Reads", e.g. >= 1000).

And as always: a high number of "Reads" means a high consumption of cache space and CPU and may also result in physical I/O. Too many "Reads" waste precious system resources and degrade performance!

So much about the technical side. But the question should also be: Why always reading the BLOB fields anyway?

In NAV there are a lot of important and heavily used tables which are "affected" by BLOB fields, as "Item", "Customer" or "Vendor" (and maybe "Contact" and others). For example, the "Item" table is permanently queried when looking up items, creating an order, posting a shipment, posting an invoice, posting an item journal etc.. Within all the processes and transactions NAV fires a SELECT * on the table, reading the "Picture", too.

But what is the need for a "Picture" when posting an order or processing physical inventory? There is none! Nope. Zip. Nada.Pointless.

But using "BLOB" fields in such tables could be a real problem regarding performance.

 

The solution

Of course, the solution cannot be to abstain from using BLOB fields. Again: using BLOBs could be very smart, not just for storing pictures, but maybe also for XML data or documents.

To avoid performance issues it is just necessary, to keep BLOBs away from business processes, and this could be done rather simple: Storing BLOBs in separate tables!

For example, when it is about the "Picture" in table "Item", just create a new NAV table e.g. called "Item Picture", existing of just two fields: "Item No." (Code20) and "Picture" (BLOB). Once this new table exists, the "Picture" could be transferred quite simple with some NAV programming – or, even faster, with some TSQL:

USE [Navision] -- change db name here

GO

INSERT INTO [dbo].[CRONUS$Item Picture] ([Item No_], [Picture])

SELECT [No_], [Picture] FROM [dbo].[CRONUS$Item] WHERE DATALENGTH([Picture]) <> 0

GO

If the data has been transferred, the "Picture" could be erased from the old "Item" table – either by some C/AL code, or this TSQL:

USE [Navision] -- change db name here

GO

UPDATE [dbo].[CRONUS$Item] SET [Picture] = NULL WHERE DATALENGTH([Picture]) <> 0

GO

(use the TSQL at own risk – no warranties or anything)

Now the "Item" table is cleaned up – de-BLOBbed so to speak - so all kinds of business processes should not encounter trouble related to BLOB Reads. Then of course it is necessary to change the application, now looking up the "Picture" from the new table "Item Picture", but actually that's a piece of cake (e.g. ItemPicture.GET(Item."No.");). Have in mind that it isNOT necessary to disable the BLOB field in the table – just not using it is all it takes.

I highly recommend to implement this wherever BLOB is used in "transactional tables"! It's just a minor effort, but could have remarkable impact on NAV/SQL performance. Further, to keep the BLOB's performance OK it is necessary to periodically maintain such tables with an ALTER INDEX REORGANIZE WITH LOB_COMPACTION (SQL 2005/2008) or DBCC INDEX_DEFRAG (SQL 2000) – see "Books Online" about details.