June 29, 2005 at 6:31 pm
I need to design a SQL Rocketship... and am looking for input from all experienced fronts
Some relevant information:
SQL server (1) needed, services LAN users and users from IIS connection
Current database is ~2Gb; massive growth expected, to 200Gb +
Employees: 50; possible growth to 200
Web users: 250; growth to 20,000
Basis of discussion is:
Windows Server 2003 Enterprise Edition (>4Gb memory)
SQL Server 2000
Memory 4-8Gb, expandable
RAID 1 System / Boot volume
RAID 5 Database/ Transaction Log
Option: add external RAID cage for Database w/ online spare
July 1, 2005 at 8:32 am
A lot of the answer depends on your design of the database(s) but for the sake of hardware (if you haven't purchased yet) consider the following.
Get a system that supports the newer dual-core processors and consider purchasing either the dual-core processors because MS license baed on physical processor not logical. So in effect 1 processors which performas as 2 processors because of the dual-cores means you save a lot on EE for processor licensing which means about a $15k or better savings per processor on licensing they are also 64 bit so they are ready for Win 2k3 64 and SQL 2k 64.
You might even want to opt for the AMD which has a higher FSB than the intel and has a much better benchmarking on the dual-core processors (a bit more costly thou but still consider the savings on processors licensing as your justification.
Get the fastest RAM with the lowest latency scores for your board and fill it now. Odds are you will never purchase the extra RAM if you leave available space after 3 years.
Do not put Web on the same box as SQL (just as a reminder).
Get a seprate RAID for log files and tempdb. Those files use a large amoutn of IO and you really want to seperate from your DB.
If possible consider RAID 10 over 5 (again suggest dual-core and sql licensing savings can be used to justify cost) as it has a much better IO for reads versus writes than RAID 5.
Now if your database will have data that remains static after time consider creating a history table on another filegroup and place those files on another drive and move off closed items to those tables and join with current data only when needed. Put those on RAID 5 to keep your RAID 10 cost down.
This will help keep performance up for in use data and still allow reporting on older items.
But all in all it all boils down to your design and implementation after you take a hardware path.
One thing I will suggest in design is use uniqueidentifiers instead of intergers or some other repeatable data for transaction items this will allow more flexibility in spread the data across multiple servers in the future if you need to.
There are dozens of more comments I am sure will be made and I can find documentation to support most everything I stated if you would like (just don't have time right now).
July 1, 2005 at 8:49 am
Fantastic advice... Except for the GUID's. Indexing, especially clustered indexes, against GUID's is pretty problematic. They're bloody slow. I'd be very cautious in when, where, and how I introduced them to designs.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 1, 2005 at 12:58 pm
Thanks, Antares. Gary
July 1, 2005 at 1:41 pm
Yes I do agree there, it however is far better for portability and the ability to split work between multiple servers to use the GUIDS but of course are not the only way. If anyone has a better way then I will listen but for the systems I have had to deal with they worked best and I really haven't had any issues working with them from a performance standpoint. And I tend to use other items for the clustered index this is purely from a surrogate key standpoint.
July 1, 2005 at 1:44 pm
Alot of information in here for design considerations:
You did not mention any failover, or Distaster recovery strategy.
Log shipping, Clustered Server. Backup Strategies.
Also Consider, the db may be 2g now. Not very big. You need to try to create a hardware roadmap. There is now way you need a $500,000 Drive and server solution if you will not meet the volume required to actually use it.
I like to employ a strategy that Servers typically last 2-3 years. Analyze if a A box with 2-4 Fast Xeon Processor and 4 gb of memory will get you thru 2 years. Then the kinks will be out of the 64 bit hardware. (BTW Some Testing I did at a company with 2 way 64 bit AMD Processors with sql 2k netted in a degrade of performance, It could have been alot of things, but could not justify the cost.)
Buying cutting edge Hardware now, when your volume does not require it will waste alot of funds. And Sql does not efficiently use 64bit processors.
July 1, 2005 at 2:28 pm
Was that with SQL 2K 64bit and Windows 2003 64bit. I have not seen in degrading in performance from anyone else and am curious about this. The only known issues I have seen had to do with hardware drivers.
July 1, 2005 at 2:42 pm
I'm with Ray on this one; frankly I'm not willing to go with AMD, if only because they have no proven track record (at least in my personal realm). AMD needs to put more effort, time and money into true marketing efforts rather than giving it to the attorneys.
Intel is not due for dual core processors in the Xeon range until end of year.
For now, I think I'm going with Dual Xeon 3.4's, 4-8Gb RAM, RAID 1 boot/system volume, RAID 1 log volume, and RAID 10 External Storage array. Don't want to overdo (much) and certainly don't want to under-do.
Gary
July 1, 2005 at 3:05 pm
Test was over a year ago, and I don't think there was a good Sql Server 64 bit out yet.
July 1, 2005 at 3:08 pm
My point here is just to buy the hardware that will easily get you thru the next 2-3 years.
If your nowhere near the projected volume, you don't need a server to satisfy the projected volume, you need a server that handles todays volume.
July 4, 2005 at 12:12 am
Get a system that supports the newer dual-core processors and consider purchasing either the dual-core processors because MS license baed on physical processor not logical. So in effect 1 processors which performas as 2 processors because of the dual-cores means you save a lot on EE for processor licensing which means about a $15k or better savings per processor on licensing they are also 64 bit so they are ready for Win 2k3 64 and SQL 2k 64.
Antares, are you sure about this? Do you have a source URL where this is clearly pointed out? We have been discussing this at work lately and have not found a clear answer. I am aware of how one proc with hyperthreading is treated as two logical procs, and therefore only needs one license. But as I said I have not found anything conclusive that says that dual-core is treated as two logical and not two physical.
July 11, 2005 at 8:34 am
February 28, 2006 at 6:21 am
Antares: Did you mean "Get one separate raid set to put both tempdb and logs on" or "Get two additional raid sets to put tempdb and logs on"?
Seems to me like one additional that both live on would be sufficient, but would like to hear more. We're about to shift servers and I actually have a chance to easily make choices.
Student of SQL and Golf, Master of Neither
February 28, 2006 at 7:32 am
It really is up to how much load you will deal with.
Ideally if you can afford to, put tempdb on a seperate raid from the transaction logs, this will give you a greater thruput overall but again that is based on your cost constraint, if you have to back off because of cost aim to put tempdb and logs on the same drive seprate from the databases themselves. Tempdb is a scratch pad for a lot of operations but is not required to be recoverable other than a location for it to exist. Under normal operating circumstances when SQL Server is restarted tempdb is rebuilt from scratch (this is why you do not purposefully store objects in it).
But then you could look at my scenario for one of my production systems. I process at least 5000 queries per day to upwards of 17000 (last stats I pulled) and my setup doesn't even blink on most quiries, all I have is a single raid 5 array with master, tempdb, databases and transaction logs on it. And the server is running SQL Server 2000, Anaylsis Services, Reporting Services for 2000, IIS and handles about 12 different sites with a mixture of ASP and ASP.NET, not to mention the DTS processes that run thru the day for various tasks. Speaking ideally I would not do this if I had the option. But the design and thru evaluating the indexes over a several month period of time I have brought it up to a very effecient condition. One database is even over 20 GB at this point but from the work done one the pages you couldn't pinpoint which site it is. On top of that much of the data structures are legacy (5+ years) old and many are not truely normalized as much as they need to be. The last two normalizations actually improved performance and stability.
So I suggestion aim for the stars in your proposal (especially for large data volumes), then back down to where you have to be and see which you can get.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply