January 4, 2010 at 2:31 am
We are in the process of changing out architecture to allow the growth to facilitate for continually adding databases to our system.
Proposed:
Two clustered SQL 2008 Servers (Running SQL 2008 (Standard) Server).
10 databases (currently 90GB each on SQL Server 2005, which should reduce to around 30GB using SPARSE functionality)
We could potentially add another 10 to 20 databases.
1 1TB SAN drive: 1 partition for Database file, 1 partition for transaction log file, 1 partition for backups (or should we have individual drives for each file?)
My questions are
1) Should we have
a SAN drive for say a maximum of 10 databases (and then for every 10 databases thereafter, employ another SAN drive),
a SAN for the backup?
a SAN for the transaction logs
2) How much RAM could you recommend? Is there any way to calculate how much RAM we need?
3) What is the best practice involved, and can anyone make any suggestions.
Many thanks,
Laurence Proctor
(Software Development Manager)
January 4, 2010 at 12:43 pm
How much RAM you need is directly proportional to the number of users and/or database connections and inversely proportional to the quality of the database design.
As far as allocating DB filegroups on the SAN and configuring it, it would depend on various metrics of the specific SAN hardware you are getting. I would assume if you discussed what you want to do with the tech support people from the SAN manufacture they could help you determine how to set that up to take best advantage of the hardware and thus maximize your performance.
The probability of survival is inversely proportional to the angle of arrival.
January 5, 2010 at 1:08 am
I assume you'll be using x64 - I'd suggest x32 would be a poor choice. I'd suggest as much memory as possible to a degree this wil be important - but - it really depends upon use/users etc. as already pointed out.
If you're going to be making a lot of physical io becasue of the number of databases then your storage is very important. Depending upon the storage choice you'll have lots of issues here. Generally shared luns are really bad news for sql server, typical effects can include backups making the databases unusable, file copies ( e.g. to tape or disk backups streaming off ) causing problems - of course you may choose to backup direct to tape or another media, not your lun.
Standard edition removes many of the advantages of using multiple procs which your setup could benefit from.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 5, 2010 at 5:56 am
I really appreciate your help here (sturner). This is a very sensible approach to this problem. I will contact the SAN manufacturer as you suggested.
I believe that currently we have around 500+ users and an extra 15 (internal) connections to our 90GB+ database. We only have 11GB of RAM, and continually experience memory problems.
So if we add another 10 of these types of database, would you say that 64GB would be adequate? Bear in mind that we currently use SQL Server 2005, and can reduce the current database by up to 75% in size.
Thanks again for your help.
January 5, 2010 at 6:00 am
Thank you for your help too (GrumpyOldDBA).
We will indeed be using x64. We intend to have around 64GB of memory in our server. Do you think this would be adequate?
As far as storage, it has been brought to my attention that we will be employing a Virtual SAN environment. Would you have any advice on this?
I will recommend tape drive backups for our database. Thanks for this advice.
Unfortunately the powers that be will not allow me to purchase Enterprise, much to my disgust, as I know there are countless features that could benefit us.
i really appreciate your help here!
January 5, 2010 at 7:46 am
yes, based on that many users and DB size I would triple the size of RAM from what you have. Of course, the more the merrier, as you can never have too much (unless you can't afford it!). Also important to make sure temp DB is configured properly .. multiple files and on a separate LUN or fast local array.
good luck.
The probability of survival is inversely proportional to the angle of arrival.
January 5, 2010 at 11:15 am
You don't need Enterprise Edition here. What you DO need is to get a professional on board to review all of the details and configurations and then help you do things optimally. There are a kajillion things at play here, and hunting and pecking on a forum will absolutely not get you a good system. It would take many posts back and forth just to get the relevant details about your IO subsystem configuration with nothing said about the data structures and data access patterns of your application(s).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 7, 2010 at 2:41 am
Thank you for your advice TheSQLGuru.
I have to agree with you (about Enterprise too). That said, I should have told the whole story. We do have a group of professionals taking care of our database structure. They are a data centre too. We are in the process of moving onto a new architecture whereby instead of continually adding new machines for each database, we will add new databases to one machine.
They have already proposed a structure to us, but it is not a detailed one. I simply wanted to get as much information so I can be as prepared as possible.
As far as data structures and data access patterns, we already have a proven infrastructure in place.
I am more worried about scalability.
By the way, I appreciate you being so frank.
Sincerely,
Laurence.
January 7, 2010 at 6:19 am
laurence.proctor (1/7/2010)
Thank you for your advice TheSQLGuru.I have to agree with you (about Enterprise too). That said, I should have told the whole story. We do have a group of professionals taking care of our database structure. They are a data centre too. We are in the process of moving onto a new architecture whereby instead of continually adding new machines for each database, we will add new databases to one machine.
They have already proposed a structure to us, but it is not a detailed one. I simply wanted to get as much information so I can be as prepared as possible.
As far as data structures and data access patterns, we already have a proven infrastructure in place.
I am more worried about scalability.
By the way, I appreciate you being so frank.
Sincerely,
Laurence.
As others around here will tell you 'Frank' is my middle name! :hehe:
Seems like you will be looking at a single box that can handle 20-30 databases that total 2-3TB or so. That really isn't much in this day and age and you should be able to acquire hardware to support that for a pretty reasonable price. I still note that good hardware/software configuration (ESPECIALLY IO subsystem) and data structures, code and perf tuning will be key to success. But you really have no reason to be concerned about the scalability of SQL Server.
If you want a second set of eyes to give your existing/proposed system a review there are several on this site that are qualified to help you with that. Best of luck with the transition!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 8, 2010 at 8:00 am
Hi Laurence
To add to the above post, its vital to get a proper SAN, i have had a basic model SAN, seems to be very nice and afforadable, but you cant get any thing out of it, not even any alalysis, so its wise to get a good SAN 🙂
January 8, 2010 at 8:43 am
having had the ability to carry out quite a period of storage sub system testing and such if your system has issues with heavy io then performance of the san will be important. It to a point depends upon the make of san and I'm not prepared to talk manufacturers here, but in general the key point for sql server and a san is to avoid any sharing of the physical disks in any manner at all. The other sometimes less obvious point is to make sure you don't get stuck with any 7.2k disks which only support low volumes of iops, ideally you want raid 10 luns using 10k or 15k disks ( sas/fc )
Even 64gb of memory only allows you about 3mb allocation for each of 16 databases. when you set max/min memory remember there are a lot of memory allocations outside of this setting - I usually allow at least 8GB for the o/s on x64 find slav oks blog for what uses memory on x64 and how much.
Good luck.
just a passing thought about lots of databases - generally the t-log drive is most critical for a database - the transaction is not committed until write back from the t log. With multiple databases you may want to have multiple dedicated disks/luns/drives ( whatever ) and share your t logs amongst them ( as you tend to do with tempdb ) likely 16 databases on one t log drive would find the drive a bottleneck based upon a probable iops of about 300 for a single 15k disk/raid 1 pair - whatever you do don't put t logs on raid 5 or 6 - and if your vendor says he/she has FAST raid 5 it only means they have hardware calculated parity, something das raid controllers have been using since sql 6.0 ( when I started )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply