How to Set-up and Host Your Own Website with an SQL Server on Azure for Students

Guest post by James Tavernor, Microsoft Student Partner from Imperial College London

About Me

Hi, I’m James, I’ve just completed my first year studying Joint Mathematics and Computer Science at Imperial College London. I’m from Cheshire, as well as enjoying programming and video games, I’ve also played the piano from a young age and picked up the Trombone a few years ago.

Setting Up Azure for Students

Sign up for an Imagine student account and verify student status to get free access to Azure web apps on the cloud at https://imagine.microsoft.com/en-us/Account.

Then you can get the student Azure offer at https://azure.microsoft.com/en-gb/pricing/member-offers/imagine/.

Once these steps have been completed go to https://portal.azure.com where you should now be able to access the Azure portal for free.

Setting up the SQL Server and Database

We are going to set up an SQL server and host an SQL database on this server. To do this choose SQL databases on the left panel.

Then choose the add option. You will then be prompted to choose the name for your SQL database and for an SQL server to host the database on, though you can just choose to create a new server.

You’ll want to ensure when creating the server that the little checkbox is ticked to allow Azure services to access the server so our web app can access it.

I have used an existing resource group in the above screenshot but you will probably want to create a new one, resource groups are generally used to group together resources for one project to help with organisation.

Now we will probably want to add a table to our SQL database. A nice tool to help with this is the SQL Server Data Tools (/en-us/sql/ssdt/download-sql-server-data-tools-ssdt) or you can use the Query editor if you’re confident with your SQL

Select your database, either from the dashboard or by navigating to SQL databases on the left panel and choosing the database we just created. Then choose overview and on the window that opens along the top there will be an option called tools, click this.

A new window will then open giving us two choices, we can open the database in visual studio like I did to create a table, or you can open the query editor to run SQL Queries on the database.

To create a table in Visual Studio using the SQL Server Data Tools is easy, we choose open in visual studio and once that opens we will be met with a connection window which will be filled in we just need to enter the password we made for the server when we created it.

After connecting, there is a file explorer on the left. Use this to navigate to your database and then into the “Tables” folder inside the database and click add new table. A new page will load up and it’s very intuitive to add new columns - you just fill in the details and it’ll dynamically expand as you add more.

Once it is ready just choose update in the top left and once it has prepared the script choose update database.

After creating the table in Visual Studio and updating the database I then used the Query editor on Azure to add values to my table.

After opening the query editor, before we do anything we’ll have to login using the credentials we provided when creating the SQL server, and then we can enter SQL commands and execute them on our database.

Click run to execute the SQL on the database.

The window below the query editor should output a message saying how the table was affected by the code (mine said “Query succeeded: Affected rows: 6”).

Can then output table to check if it all worked correctly.

We will also need to then whitelist any IP connecting to this SQL Server or Azure will deny access as a security feature. To access this, we navigate back to the overview of our SQL Database and along the top where tools was we have another option “Set server firewall” and here we can easily choose to add client IP, or manually add any ourselves.

To connect we can then use standard PHP connection code, we shouldn’t need to whitelist an IP when we host the web app because we checked the box to allow connections from Azure when we created the SQL server.

If you’re struggling with the code Azure makes it even easier by giving you example connection code for a lot of common languages. Again, on the overview choose “Show database connection strings”.

Uploading Your Website to Azure

I’m now going to upload my PHP website to Azure which will then connect to the SQL database we created.

First navigate to App Services.

Choose the add option at the top of the screen like we did with the SQL Database/Server and choose an app name, this will be the start of your URL. Use the same resource group as you have used for the rest of the project as this will help with organisation if you decide to host several websites which might use different SQL Servers or resources.

Then from the dashboard choose your App Service, then from inside your web app on the left panel choose “Deployment options”.

As our source, we are going to choose GitHub.

Then authorise Azure to view your repositories and choose the repository of your website from GitHub and which branch you want to publish and continue.

This will essentially just git clone your repository into to wwwroot of the server. The best thing about using GitHub for this is that the server will automatically update when you push updates to your repository which makes it so simple and efficient to update and issue bug fixes.

If you set up your index webpage to be in the top most directory of your GitHub repository then your website should now be set up, if you didn’t you’ll have to move some files around in the console section of the Azure page (which I will briefly demonstrate as I will be moving my database login details up a directory to be completely sure it can’t be accessed by the web).

So, on the left panel of the App Service we have open scroll down to the section developer tools and choose console - you should then have a screen like this.

Here is the code I used to move the file up a directory, I won’t go into much detail about this section because it is standard bash commands.

If you need to edit a file (like I did to put my database login details as I didn’t want to upload them to GitHub) a good way to do that is to go underneath console on the left panel and choose Advanced Tools, then choose Debug console at the header bar and then CMD.

Then simply navigate to the file you want to edit by clicking the site folder and then wwwroot as this is where your GitHub repository will have been uploaded and then click the little pencil next to the name.

And then just edit as though it were a normal text file.

If the name you chose for your web app was “name” your URL will be “https://name.azurewebsites.net” and your website should now be live at this URL. The code for my website can be viewed at https://github.com/jtavernor/MyWebsite.

Comments

• Anonymous
  July 19, 2017
  this is so helpful for me to setup SQL server. thanks for sharing such helpful article