distributing data for availability and scalability don vilen program manager sql server microsoft...

Distributing Data for Availability and Scalability

Distributing Data for Availability and ScalabilityDon VilenDon VilenProgram ManagerProgram ManagerSQL ServerSQL ServerMicrosoft CorporationMicrosoft Corporation

AgendaAgenda

Replication Technology PositioningReplication Technology Positioning SQL Server Replication Customer SQL Server Replication Customer

ScenariosScenarios Upgrade StrategyUpgrade Strategy Key Improvements in SQL Server Key Improvements in SQL Server

2005 Replication2005 Replication

SQL Server ReplicationSQL Server ReplicationA Spectrum of SolutionsA Spectrum of Solutions

MergeMerge

ReplicationReplication

QueuedQueued

UpdatingUpdating

SubscribersSubscribers

Automatic conflict detection & Automatic conflict detection & resolutionresolution

Linked Linked Servers and Servers and

Microsoft Microsoft Distributed Distributed Transaction Transaction CoordinatorCoordinator

Transactional Transactional Replication Replication

Peer-to-PeerPeer-to-Peer

ImmediateImmediate

UpdatingUpdating


Conflicts are avoided through partitioning or two-Conflicts are avoided through partitioning or two-phase commitphase commit

Increasing autonomyIncreasing autonomy Increasing consistencyIncreasing consistency

Replication TechnologiesReplication TechnologiesServer ScenariosServer Scenarios Transactional ReplicationTransactional Replication

Best choice continuously synchronizing Best choice continuously synchronizing databases to support scaling out query databases to support scaling out query workloadsworkloads

SQL Server Integration Services (was DTS)SQL Server Integration Services (was DTS) Best choice for performing transformations on Best choice for performing transformations on

data during transfer between databasesdata during transfer between databases

Database MirroringDatabase Mirroring Best choice for providing database high Best choice for providing database high

availabilityavailability

Disk / Device MirroringDisk / Device Mirroring Third-party solutionsThird-party solutions Ensure it meets SQL Server I/O Basics Ensure it meets SQL Server I/O Basics

requirementsrequirements

Mobile Synchronization Mobile Synchronization ComparisonComparison Merge Merge

ReplicatiReplicationon

Offline Offline DatasetsDatasets

Web Web ServicesServices

Incremental ChangesIncremental Changes

Conflict detection and resolutionConflict detection and resolution

Automatically initialize schema Automatically initialize schema and dataand data

Supports large datasetsSupports large datasets

Query processor locally available Query processor locally available

Unique IDs across remote sites Unique IDs across remote sites

Database constraints locally Database constraints locally maintainedmaintained

Supports heterogeneous Supports heterogeneous databasesdatabases

Does not affect database schemaDoes not affect database schema

Deploy application and db Deploy application and db togethertogether

AgendaAgenda




Customer ScenariosCustomer Scenarios

Offloading reporting and Data Offloading reporting and Data WarehousingWarehousing

Database scale out for websitesDatabase scale out for websites Empowering mobile usersEmpowering mobile users Enabling Point of Sale applicationsEnabling Point of Sale applications Consolidating and distributing Consolidating and distributing

regional dataregional data Integrating heterogeneous dataIntegrating heterogeneous data

Reporting and Data Reporting and Data WarehousingWarehousing

Reporting databases Reporting databases are continuously are continuously synchronized in near synchronized in near real-time with one or real-time with one or more source OLTP more source OLTP databases databases

Reporting and Staging Reporting and Staging user queries are user queries are offloaded from the offloaded from the OLTP databases OLTP databases

Reporting data can be Reporting data can be a subset of source a subset of source tables and columns tables and columns and also indexed and also indexed differentlydifferently

Read-only replicasRead-only replicas

Database Scale OutDatabase Scale Out

Queries scaled out Queries scaled out (often (often geographically) geographically) similar to reporting similar to reporting casescases

But databases But databases replicate replicate reciprocally and are reciprocally and are writablewritable

Redundancy Redundancy provides fault provides fault tolerance and tolerance and lowers lowers maintenance maintenance downtimedowntime

Online upgrades Online upgrades possiblepossible

London

Chicago

Tokyo

Mobile CRM / SFAMobile CRM / SFACustomer Relationship Customer Relationship ManagementManagementSales Force AutomationSales Force Automation

Key Traits Subset of

data from server

Conflicts Data

realignment Business

Logic

Point of SalePoint of Sale

Key Traits High latency

low bandwidth network

Need low cost software and administration

Sync at scheduled times

Upload orders then remove

Regional ReplicationRegional Replication

Central Central headquarters data headquarters data replicated to 1200 replicated to 1200 storesstores

Each store also Each store also replicates between replicates between two databases to two databases to provide a backup provide a backup in case one failsin case one fails

