The Windward Studio

Windward Blog Home

Moving Databases to Azure: A Step-By-Step Guide

Posted on 06/03/2015

Please Share This


Azure logoMoving databases to Azure is a pretty straightforward process if you know what to watch out for. I just went through the process here at Windward and along the way I ran into a few problems with firewalls, remote connections, database permissions and more.

So I’m writing this post to help anyone else who is migrating to Azure. To sum up, I moved our databases over to Azure by creating a Windows VM through the Azure portal and migrating the databases onto there.

The whole process took me about 3 weeks, but if I had known then what I know now, it probably would have taken less than a week. I hope this post helps you save some time and frustration!

The Step-By-Step Guide

Step One: Create an Azure VM

  1. Log into your Azure portal and click on the Virtual Machines (VM) tab on the left.
  2. Click New at the bottom left and navigate to Compute –> Virtual Machine –> From Gallery.
  3. Choose the image, tier and size that best fits your purpose and finish creating the VM.

 Step Two: Connect to the VM

  1. Once the VM’s status says “Running,” click Connect at the bottom left hand corner.
  2. This will download a Remote Desktop Protocol file – open when finished downloading.
  3. Click Connect on the popup window to continue.
  4. Enter the credentials you created when creating the VM and click Connect Anyway when it asks if you want to trust the connection.

Step Three: Migrate Database Information

Microsoft-SQL-ServerFor the purposes of Windward, I moved MSSQL, MySQL, and PostgreSQL over to Azure.

  • To move MSSQL

    1. Download the desired version of SQL Server.
      • OPTIONAL If you downloaded an Express version of the Server and it does not include SQL Server Management Studio (SSMS), download that separately.
    2. Open SSMS and connect to local instance of SQL Server with the following parameters:
      • Server type: Database Engine
      • Server name: Host name\SQL server instance (host name can be found on VM desktop background or Azure Portal Dashboard page, and SQL server instance is something like: SQLSERVER2012)
      • Authentication can be left as Windows Authentication
    3. The easiest way to migrate the database information is to connect to both servers in the same instance of SSMS.
      • If you choose to connect from a different computer to SQL Server running on the VM, see Connecting to Databases Remotely below and use the DNS name (found the Azure Portal Dashboard page) and the Endpoint Port you will later create for MSSQL.
    4. Connect to computer that has previous instance of MSSQL already on it.
    5. Once you have both servers showing in SSMS, expand the Databases tab under the connection.
    6. Right click on database you’d like to move, expand “Tasks” and click Detach.
    7. Once status is “Ready,” click OK.
    8. In a File Explorer window on the computer hosting databases to be migrated, navigate to where the Data and Log files for that database are (i.e. C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2012\MSSQL\DATA).
    9. Copy the .mdf and the .ldf files for the database and paste into a File Explorer window on the VM you just created.
    10. Once they are copied successfully, attach the database to both connections open in SSMS.
      • Right click on the Databases folder under the connection and click Attach.
      • Click Add on the popup screen and navigate to where the .mdf file is, select it, and click OK on all open windows.
  • To move MySQL

    1. Download MySQL Server X.X
      • OPTIONAL If it doesn’t come with MySQL Workbench and/or MySQL Administrator, download either of those (equivalent of SSMS).
    2. Open Command Prompt on computer that already has database information.
      • If it doesn’t come with MySQL Workbench and/or MySQL Administrator download either of those (equivalent of SSMS).
    3. Navigate to where you installed the server (i.e. C:\MySQL\mysql-5.6.24-winx64\bin)
    4. Perform the mysqldump command on the local machine:
      • The default user is ‘root’
      • mysqldump -u user -p[password] db_name > db_name.sql
      • Note: there is no space between “-p” and “password.” Also, if logging onto remote host just add “-h host” option in command.
    5. Copy and paste the db_name.sql file into a directory on the remote VM. (It will be saved into whatever directory you were in when you performed the command.)
    6. Create and populate a new database on the remote VM.
      • mysql -u user –p[password] (This will take you into the MYSQL> prompt)
      • create database db_name;
      • quit; (This is to exit the MYSQL> prompt)
      • mysql -u user -p[password] db_name < db_name.sql
    7. Use MySQL Administrator or MySQL Workbench to see details of created database.
      • You can use the command line, but these applications give better visuals.
  • To Move PostgreSQL

    (NOTE: This is very similar to the MySQL process above)

    1. Install the desired PostgreSQL Server.
      • If it doesn’t come with pgAdmin III, you probably want to install that also (equivalent of SSMS)
    2. Open a command prompt on the local machine and navigate to where you installed the server (i.e. C:\Program Files (x86)\PostgreSQL\9.1\bin).
    3. Perform the pg_dump command (note the uppercase “U”):
      • The default user is ‘postgres’
      • pg_dump -U user db_name > db_name.sql
    4. Enter in the password if prompted.
    5. Copy and paste the db_name.sql file (will be saved wherever you navigated to in your command prompt when the command was executed) into the server on the remote VM
    6. Create a database on the VM
      • createdb -U user db_name
    7. Populate database with information
      • psql -U user db_name < db_name.sql
    8. Use pgAdmin III to see details of created database
      • You can use the command line, but this has a better interface

Step Four: Connect to the Databases Remotely

  1. Create an Endpoint on Azure.

    • In the Azure Portal VM page click on the Endpoints tab.
    • Click Add at the bottom.
    • Add a ‘stand-alone endpoint,’ click arrow at bottom of window to continue.
    • Enter a name, set the Protocol to be TCP, set the public and private ports to the default port for the particular database.
      • MSSQL: 1433, MySQL: 3306, PostgreSQL: 5432
    • Wait for the Endpoint to finish creating
  2. Change Firewall settings to allow access.

    • Open Windows Firewall with Advanced Security.
    • On the left hand side click Inbound Rules.
    • Then click New Rule… on right hand side of window.
    • Set Rule Type to be Port.
    • Apply it to TCP.
    • In the Specific Local Ports box enter in the same number you made the Azure Endpoint.
    • On the next screen, allow the connection.
    • Apply the rules to all Profiles.
    • Give it a name and/or description and click Finish.

At this point you may have to open up Services and restart the instance of the database you just migrated. After that, attempt to connect to the database from a different machine, and it should connect.

If it is a newly created VM, you will also have to go into the Windows Firewall Outbound Rules and make sure the “File and Printer Sharing (Echo Request)” rule is enabled.

And, if you’re migrating MSSQL,

  1. Make sure the SQL Server Configuration Manager is set up to allow TCP/IP connections.
    • Open SQL Server Configuration Manager
    • Expand SQL Server Network Configuration on the left hand side and Click on Protocols for your_server
    • If TCP/IP is set to disabled, right click and enable it
  2. Enable and start the SQL Browser Service.
    • Open Services
    • Find SQL Server Browser
    • Make sure it’s running
      • If not, start the service

Well, that’s it! I hope this helps you move your databases to Azure. I also have screenshots to go with these steps. If you would find those helpful, or if you want to mention anything else, let me know in the comments below.

Please Share This

Author: Kylie Dale

Kylie is working toward a B.S. degree in Computer Science at the University of Colorado, Boulder. Originally from Northern California, she moved to Colorado partly for the snowboarding and the hiking. Kylie has a passion for programming, but if there’s any way for her to be outside that’s where you’d find her!

Other posts by