1

I am beginning the development of a new web application which needs to be designed to scale from the word go.

I am looking into database replication and the concept of master/slave replication (or publisher/subscriber - I think these are the same things but please correct me if I am wrong).

The topology I have been reading about involves the master handling all of the database writes and the slave handling all of the reads.

My question is:

In a scenario where the slave must have as close to real-time data as possible, will transactional replication be less of a load on the master database than if it was being read directly?

Also, will the performance of the slave not be affected by the transactional replication as though it were being written to?

Generally, it feels like the number of operations are being multiplied by the introduction of replication

e.g.

  • data is written to master database (by client)
  • data is read from master database (for replication)
  • data is written to slave database (for replication)
  • data is read from slave database (by client)

To me, it feels as though any benefit received from adding an additional server will be lossed in the addition of the extra read and write process.

Am I looking at this too simplictically? or am I missing something?

Thanks

Lewray
  • 143
  • 1
  • 1
  • 6
  • 1
    what does that mean scale from the word go – Alex Gordon Aug 13 '12 at 15:59
  • I just mean that from the very start of the project we will be considering how best to design it to scale easily. – Lewray Aug 13 '12 at 16:10
  • Is the slave on the same network segment? Is the slave required to be writable by connecting clients? What is your primary driver for replicting to one or more servers? – jimbobmcgee Aug 13 '12 at 17:21

1 Answers1

0

SQL Server replication on the publisher side is feed via an application which reads data directly from the transaction log not from the actual SQL Server database tables.

On the subscriber side data is being written to the SQL Server row by row via stored procedures (by default).

There are some VERY large applications which I manage that don't have any need to have reads offloaded to another SQL Server. There are a very few number of applications that actually need to be scaled out. 99% of the time simply setting up proper indexing and properly designing the server and the storage will handle huge workloads if a big enough server has been purchased.

mrdenny
  • 27,074
  • 4
  • 40
  • 68