What are the options for scalability with SQL Server 2008? Or does it even have options to design for scalability?
+1
A:
http://www.microsoft.com/sqlserver/2008/en/us/performance-scale.aspx
Please be more specific with your question if you need a detailed, situational analysis.
The basic scalability/redundancy options included with SQL 2008 R2 Enterprise are:
Clustering - Allows multiple nodes to work in conjunction, either Active/Active (both nodes serving traffic) or Active/Passive (one node in standby). However, it is important to point out that this clustering is available via the Windows OS and shared storage between multiple modes, so the hardware requirements on an active/active scenario can become significant.
Here is a good explanation: http://www.mssqltips.com/tip.asp?tip=1541
Mirroring - Two databases are synchronously or asynchronously updated in conjunction; this provides high availability.
Replication - full or partial duplication of data from one node to another node. Depending on how it is used, replication can give scale, redundancy, and even offline capability (for example, when used with a mobile device that is only occasionally connected).
A simple Google search for any of these (or an MSDN search) will yield an abundance of results.
You may also find these specific benchmarks interesting: http://www.tpc.org/tpcc/results/tpcc_perf_results.asp?resulttype=undefined&version=5%&currencyID=1
SQL Server presents a very cost-effective option.
Tim
2010-09-23 06:18:25
Thanks Tim. Im not looking at mirroring or replication as they are not options for real time scaling. Will see if clustering is true to what are the needs for scaling out my application
Msdnexpert
2010-09-23 06:28:07
@Tim - Clustering is a failover option as per MS. I dont see any Real Time Clustering options or some features like Real Application Cluster with SQL Server 2008.
Msdnexpert
2010-09-23 06:34:51
Edited my answer to give more info about clustering. Basically, you CAN achieve both HA and handle throughput, but only when SQL Enterprise is used in conjunction with a Windows cluster and shared storage.
Tim
2010-09-23 06:42:38
@Tim - In terms of cost SQL Server beats Oracle hand down but Im looking at close to 600000 active users.
Msdnexpert
2010-09-23 06:52:58
For 600K users, that's a significant hardware and application discussion as well as a database question (IMO). The good news is, most resellers and manufacturers will be glad to help you design the right environment because you will potentially be making a sizable purchase. My guess is that regardless of database, your most sizable investment will be in a robust SAN (or ideally, multiple SANs for failover and/or increased performance).
Tim
2010-09-23 07:09:31
A:
it looks like very broad question.
There are many scalability options in sql server and here is a great article about Scaling Up Your Data Warehouse with SQL Server 2008.
I can easily say that one of the major feature which support scalability well is Partitioning
Hope this help.
orka
2010-09-23 06:47:29