SQL Server Performance

Practical Reasons for upgrading from SQL Server 2005 to 2008?

Discussion in 'SQL Server 2008 General DBA Questions' started by kopajb@gmail.com, Jul 12, 2009.

  1. kopajb@gmail.com New Member

    I'm an intermediate Production DBA and busy writing a business case to upgrade from SQL Server 2005 to 2008, but struggle to find any 2008 features that makes upgrading to SQL Server 2008 a MUST. Yes, there are many "nice to have" features but none of them are MUSTS with the exception of (1)Maintaining Mainstream Support. I want to know about features that can justify the business costs and effort(time) for upgrading to 2008.

    I'm more interested in PRACTICAL features that can improve (1)DBA productivity and efficiency (manageability), (2) environment security, (3) increased performance , (4)increased availability, (5)standardization and (6)documentation. Can you help to list some of the features which you think are MUSTS for now. The details about how each feature works and how to practically implement it will follow at a later stage. Your assistance will be highly appreciated.
  2. moh_hassan20 New Member

    Welcome to the forum
    sql 2008 are rich with many new 50+ features which satisfy dba, developer , security and business users. I list the top cool features (from my point of view[;)]):
    • Change Data Capture (CDC): makes database auditing easier and off-line connections for smart clients applications.
    • FILESTREAM Data: Allow large binary data to be stored directly in an NTFS file system and preserving an integral part of the database and maintaining transactional consistency. Enable the scale-out of large binary data traditionally managed by the database to be stored outside the database on more cost-effective storage.
    • Backup Compression: Less storage is required to keep backups online, and backups run significantly faster since less disk I/O is required.
    • Spatial Data Types: Implement GIS data type.
    • Data Compression: Reduce the storage requirements for data and provide performance improvements for large I/O workloads.
    • Policy-Based Management: It could possibly save DBAs a huge amount of time admin.
    • Performance Data Collection: reduce Performance tuning and troubleshooting which is time-consuming tasks for DBA. A new centralized data repository for storing performance data, and new tools forreporting and monitoring.
    • Resource Governor
    • Performance Data Collection
    • Transparent Data Encryption: Enabling encryption of the database, data files, or log files, including Search encrypted data.
    do you need more [;)]

  3. kopajb@gmail.com New Member

    WoW, thank you so much for your response, Moh_hassan20! You've actually listed all the features I had included with the exception of FILESTREAM Data and Spacial Data Types.
    The question that still remain is, of the listed features, are there no cheaper altenatives? In other words, can't we implement similar features without upgrading to 2008? I mean a single CPU license for SQL Server 2008 Enterprise is $24999 while Standard is $4999. If I have 2 Enterprise servers (One for ERP, another for CRM consolidated with other critical business apps) with 4 Core CPUs, I'll end up spending (2 Servers *4 CPU * $24999 ENT license fee)= $199 992 just for the two servers.
    What about my other Standard Edition servers that hosts other business applications (20 servers with 2 CPUs each) , i.e. (20 Servers * 2 CPUs * $4999 STD license fee) = $199 960. Now add ENT and STD ($199 992+$199 960)=$399 952 (i.e. approximately Half a Million $ just for SQL Server licensing)! I know that you only pay per single physical CPU irrespective of how many cores it may have.
    Are there other cheaper options that you know of? The one that I have considered so far is Red-Gate SQL Toolbelt which will cost $8995 per 10 Servers. Now since I have 22 Servers in my shop, it will amout to (($8995 * 2) + ( 2 * $1595 single licenses) )= $21 188 for all 22 Servers (I'll spend much less since I should be able to negotiate a better rate for the 22 servers BULK!). This means I'll only be spending about 5% of the SQL 2008 upgrade but still getting similar benefits (although not all!). I know that I'm comparing APPLES with ORANGES but do you get the main point?
    Are there other third pary tools, sql scripts (both TSQL, SMO and WMI), that you know of that can mitigate the SQL 2005 feature limitations with a fraction of the cost and effort(time!)?
    Also, please correct me where I might have miscalculated the costs!
    I'm looking forward to your response...[:D]
  4. moh_hassan20 New Member

    Oh, Many math [:D]
    i will discusse your numbers in different directions:
    License Cost
    [quote user="kopajb@gmail.com"]I'll end up spending (2 Servers *4 CPU * $24999 ENT license fee)= $199 992 just for the two servers[/quote]
    The license per processor is per pysical socket indepent on the number of core, example a quad core is considered as one license not 4.
    so 2 Servers *1CPU * $24999 ENT license fee)= ....
    generally , we go to license per CPU when we have unlimited number of users , why not to recompute your license per server + CAL (indepedent on number of CPU ) and select the optimum cost.
    Server Consildation:
    you can go to the recent trend for server consolidation for cost effective. Consolidation may be in the form of:
    • Multiple databases consolidated into one single database .
    • Multiple databases consolidated to a SQL Server 2008 instance
    • Multiple SQL Server instances per physical server
    For test servers , you can go to virtualization.
    Mix An Match
    You need not to upgrade all your servers , you can continue with sql 2005 , and upgrade some servers based on the needs of the application
    Build a plan, for example, for the next three years and upgrade gradually.
    Evaluate New features against cost
    build a matrix for your current servers (vertical) , and feature for sql 2008 (horizontally) and mark with score the feature needed and prioritize the upgradebased on the sum of score.
    Think in hardware upgrade
    Take into account the infra structure for optimum performance needed , so think in hardware upgrade and synchronize upgrade to sql 2008 to the next server hardware upgrade
    i am waiting your new $ [:D]numbers
  5. RickNZ New Member

    Another thing to consider is that you may not need SQL Enterprise everywhere. Are there places in your shop where you can use SQL Standard or Workgroup? Or even SQL Express, for things like small read-only databases?
    Also, do you really need all of those CPU sockets? SQL performance is very often dictated much more by its I/O subsystem than by the number of CPUs. You might consider pulling out a few sockets and perhaps upgrading the ones that remain with higher speed processors with larger caches. You might also add more RAM or more disk channels to your servers, depending on where the bottlenecks are. It's pretty rare that I see a quad-socket quad-core server that's anywhere close to 100% CPU bound.
    Server consolidation, as someone else suggested, is also an excellent idea. Instead of 5 servers that are each 15% busy, consolidate into one server that's 75% busy.
  6. satya Moderator

    Welcome to the forums.
    As you have had a chance to get a list of features from Hassan I would like to say few from your questions:
    • DBA productivity and efficiency (manageability) - for sure SQL Server Management Studio (SSMS) has been improvised from 2005 to 2008, which gives you flavour of dashboard reports for the initial status of the servers. What more manageability you need in terms of list of endless options with Activity Monitor and other features. I have been using SQL Server since 4.2 version and version-by-version this core of DBA duties has been addressed now.
    • Environment security - this is purely dependant upon how the enterprise security policies are defined, still SQL Server comes handy to apply all of those features using Policy Based Framework where you can control the SQL arena as per the policies. Not to mention about the security layer of http://technet.microsoft.com/en-us/magazine/2008.04.sqlsecurity.aspx?pr=blog overview of security features of 2008 and http://www.microsoft.com/sqlserver/2008/en/us/wp-sql-2008-security.aspx a good one to talk about.
    • Increased performance - this is a myth that every SQL user thinks that performance can be achieved by upgrading to newer versions, I always say NO. There is a pre-requisite of how your database has been normalised and designed by the way how queries are extracting. I'm happy that MS invested lots of effort to improve our data warehousing performance, and the performance of our BI services. SSIS, SSRS, and SSAS. Always there is a condition that all of that you cannot achieve unless you spend good amount of $ums in terms of taking advantages of edition.
    • Increased availability - there is a sure enhancement in availability with the added features to clustering and fine tuning the aspects of database mirroring as compared to 2005 version. I would say the better availability feature of Cluster has been redesigned as per Windows 2008 features, again there is a catch here about using Enterprise Edition as you cannot achieve them with lesser editions.
    • Standardization - What kind of standardization you are looking here? Sorry I'm not clear on your own idea of standards.
    • Documentation - Books online aka BOL is my favourite and has been redesigned at best to get you first hand information, http://sqlserver-qa.net/blogs/tools/archive/tags/BOL/default.aspx I always refer to BOL whenever I'm not sure on any information about SQL Server.
    Hope this helps.

Share This Page