WHAT IS A DATABASE?

A database is an organized collection of data, generally stored and accessed electronically from a computer system. Where databases are more complex they are often developed using formal design and modeling techniques.

A database is an organized collection of structured information, or data, typically stored electronically in a computer system. A database is usually controlled by a database management system (DBMS). … The data can then be easily accessed, managed, modified, updated, controlled, and organized.

Types of databases

There are many different types of databases. The best database for a specific organization depends on how the organization intends to use the data.

• Relational databases became dominant in the 1980s. Items in a relational database are organized as a set of tables with columns and rows. Relational database technology provides the most efficient and flexible way to access structured information.
• Information in an object-oriented database is represented in the form of objects, as in object-oriented programming.
• A distributed database consists of two or more files located in different sites. The database may be stored on multiple computers, located in the same physical location, or scattered over different networks.
• A central repository for data, a data warehouse is a type of database specifically designed for fast query and analysis.
• A NoSQL, or nonrelational database, allows unstructured and semistructured data to be stored and manipulated (in contrast to a relational database, which defines how all data inserted into the database must be composed). NoSQL databases grew popular as web applications became more common and more complex.
• A graph database stores data in terms of entities and the relationships between entities.
• OLTP databases. An OLTP database is a speedy, analytic database designed for large numbers of transactions performed by multiple users.

These are only a few of the several dozen types of databases in use today. Other, less common databases are tailored to very specific scientific, financial, or other functions. In addition to the different database types, changes in technology development approaches and dramatic advances such as the cloud and automation are propelling databases in entirely new directions. Some of the latest databases include

• An open source database system is one whose source code is open source; such databases could be SQL or NoSQL databases.
• A cloud database is a collection of data, either structured or unstructured, that resides on a private, public, or hybrid cloud computing platform. There are two types of cloud database models: traditional and database as a service (DBaaS). With DBaaS, administrative tasks and maintenance are performed by a service provider.
• Multimodel databases combine different types of database models into a single, integrated back end. This means they can accommodate various data types.
• Designed for storing, retrieving, and managing document-oriented information, document databases are a modern way to store data in JSON format rather than rows and columns.
• The newest and most groundbreaking type of database, self-driving databases (also known as autonomous databases) are cloud-based and use machine learning to automate database tuning, security, backups, updates, and other routine management tasks traditionally performed by database administrators.

WHAT IS DATABASE MIRRORING?

In SQL Server Log Shipping and Mirroring can work together to provide solutions for high availability and disaster recovery. We can convert an existing log shipping configuration to a database mirroring configuration and also easily switch which pair of instances is using database mirroring and which instances are configured with log shipping. 

SQL Server Database Mirroring

 To move the database transactions from one SQL Server database (Principal database) to another SQL Server database (Mirror database) on a different instance the database mirroring is used. If the principal server fails, the mirror server automatically becomes the new principal server and recovers the principal database using a witness server under high-availability mode. It is a mixture of replication and log shipping. Mirroring works only with the full recovery model. 

  Logical Design Diagram of Database Mirroring

Difference between the Database Mirroring and Log Shipping

MIRRORING	LOG SHIPPING
1. It is limited to only two Servers	1.In this we can log ship to multiple Servers
2. Mirroring with a Witness Server allows for High Availability and automatic fail over.	2. Log shipping is only as current as how often the job runs. If we ship logs every 15 minutes, the secondary server could be as far as 15 minutes. Making it more of a Warm Standby.
3. We can configure our DSN string to have both mirrored servers in it so that when they switch we notice nothing.	3. We can leave the database in read only mode while it is being updated. Good for reporting servers.
4. While mirrored, our Mirrored Database cannot be accessed. It is in Synchronizing/Restoring mode.	4. Good for disaster recovery
5. Mirroring with SQL Server 2005 standard edition is not good for load balancing

Roles of the Server in Database Mirroring 

• Principal server
  
  The principal server hosts the active copy of the database (referred to as the principal database) and services client requests. The principal server forwards all transactions to the mirror server before it applies them in the principal database.
   
• Mirror server
  
  The mirror server hosts a copy of the principal database (referred to as the mirror database) and applies the transactions forwarded by the principal database to keep the mirror database synchronized with the principal database.
   
• Witness server
  
  The witness server is an optional component of a database mirroring solution. When present, a witness server monitors the principal and mirror servers to ensure continued connectivity and participation in the mirror session (referred to as quorum). If either server loses quorum, the witness server assigns the principal server role, causing automatic failover from the principal server to the mirror server if necessary. A witness server is required for automatic failover; however, one witness server can support several mirror sessions because it is not an intensive job.

Database Mirroring Modes

 Database Mirroring has three different Modes 

