SQL Server 2012 : SQL Server Private Cloud - Upgrading SQL Server

9/27/2013 7:34:52 PM

One of the best practices when building out a consolidated SQL Server environment is to upgrade your database servers to the latest release. This will buy you a longer support cycle for the product. Versions like SQL Server 2000 are completely out of support, and dragging them into a new consolidated environment is not the best choice. However, there are circumstances that will prohibit you from upgrading. These include the vendor support for the applications that are the front end for databases not supporting a release more current than, say, SQL Server 2000. Also, internal business decisions to sunset the application could mean your choice is simply to migrate the physical SQL Server 2000 server to a virtual one and call it good enough.

Whenever you decide to upgrade the database servers, there are two important tools to be aware of in the upgrade process. These tools are the Microsoft SQL Server Upgrade Advisor (Advisor) and SQL Server Upgrade Assistant (Assistant).

The Advisor tool can be run from a desktop and points to the server instance you wish to upgrade. The tool goes through a check of potential issues you may encounter given the server you wish to upgrade. Examples of some of the potential issues include the use of deprecated statements like the *= and =* join syntax or extended stored procedures that were pulled from the product.

After the tool evaluates the server, it provides useful reports like the one shown in Figure 1.


Figure 1. SQL Server Upgrade Advisor’s instance report

If you sat for a moment and thought about situations where this tool may fail you could probably find quite a few. For example, if we were using encrypted stored procedures the tool could not obtain the text to parse to evaluate and so on. This tool is not meant to be the final check in whether or not you can upgrade; rather, it’s meant to give you a rough idea of the work involved in upgrading to the latest version. A more thorough tool used in application compatibility testing is the Assistant tool.

The SQL Server Upgrade Assistant is a joint development effort by Scalability Experts and Microsoft. The Assistant tool can be downloaded for free at

The Assistant tool more thoroughly tests for compatibility than the Advisor. This tool requires more resources, primarily time, to effectively use. The idea behind the tool is to capture a SQL trace of a live production workload (or a complete set of functional tests within a test environment) and replay this trace against a separate upgraded instance of SQL Server. Since the tool is capturing a SQL trace, it will capture every statement that goes against the source database. This allows the tool to trap errors, like using undocumented DBCC commands, which the Advisor tool will not capture. When the SQL trace is replayed against the new environment, a detailed line-by-line comparison will be presented, as shown in Figure 2.


Figure 2. SQL Server Upgrade Assistant’s Report Viewer

When developing a private cloud environment for SQL Server, as someone in the DBA role, your tasks will heavily center on the database inventory and consolidation piece. The magic of a private cloud has almost everything to do with the infrastructure—the flexibility of the operating system, the hypervisor, and disk subsystems. One of the other major pieces of resource pooling is taking the physical servers and virtualizing them. Products like Microsoft System Center Virtual Machine Manager (SCVMM) have this capability. SCVMM adds a ton more value and features on top of the Hyper-V platform and is a major component in architecting a private cloud infrastructure.

  •  SQL Server 2012 : SQL Server Private Cloud - Discovering SQL Server Sprawl
  •  SQL Server 2012 : Storage Systems (part 7) - Measuring Performance - Storage Performance Testing
  •  SQL Server 2012 : Storage Systems (part 6) - Measuring Performance - Sequential Disk Access, File Layout, Flash Storage
  •  SQL Server 2012 : Storage Systems (part 5) - Measuring Performance - Storage Performance Counters, Disk Drive Performance
  •  SQL Server 2012 : Storage Systems (part 4) - Storage Technology - Remote Data Replication, Windows Failover Clustering, SQL Server AlwaysOn Availability Groups
  •  SQL Server 2012 : Storage Systems (part 3) - Storage Technology - Storage Tiering, Data Replication
  •  SQL Server 2012 : Storage Systems (part 2) - Storage Technology - SQL Server and the Windows I/O Subsystem
  •  SQL Server 2012 : Storage Systems (part 1) - Storage Technology
  •  SQL Server 2008 : Policy-based management - Policies in action (part 2) - Creating a database properties policy
  •  SQL Server 2008 : Policy-based management - Policies in action (part 1) - Importing policies from file, Evaluating policies
    Video tutorials
    - How To Install Windows 8 On VMware Workstation 9

    - How To Install Windows 8

    - How To Install Windows Server 2012

    - How To Disable Windows 8 Metro UI

    - How To Change Account Picture In Windows 8

    - How To Unlock Administrator Account in Windows 8

    - How To Restart, Log Off And Shutdown Windows 8

    - How To Login To Skype Using A Microsoft Account

    - How To Enable Aero Glass Effect In Windows 8

    - How To Disable Windows Update in Windows 8

    - How To Disable Windows 8 Metro UI

    - How To Add Widgets To Windows 8 Lock Screen
    programming4us programming4us
    Top 10
    Free Mobile And Desktop Apps For Accessing Restricted Websites
    MASERATI QUATTROPORTE; DIESEL : Lure of Italian limos
    TOYOTA CAMRY 2; 2.5 : Camry now more comely
    KIA SORENTO 2.2CRDi : Fuel-sipping slugger
    How To Setup, Password Protect & Encrypt Wireless Internet Connection
    Emulate And Run iPad Apps On Windows, Mac OS X & Linux With iPadian
    Backup & Restore Game Progress From Any Game With SaveGameProgress
    Generate A Facebook Timeline Cover Using A Free App
    New App for Women ‘Remix’ Offers Fashion Advice & Style Tips
    SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th