November 6, 2007 at 5:27 pm
IANHWG (I am not a hardware guy). I'm a DBA/developer in the finance department of a health care company. We deal mostly with reporting on billing and physician compensation.
We currently have two SQL 2000 std edition database servers. Server #1 is a DW server; it gets loaded weekly from linked servers outside our little fiefdom. It takes 12 hours plus to load, slice, and dice our DW. Managment wants to make available a larger timeframe for the DW (more data), which will take longer to load, so we are shopping for a new server. I would like to turn server #1 into a combination dev and QA/acceptance/test server, with multiple instances of SQL Server 2005. Here are the specs for #1:
Xeon 4 processors, 2.7gHz
3.62GB memory
acres of disk
Can I safely put two instances of SS2005 enterprise on it? If you have two instances on a single box, do they charge you for 2X? Or, since it's not for production, can I save some money and put standard edition on it? I am mostly interested in indexed views in enterprise. My thinking is that I can dev and test on the std edition servers, then when it's time to roll out for production I can setup the indexed views. Does this sound like a plan? Or is there a better way to reuse the older hardware? Thanks for any ideas.
November 7, 2007 at 9:49 am
Hey,
There is absolutely no reason you couldn't configure 2 x SQL Server 2K5 instances on old server, but you would need to ensure memory is limited for each instance (e.g. 1.3GB\instance) and leave enough for O\S. In terms of charge, you are allowed to install upto 50 instances of SQL Server on a stand-alone server and 25 on a fail-over configuration. Please remember, indexed views are supported on all editions of SQL Server 2K5, but only the enterprise version allows query processor automatically use the indexed view and in standard edition you will need to use the NOEXPAND query hint to make use of an indexed view. In addition, SQL Server 2K5 EE has additional scalability features.
Thanks,
Phillip Cox
November 7, 2007 at 12:06 pm
Thanks Phillip. I currently have 4GB on that server (although it reports as 3.62GB; what's up with that?). How much does the OS require?
November 8, 2007 at 5:24 am
hey,
I would suspect some of the memory is being used for video.
Thanks,
Phillip
November 8, 2007 at 5:27 am
Hey again,
Sorry, I forgot to add, the O\S needs at least 256MB, but to keep paging to a minimum and taking into consideration the applications on server, 1GB would be ideal.
Thanks,
Phillip
November 8, 2007 at 9:46 am
Phillip Cox (11/8/2007)
hey,I would suspect some of the memory is being used for video.
Thanks,
Phillip
On another server that I know we have 3.0GB installed, in Control Panel... General tab it says 3.0GB. On this one, where we are supposed to have 4.0GB, it says 3.62GB, Physical Address Extension. Maybe that's it?
Or maybe our server monkeys bent some pins installing it. 😉
November 8, 2007 at 4:04 pm
Hi Dave,
The memory you see has nothing to do with PAE. PAE is only used to access >4GB memory on a 32-bit WinOS. As Phil has explained, one possible reason why some memory is "missing" could be due to display card, system driver, etc.
Ie. If you have a graphic card of 256MB on-board, this memory must be mapped within the first 4GB of address space. so, on a 4GB memory, part of that address space is already consumed by this graphic card memory mapping, hence, explains why you dont see the full 4GB memory on your server.
Back to your 3GB server. as you know, 32-bit OS (without PAE) have addresses up to only 4GB memory. since your other server only has 3GB memory, unless your graphic card has >1GB, your server should report the full 3GB physical memory.
I've cut and paste the following quote from M$ web site on the licensing
..
For Workgroup and Standard each virtual or physical operating environment containing a running instance of SQL Server requires a Server license. For enterprise edition, each physical operating environment containing a running instance of SQL server requires a Server license and no separate licenses are needed for SQL server instances running in virtual operating environments on the same machine.
..
You can download this doco from http://download.microsoft.com/download/e/c/a/ecafe5d1-b514-48ab-93eb-61377df9c5c2/SQLServer2005Licensingv1.1.doc
From the description of your server, you might want to consider putting in memory >4GB in order to support more instances. OS at all time, would need to have ~1GB to be effective. If you do decide to put in more memory, then the /PAE switch would need to be implemented in order for the OS to see memory beyond 4GB.
Simon
Simon Liew
Microsoft Certified Master: SQL Server 2008
November 8, 2007 at 5:30 pm
Thanks for the info, Simon. I have since read a bit on PAE. I don't know why we have it enabled, since we don't have >4GB of memory on that server. There are some other features (NUMA) that you need PAE for, but those don't look necessary to me. I checked that server and it has a ATI Rage XL video card. The HP mgmt tools report the card has 4046784KB memory. Maybe only some of that is mapped into memory?
November 8, 2007 at 7:39 pm
By the way (should a hardware guy appear and suggest this). Multi-instancing doesn't require extra licenses if you're NOT running multiple virtual instance of the server. Meaning - one OS + multiple SQL Server instances = one set of SQL licenses; two instances of OS, each running their own instance of SQL Server = 2 sets of SQL licenses for the virtual processors or servers (or Enterprise edition licensed once for the physical processors).
From what you mentioned, that didn't sound to be in your plans, but it might come up at some point...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 8, 2007 at 7:41 pm
Also - the cheapest way to license a DEV/QA environment is to license each of the DEV users with SQL Server Develoiper EDITION (45$/seat or so). No server component required (just not allowed to run it in prod.).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 9, 2007 at 12:01 am
Hi Dave,
yes, apparently PAE enables several advanced system and processor features such as DEP, hot-add mem, NUMA, etc. This switch only applies to 32-bit OS.
I believe the HP mgmt tool would report a more accurate total physical memory as compared to the task manager. alternatively, command prompt > winmsd would also give the full server memory. this behaviour is just due to the task manager design where it doesnt show the "reserved" address mapping on the server. but because its so conveniently used, it just makes some people wondering why their server is not full 4GB, but in reality, its there.
some other catch, had you have 16GB on the server, task manager would only show 4GB max regardless without the PAE switch, and so will winmsd. The only way to determine your total server memory (16GB) would be via HP mgmt tool.
I've had an issue with enabling PAE, AWE (SQL2K) on a SMP windows 2003 before. Apparently, this issue occur because PAE wasnt very stable with AWE enabled on an unpatched server. Issue was resolved after SP1 patch.
http://support.microsoft.com/kb/838765/en-us
Sometimes, by enabling PAE, you might see a bit more memory from the taks manager. Ie. Without PAE, task mgr shows 3.6GB memory, with PAE it shows 3.8GB ... So, by the look of it, probably the person who added the switch thought more memory might've been gained? But, realistically, this switch is redundant other than modifying the behaviour of task manager because sql would only use up to 3GB (SQL = 2.7GB and mem-to-leave = ~300MB) assuming only 1 sql instance on the server.
Simon
Simon Liew
Microsoft Certified Master: SQL Server 2008
November 9, 2007 at 10:32 am
Matt, that's an interesting idea. The dev edition page says that "may be installed and used by one user to design, develop, test, and demonstrate your programs." So if I install two instances of SQL Server dev edition on my server, how can I have ten or so end users hitting one instance from my web sites for acceptance testing purposes? Do I still buy CALs? And what about when I am slaving away in mgmt studio with ten query windows open? Thanks.
November 9, 2007 at 10:54 am
You buy dev "licenses" as CAL's. 10 developers = 10 developer editions = 10 *45$. If you have ten testers, throw 10 more in. It's still less than half of the per user CAL, and no server component to pay for.
Just be sure to keep this out of production in any way.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 9, 2007 at 11:14 am
Such a deal! Thanks for the explanation, Matt.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply