473,837 Members | 1,454 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Advice on middleware products for TRUE Scaling out of SQL Server

We have a 3 month old quad processor/dual core server running SQL
Server 2005 and already it is getting close to hitting the CPU wall.
An 8 way CPU box is prohibitively expensive and out of the question. I

am looking desperately for a way to TRULY scale out SQL server...in the

same way that IIS can be scaled out via App Center.

The "in the box" solution for SQL Server 2005 scaling out is the DMV.
Unfortunately this solution makes the system less available rather than

more (one server outage takes them all out for that table) and requires

serious rearchitecting of the software to use. Contrast this to IIS
and AppCenter where each added server makes the system more available,
and requires no rearchitecting to work.

Before someone says "what you want can't be done in a
database"...Ora cle has an application server middleware product that
lets you do both of the above. Just plug a new server with Oracle on
it, and you've doubled your capacity. But SQL Server 2005 doesn't yet
have a similar capability.

So I read with great interest the following article that talks about
why this is the case with SQL Server. There are two issues that make
it very difficult to do:
http://www.sql-server-performance.co...bility_availab...
You can create a crude pool using replication, but the performance
times look horrendous.

However, the article also talks about the latest developments in this
field...specifi cally MIDDLEWARE that can create a scale out solution
that is more available and that requires simply adding new servers to
scale up.

I found two companies which seem to offer this new capability:
http://www.metaverse.cc/newsevents.asp?cid=17999
and
http://www.pcticorp.com/product.aspx

Both companies appear to have patents or a patent pending on the
process. I tried to contact metaverse but got no reply, despite their
recent press release. I just emailed Pcticorp today to see if I could
learn more about their product.

My question for this group is:
Does anyone have experience with either of the two products (or any
others that provide this capability)?

Many thanks in advance for your help.

Ian Ippolito
http://www.rentacoder.com

Apr 14 '06 #1
17 2652

"IanIpp" <ia**********@g mail.com> wrote in message
news:11******** **************@ z34g2000cwc.goo glegroups.com.. .
We have a 3 month old quad processor/dual core server running SQL
Server 2005 and already it is getting close to hitting the CPU wall.
An 8 way CPU box is prohibitively expensive and out of the question. I

am looking desperately for a way to TRULY scale out SQL server...in the

same way that IIS can be scaled out via App Center.

Well the first thing I would say is make damn sure it's not a code issue.

To relate a story, we had two boxes maxing out and were ready to buy a 3rd
in order handle the load.

After reading a white paper on performance tuning, I was able to work with
our developers to rewrite a single stored procedure and get to the point
where ONE box was handling the entire load and still had room to scale.

Ok, that's an extreme case (the boxes were basically doing only the one
thing) but it can show how much a difference simple tuning can make.

Ok, assuming that you've done that, if you can break any of the stuff into
read-only queries, one thing that might work is setup the current server as
a "publishing " server and use replication to push the data to "read-only"
servers.
The "in the box" solution for SQL Server 2005 scaling out is the DMV.
DMV, I'm not familiar with that acronym.
Unfortunately this solution makes the system less available rather than
more (one server outage takes them all out for that table) and requires
serious rearchitecting of the software to use. Contrast this to IIS
and AppCenter where each added server makes the system more available,
and requires no rearchitecting to work.

Before someone says "what you want can't be done in a
database"...Ora cle has an application server middleware product that
lets you do both of the above. Just plug a new server with Oracle on
it, and you've doubled your capacity. But SQL Server 2005 doesn't yet
have a similar capability.

So I read with great interest the following article that talks about
why this is the case with SQL Server. There are two issues that make
it very difficult to do:
http://www.sql-server-performance.co...bility_availab...
You can create a crude pool using replication, but the performance
times look horrendous.
Not necessarily. We do fine with it.

However, the article also talks about the latest developments in this
field...specifi cally MIDDLEWARE that can create a scale out solution
that is more available and that requires simply adding new servers to
scale up.

