Setting Up a Linked Server for Moving Large VenioOne Project Databases

VenioOne's architecture relies on linked servers for cross-database access between project databases and the central Project Control Database (VenioPCD).

Linked servers aren't primarily for "moving" data (use standard SQL methods for that), but they enable seamless redirection and distributed queries during/after migration, especially in multi-server setups. 

Prerequisites

• SQL Server 2022 with compatible editions (e.g., Standard or Enterprise).
• Enable Microsoft Distributed Transaction Coordinator (MSDTC) on both servers for multi-DB support.
  • Open services.msc, start "Distributed Transaction Coordinator".
  • Run dcomcnfg, navigate to Component Services > My Computer > Distributed Transaction Coordinator > Local DTC > Properties > Security tab.
  • Settings: Network DTC Access ✓, Allow Inbound/Outbound ✓, No Authentication Required, Enable XA/SNA Transactions ✓.
• VenioOne Console access with admin rights.
• Stop Venio services (e.g., Distributed Services) before migration to avoid locks.
• Backup all databases first.

Step-by-Step: Set Up Linked Server for Migration

1. Migrate the Project Database (Use Backup/Restore for Large DBs):

   • On old server: Backup the project DB (e.g., RIGHT-CLICK DB > Tasks > Back Up, full backup with compression).
   • Copy .bak to new server (use robocopy for large files: robocopy \\old\share \\new\share ProjectDB.bak /Z /J).
   • On new server: Restore (RIGHT-CLICK Databases > Restore Database > Device > Select .bak, set file paths, Overwrite existing).
   • Update compatibility level: ALTER DATABASE [ProjectDB] SET COMPATIBILITY_LEVEL = 160;.
   • Run integrity check: DBCC CHECKDB ('ProjectDB');.

   For detach/attach (faster for very large DBs on same network):

   • Detach on old: EXEC sp_detach_db 'ProjectDB';.
   • Copy .mdf/.ldf to new.
   • Attach on new: CREATE DATABASE ProjectDB ON (FILENAME = 'NewPath\ProjectDB.mdf'), (FILENAME = 'NewPath\ProjectDB_log.ldf') FOR ATTACH;.

2. Add the New Database Server in VenioOne Console:

   • Open Console > Administration > Configure Server > Database Server tab.
   • Enter Display Name, Database Server (new IP/name + instance), User Name/Password (SQL admin account).
   • Uncheck "Use default data and log locations" if custom; specify MDF/LDF paths.
   • Add backup location for SQL backups.
   • Click Add. The new server will show PCD Server as "NO" (not control DB).

3. Set Up Linked Server Alias on New SQL Server (From Ticket):

   • Connect to new SQL via SSMS.
   • Go to Server Objects > Linked Servers > New Linked Server.
     • Linked server: Enter the other server's name (e.g., 'VENIO-SQL-01\VENINST01').
     • Server type: SQL Server.
     • Provider: SQL Native Client.
     • Data source: New server name/IP + instance.
   • Security tab: Use "Be made using the login's current security context" or map logins if needed.
   • Test Connection.
   • This aliases the old name to new, so app queries to old redirect seamlessly.

4. Update VenioOne Configurations and Synonyms:

   • In VenioPCD DB (control DB), update tbl_pj_databaseServerInfo: Set IsPCDServer=1 for new, 0 for old.
   • Update tbl_pj_projectsetup for affected projects: Set DSID to new server's ID, DatabaseInstanceName to new DB name.
   • Request Venio Support to provide the sp_syn_change stored procedure. Run the provided sp_syn_change.sql for each project ID: EXEC dbo.sp_syn_change @projectid = YourProjectID;.
     • This recreates synonyms using the linked server, pointing to the new PCD/location.
   • If using multiple DB servers, ensure MSDTC is enabled for transactions.

5. Move Project Data Folders :

   • Console > Administration > Backup/Restore Project > Next > Move Project Location.
   • Select project, new File/Index Locations.
   • Data Copy Option: "Copy All Data from Project Location" (with retry count) for automated move, or "Update Database Only" then manually copy files.
   • Monitor status; save log for errors.

6. Verify and Test:

   • Run Health Checkup on all services.
   • Test access to projects in VenioOne Desktop/Web.
   • Monitor for errors like the ticket's SqlException (server not found); if seen, recheck linked server.
   • Update any hard-coded references in code/templates (ticket suggests checking ingestion templates).

Considerations

• Always run sp_syn_change post-move.
• Errors: If "Could not find server", it's linked server issue; use alias.

If issues persist, engage Venio support for a screenshare, as in the ticket. Share specific error logs for more tailored advice!