Setting up Microsoft Azure SQL Database to use with Power BI

So for full testing of Power BI, you can setup a Microsoft Azure SQL server database using the Adventure Works DW 2019 sample. This will allow you to connect from the Power BI service as it cannot connect easily to your laptop (you would need to configure a gateway etc).

So, go to azure.com to create an azure account (with an outlook/hotmail address). The free account gives you $200 of free credit and 12 months access to popular services. Here you can get a low cost cloud SQL server, which Power BI service can connect to without configuring a gateway and also you don't need to worry about losing it etc.

When you have a subscription, the first thing to do is to create a resource group which is basically a folder for related resources. Tip: You cannot rename a resource group.
You don't have to create a resource group, but if you are going to create a lot of things, then its probably worthwhile getting organised.

Inside the resource group (or outside if you didn't create one). Select Create > SQL Database > Create
Tip: When you create your first SQL database, you will be prompted to create a server (create new) as well as the SQL database. I could not see how to just to create a server on its own. So in my example. I will delete the first test database created during setup and then deploy the Adventure Works DW 2019 sample database later.

To create the server, enter the database name (I use Test here as I am going to delete it later), admin username and passwords.
Then you will be asked to create a database. Enter the database name and click on configure database > 

The deployment defaults to install the lowest spec server (basic 5 DTUs), estimated £4.90 a month. The DTU is a capacity measure of performance. You don't need anything for testing, so just select the basic.

There are several other tabs:
Networking: I just leave the defaults
Security: I just leave the defaults, but if you have a production server, you will want to review these options.
Additional Settings: You can choose to restore from a backup or to install the sample database.
Tip: You can install the Light weight Adventure Works LT 2019 version, but it only gives you a minimum amount of tables, so I would deploy the full Adventure Works DW 2019 data warehouse.

Click Review and Create to create the database. It will take a few minutes, so just be patient.
Tip: as you start with $200 of free credit. But remember you will get billed later, so be sure you haven't created resources you don't need (you can just delete anything you aren't using).

As I'm planning on deploying the Adventure Works DW 2019 sample from SSMS to my windows azure account, I am going to delete the Test Database I created first (so I don't have to pay for 2 databases).

Configuring the firewall to allow you to access Azure

Select your server from the Microsoft Azure home page (Home > server name)
Select Networking > Show network settings
Scroll down to firewall rules > It should show you the IP address that you are connecting in on and you can add this to the firewall (add your client IPv4 address). Click SAVE
If you don't add your IP to the firewall, you won't be able to connect with SSMS.

Installing Adventure Works DW 2019 to Azure

I had already a copy of the AdventureworksDW2019 sample database installed on SQL server express and a connection from SSMS on my local machine.

Instead of creating the database in the azure portal, you can deploy a database from SSMS.
The pre-requisite to this is to install the AdventureWorksDW2019 sample database on SQL server Express on your local machine. This is easy to setup.

Assuming you have the AdverntureWorksDW2019 installed on your local machine, you can deploy it to the cloud as follows:

Open SSMS and connect to the local SQL server Express database on your machine. Then connect to the local copy of SQL server Express.

Right click on the AdverntureWorksDW2019 database and select Tasks > Deploy Database to Microsoft Azure SQL database.

There is a simple wizard to walk through to deploy the database. To get the server name, go to your windows azure portal and select SQL server database.

Tip: To get the server name you can go to the windows azure portal and select your sever. 
The server name will be shown on the right hand side.
It will be in the format servername.database.windows.net

It will take some time to deploy the database, so just be patient.
Once complete I go to the Azure homepage and select databases. I now have 2 databases installed, so I delete the Test database that was installed when I started as I only want to pay for the Adventure Works DW 2019 database I installed.

Once it is installed, you can then try connecting to it with SSMS and run some queries.

You will then be able to test your data flows from Power BI, by connecting to your new azure SQL server database (no gateway configuration required)