I found two companies which seem to offer this new capability:
http://www.metaverse.cc/newsevents.asp?cid=17999
and
http://www.pcticorp.com/product.aspx

Both companies appear to have patents or a patent pending on the
process. I tried to contact metaverse but got no reply, despite their
recent press release. I just emailed Pcticorp today to see if I could
learn more about their product.

My question for this group is:
Does anyone have experience with either of the two products (or any
others that provide this capability)?

That I can't say much on. Sorry.

Many thanks in advance for your help.

Ian Ippolito
http://www.rentacoder.com

Apr 14 '06 #2
IanIpp wrote:
We have a 3 month old quad processor/dual core server running SQL
Server 2005 and already it is getting close to hitting the CPU wall.
An 8 way CPU box is prohibitively expensive and out of the question. I


HP ProLiant DL585-G1 128GB/2.4GHz/DC/4P
Availability Date 11/08/05
TPC-C Throughput 202,551
http://www.tpc.org/tpcc/results/tpcc...p?id=105100101

Are you doing more than 100,000 transactions/minute in an OLTP system
and can't pay an 8 way machine?

Then I guess your problem is other, not with SQL2K5.

Apr 14 '06 #3
Is your problem during ETL or Query processing?

Apr 14 '06 #4
Greg D. Moore (Strider) (mo************ ****@greenms.co m) writes:
Well the first thing I would say is make damn sure it's not a code issue.

To relate a story, we had two boxes maxing out and were ready to buy a 3rd
in order handle the load.

After reading a white paper on performance tuning, I was able to work with
our developers to rewrite a single stored procedure and get to the point
where ONE box was handling the entire load and still had room to scale.

Ok, that's an extreme case (the boxes were basically doing only the one
thing) but it can show how much a difference simple tuning can make.

Ok, assuming that you've done that, if you can break any of the stuff
into read-only queries, one thing that might work is setup the current
server as a "publishing " server and use replication to push the data to
"read-only" servers.


To be blunt, I think Ian has a lot of potential here. Provided of course,
that he has control over the code. If he has a some sleazy third-party
app, tuning may not be that much of an option. Then again, SQL 2005
offers plan guides where you can give hints or complete plans to queries
without direct access to the source code. And he can still add indexes.
The "in the box" solution for SQL Server 2005 scaling out is the DMV.


DMV, I'm not familiar with that acronym.


Dynamic Management Views, the new interface to engine-state information
in SQL 2005.

I guess that Ian was thinking of DPV, Distributed Partioned Views.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 14 '06 #5

"Erland Sommarskog" <es****@sommars kog.se> wrote in message
news:Xn******** **************@ 127.0.0.1...
Greg D. Moore (Strider) (mo************ ****@greenms.co m) writes:
Ok, assuming that you've done that, if you can break any of the stuff
into read-only queries, one thing that might work is setup the current
server as a "publishing " server and use replication to push the data to
"read-only" servers.
To be blunt, I think Ian has a lot of potential here. Provided of course,
that he has control over the code. If he has a some sleazy third-party
app, tuning may not be that much of an option. Then again, SQL 2005
offers plan guides where you can give hints or complete plans to queries
without direct access to the source code. And he can still add indexes.


True, I was just giving him the benefit of the doubt. :-)

The "in the box" solution for SQL Server 2005 scaling out is the DMV.
DMV, I'm not familiar with that acronym.


Dynamic Management Views, the new interface to engine-state information
in SQL 2005.

I guess that Ian was thinking of DPV, Distributed Partioned Views.


That makes a LOT more sense. :-)


--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Apr 14 '06 #6
All,

Thanks for the replies.

1) Sorry...yes I did mean DPV instead of DMV! The distributed
partioned views is a way to create an updatable copy of data on
multiple databases in SQL 2005 (which MSFT calls a federation). It
sounds good in concept until you realize that the outage of any one
federated database kills access to the table on all of them. So rather
than scaling and becoming MORE available...thi s solution scales at the
expense of less availability. On the other hand, the middleware
products I quoted above promise BOTH scaling and availability.

