Hardware based database partitioning for Optimal Performance and Reliability???
Shamshad Ali
related to correct news group or not. I as looking for SQL Server General
Administration news group. which I did not find here. if some one know, plz
let me know. Here is my actual question.
I have been assigned a task to partition one database into 3 to 5 databases.
Each database will be doing some specific task related to the business
logic. Like error and exceptions will be in separate db, lookup tables will
be in another db. Users related information and its Tracking will be in
another db. Currently these all functionality is in one db. Overall the
taget is to achieve optimal Performane and Reliability. Before i start, I
gathered some information and started distributing all tables among
different databases. Right now i am thinking if i use a Hardware level
database partitioning like rather i distribue tables in different databases,
there is an option to put table in specific file group and use RAID10. From
my own study i think RAID10 provide Performance and Reliability both. Is it
fine? I need to know if i'm on right path (either use partitioning db
(Option 1) in different dbs or use filegroup with RAID10(Option 2)). How
much these two will be feasible and which one approach is best? what else
you can suggest to achieve performance and Reliability both.
P.S. DB Partitioning (Option 1) will be implemented on Cluster env. Each db
will be on active/active node.
Please help what others are doing for handling large number of users
activity and large databases?
Shamshad Ali.
Andrew J. Kelly
and reliability? In terms of reliability it is probably the exact opposite.
If you have lookup tables in one db and you need to reference them from
another db you have a hard dependency between the two. So if the lookup db
is down then so is the other one. This is even worse if you plan to put each
of these in a separate SQL Instance. You can partition tables for
maintenance and to some extent performance reasons in a single db, there is
no need to split them. You can also dictate where the files in a give
filegroup will live and have the option to separate objects onto different
drive arrays if need be. With several dbs you also have the added burden of
ensuring they are all kept in sync and in the even you have a failure how
will you restore them and ensure they are all in sync? While this can be
done you make the job much more difficult than if they were all in a single
db. How large do you expect this db to be? I think you need to revisit what
you are trying to accomplish and what you will lose each way.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Shamshad Ali" <shamsh...@softech.us> wrote in message
news:Oy6pRzjG...@TK2MSFTNGP05.phx.gbl...
Erland Sommarskog
> I have been assigned a task to partition one database into 3 to 5
> databases. Each database will be doing some specific task related to the
> business logic. Like error and exceptions will be in separate db, lookup
> tables will be in another db. Users related information and its Tracking
> will be in another db. Currently these all functionality is in one db.
> Overall the taget is to achieve optimal Performane and Reliability.
I completely agree with Andrew: don't go there. It may be an idea
to split up a database in several, if you have plenty of rarely
access archive data. In this way you can reduce backups so on.
But lookup tables? Meaning that you cannot use foreign keys? That's
not a good idea at all.
> Right now i am thinking if i use a Hardware level database partitioning
> like rather i distribue tables in different databases, there is an
> option to put table in specific file group and use RAID10. From my own
> study i think RAID10 provide Performance and Reliability both. Is it
> fine? I need to know if i'm on right path (either use partitioning db
> (Option 1) in different dbs or use filegroup with RAID10(Option 2)). How
> much these two will be feasible and which one approach is best? what
> else you can suggest to achieve performance and Reliability both.
Using multiple filegroups on the other hand can be a good idea. I'm still
skeptic to put lookup tables on a separate filegroup, for the simple
reason that they tend to be small and be in memory anway.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Shamshad Ali
I want to make Option 1 more clear what we had planned. If this approach has
any flaws or will not work in production environment, please spot them
immediately. As we have almost 500,000 number of online users. We will be
using SQL Server 2005 - 32 Bit. The target is to achieve High Performance
and reliability.
We are planning to have Two clusters. Cluster A (3 nodes Active/Active) and
Cluster B (2 Nodes Active/Passive)
Cluster A:
Custer A will have 3 nodes and we keep them Active/Active, each Active node
will be passive to another node. If one will be down the any one of two
active nodes will take responsibility and provide services for failed
instance db, as the physical location of databases will be on SAN. So all
the DBs will be alive as long as any of node will remain active. Correct?
Cluster B:
Cluster B will be used for reporting purpose and site's administration
activity only. This Cluster will have 2 nodes Active/Passive and all online
data will be moved from Cluster A DBs (all partitioned DBs) to Cluster B via
SQL Server Merge replication. Merge replication will be used by site's
administration portion to update lookup tables etc.
All the users activities via website will be handled by Cluster A and the
load will be shared among instances of this Cluster. Cluster B will be only
used for reporting/ handling admin activities etc.
Please reply with your suggestions, what you recommend how to handle large
databases where Performance and Reliability is goal to achieve?
Shamshad Ali.
"Andrew J. Kelly" <sqlmvpn...@shadhawk.com> wrote in message
news:OgOIj0pG...@TK2MSFTNGP02.phx.gbl...
Shamshad Ali
Please refer to my reply to Andrew first. Also I asked for suggestions how
to handle large database as it is growing day by day and we have to plan it
for handling 500,000
number of online users. Each users' activity is logged and updated in DB and
the goal is the provide Optimal performance to users and reliability of
running application 24X7.
Also your last para!
"Using multiple filegroups on the other hand can be a good idea. I'm still
skeptic to put lookup tables on a separate filegroup, for the simple reason
that they tend to be small and be in memory anway"
How can we keep lookup tables in MEMORY? the lookup data also change
frequently.
Please help and reply with your recommendations.
Shamshad Ali.
"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9C65398A...@127.0.0.1...
Shamshad Ali
http://msdn.microsoft.com/en-us/library/ms190619.aspx
After some study, Hardware based solution are typically implemented using
RAID;
http://msdn.microsoft.com/en-us/library/ms188252.aspx
� What type of disk hardware to use, such as RAID (redundant array of
independent disks) devices. For more information, see About Hardware-Based
Solutions.
� How to put data onto the disks. For more information, see Using Files and
Filegroups.
� Which index design to use to improve query performance in accessing the
data. For more information, see Designing Indexes.
� How to appropriately set all configuration parameters so the database can
perform well. For more information, see Optimizing Server Performance.
Improving Database Performance
Using files and filegroups improves database performance, because it lets a
database be created across multiple disks, multiple disk controllers, or
RAID (redundant array of independent disks) systems. For example, if your
computer has four disks, you can create a database that is made up of three
data files and one log file, with one file on each disk. As data is
accessed, four read/write heads can access the data in parallel at the same
time. This speeds up database operations. For more information about
hardware solutions, see Database Performance.
Additionally, files and filegroups enable data placement, because a table
can be created in a specific filegroup. This improves performance, because
all I/O for a specific table can be directed at a specific disk. For
example, a heavily used table can be put on one file in one filegroup,
located on one disk, and the other less heavily accessed tables in the
database can be put on the other files in another filegroup, located on a
second disk.
Shamshad Ali
"Shamshad Ali" <shamsh...@softech.us> wrote in message
news:49C739CC-038F-4F73...@microsoft.com...
Andrew J. Kelly
It sounds like you have the makings for a real disaster there. I highly
recommend you hire a very good consultant to spend some time with you to
determine a proper approach to solve your problems. There is far too much
details to be covered properly in a forum post but it appears you do not
have a solid approach to any of the issues. For one why are you using 32 bit
if you are concerned with performance when memory will be crucial to getting
good performance. Also splitting the 3 databases across 3 nodes of a cluster
means you have to use link server access for all your joins, lookups etc
which will also be a disaster. And least of all using Merge replication for
reporting purposes is a bit extreme to say the least. If you continue down
this path you will almost assure yourself of failure or at the very least
least a maintenance nightmare that you will regret.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Shamshad Ali" <shamsh...@softech.us> wrote in message
news:513F183C-4ECC-485C...@microsoft.com...
Erland Sommarskog
> Please refer to my reply to Andrew first. Also I asked for suggestions
> how to handle large database as it is growing day by day and we have to
> plan it for handling 500,000 number of online users. Each users'
> activity is logged and updated in DB and the goal is the provide Optimal
> performance to users and reliability of running application 24X7.
I read your reply to Andrew, and while Andy is a lot more qualified to
assess hardware configuration than I am, I can only say that I raised
my eyebrow quite a bit when you said SQL Server 2005 32-bit. What's
wrong with SQL 2008 64-bit if you aim at a high-end system?
> How can we keep lookup tables in MEMORY? the lookup data also change
> frequently.
There are two answers to that question. The first answers the question
as such: because frequently accessed data will be in the buffer cache.
The other answer is the same that Andrew gave you. The fact that you
ask that question, clearly indicates that you are not qualified for the
task you present, and your organisation is in dire need of hiring outside
expertise.
But speaking having things in memory, if you want a high-end system you
may want to consider RAM disks, at least for tempdb.
Shamshad Ali
"The other answer is the same that Andrew gave you. The fact that you ask
that question, clearly indicates that you are not qualified for the task you
present, and your organisation is in dire need of hiring outside expertise.
"
Yes, Of course, I'm not an expert as you and others here, that's why I'm
here to post questions/issues/thoughts and proposals based on my knowledge
and willing to get right answers from all experts and learn and achieve
something that makes something fixed. When there is something not clear and
the way we are thinking is not justified to me I posted here to get feedback
from experts and that's why the forums and newsgroups are here. so please
help me, guide me what should be the best choice here? Let me know the path
to achieve the target as I initiated. If you need more details, please let
me know.
Your suggestion on hiring an expert need more suggestions aswell. Let me
know how and where can I find an expert?
Shamshad Ali.
"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9C65ED52B...@127.0.0.1...
Andrew J. Kelly
Erland and myself have been answering questions on these newsgroups for many
years and have hopefully helped many people. But the answers to your
questions are not something that can properly be answered in a news group.
You are talking about several complex subjects with lots of dependencies on
your specific requirements and environmental conditions. We could easily
point you in the wrong direction and that is not worth the risk just to say
we answered a question. You need someone who can sit down with you and
others there to determine a proper strategy and implementation path and that
will likely take days to weeks to accomplish properly full time.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Shamshad Ali" <shamsh...@softech.us> wrote in message
news:8FE1445A-7898-42BD...@microsoft.com...