Search This Blog & Web

Tuesday, March 26, 2013

SQL Azure Data Synchronization Process (Step by Step)

Migrating data from cloud into on-premises is always on priority of every organization. Data requires for multiple purposes like reporting, cleaning, archiving etc. One of the reason of data migration or synchronization on and off the cloud is its storage limitation as we know we cannot create huge databases on SQL Azure during trail version and even we have to pay after purchasing that is obviously not too costly but still we need to move our archived data from the cloud DBs to local Database.
Another scenario of data synchronization is at enterprise applications there are multiple offices on multiple locations and we need on-premises data to calculate reporting in easy and efficient way. Lets look at the basic architecture of azure sync.

Azure Sync Portal


SQL Data Sync comprises four main components:
  1. ·     SQL Data Sync service
  2. ·     Windows Azure SQL Database
  3. ·     Windows Azure Storage
  4. ·     Data Sync Agent

Let discuss how can create synchronization using SQL Azure cloud and make connected to our on-premises database.



Basic idea behind this is to merge azure and local data on defined rules. We need to create a Database hub to synchronize data and then distribute it to data Members or spoke. To do this we need following steps
Provision a Data Sync Server
Before you can begin the process to create a sync group you must first provision a Data Sync server on your Windows Azure subscription.
Create SQL Agent 
List Shows available databases

Click on create Sync Agent and follow Wizard
Generate Key 
Unique key has been generated to Link up your Azure DBs with Local service
Download and Install SQL Data sync Agent Preview on your local machine

Key generated above used to connect to Azure

Copy --> Paste key and click OK

Register your local database that is used to sync with SQL Azure Db.


You can use your windows auth and SQL user auth to connect local db.Review Agent status on cloud on refresh

Local db has been synchronized and status is Good

Create the Sync Group
The first step in the process to create a sync group is to give the sync group a unique and meaningful name.
Use your closet Region


 Add a SQL Azure Hub Database
  • Hub Wins: The first row change written to the hub is kept. Subsequent attempts to write to the same row in the hub are ignored. The first write to the hub is propagated out to all member databases.
  • Client Wins: Each row change in a member database is written to the hub, overwriting prior changes to the same row. The last write to the hub is then propagated out to all member databases.

No matter which policy you adopt, one of the changed rows is kept and the others are lost whenever a
Conflict arises.
You can use one of your database as Hub and Can add one or multiple as Reference.

Hub DB call sync service with local setting. If will copy all Reference tables in Hub db on Back end
     Add a SQL Azure Member or Reference Database

Select one of the database that use sync service to sync data. It would be Client to Hub, Hub to Client or Bi-directional

We have successfully configured sync databases
Configure the Sync Group
Select the sync group schema source
Creating rule for synchronize.
Set the synchronization frequency.

      Select the tables to synchronize.
Select the columns to synchronize.
Optionally filter the rows to synchronize.


 
1.     You can also select all tables and all columns but there are some restrictions applied as follows
       I. Maximum number of SQL Data Sync Servers per subscription: 1
      II. Maximum number of sync groups any database can belong to: 5
     III. Filters per table: Up to 12 (optionally 13 if one is on the primary key column)
     IV. Database, table, schema, and column names: 50 characters per name
      V. Tables in a sync group: 100
     VI. Columns in a table in a sync group: 1000


Added reference tables to Sync Database
Along with the data tables (HumanResources.Employee and Person.BusinessEntity), SQL Data Sync created six
new tables, system synchronization tables, in both databases:
schema_info: Tracks member schema information.
scope_config / scope_info: Used by the Sync Framework to determine what tables, filters, and so on are being synchronized. Each database that is participating in a sync includes these tables and includes at least one scope (if they’re being synchronized).
<table>_dss_tracking: Tracks changes to the related user table.
provision_marker: A metadata table used internally as part of the change-tracking mechanism.
 
 
Now we have successfully configured our sync environment. Lets test how it works
 
Deploy Sync Group and Verify Changes

We have same data on both databases.
Large Query window returning result from local whereas Small Query tab showing data from Cloud. Data is same.
Adding 2 new rows on the local database we have different numbers on both sides.
After adding 2 rows on local db Count is changed from 28 to 30 whereas on cloud query tab count is still 28
Now execute the sync service on cloud. For testing I do not wait frequency to execute sync

