SQL Server Performance Forum – Threads Archive
Replication Queries
Hi, We are company that use SQL 2K5 for our application software. Till now our clients were running their reports on the production database which has caused all sorts of issues. We were looking for a solution thru which we can create a replica of the production DB to be used for reporting. Mirroring seems to be a good solution unless we found out that you need the Enterprise Edition to read from the mirrored DB. "Transactional Replication" seems to be a good solution as its available in Standard Edition though which we can create a replica and query off that. Since we are new to this, just have few queries regarding it – a) Most important, is our understanding correct and can the replica DB be read or we need to create a snapshot before we can read from it? b) Typically one server will act as a Publisher(production DB) and Distributor, how much overhead this will put on the server? c) Is distributor another datasbase or just few agents that listen to publisher and pass on the message to one or more subscribers? Thanks in advance! Kind regards,Neeraj
a) Most important, is our understanding correct and can the replica DB be read or we need to create a snapshot before we can read from it?
Yes the subscriber (the replica) can be read and written too. Yes you need to create a snapshot to set up the replication which can either be done via snapshot or you can take a backup of publisher and restore at subscriber and set up replication.
b) Typically one server will act as a Publisher(production DB) and Distributor, how much overhead this will put on the server?
The overhead depends on the number of transactions on your publisher.
c) Is distributor another datasbase or just few agents that listen to publisher and pass on the message to one or more subscribers?
Distributor is just a database. Any transactions on the publisher will be queued up in the Distributor DB. There will be agents (jobs) running that will take care of pushing the transactions from the distributor to the subscriber.
***********************
Dinakar Nethi
SQL Server MVP
***********************
Hi Dinakar, Appreciate your response! In regards to your reponse for question c), kindly clarify- c) Is distributor another datasbase or just few agents that listen to publisher and pass on the message to one or more subscribers? Distributor is just a database. Any transactions on the publisher will be queued up in the Distributor DB. There will be agents (jobs) running that will take care of pushing the transactions from the distributor to the subscriber.
i) If distributor is just a database, will it require the same space as the production DB or its just a blank DB used for queing and pushing transactions? ii) Is it possible to have the distributor and subscriber on the same machine?
Distribution is another database which don’t need big space.. and there are distribution agents too.. I believe you can have distributor and subscriber on the same machine… Note: Table should have PK to participate in replication…
Do you really need upto the minute data for reporting? If not you can use backukp/restore every night… Read the following articles… http://www.awprofessional.com/articles/article.asp?p=599700&rl=1 SQL Server 2005 Books Online
Configuring and Maintaining Replication MohammedU.
Moderator
SQL-Server-Performance.com
Thanks a lot Mohammed! Another question for the curious mind, can we set up replication where the publisher, distributor & subscriber belong to different SQL versions. For e.g. publisher is SQL 2K5, distributor & subscriber are SQL 2K.
>>Another question for the curious mind, can we set up replication where the publisher, distributor & subscriber belong to different SQL versions. For e.g. publisher is SQL 2K5, distributor & subscriber are SQL 2K. Yes. ***********************
Dinakar Nethi
SQL Server MVP
***********************
SQL 2005 can be publisher or subscriber to SQL 2000…but can’t be alternate sync partner Read BOL topic "Configuring and Maintaining Replication"
http://msdn2.microsoft.com/en-us/library/ms151247.aspx HOW TO: Set Up Alternate Synchronization Partner in SQL Server 2000 Merge Replication
http://support.microsoft.com/kb/321176
MohammedU.
Moderator
SQL-Server-Performance.com
Hey Thanks Guys!! Another quick question – What is the differnce between DB Replication and DB Mirroring? If you guys can point me to a link that describes this in detail, would help me a lot. I am interested in knowing how the 2 differ behind the scenes and how one decides to choose one over the other. Thanks again!
Database Replication and Database mirroring are two different features but both can be used for HA…
Read BOL SQL Server 2005 Books Online
SQL Server Replication
http://msdn2.microsoft.com/en-us/library/ms151198.aspx SQL Server 2005 Books Online
Overview of Database Mirroring
http://msdn2.microsoft.com/en-us/library/ms189852.aspx
MohammedU.
Moderator
SQL-Server-Performance.com
]]>