Final List of My Lectures – Fall 2016

I’ve already wrote about my sessions during this fall conferences, but now I have the final agenda for all conferences where I’ll attend. If you want to come on some of my lectures, I’ll be happy and I’ll try to help you as far as I can.

conferencesthisfall.jpg

Directions NA, Chandler, AZ, US

Directions EMEA, Prague, Czech Republic

MS Sinergija, Belgrade, Serbia

NAV TechDays, Antwerp, Belgium

Advertisements

NAV Gallery Image – PS Scripts Order

This week I have training about NAV and Azure and I used some examples in NAV 2016 Gallery Image on Azure.

NAV90GalleryImage.jpg

This image is awesome, but sometime people doesn’t know what PowerShell script can be started before some others. For example, you cannot run “Use Azure SQL Database” and after that “Install Multitenancy”…

Because of that I wanted to make some small help to all people who want to use this NAV 2016 image. In the following, you can find a chart with order of using all PowerShell scripts. Now, you know how to configure all of them on the same VM.

NAV90GalleryImageOrder.jpg

MS SQL is a Global ODBMS Leader

A few days ago, I’ve wrote about place of Power BI in Gartner survey about BI & Analytics solutions. Power BI was a global leader on Gartner Magic Quadrant. Now, we have new Gartner story about Operational Database Management Systems. For the first time, MS SQL is an absolutely leader and on better position in comparison with Oracle database.

ODBMS Gartner.jpg

The operational database management system (DBMS) market is concerned with relational and nonrelational database management products suitable for a broad range of enterprise-level transactional applications. These include purchased business applications, such as those for ERP, CRM, catalog management and security event management, and custom transactional systems built by organizations’ own development teams. Also included in Gartner’s definition of this market are DBMS products that support interactions and observations as new types of transaction. (from Gartner)

Microsoft deserves this position because they market its SQL Server DBMS for the operational DBMS market, as well as the Microsoft Azure SQL Database (a DBMS platform as a service), and the NoSQL DBMSs Microsoft Azure DocumentDB and Azure Tables.

It is very important that Microsoft continue with working on enterprise SQL Servers and on Azure SQL as well. And finally about NAV. Microsoft Dynamics NAV (from NAV 2016) works on both of these SQL versions and I think this cooperation between strong database and ERP solution will continue to deliver amazing business solutions.

Administering Azure SQL

Now we have Microsoft Dynamics NAV 2016 on Azure SQL Server, I’m getting a lot of different questions about this topic. One of them is related with database administration.

If you want to administer Azure SQL Server, you can do it through SQL Server Management Studio. You just need to use your full Azure database name (YourDatabaseName.database.windows.net) and login to database using SQL Server authentication. Of course, previously you have to add your IP address to Azure SQL firewall.

But unfortunately, you cannot use SQL Server Profiler for Azure SQL (maybe in future, but currently not). You can use some other useful tools, for example Index Advisor.

Also, you cannot use SQL Agent on Azure SQL. The most of tools we used to are not available. But, instead SQL Agent we have feature on Azure as Scheduler. To use this, you need to create your new mobile service on Azure for your database. More about this tool, you can find here and here.

Development Environment on Azure SQL

After my session on NAV TechDays about deployment of NAV 2016 on Azure SQL Server, I got a lot of questions about using Development Environment in this situation.

Using Development Environment can be done absolutely normal. If you want to use DE from your local computer, first you have to add your IP address to Azure SQL firewall (SQL Databases > Servers > choose server > Configure). You can do it to allow connection between your computer and Azure SQL server.

DE01

After that, open Development Environment and type your full Azure SQL server name to Server Name (xxxxx.database.windows.net), choose Database Server Authentication, your username and password and on the end normally choose your database.

DE02

You can work with design, but if you want to open table for example, you have to make some additional configurations. Before it, you have to make connection between your local NAV Windows Client and Azure VM. You need to use certificate and encryption key. After you configure them, everything will be OK.

If you want to use Development Environment directly on Azure VM, you just need to open DE and login to Azure SQL server (I already described), nothing else. In this situation you can work what you want.

DE00

You can see it on previous picture. Database is opened from Azure SQL and you can do everything as you used to.

Cannot Connect Web Client to NAV on Azure SQL

In my last two articles, I’ve showed you how to deploy Microsoft Dynamics NAV 2016 on Azure using Azure SQL Server. Now, if you already finished it any you want to use NAV Web Client, you have to install Web Server components on VM you configured as service tier and create Endpoint for WebClient on this VM, opening port 8080. Now you have to login with different username (for example, Admin username you already used on your VM). Now, you have to type the following URL: http://<AzureVM_Name>.cloudapp.net:8080/DynamicsNAV90/WebClient/

If you have problem with connecion and get the error message, this is probably because port 8080 is not opened. If you want to open this port, you have to open Remote Desktop Connection to your VM and run the following: Control Panel > Windows Firewall > Advanced Settings > Inbound Rules.

AzuWC_CP_Fw.png

If you can find “Microsoft Dynamics NAV 2016 Web Client” as rule, you need to add 8080 as Local Port. But if you cannot find it, create a new rule (using the command in the right pane) and add this name and local port.

How to Deploy BACPAC on Azure SQL

This topic is in connection with my session “Deploying and Managing Dynamics NAV 2016 with Azure SQL Database” on NAV TechDays 15 and I want to continue my previous article.

Before I start with BACPAC deployment, first I have to make a small correction of my previous article. Last time, I forget one important thing. Before you create BACPAC file, you have to delete all Windows users from your source database.

You can do it in following way. In SQL Server Management Studio, find your source database and collapse it until you find Security > Users. Then check all users (right-click > Properties > General).

You need to check User Type field; if you find Windows User, you need to delete it. Right-click on this these users and choose Delete.

sql1

Now, you can continue with all steps I’ve already described in previous article. Now, we can start with deployment.

First, we have to have Azure subscription and create Azure SQL Server. When you open your Microsoft Azure Portal (https://manage.windowsazure.com), choose SQL DATABASES and choose SERVER tab. Click ADD and type your Login Name, Login Password, Region and click on Complete.

sql2.png

You will get some weird name as Azure SQL Server. Click on this SQL Server, go to DASHBOARD tab and copy this SQL Server name. Add .database.windows.net to your SQL Server name; this will be your full SQL Server name. Now, go to CONFIGURE tab and Add Allowed IP address to Azure SQL firewall. This step is necessary if you want to use this SQL Server from your local computer (with this step, you added your computer IP address to Azure). Every time you want to connect from some other computer to your Azure SQL Server, you have to repeat this step.

Now, you need to make connection from your SQL Server Management Studio to your Azure SQL Server. In Server Name, you need to type your full Azure SQL Name and your Login and Password.

sql3

When we make connection, we can start with deployment. Go to Database, right-click and choose Import Data-tier Application. You need to choose your BACPAC file, your target Database file and continue until system finish with deployment.

sql4.png

Now we can check result. We can find new database on SQL Server Management Studio on Azure SWL connection. Also, we can see in on Azure Portal; choose DATABASE tab on SQL DATABASES.

In process of deployment, it is possible to have one issue. This is situation if you can only select the obsolete service tiers of Azure SQL Database, Web and Business, with a maximum database size of 150 gigabytes (GB), when you try to set target database. Of course, in this situation, there is no option to select the new service tiers, Basic, Standard and Premium. If you try to upload on obsolete service tiers, you will get the error message.

This issue occurs because SQL Server Management Studio 2014 is not updated to match the current service tier offerings. This issue was first fixed in the Cumulative Update 5 for SQL Server 2014. You just need to install this CU 5 and repeat deployment step.