High Availability (HA) is the solution\process\technology to make the application\database available 24x7 under either planned or un-planned outages.
Mainly, there are five options in MS SQL Server to achieve\setup high availability solution for the databases.
The source data will be copied to destination through replication agents (jobs). Object level technology.
The source data will be copied to destination through Transaction Log backup jobs. Database level technology.
The primary data will be copied to secondary through network transaction basis with the help of mirroring endpoint and port number. Database level technology.
The data will be stored in shared location which is used by both primary and secondary servers based on availability of the server. Instance level technology. Windows Clustering setup is required with shared storage.
The primary data will be copied to secondary through network transaction basis. Group of database level technology. Windows Clustering setup is required without shared storage.
Following are the steps to configure HA technology (Mirroring and Log shipping) except Clustering, AlwaysON Availability groups and Replication.
Step 1 − Take one full and one T-log backup of source database.
To configure mirroring\log shipping for the database 'TestDB' in 'TESTINSTANCE' as primary and 'DEVINSTANCE' as secondary SQL Servers, write the following query to take full and T-log backups on Source (TESTINSTANCE) server.
Connect to 'TESTINSTANCE' SQL Server and open new query and write the following code and execute as shown in the following screenshot.
Backup database TestDB to disk = 'D:\testdb_full.bak' GO Backup log TestDB to disk = 'D:\testdb_log.trn'
Step 2 − Copy the backup files to destination server.
In this case, we have only one physical server and two SQL Servers Instances installed, hence there is no need to copy, but if two SQL Server instances are in different physical server, we need to copy the following two files to any location of the secondary server where 'DEVINSTANCE' instance is installed.
Step 3 − Restore the database with backup files in destination server with 'norecovery' option.
Connect to 'DEVINSTANCE' SQL Server and open New Query. Write the following code to restore the database with the name 'TestDB' which is the same name of primary database ('TestDB') for database mirroring. However, we can provide different name for log shipping configuration. In this case, let’s use 'TestDB' database name. Use 'norecovery' option for two (full and t-log backup files) restores.
Restore database TestDB from disk = 'D:\TestDB_full.bak' with move 'TestDB' to 'D:\DATA\TestDB_DR.mdf', move 'TestDB_log' to 'D:\DATA\TestDB_log_DR.ldf', norecovery GO Restore database TestDB from disk = 'D:\TestDB_log.trn' with norecovery
Refresh the databases folder in 'DEVINSTANCE' server to see restored database 'TestDB' with restoring status as shown in the following snapshot.
Step 4 − Configure the HA (Log shipping, Mirroring) as per your requirement as shown in the following snapshot.
Right-click on 'TestDB' database of 'TESTINSTANCE' SQL Server which is primary and click Properties. The following screen will appear.
Step 5 − Select the option called either 'Mirroring' or 'Transaction Log Shipping' which are in red color box as shown in the above screen as per your requirement and follow the wizard steps guided by system itself to complete configuration.