To move the Vault or Fortress databases, you should install or have an installation of MS SQL Server on the new box.

A) You will now need to move the database(s) to the new server. For Vault 4.x and above, you will need to move the sgmaster and sgvault databases. For Fortress 1.x and above, you will need to move the sgmaster, sgvault, and sgdragnet databases. For all prior versions of Vault, please move the sgvault database.

Regardless of your version, this can be accomplished in two ways:

1) SQL Backup / Restore -

1. Make a complete backup of the database(s).
2. Move the backup file(s) to the new SQL Server machine.
3. Restore those backup(s) into a new database using the original database name.

2) SQL Detach / Attach -

1. Detach the database(s).
2. Move all data / log files associated with the database(s) to SQL Server's data directory on the new machine.
3. Attach all database(s) files.

Once you have a working copy of the database on the new machine, you must do ONE of the following to recreate the appropriate users:

B-1) This is perhaps the easiest way to reconfigure the Vault or Fortress Server. First Uninstall the Server. Make sure to answer KEEP the database when prompted. Next, re-install the Vault or Fortress 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 1.x and above:
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 1.x and above:
USE [sgmaster]
EXEC sp_grantdbaccess N'sgvaultuser'
EXEC sp_addrolemember N'db_owner', N'sgvaultuser'
GO

On SQL Server 2005 (including SQL Server Express):

Code: 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 1.x and above:
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 1.x and above:
USE [sgmaster]
EXEC sp_grantdbaccess N'sgvaultuser'
EXEC sp_addrolemember N'db_owner', N'sgvaultuser'
GO

If SQL Server is running in Windows authentication mode, run this query, substituting the correct ASP.Net Process account. Note, 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 1.x and above:
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 1.x and above:
USE [sgmaster]
EXEC sp_grantdbaccess N'DOMAIN\VAULTSERVICE_DOTNET_ACCOUNT'
EXEC sp_addrolemember N'db_owner', N'DOMAIN\VAULTSERVICE_DOTNET_ACCOUNT'
GO



If you were using Windows authentication mode, you may want to clean up the old permissions. You can simply call

Code: Select all

sp_revokelogin N'OLD_VAULTSERVICE_DOTNET_ACCOUNT'

to remove the old account from each database.

Note, you can also use MS SQL Server Enterprise Manager to set these permissions.

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.