SQL Architecture Opinion

Question

SQL Architecture Opinion

Strommy

SSCrazy

Points: 2386
More actions
October 19, 2006 at 8:18 pm

#62218

Hello. I am looking for your opinions on an architecture solution that our System Architect came up with for our SQL Servers within our IT division. Essentially, his idea is to implement one large SQL Server that all databases will run off of, regardless of use. Here is some base information about our setup:

We have approximately 20 SQL Servers. A small number are for our public website applications. They are in a separate zone and do not contain any protected/private data. There are approximately 200 databases of varying size (most under 1 GB) with varying uses and importance in our trusted zone. We also have 4 major 3^rd party applications with a database backend. When deploying a new application, I determine which server to put it on based on uptime requirements and load. I understand that 20 servers is excessive, but my request is the following:

What do you guys think of having a single fault tolerant SQL Server for ALL databases, combining public/private data, high load needs/low priority, essentially putting all of our eggs in one basket. I already have my opinion, but I want to extend the opportunity for people in the industry to comment on this particular idea.

Thanks in advance!
Eric

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 1

From a security best practices perspective, you'd want to segregate the databases accessed by your public web site from your internal (sensitive) databases and that means different servers. The risk is fairly simple to explain:

External facing web site gets compromised and that site is used to enter in to the SQL Server itself. One lapse in security on the SQL Server side and the attacker now has access to all the data. Private data, data which should never be accessible from the outside, is now directly exposed. This isn't a good situation to be in.

As for the rest of your databases, server consolidation is often good... within reason. Having a single, fault-tolerant SQL Server, such as a cluster certainly reduces management overhead... unless the SQL Servers are short on resources. With the 4 major apps, how do they do on performance? It's impossible to say if collapsing onto a single set of servers is a good idea without someone doing some stress testing.

K. Brian Kelley
@kbriankelley

Strommy SSCrazy Points: 2386 More actions · Answer 2

Thanks for the feedback. Security and performance are my major concerns as well. Considering the nature of our database applications, there are few single servers that would have the amount of memory and IO throughput that the seperate server can provide now.

One solution that I thought had potential was to use the Polyserve product. Any thoughts on that?

Thanks,

Eric

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 3

At the PASS Summit a few years ago I met the PolyServe guys and had a chance to see demos of their product. It's nice, but it's a high-availability solution... granted, it will work on a 64-bit system. If you are able to go 64-bit, you may be able to meet your hardware requirements.

K. Brian Kelley
@kbriankelley

sqldba-294117 Hall of Fame Points: 3486 More actions · Answer 4

If security is in question then you can put SQL Servers in DMZ & open certain ports at firwall.

Strommy SSCrazy Points: 2386 More actions · Answer 5

I agree. Actually the SQL Servers for our internet facing applications are in such a zone with those protections. The additional protection comes with the fact that there is no PHI in these servers right now. They are within our intranet. Adding applications that contain PHI add additional risk that concerns me. We can mitigate the risk, but if we don't have to, that would be my preferred choice.

Thanks,

Eric

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 6

Well you already said it 'Eggs in one basket'. Additionally you have the performance concerns associated with using a single BUS. What kind of drive controller are you using? Is this a fiber channel? What about your drives, are you using a SAN? If not then you will be using a single set of heads / drives across how many drives, how many transactions per minute? What is the mean fail time of these drives under that load?

Are you running in 64 Bit OS / SQL? How many threads to you expect to open, how much memory is in the machine?

A single server situation is NEVER idea, even for a small company and a couple of DB's. You should run a clustered topology; to what degree I don't know, I am not your DBA and I don't know your business, budget, critical nature of your data, ect.

You have multiple servers now, maybe too many; I don't know. To have one however is to flip to the opposite side of the field, way too far in my opinion.

Jeffery Williams

MC-RDBMS

MCDBA

MCSA

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh