What is the simplest way to have a table from production database (simple recovery model) into the reporting database without locking?



  • I am exploring various options to have a table from production database onto the reporting database without locking the production database.

    The prod db is in simple recovery model so I cannot do log shipping.

    What is the other alternative that is as simple as log shipping in terms of configuration and maintenance?



  • https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-replication?view=sql-server-ver15 is an option (as long as the table has a primary key or a unique set of fields). It's a few steps to initially get setup but is a pretty good solution when you don't need to synchronize the entire database, and offers some of the most flexibility in what you can do with it:

    Transactional replication typically starts with a snapshot of the publication database objects and data. As soon as the initial snapshot is taken, subsequent data changes and schema modifications made at the Publisher are usually delivered to the Subscriber as they occur (in near real time). The data changes are applied to the Subscriber in the same order and within the same transaction boundaries as they occurred at the Publisher; therefore, within a publication, transactional consistency is guaranteed.

    I don't believe you'll find any solution that offers no locking, but this one has minimal locking outside of the initial snapshot being synchronized to the Subscriber. Which that can even be minimized by restoring a backup as a seed for Replication at the Subscriber.

    You can also minimize the impact of resource contention on the Publisher server by putting the Distributor on it's own server or on the Subscriber server. (I've personally found no need to do that yet, and have been using replication for many tables for years at this point.)


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2