Handling New Versions

[Gene Wirchenko]

Dear SQLers:

Here is something that I have not seen addressed in anything I
have read. How does one handle changing versions?

It turns out that my opposite number and I installed different
versions of SQL Server 2008 Express. He has R2 and I have "" (R1?).
My system is a sacrificial goat so I can blow away SQL Server any time
I want. But what of a production environment?

Suppose that there is an existing installation of SSE. Call it
OLD. We wish to install a new version. Call that one NEW. It would
be irresponsible to blow away OLD, install NEW, and hope that it all
works. We would like to be able to test that NEW does work with our
existing databases before taking out OLD.

When we have completed this, how do we do a switchover so that
whatever was pointing to OLD is now pointing to NEW? Is it possible
to rename an instance? What questions should I be asking here?

Sincerely,

Gene Wirchenko

[Erland Sommarskog]

Post by Gene Wirchenko
It turns out that my opposite number and I installed different
versions of SQL Server 2008 Express. He has R2 and I have "" (R1?).
My system is a sacrificial goat so I can blow away SQL Server any time
I want. But what of a production environment?
Suppose that there is an existing installation of SSE. Call it
OLD. We wish to install a new version. Call that one NEW. It would
be irresponsible to blow away OLD, install NEW, and hope that it all
works. We would like to be able to test that NEW does work with our
existing databases before taking out OLD.
When we have completed this, how do we do a switchover so that
whatever was pointing to OLD is now pointing to NEW? Is it possible
to rename an instance? What questions should I be asking here?

In many cases when you move to a new version of SQL Server, you also take
the oppurtunity to new hardware, while everything sorts out automatically.
However, an application that uses Express Edition is not likely to have a
dedicated server - not even a virtual one.

One option is to upgrade the current instance, but as you say this is a
little iffy. You can always test that your application still works under
the new version by first installing a test instance and load backups of
the database to that environment. However, there may be performance issues
that does not exhibit until you run things in production, and if you
have the old instance around, you can easily compare performance. That is,
keeping the old instance is attractive.

However, there is no way to rename an instance, so that leaves you with
three options:
1) In-place upgrade.
2) Install new instance and change all connection strings. (*)
3) Install new instance on a new machine, and let the new machine inherit
the old name.


(*) One alternative is to peg the instance to a certain port, and then
use the port number in connection strings. When you install the instance
you change the port numbers, so that the new instance listens to the
same port number.