Archive

Archive for the ‘SQL Azure Migration’ Category

Monitoring Transaction Replication : Replicating On-Premise SQL Server databases to SQL Azure databases

October 30, 2017 Leave a comment

In previous post, we saw How to setup Transactional Replication to replicate On-Premise SQL Server databases to SQL Azure databases, in this post we will how to monitor the Replication process and make changes to data in On-Premise and verify the changes in SQL Azure database

Replication Monitoring

Step 1: Right click on Replication –> Local Publications –> Launch Replication Monitor

capture20171025144517902

Step 2: We can see the summary of subscriptions, current average performance and current worst performance

capture20171026151048695

Step 3: Expand the Publication to see the list of subscribers, we can see the list of subscribers and each of their Performance, Latency and Last Synchronization time

capture20171026151138103

Step 4: If we need to review the status of each Agent status, we can use Agents tab and see their status

capture20171026151212465

The different types of Agents status available are as follows

capture20171026151221076

Step 5: To view the details of a particular publisher/subscriber, Please right click on the Publication/Subscription and click on “View Details

capture20171026151412435

In details view we can see the completed (Success and Failure) and current sessions, if we click on a particular session, we can also see the steps ran on that session

Details of the publication session

capture20171026151457561

Details of the Subscriber session

capture20171026151512972

On-Premise Data Update verification in SQL Azure database

We will make data changes using below query in On-Premise server

Query to insert a new record and update one record in On-Premise SQL Server database

INSERT INTO dbo.Customers 
( 
CustomerID, 
CustomerName, 
CustomerAddress, 
Comments, 
Value 
) 
VALUES 
( 80001, -- CustomerID - int 
'CustomerName80001', -- CustomerName - char(100) 
'CustomerAddress80001', -- CustomerAddress - char(100) 
'Comments80001', -- Comments - char(185) 
80001 -- Value - int 
);

UPDATE dbo.Customers 
SET Comments = 'Update Comments 80000' 
WHERE CustomerID = 80000; 

After 2 minutes, we ran the below query in SQL Azure database

Query to verify data changes in SQL Azure database

SELECT [CustomerID], 
[CustomerName], 
[CustomerAddress], 
[Comments], 
[Value] 
FROM [dbo].[Customers] 
WHERE CustomerID >= 80000; 

Output: We can see the changes made in On-Premise server is replicated to SQL Azure database

capture20171026151906330

Hope you find this post helpful !!!

Advertisements

Presenting “Migrating Data to SQL Azure” in New Jersey SQL Server User Group

June 20, 2011 Leave a comment

I will be presenting at New Jersey SQL Server User Group

Location

Set Focus
4 Century Drive
Parsippany , NJ, 07054, U.S.A

Meeting Time: 6:30 PM EST – 8:30 PM EST

Session Title: “Migrating Data to SQL Azure”

Session Synopsis:

Migrating on-premise data to SQL Azure and vice versa is one of the new challenge for Database Developers and Administrators.

This session demonstrates what data can be moved to SQL Azure and how easily we can migrate data from On Premise SQL Server to SQL Azure and also from other Heterogeneous database environments.

This session includes live demonstrations using SQL Azure DAC Import Framework tools, SQL Server/Azure Migration Tools and will be highly interactive session.

NJSQL User Group Web site http://www.njsql.org/

Join NJSQL User Group at http://groups.google.com/group/nj-sql-server-user-group

NJSQL Job Posts http://www.linkedin.com/groups?gid=1964935&trk=myg_ugrp_ovr

I will post the presentation slides tomorrow after the meeting

Migrating MySQL Databases to SQL Azure using SSMA–Part 3

June 15, 2011 2 comments

In the previous posts, we saw how to download and install SQL Server Migration Assistant for MySQL v5.0 and configure utility database, and how to create a new migration project, add MySQL databases and SQL Server databases for conversion, analyze and prepare migration report.

In this post we will how to synchronize the MySQL schema to SQL Azure database and then migrate data

To convert the MySQL schema to SQL Azure schema, select the source database “ClassicModels” in MySQL Metadata Explorer and the right click and click on “Convert Schema”

image

Once the schema is converted, we will get the messages in output window similar to this (Messages will vary based on your database), if the message returns any error we need to fix it before migration

image

Once the schema conversion is complete, to migrate the schema, Right click on Target database in SQL Azure Metadata explorer and click on “Synchronize with Database”

image

Specify the password for the target server and click “Connect”

