What is the use of replication in SQLSERVER2005

Question

Hi can any body tell me what is use of replication in sqlserver2005. backup and replicaton looks same?what is diference b/w them

Answer 1

Replication is used for a different purpose, for example to make reports without putting that load on the 'real' database.

Answer 2

Replication increases system availability. If one set of database is down, you can serve out of replica.

Backup saves you from catastrophic errors such as human error that dropped the production database. Note that in this case, replication won't save you as it will dutifully replicate drop command.

Answer 3

SQL Server replication is the process of distributing data from a source database to one or more destination databases throughout the enterprise.

Replication is a great solution for maintaining a reporting server.

Clients at the site to which the data is replicated experience improved performance because those clients can access data locally rather than connecting to a remote database server over a network.

Clients at all sites experience improved availability of replicated data. If the local copy of the replicated data is unavailable, clients can still access the remote copy of the data.

• Replication: Lots of data, fast and most recent.

• Backup/Restore: Some data, perhaps a bit slower, and a specific point in time.

Answer 4

Backups are exactly that: backups. They enable you to recover the data if something bad happens.

Replication is another beast entirely. It basically distributes the data across multiple nodes so that each node has a complete, (close to) up-to-date copy of the data.

There are a number of reasons why you would use replication including, but not limited to:

• High availability so that, if one node goes down, other nodes can still service requests.
• Geographical distribution, meaning your data can be placed close to those that need it. Clients in Belarus don't need to go all the way to Montana to get the data if you maintain a local replica in Belarus (or somewhere close) - this is for performance. You may have 10,000 clients in Belarus - it's quicker to send one copy over than have all 10,000 request data [although this depends on how often they request data].
• Prioritization. If your reporting users (bank management) have a lower service level agreement than your customer-facing staff (bank tellers) [and they should], you can put all the management onto a replica so as not to slow down the primary copy.

Answer 5

Replication can be used to address a number of different scenarios as detailed below.

Just to be clear however, Replication is not the same as Database Backup

---

Scenarios:

Server to server: Replicating Data in a Server to Server Environment

• Improving Scalability and Availability
• Data Warehousing and Reporting
• Integrating Data from Multiple Sites(Server)
• Integrating Heterogeneous
• Data Offloading Batch Processing

Server to client: Replicating Data Between a Server and Clients

• Exchanging Data with Mobile Users
• Consumer Point of Sale (POS)
• Applications Integrating Data from
• Multiple Sites (Client)

For a full overview of Microsoft SQL Server Replication see the following Microsoft reference.

http://msdn.microsoft.com/en-us/library/ms151198(SQL.90).aspx

Choose the track that is most appropriate to you (i.e. Developer / Architect) and all shall be revealed :-)