SQL Server 2014 and NAV

SQL Server 2014 has gone RTM on the first of April 2014.

For the moment Microsoft NAV has not yet confirmed SQL 2014 as compatible with some NAV versions. I hope they don’t wait too long.

But does it has some goodies that are useful for NAV?

Lets see the top features of SQL Server 2014:

  • Enhanced memory support: Standard Edition supports up to 128GB of memory. For the moment I haven’t even 1 customer that has 64 GB of memory for SQL server (the limit for SQL 2012 Standard Edition). But it might be useful in very large NAV installations.
  • Encrypted backups: I haven’t had a customer that wanted encrypted backups. But it IS Standard Edition!
  • Backup to Azure: Standard Edition : Well this could be interesting. Best practice is to have your backup not on the same physical server (and even less on the same physical disks…) and not even in the same room and even better : not even in the same building. Best would be on another continent (you never now if some meteorite will hit). Well, backup to Azure is the best you can have. In the Microsoft datacenter, you have 3 copies on different servers and if you have GEO redundant storage, you also have a copy in another Microsoft datacenter. Well, Encrypted backups as Standard Edition feature DO have sense now!
  • Delayed durability: Express Edition!: Normally when you COMMIT some data, SQL writes the data in the logfile and then your session is freed up again so you can continue working. Delayed durability keeps the COMMITed data in memory to harden it (=write it to disk) later. Does this ring a bell? The Native NAV database has had this for years! Of course both have the same problem: you COMMITed the data and it hasn’t been written to disk and your server crashes => you lose that data. This feature could be very useful if your transactionlog has performance problems and you can affort some dataloss. For more info : http://msdn.microsoft.com/en-us/library/dn449490(v=sql.120).aspx
  • Buffer Pool Extention (only on 64 bit): Standard Edition. If that 128 GB of memory is not enough, and you don’t want Enterprise Edition, you can do something in-between: put an SSD in your server and tell SQL to use it as extra memory with speeds somewhere in between memory-speed and disk-speed. Useful if your DB-size is expressed in TB instead of GB, but not really for NAV.
  • Enhanced Columnstore indexes: Enterprise Edition. Instead of indexing some fields in a table, you index only 1 column. So this index can be compressed very well and be scanned very fast. Very good for DWH. In SQL 2012 they existed but you had to disable the index before you could update the underlying table. Now you don’t need to disable the index anymore. Not really useful for NAV being an ERP and not a DWH. And there was no support for it directly in NAV (and probably it will never be supported). You would need to create/maintain them using TSQL.
  • In-memory tables (the feature is called In-Memory OLTP): Enterprise Edition. OLTP you say? Now it is getting interesting. NAV is OLTP! What are in-memory tables? An in-memory table (and its indexes) is kept in memory instead of on disk. Isn’t that the same as pinning (http://technet.microsoft.com/en-us/library/aa258284(v=SQL.80).aspx) a table? No, because pinning still using the same procedures for locks and latches. In-memory tables do not use those. They have their own procedures that are optimized for memory. Don’t worry, the data is still hardened to disk (don’t forget delayed durability), so your data is safe. They do have some limits like no AutoIncrement properties (=IDENTITY in SQL), clustered index is mandatory (is that a limit?), … Indexes are NOT maintained on disk, but only in memory, so when you start your server, it might take somewhat longer because SQL has to create the indexes on-the-fly in memory. Backups are faster,because the indexes are NOT backuped. Very important is that you need to have the memory to keep all that data in memory and keep some memory for the other tables/locking/… . This is a feature that has its uses in NAV, but NAV should support them natively (=using table-properties and NOT directly in SQL!) but it is still an enterprise edition feature. So it is not widely usable in NAV. But still a feature for which I see some cases where I might use it!

To finish, a link to the page where you have all the SQL 2014 features and in which edition you can use them : http://msdn.microsoft.com/en-us/library/cc645993(v=sql.120).aspx

NAV Techdays 2014

Get ready for NAV Techdays 2014 : 20+21 November 2014 + the pre-conference day on 19 November 2014!

Where is the bug?

IF NOT FILE.EXISTS(FileName) THEN

Datei.CREATE(FileName);

IF Datei.OPEN(FileName) THEN BEGIN

Do you find the bug?

Yes? Good. Actually, there are 3 bugs!

1) The bug you found: the developer creates the file (if it doesn’t exist) and then opens it.

2) The Classic client does NOT generated an error in this case

3) The service tier does NOT behave as the classic client but just exits without an error.

This is on NAV2009R2. I didn’t check NAV2013.

NAV 2013 C/AL editor can contain up to 240 chars on a line

Remember how many times you were typing code on a line and hit the invisible wall of 130 chars?

A lot of times, I suppose. At least I have.

Well, with NAV2013 you can get up to 240 chars!

Definitely useful if you copy a function with all its parameters in it to your code where you want to use that function.

But for the rest I would advice anyway to limit yourself to 130 chars.

Why? Because not everyone has a very-wide screen for starters and also because it is just too wide to have a good overview of the code.

And in case you write some code that you want to downgrade to older versions, you better limit yourself to those 130 chars!

