January 26, 2012 at 5:16 pm
I don't have a specific problem, but I'd appreciate some expert opinions.
I have a server with four processors: 2.5 GHz Intel Xeon E5420s, 64-bit Windows Server Standard 2008, 6GB RAM, on which I am running SQL Server 2008 Express R2. The machine was configured by someone who knows nothing at all about databases, and I sort of inherited the setup. It started life as a remote storage computer with a large amount of disk space (3TB), and recently also became a host for a single database with around ten users, rarely more than two or three at a time, and used mostly for read access. The database has around 90,000 record in the main table, 60,000 in a secondary and about twenty peripheral tables, with a few dozen to a few thousand records in each. Not a large database by SQL Server's standards, and certainly nowhere close to the limits of even the free version, but large enough to be a dog if not designed properly.
A new project has reared its head and the machine will soon be hosting databases for several departments, with potentially several users in each department. Four additional departments are on now, with very simple one-table databases, but both number and complexity of databases will grow. In all cases I expect the traffic to not be significant - the type and amount of use from each department will not be even as large as the initial one - likely one or two people from each additional department, and eight departments total in the section. Some departments may have more than one database, but in all cases, they are small - a few thousand records at the most, in the foreseeable future.
After listening to Idera's webcast on configuring servers by Robert Davis last week, I'm considering two things to deal with the growth, and I'd be glad to hear some thoughts on my plans.
1. A paid version of SQL Server. It seems to me the standard version should be plenty for the expected traffic. Actually, even the free version should handle it for a while, but I'd like to be prepared for the future, not wait until I start running into problems, and it's a shame to have all those processors not doing anything, when they could be making things run faster. But is there any benefit to using a higher-horsepower version? Moving to 2012 version? What I have read seems to indicate that the extra features in the developer and enterprise versions are far beyond what I could expect to use in my environment (single machine, probably no more than a dozen databases, most of them small), and the 2012 version doesn't appear to offer anything I need that 2008 doesn't already have, but I'm certainly willing to listen to other opinions.
2. More storage. There are six disks in the machine now: two in a RAID something comprising the C: drive and four in a RAID 5 array making up D:. This does not allow for much positioning of log, TempDB and the like. The amount of storage is adequate, even considering that the machine is used as a garbage dump for storage of stuff that probably should go in the trash. There are no more drive slots in the machine, so what I am thinking is to buy a separate disk storage unit, something like Iomega's PX network storage boxes. I've already got one in house, a 12TB unit, and it seems to work well. What I would do is attach it with a GB network cable, move all the rubbish from the RAID D: drive to the external unit, dismantle the RAID array and make each disk drive a separate letter. That would allow me to split the various SQL Server files onto completely separate drives, as well as keeping people's rubbish out from under foot. The external box could also serve as a partial backup site for the database files, and the internal drives would be reserved for SQL Server's use.
I know, it would be better to have a separate place for people to park their pornography and wedding pictures, but I can't really kick everyone off the machine just because I want to play with it myself. Also, many of the images that are stored on the machine are also linked to the databases, so it's not just random garbage. The scientists photographing samples park their photos on the machine and the databases reference them during operation.
January 26, 2012 at 5:35 pm
With your hands tied behind your back, you do not have much option than live with what you have I guess.
Regarding SQL 2012, I would always wait for the first service pack to be released before I think about installing it. SQL 2012 has lots of interesting stuff in it. If SQL 2008 provides you with all the functionality you need, then go for it. We know that it is stable.
The idea of moving to a paid version is a good idea. Utilize the power you have. If this Server is used as a storage, I am sure you will see bottlenecks in IO if the SQL Server gets busy. Make sure that you assign atleast 4Gb for the SQL Server. That would give enough breathing room for SQL Server to not to go to the disk all the time.
Regarding taking the RAID out and seperating the disk and assigning different drive letters, I am not sure how much gain you are going to get from it. As long as you are not going to get additional IO channel, you will still have an issue. Also keep in mind that the Log files are written sequentially and Data files are Random writes. Also if you remove the RAID, how will you get any redundancy? You are at a bigger risk of loosing database.
Just my 2 cents
-Roy
January 26, 2012 at 8:24 pm
Since you are using SQL 2008 R2 Express you may be wasting some of your hardware.
Express can only use 1 processor (however many cores are in that processor) (Per: http://msdn.microsoft.com/en-us/library/ms143760.aspx.
Express can only use 1GB of memory. (Per: http://msdn.microsoft.com/en-us/library/ms143685.aspx)
Express R2 databases can only be 10GB.
So I would absolutely recommend a paid version of SQL otherwise you are wasting about 4GB of memory that you just can't use..
With the number of disks you have you should probably just leave the RAID 5 alone for now. Unless you get another disk array then you might consider rebuilding it as a RAID 10 for tempdb and storing data and log in the new array..
CEWII
January 27, 2012 at 2:05 am
Thank you both, that confirms my thoughts on the paid version of SQL Server - little point in having all that RAM and processors if they're not getting used.
But I'm confused on the disk setup. Wouldn't dismantling the RAID allow me to locate files on physically separate drives? That's one of the things that I've frequently seen recommended, but when all disks are in an array, there is no telling what's going where, in addition to them all sharing one I/O channel. Splitting the array seems to me would give each one a separate channel and should improve performance. Or am I missing something fundamental?
January 27, 2012 at 3:08 am
If you are concerned about disk I/O you might want to look into using the Partition features of sql to partition filegroups to maximise I/O. - Just a thought 🙂
January 27, 2012 at 5:41 am
Well, yes, but again, wouldn't that be served best by being able to physically separate the locations? With a RAID array, everything goes down one pipe, it's up to the array to decide where to put it and those decisions may not be optimal for a database. Or does the RAID structure offer enough advantage via its distribution across various physical units to offset that? I really don't know that much about how the RAID mechanism operates. The technical articles I've found haven't helped much and I don't have the resources to try both configurations for a direct comparison.
January 27, 2012 at 6:26 am
How many IO channels do you have in the server that you have? Do you have any additional IO card? If not, you will probably use one for your OS and the other for your other drive. That would mean that what ever Drive setting you have, you still will not be able to get any dedicated channel for the different drives you are planning to have. (I think) I am not sure about that.
If you have an additional IO Card and an external drive, You could split the external drive into two and use the additional IO card to configure your IO path.
-Roy
January 27, 2012 at 6:38 am
Roy Ernest (1/27/2012)
How many IO channels do you have in the server that you have? Do you have any additional IO card?
I'm not sure - how do I find out? The server is not easily accessible - can I read it from some settings when I log onto the server remotely?
If not, you will probably use one for your OS and the other for your other drive. That would mean that what ever Drive setting you have, you still will not be able to get any dedicated channel for the different drives you are planning to have. (I think) I am not sure about that.
Wouldn't each disk have its own channel if I dismantle the RAID array and connect directly to the motherboard?
If you have an additional IO Card and an external drive, You could split the external drive into two and use the additional IO card to configure your IO path.
For the external drive I was thinking a network drive, connected via ethernet. The I/O demands for it would not be extreme. It would contain lots of stuff, but access speed is not a primary issue for the data that would be stored on it. My concern is trying to get the database engine configured as well as I can, hence my attempt to physically separate the various SQL Server files.
January 27, 2012 at 6:50 am
You will have to check the server Specs to see how many channels it has. Usually there is one Internal IO card that will have two channels. This will be connected to your Disk bay. That means you get two IO channels. One can be configured to your OS drive. The other you will have to share for your Data and log files.
Keep in mind that you will be sharing the TempDB with the User Database. It would be better to have the temp DB with its own IO path. (Especially since you said there will be reports run from the DB's)
Network drive will be just a share, right? That would not hurt much if you are not accessing those large files from that Server. if you do access those large files from the netwrok share for the DB server, then you could cause Network IO bottleneck. Are you planning to have any Teaming done with your NICs?
-Roy
January 27, 2012 at 8:01 am
pdanes (1/27/2012)
Thank you both, that confirms my thoughts on the paid version of SQL Server - little point in having all that RAM and processors if they're not getting used.But I'm confused on the disk setup. Wouldn't dismantling the RAID allow me to locate files on physically separate drives? That's one of the things that I've frequently seen recommended, but when all disks are in an array, there is no telling what's going where, in addition to them all sharing one I/O channel. Splitting the array seems to me would give each one a separate channel and should improve performance. Or am I missing something fundamental?
Channels are important but you have a VERY limited number of spindles in this configuration and about all you could do is go to mirroring..
With the limited disk hardware I don't think I would make any changes at this time.
YMMV...
CEWII
January 27, 2012 at 8:07 am
Roy Ernest (1/27/2012)
You will have to check the server Specs to see how many channels it has. Usually there is one Internal IO card that will have two channels. This will be connected to your Disk bay. That means you get two IO channels. One can be configured to your OS drive. The other you will have to share for your Data and log files.
Okay, I'll have a look, but I can't get to the machine until next week - it's in a locked room and the IT staff takes off early on Fridays.
Keep in mind that you will be sharing the TempDB with the User Database. It would be better to have the temp DB with its own IO path. (Especially since you said there will be reports run from the DB's)
That's what I'm trying to accomplish with all this. Even if there's only one channel available, wouldn't separate physical drives still be better?
Network drive will be just a share, right?
Yes, that's my intention.
That would not hurt much if you are not accessing those large files from that Server. if you do access those large files from the netwrok share for the DB server, then you could cause Network IO bottleneck.
People will be accessing them via a mapped shared drive letter, sometimes to download images, sometimes to look at them. Also, the databases will sometimes issue a DIR command to see how many images are associated with a particular record, and sometimes will also open the image file directly from the database, at the user's request. However, none of this will happen with any great frequency. Just thought of something, though - if I share the network drive directly, I can kick people off the database server entirely, since the only reason they need access is for these image files. The network drive would allow them to access the image files with zero impact on the server, and I could do things like restart the computer without disrupting such access. The server would then read the network drive just like any other user for info about image files.
Are you planning to have any Teaming done with your NICs?
Sorry, I have no idea what that means.:ermm:
January 27, 2012 at 8:27 am
pdanes (1/27/2012)
Are you planning to have any Teaming done with your NICs?
Sorry, I have no idea what that means.:ermm:
NIC Teaming is where at least a pair of NIC cards operate as a single interface, thereby increasing throughput. In other words, you have 2 x 1G NICS in your server, chances are you are only REALLY using one. If you team them then instead of have a 1G pipe you have a 2G pipe.
CEWII
January 27, 2012 at 8:54 am
Drives can fail very badly at the least expected time. At that point, you have lost your database. I would not compromise the safety of the Db for a tiny bit of performance boost. Therefore I would not remove the RAID configuration completely.
Elliot already answered regarding Teaming.
Just my 2 cents
-Roy
January 27, 2012 at 10:04 am
Elliott Whitlow (1/27/2012)
pdanes (1/27/2012)
Are you planning to have any Teaming done with your NICs?
Sorry, I have no idea what that means.:ermm:
NIC Teaming is where at least a pair of NIC cards operate as a single interface, thereby increasing throughput. In other words, you have 2 x 1G NICS in your server, chances are you are only REALLY using one. If you team them then instead of have a 1G pipe you have a 2G pipe.[/b]
Ah, thank you. I just looked up NIC Teaming and found some demos and stuff on it. I don't think I'll be doing anything like that - I don't expect throughput to be an issue any time soon. There will not be that many simultaneous users and the amount of data being shuttled around is not that large. More important is the internal performance of the database.
Roy Ernest (1/27/2012)
Drives can fail very badly at the least expected time. At that point, you have lost your database. I would not compromise the safety of the Db for a tiny bit of performance boost. Therefore I would not remove the RAID configuration completely.
You're right, they can, and this server already did have one drive go down, but thanks to the RAID array, nobody even noticed.
Thank you everyone for the insights.
Pete
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply