Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2015
    Posts
    1

    Unanswered: SYNC Up Database tables from SQL 2008R2 to SQL 2014

    Hi,

    I have SQL SERVER 2014 installed on windows server 2012R2 (Reporting read only database)

    I have 4 databases on sql server 2008R2 from which I created backup and restored them on SQL Server 2014.

    Moving foward I want to SYNC up 2014 when 2008R2 data changes (not all tables, but some tables) (data, views, sp's etc)

    The issue right now is on 2008R2 (all the 4 databases tables don't have Primary keys. I suppose Transactional replication would not work due to PK issue).

    What options do I have to sync up all the 4 databases (selected tables) of 2008R2 to SQL 2014(read only DB for reporting)
    Last edited by sharda0824; 12-20-15 at 20:46.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yep, without primary keys you won't be able to replicate changes.

    How much lag (distance from real-time) is acceptable?
    How big are the databases?
    How big are the tables you are interested in?
    Do any of the tables you want to move to the 2014 instance have deltas indicating when changes occur?
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    With the version difference tossed in, the options get a bit limited.

    You could do snapshot replication, but that may not bring over structural changes, and you are basically wiping/replacing the data in the reporting server.

    You could do the backup/restore routine over and over. This would require a bit of scripting, and care to include the COPY_ONLY directive on the source system's backup, so it does not interfere with any of the regular backups and possibly differential backups you may have. This also does not allow for changing the structure or indexes on the reporting system, unless you get into some serious scripting.

    You may be able to implement change data capture, but I believe that will require a PK on the tables you are monitoring.

    If you implement Merge replication, you will get just the changes in the data, but you will incur a pretty serious performance hit on the source system, especially for large updates, or high numbers of updates, as this will place a trigger on each replicated table.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •