Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the differences between SQL database and oracle database mirroring". The explanation in this article is simple and clear, easy to learn and understand. Please follow the ideas of Xiaobian and go deep into it slowly to study and learn "what are the differences between SQL database and oracle database mirroring" together!

First, a mirror database in a Microsoft SQL database is similar to a standby database in an Oracle database. I'm just saying similar, exactly, we need to consider the differences between different databases in our own systems. MSSQL operates as an instance. An instance contains several databases. You first log in to an instance and choose which database works for that instance. In Oracle databases, simple schema (ignoring RAC) has only one database associated with one instance. Therefore, it can be said that in Oracle database, the standby database is completely a snapshot of the primary database. In MSSQL, a mirrored database is simply a backup of the selected database, but does not include external data items such as agents, logins, and tasks (these or more database items need to be created or replicated separately on the database mirror).

In terms of the number of servers, Oracle's primary database and standby database configuration requires a minimum of 2. In MSSQL, the minimum data size is 2 or 3, depending on the high availability, high security, high performance approach you choose.

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

High protection mode: this option lets you synchronize things on both servers, but error recovery is manual. Since automatic error recovery is not part of this option, witness servers are not used either.

High performance mode: This option does not care if writes on both servers are synchronized and therefore improves performance. When using this option, you can only assume that everything on the mirror server is done successfully. This option allows only manual error recovery and therefore does not use the witness server.

To ensure automatic recovery, you need a third server, called an eyewitness (the other two are the primary database and the mirror database), which you can think of as a member of the cluster. It enables the ability to vote 2-to-1 when one of my components is unreachable and therefore requires error recovery. Witness servers are only needed if you want automatic error recovery.

In a transaction of Oracle data, the log buffer is flushed or written to the redo log before the waste data is written to the data file (ignoring the write-ahead case). This behavior of flushing or writing to the redo log is necessary when events such as instance failures occur (using rollback and rollback recovery procedures). MSSQL also recognizes the importance of writing log buffers to disk. This is called hardening. The transaction log buffer information is first written to disk or hardened, and then log blocks are sent to the mirror database. The mirror database receives the log block, stores it in a buffer, and then hardens the log block in turn.

How does an MSSQL database maintain consistency between the primary and mirror databases when data changes occur?

Oracle users are familiar with SCN, while MSSQL users use mirroring_failover_lsn (roughly a log sequence number). MSSQL differs from Oracle in that MSSQL separates transactions (two transactions on two machines) rather than one distributed transaction (requiring a remote wait for commit before committing itself).

Another similar, but slightly distorted reflection is redo logging and transaction logging. In Oracle, the completed redo log is sent to a remote server, where it is applied to the backup data. In MSSQL, transaction logs are not transmitted, but as I mentioned above, log buffer data is sent to the network. This leads to another mirror image: backup and restore mode.

In Oracle, these operations are default when you are in archive mode or non-archive mode. If archived redo logs are transferred or committed to a remote server, the primary database is clearly in archive mode, and that's how the files are generated. Operating in this mode allows for a small amount of data loss because recovery can be performed at any point before a failure (whatever the failure) occurs. In MSSQL it is similar, but there are three states to choose from.

SQL Server Books Online, like many other online resources, describes the differences between the three recovery models when using MSSQL. A quick comparison is: MSSQL full mode corresponds to archive mode in Oracle; simple mode corresponds to non-archive mode;bulk mode corresponds to using direct path insertion, adding hints, or similar to nlogging mode operation.

From the description of the three modes above (which are easily convertible and do not require a shutdown or restart) and the discussion of log buffers and archived redo logs, it is easy to conclude that database mirroring in MSSQL requires setting the recovery mode of the data to full model. Simple model may work, but in this mode only a small amount of data is maintained in the transaction log. In backup, if the log is truncated, the whole mirroring process is broken, because when you send transactions to the mirror database, if the log is truncated, the process cannot be completed.

What about database corruption?

This is the main purpose of mirroring (or backup): when the primary database is down or down, we want the system to go back to its pre-mirror or pre-backup state. How can this be achieved? We can do it automatically or manually. To do this, you need some setup already done. In MSSQL, automatic fail-back is required in HA mode, transaction security is full, data transfer is synchronous, and there are witness server cases. Running in this mode also requires an enterprise version of the database system. High security and high performance can also be achieved in the case of the standard version.

There are other versions of MSSQL available, but these are not as "clean" as Oracle's reflection: Developer, Workgroup, and SQL Express. For example, witness servers can be any version, but if you want to take a snapshot of a mirror server, you'll need an enterprise or development version.

During the process of setting up partners (usually composed of primary and mirror databases), their recovery state comes into play. Mirrors are set up on the remote/mirror server (using the Configure Database Mirrors Security Wizard is the easiest method) by using the same name, and the mirror database is set to NORECOVERY, which is usually the recovering state. In MSSQL, the recovery database is not available, so without the above settings, it cannot be used as a read-only database by other users.

To avoid this pitfall, you can take a snapshot of the mirror and make the "image" visible to the user. As I mentioned above, this requires that your database version be Enterprise (or Development). This means that the user needs to have knowledge of the snapshot database, know how to get into storing it, and tell the application which database to use. Conventionally, with the. NET environment that profiles use, you can set up a primary database and a secondary database for failover. If you've configured a backup database in Oracle, you'll find this similar.

Thank you for reading, the above is "SQL database and oracle database mirror difference what" content, after the study of this article, I believe we have a deeper understanding of SQL database and oracle database mirror difference what this problem, the specific use of the situation also needs to be verified. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.