What is the difference between an SQL database and an Oracle database image? Other database

Oracle databases are very different from MSSQL data operations, but there are analogies in mirroring operations. This article is about how MSSQL database mirroring is implemented in Oracle databases, and what differences exist between them.

First, the mirror database in the Microsoft SQL database is similar to an alternate database in an Oracle database. What I'm saying is just like, to be exact, we need to consider the differences in our systems for different databases. MSSQL as an instance to operate, an instance contains several databases, you first have to log in to an instance, and then choose which database acts on the instance. In an Oracle database, the simple schema (ignoring RAC) has only one database associated with an instance. Therefore, it can be said that, in an Oracle database, the backup database (standby databases) is a complete snapshot of the primary database. In MSSQL, the mirror database is only a backup of the selected database, but does not include proxies, logins, tasks (these or more database projects need to be created or replicated separately on database mirroring).

On the number of servers, Oracle's primary and standby database configurations require a minimum of 2 units. In MSSQL, the minimum data is 2 or 3, depending on the high availability, high security, and high-performance approach you choose.

High availability: This operation mode option allows you to synchronize transaction writes on both servers and support automatic error recovery. To use this option, you must also use a witness server.

High protection: This option allows you to synchronize things to write on both servers, but error recovery is manual. Because automatic error recovery is not part of this option, the witness server is not used.

High-performance approach: This option does not care whether the writes on both servers are synchronized and therefore improve performance. When you use this option, you can only assume that everything on the mirror server is completed successfully. This option only allows manual error recovery and therefore does not use the witness server.

To ensure automatic failback, you need a third server, which you can call a witness (the other two are the primary and mirror databases), and you can use this witness as a member of the cluster. It achieves 2:1 of the ability to vote when one of my components is unreachable and therefore requires error recovery. The witness server needs to be used only if you want to achieve automatic error recovery.

In a transaction of Oracle data, the log buffer is refreshed or written to the redo log before the scrap data is written to the data file (ignoring the write-ahead condition). This refresh or write to the redo log is necessary, such as when an instance fails (using the roll forward and rollback recovery procedures). MSSQL also acknowledges the importance of writing a log buffer to disk. But this is called hardening (hardening). The transaction log buffer information is first written to disk or hardened, and the log block is sent to the mirror database. After the mirror database receives the log block, it is saved to a buffer, which then hardens the log block in turn.

How does the MSSQL database maintain the consistency of the primary and mirror databases when the data is changed?

Oracle users are very familiar with the SCN, while MSSQL users use the MIRRORING_FAILOVER_LSN mechanism (roughly speaking, a log sequence number). Unlike Oracle, MSSQL separates transactions (two transactions on two machines), rather than a distributed transaction (which requires remote wait submissions before submitting itself).

Another similarity, but a slightly distorted reflection is the redo log and the transaction log. In Oracle, the completed redo log is sent to a remote server and the completed redo log is applied to the backup data. In MSSQL, the transaction log is not transmitted, but as I mentioned above, the log buffer data is sent to the network. This leads to another mirror reflection: Backup and Recovery mode.

In Oracle, these operations are fixed when you are in archive mode or not in archive mode. If the archived redo logs are transmitted or submitted to a remote server, then the primary database is obviously in archive mode, and those files are generated. Running in this mode allows for a small amount of data loss, because recovery can be performed at any point before a failure, regardless of the type of failure. This is similar in MSSQL, but there are three states to choose from.
SQL Server Books Online, like many other online resources, tells the different points of the 3 recovery models when using MSSQL. A quick comparison is that the MSSQL full mode corresponds to the archive mode in Oracle, the simple mode corresponds to the bulk mode, the pattern is inserted with a direct path, a hint is added, or similar to the nologging mode operation.

Based on the description of the above three modes (which are easily convertible, do not require shutdown or restart), and the discussion of log buffers and archived redo logs, it is easy to conclude that mirroring a database in MSSQL requires that the reply mode of the data be set to full mode (fully model). Simple mode may work, too. However, this mode maintains a small portion of the data in the transaction log, and in a backup, if the log is truncated, the entire mirroring process is broken, because if the log is truncated when you send the transaction to the mirror database, the process cannot be completed.

What happens when the database is compromised?

This is the primary purpose of mirroring (or backup): When the primary database is disconnected or when there is a failure, we want the system to go back to the mirror or before the backup. How can this be achieved? We can implement it automatically or manually. To implement these, you need some settings that have already been completed. In MSSQL, automatic failure recovery, back to the original state required in HA mode, transaction security is full, data transmission is synchronized, there are eyewitnesses to the case of the server. Running in this mode also requires the use of an enterprise version of the database system. High security and performance can be achieved in the case of a Standard edition.
There are other versions of MSSQL, but these are not as "clean" as Oracle's Reflection, which includes: Developer, Workgroup, and SQL Express. For example, a witness server can be any version, but if you want to take a snapshot of the mirror server, you need the enterprise or the development version.

In the process of setting up partners (partner, usually with primary and mirror databases), their recovery status begins to work. By using the same name, mirroring is established on a remote/mirror server (the simplest way to use the Metabase Mirroring Security Wizard), and the mirrored database is set to NORECOVERY, which is typically a recovery (recovering) state. In MSSQL, the recovery database is not available, so there is no such setting and cannot be used by other users as a read-only database.

To avoid this flaw, you can take a snapshot of the image, making the image visible to the user. As I mentioned above, this requires that your database version be the Enterprise (or development) edition. This means that the user needs to have knowledge of the snapshot database, know how to get into the store, and tell the application which database to use. Traditionally, the configuration file uses the. NET environment, you can build a primary database and a secondary database with a failed rollback. If you have configured backup databases in Oracle, you will find this very similar.

Conclusion

This article includes how to better understand how to perform mirroring or copying on another mainstream RDBMS, in Oracle's way. Try to learn and explain how your RDBMS works, and getting your attention from another pattern will help you figure out how your current database system works. For example, I found the relationship between the Oracle archiving model and the MSSQL three recovery models very useful. Some of the terminology used in MSSQL (partners, master databases, sightings, mirrors) helps you to compose and identify Oracle's operations for performing database mirroring.
To better evaluate how database mirroring works and how it is implemented, you can run two separate instances of MSSQL, which are not related to the operating system XP or 2003. Follow the steps in the MSDN Books Online. Download or select the AdventureWorks database (similar to Oracle's HR/SH database, etc.). None of these are preinstalled, mirroring it to the host server. Present in front of you is not only another data of the functional characteristics, you will also see the operation of MSSQL, get their own correct evaluation (I usually use the Oracle database, testing is MSSQL, conversely, you usually use the MSSQL, Now, with Oracle to test, there will be new discoveries as well.