Ads
05 July 2012
Database Replication - Performance Improvement
You can use SSRM and System Monitor to determine baseline values for the following
key factors in replication performance:
■ Latency The amount of time it takes for a data change to be propagated
between nodes in a replication topology.
■ Throughput The amount of replication activity (measured in commands
delivered over a period of time) a system can sustain over time.
■ Concurrency The number of replication processes that can operate on a system
simultaneously.
■ Duration of synchronization How long it takes a given synchronization to
complete.
■ Resource consumption Hardware and network resources used in replication
processing.
Database Replication - Conflicts
CONFLICT TYPES
While the majority of conflicts you experience will be restricted to a particular update at one replica conflicting with a disparate update at another replica, or possibly with an update at one replica conflicting with a delete at another replica, this is not necessarily the case. While these are the most common conflict types, they are not the only ones.
Each type of conflict discussed in this section can occur during the upload phase or the download phase of merge processing. Upload processing is the first reconciliation of changes performed in a particular merge session, and is the phase during which the Merge Agent replicates changes from the subscriber up to the publisher. Conflicts detected during this processing are referred to as upload conflicts. Download processing involves moving changes from the publisher to the subscriber, and occurs after download processing. Conflicts during this phase of processing are referred to as download conflicts.
Update-Update Conflicts
These types of conflicts are probably the most common. In this case, an update to a row (or column) at one replica conflicts with another update to the same row somewhere else in the topology. The behavior of the default resolver in this case is to send the winning version of the row to the losing replica and log the losing row version in the article's conflict table.
Update-Delete Conflicts
The Merge Agent detects update-delete conflicts when an update of data at one server conflicts with a row deletion at another server. In this case, the Merge Agent updates a row; however, when the Merge Agent searches for that row at the destination, it cannot find that row because the row has been deleted. If the winner is the replica that updated the row, then the DELETE at the losing replica is discarded. Therefore, the Merge Agent sends the newly updated row to the conflict loser. The Merge Agent logs the losing version of the row, in this case, to the MSmerge_delete_conflicts table.
Failed Change Conflicts
These kinds of conflicts are not immediately recognizable as conventional conflicts because there is no actual change to conflict with the source change at the destination. The Merge Agent raises these conflicts when it cannot apply a particular change (INSERT, UPDATE, or DELETE) at the destination replica. This typically occurs because of constraint definitions between the publisher and subscriber, and the use of the NOT FOR REPLICATION (NFR) property on the constraint. Here are some examples of failed change conflict situations:
•A foreign key conflict at the subscriber can occur when the subscriber-side constraint is not marked as NOT FOR REPLICATION. The merge reconciler's behavior in this case is documented in the following Microsoft Knowledge Base article:
307482
(http://support.microsoft.com/kb/307482/EN-US/ )
PRB: Foreign Key Conflicts at Merge Subscriber Result in DELETE During Next Upload
•Conflicts can occur when the publication's join filtering logic does not match its referential integrity definitions, the primary key (PK) or foreign key (FK) constraints. This can occur when the SQL Server relational engine tries to honor a certain constraint (as defined by the PK-FK relationship between the tables), but the Merge Agent is honoring the join filter definition between the articles. The Merge Agent cannot apply the change at the destination replica because of the table-level constraints, which results in a conflict.
•A lookup table that serves as the primary key data for a PK-FK relationship is not published. Generally, from a performance perspective, you ought not publish lookup tables in a merge publication if their data does not frequently change. This prevents the Merge Agent from having to consider those tables in the change-enumeration phase of processing. However, the Merge Agent does detect conflicts if changes to the parent table are not replicated to the subscriber; however, replicated changes to the child table are dependent on those parent-table changes.
•If constraints are not marked as NOT FOR REPLICATION, and are not defined the same at the publisher and the subscriber, the Merge Agent can detect conflicts during the application of changes. Because of the mismatched constraint definitions, the Merge Agent may not be able to apply the destination change. If you have defined constraints at your subscribers, and the Merge Agent is going to evaluate the defined constraints, make sure that they are the same as the constraints defined at the publisher.
•Conflicts because of unique index or unique constraint violations or primary key violations can occur if IDENTITY columns are defined for the article, and automated identity management is not used. This can be a problem if two subscribers were to use the same identity value for a newly inserted row.
•Program logic can also result in failed change conflicts if trigger logic prevents the Merge Agent from inserting a row from in the destination table. For example, a "trgUpdate" trigger is defined at the subscriber (and not marked as NFR) that performs some business logic on the incoming update. If a failure occurs, the trigger's business logic issues a ROLLBACK of the transaction, which results in the Merge Agent detecting a failed change conflict.
For all types of conflicts, the conflict_type is defined as one of ten (10) possible values. These conflict types map directly to the conflict_type in the conflict table:
Description conflict_type
---------------------------- -------------
Update Conflict 1
Column Update Conflict 2
Update Delete Wins Conflict 3
Update Wins Delete Conflict 4
Upload Insert Failed 5
Download Insert Failed 6
Upload Delete Failed 7
Download Delete Failed 8
Upload Update Failed 9
Download Update Failed 10
In the case of UPDATE-UPDATE and UPDATE-DELETE conflicts, the reason_code used in the conflict table is the same as the conflict_type. However, for failed change conflicts, the reason code is the actual server-side error that prevented the Merge Agent from being able to apply the change. For example, if the Merge Agent cannot apply a publisher-initiated INSERT at the subscriber because of a primary key violation, it logs a conflict_type of 6 ("download insert failed") and a reason_code of 2627, which is the SQL Server internal error message for a primary key violation:
Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'.
While the majority of conflicts you experience will be restricted to a particular update at one replica conflicting with a disparate update at another replica, or possibly with an update at one replica conflicting with a delete at another replica, this is not necessarily the case. While these are the most common conflict types, they are not the only ones.
Each type of conflict discussed in this section can occur during the upload phase or the download phase of merge processing. Upload processing is the first reconciliation of changes performed in a particular merge session, and is the phase during which the Merge Agent replicates changes from the subscriber up to the publisher. Conflicts detected during this processing are referred to as upload conflicts. Download processing involves moving changes from the publisher to the subscriber, and occurs after download processing. Conflicts during this phase of processing are referred to as download conflicts.
Update-Update Conflicts
These types of conflicts are probably the most common. In this case, an update to a row (or column) at one replica conflicts with another update to the same row somewhere else in the topology. The behavior of the default resolver in this case is to send the winning version of the row to the losing replica and log the losing row version in the article's conflict table.
Update-Delete Conflicts
The Merge Agent detects update-delete conflicts when an update of data at one server conflicts with a row deletion at another server. In this case, the Merge Agent updates a row; however, when the Merge Agent searches for that row at the destination, it cannot find that row because the row has been deleted. If the winner is the replica that updated the row, then the DELETE at the losing replica is discarded. Therefore, the Merge Agent sends the newly updated row to the conflict loser. The Merge Agent logs the losing version of the row, in this case, to the MSmerge_delete_conflicts table.
Failed Change Conflicts
These kinds of conflicts are not immediately recognizable as conventional conflicts because there is no actual change to conflict with the source change at the destination. The Merge Agent raises these conflicts when it cannot apply a particular change (INSERT, UPDATE, or DELETE) at the destination replica. This typically occurs because of constraint definitions between the publisher and subscriber, and the use of the NOT FOR REPLICATION (NFR) property on the constraint. Here are some examples of failed change conflict situations:
•A foreign key conflict at the subscriber can occur when the subscriber-side constraint is not marked as NOT FOR REPLICATION. The merge reconciler's behavior in this case is documented in the following Microsoft Knowledge Base article:
307482
(http://support.microsoft.com/kb/307482/EN-US/ )
PRB: Foreign Key Conflicts at Merge Subscriber Result in DELETE During Next Upload
•Conflicts can occur when the publication's join filtering logic does not match its referential integrity definitions, the primary key (PK) or foreign key (FK) constraints. This can occur when the SQL Server relational engine tries to honor a certain constraint (as defined by the PK-FK relationship between the tables), but the Merge Agent is honoring the join filter definition between the articles. The Merge Agent cannot apply the change at the destination replica because of the table-level constraints, which results in a conflict.
•A lookup table that serves as the primary key data for a PK-FK relationship is not published. Generally, from a performance perspective, you ought not publish lookup tables in a merge publication if their data does not frequently change. This prevents the Merge Agent from having to consider those tables in the change-enumeration phase of processing. However, the Merge Agent does detect conflicts if changes to the parent table are not replicated to the subscriber; however, replicated changes to the child table are dependent on those parent-table changes.
•If constraints are not marked as NOT FOR REPLICATION, and are not defined the same at the publisher and the subscriber, the Merge Agent can detect conflicts during the application of changes. Because of the mismatched constraint definitions, the Merge Agent may not be able to apply the destination change. If you have defined constraints at your subscribers, and the Merge Agent is going to evaluate the defined constraints, make sure that they are the same as the constraints defined at the publisher.
•Conflicts because of unique index or unique constraint violations or primary key violations can occur if IDENTITY columns are defined for the article, and automated identity management is not used. This can be a problem if two subscribers were to use the same identity value for a newly inserted row.
•Program logic can also result in failed change conflicts if trigger logic prevents the Merge Agent from inserting a row from in the destination table. For example, a "trgUpdate" trigger is defined at the subscriber (and not marked as NFR) that performs some business logic on the incoming update. If a failure occurs, the trigger's business logic issues a ROLLBACK of the transaction, which results in the Merge Agent detecting a failed change conflict.
For all types of conflicts, the conflict_type is defined as one of ten (10) possible values. These conflict types map directly to the conflict_type in the conflict table:
Description conflict_type
---------------------------- -------------
Update Conflict 1
Column Update Conflict 2
Update Delete Wins Conflict 3
Update Wins Delete Conflict 4
Upload Insert Failed 5
Download Insert Failed 6
Upload Delete Failed 7
Download Delete Failed 8
Upload Update Failed 9
Download Update Failed 10
In the case of UPDATE-UPDATE and UPDATE-DELETE conflicts, the reason_code used in the conflict table is the same as the conflict_type. However, for failed change conflicts, the reason code is the actual server-side error that prevented the Merge Agent from being able to apply the change. For example, if the Merge Agent cannot apply a publisher-initiated INSERT at the subscriber because of a primary key violation, it logs a conflict_type of 6 ("download insert failed") and a reason_code of 2627, which is the SQL Server internal error message for a primary key violation:
Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'.
Database Replication - Agents
■ Snapshot Agent (snapshot.exe) This agent prepares the schemas and the initial
copy of the data files. All replication types use the Snapshot Agent as the starting
point for the synchronization process. Generally, the Snapshot Agent is run on a
regular basis to keep the data files updated. These files are required if the replication
synchronization process finds anomalies in the data that cannot be fixed.
The Snapshot Agent runs in the Distributor server.
■ Log Reader Agent (logread.exe) This agent monitors the database’s transaction
log and copies each transaction that affects the publication to the distribution
database, in which the transactions are stored until applied to the Subscribers. It
is important to note that multiple publications of the same database share the
same Log Reader Agent. Log Reader Agents are used only in transactional replication
configurations and run in the Publisher server.
■ Distribution Agent (distrib.exe) This agent performs two tasks: it delivers the initial
snapshot to Subscribers and applies transactions stored in the distribution
database to Subscribers. The Distribution Agent is used in snapshot and transactional
replication. The Distribution Agent runs in the Distributor when you
configure the publication as a push subscription and in the Subscriber when you
configure the publication as a pull subscription.
■ Merge Agent (replmerg.exe) This agent delivers the initial snapshot from the Distributor
to the Subscribers. It also merges data changes that occur in the Publisher
to the Subscribers, and vice versa. When two servers modify the same information
at the same time, a conflict occurs; the Merge Agent reconciles the conflict by using
a set of rules that you define during replication configuration. The Merge Agent
runs in the Distributor when you configure the publication as a push subscription
and in the Subscriber when you configure the publication as a pull subscription.
■ Queue Reader Agent (replmerg.exe) This agent reads messages stored in queues
(SQL Server queues or Microsoft Message Queues) and applies transactions sent
to the queue to the Publisher database. The Queue Reader Agent is used only
when snapshot or transactional replication is set with the option for queued
updating subscriptions.
copy of the data files. All replication types use the Snapshot Agent as the starting
point for the synchronization process. Generally, the Snapshot Agent is run on a
regular basis to keep the data files updated. These files are required if the replication
synchronization process finds anomalies in the data that cannot be fixed.
The Snapshot Agent runs in the Distributor server.
■ Log Reader Agent (logread.exe) This agent monitors the database’s transaction
log and copies each transaction that affects the publication to the distribution
database, in which the transactions are stored until applied to the Subscribers. It
is important to note that multiple publications of the same database share the
same Log Reader Agent. Log Reader Agents are used only in transactional replication
configurations and run in the Publisher server.
■ Distribution Agent (distrib.exe) This agent performs two tasks: it delivers the initial
snapshot to Subscribers and applies transactions stored in the distribution
database to Subscribers. The Distribution Agent is used in snapshot and transactional
replication. The Distribution Agent runs in the Distributor when you
configure the publication as a push subscription and in the Subscriber when you
configure the publication as a pull subscription.
■ Merge Agent (replmerg.exe) This agent delivers the initial snapshot from the Distributor
to the Subscribers. It also merges data changes that occur in the Publisher
to the Subscribers, and vice versa. When two servers modify the same information
at the same time, a conflict occurs; the Merge Agent reconciles the conflict by using
a set of rules that you define during replication configuration. The Merge Agent
runs in the Distributor when you configure the publication as a push subscription
and in the Subscriber when you configure the publication as a pull subscription.
■ Queue Reader Agent (replmerg.exe) This agent reads messages stored in queues
(SQL Server queues or Microsoft Message Queues) and applies transactions sent
to the queue to the Publisher database. The Queue Reader Agent is used only
when snapshot or transactional replication is set with the option for queued
updating subscriptions.
Database Mirroring - Endpoints
Endpoints that support database mirroring are a special implementation of a TCP endpoint and have the following characteristics:
■ Endpoint type of TCP
■ Payload of DATABASE_MIRRORING
■ Only one endpoint supporting database mirroring allowed per SQL Serverinstance
Database mirroring endpoints establish a seventh layer of security through the use of
the ROLE option. You can specify that an endpoint be a PARTNER, WITNESS, or ALL.
An endpoint specified as PARTNER can participate only as the principal or the mirror.
An endpoint specified as WITNESS can participate only as a witness. An endpoint
specified as ALL can function in any role.
Database Mirroring - Operating Modes
Database Mirroring Operating Modes
We can configure database mirroring for three different operating modes:
High-Safety Mode Or High Availability (synchronous + Automatic Fail Over + Witness Server)
And
High Performance (Asynchronous)
The operating mode governs the way transactions are transferred between the principal and the mirror databases as well as
the failover processes that are available in the database mirroring session.
High Availability Operating Mode
Database mirroring’s High Availability operating mode provides durable, synchronous
transfer between the principal and mirror databases in addition to automatic
failure detection and failover.
SQL Server first writes all transactions into memory buffers within the SQL Server memory
space. The system writes out these memory buffers to the transaction log and then
flushes the log to the data files.
When SQL Server writes the transaction to the transaction log, the system triggers database mirroring to begin transferring the transaction log rows to the mirror. The transaction rows continue to flow to the mirror. When the application issues a commit for the transaction, the transaction is first committed on the mirror database.
An acknowledgement of the commit is sent back to the principal, which then allows the commit to be issued. After the commit is issued on the principal, the acknowledgment is sent back to the application, allowing it to continue processing.
This process guarantees that all transactions are committed and hardened to the transaction log on both the principal and mirror databases before the commit is returned to the application.
High Availability operating mode requires a witness server along with the principal and mirror databases for database mirroring to automatically detect a failure at the principal and fail over to the mirror.
A quorum is necessary to prevent the database from being served on more than one instance within the database mirroring session.
If the principal were to fail, and the mirror could not connect to the witness, it would be impossible to reach a quorum, and SQL Server would then not promote the mirror to the principal.
High Performance Operating Mode
Database mirroring’s High Performance operating mode uses a principal and a mirror
database but does not need a witness server. This operating mode provides a warm
standby configuration that does not support automatic failure detection or failover.
High Performance operating mode does not automatically fail over because the application’s
transactions are sent to the mirror asynchronously. Transactions are committed
to the principal database and acknowledged to the application.
A separate process constantly sends those transactions to the mirror, which introduces latency into the
process. This latency prevents a database mirroring session from automatically failing
over because the process cannot guarantee that SQL Server has received all transactions
at the mirror when a failure occurs.
High Protection Operating Mode
Database mirroring’s High Protection operating mode is the same as High Availability
operating mode, except that you do not configure a witness server.
SQL Server transfers transactions synchronously between principal and mirror, but because a two-outof-three quorum cannot be achieved without a witness, failover is manual.
If the principal fails in High Protection operating mode, you must manually promote the mirror to serve the database.
We can configure database mirroring for three different operating modes:
High-Safety Mode Or High Availability (synchronous + Automatic Fail Over + Witness Server)
High-Safety Mode OR High Protection (synchronous + Manual Fail Over)
And
High Performance (Asynchronous)
The operating mode governs the way transactions are transferred between the principal and the mirror databases as well as
the failover processes that are available in the database mirroring session.
High Availability Operating Mode
Database mirroring’s High Availability operating mode provides durable, synchronous
transfer between the principal and mirror databases in addition to automatic
failure detection and failover.
SQL Server first writes all transactions into memory buffers within the SQL Server memory
space. The system writes out these memory buffers to the transaction log and then
flushes the log to the data files.
When SQL Server writes the transaction to the transaction log, the system triggers database mirroring to begin transferring the transaction log rows to the mirror. The transaction rows continue to flow to the mirror. When the application issues a commit for the transaction, the transaction is first committed on the mirror database.
An acknowledgement of the commit is sent back to the principal, which then allows the commit to be issued. After the commit is issued on the principal, the acknowledgment is sent back to the application, allowing it to continue processing.
This process guarantees that all transactions are committed and hardened to the transaction log on both the principal and mirror databases before the commit is returned to the application.
High Availability operating mode requires a witness server along with the principal and mirror databases for database mirroring to automatically detect a failure at the principal and fail over to the mirror.
A quorum is necessary to prevent the database from being served on more than one instance within the database mirroring session.
If the principal were to fail, and the mirror could not connect to the witness, it would be impossible to reach a quorum, and SQL Server would then not promote the mirror to the principal.
High Performance Operating Mode
Database mirroring’s High Performance operating mode uses a principal and a mirror
database but does not need a witness server. This operating mode provides a warm
standby configuration that does not support automatic failure detection or failover.
High Performance operating mode does not automatically fail over because the application’s
transactions are sent to the mirror asynchronously. Transactions are committed
to the principal database and acknowledged to the application.
A separate process constantly sends those transactions to the mirror, which introduces latency into the
process. This latency prevents a database mirroring session from automatically failing
over because the process cannot guarantee that SQL Server has received all transactions
at the mirror when a failure occurs.
High Protection Operating Mode
Database mirroring’s High Protection operating mode is the same as High Availability
operating mode, except that you do not configure a witness server.
SQL Server transfers transactions synchronously between principal and mirror, but because a two-outof-three quorum cannot be achieved without a witness, failover is manual.
If the principal fails in High Protection operating mode, you must manually promote the mirror to serve the database.
25 June 2012
An installation package for the product Microsoft SQL Server Native Client cannot be found. Try the installation again using a valid copy of the installation package 'sqlncli.msi'.
Work Around:-
Go to the Binaries Location,
>> Go to D:\SQL Server\SQL-2005\Servers\Setup
>> Search sqlncli.msi
>> Double click choose Repair
>> Once Done Try to install, It will succed.
*This might work, only when you installed and uninstalled couple of time with different sql servers editions
Solution:-
If it failed to work as mentioned above
Please visit http://support.microsoft.com/kb/929667
http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/16fd90cf-0dbc-4012-9970-6ca29b6f4aef
Go to the Binaries Location,
>> Go to D:\SQL Server\SQL-2005\Servers\Setup
>> Search sqlncli.msi
>> Double click choose Repair
>> Once Done Try to install, It will succed.
*This might work, only when you installed and uninstalled couple of time with different sql servers editions
Solution:-
If it failed to work as mentioned above
Please visit http://support.microsoft.com/kb/929667
http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/16fd90cf-0dbc-4012-9970-6ca29b6f4aef
Subscribe to:
Posts (Atom)
-
ERROR: report server remote procedure call (RPC) end point. Verify that the Report Server Windows service is running, and then retry the o...
-
Error messages: · The process could not execute 'sp_repldone/sp_replcounters' on ' SCKNLSSI '. (Source:...
-
Error Message: Replication-Replication Distribution Subsystem: agent DBA\DBA-EPDW-EPDW-DB6C\DB6C-5 failed. The subscription(S) have been...