Replication doesn’t send the entire database to another location, unlike log shipping or always-on solutions. Instead, it has a unique advantage – you can choose to replicate specific parts of the database, like views, tables, or any other objects you want.

In essence, replication gives you the flexibility to pick and choose exactly what you want to replicate, making it a more selective and efficient way to distribute data to another location.

replication perquisites

In a replication setup, it’s recommended to use a domain account, often referred to as a service account, for the replication process. This account should be granted the necessary permissions(sysadmmin) on all servers that are part of the replication
network shared folder to be used to share snapshot between server
table must have primiray key otherwise it wont be able to be add in article

type of replication

• Transactional replication
• Peer to peer replication
• Snapshot replication
• Merge replication

Transactional replication

Replication works by replicating each transaction for the articles that have been published. When a transaction is written to the transaction log (‘t-log’) of the publisher, a component called the ‘log reader agent’ scans this log. It then takes the transactions it finds and writes them to the distribution database. From there, the data is further distributed to the subscribers.
Only committed transactions are replicated to maintain data consistency. Uncommitted transactions, those that haven’t been confirmed as complete, will not be included in the replication process
Transaction replication is commonly used in scenarios where there’s no room for delay, like in banks and stock markets. These environments require real-time updates to stay current.
In essence, transaction replication is the go-to solution in situations where even a slight delay is not acceptable, ensuring that data stays up-to-the-minute
In transactional replication, we have the Log Reader Agent. Its job is to take transactions that have been earmarked for replication, found in the Publisher’s transaction log, and transfer them to the distribution database
Each database published using transactional replication has its own Log Reader Agent that runs on the Distributor
Push: In a push subscription, data is pushed from the publisher to the subscriber. It’s like the publisher taking the initiative to send updates to the subscriber.
Pull: In a pull subscription, the subscriber requests changes from the publisher.

Distribution Agent

Distribution Agent: It plays a role in both snapshot replication and transactional replication. Its main tasks are to apply the initial snapshot to the subscriber and to transfer transactions stored in the distribution database to subscribers.
Location: The Distribution Agent operates either at the distributor (for push subscriptions) or at the subscriber (for pull subscriptions). In essence, it runs where it’s needed to ensure data flows smoothly to subscribers.

setup Transactional replication

first create user that will be used to authenticate the replication , for best practises create domain account for replication
add the use to server participating in the replication with (SYSADMIN) privilege
i have create database called foods , and add to table one with primary key , one without primary key , later you will see why

4.Start by configuring the distributor:

Navigate to the ‘Replication’ section.
Right-click and select ‘Configure Distributor.’

When you’re in the wizard, it will inquire about the location for the distributor. In best practices, it’s recommended to have a separate MS SQL server dedicated solely to this distributor task. However, for testing purposes, you can choose to keep the distributor on the current server

6.At this point, the wizard will prompt you to specify a path for saving the snapshot. It’s advisable to use a network path for this purpose. This way, subscribers can easily access and obtain the snapshot when needed , make sure the account we crated in step 2 has assess to the network shared file

7.The wizard will inquire about the location for the distributor database’s MDF and LDF files. By default, it uses the server’s default path for these files. If this suits your needs, you can simply click ‘Continue.’ However, if you prefer, you can choose to change the file path

8.now the wizard asking for publisher and prompt you to add more publisher , for this step just skip and click ‘next ‘
9. after that wizard will configure the distputer

To configure the publisher, start by right-clicking on ‘Local Publisher’ and then choose ‘New Publisher’ from the menu
The wizard will prompt you to specify the database you want to replicate. For this example, you can select ‘Foods.
Next, the wizard will ask you to choose the type of publishing. In this case, select ‘Transactional Replication,’ and then click ‘Next.
Next, you’ll need to select the tables you want to publish. It’s important to note that tables without a primary key won’t be allowed to be published. In the image below, you can see one table marked in red because it lacks a primary key

14.Next, you’ll encounter the ‘Snapshot’ window. In this step, you’ll want the agent to create a snapshot and initialize the subscriber. Be sure to checkmark this option
Now, you’ll reach the ‘Agent Security’ configuration. Here, you should use the account you created in step 2. It’s important to avoid running the agent under the SQL Server Agent services account

In addition, you’ll want to add the user (rep) that you created earlier for this process.

16 remining of wizard it will ask name for publication ,the it will start configure the publication

subscriber to publication

we have secsafully configured the distrputer and create our first publisher m now we need to add subscriber to publisher to be able to receive updates

to add subscriber expand replication section , right click on local subscriber , then select new subscriber

After the ‘Welcome’ window, you’ll be asked to specify the publisher.

Following that, you’ll encounter the ‘Distribution Agent Location’ window with two distinct options. You’ll need to make a choice:

Push: With this option, you push the articles from the distributor to the subscribers. It’s a good choice when you want to run all agents on the distributor.
For our purpose, selecting ‘Push’ is recommended

now your asked to specify the subscriber, we will add the second sever SQLSTG as subscriber
click add subscriber

Obviously, we don’t have the database already set up on the subscriber server. But there’s a solution—just select the ‘New Database’ option

In the ‘Distribution Agent Security’ window, you’ll need to add the account that you previously created for replication, which is ‘rep.’

Make sure that this account is added with the ‘sysadmin’ role on the subscriber server

rest of steps ask if want to start now or scheduled it later then once done the subscriber will start pulling from publisher

What is Peer-to-Peer Transactional Replication?

Peer-to-Peer Replication: This is when you have multiple servers, and each server acts as both a publisher and a subscriber, maintaining its own distributor. It allows for real-time synchronization of updates, inserts, and deletes between these servers.
Topology: In this design, there’s a master server to which other servers connect. These connected servers are often called ‘nodes.’
Resilience: Even if one of these locations goes offline, the others can continue to stay synchronized. This is because each node has the capability to act as both a publisher and a subscriber.
Database Backup and Restore: To make this setup work, you’ll need to regularly back up and restore databases on each server involved in replication.
Distributor Activation: Additionally, you’ll need to enable the distributor functionality on each server that’s part of the replication network. This helps manage the flow of data between them.

Understanding SQL Server Replication: Types, Prerequisites, and Key Components