2

I'm about to scale UP our sql2008 database. Simple. But I might need to scale OUT our sql databases.

For a simple scale out situation (ie distributing the processing load), are there some good initial best practices? i know there's so many solutions which will be product specific -> many writes & not many reads, many read & not many write, a bit of both, etc. etc..

But for a site that's pretty read heavy (instead of write heavy), is there a common starting point? eg. grab a second sql box, add some sync thingy and off u go.

Improve this question

4 Answers 4

Reset to default
2

How up-to-date does the data on the other boxes need to be?

MSSQL has a great one-way sync relationship setup. You have to be licensed for appropriate versions of SQL Server (I don't think it's included in the most basic one), but it's exceptionally easy to set up.

The only catch is that you can only write to one location, all the other locations need to be read only. For two-way syncing (if you're going to be writing at all) it's a lot more complex.

So in short, yes, a 2nd box with a sync thingie will work quite well, but you will also need to do your own load balancing (i.e. have one web server read one sql server, another web server read the other sql server), as they still appear as seperate instances. Otherwise you're into clustering which is another kettle of fish.

So, this sync thingie - what is it and how do you set it up? Well, in your SQL Management Studio (SSMS) you will see a folder for "Replication" in the navigation pane, with publications and subscriptions.

In a nutshell, you will:

  • Publish a database on the primary database server
  • Subscribe to a publication on the secondary, read-only servers
  • The subscribers will synchronise on a schedule (this can be constant for almost-instant replication)

There's a lot of articles, so just google SQL Server Replication.

As far as hardware goes, our primary db server is a Dual-Quad-Core with 4gb of ram. Our slaves are Dual-Core with 4gb of ram. You can buy a lot of servers at that level. Of course, it all depends on what kind of load you expect.

Improve this answer
5
  • @Farseeker: "but you will also need to do your own load balancing (i.e. have one web server read one sql server, another web server read the other sql server), as they still appear as seperate instances" Can u elborate on this? Are there some links to how other people have done this? The rest of the answer makes sense :)
    – Pure.Krome
    Jul 18, 2009 at 1:46
  • Sure. SQL Server A will be a full read-write 'master', or 'publisher', and SQL Servers B & C will be read-only slaves 'subscriber'. Lets say you also have three web servers, WEB-A, WEB-B, WEB-C. If each web server points to SQL-A then SQL-B and SQL-C will sit around doing nothing. You would have to configure WEB-B and WEB-C to read from SQL-B and SQL-C, and write to SQL-A. If you're after a solution where all web servers connect to say, SQLSERVER and a broker then chooses the server with the least load, that's something else alltogether.
    – Mark Henderson
    Jul 19, 2009 at 21:38
  • Or, an easy (but inefficient) way to do this, is in your application, have two DB connections. The 'DBWRITE' connection will ALWAYS connect to SQL-A and 'DBREAD' will cycle through SQL-A, SQL-B and SQL-C (Switching to a different one each time it connects/reconnects). Like I said, it's not going to be particularly efficient but it could work.
    – Mark Henderson
    Jul 19, 2009 at 21:41
  • are there any links to projects that have this or pages that elaborate on this .. with hopefully some info against a .NET project using SQL Server?
    – Pure.Krome
    Jul 26, 2009 at 8:04
  • Hmm, I've been re-reading your question and brushing up on my SQL Replication settings for a project at work and I think you'd be better off with an SQL Merge replication. I've never used it myself, but here's some reading: msdn.microsoft.com/en-us/library/ms151329.aspx and databasejournal.com/features/mssql/article.php/1438231/…
    – Mark Henderson
    Jul 27, 2009 at 2:10
1

the question is more complex that a few posts can answer. There are too many options how to scale you servers, just to name a few: failover cluster, log shipping, replication, database mirroring.

I would recommend you a wonderful book that can be downloaded here : Pro SQL Server 2005 High Availability

Hope you will find answers to your questions there

Improve this answer
1
  • Cheers for the link. Unfortunately, that book is not part of the download, in the link provided :( damn!
    – Pure.Krome
    Jul 18, 2009 at 1:59
0

These days hardware is pretty powerful and cheap, so you can go a long way just by using a decent server. If, as you say, your database is mostly for reading, then a Poweredge 2950 with 16GB of RAM and a six 15K disk RAID5 (or 6) is a very powerful base for a SQL Server; add as many cores as you want but even twin quad cores isn't that expensive. I think the 2950 will take up to 64GB of RAM, though this will be expensive!

Your hardware may already be this powerful. If so you're looking at a step change in power and cost, and if so I think you need better advice than just a few posts on ServerFault :-)

JR

Improve this answer
0

well this is difficult to answer question in that sense, that you (devs) had to think about this problem while designing database. But take a look into shared databases for high read/write ratio this might be easiest to implement. Other solution might be replication.

enjoy, m

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .