What are the pro’s and con’s of moving from Navision Navtive DB (version 2.60g) to SQL Server 2000 (version 3.6)? We are being told from outside consultants that this is recommended.
Hi, We could not make SQL work for US. It is very Sloooow. I would recommend you test it very carefully before you make this decision. My general recommendation you should stay with Native Database if you have less than 100 users. If you have more than 100 you should test, test and test before make this decision.
Hi Mr Brown. Some thoughts below: Pros for SQL: - More open environment for integrations, report/olap and such tools - Better security functionality when running 3.60 - Much better backup functionality, (standby server, point in time restore etc) - Can handle more users - Can handle more data - Better performance (!) on high volumes of users and data given that u have properly configured HW - Easier to set up test databases since You can have more than one database on the server without the nedd to modify services files and such - More db tools. Cons: - Need MUCH more hardware - A little bit more administration On the bottom line I think SQL is a better database environemt if You just configure You hardware correctly. But You ned a lot more CPU and RAM and need to think about how to configure You disks. For small installations with few users and no/small needs for integration C/SIDE is the better choice. Both databases is extremly stable. No matter how You do SQL will be more expensive. //Lars
Hi Lars, I would like to add to the Cons to SQL. There are a some (a lot?) differences in code behaviour on native versus SQL and they are little known. For instance: 1. When using keys: if the filtering sequence does not match exactly the key then the system will just use a table scan. The example in the manual is : let’s say we have a key for Customer Ledger Entry, Customer No., Currency Code. If you filter on Customer No. and Currency Code that the key will be used. If you filter just on currency code then the system will not be able to use the key on SQL and will revert to primary key. [xx(] 2. There are also issues with locking. While in theory is record level locking, in certain situation depending on the coding it ends up being effectively table locking. While that may not sound any different than native the problem is that native is faster at dealing with the db. 3. a.s.o. Despite the above things I am not necessarily advocating against SQL (even though I do not prefer it [;)]) All I am trying to say is that you need a thorough review of your system from a software side and not only hardware side. And as Val said you need to test a lot to make sure that you find where the trouble areas are and see if you can adjust them … Also while SQL backup routines are a lot simpler and faster we all know that you still need to take Navision backups also from time to time to test the integrity of the db. I find it that usually SQL gets pushed by the client’s IT guys who are likely to know SQL and they feel comfortable with it … But that comfort can have its drawbacks when the IT imports data straight into SQL messing up flowfields and everything else … [}:)] I think that the best reason for going to SQL is as Lars said: “More open environment for integrations, report/olap and such tools” If you plan on doing that kind of stuff then it may be useful. But then you have to make sure you do it the right way … Cristi
I fully agree with You Cristi. But the most common problem with SQL-implementations is that people underestimate the requirements that SQL has on HW.
Thanks everyone for your input. At this point I feel that I have no choice… the decision has already been made. Looks like I’m going to find out the hard way. One additional question… is it possible to use replication with Navision. Does anyone have any experience with this?
Hi M. Brown, I know it will take some time but feel free to share with us how it went after it is done … I have a feeling you will need someone to talk to anyway 
Cristi
quote:
Originally posted by mbrown0812
One additional question… is it possible to use replication with Navision. Does anyone have any experience with this?
No. You can’t use the replication feature in SQL on a Navision database. I think I read somewhere about someone who had done this, but i think it was some heavy modifications involved (something like adding a field in every table for replication purpuoses). //Lars
quote:
Originally posted by Lars Westman
But the most common problem with SQL-implementations is that people underestimate the requirements that SQL has on HW.
I have feeling that in some cases SQL can not work faster than Native DB. We have tested SQL with 64GB database. The speed does not improve whatever hardware we put in. We come to conclusion is the problem is how SQL version handles Flow Fields. SQL can handle only 4GB of cache size and it is not enough if database has big tables with big flow field keys. So SQL has to read data from HD.
Lars, Is this based on past experience or what you have heard? I am hearing from others that it should be possible because the replication is handled by SQL server. In fact, this the main selling point brought up by the consultants.
quote:
Originally posted by Lars Westman
- Better performance (!) on high volumes of users and data given that u have properly configured HW - Easier to set up test databases since You can have more than one database on the server without the nedd to modify services files and such
Lars, did you ever tried to rename a company on an SQL-Server? A proper configured SQL-Server running 70 companies took 3 1/2 days to rename ONE company! Jus my 2 cents
quote:
Originally posted by mbrown0812
Lars, Is this based on past experience or what you have heard? I am hearing from others that it should be possible because the replication is handled by SQL server. In fact, this the main selling point brought up by the consultants.
Sorry, one more. Yes, you consultants are right. Replication is handled by SQL server. Give that/these guy(s) 1000 bugs more per day! After you paid them, ask them how the replication is working with the Navision business logic. Sure you can replicate, but it makes no sense without modifying the business logic (e.g. some tables entries are related to the running number in other tables), adding more rules for replication (e.g. who / which database can delete a customer) and taking care of all the (re-) numbering stuff in Navision. While I am writing this I think you better give them 1500 bugs more per day. If this (replication) is the main selling point, the consultants get commission on purchased hardware and software. I don’t know what you want to do with Navsion, how many user/companies, db size etc. but replication (SQL) should not be involved in the selling of Navsion. Regards Walter
I have a funny idea come up in my mind after reading. The problem in SQL is more related to SIFT and company name. I am sure Navision will like to remain in the traditional way they run. But as they have been acquired by microsoft, they should think of better way to integrate with SQL. Something like can Navision use OLAP technology in SQL, company in the table name. As Walter said, the SQL option take a long time to just rename a company (although it is an occasional job). I think Navision should integrate with its parent company’s technology in more efficient way. Regards, Richard
quote:
Originally posted by mbrown0812
Lars, Is this based on past experience or what you have heard? I am hearing from others that it should be possible because the replication is handled by SQL server. In fact, this the main selling point brought up by the consultants.
No. haven’t tried it myself. Atually it seems to be possible, but I would advice You to be very careful. I think You or Your consultants could spend a lot of time here. You can read some more here: http://www.mbsonline.org/forum/topic.asp?TOPIC_ID=443&SearchTerms=SQL,Replication
quote:
I don’t know what you want to do with Navsion, how many user/companies, db size etc. but replication (SQL) should not be involved in the selling of Navsion.
Couldn’t agree more. The main reasons for SQL is M$-religion and integration (and be careful with both…) /Lars
Since I work with SQL, I can tell you that you can take a (few) cup(s) of coffee before you see the next screen. Wich is a great advantage. We tried everything, we have follow the Navision advices for hardware, SIFT, even in Denmark they cannot tell us why it’s so slow … They say it’s better with 3.6. Let’s wait .NET [8D]
Now that’s intresting! Please tell us a little more…
quote:
Originally posted by richtsang
I have a funny idea come up in my mind after reading. The problem in SQL is more related to SIFT and company name. I am sure Navision will like to remain in the traditional way they run. But as they have been acquired by microsoft, they should think of better way to integrate with SQL. Something like can Navision use OLAP technology in SQL, company in the table name. As Walter said, the SQL option take a long time to just rename a company (although it is an occasional job). I think Navision should integrate with its parent company’s technology in more efficient way. Regards, Richard
Agree 100%
Pro’s for SQL - the fact that one day Microsoft Business Solutions will no longer support a cside database … eventually! Basis _ Microsoft policy is to get companies & personal users to use latest software and phase old software out - eg. Windows95 is no longer supported by Microsoft. SQL Server replication will work but not the way users believe it will and not the way a ‘normal’ SQL database would replicate. A couple of guys I know (experienced Navision developers), have written their own type of replication which they called - synchronisation. This is for 3 separate sites running Attain 3.10b with SQL Server2000. Very complex and loads of issues but running and a good little system. Hardware requirements for SQL Attain - dual processor, loads of RAM and as many disks as possible - yes more expense, but in my opinion, a proper hardware setup and it also opens up possibilites of getting at the data using other tools.
It is not better with 3.60. Based on our experience 3.60 works even slower than 2.60.
Thats because a 3.6 application is x times more complex that a 2.6. If you look at the server calls required with 3.6 application areas compared to 2.6 you’ll see the reason for it being slower. If you compare like with like, i.e. a 3.6 c/side running your 2.6 application it is much faster against SQL Server than the 2.6 c/side because there has been around 18 months of c/side optimization in that period. However, the real performance benefits come in the application design, and there has been little (almost no) emphasis on improving the performance again SQL Server in that respect.