1 / 20

Windows Azure SQL Data Sync

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

valmai
Download Presentation

Windows Azure SQL Data Sync

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Windows Azure SQL Data Sync Name Title Microsoft Corporation

  2. Agenda Overview Architecture Features Best Practices

  3. Overview

  4. 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)

  5. 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)

  6. Architecture

  7. 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

  8. Features

  9. Key Features Sync Framework as a Service Full Data Synchronization Capabilities Conflict Handling Logging and Monitoring Scale Data Filtering No-Code

  10. 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

  11. 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

  12. Data Sync And Reporting Filtered Subset of Data Application Reporting Different Indexing SQL Database SQL Database Sync One-way Sync

  13. 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

  14. 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

  15. Best Practices

  16. 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

  17. 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

  18. 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

  19. Data Sync demo

More Related