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.

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)

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.

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

Upgrading to SQL Server 2012

http://blogs.extremeexperts.com/2012/05/17/upgrading-to-sql-server-2012/
By Vinod Kumar

SSRS Scripting Tool

 Download Here : 


http://weblogs.asp.net/akjoshi/archive/2009/01/29/using-rs-scripter-to-create-deployment-script-for-reporting-services.aspx

Data Source Updation  can be done using batch file provided by this tool.

Package 'Microsoft SQL Management Studio Package' failed to load

"Reinstalling .Net Framework X.X solved the problem" found at:

"http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/75e2f725-338f-41a7-b66b-5fb4be2d339c"

Posted by Duncan Smart on 04/01/2012 at 14:43

I found that renaming the "HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server" registry key solved the issue for me. More info: http://blog.dotsmart.net/2012/01/04/solution-for-package-microsoft-sql-management-studio-package-failed-to-load/