kriki: SQL Server 2014 and NAV

[Blog bot]

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/libr...=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/l...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/libr...=sql.120).aspx



Читать дальше