Building a SQL 2000 server farm

I work for an insurance company that presently has one production sql 2000
server with 4 unique databases. The databases do have some interaction with
each other. I am in the process of planning an upgrade for this server. Ou
r
performance has been slowly dimishing as our system and databases grow. We
have done a lot of optimizations on our data but we have come to the point
where it is time to upgrade the hardware.
I see two possible upgrade options:
1) Build a beefy server with quad processors and a superfast disk subsystem.
2) Build 4 seperate highpower servers and distribute the databases across
these four servers.
Option 2 seems to me to be a much better approach with much better future
growth potential. My question is: What SQL technology should I use to
accomplish the task of accessing the data from multiple servers? I am
familular with and have used linked servers but this approach seems
cumbersome and inflexable.
In my perfect world I would implement the following system:
1) Setup a server farm with a name of "Farm1"
2) Put my four servers in Farm1
3) I would like to access the data using a format simular to
"Farm1.DatabaseName.dbo.tablename"
The reason I like this approach is for redundancy. If one of my servers
fail I could mount the servers database on another server and access the dat
a
without having to change the data path in all my applications.
Is this possible with SQL 2000? Is it possible with a competing product?
If it is possible could someone give me a shove in the right direction?
Thank you for your help!"Oughtsix" <Oughtsix@.discussions.microsoft.com> wrote in message
news:466A0151-DBBA-4204-A208-42704A17DD57@.microsoft.com...
> I work for an insurance company that presently has one production sql 2000
> server with 4 unique databases. The databases do have some interaction
with
> each other. I am in the process of planning an upgrade for this server.
Our
> performance has been slowly dimishing as our system and databases grow.
We
> have done a lot of optimizations on our data but we have come to the point
> where it is time to upgrade the hardware.
> I see two possible upgrade options:
> 1) Build a beefy server with quad processors and a superfast disk
subsystem.
> 2) Build 4 seperate highpower servers and distribute the databases across
> these four servers.
> Option 2 seems to me to be a much better approach with much better future
> growth potential. My question is: What SQL technology should I use to
> accomplish the task of accessing the data from multiple servers? I am
> familular with and have used linked servers but this approach seems
> cumbersome and inflexable.
> In my perfect world I would implement the following system:
> 1) Setup a server farm with a name of "Farm1"
> 2) Put my four servers in Farm1
> 3) I would like to access the data using a format simular to
> "Farm1.DatabaseName.dbo.tablename"
> The reason I like this approach is for redundancy. If one of my servers
> fail I could mount the servers database on another server and access the
data
> without having to change the data path in all my applications.
> Is this possible with SQL 2000? Is it possible with a competing product?
> If it is possible could someone give me a shove in the right direction?
> Thank you for your help!
Google for SQL Server Scale Up Scale Out
Tons of great articles on this type of thing.
Without knowing the nature of your databases, how they are being utilized
etc. I really couldn't make a recommendation about which approach would be
best for your needs.
Rick Sawtell
MCT, MCSD, MCDBA|||I'll wade in in you don't mind...
For the highest scalability, putting each database on it's own server is the
way to go... But the question is whether or not you really need that
scalability... It is certainly going to be much more expensive... If I may
make a suggestion...
Maybe you should consider using an Active/Passive cluster... one server
holds all 4 databases, but can automatically fail over to the other
server... users connect with a virtual server name, so if a failover occurs,
the connection string does not have to change...
if you need to grow, make the cluster active/active, with SQL running on
each server ( each can fail over to the other)... You'd would have to buy
additional disk, server licenses, and move 2 of the databases to the other
server. The client connection strings would also have to be changed for
those 2 databases.
The way you can go ahead and abstract this a little, is to use the client
network utility and create 4 aliases ( representing each of the 4
databases)... initially point each of them to the single virtual server...
As you make changes, you will have to change the alias on the client ( but
not the client apps.)
Hope this helps...
By the way - Talk to your Operating system ( DNS) kinds of guys who know
clustering really well... They may also have some ideas...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Oughtsix" <Oughtsix@.discussions.microsoft.com> wrote in message
news:466A0151-DBBA-4204-A208-42704A17DD57@.microsoft.com...
>I work for an insurance company that presently has one production sql 2000
> server with 4 unique databases. The databases do have some interaction
> with
> each other. I am in the process of planning an upgrade for this server.
> Our
> performance has been slowly dimishing as our system and databases grow.
> We
> have done a lot of optimizations on our data but we have come to the point
> where it is time to upgrade the hardware.
> I see two possible upgrade options:
> 1) Build a beefy server with quad processors and a superfast disk
> subsystem.
> 2) Build 4 seperate highpower servers and distribute the databases across
> these four servers.
> Option 2 seems to me to be a much better approach with much better future
> growth potential. My question is: What SQL technology should I use to
> accomplish the task of accessing the data from multiple servers? I am
> familular with and have used linked servers but this approach seems
> cumbersome and inflexable.
> In my perfect world I would implement the following system:
> 1) Setup a server farm with a name of "Farm1"
> 2) Put my four servers in Farm1
> 3) I would like to access the data using a format simular to
> "Farm1.DatabaseName.dbo.tablename"
> The reason I like this approach is for redundancy. If one of my servers
> fail I could mount the servers database on another server and access the
> data
> without having to change the data path in all my applications.
> Is this possible with SQL 2000? Is it possible with a competing product?
> If it is possible could someone give me a shove in the right direction?
> Thank you for your help!|||Oughtsix wrote:
> I work for an insurance company that presently has one production sql
> 2000 server with 4 unique databases. The databases do have some
> interaction with each other. I am in the process of planning an
> upgrade for this server. Our performance has been slowly dimishing
> as our system and databases grow. We have done a lot of
> optimizations on our data but we have come to the point where it is
> time to upgrade the hardware.
> I see two possible upgrade options:
> 1) Build a beefy server with quad processors and a superfast disk
> subsystem. 2) Build 4 seperate highpower servers and distribute the
> databases across these four servers.
> Option 2 seems to me to be a much better approach with much better
> future growth potential. My question is: What SQL technology should
> I use to accomplish the task of accessing the data from multiple
> servers? I am familular with and have used linked servers but this
> approach seems cumbersome and inflexable.
> In my perfect world I would implement the following system:
> 1) Setup a server farm with a name of "Farm1"
> 2) Put my four servers in Farm1
> 3) I would like to access the data using a format simular to
> "Farm1.DatabaseName.dbo.tablename"
> The reason I like this approach is for redundancy. If one of my
> servers fail I could mount the servers database on another server and
> access the data without having to change the data path in all my
> applications.
> Is this possible with SQL 2000? Is it possible with a competing
> product?
> If it is possible could someone give me a shove in the right
> direction?
> Thank you for your help!
Hi
Without knowing the size and utilisation of your databases, it's hard to say
it one server is better or worse than 4 seperate. As Rick mentions, 4
servers in a proper configuration will cost a bit - not that 1 beefy server
is cheac either but still....:-). One poverfull server with a good
disk/RAID configuration where you can split logfiles for different databases
on different arrays etc. might be able to do a good job.
You also mention that the 4 databases have some interaction with eachother -
but how much? If you split them on 4 servers, there might have to be done
quite a bit of coding in the application(s) to access databases on other
servers - and is this possible? If your applikation is a "standard"
applikation it can easily turn out to be a nightmare with this additionally
coding because who is then responsible for this when you receive upgrades
etc.?
With this in mind, I think I'd go for either a cluster/SAN solution, or just
a single powerfull server with an adequate disk system.
Regards
Steen|||Rick, Wayne and Steen;
Thank you for your replies!
Well the boss liked the idea of multiple SQL servers and I ended up with 4
dell 2850's with Dual 3.2 Xeons, 4GB memory, internal Raids and two JMR Raid
boxes on my desk.
A little more background:
All of our applications are written in house.
Our old server was an IBM 380 with dual 1.2 Xeon processors and 5 x 18 GB
15K rpm drives.
Every three years we send all of our old servers and workstations to the
scrap heap and replace all of our equipment. Our current cumulative databas
e
size is 24GB. Our total dollar amount underwritten has been doubling every
year for the last four years. My best estimate is our database storage need
s
have quadrupled every year.
The four Dell 2850 servers are setup with:
1) The OS on a partition on the internal raid.
2) The Log files on the second partition of the internal raid.
3) The Data files on the JMR boxes.
4) The JMR boxes have two sets of fiber ports capable of connecting two
servers at once. We have two partitions of 1.6TB on each JMR. Each of the
4
Dells is assigned one of the 1.6TB partitions.
5) The cost of these four servers was less than the cost of a decked out
Quad processor server.
6) This configuration has to last us 3 years, what ever our growth, without
any additional hardware.
Last night we migrated our databases from the IBM to one of the Dells. This
morning the speed improvement is extremely noticeable.
This morning I have been charged with taking the three remaining Dell
servers and create some sort of server farm out of them. I am continuing to
research scaling out but most of what I have found is the theory of how it
works with very little about the nuts and bolts. I have added "the network
utility and client aliases" to my research list.
I am a programmer with a strong background in networking. I am in charge of
SQL server administration, our asp based website which is targeted as the
primary company interface to our patients, doctors and sales agents. I also
do some work in VB but the majority of our back end processing is handled in
VB by other programmers.
Any additional tips or shoves in the right direction are greatly
appreciated. Specifically I have been reading very mixed feedback about
implementing SQL server clustering. I am very interested in any experiences
with SQL clustering, good or bad? I am very interested in the possibility
leaving our four SQL servers independent then linking them together. My
ultimate SQL system would have one “Farm” name to reference all the
databases. Ie. Farm.database.table
Thank you!