High Availability - Availability of MS SQL Server

High Availability (HA) is a solution / process / technology to ensure applications / databases are accessible 24/7 in any condition, whether intended or unexpected.

Basically, there are 5 options to set up database availability in MS SQL Server.

  1. Replication
  2. Log Shipping
  3. Mirroring
  4. Clustering
  5. AlwaysON Availability Groups

Replication

The original data will be copied to the destination via the replication task (agent / job), using technology at the object level. Some terms need to know:

The publisher (Publisher) is the source server.

  1. Distributor (Distributor) is optional, storing the copied data to the subscriber (Subscriber).
  2. Subscriber is the destination server.

Log Shipping

The source data is copied to the destination via the Transaction Log backup task, using the database-level technology. Some terms:

  1. The primary server (Primary Server) is the source server.
  2. Secondary server (Secondary Server) is the destination server.
  3. The monitoring server is optional, monitored by Log Shipping status.

Mirroring

Primary data is copied to secondary through network transactions with the help of projection and port number connections, using database-level technology. Some terms:

  1. The original server (Principal Server) is the source server.
  2. The Mirror Server is the destination server.
  3. Witness Server is optional, used for automatic fault tolerance solutions.

Clustering

Data is stored in a common location, used by both primary and secondary servers, using technology at the instance level. You need to set up Windows Clustering on this shared storage. Some terms:

  1. Active Node is where SQL Services runs.
  2. Passive Node (Passive Node) is where SQL Services does not run.

AlwaysON Availability Groups

Primary data will be transferred to secondary through transactions, using technology at the database group level. Windows Clustering settings do not need common storage. Some terms:

  1. Primary Replica is the source server.
  2. Secondary Replica is the destination server.

Steps to configure High Availability for Mirroring and Log Shipping

Below are the steps to configure HA (Mirroring and Log Shipping) except Clustering, AlwaysON Availability Groups and Replication.

Step 1 - Get a full copy and a T-log copy of the original database.

For example:

To configure Mirroring / Log Shipping for the TestDB database on TESTINSTANCE as the primary SQL Server server and DEVINSTANCE as the secondary server, use the following query to get the full backup and T-log on the source server (TESTINTANCE).

Connect to SQL Server TESTINSTANCE and open a new query, write the code below and execute as shown in the figure.

 Backup database TestDB to disk = 'D:testdb_full.bak' 
GO
Backup log TestDB to disk = 'D:testdb_log.trn'

High Availability - Availability of MS SQL Server Picture 1
Query retrieves backup

Step 2 - Copy the backup file to the destination server.

In this case we only have one physical server and two versions of SQL Server Instance, so we don't have to copy. But if two copies of SQL Server Instance are located in two different physical servers, then copy the following two files to the location on the secondary server, where DEVINSTANCE is installed .

High Availability - Availability of MS SQL Server Picture 2
Copy the backup file to the destination server

Step 3 - Restore the database with a backup file on the destination server by selecting norecovery.

For example.

Connect to SQL Server DEVINSTANCE and open a new query New Query. Write the following code to restore the database with the name TestDB, identical to the name of the original database. You can use a different name for Log Shipping configuration. Use the norecovery option to restore.

 Restore database TestDB from disk = 'D:TestDB_full.bak' 
with move 'TestDB' to 'D:DATATestDB_DR.mdf',
move 'TestDB_log' to 'D:DATATestDB_log_DR.ldf',
norecovery
GO
Restore database TestDB from disk = 'D:TestDB_log.trn' with norecovery

High Availability - Availability of MS SQL Server Picture 3
Restore databases with file saving

Refresh the database folder on the DEVINSTANCE server to see that the database has been restored TestDB with recovery status as shown below.

High Availability - Availability of MS SQL Server Picture 4
Database recovery status

Step 4 - Configure HA (Log Shipping / Mirroring) as shown below.

For example:

Right-click on SQL Server TestDB database TESTINTANCE (root server) and click Properties. The screen below will appear.

High Availability - Availability of MS SQL Server Picture 5
Configuring Log Shipping / Mirroring

Step 5 - Choose Mirroring or Transaction Log Shipping according to your needs and follow the steps provided by the system to complete the configuration.

Previous article: How to start and stop services in MS SQL Server

Next article: Report creation service in MS SQL Server

4 ★ | 1 Vote

May be interested

  • Overview of MongoDBOverview of MongoDB
    mongodb is a cross-platform database, operating on the concepts of collection and document, it provides high performance, high availability and easy scalability.
  • Network basics: Part 3 - DNS ServerNetwork basics: Part 3 - DNS Server
    a dns server is a server that contains a database of public ip addresses and hostnames associated with them. in most cases, the dns server is used to resolve or translate those common names into ip addresses as required.
  • HOW TO INSTALL ISA SERVER ENTERPRISE 2000HOW TO INSTALL ISA SERVER ENTERPRISE 2000
    main functions of the product: - protection of the network against internet attacks - allowing clients inside the internal network to access controlled services outside the internet. installation instructions on windows 2000/2003 server: - isa server 2000 installation server must be a clean server, which means that you should not deploy other network services that are not recommended. this will help set up a system with high safety. services should not be installed together with isa server 2000:
  • How to set up your own Git server on LinuxHow to set up your own Git server on Linux
    while you can count on globally renowned git hosting services like github, in some cases it is better to host a personal git server for enhanced privacy, customizability, and security.
  • Learn about different types of RAM serversLearn about different types of RAM servers
    understanding the knowledge of computer memory types will be an indispensable part in choosing to buy as well as ensuring your system can operate long term and achieve high performance.
  • Use IIS to set up FTP Server on WindowsUse IIS to set up FTP Server on Windows
    set up an ftp server (file transfer protocol server) to share and convert large files with unlimited traffic.
  • Microsoft adds a bunch of new Intel CPUs to the Windows 10 LTSC support list until 2027Microsoft adds a bunch of new Intel CPUs to the Windows 10 LTSC support list until 2027
    this week, microsoft announced the general availability of windows server 2025 along with system center 2025. at the same time, the company also announced the list of processors that have been confirmed to support windows server 2025.
  • How to change DNS server on the most popular routersHow to change DNS server on the most popular routers
    changing the dns server settings on your router is not difficult, but every manufacturer uses their own custom interface, which means the process can be very different depending on which router you are owned.
  • Exchange 2007 Availability ServicesExchange 2007 Availability Services
    the availability service in exchange 2007 is an important service in practice, it is responsible for collecting updates about the busy / free status of users, and allowing them to run over outlook. 2007 and exchange 2007. in the future
  • How to Check Uber Availability in a Specific RegionHow to Check Uber Availability in a Specific Region
    uber allows you to order rides from partner drivers right on your phone, tablet or computer. to check if service is available in your current area (or where you're going), use the city checker tool on the uber website. you can also download the uber app and set up an account. the application will automatically notify you whether the service is available or not. even if the service is not available in your current location, the app will still start working when you move to a place where uber has service.