SQL Server Performance

Replication question

Discussion in 'All SQL Server 2011 Questions' started by Inkster Jim, Sep 7, 2011.

  1. Inkster Jim New Member

    Hi folks, first post on this forum.

    I am trying to do a replication between a test server to a staging table and eventually to a development server. What I want to know is can I do a replition on a table by table basis rather than a whole instance? Also, can I do it using jobs, or can you do that with the wizards in the replication procedure of SSMS?

    Thanks in advance!
  2. preethi Member

    Welcome to forums.
    replication is not at the database level. It is at table level.
    Jude of object explorer click "replication" and right click. first you need to configure a server to perform replication and then create the publications for all the objects you need BOL gives more information on how to setup replication.
  3. satya Moderator

    Welcome to the forums.
    Are you sure this is for next version of SQL Server?

    Further there are different types of replication such as MERGE, Transactional and Snapshot which one suits your environment?
    Also you can go through the documentation from SQL Server Books Online to see the better understanding of the technology.
  4. Inkster Jim New Member

    Thanks guys,

    Preehi: Forgive my ignorance, but what is BOL?

    Satya: I beleive the type we are doing is Transactional, does that make a difference as to how to go about setting up the jobs?
  5. Inkster Jim New Member

    Preehi,
    Never mind. After taking another look it hit me BOL - Books On Line
  6. preethi Member

    Oops. (Sorry for confusing with BOL)
    You need to answer for some more questions to setup replication
    Is it uni directional (The subscriber will not update the rows)?
    Do you want the data to be pushed from the publisher's side (Publisher: who currently has the table; Subscriber: To where you areplanning to replicate the data) do subscriber needs to pull the data as and when needed?
    Do you want to Publish all columns? All Rows?

    If you use replication wizard (Using object explorer) the jobs will be setup for you. It is much easier. (Many, who has experience with SQL Server for years, still prefer to use this method rather than writing a script for it.) After creating them, you can generate the script for the publication/subscription and learn on whats hapening behind the screen.
    I still dont know the version you are working with, by this book (Pro SQL Server Repliation) too can be useful.

    Hope this helps.
  7. satya Moderator

    Then you have plenty options to read on from BOL .. it will give you first hand information on understanding the options you may have with replication... read on.
  8. Inkster Jim New Member

    Preethi,

    We will be using SS 2008 R2. Subscriberers will not be able to update the rows. Still in the brain storming phase.

    Thanks
  9. preethi Member

    It appears like transaction replication is the best choice for you. Post here if you need more help.
  10. satya Moderator

    ... little addition too from BOL:

    Transactional replication is typically used in server-to-server environments and is appropriate in each of the following cases:
    • You want incremental changes to be propagated to Subscribers as they occur.
    • The application requires low latency between the time changes are made at the Publisher and the changes arrive at the Subscriber.
    • The application requires access to intermediate data states. For example, if a row changes five times, transactional replication allows an application to respond to each change (such as firing a trigger), not simply the net data change to the row.
    • The Publisher has a very high volume of insert, update, and delete activity.
    • The Publisher or Subscriber is a non-SQL Server database, such as Oracle.
    By default, Subscribers to transactional publication should be treated as read-only, because changes are not propagated back to the Publisher. However, transactional replication does offer options that allow updates at the Subscriber. For more information, see the section "Updating Data at Subscribers" in this topic.
  11. ghemant Moderator

Share This Page