windows azure sql data sync n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Windows Azure SQL Data Sync PowerPoint Presentation
Download Presentation
Windows Azure SQL Data Sync

Loading in 2 Seconds...

play fullscreen
1 / 20

Windows Azure SQL Data Sync - PowerPoint PPT Presentation


  • 114 Views
  • Uploaded on

Windows Azure SQL Data Sync. Name Title Microsoft Corporation. Agenda. Overview Architecture Features Best Practices. Overview. SQL Data Sync. Data Synchronization Microsoft Sync Framework technology as a service No code setup and configuration

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Windows Azure SQL Data Sync' - valmai


Download Now An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
windows azure sql data sync

Windows Azure SQL Data Sync

Name

Title

Microsoft Corporation

agenda
Agenda

Overview

Architecture

Features

Best Practices

sql data sync
SQL Data Sync

Data Synchronization

Microsoft Sync Framework technology as a service

No code setup and configuration

Synchronize datasets hosted in SQL Database or SQL Server

Customizable to fit your business needs

SQL Database (US)

SQL Database (Hub)

SQL Server (OnPrem)

SQL Database (WE)

overview
Overview

Same Cloud Benefits

Enterprise class scalability

Automatic support for High-Availability

Rapid Provisioning

Global Distribution

Elasticity

Uptime

SQL Database

Sync

SQL Data Sync

Sync

Sync

Sync

Remote Offices

Retail Stores

On-premise(HQ)

components and architecture
Components And Architecture
  • Key:
  • Windows Azure Web or worker roles

Windows Azure Portal

Admin UI

HTTPS

Browser

Service Data

  • Configuration - Servers,Sync Groups, Schemas, Agents, etc.
  • Tasks queue - sync, get schema, provision, etc.
  • Logging - task results

SQL Data Sync

Admin UI Handler

SQL Database

Task Scheduler

Data Sync Agent

Service Data

  • Temporary batch files of uploaded data changes

UI

  • - Get tasks
  • - Task results

Cloud Task Execution

Windows Service

Windows Azure Blobs

HTTPS

TDS

Agent Task Execution

User Databases

  • Added database objects:
  • Change Tracking tables
  • Sync meta-data tables
  • Triggers & sprocs

TDS

User Databases

  • Added database objects:
  • Change Tracking tables
  • Sync meta-data tables
  • Triggers & sprocs

Batch Files

  • Temporary files containing changed data to upload
key features
Key Features

Sync Framework as a Service

Full Data Synchronization Capabilities

Conflict Handling

Logging and Monitoring

Scale

Data Filtering

No-Code

syncing between sql server and sql database
Syncing Between SQL Server And SQL Database

On-Premises

Application

Cloud

SQL Server

Sync

Application

Multiple Locations (e.g. branch)

Share database between locations

Aggregate data in cloud

One-way sync to cloud

One-way sync from cloud

Two-way sync

SQL Database

Application

E.g. Migration period

E.g. Different apps sharing same data

E.g. DR

Sync

SQL Server

syncing between sql databases
Syncing Between SQL Databases

Application

Cloud

SQL Database

Sync

Application

Application

Geographically located web applications

Sync

SQL Database

SQL Database

Sync

Application

Scale-out via multiple copies of data

Separate reporting & OLTP workloads

SQL Database

data sync and reporting
Data Sync And Reporting

Filtered Subset of Data

Application

Reporting

Different Indexing

SQL Database

SQL Database

Sync

One-way Sync

sql data sync and windows azure traffic manager
SQL Data Sync And Windows Azure Traffic Manager

Europe

Traffic Manager

Control traffic distribution Azure-hosted applications

DNS-based

Several Distribution Options

Application

SQL Database

Sync

Application

US

SQL Database

Sync

Asia

Application

SQL Database

behavior and database impact
Behavior And Database Impact

Batching

Data change split into batches for transfer

Sync Provisioning

Change Tracking via triggers and side-tables

Minimal DML overhead

No change to user tables

Stored Procedures and meta-data tables

Transactional Consistency

“Net changes”; not “all changes”

Batches split into transactions to apply

PK/FK ordering preserved

Transaction boundaries at source not preserved

E.g. Could get new Order applied in separate transaction to associated new OrderDetail

best practice
Best Practice

Database Provisioning

Basic database auto-provisioning provided:

Goal is to make Data Sync easy to try

Creates tables when the destination tables do not exist

Only the columns selected for syncing are created in the destination tables

Indexes in XML type columns and CHECK constraints are not created

Index option not set; e.g. STATISTICS_NORECOMPUTE

Triggers, Views, Stored Procedures not created

Recommendation:

Use the auto-provisioning capability only for trying the service

For production, you provision the database schema

disaster recovery best practice
Disaster Recovery Best Practice

Data Sync for DR

Examine characteristics:

Need change tracking (triggers, side tables) for all tables

Transaction boundaries not synced; operations in transactions can be split

Syncs on a fixed interval; no way to configure a delay

No automatic failover

Can’t restore to a particular time

Provided:

Sync as frequently as every 5 minutes

On-going sync of delta’s, not wholesale export

considerations
Considerations

Initial Sync

DO NOT pre-populate member database with data

DO let Data Sync perform initial sync

Reasoning

Pre-poulated rows are treaded as conflicts

Conflicts impact performance