/*==================================================================================
Curso: SQL SERVER 2019
https://www.udemy.com/course/draft/3957796/?referralCode=FB10D369E786D9FE8A48

Instrutor: Sandro Servino
https://www.linkedin.com/in/sandroservino/?originalSubdomain=pt
https://filiado.wixsite.com/sandroservino
==================================================================================*/


/*==================================================================================
Curso: SQL SERVER 2019
https://www.udemy.com/course/draft/3957796/?referralCode=FB10D369E786D9FE8A48

Instrutor: Sandro Servino
https://www.linkedin.com/in/sandroservino/?originalSubdomain=pt
https://filiado.wixsite.com/sandroservino
==================================================================================*/


/*==================================================================================
ATIVIDADES ROTINEIRAS BÁSICAS DO DBA
Replicaçäo de Dados

Tipos de Replicacao:

1. Snapshot     (https://docs.microsoft.com/en-us/sql/relational-databases/replication/snapshot-replication?view=sql-server-ver15)
2. Transacional (https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-replication?view=sql-server-ver15)
3. Merge        (https://docs.microsoft.com/en-us/sql/relational-databases/replication/merge/merge-replication?view=sql-server-ver15)
4. Peer to Peer (https://docs.microsoft.com/en-us/sql/relational-databases/replication/publication-peer-to-peer-replication?view=sql-server-ver15)

-- The main difference is that for merge replication there is only one publisher and one or more subscribers, 
but in peer-to-peer replication all nodes are both publishers and subscribers(though original node is highlighted with green arrow).
-- Merge replication änd Peer to Peer has conflict resolution(you can specify conflict resolution priority).
-- In peer-to-peer replication all nodes are identical while in merge they can differ. 
   I mean that subscribers can get different data from the publisher.
-- They both are basically doing the same job
-- Peer-to-peer is only available in Enterprise Edition.

-- Uma forma de resolver conflitos é trabalhar no modelo de dados, atraves das chaves primarias para particionar os dados ou se nao
-- for possivel, delimitar faixas de dados para as chaves por determinados locais ou clientes, ou similares, mas isto nao evitará por exemplo
-- do mesmo cliente ser inserido nas duas bases de dados e todas as suas relacoes com valores de chaves diferentes e a complexidade de se juntar
-- todos os dados do mesmo cliente em apenas uma chave primaria e a delecao ou desabilitacao do mesmo cliente que estava vinculado em outra chave
-- primaira é enorme, entao o ideal é apenas 1 ponto de entrada de alteracao de dados para evitar problemas de integridade. A propria MS recomenda:
"We strongly recommend that write operations for each row be performed at only node, for the following reasons:
If a row is modified at more than one node, it can cause a conflict or even a lost update when the row is propagated to other nodes”. (Microsoft)
"
-- Neste caso, frente ao risco, nao vejo sentido, considerando hoje as nuvens e a grande velocidade dos links, trabalharmos com bases replicadas
-- com alteracoes em todas as instancias.

Náo é Replicacao, mas comentario rapido:
- Log shipping
- Mirror (Ainda disponivel na versão 2019, mas marcado como deprecated, substituir por always on)

Deprecated database engine features in SQL Server 2019 (15.x)
https://docs.microsoft.com/pt-br/sql/database-engine/deprecated-database-engine-features-in-sql-server-2017?view=sql-server-ver15

"Deprecated
However, under rare situations, we may choose to permanently remove the feature 
from SQL Server if it limits future innovations.
For new development work, we do not recommend using deprecated features."

Referencia para configurar merge e peer to peer
-- https://www.sqlshack.com/sql-server-replication-configuration-peer-to-peer-and-merge-replication/

==================================================================================*/


 -- VAMOS AO LABORATORIO TRANSACTION REPLICATION

https://docs.microsoft.com/pt-br/sql/relational-databases/replication/tutorial-preparing-the-server-for-replication?view=sql-server-ver15
https://docs.microsoft.com/pt-br/sql/relational-databases/replication/tutorial-replicating-data-between-continuously-connected-servers?view=sql-server-ver15