Part 1: Creating SQL Server Virtual Machine on Windows Azure (using the new portal)
|1. Login to Windows Azure Portal|
|2. Click New in the lower left corner|
|3. Click SQL Se3rver 2014 Standard|
|4. Enter Host Name, User Name, Password (Host Name must be unique system wide, username cannot be “admin” password must be complex)|
|5. Click Pricing Tier if you want to change the size (Standard A2 is recommended and the default. This machine has 2 cores, 3.5gb memory, 4 data disks 2000 IOPS, load Balancing and Auto Scale capabilities)|
6. Click Network, Storage, Diagnostics
7. Review OS Settings, Availability Set, Network, Storage Account, and Diagnostics. Make changes as desired. Defaults should be fine unless you are connecting this server to other servers already on Windows Azure. If you are, you will want to make sure you chose the same network settings as the other servers. Especially note the Domain Name. This is the URL that you will use to access the server. You can change it now if you like.
|8. Click Location then select the location where you would like your server hosted.|
|9. Click Create|
10. It will take about 5 mins or so to create the virtual machine. Once it is created, we will need to start it, configure the drives, and install and configure the Adventure Works Database
While it is working, there will be a tile on your dashboard …
|11. Once Finished, we need to add login to the machine. You do this by click The Server Tile (in my case it is SQLAdvWrks) then click Connect on the top of the screen|
|12. Click Open|
|13. Click Connect|
|14. Click Use another account|
|15. Enter the credentials you created when you created the machine in part 1 above|
Part 2: Setup SQL Server
Part 3: Download, Install & Configure Adventure Works Database
We will need to download Adventure Works from Download Adventure Works 2014 For this exercise, we will do the Adventure Works 2014 OLTP Script.zip which will create the database for us. You may want to download some other components as well. It is easier to just download the database backup but there is much to learn from taking a look at the script that creates the database.
|1. We need to turn off protected mode on IE in order to download.
Click Server Manager – Local Server – IE Enhanced Security Configuration On
Turn it off..
When you browse the web, you will get a warning message that Protected mode is not on. Just ignore it for now, you can turn it back on later when you are done downloading.
|2. Open your web browser, go to Bing.com and search “Adventure Works 2014 Sample Databases” and select the CodePlex link (likey first) or you can simply go to http://msftdbprodsamples.codeplex.com then select Adventure Works 2014 Sample Databases.|
|3. You will want to add MSFTDBPRODSAMPLES.CODEPLEX.COM to trusted sites.
You do not need to add the other sites to trusted.
|4. Click Adventure Works 2014 OLTP Script.zip
Click here if your browser does not start the download automatically.
|5. Click Save As|
|6. Browse to F:SQLData Right Click, Select New Folder – type AdventureWorks, ENTER, Select the AdventureWorks folder, click Open, then Click SAVE|
|7. Click Open Folder
Right Click Adventure Works 2014 OLTP Script
Select Extract All
|8. Take a look at the files that were extracted. We will modify the instawdb.sql file Double Click
The file should now be open in an SQL Query window.
We need to change the default paths for our database. Scroll down in the doce to the :setvar lines and change the paths to our F drive location
:setvar SqlSamplesDatabasePath “F:SQLDataAdventureWorks”
NOTE: Make sure you have a trailing backslash after the paths
|9. We cannot run the script yet. First we have to enable SQLCMD mode.
To do this Click the menu Query then click SQLCMD Mode
|Now we can run the script… Click Execute|
|After a few minutes you will hopefully have positive results|
|If you expand databases in the left NAV you should see your database.|
|Now you can just run a query to see the data…
/****** Script for SelectTopNRows command from SSMS ******/
I hope you found this post helpful, If you did, please use the social media buttons above to promote it. In the near future, I will create another post to show how to do other things with our server like synchronizing with on-premises servers and monitoring cloud services.