When upgrading to another version of SQL Server, you usually choose one of the following methods…
- In-Place upgrade: you install the new version of SQL server on the top of an existing installation
- Side-by-side upgrade: You prepare a new environment (H/W) and move all databases to this new environment and connect all clients to it. Shut-down the old one.
You will usually need to minimize the down-time. In general, Install the software pre-requisites of the new version prior to performing the actual upgrade. With SQL 2012, we have witnessed some updates that might take 30-60 minutes to complete.
Now, decide how your data files will be upgraded and moved, this greatly depends on your upgrade approach…
- If you are doing side-by-side upgrade, you might have large amount of data to transfer since copying data over network is really time consuming. To avoid that, think about utilizing existing SAN solutions to create mirrored disks. Once youAttach the mirrors to the new environment. This is good approach if you have 2+ TB. In more details…
- Configure your SAN storage solution to create mirrored copy of your data disks
- Once you decide to move to the new environment, breake the disk mirrors
- Attach the mirrored disks to the new environment. It should contain all your data files
- You can script the previous 2 steps for more automated and faster move
- Attach the data files and databases to the new SQL Server
- If you are doing in-place upgrade for a cluster, utilize the rolling upgrade technique to avoid downtime on your SQL Service…
- Identify one node that will be upgraded last. Make this node the only active node in the cluster. This means: from the cluster manager, move all SQL Service groups to this node.
- Ensure that the first node that you intent to upgrade completely passive. This means that: from the cluster manager, move SQL Server services to any other node (or the active node that you identified earlier). This has the impact that your data files will not be upgraded after you upgrade that node and will still have the old SQL Server format. This is because the new SQL Server did not catch the data files yet and you can still rollback your upgrade.
- Do the same for all remaining nodes. Just notice that your SQL Services is still up and you do not have any downtime yet.
- Once you are done with all nodes except one (The only active node currently), prepare for the down time.
- From the cluster manager, Move SQL Services groups to any other node. During this move, SQL will start a little bit slower than usual as the new SQL Server will have to register the upgrade and modify the data files formats.