My DB is SQL Server 2008 and I want to scale up the system by 5 fold (maybe more in the future).

The system currently has 95% read & 5% write from front end. And from backend actual data is bulk loaded using ETL.

I looked in to options like

  1. Scaling with shared database (using SAN)
  2. SODA (new caching mechanism)
  3. Clustering in SQL Server 2008 R2

but all either allows only read or duplicate the existing data.

Can someone point me in the right direction (replication of data would be my last but one option).

Thanks JC

Thanks all for the suggestions.

Looks like i am left with increase the hardware and for better performance at this point of time.

SAN setup would become more administrative work because the updates from data load would happen atleast once a day and we need to bring down the servers on SAN every time. And there is also some updates happening from front end.

Other thing i am planning to do is to divide the data on to two servers based on regions, which will reduce the load on each server to almost half.

  • 1
    Problem is you've mentioned at least two technologies that have nothing to do with scaling your system, Clustering and Replication. Since you're talking those, I'm having a hard time understanding what problem it is you're trying to solve. – Grant Fritchey Apr 3 '12 at 10:54
  • Grant. Replication especially peer to peer can be used for Scale Out scenario. – SQL Learner Apr 4 '12 at 16:46
  • Before undertaking the upgrade process, have tackled performance tuning the most expensive queries? – datagod Apr 4 '12 at 17:25

To take a different approach, what is driving your read workload - Is it the application, or do you have a bunch of reports hanging off the system that are driving the load? Depending on the nature of your workload you might be able to re-shuffle your hardware or push some of it off onto other machines.

A couple of ideas:

  • If you have a lot of read workload driven by reports (as opposed to application screens), consider moving those off onto a replicated server (more than one if necessary). More extremely, if you have the option of doing this, you could create a data mart specificually tuned for the reporting. Obviously this might depend on how up to date the data has to be.

    It should be possible to do this without having to interfere in the application code, so it's not as an extreme approach as one might think. If you need it to be up to date you could do something with change data capture at the expense of extra complexity.

  • If your DB server is I/O bound but you have plenty of spare CPU capacity (or maybe you can upgrade the CPUs) you might get some mileage from placing some or all of your database on SSDs.

Without looking into the technical specifics of your problem it's hard to see what can actually be done with the workload.

  • most of request are for reading the data. but they are not reports as such. so replicating some part of the data in to a different server would take considerable amount modifications to the code for getting data from both databases. – JCVKR Apr 4 '12 at 9:02

Do you have your terminology right? stealing from wikipedia:

  • To scale vertically (or scale up) means to add resources to a single node in a system, typically involving the addition of CPUs or memory to a single computer.
  • To scale horizontally (or scale out) means to add more nodes to a system, such as adding a new computer to a distributed software application.

The methods your describing are scale-out, except for 3. which is neither.

  1. good option if you have a SAN that support creating read-only LUN's. You can send read only queries to your read only instances. this will help you scale OUT
  2. SODA = Service Oriented Database Architecture? is a design architecture. no.1 may help you with this, but its a bigger concept & it looks like you're supporting something that's already been built.
  3. sql server supports fail-over clustering which alone isnt going to help you. its for high availability.

Yes, most scale out options will be about caching data, or creating read-only replicas. What other options did you have in mind?

  • I was thinking of some thing like replicating the scripts alone on multiple servers and acessing a single data, but came to find out that only oracle rac does that but not sql server. and i dont have the flexibility to modify my existing scripts as they have some business logic which is difficult to re write . – JCVKR Apr 4 '12 at 8:54
  • the latest & greatest sqlserver has is AlwaysOn which would suite your needs. its a 2012 feature though msdn.microsoft.com/en-us/library/ff877884.aspx – Nick Kavadias Apr 4 '12 at 15:47
  • Not sure why SQL server team can't implement somthing similar to ORACLE RAC (Real Application Clustering?) – SQL Learner Apr 4 '12 at 16:44
  • 1
    lets not start a religious war here. IMHO - lots of patents on the way the network page locking works in RAC are owned by oracle & a share everything architecture is an ugly thing to design – Nick Kavadias Apr 4 '12 at 17:15

Theoretically speaking and with the information provided: if you want to scale up your system five fold, increase your CPU/Disk/Memory by five times.

If you want to scale out, you can use transactional replication. Have one publisher that your are writing to (as you have only 5% writes) and scale out to your subscribers (maybe five of them) who are read only. You can even use log shipping with readable secondary instance. This will throw in a bit of DR for your setup.

Clustering only gives you high availability. There is no performance gain with the increase in the number of nodes you have.

  • if only performance linearly like that :( – Nick Kavadias Apr 4 '12 at 15:49

One othe way to Scale SQL Server out is using Peer to Peer Replication.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.