Tag: install

How to Migrate from SQL 2005 to 2014

On April 12, 2016 Microsoft will no longer offer extended support for SQL Server 2005.  This means no more security updates or hotfixes.  However, the bigger reason to upgrade is the fantastic performance improvements offered in SQL 2014.  SQL Server 2014 has been shown to be 13X faster than 2005, and that is before taking advantage of the in-memory OLTP.  In addition there are features such as AlwaysOn availability groups, updateable columnstore indexes, T-SQL Intellisense and backups directly to the cloud, just to name a few.  For a more complete listing read What’s New in SQL Server 2014 since SQL Server 2005.

Preparation is key.  Microsoft has provided a number of tools to make migrating your databases easier.  You’ll want to use these tools to assess and plan your migration well ahead of time.  Support for Windows Server 2003 is also ending soon, which may impact your decision making.  Part of your assessment will be to ensure your environment is suitable for SQL Server 2014 before migrating your databases. SQL Server 2014, of course, has minimum hardware and software requirements.   Microsoft recommends a minimum of 4GB of memory, to be increased as database size increases, and processor speed of 2.0GHz or faster. The OS depends on which version of SQL Server you are installing. 

There are two ways to migrate from SQL Server 2005 to SQL Server 2014.  You can do a clean install and restore from backup, or you can do an in-place upgrade. The clean install may sound appealing from a Windows registry and system stability standpoint.  However it means restoring all of your databases, and any custom settings and properties you may have changed.  Over 10 years a lot of tweaks to settings can have been made, and it could be very time consuming to restore them all.  It may even mean purchasing another OS license.

You can do an in-place upgrade to SQL Server 2014 from any version as far back as SQL Server 2005.  You cannot do the in-place upgrade for versions prior to that.  You would need to upgrade a prior version to 2005 or 2008 and then upgrade again to 2014. You do need to consider the edition you are upgrading from, Standard, Developer, Enterprise, etc to determine the supported upgrade path.  With the SQL Server 2014 in-place upgrade you will be able to upgrade SSIS packages, SSAS and SSRS.

I highly recommend you install and run the Upgrade Advisor prior to doing the in-place upgrade.  This tool will analyze components of your previous version and identify issues you may need to address before upgrading to SQL Server 2014.  Upgrade Advisor can be installed remotely to analyze all supported components of SQL Server, except SSRS. The remote computer where you are installing Upgrade Advisor must meet the Upgrade Advisor prerequisites.  If you want to analyze SSRS you must install Upgrade Advisor on the report server itself.

You can install the Upgrade Advisor from the the SQL Server setup, or download it from Microsoft. 

image

In addition to the Upgrade Advisor, I found this great resource, Upgrading to SQL Server 2014: A Dozen Things to Check, written by Thomas LaRock. It is not specific to migrating from SQL Server 2005, but it has some great tips for preparing for a smooth SQL upgrade.

Once you have addressed any pre-installation issues, you are ready to perform the upgrade.  I won’t run through the step-by-step as there are many articles documenting this, such as this one.

There are many great reasons to upgrade your SQL Server, and with the end of extended support in sight, now seems like a good time.  Preparation for your upgrade is key, and Microsoft has provided lots of tools to make it easier.

Setting up Hyper-V and creating a VM

Until now I’ve been using a service to set up separate environments to connect to my clients.  The multitude of VPN software can mess with my machine so I prefer to keep them separate.  Lately I’ve had a lot of challenges with the service, and some down time along with it.  I’ve been wanting to set up Hyper-V on my machine and this was a good excuse.  Thank you to John White for getting me started and answering my myriad of questions.

Here are the steps to setting up Hyper-V on your machine and creating your first VM.

  • Enable Hyper-V on your machine. Enable virtualization in your BIOS if necessary
  • Ensure that you have an active Hyper-V switch that can connect to external networks. Pin to taskbar
  • Create a new VM in a separate (external disk?) location. Use a new disk, if possible, allocate all space
  • Install Windows 8.1 with Service Pack 1 (64 bit) using ISO file
  • Sign in to the VM using your Windows Live credentials.
  • Disable One Drive replication unless you really want it
  • Install Office and any other software you require

Enable Hyper-V on your machine

In order to set up a virtual machine on your computer you need to enable Hyper-V.  You can do that in the Control Panel->Programs->Programs and Features->Turn Windows features on or off.  You will want to check off both the Hyper-V Management Tools and Hyper-V Platform.

image

If Hyper-V platform is greyed out, you will need to enable virtualization in your BIOS settings.  I have blogged the steps here.  You can go back and enable Hyper-V on your machine if you have not already done so.

Ensure that you have an active Hyper-V switch that can connect to external networks. Pin to taskbar

I’ve set my Hyper-V switch up as a Wired Connection, as that is my set up is currently configured as a landline.  You can create a second switch for wireless in case you need this.  This is the network the VM will use.  Your main machine also becomes a VM.

Search for Hyper-V Manager and open it.  Pin it to your taskbar.

image

Click on your host machine and select Virtual Switch Manager from the Actions pane.

image

If a Virtual Switch doesn’t already exist you’ll want to set one up.  This is what mine looks like.

image

Create a new VM in a separate (external disk?) location. Use a new disk, if possible, allocate all space

Ideally you’ll get an external hard drive(s) for VMs.  Ideally you’ll need 128GB for the VM.  Ideally you’ll want your VM to have at least 4GB of RAM.  Set the start up memory to 4096, and use Dynamic Memory. 

These are the screens you will encounter when you set up a new VM.

image

***Note:  You MUST select Generation 1 if you want to be able to mount an ISO file to install the operating system

image

image

image

image

I will explain how to install the operating system next.  For now select "Install an Operating system later".

image

Install Windows 8.1 with Service Pack 1 (64 bit) using ISO file

Right click on your newly created VM and select Settings.

image

Select the IDE Controller for the DVD drive and mount the ISO file by selecting "Image file" and browsing for it.

image

Right click on the VM in the Virtual Machines window and select Start.  When you see the State change to Running, you can right click on the VM and connect to it.  The installation wizard will start from the DVD drive.  Follow the prompts and install Windows.

When prompted, log in to the VM using your Windows Live credentials.

Set up the VM as a new PC.

image

On the next screen disable One Drive replication, unless you really want it.

Now you can install Office and any other software you require.  You’re all set to go with your first VM!