I have a server with 12 gb of ram and 4tb of the space on the harddrive. i have to share the space and ram on this server with other applicaition servers, along with SQL SERVEr 2008. As its not a dedicated server for the database , i was wondeirng what (ram and space )should ask for the sql server 208, so that there is not much of the performance issues. i have to put 3 instances of the sql server, for as the dev test and prod. Any suggestions?? Thanks,
You've got a single server that is acting as test, development, and production? Before we even begin to talk about how well SQL Server will work with other application servers or not (usually not), what happens when the well-meaning developer writes a query that uses up all CPU available? I would absolutely advocate against having development and test on the same server as production. Even if you have to develop on local boxes, it's safer than having development on the same machine as production. That said, SQL Server does not play well with others. You can limit the memory available, and based on the information supplied, I would. You didn't say if it was a 64bit machine or not. Assuming it is, I'd allocate as much memory for SQL Server as I could. It's going to be very hard to give you a specific number because I don't know the other systems or how much load they anticipate handling. I would not go below about 4gb, just based on the fact that you're talking about managing up to 4tb of data. You don't mention the number of processors on the machine. I assume, based on the memory & storage there's probably more than one. If SQL Server has to share these processors, best thing would be to set affinity in SQL Server to some sub-set of processors, but that assumes the other services can also set affinity. If not, just leave it alone and hope SQL Server wins the processor wars more often than it loses them. From the sounds of things, if you're really that unsure of stuff, you might want to bring in a consultant to help out.
This is really a loaded question. The amount of drive space all depends on what data you plan to store in the databases. It may be that 4TB is over kill if your production databases only total 20 GB. As for RAM, again it really depends what kind of load SQL will be taking. Should should allocate more RAM for Production than you would dev and test. Since you state that there will be other applications other than SQL running I would ask for 2 GB Dev, 2 GB Test, and 4 GB Production, that will leave 4 GB for the OS and applications running. That is just me and my opinion. Will the Dev and Test be exact copies of the production DB's? If so just triple whatever requirement you need for Prod. I would mention that you should follow best practice with designing your storage structure. Separate spindles (luns if SAN) for your data, logs, tempdb (could split tempdb data and tempdb logs as well), and backups.
It is quite difficult to give a answer to this question, as there is little indication of what is going on on the system apart from SQL Server. You do mention that there are other processes, but not what. If it is file serving, then that shouldn't cause too many problems, but something like an exchange server could prove difficult to house together with SQL Server. If you had this machine for pure SQL Server, I would suggest something in the region of 8-9GB for SQL Server (you will have to split this over the three instances), leaving 3-4 GB for windows/rest. However, it may be that the other processes are RAM hungry (although SQL Server is one of the **most** RAM hungry applications available). Please check that side of things out to see if there are other resource intensive processes and account for that in RAM assignment. The RAM assignment is a per instance setting, you will have to set a maximum and allow SQL Server to do its thing. It will not take all the RAM in one go, but rather "grow" up to the maximum you specify. The default is something ridiculously high (can't remember the number of the top of my head), which basically tells SQL Server to take all the RAM it can. As for disk storage, only you can say how much space will be required, it is sort of a "how long is a piece of string" type of thing. I think you will be more interested in making sure that the 4TB you have will perform fast enough to run SQL Server at any sort of servicable speed. If you have 4 1TB drives, all in a RAID 5 array, you may have some problems. SQL Server needs disk I/O and **lots** of it. With 12GB of RAM (8GB available if lucky), you will probably be using the drives to serve up data quite a bit. If they are not up to the task, you will experience queries waiting on disk. This can be overcome by uprating the disks (more, bigger, faster) or upping the RAM for SQL Server (allows more data to be cached in RAM). Take a look at this whitepaper for [Storage Best Practices] If you are on SAN storage, then I can highly recommend the [SAN Storage Best Practices from Brent Ozar] If you can provide a little more detail into the setup, I, or others here, may provide a little more targeted help. :