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

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

Edit

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