none
Replica for reporting in another location RRS feed

  • Question

  • Hello,

    I have a problem with users who refreshing SQL query in Excel few times per day. Some queries are heavy and it clog our SQL server. We have central datacenter, few branch offices and headquarter. Users from HQ (ca 160 people) refresh excel reports few times per day. I wonder if it's possible to create local replica, then they will be able to get data from local source without stress the main server.

    Which replication option will be the best to achieve this goal?

    I saw I can use Transactional replication - it will be good option but I cannot add tables without primary key. I want to replicate whole database and sync only changes. It would be best to push changes from the main server every time they appear.


    Sunday, November 24, 2019 1:04 PM

All replies

  • another main point to consider - how up to date the reporting data needs to be? If you do not need this real time and probably one day old copy is okay , you could consider log shipping. 

    At it's core, you apply log backups to secondaries server and while they are being applied, users cannot connect to the DB. You can leave DB in read only mode, so, users can connect after it's done.

    or can you add primary key to tables. I think most change tracking mechanisms ( Transaction replication, CT, CDC) depend on primary key requirement.  

    if not, primary key, can you create unique index on the tables missing primary key?

    The other option is to use availability groups but that may be little bit expensive for your needs.

    If not, the other option would be to create triggers but that would be my last option.


    Hope it Helps!!


    Sunday, November 24, 2019 1:59 PM
  • Hi Doomus,          

    If you want to build a local replica server so as to reduce the pressure of the primary server and complete the synchronization of the whole database level, transaction replication may not be appropriate. Replication is more database object-oriented, and its operation mechanism brings great sales to the server. In addition to the performance of the primary server, you may need to consider the degree of data synchronization between the primary server and the local replica server. If you want to ensure that the report data on the local server and the master server are fully synchronized, and that the latest data can be read from the local server, you can consider using the AlwaysOn technology, by establishing the AlwaysOn availability group, set the local server as readable, and adopt the data synchronization mode. However, using AlwaysOn requires that your SQL server is SQL Server 2012 and later.         

    If you can accept a certain time lag of data, log shipping will be more appropriate, but the mechanism of log shipping makes its secondary server not always accessible. You need to plan the backup interval and the time when the client accesses the database.         

    In addition, if you use AlwaysOn or log shipping, you can send some read-only requests to the secondary server, which can share the burden of the primary database to some extent.

    Hope this could help you.

    Best regards,

    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 25, 2019 5:41 AM
  • Thank you Stan210 and Cris Zhan for answers, when I think about it now, there are few special factors in environment, first one is connection, whole traffic is putted through VPN tunnel so it's limited to ca 80Mb/s which is 10MB/s max, database takes 1.3TB, loggs 400GB but there is no additional tasks between both locations during the night, whole bandwidth can be consumed by SQL. Second - SQL version, primary server is SQL Server 2012 Enterprise SP4, secondary SQL Server 2019 Ent. Is it possible to create availability group across different platform?

    how up to date the reporting data needs to be? Business would say "real time" and IMO it will be the best option but I think if they will be refreshed twice a day it will be ok.

    Add primary key to tables - It's not so easy to create primary keys on all tables, I have to much tables without key like almost half of all tables.
    • Edited by Doomus Tuesday, November 26, 2019 5:54 AM
    Tuesday, November 26, 2019 5:45 AM
  • Hi Doomus,

    I think it isn't possible to create availability group across different platform.

    The SQL server versions of all replicas in an availability group must be consistent. Even for rolling upgrade, the versions are only inconsistent for a short time. It is stated in the official documents. please refer to this.

    And according your description,maybe you don't need data to be fully synchronized,using the log shipping is more appropriate.

    But it should be noted that log shipping between different versions of SQL server is not supported too.

    Best regards,

    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 26, 2019 8:17 AM
  • with the above constraints, It looks like your best bet is taking log backups on primary and apply them on secondary at night or something.

    You do not have to do this as Logshipping but just set a process to take copy the log backups from primary to secondary site( not needed if secondary can connect to backups) and restore them with read-only mode. 

    With log shipping, you get the option to manual fail over and switch primary around, but with different versions, you cannot do that. and I think that's okay because you are not looking DR solution but someway to offload reporting workload.


    Hope it Helps!!

    Wednesday, November 27, 2019 3:09 PM
  • Hi Doomus,
    If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.

    In addition, if you have another questions, please feel free to ask.

    Thanks for your contribution.

    Best regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 28, 2019 2:15 AM