Embedded Files
Post date: Sep 12, 2010 3:09:29 AM
I have been asked a couple of times now what is the best solution for my environments in terms of availability, scalability. And as my experience grows I tend to answer with the famous "it depends". Why, well because regarding your needs (reporting server , load balance load across multiple server(s), hot fail-over server , data-warehouse server etc...) a solution may fit best for a customer and another one will suit best another customer.
As in the real life, describing the purpose of your secondary server will drive 80% of the requirements to answer correctly the question. The last 20% will go with related business requirements and possibilities in terms of infrastructure and $. I have listed below and describe the architecture for the replication, database mirroring and the log-shipping solutions.
a. Database Replication
a. Database Replication
Transactional / Merge / Snapshot / Peer to peer
Publishing Data and Database Objects
When creating a publication, you choose the tables and other database objects that you want to publish. You can publish the following database objects using replication. The database objects within a replication environment are called "articles".
A replication high availability architecture is defined by 3 majors elements. The publisher that manage the publication of the data to be replicated, the distributor that manage the distribution of the transaction and finally the subscriber which will subscribe to a set of publisher data objects :
- the publisher manage the objects that that will be available for the subscribers to load (called articles)
- the distributor implement all the agents jobs that will deal with the transfer , historic and the maintenance of the transaction log distribution between the publisher and their subscribers.
- the subscriber persist the publisher data to an offset location. The susbriber will have the possibility to choose from a list of the publisher publication object list described above.
b. Database Mirroring
b. Database Mirroring
Architecture without witness server
Architecture with witness server
How database mirroring works
The principal and mirror servers communicate and cooperate as partners in a database mirroring session. The two partners perform complementary roles in the session: the principal role and the mirror role.
Database mirroring involves redoing every operation that occurs on the principal database onto the mirror database as quickly as possible.
Redoing is accomplished by sending a stream of active transaction log records to the mirror server. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record.
Note : beginning in SQL Server 2008, the principal server compresses the stream of transaction log records before sending it to the mirror server. This log compression occurs in all mirroring sessions.
Operating Modes
A database mirroring session runs with either synchronous (high-safety mode) or asynchronous (high-performance mode) operation. Under asynchronous operation, the transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance. Under synchronous operation, a transaction is committed on both partners, but at the cost of increased transaction latency.
High-safety mode with automatic failover requires a third server instance, known as a witness. Unlike the two partners, the witness supports automatic failover by verifying whether the principal server is up and functioning.
Note : the high-performance mode is not available in SQL Server Standard versions.
Transaction Safety and Operating Modes
Whether an operating mode is asynchronous or synchronous depends on the transaction safety setting. If you exclusively use SQL Server Management Studio to configure database mirroring, transaction safety settings are configured automatically when you select the operation mode.
If you use Transact-SQL to configure database mirroring, you must understand how to set transaction safety. Transaction safety is controlled by the SAFETY property of the ALTER DATABASE statement. On a database that is being mirrored, SAFETY is either FULL or OFF.
If the SAFETY option is set to FULL, database mirroring operation is synchronous, after the initial synchronizing phase. If a witness is set in high-safety mode, the session supports automatic failover.
If the SAFETY option is set to OFF, database mirroring operation is asynchronous. The session runs in high-performance mode, and the WITNESS option should also be OFF.
c. Log Shipping
c. Log Shipping
Log Shipping Operations
Log shipping consists of three operations:
Back up the transaction log at the primary server instance.
Copy the transaction log file to the secondary server instance.
Restore the log backup on the secondary server instance.
The log can be shipped to multiple secondary server instances. In such cases, operations 2 and 3 are duplicated for each secondary server instance.
A log shipping configuration does not automatically fail over from the primary server to the secondary server. If the primary database becomes unavailable, any of the secondary databases can be brought online manually.
Primary Server and Database
The primary server in a log shipping configuration is the instance of the SQL Server Database Engine that is your production server.
The primary database is the database on the primary server that you want to back up to another server. The primary database must use the full or bulk-logged recovery model; switching the database to simple recovery will cause log shipping to stop functioning.
Secondary Server and Databases
The secondary server in a log shipping configuration is the server where you want to keep a warm standby copy of your primary database.
A secondary server can contain backup copies of databases from several different primary servers. The secondary database must be initialized by restoring a full backup of the primary database and the tail log backup.
Page updated
Google Sites
Report abuse