Tag: REPLICATION

No comments

MariaDB/MySQL – InnoDB_flush_log_at_trx_commit

  By , , ,

In this blog post, we are going to talk about the variable InnoDB_flush_log_at_trx_commit. We are going to discuss what each value that we can assign to this variable means, and how it can affect performance and durability.

Innodb_flush_log_at_trx_commit controls the durability in ACID compliance

A – atomicity

C – consistency

I – isolation

D – durability

The possible values for this variable is 0, 1, and 2.

The specific path of the query goes to the innodb buffer pool, then log buffer (redo logs), then OS buffer, then finally to log file.

When innodb flush log at transaction commit is set to 0, your write goes thru memory, thru the buffer pool into the log buffer. That write then flushes from the log buffer to the log file on disk for every 1 second or when the OS flushes.

If this variable is set to 1, which is maximum durability. Your write goes to the log buffer, but the commit of the file ensures that it is written all the way on disk. This value will have a bit of performance hit compared to value 0.

If the value is set to 2, the write goes to the log buffer, but the file will be committed all the way to the OS buffer. And then the OS will flush to disk roughly every 1 second.

Advantages and Disadvantages

0 – when the database crashes, the log buffer within memory will get loss, and there is a possibility of losing those transaction. This setting is for performance, but not for durability.

1 – every write will surely be written to the redo log on disk. You will not lose that write regardless of the crash.

2 – You will lose only about 25% performance as compared to 1. If the DB crashes, the file is still written to disk cache, and then written to disk later. But if the DB server itself crashes, that DB server’s disk buffer may lose its data. This can be prevented though if we have battery backup or SAN.

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

MariaDB/MySQL – Difference Between Undo and Redo

  By , ,

Undo log is used to keep track of changes performed by active transactions and roll them backup if necessary. It is physically stored in the system table spce and, optionally, in other tablespaces.

Redo log tracks data of the requested data changes and is used to recover tables after a crash. It is physically stored in dedicated files.

Redo and Undo logs are both used during crash recovery.

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL Server – How To Configure Peer-to-Peer Replication in SQL Server 2016

  By ,

First, let’s run through the Replication components before we setup Peer-to-Peer replication.

  • Article – the object we want to replicate
  • Publication – Group of articles
  • Publisher – Server that contains publication
  • Subscriber – Receives publication
  • Distributor – Controls agent and tracks subscribers
  • Agent – Tasks to move data

What is Peer-to-Peer Replication?

In Peer-to-Peer, what it does is that it takes the one-way street where everything that happens in the publisher gets pushed off to the subscriber and turns it into a two-way street (bi-directional replication). Everything that happens in the subscriber can also be pushed to the publisher. In Peer-to -Peer, we only have conflict detection not conflict resolution. The key with peer to peer is  to avoid conflict entirely.

Steps To Setup Peer-to-Peer Replication:

1. Create distribution on all instances that are participating in Peer-to-Peer.

You must configure a distributor for each node that will be participating in peer-to-peer else you will encounter the error below.

To set up distributor, follow the steps below.

right-click on Replication > Configure Distribution…

Click Next.

Choose the first option. Click Next.

Specify a snapshot folder path. Click Next.

Click Next.

Click Next.

Click Next.

Click Finish.

Click Close.

Note: Do the same for all the instances that will be participating in Peer-to-Peer Replication

2. Backup the databases to replicate then restore it in the subscriber node (data needs to be synchronized first before we configure peer-to-peer)

3. Create publication.

Right-click on Local Publication > New Publication…

Click Next.

Choose the database where the articles to be published are located. Click Next.

Choose Peer-to-Peer Publication. Click Next.

Choose the object/s to publish. Click Next.

Click Next.

It is a best practice to specify the account that you will use to run the Log Reader Agent process. However, for the purpose of this Demo, I will just use the SQL Server Agent service account. Click OK.

Click Next.

