How to Migrate an On-Premises SQL Server 2012 Database to Windows Azure SQL Virtual Machine

 

How to Migrate an On-Premises SQL Server 2012 Database to Windows Azure SQL Virtual Machine

Rate This
  • Comments 1

Overview

This next section is about migrating on-premises SQL Server database to the public cloud.

Windows Azure SQL Server for VMs (IaaS) makes it possible for you to host your cluster in a Microsoft datacenter. The key advantage this public cloud-hosted option offers is a high level of compatibility with existing on-premises SQL Server installations. Because this option supports a full-featured version of SQL Server, you’re able to leverage HA, transparent data encryption, auditing, business intelligence (BI) such as analysis services and reporting services, distributed transactions, support for active directory (Active Directory Federation Services, or AD FS 2.0), and more. Moreover, you can build your own VM or leverage a template from the Windows Azure image gallery (described later). Finally, this offering allows you to build your own virtual private networks (VPNs) easily, bridging your cloud resources to your on-premises network and providing a seamless experience that blurs the lines between the massive Microsoft datacenters and your internal servers.

highlevel

Choosing a base image from the gallery

Objectives

In this hands-on lab, you will learn how to:

  • Provision a virtual machine with SQL Server 2012 and Windows Server 2012 (SQL Server 2012 SP1 Enterprise on WS 2012)
  • Use Remote Desktop to start SQL Server Management Studio and import the bacpac file
  • Configure Named Pipes on SQL Server Express 2012
  • Open port 1433 on the virtual machine to allow incoming connections to SQL Server 2012.
  • Configure the Firewall to allow incoming connections
  • Enable SQL Server Authentication and add a new login user
  • Test the connectivity to the virtual machine using various tools
  • Create a connection string that can be used to leverage the Windows Azure SQL Virtual Machine we create

Prerequisites

The following is required to complete this hands-on lab:

Setup

