Post reply

Readonly copy of parts of the database on another server for reporting purposes

  • November 11, 2014 at 5:08 am

    #1757655

    I think Steve is on the best track. You need to look at three things: 1) Total size of the objects you need to copy; 2) Rate of change to what you need to copy, 3) Do you need continuous or time-limited access to your copied data. These three things will point you to your best solution.

    If the rate of change is high (say above 50%) then your best solution may be to take a fresh copy every day. You then need to look at how you can optimise the time needed for the copy, such as using snapshot technology, etc.

    If the rate of change is fairly low (say under 20%) then your best solution will be to ship incremental changes. There are a number of ways to do this: Log Shipping DB mirroring if you need most of your DB copied, or replication or change tracking based ETL if you only need a few tables copied.

    DB Mirroring, preferably the Always On version, is the easiest to deal with, and will give you an exact copy of your source database on your target server. Always On gives continuous access, DB mirroring can give you 23x7 access but a daily outage would be needed to reset a database snapshot. The main downside (apart from space) is that Always On is Enterprise Edition only.

    A solution based on transactional replication is built in to SQL Standard edition or above, you just need to configure it. If you use change tracking you will need to build an ETL workflow. Both of these solutions will give you continuous access to your database copy.

    Hopefully you should be able to work out what solutions might fit your requirements. Let us know your thoughts, and people in this group may be able to offer further advice. Whatever solution you choose, you are the person who has to implement and maintain it, so do not choose a solution you cannot work with.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • November 12, 2014 at 5:10 am

    #1757921

    Thank you all!

    Greetz
    Query Shepherd

Viewing 2 posts - 16 through 16 (of 16 total)

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