DBI 314 Zen 4 Sync A SQL Server

DBI 314 Zen 4 Sync: A SQL Server Replication Orchestration Platform Jean-Yves Devant Program Manager - SQL Server Microsoft Corporation Christophe Bigot Senior Analyst - Application Development Avanade France

Senior Analyst Application Development Avanade France christophe. bigot @avanade. com zen 4 sync. codeplex. com Christophe Bigot Former developer of Zen 4 Sync

Program Manager - SQL Server Microsoft Corporation jeanyd@microsoft. com zen 4 sync. codeplex. com Jean-Yves Devant Replication Change Data Capture Change Tracking Sustained engineering of HA Technologies

Agenda The story of Zen 4 Sync. The architecture. Demos. And now?

The story 15 years of history in SQL Server Making customers successful for their scenarios Offline scenario Sales force automation Automotive, medical applications Technical force automation Delivery/shipping business

Merge Replication SQL Server IIS HTTPS Edge, 4 G, 3 G, GPRS, Satellite • Size of deployments is increasing: • From 100 s and 1. 000 s to 10. 000 s of subscribers SQL Server Compact

Challenges Anticipating Performance Impact Latency Verifying business objectives are met Correctness of data Business rules Compatibility of business processes Testing and simulating Business scenarios Thousands of subscribers

• Testing and simulation tool • Not a product, no commitment, not a public announcement • An advanced prototype • Replication orchestration platform • Anticipate… capacity to interact with real enterprise backend • Demonstrate… scalability before deployment • Validate… • … business objectives/constraints and IT goals • … business rules have been honored

What Zen 4 Sync can do

Target non SQL Server Compact Merge Replication subscribers Target Transactional Replication subscribers

The architecture Database schema Roles

Test Server Name Client Test Session Name Scheduled Start Date Time Test Scenario Activity Type Name Activity Context Activity History Start Date Time End Date Time Error Merge sync. SQL execution SQL data check SQL CE backup MEF component

Zen 4 Sync You Clients Test Servers Orchestrators Repository Publisher Distributor SQL Server

Clients Test Servers Orchestrators Repository

Clients Test Servers Orchestrators

Clients Publisher Distributor SQL Server Test Servers

Zen 4 Sync You Clients Test Servers Orchestrators Repository Publisher Distributor SQL Server

Avanade Inc Global services innovator, helping customers realize the best results from the Microsoft platform. Joint venture created in 2000 by Accenture and Microsoft $ 1, 3 billion revenue in FY 11 - More than 60 offices in 20 countries More than 15, 000 professionals – 17 400 Microsoft certifications Microsoft Alliance Partner of the Year – Application Platform (2011) Zen 4 Sync: • Used for large project for technical force automation scenario (France) • Contributed to the evolution of the platform

Demo 1 Initialization of subscribers

Zen 4 Sync TECHED-Z 4 S-1 TECHED-CUSTOMER SQL Server 2012 IIS Northwind DB SQL Server 2012 SQL Compact 3. 5 SP 2 CU 6 Zen 4 Sync Orchestrator Zen 4 Sync Test Server TECHED-Z 4 S-2 SQL Compact 3. 5 SP 2 CU 6 Zen 4 Sync Test Server

Zen 4 Sync TECHED-Z 4 S-1 TECHED-Z 4 S-2 S Y N C TECHED-CUSTOMER

Demo 2 Reorganization of sales area and sync.

Zen 4 Sync TECHED-CUSTOMER Stored proc. TECHED-Z 4 S-1 Orders INSERT

And now?

mva

Learning Connect. Share. Discuss. Microsoft Certification & Training Resources http: //northamerica. msteched. com www. microsoft. com/learning Tech. Net Resources for IT Professionals Resources for Developers http: //microsoft. com/technet http: //microsoft. com/msdn

Complete an evaluation on Comm. Net and enter to win!

Scan the Tag to evaluate this session now on my. Tech. Ed Mobile

Appendix Additional resources Evaluation of filters ETL/DML challenge

http: //zen 4 sync. codeplex. com http: //support. microsoft. com/kb/2628887 http: //msdn. microsoft. com/en-us/library/dd 460648. aspx http: //mef. codeplex. com/

If you need to reach the red markers… Faster DML server side Faster syncs Setup belongs Partition groups … you need to reach a compromise. Smaller deployments Large number of concurrent subscribers Setup belongs Partition groups Consider the best practices shared in this session

ERP SQL Server Main publisher

Offline Scenario: The ETL/Massive DML Challenge Possible solution: SSIS Daily ERP SQL Server Main publisher Flat files te a c Staging tables n u Tr MERGE T-SQL statement not merge replication User tables

ERP SQL Server Main publisher