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.
- Replication
- Log Shipping
- Mirroring
- Clustering
- 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.
- Distributor (Distributor) is optional, storing the copied data to the subscriber (Subscriber).
- 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:
- The primary server (Primary Server) is the source server.
- Secondary server (Secondary Server) is the destination server.
- 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:
- The original server (Principal Server) is the source server.
- The Mirror Server is the destination server.
- 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:
- Active Node is where SQL Services runs.
- 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:
- Primary Replica is the source server.
- 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'
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 .
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
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.
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.
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
You should read it
- SQL Server setup is always available
- How to configure VMware High Availability (VMHA)
- Standby Continuous Replication Management (SCR) - Part 1
- Analysis of disaster recovery perspective and high availability of Exchange Server
- Checking Exchange Server 2007 with MOM 2005 (Part 3)
- The difference between web server and app server
- Network basics: Part 3 - DNS Server
- New points in SQL Server 2017
May be interested
- Overview of MongoDBmongodb 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 Servera 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 2000main 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 Linuxwhile 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 serversunderstanding 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 Windowsset 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 2027this 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 routerschanging 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 Servicesthe 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 Regionuber 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.