How to install microsoft security essentials in windows server 2012,2016

What is MS SQL Server Replication?

SQL Server Replication is a technology that allows you to copy and distribute data and database objects from one database to another, and then keep the two databases in sync with each other. Replication can be used to synchronize databases on different servers, or to keep a backup server up-to-date with the primary server.

Replication is a process of copying data from one SQL Server instance to another. In SQL Server, replication can be configured in many different ways to suit the needs of your environment. For example, you can replicate all of the databases on a server, or just some of them. And you can replicate the data from one server to multiple servers, or from multiple servers to one central server.

There are three main components to replication: publishers, subscribers, and distribution agents. The publisher is the server that contains the data you want to replicate. The subscriber is the server that will receive the replicated data. And the distribution agent is responsible for copying the data from the publisher to the subscriber (or subscribers).

When configuring replication, you first need to decide what type of replication you want to use. There are two types of replication: snapshot replication and transactional replication. Snapshot replication copies the entire database (or selected tables) at regular intervals and applies any changes that have been made since the last snapshot was taken. Transactional replication copies only changes that have been made to the database, as they happen.

The Different Types of Replication

There are three different types of replication in MS SQL Server: transactional, merge, and snapshot. Transactional replication is the most common type and is used to replicate data from one database to another on a regular basis. Merge replication is used to replicate data between two databases that are not always connected. Snapshot replication is used to create a point-in-time copy of a database.

The Benefits of Replication

Replication can be used to improve performance or increase redundancy. In some cases, both can be achieved simultaneously. Read on to learn more about the benefits of replication in MS SQL Server.

Performance:

When data is spread across multiple servers, each server can handle a portion of the overall load. This can help to improve performance by distributing the workload across multiple machines.

Redundancy:

If one server goes down, the other servers can pick up the slack and keep the system running. This can help to prevent downtime in the event of a hardware failure or other unforeseen incident.

Simplicity:

Replication is a relatively simple process to set up and maintain. compared to other high availability solutions such as failover clustering.

How to Set Up Replication

Setting up replication in MS SQL Server can be done in a few simple steps:

1. Set the same password for the mssql user on both servers.
2. Administrators (local administrators, not domain administrators)
3. The Agent service should be configured to start automatically.

Below is a screenshot of the views in SQL Server Management Studio for the main database server (TEST-SERVER) and the second server (TEST-IT PC-1).

Configuring Distribution

Multiple publishers and subscribers can use Distribution. In this example, Distribution is configured on the main server on which the source database is stored. Right-click Replication on the main server (TEST-SERVER) and select Configure Distribution from the context menu.

In this example, select the current database instance on the main server (TEST-SERVER) as the Distributor. Click Next for each step in the Wizard to proceed.

It is recommended that you keep the default path for the snapshot directory. A snapshot is required for initializing replication. Make sure your snapshot directory has enough free space on the disk. At least as much free space as a replicated database must exist.

Enter the name of the distribution database. Leave the default name (distribution) and folders for the distribution database and log files.

On the primary MS SQL Server instance that hosts the source database that will be replicated, select the checkbox next to the distribution database name to define MS SQL Server replication publishers that can access the Distributor. The distribution database is distribution in this example, which is the instance TEST-SERVER.

Click on the Configure distribution checkbox to configure distribution during the final step of the wizard. In this example, we will not generate a script file

Create the Distributor by completing the Wizard. Check the Distribution configuration summary and click Finish.

Once the Distributor has been created and configured successfully, a success status should appear.

2. Configuring the Publisher

Having configured Distribution, you can now configure Publisher. The Publisher must be configured on the main server (TEST-SERVER) where the master database is stored. Select Replication, right-click Local Publications, and select New Publication from the context menu.

New Publication Wizard opens.

Choose the database you want to replicate from the Publication Database list

This step allows you to select the replication type for a database. Let’s choose transactional publication, which is a popular replication type

There are no filters added in this example (this is the default configuration of filters).

Let’s configure the Snapshot Agent to run immediately. Select the checkbox Create a snapshot immediately and keep the snapshot available to initialize subscriptions.

Click the Security Settings button to select the account under which the Agent will run. Select the Snapshot Agent security settings.

Using the Snapshot Agent Security window, enter the credentials of the mssql Windows user you created earlier. Select connect to the publisher by impersonating the process account. Click OK to save the settings and go back to the wizard.

In the Snapshot Agent and Log Reader Agent sections, you can see the defined user.

During the final step of the wizard, click the upper checkbox to create the publication.

Click Finish to create a new publication after you have completed the Wizard

You can monitor the progress of creating a new publication in the Creating Publication window. If everything has been done correctly, you should see the success status.

You can see the publication in Object Explorer by going to Replication > Local Publications.

Configuring the Subscriber

In MS SQL Server, replication can either be pull or push. If push replication is configured, the Subscriber must be configured to run agents on the main database server (TEST-ITPC-1 in this case).

The Subscriber must be configured to run agents on the second machine (TEST-ITPC-1), the machine where the database replica will be created, if pull replication is configured

Right-click Local Subscriptions in Object Explorer and choose New Subscription from the context menu.

Open the New Subscription Wizard

The New Subscription Wizard opens.

In our example, the Publisher is TEST-SERVER, and the publication name (that was created earlier) is database name. Click Next for each step in the wizard to continue.

How to install Microsoft security essentials in windows server

Location of the Distribution Agent. You need to choose either a push subscription or a pull subscription to replicate in this step. In our example, we want all agents to run on the source server side, so we selected the push subscription option. MS SQL Server replication can be managed centrally with it.

By default, the server you run the wizard on (TEST-SERVER in this case) is displayed as the Subscriber and no subscription database is defined.

We will add a new subscriber and select the subscription database on the second database server (TEST-ITPC-1). Click Add Subscriber and select Add SQL Server Subscriber from the context menu.

Connect to the second MSSQL Server instance using the credentials entered in the popup window.

Select the second server on which your database replica will be stored

Click the button with three dots (…) and select the user and other security options for the Distribution Agent.

Open the Distribution Agent Security window and select the mssql user account to run the Distribution Agent on the SERVER host. To save settings, enter the password for the mssql Windows user. Select the Distributor by impersonating the process account option and the Subscriber by impersonating the process account option.

Your subscription properties have now been set.

Select the Agent located on the Distributor to run continuously for the current subscriber.

When you tick the Initialize checkbox, select immediately from the drop-down menu. If necessary, you can also select Memory Optimized.

At the end of the wizard, select the upper checkbox to create the subscription(s).

Complete the Wizard. You can check your subscriptions settings and hit Finish to create the subscription.

If the Success status appears, the subscription has been successfully created.

Finalizing replication configuration

You can check the MS SQL Server replication status once you have configured the Distributor, Publisher, and Subscriber.

Launch the replication monitor on the first server to check the status of the MS SQL Server replication.

There are no errors displayed, and MS SQL Server replication should work.