Variable-names in NAV2013 can be longer than 30 chars

Before NAV2013, the names of variables (and functions) were limited to 30 chars. In NAV2013, you can go beyond that length.

I did some tests to see if NAV only considers the first 30 chars or also considers the chars after that length. NAV2013 considers also chars beyond the first 30!

I didn’t check out how long the variables can be because I think that 30 chars is long enough.

And in case you write some code that you want to downgrade to older versions, you better limit yourself to those 30 chars!

File.QUERYREPLACE in NAV2009R2

I noticed some strange behaviour of NAV2009R2 with QUERYREPLACE (of course with File=>Database=Alter=>tab Options=>Enable for Microsoft Dynamics NAV Server=TRUE):

file.QUERYREPLACE(FALSE);

=> gives a warning "Function ‘QUERYREPLACE’ is obsolete for Microsoft Dynamics NAV Server.". Nice! I don’t want to have an error when running in the NAV-servicetier, so I will fix my code.

IF (NOT ISSERVICETIER) THEN

file.QUERYREPLACE(FALSE);

=> does NOT give a warning. So the NAV-compiler is intelligent and knows I will never use the QUERYREPLACE in the servicetier thanks to this IF-statement.

IF (NOT ISSERVICETIER) AND TRUE THEN

file.QUERYREPLACE(FALSE);

=> gives a warning. I will never use the QUERYREPLACE in the servicetier but the NAV compiler didn’t figure that out!

I admit this blog post will not help you in your development, but it is a nice fun to know feature.

I created a new page, put some fields on it but they don’t appear in the Windows client

I also restarted the service tier, but still the same problem. And the preview shows the new fields!

Why?

Probably in your personalized settings, there is some leftover that hides the fields.

Solution:

Go to "Customize this page" and where you see "Restore defaults", click it (there are multiple ones and I didn’t check out which one exactly does the trick). After that you should be able to see the new fields.

Zoom aka "About this page" in NAV2013

The "About this page" is very handy to have some info about the page and the table of that page.

But it doesn’t work on a subpage. If I select "About this page", it always shows me the info of the main page.

It is possible to do it, and it is quite easy too : When you hover over the "About this page", it shows you the shortcut-keys : CTRL+ALT+F1 (not exactly an easy one to remember and even less to use!) (BTW: did I already advice you to spend some time to learn the shortcuts of NAV? You will work a lot faster using them!)

Knowing this shortcut, you just have to go to a field on the subpage and then hit the shortcut combo and you will have info on your subpage.

Destroying your backup strategy by making a backup in SQL Server

Yes, you read correctly, it is possible to destroy your backup strategy by making a backup.

How is that possible? Making backups is the whole point of a backup strategy!

I explain with an example:

For a server, your backup strategy is to make a full backup during the night and making transaction log backups every 15 minutes during the working hours. (BTW: I hope you don’t just do only 1 (one!) transaction log backup to empty the transaction log file, do you? BTW2: I hope you don’t put the backup-files on the same fysical disks as the database or transaction log files.)

Lets say, you have that setup well enough and all is working fine.

If it happens you need to do a restore of the database to the same server (same DB name or another DB name), SQL Server shows wich backups (a combo of 1 full backup, 0 or 1 differential backups and 0 or more transaction backups) you need to restore. If you confirm, SQL starts restoring all and all goes well if all backup files are found (and not damaged of course).

Now, if you do a full backup at a certain point (why is not important here) and you move the backup somewhere else (or delete it when your finished with it), you just might have destroyed your backup strategy!

WHY?

Lets get back to the restore before: SQL Server will propose the full backup you did (and deleted!) if you want to restore to the last changes in the database. When you start the restore, it will error out because SQL Server did not find your full backup you just deleted! Congratulations, you just destroyed your backup strategy! Actually, it is still possible to restore your database, but you have to do it manually.

How to avoid this problem? It is easy: Make your backups using the "Copy-only backup" toggle on the GUI interface. You don’t see that toggle? It is on the first page. It isn’t!?!? Are using still using SQL 2005? No? You are using SQL 2000???????????? What? You really should think about upgrading!!!!!

Ok, if you are using SQL2000 (or older) forget it. Or better : UPGRADE!!!

If you are using SQL2005, it is possible but you need to do it in TSQL. Generate the TSQL script (there is a nice button to do this.)

It will generate something like this:

 

BACKUP DATABASE [your_database] TO DISK = N’I:\your_database.BAK’ WITH NOFORMAT, NOINIT, NAME = N’your_database-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

 

Now add the "COPY_ONLY" to the command like below and you are ready to go.

 

BACKUP DATABASE [your_database] TO DISK = N’I:\your_database.BAK’ WITH COPY_ONLY, NOFORMAT, NOINIT, NAME = N’your_database-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

 

What does this COPY_ONLY do?

Well, not much, it just says SQL to consider this backup as not-existent for your backup-strategy. So when doing a restore, SQL will NEVER propose this backup.

Browse for a folder in NAV2013

This is really an easy one:

Function C419.BrowseForFolderDialog does just that AND it is written in .NET!

Create a new blog and join in the fun!