2) One other thing..."scalin g up" (adding more CPUs) has a less than
linear effect on performance becuase switching and other things hurt
performance. In other words...if you go from a 2 cpu machine to a 4 cpu
machine...you won't get double the performance (some source quote the
last CPU as only increasing 15-17% rather than 50%). But "scaling out"
(adding more servers) with a middleware product (if it truly works)
promises a linear increase in performance. So it would not only be
cheaper but better...and that is always what everyone is looking for.
That is why I'm hoping that someone here has experience with such
products and can comment on them.

3) Regarding tuning queries,etc.

Yes, we have control over the code but we already run
extensive/constant query tuning and add/adjust indexes and regularly
use the Database Tuning Advisor (see my post here for some of the
existing bugs I've found in SQL 2005's DTA:
http://rentacoder.com/CS/blogs/real_...03/17/447.aspx
). We also update statistics and defrag the indices (and rebuild the
ones that can't be defragged). There are 2 bugs I have open tickets on
with indices not being defragged even after rebuilding...an d not on
small tables, but large ones with thousands of pages of data. I'll
update my blog once MSFT gives more information on what is going on.

But if you are growing, tuning, defragging indices, etc. can only get
you so far. Eventually you WILL run into the limitations of your
hardware. Guaranteed. So it's not a true solution...it just delays
the inevitable.

Regarding:
HP ProLiant DL585-G1 128GB/2.4GHz/DC/4P

Availability Date 11/08/05
TPC-C Throughput 202,551
http://www.tpc.org/tpcc/results/tpcc...p?id=105100101
Are you doing more than 100,000 transactions/minute in an OLTP system
and can't pay an 8 way machine? Then I guess your problem is other, not
with SQL2K5.

I looked at this result and was encouraged for a minute that perhaps we
might be able to make better use of the hardware "somehow". But then I
looked deeper. I have to wonder how applicable these #s are to "real
life". Maybe I'm offbase, but our machine is the top of the line Dell
quad processor/dual core model..and comes in at about $35k (just the
machine...no software licenses). The machine in this contest was
priced at half a million dollars ($500,000)! What are they running
this thing on? My understanding is that Microsoft devotes an entire
team to doing exotic things to the hardware that companies without PHDs
in computer engineering and system design cannot do. I have also heard
(but don't know if it's true) that they add features to SQL Server
after receiving their workload to make it perform well...and if so then
this is something else that no one else can do. If I'm wrong about
any of this, someone please correct me. If I'm essentially right, then
it's not reasoanble to expect these rates.

So in that vein...I'd be interested to hear about anyone with "real
life" implementations and the TPS they are achieving? First, what is
the best way to measure TPS? I found the perf mon counter
"batches/sec"...is that what others use? If so, then we are at about
6,000-8,000/minute on a 64 bit quad processor/dual core machine and
currently at 70-80% CPU capacity. This is far below the 202,000/minute
of the TPC benchmark. What do other people get on this stat?

4) Does anyone have experience with a read-only database in a real life
situation? In reading some papers it seems that using replication to
do this will severely slow down your inserts and updates (one quoted
50-80%). That isn't a realistic solution. Another possible solution
is mirroring and using a snapshot, but if you do this, Microsoft won't
support your database anymore (also not realistic). Maybe log shipping
is the best way...what is your real life experience?

5) Does anyone have experience using these middleware products which
promise better performance and price than traditional scaling up and
more availability than traditional "scaling out" via DPVs or a read
only database?

Ian Ippolito
www.RentACoder.com

Apr 19 '06 #7
Stu
Hey Ian,

Not sure if this will help you or not, but I'm interested in
"real-world" statistics as well. Using your batch requests/sec metric,
we're getting anywhere from 21,000 -30,000 batches per minute on a
32-bit dual XEON machine, with 8 gig of RAM. We run a mixture of both
OLTP and OLAP databases on that box.

