none
transactional replication, but no PK RRS feed

  • Question

  • What is the next best option if I can't do transactional replication because the table I need to replicate does not have a PK? The reason I wanted to do transactional replication is because I only wanted incremental changes from the publisher table. (So, doing a snapshot replication would defeat that purpose)

    • Moved by Naomi N Sunday, February 24, 2013 8:11 PM Better answer can be here
    Friday, February 22, 2013 8:56 PM

Answers

  • this can be better answered in replication forum.

    depending on what you want to do with the secondary server... you can look at database mirroring and log shipping(stand by/no recovery mode) options.
    As far as i know, in replication, you have only snapshot replication option if you do not have primary key.

    Hope it Helps!!

    Friday, February 22, 2013 9:01 PM
  • only option you have to use snapshot replciation. If you want to use replication on tables without primary key.

    Other option you have log shipping or mirrioing but this a DB replica option not for particular artical or object. Reanalysis you requirment & plan for avilable  3 (snapshot replciation or log shipping or mirrioing ) options.


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Monday, February 25, 2013 8:41 PM
  • The table must have a key on it. After all how are you doing to update or delete a row. You have to be able to identify this row somehow to do the update or delete on it. Whatever criteria this is - make that your key.

    Now this will work in the majority of the cases, the only cases where it will not work is when one of the columns must support nulls, or the table is just a temporary storage table - a log or audit table. In this case you should add an identity column to this table and then put a pk on it.

    In some cases you will break the existing code base as adding this additional identity column will add an unexpected new column which the app might not expect.

    In this case you can insulate your table by a view.

    For example if your table looked like this

    create table TableName(Char1 char(20), char2 char(30), intcol int)

    create a new table like this

    create table tableNameNew(PK int identity primary key, Char1 char(20), char2 char(30), intcol int)

    move the data to the new table, and then drop the existing table.

    Then create a view which looks like this

    Create view TableName as select char1, char2, intcol from TableNameNew.

    Replicate both the view and the table.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Monday, February 25, 2013 9:56 PM
    Moderator

All replies

  • this can be better answered in replication forum.

    depending on what you want to do with the secondary server... you can look at database mirroring and log shipping(stand by/no recovery mode) options.
    As far as i know, in replication, you have only snapshot replication option if you do not have primary key.

    Hope it Helps!!

    Friday, February 22, 2013 9:01 PM
  • Transactional replication does require a primary key,  there are a couple of options here.  

    here is a good BOL link on choosing the appropriate type of replication:

     http://msdn.microsoft.com/en-us/library/ms152565(v=sql.105).aspx

    another option that this article does not go over is snapshot replication on a mirrored database.  You could asynchronously mirror to another instance then snapshot that data on a fairly regular basis.  

    In either case it depends on what the purpose of the replicated database is for.  


    Friday, February 22, 2013 9:48 PM
  • only option you have to use snapshot replciation. If you want to use replication on tables without primary key.

    Other option you have log shipping or mirrioing but this a DB replica option not for particular artical or object. Reanalysis you requirment & plan for avilable  3 (snapshot replciation or log shipping or mirrioing ) options.


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Monday, February 25, 2013 8:41 PM
  • The table must have a key on it. After all how are you doing to update or delete a row. You have to be able to identify this row somehow to do the update or delete on it. Whatever criteria this is - make that your key.

    Now this will work in the majority of the cases, the only cases where it will not work is when one of the columns must support nulls, or the table is just a temporary storage table - a log or audit table. In this case you should add an identity column to this table and then put a pk on it.

    In some cases you will break the existing code base as adding this additional identity column will add an unexpected new column which the app might not expect.

    In this case you can insulate your table by a view.

    For example if your table looked like this

    create table TableName(Char1 char(20), char2 char(30), intcol int)

    create a new table like this

    create table tableNameNew(PK int identity primary key, Char1 char(20), char2 char(30), intcol int)

    move the data to the new table, and then drop the existing table.

    Then create a view which looks like this

    Create view TableName as select char1, char2, intcol from TableNameNew.

    Replicate both the view and the table.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Monday, February 25, 2013 9:56 PM
    Moderator
  • Hi All,

    In the event that the databases are all legacy and works very fine(if it ain't broke, don't fix it.) for the purposes they were designed for, how else would one carry out some sort of replication(close to real time data) of the publisher db?

    Would the manual Failover type using mirroring or log shipping be worth considering on two sql server 2008r2?

    Thanks

     

    Friday, April 12, 2013 9:23 PM
  • It depends on what you want to do with your secondary database.

    Hope it Helps!!

    Friday, April 12, 2013 10:14 PM