A) You will now need to move the database(s) to the new server. The databases will depend on your version of Vault.
- Vault 3.x and earlier -- move the sgvault database.
- Vault 4.x and 5.x -- move the sgmaster and sgvault databases.
- Fortress 1.x, 2.x or VaultPro 5.x -- move the sgmaster, sgvault, and sgdragnet databases.
- Vault 6.x, 7.x, 8.x, 9.x and 10.x -- move the sgmaster, sgvault, sgnotify,and sgvaultindex databases
- Vault Professional 6.x, 7.x, 8.x, 9.x and 10.x -- move the sgmaster, sgvault, sgdragnet, sgnotify,and sgvaultindex databases (sgdragnet is not needed with Vault Standard)
Regardless of your version, this can be accomplished in two ways:
1) SQL Backup / Restore -
- Make a complete backup of the database(s).
- Move the backup file(s) to the new SQL Server machine.
- Restore those backup(s) into a new database using the original database name.
2) SQL Detach / Attach -
- Make a complete backup of the database(s).
- Detach the database(s).
- Move all data / log files associated with the database(s) to SQL Server's data directory on the new machine.
- Attach all database(s) files.
B-1) This is perhaps the easiest way to reconfigure the Vault, Fortress, or Vault Pro Server: First Uninstall the Server. Make sure to answer KEEP the database when prompted. Next, re-install the Vault, Fortress or Vault Pro Server using the NEW SQL Server machine upon installation. When prompted, answer USE the existing database.
C-1) Nothing else is required.
or
B-2) If you do not wish to uninstall/reinstall, you can configure this through a SQL Query SQL Tool. Note that when a database has been restored to a different SQL Server, the database login / user must be dropped from the restored database before running the grantdbaccess.
If SQL Server is running in SQL Server authentication mode, run this query, substituting the Vault Admin password for 'VAULTS_ADMIN_PWD.
On SQL Server 2000 (or MSDE):
Code: Select all
USE [master]
IF ( NOT EXISTS (SELECT sid FROM syslogins WHERE name = 'sgvaultuser') )
EXEC sp_addlogin N'sgvaultuser', N'VAULTS_ADMIN_PWD', N'sgvault'
GO
-- next access should be granted in the vault database
USE [sgvault]
EXEC sp_grantdbaccess N'sgvaultuser'
EXEC sp_addrolemember N'db_owner', N'sgvaultuser'
GO
-- The following is ONLY for Fortress and VaultPro:
USE [sgdragnet]
EXEC sp_grantdbaccess N'sgvaultuser'
EXEC sp_addrolemember N'db_owner', N'sgvaultuser'
GO
-- The following is for Vault 4.x and above or Fortress or Vault Pro:
USE [sgmaster]
EXEC sp_grantdbaccess N'sgvaultuser'
EXEC sp_addrolemember N'db_owner', N'sgvaultuser'
GOCode: Select all
USE [master]
IF ( NOT EXISTS (SELECT sid FROM syslogins WHERE name = 'sgvaultuser') )
CREATE LOGIN sgvaultuser WITH PASSWORD = N'VAULTS_ADMIN_PWD', DEFAULT_DATABASE = sgvault, CHECK_POLICY = OFF;
GO
-- next access should be granted in the vault database
USE [sgvault]
EXEC sp_grantdbaccess N'sgvaultuser'
EXEC sp_addrolemember N'db_owner', N'sgvaultuser'
GO
-- The following is ONLY for Fortress and VaultPro:
USE [sgdragnet]
EXEC sp_grantdbaccess N'sgvaultuser'
EXEC sp_addrolemember N'db_owner', N'sgvaultuser'
GO
-- The following is for Vault 4.x and above or Fortress and VaultPro:
USE [sgmaster]
EXEC sp_grantdbaccess N'sgvaultuser'
EXEC sp_addrolemember N'db_owner', N'sgvaultuser'
GONote, if the Vault Server will be running on a different machine, then you must use an account for the Vault Service's ASP.Net process that has network access to the SQL Server.
Code: Select all
USE [master]
IF ( NOT EXISTS (SELECT sid FROM syslogins WHERE name = 'DOMAIN\VAULTSERVICE_DOTNET_ACCOUNT') )
EXEC sp_grantlogin N'DOMAIN\VAULTSERVICE_DOTNET_ACCOUNT'
-- next access should be granted in the vault database
USE [sgvault]
EXEC sp_grantdbaccess N'DOMAIN\VAULTSERVICE_DOTNET_ACCOUNT'
EXEC sp_addrolemember N'db_owner', N'DOMAIN\VAULTSERVICE_DOTNET_ACCOUNT'
GO
-- The following is ONLY for Fortress and VaultPro:
USE [sgdragnet]
EXEC sp_grantdbaccess N'DOMAIN\VAULTSERVICE_DOTNET_ACCOUNT'
EXEC sp_addrolemember N'db_owner', N'DOMAIN\VAULTSERVICE_DOTNET_ACCOUNT'
GO
-- The following is for Vault 4.x and above or Fortress and VaultPro:
USE [sgmaster]
EXEC sp_grantdbaccess N'DOMAIN\VAULTSERVICE_DOTNET_ACCOUNT'
EXEC sp_addrolemember N'db_owner', N'DOMAIN\VAULTSERVICE_DOTNET_ACCOUNT'
GO
Code: Select all
sp_revokelogin N'OLD_VAULTSERVICE_DOTNET_ACCOUNT'Note, you can also use MS SQL Server Managment Studio to set these permissions.
An addendum to step B-2 if you do not drop the users found in the various sg* databases and the SQL Server login names are an exact match on the new SQL Server:
For versions of SQL Server that support the stored procedure, you may use the 'sp_change_users_login' with the AUTO_FIX option to re-map the SQL Server logins on the new machine to the existing users found within the restored or attached sg* databases.
For later versions of SQL Server, you may want to use the 'ALTER USER' with the LOGIN option to re-map the SQL Server logins on the new machine to the existing users found within the restored or attached sg* databases.
It bears repeating this only works if the SQL Server login name matches on both the old and new SQL Server (ie. 'sgvaultuser'). See SQL Server's documentation on 'sg_change_users_login' or 'ALTER USER' for additional information.
C-2) Finally, re-configure Vault Server to find the database on the new machine. Simply edit Vault Server's web.config file. Inside the <connectstring> element, you will find the name of your old database server. Modify the connect string so Vault is looking for the database on the new server. Save the changes to web.config.
***UPDATE***
Please note that later versions of Vault have multiple databases and multiple connect strings. The databases a of May 2014 are sgvault, sgmaster, sgnotify, and sgvaultindex. If you have Vault Professional, you will also have sgvaultindex.
There can be multiple web.config files and in the web.config you may have the following lines. The web.config files are found in the vaultservice, vaultservice/vaultnotifyservice, and vaultservice/vaultindexservice Server installation directories.
Code: Select all
<!-- SQLSERVERMODE-add key="ConnectString" value="Application Name='SourceGear Vault Server'; Connection Reset='true'; Server=(local); Database=sgvault; User ID=UUUUUUUU; pwd=PPPPPPPP" />SQLSERVERMODE- -->
<add key="ConnectString" value="Application Name='SourceGear Vault Server'; Connection Reset='true'; Server=(local); Database=sgvault; Integrated Security='true'" />
<!-- SQLSERVERMODE-add key="SGIndex_ConnectString" value="Application Name='SourceGear Vault Server (Find in Files)'; Connection Reset='true'; Server=(local); Database=sgvaultindex; User ID=UUUUUUUU; pwd=PPPPPPPP" />SQLSERVERMODE- -->
<add key="SGIndex_ConnectString" value="Application Name='SourceGear Vault Server (Find in Files)'; Connection Reset='true'; Server=(local); Database=sgvaultindex; Integrated Security='true'" />
<!-- SQLSERVERMODE-add key="SGNotify_ConnectString" value="Application Name='SourceGear Vault Server (Notification)'; Connection Reset='true'; Server=(local); Database=sgnotify; User ID=UUUUUUUU; pwd=PPPPPPPP" />SQLSERVERMODE- -->
<add key="SGNotify_ConnectString" value="Application Name='SourceGear Vault Server (Notification)'; Connection Reset='true'; Server=(local); Database=sgnotify; Integrated Security='true'" />