image

The schema migration preview window will display the list of objects being migrated, Click “Ok” to proceed

image

Migration process will be displayed in Output window, Once complete the output window will display the below message

image

Now we have successfully migrated the schema, To migrate the data, right click on Source Database and click “Migrate Data”

image

Specify the MySQL Server credentials and click “Connect”

image

Specify SQL Azure Target server credentials and click “Connect” to proceed

image

The data migrations process will prepare the data and start uploading the data to SQL Azure, the process details will be displayed in output window and the following message and below Data Migration Report will be displayed once the data migration completes successfully.

image

image

You can save the Data Migration report as CSV file for your reference.

You can connect to SQL Azure server using Project Houston or SQL Server Management Studio and query the data

image

Now we have successfully migrated the database schema and data from MySQL database to SQL Azure

Hope you all find this information useful !!!

Migrating MySQL Databases to SQL Azure using SSMA–Part 2

June 14, 2011 1 comment

In the last post, we saw how to download and install SQL Server Migration Assistant for MySQL v5.0 and configure utility database, In this post we will see how to create a new migration project, add MySQL databases and SQL Server databases for conversion, analyze and prepare migration report.

Start the SSMA for MySQL (32 bit) by clicking on Start Menu –> All Programs –> Microsoft SQL Server Migration Assistant for MySQL 5.0

image

Create a New Project from File Menu

image

Specify the Name of Migration project, select the location where you want to save the migration project and Target to SQL Azure (Other supported targets are SQL Server 2005, 2008 / 2008 R2, “Denali”)

image

Click on “Connect to MySQL” to connect to Source MySQL Server

image

Specify the Server name, User name and Password, If required change the other parameters based on your MySQL Configuration and click “Connect”

image

Once Connected, MySQL Metadata Explorer will be populated with the list of MySQL Databases and UDFs available

image

For today’ demo, we will migrate “ClassisModels” database, you can expand the objects node to view the list of objects available in the database, You can select the list of object you want to migrate

image

Now click on “Connect to SQL Azure” on tool bar to connect to destination SQL Azure server and the specify the server name, user name and password and click “Browse”

image

Click “Yes” to proceed, The context menu will display the list of databases available and an option to create new database

image

Let us create New Database named “ClassicModels_DB” by clicking on “Create New Database”

image

Specify the name of the database and then select Database size and click “Create” to create the new database

image

Once the database is created, you will be able to see the above message, Click “Ok” to proceed. The new database name will be populated in database combo automatically

Click “Connect” to proceed

image

Once connected, SQL Azure database will be loaded in SQL Azure Metadata Explorer

image

Schema Mapping

Now let us modify the target schema, By default the Target schema will be set as Source database name, we can change it to our existing schema ex. dbo

image

Select the source schema and click “Modify”
image

Click on “…” button to browse and select the existing Schema, once selected, click “Select” to proceed

image

and then click “Ok” update the target the schema for selected source schema

image

Now we have successfully mapped the schema, let us map the types

Type Mapping

Type mapping window display the source type and destination type , if you want to change to change an existing mapping you can select a type and click “Edit”

image

Select the new Target Type and specify the length if required and click “Ok” to proceed

image

You can also add a new type mapping by clicking on “Add”

image

Character Set Mapping

In Character Set mapping window, you can view and review the character set mapped between Source and Destination database, you  can make changes by selecting the Target Charset Type combo and then click “Apply”

image

SQL Modes

In SQL Modes window, you can select what effective SQL Modes will be used during migration

image

You can make the preferred changes and click “Apply”

Now we have successfully created a new MySQL migration project, connected source and destination databases and configured the migration properties.

Let us now Analyze the migration and see the reports

Right click on the Source database name “Classicmodels” and click on “Create Report”

image

Once the report is created, it will be displayed automatically

image

If there is any errors in analysis, the object will be marked in Red, and also the messages will be displayed in bottom pane.

Based on the error message, we can fix the issue and then migrate.

In the next post, we will how to synchronize the MySQL schema to SQL Azure database and then migrate data.

Migrating MySQL Databases to SQL Azure using SSMA–Part 1

June 13, 2011 2 comments

Microsoft SQL Server Migration Assistant (SSMA) for MySQL is a tool to automate migration from MySQL database to SQL Server or SQL Azure.

SSMA for MySQL v 5.0 is freely available from Microsoft, you can download it from the following link http://www.microsoft.com/downloads/en/details.aspx?FamilyID=ac017a90-3b53-49b6-92a7-0f91623ef530