Optionally, you can generate a script file with steps to create the publication. I will not choose to in this demo.

Click Next.

Give your publication a name then click Finish.

Click Close.

4. Configure Peer-to-Peer topology.

Right-click on your publication > Configure Peer-to-Peer Topology.

Choose the newly created publication. Click Next.

Hover your cursor on the first node. Take note of the Peer originator id. As you can see, it is 100.

Now, on the topology Wizard,  right-click on the design surface> Add a New Peer Node.

Connect to subscriber node.

Select the database on your subscriber. This is the database that we restored from your publisher. If you recall, at the beginning of this tutorial, that we backed up the database that we want to replicate and restored it in the subscriber node.

The Peer Originator ID of the first node is 100 remember? This is now the 2nd node and we will set the Peer Originator ID to 101. If we want add more nodes to the topology, then just set the Peer Originator ID of the next node to 102, the next node to 103, etc. You get the idea.

Tick Connect to ALL displayed nodes and choose Use Push subscription (optionally you can choose Pull subscription)

Click OK.

Now we can see the 2nd node that we just added visually on the design surface.

Click Next.

Click the ellipsis…

It is a best practice to specify an account that you will use to run the Log Reader Agent process. However, for the purpose of this Demo, I will just use the SQL Server Agent service account. Click OK.

Click Next.

Click the ellipsis…

It is a best practice  to specify an account that you will use to run the Distribution Agent process. However, for the purpose of this Demo, I will just use the SQL Server Agent service account. Click OK.

Tick the “Use the first peer’s security settings for all other peers”. This is useful especially if you have many peers configured.

Click Next.

In the next page of the wizard,  we have to specify how we want the new peer(s) to be initialized. Choose the first option if you know that there are no changes made since the last the backup was taken. However, if there were data modification after the backup was taken, choose the second option.

Click Next.

Review the choices that you have made, then click Finish.

Click Close.

In this blog post,  we have learned how to setup Peer-to-Peer Replication. Everything that happens in the subscriber will also be published to the publisher. Just take note that in Peer-to-Peer, we only have conflict detection not conflict resolution. We have to avoid conflict entirely.

 

 

2 Comments

SQL Server – Reinitializing a Single Article In Transaction Replication

  By ,

In this blog post, I will list down the steps on how to re-initialize just a single article in transaction replication

1.  Turn off @allow_anonymous and @immediate_sync on the publication.

use PUBLICATION_DB_NAME
go
EXEC sp_changepublication
@publication = 'Replication_Test',
@property = N'allow_anonymous',
@value = 'false'
GO
               
EXEC sp_changepublication
@publication = 'Replication_Test',
@property = N'immediate_sync',
@value = 'false'
GO

The reason we have to disable @immediate_sync is that everytime you add a new article, and if @immediate_sync is enabled, it will cause the entire snapshot to be applied. Our objective is to only apply a particular article.

2.  Add new article.

EXEC sp_addarticle
@publication = 'Replication_Test',
@article = 'REPL_TABLE',
@source_object = 'REPL_TABLE',
@force_invalidate_snapshot = 1

3.  Refresh the subscription

EXEC sp_refreshsubscriptions @publication = 'Replication_Test'
GO

4.  Check the current snapshot agent history.

use distribution
go
select * from dbo.MSsnapshot_history

5.  Start Snapshot agent.

EXEC sp_startpublication_snapshot @publication = 'Replication_Test';
GO

6.  Check the Snapshot Agent history again. You should see a snapshot generated only for the newly added article/s.

7.  Turn ON @allow_anonymous and @immediate_sync on the publication.

use PUBLICATION_DB_NAME
go

EXEC sp_changepublication
@publication = 'Replication_Test',
@property = N'immediate_sync',
@value = 'true'
GO


EXEC sp_changepublication
@publication = 'Replication_Test',
@property = N'allow_anonymous',
@value = 'true'
GO

This is how you can re-initialize only the required articles.