NAV TechDays 2014: Installing and configuring SQL Server for NAV

The material of my workshop (including a pdf with the slides) is online: http://mibuso.com/dlinfo.asp?FileID=1605

The NAV2009R2 webservice does not start in time

Yesterday, I had a customer blocked.

What happened?

We imported some objects and restarted the webservice so it would take the new objects. But the webservice didn’t start anymore. (BTW: they still use the classic environment but a lot of data comes by webservice.)

In the event-log, there was nothing from the webservice. Strange.

But I got some other messages stating that some entries in the registry were blocked by the webservice user and had been unblocked by the user login service (or something like that). What the ….????

What is going on?

Problem with the account used for the webservice? Tried another account. Same problem. So, not a problem with the user account.

Is it SQL Server somehow? Changed SQL Server setup and restarted SQL Server to see all logins (failed and succeeded). After a lot of retries to start the servicetier, I rarely got a login from it in SQL Server. So most of the time, the servicetier doesn’t even get to SQL Server.

Firewall, antivirus? Disabled. Still same problem. So this is not slowing down things.

With the strange messages in the event-log, I found that there was a 30-second time-out. But what kind of time-out?

After some duckbingling, I found that the service controller starts a service and that service should communicate to the service controller when it is completely started. If after 30 seconds the service controller doesn’t receive a message from the service, the service controller assumes that the service has failed and KILLS it!

Ahha! Now we have something!

Maybe if I can change that 30-second time-out to 2 minutes, the webservice has enough time to activate itself.

I found this post http://www.itgeekdiary.com/increase-the-service-timeout-period/ that helped me how to change the 30-second time-out. In short: I needed to add/change a key in the registry and then restart the server.

Done that. Restarted the server. And TADAAAAAA. The service is up and running and humming.

Doublechecking it inside NAV: I do some logging of webservice processes to see what they are doing and indeed, logs were coming in.

So, did I fix the problem?

No. I didn’t! But I did find a way around it.

The problem is not fixed, because that would mean having the webservice start under 30 seconds.

It is a some problem between the webservice and Windows. But I don’t have the knowledge to find out the problem.

 

But anyway, if my experience can help someone avoiding to be problem-solving from 17:00 to 22:30, then I would say, it has been a useful post.

Where are my keys?

Ever were searching for your keys? A colleague and me were searching for them once.

BTW: I am not talking about our car-keys or office-keys but about our NAV-keys!

And we found out that when a table is locked in NAV2013 (I didn’t check out NAV 2009R2 and NAV2013R2) by someone. If someone opens the table and wants to check the keys, they aren’t there.

NAV integer table in SQL-statement - improved

Maybe you remember my old post NAV integer table in SQL-statement.

It does work but has some drawbacks: it works well for small tables but get very fast very slow with a greater number of rows.

There are a lot of possible methods that are better. The best (most of the time) is the Itzik-Style Cross-Join.

WITH
  E1(N) AS (
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
           ),                          -- 1*10^1 or 10 rows
  E2(N) AS (SELECT 1 FROM E1 a, E1 b), -- 1*10^2 or 100 rows
  E4(N) AS (SELECT 1 FROM E2 a, E2 b), -- 1*10^4 or 10,000 rows
  E8(N) AS (SELECT 1 FROM E4 a, E4 b)  -- 1*10^8 or 100,000,000 rows
 SELECT TOP (4000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E8
;
For some other possible ways, I’ll point to the article I read about it.
Hidden RBAR: Counting with Recursive CTE’s

I forgot my administrator password

Ever had this problem? I just did in a virtual machine……

But I got it back using this link : http://pogostick.net/~pnh/ntpasswd/

I hope this is NOT helpful to you because you never need it. In the other case, I really hope it is helpful to you!

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!

Create a new blog and join in the fun!