locked
Need to implement Transactional Replication in SQL Server 2008 on same domain RRS feed

  • Question

  • Hi,

    I have requirement to design replication architecture to capture OLTP application change and  to keep store in different subscriber.

    Example: In Human Resource schema we have complete organization data. In need to create transaction replication which will be  subscribe for different department wise database.

    (Human Resource :Publisher)----------(Only Finance department employee)-----------> DeptFinance (subscriber 1)  

    (Human Resource :Publisher)----------(Only Operation department employee)-----------> DeptOperation (subscriber 2)  

    (Human Resource :Publisher)-----------(Only IT department employee)----------> DeptIT (subscriber 3)  

    Please assist me to implement this requirement.

    Regards,


    Kapil Kaushish

    Friday, December 14, 2012 8:48 AM

Answers

  • If I understood you in the right way.. I think, this is what you are intending to do :

    you have one source database(contains information about all the employees, departments and job_history). you call this database as Human Resource.

    you want to replicate the department wise data to their own database( i.e operation department datat to Operations database).

    If this is your requirement, you can do it as the following...

    During the replication set up, you will get an option to "filter rows", there you can

    Select * from Department where departmentid = 1 (departmentId = 1 is operations)

    select * from employee where departmentid = 1

    select * from job_history where departmentid =1..this will be one publisher on your source DB(human resource).

    In the end, this will be replicating operations data from your source(human resource) to subscriber(operations database)

    similarly you will have configure more publishers for other departments......

    The following link has it in detail about replication set up. http://www.sql-server-performance.com/2010/transactional-replication-2008-r2/



    Please mark as 'Answer', if the solution solves your problem.

    • Proposed as answer by PrinceLucifer Monday, December 17, 2012 7:16 AM
    • Marked as answer by Kapil Dev K Monday, December 17, 2012 12:52 PM
    Monday, December 17, 2012 7:03 AM

All replies

  • Hello Kapil,

    A publisher can have multiple subscribers. So, you can use Transaction replication and choose ONLY the tables you want to replicate and also, can filter out the rows.  I assume, all  the required tables have primary key on them.

    What version of sql are using?

    The following link has it in detail. http://www.sql-server-performance.com/2010/transactional-replication-2008-r2/.

    Also, One other way to do it is you can send all the data to one subscriber and assign permissions to people to be able access their only data.

    i.e Finance people can access only their data similarly operation dept can access only their data. This is will lot easier if you AD or groups is set up that way.

    Also,if this is  a replication requirement it should be moved to replication section.

    HTH.


    Please mark as 'Answer', if the solution solves your problem.


    • Edited by Stan210 Saturday, December 15, 2012 1:03 PM update.
    Saturday, December 15, 2012 12:59 PM
  • Hello Stan,

    I have been using Edition 'Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) '

    I have table : EMPLOYEE, DEPARTMENTS and JOB_HISTORY; where relation is

    DEPARTMENT(DEPARTMENT_ID(PK)) ----< EMPLOYEE(DEPARTMENT_ID(FK))

    DEPARTMENT(DEPARTMENT_ID(PK)) ----< JOB_HISTORY(DEPARTMENT_ID(FK))

    I have different database(Department wise) on different server or different database on  same server. I want to replicate these department wise database with my source database. 

    Regards, 


    Kapil Kaushish

    Monday, December 17, 2012 5:44 AM
  • If I understood you in the right way.. I think, this is what you are intending to do :

    you have one source database(contains information about all the employees, departments and job_history). you call this database as Human Resource.

    you want to replicate the department wise data to their own database( i.e operation department datat to Operations database).

    If this is your requirement, you can do it as the following...

    During the replication set up, you will get an option to "filter rows", there you can

    Select * from Department where departmentid = 1 (departmentId = 1 is operations)

    select * from employee where departmentid = 1

    select * from job_history where departmentid =1..this will be one publisher on your source DB(human resource).

    In the end, this will be replicating operations data from your source(human resource) to subscriber(operations database)

    similarly you will have configure more publishers for other departments......

    The following link has it in detail about replication set up. http://www.sql-server-performance.com/2010/transactional-replication-2008-r2/



    Please mark as 'Answer', if the solution solves your problem.

    • Proposed as answer by PrinceLucifer Monday, December 17, 2012 7:16 AM
    • Marked as answer by Kapil Dev K Monday, December 17, 2012 12:52 PM
    Monday, December 17, 2012 7:03 AM