HTH,
Stu

Apr 19 '06 #8
Lan,
Just curious, is this a transactional system or data warehouse or both?
We have extensive experience with scaling out SQL Server, but only
from a data warehousing (ETL and Reporting) perspective and not a
transactional perspective.
Thanks,
Brad

Apr 19 '06 #9
Thanks Stu...that is interesting. We're getting less per minute than
you are on twice the # of processors. What percentage of CPU usage
does your box average?

Brad...this is a transactional system (OLTP) database only, with no
data warehousing running on it.

Does anyone else want to comment on their configuration, batches/minute
(or other metric) and CPU usage?

Ian

Apr 19 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
1785
by: Sam Watson | last post by:
Hi, I could use a little help with a project i'm going to build. I know I want to use python and wxWindows but thats about all I know. The client will be linux or windows. The server will be linux. Basically, its a client/server app with a rich GUI client. The big catch is I want to be able to run the client/server remotely across the web in a secure fashion. I sort of want to stay away from a web server based solutions because they...
13
3476
by: David Eng | last post by:
Finally, the C++ standard committee realizes the importance of middleware and distributed computing. The committee now focus on C++ extensions for ISO CLI, the Microsoft middleware platform. Sadly, they chose the wrong middleware platform. Microsoft has notorious application software. They never produce a true enterprise level software. Most of their software products target small companies. However, the strength of C++ is it can...
1
1938
by: hico74 | last post by:
Hi all. I'm looking for a tool which should act like some kind of middleware/ logical layer bewtween the SQL server and the webbased user interface. - It should be possible to easily create simple web forms (only data input and output) without programming effort by "clicking" the fields and their order on the web mask within an admnistrative interface. - It should also be bossible to add "new fields" to the database,
47
4549
by: ship | last post by:
Hi We need some advice: We are thinking of upgrading our Access database from Access 2000 to Access 2004. How stable is MS Office 2003? (particularly Access 2003). We are just a small company and this is a big decision for us(!) It's not just the money it's committing to an new version of Access!
5
5345
by: Calimero | last post by:
I have to write a middleware that could be see as a very specialized Web server (based on threads and sockets) I hesitate between C++ (speed) and C# (more elegant) What is the performance penalty if I use C# instead of native (not managed) C++ ? This software could have to manage 100's of simultanous connections. Realist in C# ? Thanks for your advice.
1
9668
by: David Van D | last post by:
Hi there, A few weeks until I begin my journey towards a degree in Computer Science at Canterbury University in New Zealand, Anyway the course tutors are going to be teaching us JAVA wth bluej and I was wondering if anyone here would be able to give me some tips for young players such as myself, for learning the language. Is this the best Newsgroup for support with JAVA?
13
3124
by: Alan Silver | last post by:
Hello, MSDN (amongst other places) is full of helpful advice on ways to do data access, but they all seem geared to wards enterprise applications. Maybe I'm in a minority, but I don't have those sorts of clients. Mine are all small businesses whose sites will never reach those sorts of scales. I deal with businesses whose sites get maybe a few hundred visitors per day (some not even that much) and get no more than ten orders per day....
7
2067
by: John Paul | last post by:
I'm thinking of building an e-commerce site in php. Anyone got any advice in building one? What is the best way to implement a payment system? Are any legal issues involved? Thanks,
4
2120
RedSon
by: RedSon | last post by:
I'm doing a bit of bluetooth programming and I am writing some middleware to make developing applications easier. The BT driver is implemented as a simple serial driver so its like OpenFile and WriteFile and ReadFile. So I get a notification from the module that there is a phone that wishes to make a hands free profile connection to my device. It also give me a big byte array with some information about the phone that is requesting to connect....
0
9843
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9683
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10577
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10630
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9407
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5851
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4476
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4045
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3126
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.