OK, over the last couple of weeks I have been asked, more than once, ‘so if I go to SQL, what can I do, that I can’t do on Native’?
I started to ponder on this, did a bunch of searching, but couldn’t find a definitive list.
I thought I would get your thoughts on this and put my two cents and questions in as well.
SQL Reporting Services - feasible at this time?
Business Analytics - Does this work with Nav5 / SQL 2005? Any issues?
Notification Services - Does anyone use this?
NAV / Sharepoint - I read that Employee Portal is extremely limited, do I have any other options at this time?
Active Directory management - this can be a huge plus for managing security. In addition to streamlining an enterprises security it also allows for passwords that expire.
SQL Backups - incomparably more effective as part of Disastor Recovery model because of the speed of the backup and restore process. Backups can also be taken while the db is in use (without using the less than reliable Hot Copy).
3 . SQL Reporting Services is definitely feasible. Of course, there are times when NAV reports do the job more easily.
Analysis Services - the best value for data warehousing as far as I am concerned. I have tried Business Analytics, but they never seemed to get the cubes quite right in any of the earlier versions, so I suspect 5.0 is no exception. I would give the Analysis Services a try anyway – the front end makes it easy to get your foot in the door of building your own cubes.
Finally, perhaps the most compelling reason of all, is that SQL is an industry standard database. There are many more people who understand how to use SQL and SQL tools than there are people who get C/SIDE. The way this drives down risk/cost of ownership for an organization should be slam dunk alone. M And of course, being standardized means that it integrates more easily with 3rd party tools.
One point that I am not mentioning is performance. Mainly this is because going to SQL is a pretty good idea even without considering performance.
Without getting into a big debate about which actually performs better, I don’t think there can be any doubt that SQL will perform better if the scale is large enough. This is due to three (of many) factors:
Record level locking vs. Table level locking
More efficient use of hardware - drives and processors
the query optimizer will select the best key for you
Personally, the performance issue is factored by size of installation. I have worked with companyies that were happy to stay with the native database because there were maybe 5-10 users and the company did not want the expense of upgrading to SQL Server - and performance was acceptable. However, ‘larger’ companyies will ‘possibly’ benefit from using the SQL option as it ‘should’ perform better with larger user base systems. I use the words ‘possibly’ and ‘should’ because I have seen, SQL installations performing terribly due to incorrect setup and non-existant maintenance & re-indexing plans. Of course, this is self explanatory and I dont want to appear to be patronising anyone reading this post. [:$]
Finally, the positives for SQL Server as mentioned are; reporting services, scalability & future proofing your installation (SQL Server is the industry standard database).
Besides the advantages mentiones above, working on Sql gives you many more possibilities to integrate with third party tools. Using the ‘native’ database you have ODBC, or the NAS or CFRONT, but these are not so straithforward.
Reporting Services is really nice and ‘easy’ to integrate, but requires a little knowledge of writing sql queries. The NAV 2006 will integrate with SSRS, but only opto a certain level.
Speaking of NAV 2006, if you want to use the new role tailored client, you will have to be on Sql Server…
Of course Sql server requires a little more maintenance, and maybe in the beginning some finetuning, and most of this can be ‘automated’ using maintenance plans.