Post reply

Implement Data integrity

  • October 31, 2008 at 8:45 am

    #154868

    :(I have a production application which holds 100+ tables which does not have primary keys at all and running in sql server 2005. I inherited this application and was requested to provide adhoc dba support. When a request came to provide a report only environment, I tried to implement transactional replication and to my chagrin, I found all the tables were without primary keys.

    The solution i provided to them is to do an auto backup of production db, kill all sessions on the reporting db and restore prod db onto reporting db and scheduled the entire stuff to run automatically.

    Now, I want to fix the problem on identifying the appropriate primary keys for all these tables.

    I was thinking of reverse engineering the data model using erwin and work with various people to identify pk's / uk's and implement them.

    What is the best way to proceed?

    Have any of you come across similar experience? Pls share.

    Thanks

    Murali

  • October 31, 2008 at 10:13 am

    #892449

    To be honest you're already on the right track. You're ultimately going to have to talk to the person/people that know the most about the system in order to establish what primary keys will be most suitable.

    You can, go through each table and identify potentially suitable primary keys. Search for columns that contain unique data and they will be your "candidate keys". But you'll still need to talk to people to get definitive answers.

    Good luck

    Karl
    source control for SQL Server

  • October 31, 2008 at 10:21 am

    #892455

    If yours is an enterprise edition then you can make use of Database Snapshot feature for reporting purpose. Your way of reverse engg. looks fine. If you don't find any candidate for primary key then using adding an identity column will help too.

    Let experts comment more on this.

    MJ

  • October 31, 2008 at 8:41 pm

    #892661

    Since you are interested in replicating all the 100 tables in a database, why don't you consider Log shipping as your Solution. With Log Shipping you can also keep you secondary read only database which can be used for Reporting services.

    Now in SQL Server 2005 I think Log Shipping provides an option to restore logs on secondary database when users are connected to database ... ( not sure please check on this ).

    But to me sounds like Log Shipping is good solution. This also depends on the traffic on the database... You can keep the 5 minutes interval for Log Shipping.

    To implement Log Shipping, its not mandatory to have primary keys on tables.

    I would wait for Master's to speak on this... like some one said in previous post.

    Hope this helps.

    Imran Mohammed.

  • October 31, 2008 at 9:02 pm

    #892664

    Muralidharan Venkatraman (10/31/2008)

    :(I have a production application which holds 100+ tables which does not have primary keys at all and running in sql server 2005. I inherited this application and was requested to provide adhoc dba support. When a request came to provide a report only environment, I tried to implement transactional replication and to my chagrin, I found all the tables were without primary keys.

    The solution i provided to them is to do an auto backup of production db, kill all sessions on the reporting db and restore prod db onto reporting db and scheduled the entire stuff to run automatically.

    Now, I want to fix the problem on identifying the appropriate primary keys for all these tables.

    I was thinking of reverse engineering the data model using erwin and work with various people to identify pk's / uk's and implement them.

    What is the best way to proceed?

    Have any of you come across similar experience? Pls share.

    Thanks

    Murali

    That's probably faster than snapshot replication. However, if you have a SAN, some SAN's have a "clone" capability. On Tera-Byte sized databases, it'll take a day or two to sync up the first time... after that, it'll create a "fresh copy" of everything in about 11 minutes total. Now, understand that this is NOT replication... it's a "REPLACEMENT". The resulting databases will NOT be read only, but anything you write to them will be lost the next time you run the clone copy. It does, however, make a pretty good way to make a copy of the production server for development and/or reporting purposes.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply