Part 1: Creating SQL Server Virtual Machine on Windows Azure (using the new portal)
| Step-By-Step | |
| 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
| Step-By-Step | |
| 1. The first thing we want to do to configure SQL Server is to add additional disks. Click Virtual Machine (in the Summary section) |  |
| 2. Scroll Down, Click Disks |  |
| 3. Click Attach New |  |
| 4. Enter Disk Size (eg. 127) Click Configure required settings |
 |
| 5. Click Choose Storage Account then select your storage account |  |
| 6. Click Choose Container then click vhds then Click OK then OK again to finish “Attach a new disk” |  |
| 7. After a few seconds, In the Left Nav bar you should see Notifications. Click and you will see the disk was attached successfully |  |
| 8. Connect to SQL Server machine, Login using the credentials you used in part 1 when you created the machine. (You may have to scroll back to the left) |  |
| Now, let’s format the new disk we just created 9. When you login to the server, the server dashboard should already be running. Click File and Storage Services |
 |
| 10. Click Disks |  |
| Click the last disk (Disk 2) Right Click Select New Volume In the New Volume Wizard click next a couple times… |
 |
| Click OK to initialize and bring the disk online at a GPT disk. |  |
| Click Next Again for the Volume Size Click Next again for the Drive letter F Enter the Volume Label “Data” then click Next On the Summary Screen click Create When finished, click Close |
 |
| Open Windows Explorer (folder icon on task bar) drill down into the F drive Right-click in the white space of the drive and select New Folder Give the new folder a name of SQLData |
 |
| Click Windows Logo Start – Down Arrow Right Click SQL Server 2014 Management… click Pin to Start. Right Click SQL Server 2014 Management… Click Pin To Task Bar |
  |
| Click SQL Server 2014 Management.. Click Connect Right Click Server Name Select Properties |
 |
| Click Database Settings and change the default locations to the F drive Data: F:SQLData Logs: F:SQLData Backup: F:SQLDataBackup Click OK |
 |
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 Click Extract |
|
| 8. Take a look at the files that were extracted. We will modify the instawdb.sql file Double Click instawdb.sql 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.