NAV Team: Dynamics NAV and SQL Server Azure

[Blog bot]

With more and more focus on cloud computing and cloud services, I have been asked several times about the possibility of using Dynamics NAV with SQL Server Azure. So I decided to write this blog entry to help "shed some light" on the topic.

What is SQL Server Azure you ask? SQL Server Azure is the cloud version of Microsoft SQL Server. The difference is that it's a cloud database running in Microsoft datacenters around the globe. High-availability and fault tolerance are built in.

Even though SQL Server Azure is based on the SQL Server you are all familiar with, there are some important differences that directly affect its ability to be used a database platform for Dynamics NAV that need to be reviewed and considered. In this post I will go over some of the important differences between the two database platforms and their implications for Dynamics NAV.

Differences That Affect NAV

SQL Server Azure has a 50 gigabyte limit per database. This is because SQL Server Azure is based on a "scale - out" database "sharding" model where the data for a single application is no longer stored in one monolithic database but broken up over many smaller databases allowing Azure to scale across many "physical" servers for much higher scalability. The issue with this is that Dynamics NAV (or any of the Microsoft ERP products for that matter) is not architected to support database "sharing" as this is a relatively new concept for SQL Server, which for years has followed a "Scale Up" instead of "Scale Out" model. Dynamics NAV's data engine would have to be completely re-architected to be able to support this model. So if your NAV database is over 50 GB or will ever be over 50 GB, using SQL Server Azure as a database platform for NAV is not currently possible due to its application architecture.

The initial database creation is handled via TSQL scripting or the SQL Azure Portal. The SQL Azure portal will allow you to create new empty database but not restore a database from back-up. Currently in NAV you can create the Cronus database by restoring the provided SQL back-up on the server. You are also able to create a database and do the necessary coding and configuration and then restore it into the production environment. On SQL Azure these two activities are not currently possible. In order to restore the Cronus database or development database into a SQL Azure environment you would need to script the creation of all objects: tables, views, indexes, functions, and so on and also script the importing of data via TSQL, BCP, or SSIS. This can be done and once you get used to it probably would not be much of an issue, but for people not that familiar with TSQL scripting this could present a significant challenge.

When using an on-premise SQL Server, you can set collations at server, database, column, and expression levels. SQL Azure does not allow setting the collation at the server or database level. To use the non-default collation with SQL Azure you need to set the collation at the column level or the expression level. This could pose a major issue for Dynamics NAV installs not using code page 1252.

SQL Azure does not support tables without clustered indexes. A table must have a clustered index. If a table is created without a clustered constraint, a clustered index must be created before an insert operation is allowed on the table. This is not a big issue as the standard NAV database has clustered indexes on all tables, but could be an issue for people who use staging tables in Dynamics NAV for data migrated from other data sources as these types of tables perform better without a clustered index.

Note: Currently Dynamics NAV does not support SQL Azure, but we are evaluating it for possible future use.

Michael De Voe

Senior Premier Field Engineer

Microsoft Dynamics

Microsoft Certified Master - SQL Server 2008



Источник: http://feedproxy.google.com/~r/Micro...ver-azure.aspx