scale out scenarios with transaccional replication
DESCRIPTION
Transactional Replication exists in SQL Server since 1995; It is a feature that used correctly brings customization and scalability to your data. Considering your solutions from the data-flow perspective, SQL Server Transactional Replication allows you to move data (articles) across servers transparently to your ERP/LOB applications. In this session we will introduce a real customer scenario moving data from OLTP to DW server almost transparently. You will see how and where to make the changes/transformations to your data to addecuate to your business rules. To close the cicle you will see how to consume the data from SSAS, and how to customize it to near-real time sincronization.TRANSCRIPT
![Page 1: Scale out scenarios with transaccional replication](https://reader033.vdocuments.us/reader033/viewer/2022052907/559412771a28ab0b618b4801/html5/thumbnails/1.jpg)
Scale-out Scenarios with
Transactional Replication
Eladio Rincón
SolidQ, OLTP Director for Spain&Portugal
SQL Server MVP
![Page 2: Scale out scenarios with transaccional replication](https://reader033.vdocuments.us/reader033/viewer/2022052907/559412771a28ab0b618b4801/html5/thumbnails/2.jpg)
The Sponsors
![Page 3: Scale out scenarios with transaccional replication](https://reader033.vdocuments.us/reader033/viewer/2022052907/559412771a28ab0b618b4801/html5/thumbnails/3.jpg)
The Volunteers
They spend their FREE time to give you this
event. (2 months per person)
Because they are crazy.
Because they want YOU
to learn from the BEST IN THE WORLD.
If you see a guy with “STAFF” on their back –
buy them a beer, they deserve it.
![Page 4: Scale out scenarios with transaccional replication](https://reader033.vdocuments.us/reader033/viewer/2022052907/559412771a28ab0b618b4801/html5/thumbnails/4.jpg)
Paulo Matos:
![Page 5: Scale out scenarios with transaccional replication](https://reader033.vdocuments.us/reader033/viewer/2022052907/559412771a28ab0b618b4801/html5/thumbnails/5.jpg)
Paulo Borges:
![Page 6: Scale out scenarios with transaccional replication](https://reader033.vdocuments.us/reader033/viewer/2022052907/559412771a28ab0b618b4801/html5/thumbnails/6.jpg)
João Fialho:
![Page 7: Scale out scenarios with transaccional replication](https://reader033.vdocuments.us/reader033/viewer/2022052907/559412771a28ab0b618b4801/html5/thumbnails/7.jpg)
Bruno Basto:
![Page 8: Scale out scenarios with transaccional replication](https://reader033.vdocuments.us/reader033/viewer/2022052907/559412771a28ab0b618b4801/html5/thumbnails/8.jpg)
Upcoming SQL Server events:
XXXIII Encontro da Comunidade SQLPort
Data Evento: 23 Abril 2013 - 18:30
Local do Evento: Auditório Microsoft, Parque das Nações, Lisboa
18:30 - Abertura e recepção.
19:10 - "Analyzing Twitter Data" - Niko Neugebauer (SQL Server MVP, Community Evangelist – PASS)
20:15 - Coffee break
20:30 - "First Approach to SQL Server Analysis Services" - João Fialho (Consultor BI Independente)
21:30 - Sorteio de prémios
XXXIV Encontro da Comunidade SQLPort
Data Evento: 7 Maio 2013 - 19:00
Local do Evento: Porto
18:30 - Abertura e recepção.
19:00 - «Apresentação para Developers» - para definir
20:15 - Coffee break
20:30 - «Apresentação para definir» - para definir
21:30 - Sorteio de prémios
![Page 9: Scale out scenarios with transaccional replication](https://reader033.vdocuments.us/reader033/viewer/2022052907/559412771a28ab0b618b4801/html5/thumbnails/9.jpg)
Eladio Rincón
OLTP Director @ SolidQ Spain & Portugal
SQL Server MVP since 2003
Manages with other MVPs PASS Spanish Chapter
What I do?
Designing HA and DR solutions
Troubleshooting and Optimization
Complex Upgrade and Migration projects
Datawarehousing on PDW and Fast Track DW
![Page 10: Scale out scenarios with transaccional replication](https://reader033.vdocuments.us/reader033/viewer/2022052907/559412771a28ab0b618b4801/html5/thumbnails/10.jpg)
Agenda
The business Case to Improve
Transactional Replication Concepts
Demo: Seting up Transactional Replication
Demo: Applying Business Logic (Transform)
Demo: Consuming Data (Query)
![Page 11: Scale out scenarios with transaccional replication](https://reader033.vdocuments.us/reader033/viewer/2022052907/559412771a28ab0b618b4801/html5/thumbnails/11.jpg)
Business Case to Improve
Processing Type Online (OLTP)
Analytical (BI / DW)
Batches (mix OLTP y BI)
Resources needed IOPS – IO Subsystem
Volume – IO Subsystem
Processing – CPU
Concurrency – Apps
![Page 12: Scale out scenarios with transaccional replication](https://reader033.vdocuments.us/reader033/viewer/2022052907/559412771a28ab0b618b4801/html5/thumbnails/12.jpg)
Proposed Architecture
Roles
Diversification
![Page 13: Scale out scenarios with transaccional replication](https://reader033.vdocuments.us/reader033/viewer/2022052907/559412771a28ab0b618b4801/html5/thumbnails/13.jpg)
Proposed Architecture
Objects Location
Data in Several Servers
Sync-ing Objects
Data Coordination
Business Rules and
Processing Rules
Might need to process in
several servers
Cons Pros
Scalability
Scale-out
Async Objects
Processing
Non Real Time
Processing
Resources Fine-
Allocation
![Page 14: Scale out scenarios with transaccional replication](https://reader033.vdocuments.us/reader033/viewer/2022052907/559412771a28ab0b618b4801/html5/thumbnails/14.jpg)
Proposed Architecture:
Technology
Transactional Replication
Allocate the Data in Different Servers/Sites to:
Async Processing
Ad-hoc Reporting
Data Aggregation
SQL Server Analysis Services
Data Aggretation Strenghts
Client Tools for Querying (Excel Self-Service BI)
In Multi-Dimensional Proactive Caching
![Page 15: Scale out scenarios with transaccional replication](https://reader033.vdocuments.us/reader033/viewer/2022052907/559412771a28ab0b618b4801/html5/thumbnails/15.jpg)
Transactional Replication – Concepts
![Page 16: Scale out scenarios with transaccional replication](https://reader033.vdocuments.us/reader033/viewer/2022052907/559412771a28ab0b618b4801/html5/thumbnails/16.jpg)
Transactional Replication – Concepts
![Page 17: Scale out scenarios with transaccional replication](https://reader033.vdocuments.us/reader033/viewer/2022052907/559412771a28ab0b618b4801/html5/thumbnails/17.jpg)
Transactional Replication: Demo Scenario
Publisher Subscriptor
SNUCKI9\SQL2012 SNUCKI9\SQL2012DEV
Source DB Destination DB
AdventureWorksLT2012 MyDW
Source Table Destination Table
SalesOrderDetail SalesOrderDetail
SalesOrderHeader SalesOrderHeader
![Page 18: Scale out scenarios with transaccional replication](https://reader033.vdocuments.us/reader033/viewer/2022052907/559412771a28ab0b618b4801/html5/thumbnails/18.jpg)
Setup
Transactional
Replication
![Page 19: Scale out scenarios with transaccional replication](https://reader033.vdocuments.us/reader033/viewer/2022052907/559412771a28ab0b618b4801/html5/thumbnails/19.jpg)
Applying Business Logic to the
Distributed Data
Replication
![Page 20: Scale out scenarios with transaccional replication](https://reader033.vdocuments.us/reader033/viewer/2022052907/559412771a28ab0b618b4801/html5/thumbnails/20.jpg)
Applying
BL to
Distributed Data
![Page 21: Scale out scenarios with transaccional replication](https://reader033.vdocuments.us/reader033/viewer/2022052907/559412771a28ab0b618b4801/html5/thumbnails/21.jpg)
Consuming Data
Multi-Dimensional or Tabular Models
Pre-calculated data
Less resources usage (CPU, IO)
Periodical refresh: what business says
SQL Server Agent jobs
Proactive Caching (notifications)
Data Consumption
Excel or Reporting Services
Flexible vs less-flexible
![Page 22: Scale out scenarios with transaccional replication](https://reader033.vdocuments.us/reader033/viewer/2022052907/559412771a28ab0b618b4801/html5/thumbnails/22.jpg)
Consuming
Data
![Page 23: Scale out scenarios with transaccional replication](https://reader033.vdocuments.us/reader033/viewer/2022052907/559412771a28ab0b618b4801/html5/thumbnails/23.jpg)
Following these Techniques
Servers 1
Procs 32
Memory 128GB
CPU Consumpt. +80% avg
SAN High
Batches/sec 1400
Activity
OLTP 40%
BI-Low 25%
BI-Medium 30%
Servers 2
Procs 32 (Agg)
Memory 64GB (Agg)
CPU Consumpt. 25% +30%
SAN Low
Batches/sec 2600
Activity
OLTP 30%
BI-Low 30%
BI-Medium 35%
![Page 24: Scale out scenarios with transaccional replication](https://reader033.vdocuments.us/reader033/viewer/2022052907/559412771a28ab0b618b4801/html5/thumbnails/24.jpg)
Final Thoughts
Combine existing Technologies Partitioning, Replication, AlwaysOn, Log Shipping
Improving the Infra (hardware) really helps (ROI) Memory at very atractive prices
CPU and IO nice price
Escalability vs Architecture Design your solution (Software) with Escalability in mind
Adjust the Technology to your Solution needs (Software)
![Page 25: Scale out scenarios with transaccional replication](https://reader033.vdocuments.us/reader033/viewer/2022052907/559412771a28ab0b618b4801/html5/thumbnails/25.jpg)
Eladio Rincón
www.elrincondelDBA.com
www.SolidQ.com