Should we Replicate? Or is there another Performance solution?

Question

Should we Replicate? Or is there another Performance solution?

simon4132-806507

Mr or Mrs. 500

Points: 580
More actions
December 7, 2009 at 12:34 pm

#218323

Hi,
We have a busy website that fires about 1000 queries per second at our mirrored sql server 2005 db.
At very busy times the database slows down. We have just added loads of memory which reduced this, but it still does slow down.
It seems a good idea to me to set up some replication subscribers for read-only (select) queries and just use the master database for write queries. Then we could spread the load accross a number of machines.
This would be really great - apart from the fact that we would need to set up 2 mirrored machines for the distribution server, and then the subscriber machines cannot actually be mirrored which is not good.
This seems to be how other large sites expand though to cope with huge traffic.
Does anyone have any better ideas?
If not - then what sort of spec do you need for the distribution server? Should it be similar to the spec of the publisher?
What is the replication time lag? do the records get updated on the subscribers instantly?
One final thing - the other option is simply to setup another database mirror and move new users onto the new database - which would be empty and therefore fast, however we would prefer to just have one database.
Any ideas are welcome!
Thanks
Simon

Viewing 5 posts - 1 through 4 (of 4 total)

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

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 1

There is not much of a time lag in Replication (Transactional Replication), so the subscribers will have the data changes (inserts, updates, deletes) in a near real time.

Regarding a suggestion, I would try implementing Peer-to-Peer Transactional Replication. It would be Scale-Out deployment.

Read the Technet Article Peer-to-Peer Transactional Replication

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

Paul White SSC Guru Points: 150467 More actions · Answer 2

You really need to analyse where the current bottlenecks are before embarking on such a bold project.

http://technet.microsoft.com/en-gb/library/cc966413.aspx

That White Paper is a great starting point. Depending on what your main 'waits and queues' are, will drive appropriate solutions.

As far as replication is concerned, no, it is not instant. On a well-specified system, you may expect typical latency of a few seconds, but it depends.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

LOTs of possibilities here (as usual). what type of mirroring? network speed/latency? have you done fileIO and waitstats analysis (on BOTH primary and secondary) to determine what is really the problem?

I would like to add that mirroring and replication are both very advanced topics and you should consider getting a qualified consultant on board to do a perf check on your current system and then help you determine the correct course of action to solve your specific needs. Going back and forth on a forum is not the way to solve such complex issues as you present. 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

simon4132-806507 Mr or Mrs. 500 Points: 580 More actions · Answer 4

Hi,

Thanks for all the replies..

I think you are right in that the best solution is probably to get a consultant on board who can really look into this properly.

All the best,

Simon