Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jpotucek
Constraint Violating Yak Guru
273 Posts |
 Posted - 2004-08-16 : 12:13:36
|
| In order to reduce the number of SQL Server Instances in our Network, my Company is considering combining SOME of our current Environment (30 - 40 Prod and DEV Instances with MULTIPLE Databases and a diverse set of Applications) into 2 Production Clusters and DEV/TEST Instances as needed. We will probably also add several more LARGE applications to replace our current CRM environment. In talking with some of the developers and business people there could be as many as 1000 users accessing any one SQL Server database at any time. I need to research this and find out what kind of limitations or guidelines on SQL*Server such as maximum file size for database files, number of users or memory restrictions. I also need information on how these Clusters need to be configured to handle that kind of usage.Can anyone lend any insight or perhaps tell me where I may be able to research this? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-16 : 12:21:19
|
| Go to the Index tab in BOL. Type in capacity. Pull up the capacity specification SQL Server topic. It'll tell you SQL Server's maximum capacity.Tara |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-16 : 12:39:58
|
| [code]Maximum Capacity SpecificationsThe first table specifies maximum capacities that are the same for all editions of Microsoft® SQL Server™ 2000. The second and third tables specify capacities that vary by edition of SQL Server 2000 and the operating system.This table specifies the maximum sizes and numbers of various objects defined in Microsoft SQL Server databases, or referenced in Transact-SQL statements. The table does not include Microsoft® SQL Server 2000™ Windows® CE Edition. Maximum sizes/numbers Object SQL Server 7.0 SQL Server 2000 Batch size 65,536 * Network Packet Size1 65,536 * Network Packet Size1 Bytes per short string column 8,000 8,000 Bytes per text, ntext, or image column 2 GB-2 2 GB-2 Bytes per GROUP BY, ORDER BY 8,060 Bytes per index 900 9002 Bytes per foreign key 900 900 Bytes per primary key 900 900 Bytes per row 8,060 8,060 Bytes in source text of a stored procedure Lesser of batch size or 250 MB Lesser of batch size or 250 MB Clustered indexes per table 1 1 Columns in GROUP BY, ORDER BY Limited only by number of bytes Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement 10 Columns per index 16 16 Columns per foreign key 16 16 Columns per primary key 16 16 Columns per base table 1,024 1,024 Columns per SELECT statement 4,096 4,096 Columns per INSERT statement 1,024 1,024 Connections per client Maximum value of configured connections Maximum value of configured connections Database size 1,048,516 TB3 1,048,516 TB3 Databases per instance of SQL Server 32,767 32,767 Filegroups per database 256 256 Files per database 32,767 32,767 File size (data) 32 TB 32 TB File size (log) 4 TB 32 TB Foreign key table references per table 253 253 Identifier length (in characters) 128 128 Instances per computer N/A 16 Length of a string containing SQL statements (batch size) 65,536 * Network packet size1 65,536 * Network packet size1 Locks per connection Max. locks per server Max. locks per server Locks per instance of SQL Server 2,147,483,647 (static)40% of SQL Server memory (dynamic) 2,147,483,647 (static)40% of SQL Server memory (dynamic) Nested stored procedure levels 32 32 Nested subqueries 32 32 Nested trigger levels 32 32 Nonclustered indexes per table 249 249 Objects concurrently open in an instance of SQL Server4 2,147,483,647 (or available memory) 2,147,483,647 (or available memory) Objects in a database 2,147,483,6474 2,147,483,6474 Parameters per stored procedure 1,024 1,024 REFERENCES per table 253 253 Rows per table Limited by available storage Limited by available storage Tables per database Limited by number of objects in a database4 Limited by number of objects in a database4 Tables per SELECT statement 256 256 Triggers per table Limited by number of objects in a database4 Limited by number of objects in a database4 UNIQUE indexes or constraints per table 249 nonclustered and 1 clustered 249 nonclustered and 1 clustered 1 Network Packet Size is the size of the tabular data scheme (TDS) packets used to communicate between applications and the relational database engine. The default packet size is 4 KB, and is controlled by the network packet size configuration option.2 The maximum number of bytes in any key cannot exceed 900 in SQL Server 2000. You can define a key using variable-length columns whose maximum sizes add up to more than 900, provided no row is ever inserted with more than 900 bytes of data in those columns. For more information, see Maximum Size of Index Keys.3 The size of a database cannot exceed 2 GB when using the SQL Server 2000 Desktop Engine or the Microsoft Data Engine (MSDE) 1.0.4 Database objects include all tables, views, stored procedures, extended stored procedures, triggers, rules, defaults, and constraints. The sum of the number of all these objects in a database cannot exceed 2,147,483,647.Maximum Numbers of Processors Supported by the Editions of SQL Server 2000This table shows the number of processors that the database engine in each SQL Server 2000 edition can support on symmetric multiprocessing (SMP) computers.Operating System Enterprise Edition Standard Edition Personal Edition Developer Edition Desktop Engine SQL Server CE Enterprise Evaluation Edition Microsoft Windows® 2000 DataCenter 32 4 2 32 2 N/A 32 Windows 2000 Advanced Server 8 4 2 8 2 N/A 8 Windows 2000 Server 4 4 2 4 2 N/A 4 Windows 2000 Professional N/A N/A 2 2 2 N/A 2 Microsoft Windows NT® 4.0 Server, Enterprise Edition 8 8 2 8 2 N/A 8 Windows NT 4.0 Server 4 4 2 4 2 N/A 4 Windows NT 4.0 Workstation N/A N/A 2 2 2 N/A 2 Microsoft Windows 98 N/A N/A 1 Use Desktop Engine 1 N/A N/A Microsoft Windows CE N/A N/A N/A N/A N/A 1 N/A Maximum Amount of Physical Memory Supported by the Editions of SQL Server 2000This table shows the maximum amount of physical memory, or RAM, that the database engine in each SQL Server 2000 edition can support.Operating System Enterprise Edition Standard Edition Personal Edition Developer Edition Desktop Engine SQL Server CE Enterprise Evaluation Edition Windows 2000 DataCenter 64 GB 2 GB 2 GB 64 GB 2 GB N/A 64 GB Windows 2000 Advanced Server 8 GB 2 GB 2 GB 8 GB 2 GB N/A 8 GB Windows 2000 Server 4 GB 2 GB 2 GB 4 GB 2 GB N/A 4 GB Windows 2000 Professional N/A N/A 2 GB 2 GB 2 GB N/A 2 GB Windows NT 4.0 Server, Enterprise Edition 3 GB 2 GB 2 GB 3 GB 2 GB N/A 3 GB Windows NT 4.0 Server 2 GB 2 GB 2 GB 2 GB 2 GB N/A 2 GB Windows NT 4.0 Workstation N/A N/A 2 GB 2 GB 2 GB N/A 2 GB ©1988-2000 Microsoft Corporation. All Rights Reserved.[/code]Sorry....I just couldn't resist. :) I wouldn't ever have DEV/TEST instances on my production clusters by the way. That's a recipe for disaster.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
jpotucek
Constraint Violating Yak Guru
273 Posts |
Posted - 2004-08-16 : 13:57:28
|
| Thank you for the replies.DEV/TEST are definitely seperate.. on standalone Servers.. |
|
|
|
|
|
|
|
|
Scaling up?