SQL Server Performance

Replication without PK

Discussion in 'SQL Server 2005 Replication' started by ajitgadge, Nov 1, 2007.

  1. ajitgadge New Member

    I have to create DB Server for Reporting purpose ( Update statement also included on Reporting server). So i thought the best way to do it by Transactional Replication with minimum latency.But the problem is here , in our DB Schema i have total 430 Tables and only 260 Tables have PK and remain 170 Tables don't have PK. I know it is bad design :) and i am not able to do replication without PK. So on above scenerio following is my Question.
    1: What is best way to create Reporting DB Server in this case? Is Trans Replication is best option?
    2: If i want to do Trans Replication then i know that i have to Identify or add PK on those tables which don't have PK. So is it possaibel for me to add Identity Column or GUID in those table? Can Trans Repliaction will accept?
    Please help me in this case as it is very urgent matter. Project is always in tight schedule :)
    Thanks in Advance.
  2. Adriaan New Member

    By all means add an Identity column as the 'fake' PK. Avoid GUIDs.
  3. ajitgadge New Member

    Yes thats true but is insert into * statement will affect this identity at Publisher as well as Subsscriber end?
  4. Adriaan New Member

    Good point, not sure what you can do. Deny SELECT permission on the IDENTITY column? Insert against view instead of against table?
  5. ajitgadge New Member

    Hmm..So do you mean i have to create View of the tables and then Insert into select * from view ?
  6. Adriaan New Member

    Uh, sorry - wrong answer. Make the identity column "Not for replication". And look up the details in Books Online - there must be some explanation there.
  7. ndinakar Member

    If you are in 2005, when you ALTER the table, the schema change is automatically taken care of. Give it a try and let us know. If it doesnt work, then you have to ALTER the table on the subscriber end and make the IDENTITY column NOT FOR REPLICATION.
  8. ajitgadge New Member

    Thanks i will try this.

Share This Page