Vault 4.1 install alongside existing 3.1.5 install

[dorjem]

Hi

Problem we're trying to solve is - time line and business risk mitigation during upgrade from 3.1.5 to 4.1.

Environment -
ServerA : 3.1.5 vault server install (32bit)
ServerB : production database server running sql2005 instance

ServerC : 4.1 vault server install (64bit running ASP.NET 2.0.5 in 32bit mode)
ServerD : development database server running sql2005 instance

Requirements -
We need to have the databases for both the Vault versions working on the single production sql2005 instance on ServerB
We need Vault 4.1 functionality right now for a new project

Future -
At a time convenient to the current ServerA users we will upgrade that installation from 3.1.5 to 3.1.9 and then to 4.1
At that point when both installations are at the same version we will use the 'import / export' tool to transfer repositories into a single installation again.

Help -
We see the only way to do this being the renaming of the sgvault4.1 and sgmaster4.1 databases on ServerD, moving them to ServerB and then hooking ServerC up to the 4.1 databases on ServerB using the web.config file.

Forseen problems -
We appreciate that users who will have to use both 4.1 and 3.1.5 clients will have problems. Probably able to be mitigated by moving the cache file location on the client pc and moving the 3.1.5 exe prior to install of the 4.1 client.

Unknowns -
Can we rename the databases and expect them to work ok ?
I've spent some time looking through the forum and have found comments indicating that the two 4.1 databases interact via stored procedures in the sgvault database.
I've looked through some of the stored procedures and haven't found any hard coded references to sgmaster. I have found some really well documented sharp looking transact sql.
From what I can gather the sys.spdatabases procedure gets a list of all the databases the user has access to.
We're using SQL authentication and now that we have things installed (install required access to create/delete databases and the master database) we can cut back the permissions so that the SQL user only has access to those two databases, thus I think ensuring that things work.

I'd be very interested in anyones comments on this.

The other question I have is where does the sgvaultuser come into all this our installation used XYZuser SQL user.
I this going to be the achilles heal of our potential solution.

Again thanks to Linda and Beth for great responses online and via phone.

[dorjem]

Have just been testing with the following

Cloned sgvault and sgmaster databases on the development sql2005 instance to sgvaultA and sgmasterA.

Then changed the web.config file on the vault server to point to the sgvaultA database, ran an iisreset, logged on to the admin web client.
I could logon, so I created a new user, thinking this would allow me to differentiate the databases.

Of course sgvaultuser had access to sgvault and sgmaster databases and the clones sgvaultA and sgmasterA. So the user was inserted into the users table in sgmaster not sgmasterA.

I then had our DBA restrict the access of the sgvault SQL user to only the cloned sgvaultA and sgmasterA databases.

On trying to logon I get a "Login Failed: The username or password supplied is invalid." message

My supposition from this test is that the code and or stored procedures do have sgmaster hard coded in them somewhere.

What to do ?
For my situation because versin 3.1.5 doesn't have a sgmaster database I can probably live with it.
When I upgrade the 3.1.5 version to 4.1 I'll just have to backup and rename the existing 4.1 sgmaster database. Export what we want from the upgraded version, rename the upgraded version 3.1.5 sgmaster database, rename the 4.1 sgmaster database back to sgmaster and run import to get the files into a single installation

Please let me know if you see holes in this logic or problems with it.

Many thanks

[dorjem]

Hi there,

For similar reasons to the following post
[url]http://support.sourcegear.com/viewtopic.php?t=6368[/url]
Having the ability to run a T-SQL command that hashed a password for the SQL user would be great.

The screen asking for the Vault admin password should indicate that this will be used for SQL authentication too. Very easy security hole to open up thinking the password is for the GUI / app only, not live on the database server.

[dorjem]

Testing today I've done the following

As before

ServerC : 4.1 vault server install (64bit running ASP.NET 2.0.5 in 32bit mode)
ServerD : development database server running sql2005 instance

Databases on ServerD
sgvault
sgmaster
sgvaultA
sgmasterA

New today
SQL users on ServerD
sgvaultuser - can only access sgvault and sgmaster databases
sgvaultuserA - can only access sgvaultA and sgmasterA databases

Change ServerC web.config file to point to to sgvaultA database, and use sgvaultuserA

Result on the admin logon web page - user and or password is invalid

So I changed the permissions for the SQL user sgvaultuserA so that it only had access to the sgvaultA and sgmaster databases

