Deploy SQL Server 2014 VM on Azure Cloud and Configure with Adventure Works DB–Step-By-Step


Thought Leadership & Events

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 image
3. Click SQL Se3rver 2014 Standard  image
4. Enter Host Name, User Name, Password (Host Name must be unique system wide, username cannot be “admin” password must be complex) image
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) image

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. image
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 …

image
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  image
12. Click Open image
13. Click Connect image
14. Click Use another account image
15. Enter the credentials you created when you created the machine in part 1 above  
  • NOTE: it may take some time for you to be able to login because DNS must propagate 
  • If you get an error message “Remote Desktop Can’t find the computere “YourComputerName.cloudapp.net”. This might mean that the “YourComputerName.cloudapp.net” does not belong to the specified network.  Verify the computer name and domain that you are trying to connect to”  it means DNS has not fully propagated yet.  You can wait or you can go obtain the IP address and connect to the IP Address.  image
 
  • To obtain the IP address:Scroll all the way to the Left and click YourComputerName 
image
  • click Properties
image
    • Make a note of the IP Address. 
    • You can now run Remote Desktop Client with this IP address to connect directly to your server.
image

 

 

      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) image
      2. Scroll Down, Click Disks image
      3. Click Attach New image
      4. Enter Disk Size (eg. 127)
          Click Configure required settings
      image
      5. Click Choose Storage Account then select your storage account image
      6. Click Choose Container then click vhds then Click OK then OK again to finish “Attach a new disk” image
      7. After a few seconds, In the Left Nav bar you should see Notifications.  Click and you will see the disk was attached successfully image
      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) image
      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
      image
      10. Click Disks image
      Click the last disk (Disk 2)
      Right Click
      Select New Volume
      In the New Volume Wizard click next a couple times…
      image
      Click OK to initialize and bring the disk online at a GPT disk.

      image
      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
      image
      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
      image
      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
      image
      image
      Click SQL Server 2014 Management..
      Click Connect
      Right Click Server Name
      Select Properties
      image
      Click Database Settings and change the default locations to the F drive
      Data: F:SQLData
      Logs: F:SQLData
      Backup: F:SQLDataBackup
      Click OK
      image

       

      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.
      image
      image
      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.

      imageimage
      4. Click Adventure Works 2014 OLTP Script.zip
      Click here if your browser does not start the download automatically.
      image
      5. Click Save As image
      6. Browse to F:SQLData Right Click, Select New Folder – type AdventureWorks, ENTER, Select the AdventureWorks folder, click Open, then Click SAVE image
      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: Change this path if you copied the script source to another path
      :setvar SqlSamplesSourceDataPath “F:SQLDataAdventureWorksAdventure Works 2014 OLTP Script”

      NOTE: Make sure you have a trailing backslash after the paths Smile

      image
      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
      image
      Now we can run the script… Click Execute image
      After a few minutes you will hopefully have positive results image
      If you expand databases in the left NAV you should see your database. image
      Now you can just run a query to see the data…

      /****** Script for SelectTopNRows command from SSMS  ******/
      SELECT TOP 1000 [BusinessEntityID]
            ,[PersonType]
            ,[NameStyle]
            ,[Title]
            ,[FirstName]
            ,[MiddleName]
            ,[LastName]
            ,[Suffix]
            ,[EmailPromotion]
            ,[AdditionalContactInfo]
            ,[Demographics]
            ,[rowguid]
            ,[ModifiedDate]
        FROM [AdventureWorks2014].[Person].[Person]

      image

       

      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.