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.
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.
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.
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.
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.