SQL Server Performance

Replication Queries

Discussion in 'SQL Server 2005 Replication' started by NeerajD78, Feb 9, 2007.

  1. NeerajD78 New Member

    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
  2. ndinakar Member

    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
    ***********************
  3. NeerajD78 New Member

    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?




  4. MohammedU New Member

    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
  5. NeerajD78 New Member

    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.
  6. ndinakar Member

    >>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
    ***********************
  7. MohammedU New Member

  8. NeerajD78 New Member

    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!
  9. MohammedU New Member

Share This Page