4

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.

Improve this question
3
  • 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
    Commented Apr 3, 2012 at 10:54
  • Grant. Replication especially peer to peer can be used for Scale Out scenario.
    – SQL Learner
    Commented Apr 4, 2012 at 16:46
  • Before undertaking the upgrade process, have tackled performance tuning the most expensive queries?
    – datagod
    Commented Apr 4, 2012 at 17:25

4 Answers 4

Reset to default
5

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.

Improve this answer
1
  • 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
    Commented Apr 4, 2012 at 9:02
4

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?

Improve this answer
4
  • 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
    Commented Apr 4, 2012 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
    Commented Apr 4, 2012 at 15:47
  • Not sure why SQL server team can't implement somthing similar to ORACLE RAC (Real Application Clustering?)
    – SQL Learner
    Commented Apr 4, 2012 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
    Commented Apr 4, 2012 at 17:15
1

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.

Improve this answer
1
  • if only performance linearly like that :(
    – Nick Kavadias
    Commented Apr 4, 2012 at 15:49
1

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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