Now look at the data on both sides’ data has been synchronized on both databases.
 
After executing service data is synced and equal on both sides.

As we have configured our reference database as bi-directional. Lets go to the cloud db and verify what happens when there is data change in cloud db. I have deleted some rows on cloud db and look at the total rows difference on both sides now.


After running sync service data has been updated on both sides.
Debugging the Logs

We have configured and test changes to sync data on Azure. Hope you will get benefit out of this post.

Tuesday, March 12, 2013

Working with SQL Azure Management Portal

In previous posts we have installed and connect our Azure database using Azure Management portal and SQL Server Management Studio.

In this post we will how can we perform SQL related tasks on Azure Management Portal

Connect Azure database Management portal using your credentials

Database Overview


Querying your tables


Creating table using Azure portal. every table must have Primary Key Index

Creating Indexes and Keys

Looking at your table data

Querying your data. Look at the left panel and you will see recently history.


Looking at the execution Plan screen 1

Looking at the execution Plan screen 2 (Graphical Execution Plan)

Your recent work history shows in tabs as database dashboard

Running store procedure

Creating database using SSMS and Clound MS for SQL Azure

SQL Azure
We have successfully created and configured our Windows Azure trail version account. In this post we will see how can we create SQL Azure database and use simple SQL Queries through Azure Management Studio and SQL Server Management Studio



You can use Windows Azure SQL Database Management Portal or the SQL Server Management Studio (SSMS) client application to administer your SQL Database subscriptions and create and manage associated logical servers and databases. The guidance below describes how to use Management Studio to manage SQL Database logical servers and databases.

This task includes the following steps:
Step 1: Get SQL Server Management Studio
Step 2: Connect to SQL Database
Step 3: Create and manage databases
Step 4: Create and manage logins
Step 5: Monitor SQL Database using Dynamic Management Views

Management Studio is an integrated environment for managing SQL databases. When managing databases on Windows Azure, you can use the Management Studio application installed with SQL Server or download the free SQL Server 2012 Management Studio Express (SSMSE) version.

We have already see how to install SQL Server 2012 Express on your machine.
http://shamas-saeed.blogspot.com/2013/03/step-by-step-installation-sql-server.html

Now we create new database using Windows Azure and SQL Server Managment studio.

Create new database using Windows Azure Portal

Create Database and set its credentials
Selected region where this database 

After creating your database you can see your database lists in all items. Carefully select location.

This is Administrative section for you new DB. From where you can add your Public IP in firewall and create roles.

Register your Public IP address to access this database from your application

Connecting and creating database using SQL Server Management Studio 2012
Connect to Azure SQL Management portal using Manage Button on VM database screen

Successfully connected through Azure Management Portal. Now you can Query and View you database
Using 2012 Express Management studio you connect to Azure database

You can view, create, query and administration your database using on premises management studio



Step by Step Windows Azure account creation trail-account


What is Windows Azure?

Windows Azure is a cloud services operating system that serves as the development, service hosting and service management environment for the Windows Azure platform. Windows Azure provides developers with on-demand compute and storage to host, scale, and manage web applications on the internet through Microsoft datacenters.
Windows Azure is a flexible platform that supports multiple languages and integrates with your existing on-premises environment. To build applications and services on Windows Azure, developers can use their existing Microsoft Visual Studio expertise. In addition, Windows Azure supports popular standards, protocols and languages including SOAP, REST, XML, Java, PHP and Ruby. Windows Azure is now commercially available in 40 countries.


Now look at the screen shots and you will know how to create Azure trail version account to introduce yourself with Cloud.

When you try to connect to Windows azure you will get this message

Trail version registration starts here

You need to have Microsoft live account to get Azure registration

You need to give your Master visa card or Credit card Information, You will not charged anything on this information during trail version and without your notification

Verify your account using your Mobile phone information

Now you have registered your account, This update will take 5 to 10 minutes to implement

Here you start configuring your Azure VM account to use Cloud services

Main menu configuration



If you find any notification and error message you can use right bottom of your screen

We have successfully created and configured our Windows Azure trail version account. In next post we will see how can we create SQL Azure database and use simple SQL Queries through Azure Management Studio and SQL Server Management Studio