Monday, April 18, 2011

How to upgrade to SQL Server 2008 R2 with minimal downtime: 1-2 minutes


Here is real life example,
how to upgrade SQL Server 2005
to SQL Server 2008 R2 with
less than 2 minutes downtime
and without any connection
string changes.
Sounds interesting?
Then keep reading.





Site configuration:
Active/Passive failover cluster with SQL Server 2005
Enterprise Edition.













Generally, this is the way:
Break Cluster, leave one node in the cluster(Node X),
install new cluster on second node Y,
setup SQL 2008 in the second node Y,
setup mirroring between node X and node Y,
do mirroring failover,switch between the clusters
(assign IP of original cluster to the new cluster),
install SQL 2008 on Node X, join Node X to the new cluster.

Below is high level work plan step by step,
I will not elaborate on system related aspects,
I will just mention the steps:

Let suppose Node X is Active.

1) Break the cluster, remove passive node Y from the cluster

2) If you have Domain Controller installed on Node X/Y machine,
    you will have to remove it, because SQL 2008 cannot be
    installed on Domain Controller machine, you will need to
    install the DC on other machine.
    Remove the Node Y from old domain, Join it to new domain.

3) Define new Cluster on Node Y with different name.

4) UnInstall SQL Server 2005,Install SQL Server 2008 R2 on Node Y
   (Cluster installation type)
    Create Logins with Same SIDs,Jobs and etc.

5) Setup Mirroring between Node X and Node Y, in async or sync
    mode, yep mirroring between SQL 2005 and 2008, it works fine

6) Do manual Mirroring failover (recommended  changing to
    synchronized mode before the failover). Remove the Mirroring.
    2-5 seconds downtime.

7) Switch the Clusters, assign the IP of old cluster to the new
    Cluster, change the DNS records.
    1 minute downtime.

8)  That`s all, the SQL is up and ready to get connections.
     As you can see, there is no need to change any connection
     strings in the applications.

9) Now complete work on Node X, UnInstall SQL 2005,
    install SQL 2008, join it to new Cluster.

Again, this is in high level.

Important notes:

a. When you finish the manual Mirroring failover, there is no way
    back to SQL Server 2005.

b. Check if you can allow some time period without Cluster protection.

Thanks for reading

6 comments:

  1. Why do you break the cluster (unless you change the cluster architecture)? You should just run upgrade wizard on each node.

    ReplyDelete
  2. Because in my scenario
    we do NOT do In-place Upgrade of
    existing SQL Server,

    It more likely 'Side by Side Upgrade'

    ReplyDelete
  3. What size databases were you working with and did you have multiple ones? I am looking to do the same thing but with detach/reattach but after reading this it would limit steps and decrease downtime. I am also curious about the shared storage. When you were done with everything I am assuming the DB files were on the same san storage but they had to be within different directories then the original locations!?

    ReplyDelete
  4. Database size: about 100 GB
    Multiple DBs: No,only 1 DB was mirrored
    Storage: The files path was different than the
    original due to the Mirroring.

    ReplyDelete
  5. Did you use different LUNS for this setup or were you able to use the same disk resource?

    ReplyDelete