Best method?-Move Vault 3.5.1 on SQL2k to new SQL2k5 server

[tpait]

We currently have Vault 3.5.1 installed on our old Windows SBS server which runs SQL 2000. We now have a new SQL2005 server and would like to move the Vault app and database onto the new server.

What is the best method to do this? I was hoping we can just install the Vault app on the SQL2005 server, switch off access to the Vault app on the old server, backup the database using the Vault Backup tool, restore this backup copy onto the new server, and then switch all the clients to point to the new server IP? Or is it more complicated than this eg because we are changing SQL versions?

At some point we may also like to upgrade to the new version... is it better to install the new version on the new server first then transfer the data or install the old version, transfer the data, then upgrade to the new version?

Our priorities are to keep things safe and available - ie if something goes wrong with the new install, we can always fall back to using the older server until it is fixed, and also want to minimise down time in the transition.

Thanks in advance!

[lbauer]

Actually you would restore the database on the new SQL Server machine first. Details here:
http://support.sourcegear.com/viewtopic.php?t=228

You might want to try the upgrade first on a non-production machine. Then you can upgrade before you move the database or after, it really doesn't make much difference.

[Tri]

Hi,

We have gone through the same scenario to upgrade Vaut Server 3.53 SQL2K -> 4.12 on SQl Server 2005. Physical Server is Windows 2003 Server standard 32 bits. We made several simulations on a test server before going Production. It worked quite consistently. Here is what we dit:

1- Shutdown Vault Service
2- Full Backup Vault 3.53 SQL2K database (sgvault)
3- Uninstall Vault Server 3.53 (you can select not to keep the Vault DB, we prefer to keep it and drop the DB manually using SQL2K Query Analyzer)
4- Uninstall SQL2K
5- Install SQL Server 2005 & all patches, possibly recommended patches for Windows 2003 as well.
6- Restore sgvault backup made in step2 (using SQL Server 2005 Management Studio)
7- Install Vault 4.12 and confirm reusing existing DB.

So basically all the DB restore / backup are made outside of Vault Server installer. We prefer to do these operations using SQL Server tools to have better feed back. For example, by running a DBCC CHECKDB after the DB is restored.

Hope that helps.

[tpait]

OK thanks both for your help.

I have one more question though before I go ahead with this as we haven't really got a test machine to try it out. If we were to go through this routine (backup old database, restore to sgvault db on the new server, install old version of vault, then upgrade to new version of Vault) then take a little time to test that everything had worked OK while everyone else was still using the old server, could we then at some point decide it was successful, take another backup from the old server and restore to the new server so that our data is up to date and then update everyone's client to talk to the new server? Or does the new Vault install make modifications to the existing data that would be overwritten if we restored a newer version of the database from the old server?

Or after the testing could I just go through uninstalling Vault on the new server, restore the updated database and then reinstall Vault again?

[Tri]

When we made the upgrade, we did it Saturday midnight, Vault Server 3.53 was shut down so the sgvault DB was the latest. During the Vault Server update to 4.12, no one could work on Vault so we didn;t have any live data to catch up.

Vault Server 4.12 installer makes a conversion 3.5x to 4.1x. When it is done, the Vault sgvault DB is in 4.x format. If you restore a fresher 3.5x DB on top of it. Basically overwriting the sgvault 4.x by a sgvault 3.5x, without rerunning the Vault Server 4.12 installer, you WILL get corruption because the sgmaster DB (new in v4) is no longer in sync with the sgvault v.3.5x you have just restored.

When we repeated a simulation of 3.5x -> 4.12 update. Each time we dropped entirely the sgvault, sgmaster DBs of Vault 4.12. Uninstalled Vault Server 4.12, Restored a newer sgvault 3.53 database, and finally rerun the Vault Server 4.12 installer again.

I don't know if Vault Server 4.12 installer can handle the conversion if we skipped the part of dropping the DBs from V4. But as dropping DB + restoring DB take the same duration, so we preferred to make sure the Vault Server 4.12 update process starts with no trace of data / files left over from v4.

[lbauer]

When we repeated a simulation of 3.5x -> 4.12 update. Each time we dropped entirely the sgvault, sgmaster DBs of Vault 4.12. Uninstalled Vault Server 4.12, Restored a newer sgvault 3.53 database, and finally rerun the Vault Server 4.12 installer again.

You are correct that you cannot restore a 3.5.3 database to an existing 4.1.2 Vault installation. It won't work. If you wanted your latest 3.5.3 database for production, you would uninstall your 4.1.2 test installation, including dropping the 4.1.2 sgvault and sgmaster dbs, restore the 3.5.3 database, then reinstall 4.1.2. In nearly all upgrades, Vault installer upgrades the database(s) to be compatible with the new version of Vault

[tpait]

I managed to do some testing by loading VMWare server and created a virtual machine with W2k3 and SQL2k5 both with SP2.

On this machine I took an SQL backup from the old installation, created a sgvault database within SQL on the VM, right clicked on this database and restored the database from the SQL2000 backup file. After that I installed the latest 4.1.2 server version of Vault and kept the database. Its initial web setup asked me to update the license information, which I did, and then I checked the user and repository information. All looked good. I also installed the client software so I could look at all the data inside the repositories, and so far (although I am no expert in actually using this app - I will get another user to check it out) it all looks good!

If the production install is this easy I will be very pleased!

I notice the email function has been made more prominent in this version - I hadn't noticed it was there before....

One thing I was not sure about though - is there any need to transfer the log files as stated in your reference: http://support.sourcegear.com/viewtopic.php?t=228 . I didn't do this, and kind of wondered how this would work going from SQL 2000 to 2005......

[Beth]

created a sgvault database within SQL on the VM

You don't need that step.

restored the database from the SQL2000 backup file

When you do this, you can manually type in sgvault for the database name.

is there any need to transfer the log files

Those are already in the backup and will appear when you restore. Taking a SQL 2000 backup and restoring it on SQL 2005 is fine. SQL will upgrade the database to what it needs for SQL 2005. You just can't go in the other direction from SQL 2005 to SQL 2000 I think.