High-Availability Mode In this Mode we need all the three servers, since the transaction safety level is set to FULL that results in the “Database transfer mechanism between the principal and mirror server is synchronous” which means that the principal server waits for an acknowledgement from the mirror server that the transaction log record has been recorded on the mirror server. Then, the client application gets confirmation that the transaction is committed. But if the principal server becomes unavailable then the witness server and the mirror server will form a quorum and perform automatic failover. High-Protection Mode This mode is nearly the same as High-Availability mode, but the difference is that there is a need for two servers only (Principal server and Mirror server). The transaction safety level in this mode is also set to FULL that results in the same High- Availability Mode “Database transfer mechanism between the principal and mirror server is synchronous”. Another difference is if the principal server becomes unavailable in this mode then we need to manually perform the failover because there is no witness server in this mode. Because the transaction safety level is set to FULL, we do not lose any committed transactions in the event of a failover. High-Performance Mode The same as High-Protection Mode, there is also a need of two servers (principal and mirror server). In this Mode the transaction safety level is set to OFF that results in “Data transfer mechanism between the principal and mirror servers is asynchronous”, which means that the principal server does not wait for an acknowledgement from the mirror server that all transaction log records have been recorded on the mirror server and the client application gets confirmation that a transaction is committed as soon as the principal server has written the transaction to the log. If the Principal server in this mode becomes unavailable then we must manually perform the failover since there is no witness server in this mode. Because the transaction safety level is set to OFF, we might lose some transactions in the event of a failover.  

Operating Mode	Transaction safety	Transfer mechanism	Quorum required	Witness server	Failover Type
High Availability	FULL	Synchronous	Y	Y	Automatic or Manual
High Protection	FULL	Synchronous	Y	Y	Manual only
High Performance	OFF	Asynchronous	N	N/A	Forced only

Loss of Servers 

1. PRINCIPAL Server Lost
   
   The following scenario considers what happens when the principal server is lost in a High Availability scenario:
   
   
    
2. MIRROR Server Lost
   
   If the mirror server is lost first, the principal server is considered exposed because it cannot send data to the mirror.
   
   
    
3. WITNESS Server Lost
   
   When the witness server fails, mirroring continues but no automatic failover is possible. Loss of just one more server will mean there is no quorum, and the principal database will no longer be able to serve the database.
   
                                                    

Types and Operating Modes in Mirroring

• Synchronous mirroring : In all SQL Server databases, data changes are recorded in the transaction log before any changes to actual data pages are made. …
• Asynchronous mirroring : …
• Transaction Safety : …
• Quorum : …
• Operating Modes : …
• High Availability Mode : …
• High Protection Mode : …
• High Performance Mode 

Procedure to Configure Database Mirroring in SQL Server

The procedure of configuring SQL Server Database Mirroring is mainly divided into two sections:

I. Preparation of server instances to take part in Database Mirroring in SQL Server

For every database mirroring session, following requirements are need to be fulfilled:

1. Separate server instances, residing on separate host systems, should host the primary server, the mirror server, and the witness server.
2. All the server instances need a database mirroring endpoint. If you need to do so, make sure that the endpoint is accessible by other server instances as well.
3. Two types of transport security exist for database mirroring:

• Windows Authentication
• Certificate-based Authentication

The network access requirement depends on the form of authentication that is being used:

• If Windows Authentication Is Used
• Is Certificates Are Used

Make sure that the logon for the all the database exists on the mirror server.

II. Establishing Database Mirroring in SQL Server

Pre-requisites:

1. Restore all the below-mentioned backups for creating a mirror database by RESTORE WITH NORECOVERY command on all the restore operations:

• Restore the recent full database backup of principal server. Make sure that the principal server used full recovery model at the time of the backup. Moreover, the name of the mirror database must be same as the primary server.
• In case any differential backup of the data has been taken after the restoration of full backup, restore the recent differential backup.
• Do the restoration of all the log backups done since the differential or full backup of the database.

Mirroring can be setup by either using Windows Authentication i.e. Transact-SQL or by Database Mirroring Wizard i.e. SQL Server Management Studio. Here, we are going to use SQL Server Management Studio.

Procedure for Configuring SQL Server Database Mirroring Via SQL Server Management Studio

Follow the below-mentioned steps for establishing database mirroring in SQL Server:

1. Open SQL Server Management Studio.
2. Connect to the Primary server.
3. Click on View option and select Object Explorer option from the drop-down menu.
4. Click on the Server Name and expand the server tree.
5. Click on Databases and expand it.
6. Select the database that has to be mirrored.
7. Right click on the database and select Tasks option.
8. Click on Mirror option. Database Properties dialog box with Mirroring page will open.
9. Click on Configure Security option to start the configuration.
10. The Configure Security option will automatically create the database mirroring endpoint on all the server instance and will enter the server network address.
11. Make sure that the below-mentioned conditions exist.

• You are connected to principal server instance.
• Security is configured in a correct manner.
• The TCP address of the primary and mirror servers are specified.
• If the operating mode is High Safety with automatic failover, then make sure the TCP address of the witness server is also specified.

Click on Start Monitoring to start the process.In case you want to change the operating mode, you can do so and save the changes by Ok.

Conclusion

With the help of the above-mentioned procedure, you can easily configure SQL Server Database Mirroring.