Result on the admin logon web page - I can logon, add files to a repository using the web and the desktop client, view diffs etc

BUT when I click on the 'add user' link within the Admin tab I get the following error 'Error in application'

Now I'm picking that this is due to
1 - sgvaultuser is given specific permissions on some of the stored procedures.
Though I've given sgvaultuserA db_owner rights to both sgvaultA and sgmaster databases
2 - the code or stored procedures contain hard references to sgvaultuser

I'm still happy for anyone to comment on my logic or offer suggestions / examples of how this has been got working in production environments like ours.

Many thanks

[lbauer]

This is all very complicated, and is a configuration is not supported, even though it might appear to work.

I know you have concerns about the time it takes to upgrade, but upgrading may not take that long, depending on the size and complexity of your database. It would be better to upgrade to 4.1.1, and have all your users on the same Vault server/database.

Because you are a few versions out of date, I would restore a copy of the 3.1.5 database to SQL Server on a a test machine (or VM if you have one), and try upgrading to 3.1.9, and then to 4.1.1, to see if there would be any problems in the upgrade.

If you're already using Vault in production on two different machines, I would recommend upgrading the 3.1.5 database, then import the existing 4.1 repository or folders into the upgraded database.

[dorjem]

Update on this posting incase others end up looking at it.

Thanks Linda for your input about the configuration not being supported.

What we've done is as follows
Vault 3.1.5 - installed and running against an sgvault database on database server X instance A
Vault 4.1. - installed and running against sgvault and sgmaster databases on database server X instance B
(Our DBAs weren't too keen on having two instances for the same version of SQL, 2005 in our case, running but have accepted it to accommodate the business reasons)

We've also discovered a stand alone Vault 3.1.9 instance on server B running in another area of the business.

Our plan now is to do the following

Upgrade 3.1.5 server X instance A to 3.1.9
Export the standalone 3.1.9 server B database and Import it into Server X instance A 3.1.9 version
Then upgrade Server X instance A 3.1.9 version to Version 4.1
Then export from server X instance B 4.1 and import into Server X instance A 4.1

We haven't iniated this yet but will do so when the business situation allows.

Two comments - I believe it would be most helpful for SourceGear to Explicitly state at the top of all upgrade/install procedures that "database name and database user are not able to be changed due to the architecture of the product" would be very useful. Only because the most software products do allow for this, so it is assumed to be true for all products.

Hope this helps anyone reading it

[lbauer]

I believe it would be most helpful for SourceGear to Explicitly state at the top of all upgrade/install procedures that "database name and database user are not able to be changed due to the architecture of the product" would be very useful.

We'll try to make this more clear in our documentation.

Some comments: If you had Vault Server installed on two different machines, you could have two Vault databases on the same machine, provided they were each running in a different instance of SQL Server. You would have to specify the instance name during installation or modify the connection string in the Vault Service web.config files.

If you plan to consolidate by exporting items from one database to another, you need to upgrade the 3.1.x databases to at least Vault 3.5.3. Import\Export was not available until Vault 3.5.x.

When you upgrade to Vault 4.x, you'll need to upgrade your licenses, and the 4.x licenses will work in 3.5.x.

[dorjem]

Hmm Thanks for that Linda

According to the information I've had from you and from Beth to get my three database A B and C into one database I need to

Database A upgrade 3.1.5 > 3.1.9 > 3.5.3
Database B upgrade 3.1.9 > 3.5.3

Export B and import into A

Database A upgrade from 3.5.3 > 4.1
Export C (Already 4.1) and import into A

Are there any versions I need to step through other than the ones listed ?


PS one other comment on the documentation - or suggestion for enhancement. To do the install the SQL user has to have full rights to the database instance. In the sort of corporate production environment I work in it would be great if there was an option in the installer to let it know the databases exist but need to be populated by the installer. That way the Database Admins are happy they're not letting some unknown events into their environment and the install procedure avoids doing the install to one server and then moving the database.

[lbauer]

Database A upgrade 3.1.5 > 3.1.9 > 3.5.3
Database B upgrade 3.1.9 > 3.5.3

Export B and import into A

Database A upgrade from 3.5.3 > 4.1
Export C (Already 4.1) and import into A

Are there any versions I need to step through other than the ones listed ?

This sounds fine.

Regarding the installation, the Vault installer needs administrative access to the SQL Server instance in order to create the proper user and upgrade the database (or create a new database when first installed). Once the installer is finished, those credentials are no longer used.