Getting Started with SQL Server Replication

=====================================

SQL Server replication allows you to create multiple copies of your database across different instances, improving availability, scalability, and performance. In this article, we'll cover the basics of getting started with SQL Server replication.

Why Use SQL Server Replication?

--------------------------------

Replicating data across multiple servers offers several benefits:

1. Improved Availability: If one instance fails or becomes unavailable due to maintenance, users can still access their data from another replica.

2. Scalability: By distributing the workload and storing data on different machines, you can handle larger volumes of traffic without affecting overall performance.

3. Read-Only Reporting: Replicating a read-only copy of your database for reporting purposes reduces the load on your primary system.

Types of SQL Server Replication

------------------------------

SQL Server provides several types of replication to suit various needs:

1. Full Database Backup and Restore

2. Log Shipping

3. Mirroring

This article will focus primarily on Log shipping, as it is one of the simplest forms of SQL server replication.

Getting Started with Log Shipping

### Prerequisites

Before you begin:

- Ensure that both source (Primary) and destination (Secondary) servers are running at least SQL Server 2000 Service Pack 2 or higher.

- Make sure Kerberos authentication is enabled on all machines involved in the process if Windows Authentication will be used.

Step-by-Step Instructions for Setting Up Log Shipping

### Step 1: Configure a Shared Folder

On both Primary and Secondary servers, create an empty folder that can be shared across both instances. This folder will serve as a destination point where transaction log files from the primary server will be copied to by the secondary server.

### Step 2: Enable Database Mirroring on Both Servers

Enable database mirroring between your source (Primary) and target (Secondary) servers using SQL Server Management Studio:

1. In Object Explorer, expand Server Name, then right-click DatabaseName.

2. From the context menu, select Properties.

3. Under Mirroring, check Mirror a Database to a Witness Server or Always On Availability Group`.

4. Enter either IP address of your secondary server in `MIRROR_SERVER_NAME`.

### Step 5: Configure Log Shipping using SQL Agent

1. In Object Explorer, right-click the SQL Server Agent node under the instance you want to configure.

2. Double click on it and expand Jobs > Right Click -> New Job

3. Name this job 'LogShippingJob'

4. Go back to the steps tab and double click on 'New Step'

5. Choose `Transact-SQL` as type and enter below query in the command field:

```

USE master;

EXEC sp_addlogreader_agent @jobname='LogShippingJob',@logtype=0, @monitors = ''Primary Server Name'';

```

6. Repeat steps 4-5 to add a new job called 'BackupSecondary' on Secondary server.

### Step 7: Schedule the Jobs

1. Double-click SQL Agent > Right click -> Properties.

2. Click on Schedules tab and create two new schedules named as 'PrimaryRun' & 'Secondryrun'.

3. Set start time for both to say every hour at 00:05 AM/PM

4. Now go back to your jobs created in step 5-6, double click on them.

5. Click on Schedules tab and select the newly created schedule.

### Step 8 (Optional): Configure Email Alerts

1. Right-click SQL Agent -> Properties->Alerts Tab

2. Add New Alert with condition as 'Last Run Out Time' > equal to 'NULL'

3. Under Action ->Email Profile, click on ellipses button and select the email profile created earlier.

4. Enter recipient's email id in To field

-------

In this article we learned about SQL Server Replication using Log shipping method which is one of simplest forms of sql server replication. This process involves creating a shared folder across both primary & secondary servers, enabling database mirroring and finally scheduling jobs to backup transaction logs from the source (Primary) machine every hour at specific interval say 5 minutes after an operation occurs on it\. It's important to note that this article only covers basic steps; further configuration may be necessary depending upon your organization's requirements such as data compression, encryption etc.

I hope you found this introductory guide helpful in getting started with SQL Server replication. Remember always test these concepts thoroughly before implementing them in a production environment!

Sql server replication