In order to execute the exercises in this hands-on lab you need to set up your environment.

  1. Start by logging into the Windows Azure Portal (http://manage.windowsazure.com)
  2. How to Export an On-Premises SQL Server Database to Windows Azure Storage
  3. How to create a Windows Azure Storage Account

A set of database-oriented posts

This post is part of a set of posts that go together:

  1. How to create a Windows Azure Storage Account
  2. How to Export an On-Premises SQL Server Database to Windows Azure Storage
  3. How to Migrate an On-Premises SQL Server 2012 Database to Windows Azure SQL Virtual Machine
  4. How to Migrate an On-Premises SQL Server 2012 Database to Windows Azure SQL Database
  5. Setting up an Azure Virtual Machine For Developers with Visual Studio 2013 Ultimate and SQL Server 2012 Express

Exercises

This hands-on lab includes the following exercises:

Creating a Windows Azure SQL Virtual Machine and importing a database

In this section, you will log into the Windows Azure Portal and create an Windows Azure SQL Virtual Machine using the Windows Azure Gallery.

  1. Return back to the Windows Azure Portal. To start select virtual machines from the left menu panel. Next, click the +New button in the lower left corner.

    image001

    Creating a Windows Azure SQL Virtual Machine

  2. Next, select Virtual Machine | From Gallery from the portal. You will then be provided a list of virtual machine images to choose from. We are interested in SQL Server 2012 Sp1.

    image002

    Choosing a base image from the gallery

  3. Notice that there are many versions of SQL Server available on various operating systems. Make the selection as seen below, SQL Server 2012 SP1 Enterprise on WS 2012.

    image003

    Selecting SQL Server 2012 SP1 Enterprise on WS 2012

  4. This next screen allows you to specify a virtual machine name. You will need to choose a unique name that is not already chosen. This screen also allows you to specify the machine size. For testing purposes we recommend a small size as there won’t be much load for an individual developer. You will also need to specify a username and password that will be used to log into the machine. This will not be the same username and password for the web application. We will need to add a database user separately using SQL Server Management Studio in a future step.

    image004

    Configuring the Virtual Machine

  5. You will also need to provide a DNS name, which represents the unique name of the server on the web. It also specify a Region, we should generally be the same Region as the applications that will be consuming the database to minimize latency.

    image005

    Configuring the Virtual Machine

  6. No changes or modifications are needed for this final step in the virtual machine configuration.

    image006

    Configuring the Virtual Machine

Task 2 – Remote Desktop into Virtual Machine

The goal of task 2 is to configure the virtual machine and the underlying database. You will import the bacpac file. You may need to re-visit the Windows Azure portal to get the details of your storage account where the bacpac file resides.

  1. We are now ready to begin the importing of the Bacpac file that was created in Task 1. To accomplish this task, we will need to use Remote Desktop to connect to the Virtual machine directly. From there we will start SQL Server 2012 Management Studio to perform the actual import process. For this step, click the Connect option from the bottom menu bar.

    image007

    Importing into Windows Azure SQL Virtual Machine

  2. A popup window will appear at the bottom of the browser window. Click on the Open button to start a remote desktop session. You will need to use the Username and Password specified when you created the virtual machine in the previous step.

    image008

    Opening a Remote Desktop Session

  3. You are now remotely connected to the virtual machine you just created. The next step is to start SQL Server 2012 Management Studio.

    image009

    Starting SQL Server 2012 Management Studio

  4. The virtual machine comes equipped with SQL Server 2012 Management Studio. Select from the start menu and launch it.

    image010

    Starting SQL Server 2012 Management Studio

  5. It may take a few moments to load the first time.

    image011

    Starting SQL Server 2012 Management Studio

  6. You will be presented with the appropriate default parameters to connect to the database server. You don't need to enter anything. Just click the connect button.

    image012

    Connecting to database with Windows Authentication

  7. We are now ready to import the data tier, which is really the Bacpac file that we had created earlier. As you recall, that file is sitting in Windows Azure storage. Right mouse click on Databases and select Import Data Tier.

    image013

    Importing a Data-tier Application

  8. The wizard will begin and you will be presented with various screens to import the database. Click Next on the Import Data-tier Application Wizard.

    image014

    Starting the import wizard

  9. Because the Bacpac file is sitting in Windows Azure storage, select the radio button | Import from Windows Azure. At this point you should click Connect, assuming you have the storage account information. If you do not, the next steps will allow you to get that information. If you have the Storage Account Name and the Primary Access Key, you can skip past the portal help section in the next 2 steps.

    image015

    Importing Data-tier Application

  10. The point of this step is to verify that you have not forgotten the details of the Bacpac file inside of Windows Azure Storage. You can return back to the Windows Azure portal and click Storage on the left menu. You should be able to easily identify the Bacpac file that you had created previously in an earlier exercise. Once you highlight the Bacpac file, you can select Manage access keys from the bottom of the screen. The access key will be needed for the import process in SQL Server Mangement Studio.

    image016

    Importing Data-tier Application

  11. As explained previously, you may need the primary access key. When at the portal, note that you can now access the primary access key and copy it to the clipboard so that it can be used for the import within SQL Server Mangement Studio in this step. Notice you need to provide the Storage Account Name as well as the Account Key. Enter that information then enter Connect.

    image017

    Importing Data-tier Application

  12. You should be able to select the Container name from the drop down combo box. Click Next to continue.

    image018

    Importing Data-tier Application

  13. You will also be able to select the file name for the Bacpac file.

    image019

    Importing Data-tier Application

  14. Accept the default values and click the Next button.

    image020

    Importing Data-tier Application

  15. The import process is now complete. Click Finish to dismiss the import data tier wizard.

    image021

    Importing Data-tier Application

  16. Verify that your import a successful and click Close.

    image022

    Importing Data-tier Application

Task 3 – Verifying a correct import of the bacpac file

The goal of task 3 is to verify that the database imported correctly. You enter some basic queries to do so.

  1. You can now verify that all the database structures, as well as the data is available. Right mouse click on the Customers table and create a select Query in a New query editor window. A simple query can verify if all the appropriate data structures and data are made it through the import process.

    image023

    Verifying the import with SQL Server 2012 Management Studio

  2. Execute the query and verify that the correct data appears in the results pane below.

    image024

    Verifying the import with SQL Server 2012 Management Studio

Task 4 – Configuring Named Pipes, and opening up TCP port 1433

Your database and virtual machine is not accessible by client applications. This lab is about enabling Named Pipes and port 1433.

Named-pipes provide a way for inter-process communication to occur among processes running on the same machine. What named pipes give you is a way to send your data without having the performance penalty of involving the network stack.

SQL Server is a Winsock application that communicates over TCP/IP by using the sockets network library. SQL Server listens for incoming connections on a particular port. The default port for SQL Server is 1433. The port doesn't need to be 1433, but 1433 is the official Internet Assigned Number Authority (IANA) socket number for SQL Server.

  1. Let’s begin by starting SQL Server Configuration Manager.

    image025

    Starting SQL Server Configuration Manager

  2. Once SQL Server Configuration Manager is open, navigate to the section on the left called SQL Server network configuration | Protocols for MSSQLSERVER. Be sure to enable Named Pipes. You can close SQL Server Configuration Manager now.

    image026

    Changing SQL Server Configuration Management

  3. We now need to return back to the portal and open up TCP port 1433 the virtual machine. Start by selecting the virtual machine previously created, and click the -> to the right of the virtual machine name (DBMvcSample).

    image027

    Opening up TCP Port 1433

  4. From the top level menu select Endpoints, then click the Add button from the bottom of the portal window.

    image028

    Opening up TCP Port 1433

  5. The dialog box is for you select the Name, Protocol, the Public and Private ports. Fill in the dialog box as seen below. As mentioned previously, for security purposes, it might make sense to use another lesser-known port then 1433.

    image029

    Opening up TCP Port 1433

Task 5 – Enabling Connections by Configuring Windows Firewall

Inbound rules explicitly allow, or explicitly block, inbound network traffic that matches the criteria in the rule.

For example, you can configure a rule to explicitly allow traffic secured by IPsec for Remote Desktop through the firewall, but block the same traffic if it is not secured by IPsec.

When Windows is first installed, all unsolicited inbound traffic is blocked.

To allow a certain type of unsolicited inbound traffic, you must create an inbound rule that describes that traffic. For example, if you want to run a Web server, then you must create a rule that allows unsolicited inbound network traffic on TCP port 1433.

  1. From the Start menu, choose Windows Firewall with Advanced....

    image030

    Starting the Windows Firewall

  2. Right mouse click on Inbound Rules and choose New Rule.

    image031

    Adding an Inbound Rule

  3. The second radio button is Port, so select Port and click the Next button.

    image032

    Adding an Inbound Rule

  4. Be sure that the rule applies to TCP ports, specifically port 1433, as seen below.

    image033

    Adding an Inbound Rule

  5. The action that we desire is to Allow the Connection. Then click Next.

    image034

    Adding an Inbound Rule

  6. By default all three checkboxes are enabled. Do not change them and click Next.

    image035

    Adding an Inbound Rule

  7. Provide a Name for the new Inbound rule that we just created and click Finish. You are now done configuring the Windows firewall to allow Incoming TCP connections on Port 1433.

    image036

    Adding an Inbound Rule

Task 6 – Enabling SQL Server Authentication and Adding a User

During setup, you must select an authentication mode for the Database Engine.

There are two possible modes: Windows Authentication mode and mixed mode. Windows Authentication mode enables Windows Authentication and disables SQL Server Authentication. Mixed mode enables both Windows Authentication and SQL Server Authentication. Windows Authentication is always available and cannot be disabled.

When using SQL Server Authentication, logins are created in SQL Server that are not based on Windows user accounts.

Both the user name and the password are created by using SQL Server and stored in SQL Server.

Users connecting using SQL Server Authentication must provide their credentials (login and password) every time that they connect.

When using SQL Server Authentication, you must set strong passwords for all SQL Server accounts. For strong password guidelines, see http://technet.microsoft.com/en-us/library/ms161962.aspx.

  1. As previously stated, because our connection string will leverage SQL Server standard security, we will need to create a new login user that leverages SQL Server Authentication. Right mouse click Security | Logins | New Login.

    image037

    Adding a New Login and SQL Server Authentication

  2. Enter a login name and remember what you indicated. It will be needed in the connection string later from our web application. In this example we will call the login name TestUser. De-select Enforce password policy.

    image038

    Adding a New Login and SQL Server Authentication

  3. From the left side of the dialog box under Select a Page, click on User Mapping. Be sure to map TestUser to MVC4Sample. Make sure the Default Schema reads db_owner. Lower in the dialog box for Database Role Membership, select db_owner and public.

    image039

    Adding a New Login and SQL Server Authentication

  4. In the final step we will need to enable SQL Server Authentication. This can be done by choosing properties after right mouse clicking on SQL Server and the Object Explorer, as seen below.

    image040

    Enable SQL Server Authentication

  5. On the left side of the dialog box choose Security and be sure that under server authentication you have chosen SQL Server and Windows authentication mode. Click OK when finished.

    image041

    Modifying Server Properties

  6. In order for all these changes take effect, you will need to restart the database. Right mouse click on SQL Server and choose Restart.

    image042

    Restarting SQL Server

  7. Choose Yes from the pop-up dialog box to indicate that you do wish to restart the server.

    image043

    Verifying the restart

Task 7 – Verifying Connectivity

This final task is to verify that we can indeed connect to the database from the outside world (from client web applications).

  1. Start Visual Studio 2013 Ultimate RC. From the View menu, choose Server Explorer.

    image044

    Starting Server Explorer in Visual Studio 2012 Ultimate or Visual Studio 2013 Ultimate RC

  2. Right mouse click on Data Connection in Server Explorer and choose Add Connection

    image044

    Adding a database connection

  3. Select Microsoft SQL Server and click Continue.

    image045

    Choosing the data source

  4. Type in the server name of the Windows Azure SQL Virtual Machine you created earlier. Yours will be different from dbmvcsample.cloudapp.net. Next select Use SQL Server Authentication and type in the User name and Password. Next, select Selet or enter a database name and you should be able to see MVC4Sample. Finally, click Test Connection to verify the database is available.

    image046

    Adding the connection

  5. Right mouse click on Tables | Customers and select New Query. Next, type in select * from Customers.

    image047

    Creating a query

Task 8 – Viewing the connection string

As a final note, this is what the connection string might look like. Your server name will be different however.

(Code Snippet - Connection String)

C#

Data Source=dbmvcsample.cloudapp.net;
Initial Catalog=MVC4Sample;
User ID=TestUser;Password=***********

Summary

In this lab, you saw how to configure a Windows Azure SQL Virtual Machine. Specifically you learned:

  • How to use the Windows Azure Gallery to quickly provision a virtual machine
  • How to import a bacpac file to load a database into the Windows Azure SQL VM
  • How to configure named pipes, tcp ports, and firewall settings to allow outside access to the database
  • How to validate connectivity to the virtual machine
  • Hi All,

    Here I have scenario like my on-premise sql is sql 2000 server now I am planning to migrate to windows azure sql server 2008r2.

    Can anyone please guide me how to do.

    what all the compatibility issues or security issues or connectivity issues?

    Thanks in advance!!!

    Regards,

    Satish k

Page 1 of 1 (1 items)
Leave a Comment
  • Please add 6 and 2 and type the answer here:
  • Post