How Do You Upload a Sql Express Database to a Website

Get-go published on MSDN on Jul 19, 2017

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

About Me

Howdy, I'm James, I've just completed my first year studying Articulation Mathematics and Computer science at Imperial College London. I'g from Cheshire, equally well equally enjoying programming and video games, I've also played the piano from a young historic period and picked up the Trombone a few years agone.

Setting Upwardly Azure for Students

Sign upwards for an Imagine educatee account and verify pupil status to go free access to Azure web apps on the cloud at https://imagine.microsoft.com/en-usa/Account .

Then yous tin can get the educatee 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 gratuitous.

Setting upward 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 desire to ensure when creating the server that the trivial checkbox is ticked to allow Azure services to admission the server and then our web app can access it.

I have used an existing resources grouping in the above screenshot merely you volition probably want to create a new one, resource groups are more often than not used to group together resources for 1 project to assist with arrangement.

Now we will probably desire to add a table to our SQL database. A nice tool to assistance with this is the SQL Server Data Tools ( https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt ) or you can utilize the Query editor if you're confident with your SQL

Select your database, either from the dashboard or past navigating to SQL databases on the left console and choosing the database nosotros merely created. So choose overview and on the window that opens along the top in that location will be an choice chosen tools, click this.

A new window will then open giving us 2 choices, we can open the database in visual studio like I did to create a tabular array, 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 cull open in visual studio and once that opens nosotros volition be met with a connection window which will be filled in nosotros just demand to enter the password nosotros made for the server when we created it.

Later on connecting, there is a file explorer on the left. Utilise 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 information technology's very intuitive to add new columns - you just fill up in the details and it'll dynamically expand every bit you add more.

Once it is ready simply cull update in the peak left and one time information technology 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.

Subsequently opening the query editor, earlier nosotros practice anything we'll have to login using the credentials nosotros provided when creating the SQL server, and so 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 bulletin saying how the tabular array was affected by the code (mine said "Query succeeded: Afflicted rows: 6").

Tin can then output table to bank check if it all worked correctly.

We volition also need to then whitelist whatsoever IP connecting to this SQL Server or Azure will deny access as a security feature. To access this, we navigate dorsum to the overview of our SQL Database and along the acme where tools was we have another option "Set server firewall" and here we tin can easily choose to add customer IP, or manually add whatever ourselves.

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

If you're struggling with the code Azure makes information technology even easier past giving you example connection code for a lot of common languages. Again, on the overview choose "Bear witness database connexion 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 superlative of the screen like we did with the SQL Database/Server and choose an app proper name, this will be the start of your URL. Use the same resources grouping as you have used for the residue of the projection as this will help with organisation if you decide to host several websites which might use different SQL Servers or resource.

Then from the dashboard cull your App Service, then from inside your web app on the left console choose "Deployment options".

As our source, we are going to choose GitHub.

And so 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 substantially just git clone your repository into to wwwroot of the server. The all-time thing about using GitHub for this is that the server will automatically update when you push updates to your repository which makes it so unproblematic and efficient to update and issue issues fixes.

If you prepare your index webpage to exist in the superlative most directory of your GitHub repository and then your website should now be set, if y'all didn't you'll have to move some files around in the panel section of the Azure page (which I volition briefly demonstrate every bit I will be moving my database login details up a directory to exist completely certain it tin can't exist accessed past the web).

So, on the left panel of the App Service we have open whorl downwardly to the section developer tools and cull console - you should then accept a screen like this.

Here is the lawmaking I used to move the file upward a directory, I won't get into much particular about this section because it is standard bash commands.

If you lot 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 practiced way to do that is to become underneath console on the left console and cull Advanced Tools, then choose Debug console at the header bar and then CMD.

So merely navigate to the file you want to edit past clicking the site folder and and then wwwroot equally this is where your GitHub repository will have been uploaded and and then click the little pencil adjacent to the name.

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

If the proper noun you lot chose for your web app was "name" your URL will be "http://proper noun.azurewebsites.net" and your website should now be alive at this URL. The code for my website tin can be viewed at https://github.com/jtavernor/MyWebsite .

ruleynowild.blogspot.com

Source: https://techcommunity.microsoft.com/t5/educator-developer-blog/how-to-set-up-and-host-your-own-website-with-an-sql-server-on/ba-p/379000

Share this post