I have a production database (SQL server 2016) which is hosted on an Azure VM. We have set up a log shipping (every 15 mins) onto another Azure VM which is a read replica and would act as a DR system. The team also makes use of this DB for their quick reporting needs.

But since it is a read replica team will not be able to create any tables in it for reporting purpose. So looking for a way to get real-time data from production DB on a periodic basis but also it has to be writable so that teams can dip into it for all their quick reporting needs.

  • 1
    Can they use temp tables? – Erik Darling Jan 17 '18 at 2:35
  • 2
    Or create their objects in a separate database on the replica. – Aaron Bertrand Jan 17 '18 at 2:38
  • I meant I wanted them to be able to create new physical tables / temp tables as per their need in this new DB so that they can continue their reporting/analytics work on it. But since we have set it up as read replica via logshipping it is not allowing it currently. So looking for other solutions which can help us achieve it – prasanth Jan 17 '18 at 2:45
  • In lieu of a full blown datawarehouse solution, I would lean towards Aaron Bertrand's approach of a separate database. Maybe consider scripting out views for each of the source tables with identical names. That way all of the users existing queries will still work in the new DB. – Shooter McGavin Jan 17 '18 at 2:51
  • @AaronBertrand Can you please elaborate? Once we create a new DB within the same server where the read replica is getting updated every 15 mins how can the users query data from both those DB together? They should have a login created with access to both of these DB is it – prasanth Jan 17 '18 at 2:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.