Upgrade to 2009 Classic or 2015 RTC, regarding SQL

mdPartnerNL

My customer has problems with the size and speed of the database. They are working with NAV 4.2 CLASSIC, source code and also need to upgrade.

A year ago I suggested to go for 2009 SQL but with a little extra effort (3 months of work) I can upgrade to NAV 2015 RTC too.

I will need to do a lot of optimizing the table indexes in 2009/SQL with an external consultant (im sure about this) but don't want to loose all this by redoing it in 2015/SQL.

What do you suggest: 2009 or 2015 ?

lvanvugt

SQL performance has improved with every version.

TonyDuarte

You could also set your target to 2013 R2, to let 2015 some time for problem solving.

mdPartnerNL

The code changes between 2013 and 2015 are not that big. So don't think this will give problems. The change in SQL from multiple users in 2009 to one SQL user in 2015 and how it all has improved in 2015 make me believe 2013/15 is faster and easier to upgrade to the next newer version.

bbrown

mdPartnerNL wrote:

... The change in SQL from multiple users in 2009 to one SQL user in 2015....

How do you see this making a difference? It's still multiple sessions. The fact that they all use the same credentials makes no difference. The one downside I'm seeing is it becomes harder to troubleshoot problems such as blocking and deadlocks. Since you can't relate SQL calls back to the users.

mdPartnerNL

From experience had a lot of problems after upgrading to 2009 with security problems but some could only be solved by giving more rights to a user then needed.

bbrown

mdPartnerNL wrote:

From experience had a lot of problems after upgrading to 2009 with security problems but some could only be solved by giving more rights to a user then needed.

Are you talking about security from the standpoint on NAV permissions, or SQL? In terms of NAV permission, those are still defined to the individual users. So not much changes there.

In terms of SQL, users have never needed more than the "Public" roles. The only exception being those involved with database management task. Such as importing new objects etc. But I will agree that the need to create only the NST accounts does simplify a new install. But also adds the step of cleaning all those "no longer needed" accounts out during upgrades.

mdPartnerNL

No, a lot of 2009 bugs with sql permissions are solved in 2015.

bbrown

mdPartnerNL wrote:

No, a lot of 2009 bugs with sql permissions are solved in 2015.

Can you provide some examples? I've yet to run into a single issue related to SQL permissions. In 2009, the users are still only members of the "Public" role. So what SQL permission issue could there be?

2013 does remove the delegation step. Is that what you are referring to?

What sort of SQL permission changes have you been making to solve your 2009 problems?

mdPartnerNL

Only changes in NAV security, and going to simple mode.

I was under the impression this is all improved in 2015 and that's why 2015 is faster then 2009.

bbrown

The choice of "Security Model" between Enhanced and Standard went away with NAV 2013. Since users no longer have any SQL permissions to the database, this setting no longer has any meaning. It really never had any impact on performance other than when you needed to synchronize users.

I have not spent a lot of time with 2015 yet, but my understanding is they have basically continued the performance improvements that were started with 2013 R2. I don't believe there have been any major platform changes. With 2013 some of the improvements have come from trimming down the standard indexes. But that doesn't always work for every site. Some larger sites, or those using certain features may sometimes need those indexes.

mdPartnerNL

Ok, clear, so 2009 and 2015 perform equal

bbrown

mdPartnerNL wrote:

Ok, clear, so 2009 and 2015 perform equal

That is not what I am saying.

thegunzo

You can always expect that the newest version is the best one all around and that is the case with NAV 2015.

BardurKnudsen

A minor clarification to bbrown's comment: "How do you see this making a difference? It's still multiple sessions. The fact that they all use the same credentials makes no difference.":

Well; it does make a difference. The key here is that the NAV server has a pool of SQL connections, so whenever a client needs to access the database, the NAV Server just grabs a free connection from the pool instead of having to create a new connection, and when it's done, the connection is returned to the pool.

bbrown

BardurKnudsen wrote:

A minor clarification to bbrown's comment: "How do you see this making a difference? It's still multiple sessions. The fact that they all use the same credentials makes no difference.":

Well; it does make a difference. The key here is that the NAV server has a pool of SQL connections, so whenever a client needs to access the database, the NAV Server just grabs a free connection from the pool instead of having to create a new connection, and when it's done, the connection is returned to the pool.

Ok. so you eliminate the time needed to establish a connection. But how does that improve the time it takes to execute the database task?

bbrown

BardurKnudsen wrote:

A minor clarification to bbrown's comment: "How do you see this making a difference? It's still multiple sessions. The fact that they all use the same credentials makes no difference.":

Well; it does make a difference. The key here is that the NAV server has a pool of SQL connections, so whenever a client needs to access the database, the NAV Server just grabs a free connection from the pool instead of having to create a new connection, and when it's done, the connection is returned to the pool.

That does help to answer the question of why I see all those database connection even when not that many users are active. Does the system eventually clean up those unused connections?

BardurKnudsen

Yes, the server will close unused sql connections after 3 minutes of being idle.
Time flies, so we often forget what actually is done in each version. For the database handling specifically, we have improved these areas since NAV2009:
- "Managed Data Stack": In NAV2009 most of the data access layer was still the classic, compiled C++ code, which in itself was fast, but the interoperation between the managed parts (.net) and the unmanaged parts (compiled c++) was very expensive. In NAV2013R2 there is nothing left of the old stack.
- We discontinued the classic database server in NAV2013, which allowed us to start optimizing solely for SQL.
- Caching has improved in each version.
- Connection pooling (as discussed earlier).
- Use of MARS (Multiple Active Result Sets) instead of cursors gives fewer SQL Server roundtrips and allows SQL Server more freedom to choose the best access plan.
- 'Autocalcfields' which creates sql queries that get record data and calculated fields in one query and hence only one server roundtrip.

-and probably some stuff I have forgotten :-)

bbrown

Thanks for the info. I have read about some of those changes.

I'm not saying that 2013 R2 is not faster. It definitely is in many ways. What I am saying is this does not mean you can simply upgrade any old system, with all its poorly designed modifications, and assume it will all be magically faster. Many time it will require some additional effort to achieve the desired results. In particular, resigning modifications to take advantage of these system improvements.

These new improvements also require some changes in approach to coding. For example, I've seen noticeable performance improvements by using Autocalcfields in certain situation. But if you don't change your code to use it, it don't help.

mdPartnerNL

BardurKnudsen wrote:

Yes, the server will close unused sql connections after 3 minutes of being idle.
Time flies, so we often forget what actually is done in each version. For the database handling specifically, we have improved these areas since NAV2009:
- "Managed Data Stack": In NAV2009 most of the data access layer was still the classic, compiled C++ code, which in itself was fast, but the interoperation between the managed parts (.net) and the unmanaged parts (compiled c++) was very expensive. In NAV2013R2 there is nothing left of the old stack.
- We discontinued the classic database server in NAV2013, which allowed us to start optimizing solely for SQL.
- Caching has improved in each version.
- Connection pooling (as discussed earlier).
- Use of MARS (Multiple Active Result Sets) instead of cursors gives fewer SQL Server roundtrips and allows SQL Server more freedom to choose the best access plan.
- 'Autocalcfields' which creates sql queries that get record data and calculated fields in one query and hence only one server roundtrip.

-and probably some stuff I have forgotten :-)

By discontinuing the classic client we also lost the option to compare the speed between 2009..2015 servers with real data. It's hard to make a decision ):