Introduction to Mirroring of SQL Server 2005 databases

Since SQL Server 2000, you've been able to create an alternate server, transfer logs, and back up and store by using replication-but now Microsoft has introduced a built-in tool that can automate error recovery. Database mirroring is a new feature of SQL Server 2005 that allows you to mirror the contents of a database in one SQL Server to another SQL Server. It also allows you to make error recovery by mirroring the database in the event of an error.

Edgewood Solutions's Greg Robidoux answers some of our common questions about database mirroring in SQL Server 2005, and explains why you should now start testing the built-in functionality to see if it will ultimately benefit your environment.

What is database mirroring?

Robidoux: Database Mirroring moves database transactions from a SQL Server database to another SQL Server database in a different SQL Server environment. A mirrored copy is an alternate copy and cannot be accessed directly; It is only used in case of error recovery.

Because it is a new feature introduced in SQL Server 2005, this feature can only be used in this version of the software. In a way, it is a hybrid of replication and log transmissions: All of your things are moving (log transmissions) to a mirrored copy of your database at the level of things (copy), while reducing the problems you may face when implementing log transfers or replications.

Copying includes a lot of moving parts, and if your database plan changes frequently, it's also difficult to keep your copy running smoothly. For log transmissions, there are also a number of processes that need to be implemented to ensure synchronization, such as creating backups, copying backups, and backing up storage. If a process crashes, the entire process crashes.

How does database mirroring work?

Robidoux: The minimum requirements for database mirroring include two different SQL Server running environments. The primary server is called "Basic," and the second server is called "mirrored." The basic database is the database you actually use, and the mirror database is an alternate copy of your database. When transactions are written to your base server, they are also routed to and written to your mirror database.

In addition to basic and mirroring, you can also introduce another optional component called "Witness". The witness database is the third running instance of SQL Server 2005, which is used for internal communication between basic and mirroring when it comes to determining when error recovery occurs. Use this option only if you want to implement automatic error recovery. 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.

What is the way to achieve it?

Robidoux: Database mirroring provides three ways to implement it. Choose what you want to do with the error recovery process.

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: This option does not care whether the writes on the two 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.

Always remember that automatically restoring your data to a second copy is the real benefit of database mirroring. As a result, most implementations may be using a high accessibility approach. Other options still provide a built-in error recovery process, but only if you have a great interest in automating error recovery in the event of an error.

How many versions can a database mirror work on?

Robidoux: Database mirroring can only be found in Standard Edition, Development edition, and Enterprise Edition of SQL Server 2005. These versions are required for both the base server and the mirror server's SQL Server running instance. The witness server can run on any version of SQL Server. In addition, there are some other features that are specific to the development and Enterprise editions of SQL Server, but the Standard Edition has the most basic functionality.

Summarize

Today, SQL Server 2005 's database mirroring has been turned off, but all the functionality still exists. Database mirroring can be opened by using a search flag-but Microsoft does not support this feature now, so don't run it in your product environment. Database mirroring will be fully implemented and supported by the end of this year.

To open this feature and start your test, you can look at the previous expert knowledge solution. Microsoft is looking for additional feedback, so you can see if it can be a candidate for your SQL Server environment.