<\/p>\n
1. Database Replication<\/strong><\/h4>\nIn this approach, we copy object from a database to another database. These objects can be tables, view, stored procedures etc. It can be scheduled to run as SQL Server Agent jobs. There are three components which includes<\/p>\n
\n- Publisher<\/strong> – this is the primary server holding the data to be replicated.<\/li>\n
- Distributor<\/strong> – this is an optional database that stores the replicated data from the publisher<\/li>\n
- Subscriber<\/strong> – this is the destination server where data is copied to<\/li>\n<\/ul>\n
You can find replication in SQL Server Management studio a shown in the figure below:<\/p>\n![\"Replication](\"https:\/\/i0.wp.com\/www.kindsonthegenius.com\/mssql\/wp-content\/uploads\/sites\/11\/2020\/10\/Screenshot-2020-10-01-at-11.34.32.png?resize=534%2C347&ssl=1\")
<\/a>Replication in MS SQL Server<\/figcaption><\/figure>\n <\/p>\n
2. Database Mirroring<\/strong><\/h4>\nIn database mirroring, a database is copied from a primary instance called the principal to a secondary instance. Here, there are three components involved:<\/p>\n
\n- Principal<\/strong> – this is the primary instance containing the original data<\/li>\n
- Mirror<\/strong> – this is the instance where data would be mirrored to<\/li>\n
- Witness<\/strong> – optional instance that contains additional information<\/li>\n<\/ul>\n
Database mirroring may be removed in subsequent versions of SQL Server. Would be replaced with Always On Availability.<\/p>\n
However, to configure database mirroring, you can right-click on the database, Tasks > Mirror. This is shown below:<\/p>\n![\"Database](\"https:\/\/i0.wp.com\/www.kindsonthegenius.com\/mssql\/wp-content\/uploads\/sites\/11\/2020\/10\/Screenshot-2020-10-01-at-11.37.00.png?resize=666%2C449&ssl=1\")
<\/a>Database Mirroring in SQL Server Management Studio<\/figcaption><\/figure>\n <\/p>\n
3. Log Shipping<\/strong><\/h4>\nThis is a high availability and disaster recovery(DR) approach for SQL Server database. As the name shows, this method requires backup of database transaction logs. This backup is configured to run at intervals using SQL Server job. The following three components are involved:<\/p>\n
\n- Primary Server<\/strong> – this is source server holding the original database<\/li>\n
- Secondary Server<\/strong> – this is the destination server where the transaction logs are backed up to<\/li>\n
- Monitor Server<\/strong> – an optional server used for\u00a0 monitoring the log shipping status<\/li>\n<\/ul>\n
You can find Log Shipping in SQL server management studio when you right-click on the database > Tasks > Ship Transaction Logs. See figure below:<\/p>\n![\"Transaction](\"https:\/\/i0.wp.com\/www.kindsonthegenius.com\/mssql\/wp-content\/uploads\/sites\/11\/2020\/10\/Screenshot-2020-10-01-at-11.39.25.png?resize=811%2C515&ssl=1\")
<\/a>Transaction Log Shipping in SQL Server<\/figcaption><\/figure>\n <\/p>\n
4. MS SQL Database Clustering<\/strong><\/h4>\nHere we are talking about Failover Clustering. In this approach, we create two or more servers(cluster) with similar access to shared storage disks that stored the database files. Here the servers are described as nodes and communicate via a network.<\/p>\n
\n- Different nodes in a network(Virtual machines are used the the demo)<\/li>\n<\/ul>\n
<\/p>\n
5. Always On Availability<\/strong><\/h4>\nThis is a new feature in SQL Server 2016 and provides a high availability feature for mission critical databases. This relates to the Database Clustering. However, in this demo for this part we would set up a network with three nodes:<\/p>\n
\n- DC Server<\/strong> – this a Windows 2019 Server configured as a domain controller<\/li>\n
- Server 2<\/strong> – this is the primary node of the SQL Always On Availability group<\/li>\n
- Server 3<\/strong> – the secondary node of the SQL Server Always On Availability group<\/li>\n<\/ul>\n
You can find replication in SQL Server Management studio a shown in the figure below:<\/p>\n <\/p>\n In database mirroring, a database is copied from a primary instance called the principal to a secondary instance. Here, there are three components involved:<\/p>\n Database mirroring may be removed in subsequent versions of SQL Server. Would be replaced with Always On Availability.<\/p>\n However, to configure database mirroring, you can right-click on the database, Tasks > Mirror. This is shown below:<\/p>\n <\/p>\n This is a high availability and disaster recovery(DR) approach for SQL Server database. As the name shows, this method requires backup of database transaction logs. This backup is configured to run at intervals using SQL Server job. The following three components are involved:<\/p>\n You can find Log Shipping in SQL server management studio when you right-click on the database > Tasks > Ship Transaction Logs. See figure below:<\/p>\n <\/p>\n Here we are talking about Failover Clustering. In this approach, we create two or more servers(cluster) with similar access to shared storage disks that stored the database files. Here the servers are described as nodes and communicate via a network.<\/p>\n <\/p>\n This is a new feature in SQL Server 2016 and provides a high availability feature for mission critical databases. This relates to the Database Clustering. However, in this demo for this part we would set up a network with three nodes:<\/p>\n<\/a>2. Database Mirroring<\/strong><\/h4>\n
\n
<\/a>3. Log Shipping<\/strong><\/h4>\n
\n
<\/a>4. MS SQL Database Clustering<\/strong><\/h4>\n
\n
5. Always On Availability<\/strong><\/h4>\n
\n
![\"Always](\"https:\/\/i0.wp.com\/www.kindsonthegenius.com\/mssql\/wp-content\/uploads\/sites\/11\/2020\/10\/Screenshot-2020-10-01-at-11.42.48.png?resize=317%2C337&ssl=1\")
<\/a>