locked
Database copy RRS feed

  • Question

  • I have production database(db1) on server1 and copy of it(db2) for reporting purposes on server2 (and also for some other critical production applications!).
    db2 is refreshed every hour by log shipping and is Standby/Read-only database.

    This is not an acceptable option any more, since all users are dropped whenever the log is restored.
    The size of db is 200 GB so only changes should be transfered from db1 to db2. 

    I thought about replication. But there is one problem. The replication adds column to each replicated table.
    Since application on db1 uses asterisk in many queries( SELECT t1.*, t2.* FROM...) the additional column would break the application from working.
    And changing the application to use column name instead of asterisk is not an option(many reasons).

    Since db2 should have at least 1 hour old data because of production applications, I thought to have 2 databases on server2.
    First db for reporting purposes would be restored once per day, and second for production applications would be restored every hour as it is now.
    This way I would solve the problem that reporting users have(since many reports last long, log shipping often executes in the middle of reports and breaks them).
    But still there is problem for production applications.

    Do you have any idea what would be the best solution here with my limitations?

    Second question.
    Can I add some index to db2, without changing the db1 and break the log shipping?
    (change db2 to not read only, add index and change back to read only mode)

    Thanks.

    Friday, August 24, 2012 10:22 AM

Answers

  • I go with option 5 that is transaction replication. Replication is quite reliable in SQL Server.


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Marked as answer by simonxy Friday, August 24, 2012 2:12 PM
    Friday, August 24, 2012 2:11 PM
  • SSIS - In case I don't have timestamp on table I have to compare all data from source to target to find the changed one.

    You could set up Change Tracking on the tables, to fetch the changed rows.

    But it seems to me that roll-your-own replication takes more effort than just plain old transactional replication, so try that first.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by simonxy Monday, August 27, 2012 8:11 AM
    Friday, August 24, 2012 10:12 PM

All replies

  • Which mode of replication did you try? I believe Merge Replication adds a ROWGUIDCOL, but Transactional Replication does not add any columns to my knowing. (But I don't use replication much, so I can be wrong.)

    If you use replication, you can add indexes to db2, but with log shipping db2 is read-only and you cannot add any indexes.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, August 24, 2012 10:49 AM
  • Hi Simon,

    With regards to your first question, If replication is not an option because of application limitation then I you have following options:

    1) In loggshipping configuration do not disconnect user while restoring the log. This option has side effects means if the users are connected then the restore process will fail.

    2) If you are using the SQL Server 2012, then go for database mirroring with readonly replica.

    3) Write the SSIS package and manually pull the changes across. Sounds like a preffered option in your case.

    4) Use Snapshot Replication, Because it does not add additional column.

    5) Use Transaction Replication but do not select updatable subscriber option. This will ensure addition column will not be added.

    With regards to your second question, You have to break replication if you are changing DB2 because you need to set the database to read-write mode. The only option for this is add the index on your primary database and it will automatically get created on secondary database. Apart from suggested options above, There is no otherway to achieve this task.


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Basit Farooq Friday, August 24, 2012 11:19 AM
    Friday, August 24, 2012 10:56 AM
  • Thank you for your thoughts.

    1) is not acceptable.

    2) we have sql2008

    4) 200 GB of data sending every hour? I think it is not realizable. I believe snapshot is more suitable if you spread only parts of data to different servers.

    One of the reasons that we have log shipping is also that there is practically no influence(extra load) on source database.

    3 or 5 - which one of them has the less cost to source database? I believe transaction replication is the best here.

    SSIS - In case I don't have timestamp on table I have to compare all data from source to target to find the changed one.
    And that is true also in case of delete rows from source. In fact I would have to send all 200GB of data every time
    (without changing of source database and add some logic). What do you think?

    Are there any known problems with transactional replication? I have reliability in my mind.

    Thanks, S

    Friday, August 24, 2012 2:07 PM
  • I go with option 5 that is transaction replication. Replication is quite reliable in SQL Server.


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Marked as answer by simonxy Friday, August 24, 2012 2:12 PM
    Friday, August 24, 2012 2:11 PM
  • SSIS - In case I don't have timestamp on table I have to compare all data from source to target to find the changed one.

    You could set up Change Tracking on the tables, to fetch the changed rows.

    But it seems to me that roll-your-own replication takes more effort than just plain old transactional replication, so try that first.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by simonxy Monday, August 27, 2012 8:11 AM
    Friday, August 24, 2012 10:12 PM
  • Thanks for your suggestion.

    If i would like to track the changes immediately, then the best option is still transactional replication?

    In case of SQL2012:
    Miroring in sql2012 is depricated, so a guess I should use SQL 2012’s Always-On feature for read only copy.
    But I can't put custom indexes on copied database and i need enterprise version of SQL2012?

    If I would like to track changes periodically (for example every hour) the Change Tracking on the tables is probably really the best solution?
    (there is no big performance hit?)

    I guess Change Data Capture is better than change tracking, since it capture also data, which were changed(from log with low impact on system). But it is available only for enterprise version.

    With change tracking I have to make LEFT OUTER JOIN to production table and since I don't know which columns were changed, I have to return all columns - probably some performance hit. But since JOIN would be normally by clustered index I guess still satisfied.

    One disadvantage of this solution is that I have a lot of work:
    for every table in my database I have to enable table for tracking and than include update script in SSIS package for each table separately (but benefit of multithreading). And then also managing this packages.

    But I guess not much less work with replication. Only log shipping is simple here :)

    br,
    Simon

    • Edited by simonxy Monday, August 27, 2012 10:13 AM
    Monday, August 27, 2012 8:11 AM
  • If you want replication in more or less real time, Transactional Replication is your best bet.

    Read-only secondaries requires SQL 2012 Enterprise, Windows Cluster and whatnots. And you cannot add any local indexes. It's essentially just a scale-out snapshot-isolation solution.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, August 27, 2012 9:09 PM