Database Database administrators are administrators are centralizedcentralized

Regional ReplicationRegional Replication

Each ship contains Each ship contains a SQL Server a SQL Server databasedatabase

When the ship When the ship docks, it replicates docks, it replicates to the to the headquarters’ headquarters’ databasedatabase

Replication was Replication was customized to customized to perform additional perform additional processing when processing when synchronizingsynchronizing

SQL ServerSQL Server


Customized Customized ReplicationReplication

LogicLogic


Transactional Replication

HeadquartersHeadquarters

Oracle ReplicationOracle Replication SQL Server 2005 SQL Server 2005

Transactional Transactional Replication Replication

SQL Server SQL Server synchronized with synchronized with Oracle production line Oracle production line system in near real-system in near real-time time

Used to monitor Used to monitor manufacturingmanufacturing

Reporting: Quality Reporting: Quality control, status, and control, status, and “what if” queries “what if” queries spread across multiple spread across multiple databasesdatabases

Manufacturing SystemManufacturing System

OracleOracle10G10G

SQL Server 2005SQL Server 2005Transactional

Replication

Top SQL Server CustomersTop SQL Server CustomersTransactional ReplicationTransactional Replication

Runs many core trading applications on SQL Runs many core trading applications on SQL ServerServer

Replicates data to integrated multiple trading Replicates data to integrated multiple trading systemssystems

15+ TB data in multiple systems, multiple 15+ TB data in multiple systems, multiple hospitalshospitals

Replicates to meet HA requirementsReplicates to meet HA requirements

Leading discount retailer, SQL Server in 1200 Leading discount retailer, SQL Server in 1200 storesstores

Replicates data up and down between stores & Replicates data up and down between stores & HQHQ

[Click logo for case-study]

http://www.microsoft.com/resources/casestudies/CaseStudy.asp?CaseStudyID=14483

http://www.microsoft.com/resources/casestudies/CaseStudy.asp?CaseStudyID=14967

http://members.microsoft.com/CustomerEvidence/search/EvidenceDetails.aspx?EvidenceID=38&LanguageID=1&PFT=Microsoft%20SQL%20Server%202000&TaxID=20364

AgendaAgenda




Upgrade: ReplicationUpgrade: Replication

SQL Server 2005 Express Edition SQL Server 2005 Express Edition does not support Merge publishingdoes not support Merge publishing SQL Server 2000 MSDE did support itSQL Server 2000 MSDE did support it

SQL Server Express requires custom SQL Server Express requires custom code or Windows Sync Manager to code or Windows Sync Manager to synchronizesynchronize

If you run agents as DBO, now If you run agents as DBO, now requires a requires a job_loginjob_login and and job_passwordjob_password to be added to to be added to replication scriptsreplication scripts

Upgrade: Merge Upgrade: Merge ReplicationReplication Snapshot must be executed after Snapshot must be executed after

setupsetup Upgrade orderUpgrade order

Distributor, publisher, subscriber Distributor, publisher, subscriber Applications must be built on Visual Applications must be built on Visual

Studio 2005 to use programming APIsStudio 2005 to use programming APIs SQL Server 2005 subscribers SQL Server 2005 subscribers

outperform previous versionsoutperform previous versions

AgendaAgenda




Database Schema Database Schema ChangesChanges One of the key problems when One of the key problems when

replicating data is accommodating replicating data is accommodating schema changes without disrupting schema changes without disrupting replicationreplication

SQL Server 2005 supports adding, SQL Server 2005 supports adding, dropping, and altering columns dropping, and altering columns without stopping replicationwithout stopping replication Alter table alter column ( foo int )Alter table alter column ( foo int )

Tables and other supported objects Tables and other supported objects can also be added or droppedcan also be added or dropped

Database Scale-Out Database Scale-Out RequirementsRequirements Maximize website uptimeMaximize website uptime

Database upgrades or failures shouldn’t bring Database upgrades or failures shouldn’t bring down the application systemdown the application system

Database upgrades should be done without Database upgrades should be done without outagesoutages

Scale query workloads beyond what’s Scale query workloads beyond what’s possible with a single database possible with a single database Must be possible to add or remove databases Must be possible to add or remove databases

without an outage while also maintaining without an outage while also maintaining consistencyconsistency

SQL Server 2005 Peer-to-Peer Replication SQL Server 2005 Peer-to-Peer Replication provides this capabilityprovides this capability Workbench wizard allows easy replication setupWorkbench wizard allows easy replication setup

Distribution Agent

DistDB

Logreader Agent

Distribution Agent

DistDB

Logreader Agent

Distribution Agent

DistDB

Logreader Agent

London Chicago

Tokyo

Peer-to-Peer TopologyPeer-to-Peer Topology

Transactional Replication

Replicating Large Replicating Large DatabasesDatabases The key problem with replicating The key problem with replicating

large databases is initializing the large databases is initializing the subscribersubscriber This can be time-consuming and This can be time-consuming and

requires downtime to ensure consistency requires downtime to ensure consistency of the replicated databaseof the replicated database

SQL Server 2005 has support for SQL Server 2005 has support for using an existing database backup to using an existing database backup to perform the initial synchronizationperform the initial synchronization Initialization with a backupInitialization with a backup

Changes that have occurred on the Changes that have occurred on the source system after the backup are source system after the backup are tracked and synchronized tracked and synchronized automaticallyautomatically

Synchronizing Data Over the Synchronizing Data Over the WebWeb Key customer driversKey customer drivers

Wireless increasingly pervasiveWireless increasingly pervasive Virtual Private Network (VPN) too Virtual Private Network (VPN) too

expensive or complicated for expensive or complicated for organizationsorganizations

-InternetUrl -InternetUrl https://myserver.mydomain.comhttps://myserver.mydomain.com

Publisher/DistributorPublisher/Distributor

FirewallFirewall

Internet Internet Information Information

Services (IIS)Services (IIS)SubscriberSubscriber

Replisapi.dllReplisapi.dll

Logical RecordsLogical Records

ProblemProblem Merge replication tracks changes row by Merge replication tracks changes row by

rowrow An actual order is represented in the An actual order is represented in the

Customers, Orders, OrderDetails tablesCustomers, Orders, OrderDetails tables Disconnection during sync results in Disconnection during sync results in

incomplete order on serverincomplete order on server SolutionSolution

Logical records create a logical Logical records create a logical consistency unit between tablesconsistency unit between tables

All three changes are sent or nothing at All three changes are sent or nothing at allall

Business LogicBusiness Logic

APIs to allow custom programming APIs to allow custom programming during the synchronization of during the synchronization of changeschanges

Allows developer to take custom Allows developer to take custom actions on different states of dataactions on different states of data Publisher insert, update, deletePublisher insert, update, delete Subscriber insert, update deleteSubscriber insert, update delete

Business Logic is registered on a per Business Logic is registered on a per article basisarticle basis

Merge Performance Merge Performance ComparisonComparison

Rows Per Second

848

17511656

1968

3296

0

500

1000

1500

2000

2500

3000

3500

SQL 2000 SQL 2005backcompat mode

SQL 2005 Pre-ComputedPartitions

Field Force, POSApplications

Reference Data

Scalability ComparisonScalability Comparison

Synchronization Time

0

100

200

300

400

500

600

700

800

900

1000

20 50 100

# of concurrent syncs

Ave

rag

e S

ync

Tim

e

SQL 2000

SQL 2005

MonitoringMonitoringGoalsGoals

Provide answers to common Provide answers to common questions:questions: Why is the system slow?Why is the system slow? How long until it catches up or finishes?How long until it catches up or finishes? Where are the potential problems?Where are the potential problems?

Oracle PublishingOracle Publishing

Designed specifically for Oracle PublishersDesigned specifically for Oracle Publishers v8+ on any operating system v8+ on any operating system

Administered like SQL Server, from SQL Administered like SQL Server, from SQL ServerServer No Oracle-side software install necessaryNo Oracle-side software install necessary Requires minimal knowledge of Oracle Requires minimal knowledge of Oracle

Leverages existing SQL Server skillsLeverages existing SQL Server skills Standard Transactional and Snapshot Standard Transactional and Snapshot

PublicationsPublicationsSQL ServerSQL Server

20052005DistributorDistributor


Transactional Transactional ReplicationReplication

ORACLE

Many Other New FeaturesMany Other New Features

Transactional tracer tokenTransactional tracer token Merge Article typesMerge Article types

Tables, views, stored procedures, user-Tables, views, stored procedures, user-defined functions, and stored procedure defined functions, and stored procedure executionexecution

Resumable snapshotResumable snapshot Parallelism and contention Parallelism and contention

improvementsimprovements Wizards simplifiedWizards simplified Replication Management Objects Replication Management Objects

((RMORMO) managed replication APIs) managed replication APIs SecuritySecurity

SummarySummary




For More InformationFor More Information Books Online Replication SectionBooks Online Replication Section Merge ReplicationMerge Replication

White PaperWhite Paper Oracle Oracle

Oracle Publishing Quick Start WhitepaperOracle Publishing Quick Start Whitepaper SQL Server 2005 Oracle Replication SQL Server 2005 Oracle Replication

OverviewOverview Peer-to-Peer Peer-to-Peer

Books OnlineBooks Online

distributing data for availability and scalability don vilen program manager sql server microsoft...

Documents