Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210

    Unanswered: SQL Server above 4GB RAM

    It's upgrade time. I've got $25 - $40K to spend for new server, disk array, and software. Objective - overcome the SQL wall we're encountering in prep for a new rollout that's expected to double our DB processing demands.

    I don't know much about 64 bit, if I should consider using it, and if it's needed to break the 4GB barrier.

    Microsoft's explanation of "SQL Versions" here and the various links on it lead me to believe it's not all that tried-and-true. Should I be nervous about going with SQL Server 64 bit?

    2nd (dumb?) question; do I even need a 64 bit version of Windows OS / SQL in order to break the 4GB barrier? I think I'm talking about AWE Memory , but I don't know much about that either. Is it common to use this?

    Note: Currently we run SQL Server 2000 Standard on Windows 2000 Standard. Database is 2GB. Server is 4 year old IBM x235 2xZeon 2.0GHz dual processors (not dual core), 3GB RAM, mirrored C/D drive and RAID 5 E/F.

    Any advice for upgrade path and leaving my expansion options open would be appreciated.

    Sorry to ramble. To consolidate:

    1. Is SQL 64 bit reliable?

    2. Is SQL 64 bit necessary to triple my load?

    3. Is AWE memory the norm and is IT a reliable upgrade path?

    4. If so; what's my cheapest path for that in terms of the underlying OS should I get SQL 2005 version?

    5. Side note: Does SQL Server 2005 Standard's 4 processor limitation count quad-core as 4?
    Last edited by vich; 08-02-07 at 23:38.

  2. #2
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    We have about the same Issue but the main thing to think about when choosing a solution is what kind of aplication are u runnning and how much data are we processing......64 bit machine with good hardware will defenitly improve perfomance but again it all depends on the kind of application and data.

  3. #3
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    For your last note quad core will be counted as 4 processors ...it woulkd cost a mint in liscences if it was they other way around

  4. #4
    Join Date
    Jan 2004
    Location
    Montreal, Canada
    Posts
    151
    justifying 1 quad core = 1 processor

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    To answer your questions as posted:

    Both versions of SQL-64 are rock solid reliable. I prefer the Itanium version, but the x64 version is still light-years ahead of the 32 bit version in terms of performance, especially for large configurations.

    Depending on your existing load, you may or may not need 64 bit SQL Server to handle three times the existing load. It will probably be easier to implement, and certainly easier to grow a 64 bit implementation.

    AWE is the present "state of the industry" for 32 bit servers. It works well up to a point, then the overhead associated with managment starts to outweigh the benefit you get from more RAM. Keep in mind that AWE is a paging architecture, not a flat architecture... AWE gives you access to more memory, but only "one chunk at a time" instead of as a uniform address space. AWE can be used for buffers and for bulky objects, but it can't be used for procedure cache, etc.

    I don't understand point #4 well enough to respond to it.

    The last time I reviewed Microsoft's licensing agreements, dual core and quad core processors count as one CPU. There is no expectation that this will change in the foreseable future, but they always qualify observations like that with the caveat that corporate/legal can change the definition of pi if it suits them, without advance notice of any kind.

    -PatP

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    One added note, if you have "add on" software that you're accustomed to using in the 32 bit world, it probably won't work in the 64 bit world. If you use special SAN or NAS drivers, non-Microsoft backup tools (especially ones like LightSpeed) or other low level tools be careful to consult with both the vendor and Microsoft before you include those tools in your migration plan. I've seen more than a few upgrades go down the drain because of assumptions about portability of "add on" tools.

    -PatP

  7. #7
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by Pat Phelan
    One added note, if you have "add on" software that you're accustomed to using in the 32 bit world, it probably won't work in the 64 bit world. If you use special SAN or NAS drivers, non-Microsoft backup tools (especially ones like LightSpeed) or other low level tools be careful to consult with both the vendor and Microsoft before you include those tools in your migration plan. I've seen more than a few upgrades go down the drain because of assumptions about portability of "add on" tools.

    -PatP
    Thank you Pat for all the info and for your added note about 32 bit side-software.

    I do have Developer copies of the 64 bit stuff so I may just use them for testing. However; I most likely will be purchasing the entire solution new so I could just run a poll with saying something like:

    "If you currently have a dedicated SQL Server 64bit, what hardware/software configuration do YOU have that you would be so kind as to let me copycat"? Of course; my pockets aren't as deep as you guys in the big league.

    Here's what I plan on running so if anyone sees a conflict, I'd very much appreciate a heads up:

    1. SQL Server 2000 Standard 64 bit (we may buy a license for 2005 Standard if the upgrade isn't too consuming ... I have a development copy for testing).

    2. Norton Antivirus 64 bit version (maybe ... or no antivirus)

    3. Executive Software Diskkeeper Standard Edition version 8.0.478 (not as critical provided we can find some defrag software for 64 bit)

    4. Windows Server 2003 Standard 64 bit

    5. Symantec Backup Exec Remote Agent ver 10.1.5629 (version that has added Win 2003 Server x64 support).

    6. Various DOS commands for networking and reporting.

    7. Terminal Services.

    8. Hardware will likely be IBM x Series server, single Intel quad core Zeon (upgradable to 2), IBM DS3400 SAN , probably 8GB RAM. OS installed by IBM. (note: Win 2K3 Std x64 listed on DS3400 support list)

    9. Several local servers and an Access 2003 app (runs on desktops) will be attaching via ODBC or OLEDB. I'm *assuming* that Access won't have issues since it uses a standard database layer.


    Hillcat: I posted this quote on another thread, but here's a repeat as an FYI. I got this from my Microsoft rep at Tech Depot. I'd really love to hear if you've experienced anything different (like - some kind of software enforcement by SQL not allowing you to exceed 4 even though they're really just cores). Thanks!
    SQL Server Multicore Licensing Policy
    Published: August 1, 2005

    Multicore processors, which consist of multiple processing execution
    units or "cores" on one chip, promise to boost computing power, allowing
    servers, workstations, and PCs to perform more functions simultaneously.
    By the end of 2006, Intel expects more than 80 percent of its server
    products to be shipping with multicore technology. Because most
    server-side software is licensed "per processor," it has caused
    confusion among some software vendors regarding whether to charge their
    customers "per processor" or "per core."

    Microsoft has been driving thought leadership in this area by charging
    the same amount per processor, regardless of how many cores are in the
    processor. Microsoft was the first database vendor to make this
    announcement, in October of 2004, and continues to be the only vendor to
    date that has taken this position. This strategy is based on the belief
    that multicore processors are a natural extension of Moore's Law (that
    the number of transistors on a chip doubles about every one to two
    years), and that the benefits should be passed on directly to customers.

    For example, if you are using SQL Server Enterprise Edition on a
    four-processor server with dual-core processors, using all eight cores
    (two cores x four processors), you will require only four processor
    licenses. That can mean substantial savings for customers of Microsoft
    compared to its competitors.
    Last edited by vich; 08-03-07 at 21:06.

  8. #8
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Scratch all that TMI.

    Here's the question. Apart from other server software, is the upgrade from SQL Server 2000 Standard to SQL Server 2000 Standard x64 a no brainer?

    IE: Are there SQL aspects that operate differently or should I expect (after some testing) that I can just restore my database and not worry too much about the actual Stored Procedures, Views, Triggers, User Functions, etc to simply work as before?

    Thanks!

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm going to go way out on a limb here...

    Microsoft offers some great assistance for migrating from 32 to 64 bit servers, and much of that assistance is free. They offer virtual machines for you to upload your database onto and test to your heart's content for short periods (I think the limit is normally 90 days, but I'm fuzzy on the details). They will often offer consulting services at reduced rates (sometimes free) for these upgrades too.

    These are great resources. Take advantage of them! If you have trouble reaching one of Microsoft's sales creatures directly, let me know and I'll figure out the politically correct way to sic one or more of them on you!

    -PatP

  10. #10
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by Pat Phelan
    I'm going to go way out on a limb here...

    Microsoft offers some great assistance for migrating from 32 to 64 bit servers, and much of that assistance is free. They offer virtual machines for you to upload your database onto and test to your heart's content for short periods (I think the limit is normally 90 days, but I'm fuzzy on the details). They will often offer consulting services at reduced rates (sometimes free) for these upgrades too.

    These are great resources. Take advantage of them! If you have trouble reaching one of Microsoft's sales creatures directly, let me know and I'll figure out the politically correct way to sic one or more of them on you!

    -PatP
    Thank you Pat!

    As a first toe in the water, and since it's so easy, I'm just going to give it a whirl on my home computer. I run an IBM 2-Core Win XP Pro x64 and expect I can get demo SQL versions online, so it's an afternoon's work. I suppose I could even dual-boot (install) into Win 2003 Server.

    If I get problems or if it seems reasonable to proceed, I'll be calling on those resources. The fact that they have so many resources at all sort of answers the "no brainer" aspect of my question.

    SQL 2000 Std has been pretty solid for me thus far (it does hiccop once in a while) and I'd not want to do anything to break it. But knowing MS support is so readily there is a comfort. Sometimes I feel like I'm up the creak alone with a wobbly paddle and weak arms. So far they've come through but venturing into new waters ... well, I just liked hearing 64 bit versions aren't the bleeding edge.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You might seriously consider one of the Virtual PC demo packages that Microsoft offers. Virtual PC is what the name implies, it allows you to run a "virtual" PC within an application window on your present machine. Microsoft has a number of "pre-cooked, ready to eat" virtual machines that allow you to simply download a pair of files (a VHD or Virtual Hard Disk, and an XML configuration file) that allow you to logically "host" one of Microsoft's well understood, well supported configurations.

    Virtual PC is a free and easy way to get things up and running with very little fuss, muss, or bother.

    Microsoft also offers another option where you can load your files onto a virtual machine that they host, which allows you to experiement with much larger system configurations and much better load testing tools than the average PC will support. It also allows you to ask their engineers for design comments and or assistance, which can sometimes turn up solutions you would never have thought of that offer more power for less money (something that I'm quite fond of, and usually shocked to see a vendor offer at any price, much less for little or no charge).

    -PatP

  12. #12
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by Pat Phelan
    You might seriously consider one of the Virtual PC demo packages that Microsoft offers. Virtual PC is what the name implies, it allows you to run a "virtual" PC within an application window on your present machine. Microsoft has a number of "pre-cooked, ready to eat" virtual machines that allow you to simply download a pair of files (a VHD or Virtual Hard Disk, and an XML configuration file) that allow you to logically "host" one of Microsoft's well understood, well supported configurations.

    Virtual PC is a free and easy way to get things up and running with very little fuss, muss, or bother.

    Microsoft also offers another option where you can load your files onto a virtual machine that they host, which allows you to experiement with much larger system configurations and much better load testing tools than the average PC will support. It also allows you to ask their engineers for design comments and or assistance, which can sometimes turn up solutions you would never have thought of that offer more power for less money (something that I'm quite fond of, and usually shocked to see a vendor offer at any price, much less for little or no charge).

    -PatP
    Thank you. Nice service - both for load testing and consultation. The latter is my greater need. See post 8 here.

    In other words - we will probably upgrade, and when we do I'm still in a quandary about why anyone wouldn't go with the x64 version since the price is about the same, but after pouring through these forums and links its becoming clear that I could probably double or triple my performance and seriously reduce the load by taking more traditional measures like incremental hardware upgrades and applying simple performance measures like what you guys preach all day long.

    So; I've come to see the light (oh Glory be). That link has a pretty good beginning hit list.

    The 64 bit version is more of a curiosity now as far as my immediate needs go. If those measures don't solve the problems (and I think they should for a good year or so) then I'll once again need to know "why not x64".

    OK - The top item on my list is bad indexing - one other is bad SQL memory allocations - surely I've got other gross misconfiguration issues. So, simply monitoring DB and OS activity to find the hot spots and effects of tuning. I'm wondering if Quest or someone similar has a "Performance God" program of some kind that magically finds these areas, and where I could log measures taken an it'll report on resulting increase/decrease in performance. So; <LAUGH> sort of a DBA Expert System <\LAUGH> .... <no really \laughing>.

    OK, I know there's five 3 inch books and 10 years of hard experience not easily replaced, but there must be some pretty no-brainer mistakes or hot spots that some super slick analysis tool could immediately find. Plus a "change tracker" that will detect that last week SQL averaged 50 page faults/hr and after I reduced Max SQL Memory or added physical memory it dropped to 30/hr. Sort of track key changes to system config and key indicators to performance, with a capability for me to enter log items such as "added report". Tracking before/after versions of queries, triggers, SPs, etc and how they fare would be nice. I could see justifying about $4000 for such a tool. Am I dreaming? Is there something that comes close or do you guys do it by brute manual record-keeping? Surely; a tool could auto-detect significant system changes too (why not, they matter), so whether a Virus Checker was de-installed, Disk Drives were upgraded, free space cleaned, defrags or re-indexing run, etc. Pretty much an intelligent DBA's Auditor and Analysis tool packaged for pseudo (read unskilled) DBAs.

    Thanks
    Last edited by vich; 08-06-07 at 15:13.

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    While not exactly what you want as it pops out of the box, MOM comes really close.

    MOM provides you with monitoring tools that will make you drool, and a framework to manage those tools. MOM is emphatically NOT a 100% ready to go monitoring solution, but I don't think such a thing is even possible. MOM is a framework that provides about 80% of what you need out of the box, it is supported by many vendors with MOM Agents for their products (folks like EMC, Seagate, HP, etc), and it includes tools to make your own agents if you need things that no one else produces yet.

    This is not a product that you just plop down and forget, it is one that requires constant adjustment as your computing needs change (for example, a box that is critical at year end might only be interesting at mid year, or a box that was amusing in the staging environment becomes wildly exciting when it moves to production).

    You need to take care of MOM, but she'll help you ride herd on the rest of the boxes and let you sleep at night!

    -PatP

  14. #14
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    Vich,
    Why not upgrade to SQL Server 2005 64-bit? SQL Server 2000 support will be stopped next year. If you are doing all these upgrades, why not just upgrade to SQL Server 2005?

    -Reghardt

  15. #15
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by Reghardt
    Vich,
    Why not upgrade to SQL Server 2005 64-bit? SQL Server 2000 support will be stopped next year. If you are doing all these upgrades, why not just upgrade to SQL Server 2005?

    -Reghardt
    Good point.

    As stated; I'm discovering I don't think an upgrade will be needed to solve the immediate problems.

    So yeah; I'll definatly want to go 64 bit unless it'll become a headache unto itself, next year though.

    I'm 5 months behind on delivering a major software addition but it'll finally go in next month. Testing shows it'll increase our load a lot (maybe double or more), and the system has increasingly been dragging even without it (sometimes even hanging). So; lacking the luxury of time, I was looking for the quickest solution. That obviously precludes upgrades that become projects unto themselves.

    Hearing that SQL 2005 has a SQL 2000 compatibility switch was encouraging. Further; hearing that x64 is supposed to be just a restore is also encouraging. If it's really that easy, then it fits my problem (needing immediate relief) if simpler measures aren't enough.

    Meantime; I like the safer and more concervative solution of just fixing what I've got, for now. If I were experienced at doing these upgrades; it would probably be another story but I just lack complete confidence in pulling that off WHILE doing normal site support AND wrapping up the final programming and user implementation issues with a major SW install.

    So; Tonight I moved the main log file to the OS's physical mirrored volume (db is still on the RAID 5 volume). Earlier today I marked .trn, .mdf, and .ldf to be skipped by Symanted Antivirus. Yesterday I reduced the MAX memory + ordered more physical memory to bump it form 3GB to 4GB (believing that OS was starved). Tomorrow I'll order 15K RPM drives to replace the 10K RPM. Next week; I'll have the web server configured on a separate server (awaiting more memory for that too). All about $3000 and minimal effort. $3K isn't a bad price tag to take us into next year.

    Upgrading has risks so when there's more breathing room to take them, I will go x64 if that's not a large additional risk (cost seems the same) - whatever version of SQL seem appropriate (Standard or EE).

    Thanks!
    Last edited by vich; 08-07-07 at 01:38.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •