SQLTeam.com | Weblogs | Forums

Will Replication solve my problem


#1

I will do my best to explain the situation and what I am trying to achieve.

My company provides live data during sports events and with hiring more data entry employees comes more locks on our database. We have a team that enters data as the sporting event is happening and another team that "publishes" this data onto a website. Problem is during busy times during the "publishing" important tables get locked and slow down the saving of events done by the data entry team.

We have determined that maybe there are too many hands in the pot and we will replicate between two databases so the data entry team saves on DB1, that is replicated to DB2 and the "publishing" hits DB2. Which could help but I feel that during the replication process the tables will still be locked and I still have the same problems.

I have more then capable hardware and open to all suggests and what the best practice might be. Is this my best option and hope for the best?


#2

You might be able to solve the locking problem (Writers block Readers) by setting the database to READ_COMMITTED_SNAPSHOT. This would require no code changes, but you do need to check that the (very few) issues regarding coding changes do not apply to you (or you can easily fix them :slight_smile: )

Alternatively for "Replication" you could use Log Shipping. You set the data entry database to Full Recovery Model (probably already set to that, if not it probably should be!!) and then take Log Backups frequently. They are copied to your other server and restored, with the database remaining in "recovering" state (i.e. so more Log backups can be restored). The database is accessible READ_ONLY for reporting.

The data will only be as "fresh" as the Log Backup frequency. You could take a log backup every minute ...

If that is not often enough you would need replication.

The amount of complexity of each solution increases dramatically between each of those three choices.


#3

I assume you are using SQL server here, and with SQL 2012, I would suggest you to use availability groups with readable secondary. Where the Primary can be used for data entry guys and the read-only copy used for the data publisher, as from the looks of it, the data publish team will only "read" the data and publish them online.

This might be perfect for you as the data is in real time and no locking will occurs since the read and the write are in different server. However, you will need a secondary hardware to set it up.