Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.5K Microsoft Dynamics NAV
- 18.5K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 267 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 992 SQL General
- 384 SQL Performance
- 33 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Options
Moving Navision 3.70 to new SQL Server installation
zaurb
Member Posts: 26
Hi!
We currently run Navision 3.70 on SQL Server 2000 and Windows 2003 Server 64 bit edition with 4 Gigs of RAM in VMWare ESX server 3.5.
I have moved the existing Navision installation to a new test vm with Windows Server 2003 64 Bit, 4 vCPU, 8 Gb RAM with Dynamics NAV 5.
To accomplish this I've done the following:
1) Installed + configured Windows 2003 Server 64 bit with latest MS updates.
2) Installed SQL Server 2008 Std with SP1 and updated through Windows Update.
3) Installed Navision Application Server.
4) Backud up Navision database in old installation using Navision client 3.7.
5) On new VM using the Dynamics NAV 5 client created a new database and restored the backup of Navision 3.70 to a new database.
Questions:
1) This is the first time I do installation of Navision. So, I would really appreciate any suggestions if the procedure I've followed so far is correct.
2) The database size on the old installation is somewhat 170 Gb, after restoring this db to a new server, the db size is around 70 Gb only. Why?
3) When I start a Dynamics NAV 5 client to connect to a new installation the connection time takes around 5-7 seconds vs 1-2 on the old server with client 3.7. Why?
4) When I do "synchronization of all users" from the Dynamics NAV 5 client (Tools->Security-> Synchronize All) it takes more than 1 hour... On old server 1-2 seconds.
5) What other procedures should I have done in this scenario? Both on SQL Server 2008 and within Dynamics NAV 5 client.
Thank you very much in advance!
We currently run Navision 3.70 on SQL Server 2000 and Windows 2003 Server 64 bit edition with 4 Gigs of RAM in VMWare ESX server 3.5.
I have moved the existing Navision installation to a new test vm with Windows Server 2003 64 Bit, 4 vCPU, 8 Gb RAM with Dynamics NAV 5.
To accomplish this I've done the following:
1) Installed + configured Windows 2003 Server 64 bit with latest MS updates.
2) Installed SQL Server 2008 Std with SP1 and updated through Windows Update.
3) Installed Navision Application Server.
4) Backud up Navision database in old installation using Navision client 3.7.
5) On new VM using the Dynamics NAV 5 client created a new database and restored the backup of Navision 3.70 to a new database.
Questions:
1) This is the first time I do installation of Navision. So, I would really appreciate any suggestions if the procedure I've followed so far is correct.
2) The database size on the old installation is somewhat 170 Gb, after restoring this db to a new server, the db size is around 70 Gb only. Why?
3) When I start a Dynamics NAV 5 client to connect to a new installation the connection time takes around 5-7 seconds vs 1-2 on the old server with client 3.7. Why?
4) When I do "synchronization of all users" from the Dynamics NAV 5 client (Tools->Security-> Synchronize All) it takes more than 1 hour... On old server 1-2 seconds.
5) What other procedures should I have done in this scenario? Both on SQL Server 2008 and within Dynamics NAV 5 client.
Thank you very much in advance!
Zaur Bahramov
0
Comments
This is due to different technology used to calculate SIFT fields. NAV versions prior 5.0 SP1 used additional tables to precalculate and store values of SIFT fields, NAV5.0 SP1 and later uses indexed views to do that.
? there could be due to many reasons... protocol selected to connect NAV client do SQL database... computer settings, server settings, network settings... Anything.
If you close NAV client and re-open and connect second time to the same SQL server does it take similar amount of time or much shorter ?
You've used Enhanced security settings. Switch it back to Standard.
Test, test, and test test literally everything... Double check NAS server based solution if you use any, as some components does not work in x64 environment. Touch every single piece of your new installation. check entering documents, journals, posting, reporting.. Also test performance.
How do you organize this in procedures is up to you.
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
2) I see, so that explains why the db size is so little with respect to the original db. Also I did a dbcc reindex on huge tables from t-sql and the db file size grew to 80 gb.
3) Connection time resolved with switching to Standard security model from Enhanced model.
5) I will do testing of all procs together with pilot users for this project, but I wonder if there're any optimizations to be made directly on sql server and/or navision performance wise.
Thank you very much for your help!
Regards,
Slawek
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
Not yet really. I've just installed the Windows Server 2003 / SQL Server 2008 and made restore from fbk files created by Navision 3.70 client into a new database on a new server using Dynamics Nav 5.1 client.
As for the performance testing, I will procede with this the next week. What I was actually planning to do is to determine at least one key user in each department, like sales, accounting, marketing, etc, install a Dynamics NAV client on their computers and link them to new server and ask them to run their normal working procedures for testing processes.
This was actually my performance testing plan as of DBA.
As for customizations and localization of Navision itself I can't do anything here, since am not a Dynamics programmer. Portability of this stuff is up to our cunsultants.
My first guess was the network. More precise - problem with primary DNS server. If primary DNS is dead then any first-time TCP/IP connection takes much longer (DNS query made to primary server must timeout, then another query is send to secondary DNS), but subsequent connections are fast due to local DNS caching. On the other hand 'much longer' is usually 15 sec (with standard Windows TCP/IP configuration), 5 - 7 sec is too short.
Now, knowing the solution source seems to be quite obvious
Slawek
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
Just checked that 5.1 actually is 6.0 which is NAV 2009.
NAV 5.1 was the name used long time ago by Microsoft (even before NAV 5.0 was launched). Then it was named NAV 6, and finally arrived as NAV2009
So we have :
NAV 5.0 - quite old stuff now, uses the same technology as all previous versions (I mean SIFT tables)
NAV 5.0 SP1 - smaller database due to implementing SIFT as indexed views, generally much faster, but...
NAV 2009 - same as 5.0 SP1 (from DB point of view), but introduces 3 tiered architecture, kind of 'thin' client, web services
NAV 2009 SP1 - the newest stuff. Many errors fixed
Updating to NAV 5 SP1 if fairly safe, however, as David suggested, it might suprprise with some performance problems.
NAV 2009 uses the same technology (clasic Client), but introduces some othes surprises (like case-sensivity in text comparison statements), so updating to this version is a bit more dangerous. Or it just requires a bit more detailed testing. On the other hand it opens new possibilities of application integration thanks to 3 tiered architecture.
Slawek
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
[-o< [-o< [-o< [-o<
Good luck with that. :-#
I know I know \:D/ Sounds funny.
But I'm not an expert in Navision (yet). I just really need an advise on how to procede...
There was a project last month where the client moved from 3.70 to 5.00sp1. Everyone did their testing, all was fine, they went live, and after a week the performance was so bad they went back to 3.70 and reentered the last weeks transactions again.
With 220 users that was quite a task.
Trust me you really want to test this first. Your plan at the moment is a plan to fail.
BTW my involvement was the day before they went live, when the client had some doubts and called me. Once they described what they were planning and what testing they had done, I suggested the only option was to abort the upgrade for a month to do testing.
Mind you I have also seen many upgrades like this that just worked first time no problem. BUt people never call me when things are good, the call me when the system is on fire and they have no other option. So I normally only see the disasters. Lets hope you don't need to call me
Best advise I can give is call a Navision expert and get them to test it for you before you go live. But I get the feeling that you don't have the budget for that.
Then the company providing assistance for Navision itself will do the rest.
That-s why am interested mainly in setting up the working environment for future upgrade.
How should I troubleshoot the SQL Server installation to find out the reason. I tried to do the following, however, I don't know how exaxtly interpret results:
///////////////////////////////////////////////////////////////////////
How should I interpret results of the above commands? What are the best ways of identifying the reason of the low performance?
Thank you!
Our systsem is Windows Server 2008 R2 x64 which in fact is a VM on ESX 3.5 . We are going to do an upgrade to vSphere soon, since ESX 3.5 has only an experimental support for Windows Server 2008.
As for the database it's about 150 Gb, log file is on a separate disk.
RAM 8 Gb - I've configured Minimum memory on SQL as 0 and max as 6656. This was I give 1.5Gb to OS and the rest 6.5 Gb to SQL.
After restoring an fbk to sql 2008 I created several maintenance tasks as follows and ran them:
1) Update statistics
2) Rebuild and reorganize indexes
3) Integrity check
Compatibility level set to 100.
In general that's all. Also, in the very beginning I've configured the paging file as about 12Gb both min and max size, but then noticed in the Task Manager -> Performance that the Memory column was all green (7.6Gb), i.e. using all RAM for some reason, even when server was idle. I've set the paging file to be managed by windows, and ot it's between 2.6-3.2Gb even when procs are running.
I've asked my chief to re-launch his procedure, that was usually running 18 hrs on old server, and that didn't complete even after 4 days on a new server, and will see what happens.
Can you advise smth else on server configuration? What counters should I use in these scenario, which DMVs to run and which values to read/compare?
Looks like you have a set of custom developed stored procedures to retrieve system data. Was that a custom job just for you or did you get some sort of toolset? Have you tried to get the people/company that wrote those to come in and give you some support? I think 24 hours to run any job on SQL Server is too much, let alone 4 days.
RIS Plus, LLC
MVP - Business Apps
What happens is the following: Our Navision supporting company has told me to backup database using Navision 3.70 on our production server, then restore it on windows 2008/sql server 2008 server using the client 5.0 SP1.
That's what I've actually did. Then my chief started his procedure which he usually runs on SQL Server 2000/Navision 3.70 and this procedure runs extremely long time.
So, I just need to figure out, whether this is due to the bad configuration of SQL server or actually inside this procedure and if so, ask our Navision supporting company to take a further look and make modifications as neccessary.
My problem now is that I can't identify the source of problem... ](*,)
In general, when you are faced with implementing best practices, you should get someone involved that knows what they are doing. If I were you, I would get your partner involved, and get them to look at your system. If they don't know about SQL Server or virtualization, then find someone who does know about this.
RIS Plus, LLC
MVP - Business Apps
I've checked one by one the settings resulted by:
EXEC sp_configure 'Show Advanced Options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure;
on both servers. They are similar.
So, once again, I know, what could be the problem, but I need some clue to resolve it. The clue could be one or all of the following:
1) Version of ESX server, which is 3.5 on which we run Windows 2008/SQL 2008 64bit
2) Settings of Windows 2008 and Sql 2008.
Since the procedure which I've described is really simple and it runs on both servers without errors, there's a very little chance that something is wrong with this procedure itself. I'm asking for help whether someone has already encountered such latencies on Windows 2008 server R2 x64bit and SQL Server 2008 Std environment in the ESX 3.5 context. I know, maybe it's kind of specific to server administration rather than navision programming, but I was hoping that someone could help me to identify the problem.
If necessary, I can write here more details about the procedure, even post the code behind forms. But am just absolutely not sure that the problem lays in C/Al and procedure itself...
RIS Plus, LLC
MVP - Business Apps
I've also checked on SQL Server 2000 and these values are very low on the production server.
I've checked the forum and noticed that some other people also have had the similar problem with these SPs when migrating a DB from 2000 to 2005. In our case it's SQL Server 2008 though.