1

With Microsoft SQL Server 2005, is it possible to combine the processing power of multiple physical servers into a single logical sql server? Is it possible on SQL Server 2008?

I'm thinking, if the database files were located on a SAN and somehow one of the sql servers acted as a kind of master, then processing could be spread out over multiple physical servers, for instance even allowing simultaneous updates where there was no overlap, and in the case of read-only queries on unlocked tables no limit.

We have an application that is limited by the speed of our sql server, and probably stuck with server 2005 for now. Is the only option to get a single more powerful physical server?

Sorry I'm not an expert, I'm not sure if the question is a stupid one.

TIA

TuffyIsHere
  • 11
  • 1
  • "We have an application that is limited by the speed of our sql server" - are you ABSOLUTELY sure your hardware is the cause??? – Mitch Wheat Mar 20 '11 at 02:42
  • No, but I'm pretty sure it's a combination of hardware and software. We had server timeouts until we moved to a bigger server a couple of years ago, but then the database got a lot bigger and we started getting timeouts again. The application code has been pretty well worked over and the developers are long gone now anyway. – TuffyIsHere Mar 20 '11 at 07:03

3 Answers3

2

Before rushing out and buying new hardware, find out where your bottlenecks really are. Many locking problems can be solved with the appropriate indexes for your workload.

For example, I've seen instances where placing tempDB on SSD solved performance issues and saved the client buying an expensive new server.

Analyse your workload: How Can I Log and Find the Most Expensive Queries?

With SQL Server 2008 you can utilise the Management Data Warehouse (MDW) to capture your workload.

White Paper: SQL Server 2008 Performance and Scale

Also: please be aware that a SAN solution is not necessarily a faster I/O solution than directly attached storage. It depends on the SAN, number of Physical disks in a LUN, LUN subscription and usage, the speed of the HBA's and several other hardware factors...

Community
  • 1
  • 1
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
1

Optimizing the app may be a big job of going through all business logic and lines of code. But looking for the most expansive query can easily locate the bottleneck area. Maybe it only happens to a couple of the biggest tables, views or stored procedures. Add or fine tune an index may help right the way. If bumping up the RAM is possible try that option as well. That is cheap and easy configure. Good luck.

Sibbo
  • 3,796
  • 2
  • 23
  • 41
Mathew
  • 11
  • 1
-2

You might want to google for "sql server scalable shared database". Yes you can store your db files on a SAN and use multiple servers, but you're going to have to meet some pretty rigid criteria for it to be a performance boost or even useful (high ratio of reads to writes, small enough dataset to fit in memory or a fast enough SAN, multiple concurrent accessors, etc, etc).

Clustering is complicated and probably much more expensive in the long run than a bigger server, and far less effective than properly optimized application code. You should definitely make sure your app is well optimized.

easel
  • 3,982
  • 26
  • 28
  • Thanks, I think you are right. I will have to look again at clustering, I thought it was for failover. Anyway, optimizing the app was already done by developers who are gone now. I am just grasping at straws. :-( – TuffyIsHere Mar 20 '11 at 07:08
  • sql server clustering is for failover, not scalability – Mitch Wheat Mar 20 '11 at 07:37
  • 2
    -1 sql server clustering is nothing to do with performance or scalability – gbn Mar 20 '11 at 07:45
  • Thanks for being pedantic about my suggestion for google keywords. Fixed. Multiple servers connected to shared storage is occassionally referred to as "clustering". What he probably wants is a "Scalable Shared Database", which is something he could do with a SAN. http://technet.microsoft.com/en-us/library/ms345392.aspx – easel Mar 20 '11 at 16:21
  • 1
    @easel. Don't you think it is a bit limited to run against a read-only db? Or can this be configured to work with updates as well? – Mikael Eriksson Mar 20 '11 at 21:15
  • Yes, it's very limited. There's no silver bullet, but if you need more performance, there are ways to get it which is what the OP asked for. – easel Mar 21 '11 at 19:15