CODEWITHSUNDEEP
sql-serversql-server-2008-r2database-replicationlog-shipping
M.Ali
M.Ali

Reputation: 69504

Best Solution to have a Live copy of a Database when replication is not an Option

Recently I had to implement transactional replication to have a live copy of that database on another server for reporting purposes. While configuring replication I realized that a lot of tables didn't have a primary key, so I could not publish all the tables I wanted to.

Second option was to implement merge replication but that would have added a GUID column to all the tables. Since it is a database for a vendor application and vendor has warned us to not "touch" the database structure because any change in the database structure can cause their application to break. So merge replication is not an option anymore.

I have been doing some research on other available options for me in this scenario; the only thing I could find is Log Shipping. I know it will leave my database in Read-Only mode but (to my knowledge) since this is the only option I am left with and it will be strictly used for Reporting purposes only I think I can live with this.

Can anyone suggest a better solution for this? Or is Log Shipping the only option left for me?

It is SQL Server 2008 R2 64-bit DataCenter Edition.

Upvotes: 1

Views: 2513

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

Your other options are:

  1. Database mirroring, and using a snapshot for read-only operations. It can be a pain to manage snapshots.

  2. Upgrading to SQL Server 2012, and make use of Readable Secondaries in Availability Groups. This can be a pain in the wallet.

You mention log shipping but, based on your follow-up comments I don't think it's clear that, every time you restore a log to the log shipped copy, you need to kick out all of the users that may be running reports. This is because you need exclusive access to the database in order to restore the log. This is another case of "you get what you pay for" - you can log ship to Express instances, if you want to (and if your database supports it), but it's not exactly a watertight solution.

Upvotes: 2

Related Questions