It also has free support. SSMA automates the migration process by doing schema analysis, preparing migration report and migrating data.

SSMA for MySQL v 5.0 supports migration from MySQL 4.1 and higher to SQL Server 2005 / 2008 / 2008 R2 and SQL Azure

To install and run this we need MySQL ODBC Connection v 5.1, which you can download from the following link http://www.mysql.com/downloads/connector/odbc/

Let us see how to install the SSMA for MySQL v 5.0

Once downloaded the setup from the above URL, extract the zip file and run “SSMA for MySQL 5.0

image

Click “Next” to proceed

image

Accept the license agreement and click “Next” to proceed

image

Select Usage Report Settings, if you wish to report and then click “Next” to proceed

image

Select “Setup Type” and click Next to Proceed

image

image

Click “Install” to confirm and proceed with installation

image

image

Click “Finish” to complete the installation

image

Now we have successfully installed SSMA for MySQL v5.0.

In addition to this, we need to install SSMA for MySQL Extension Pack as well

Run the “SSMA for MySQL 5.0 Extension Pack.exe” and click “Next” to proceed

image

Accept the license agreement and Click “Next” to proceed

image

Select Setup type and click “Next” to proceed

image

image

Click “Install” to confirm and proceed with installation

image

image

Click “Next” to complete the installtion, and to start the configuration process of Utility database

image

Select the SQL Server instance on which you want to install the Utility database and click “Next” to proceed

image

Specify Authentication information and click “Next” to proceed

image

Provide a strong password to protect the authentication information stored in Utility database and click “Next” to proceed

image

Select “Install Utilities…” option and click “Next” to finish the installation

image

Once installed the following message will be displayed

image

If you want to install Utility database in another server, click “Yes” or click “No” to finish the installation

image

Click “Exit” to complete the installation.

Now we have successfully installed and configured the SSMA extension pack for MySQL.

In the next post we will see how to create a new migration project, analyze the MySQL database schema, prepare migration report and migrate data.

Migrating Access Databases to SQL Azure

April 27, 2011 6 comments

Step 1: Download and Install “Microsoft SQL Server Migration Assistant 2008 for Access v 4.2” from

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=5abe098d-c7e1-46c6-994a-09a2856eef0b&displaylang=en

Step 2: Register SSMA 2008 for Access and download License and install the License

Step 3: Run SSMA 2008 for Access v 4.2

image

Click Next to create a new Migration Project

image

Specify Project Name and Select Project File Location and select Migration To: “SQL Azure

image

Click “Add Databases” to Select the Access Database(s) you want to migrate

If you are not sure the location of your database , click “Find Databases” to find it

image

You can search using the following criteria

1. Specify a folder such as C:\Databases

2. Specifying a part of the file name

3. Created or Updated Date

4. Size of the database

5. Owner of the database

image

After adding you databases, Click “Next” to proceed

image

Select the Tables and Queries you want to Migrate

image

Specify “SQL Azure” Server Name, username and password and the click “Browse”

image

Click “Yes” to Continue

image

You can select any existing SQL Azure database, or Create New SQL Azure Database

image

Specify Database Name, You can select Database Size as 1 GB or 10 GB and then click “Create”

image

Click “Ok” to proceed

image

If you still want to continue using Access as Front End for your SQL Azure database, check “Link Tables” option to create Linked Tables in your Access database and click “Next” to proceed

image

Once the scripts are prepared and ready, you will be prompted to confirm the SQL Azure information again

image

Enter the Password and click Connect

image

Review the List of tables and its associated Indexes and Triggers ready to be migrated and then click “Ok” to proceed

image

The above Legends explains the directions of data migration

image

You can click on legends “Blue Arrow” to change the direction, in the above screenshot, we have selected “Order Details” not to be migrated

Click “Ok” should start the migration process

After migration, you should be able see the tables migrated in SQL Azure server

image

For all the tables, a TimeStamp field will be added as last column for enabling continuous Data Migration

image

Using SQL Azure Metadata Explorer, You can generate Reports to identify the issues in migration or Migrate data from one or multiple tables

image

Sample Migration Report

image

image

Table level report

image

Now you can access your data in SQL Azure using SQL Server Management Studio or SQL Azure Management Portal or Any Applications supporting SQL Azure connections

image

Happy Migration !!!

Categories: SQL Azure Migration
%d bloggers like this: