How to migrate a remote memoQ SQL database

Before explaining the database migration process, here are a couple of important notes:

• Please stop the memoQ server service and create a full backup of the MemoQServer database as a safeguard.

• Please find below the supported MS SQL Server versions:

memoQ 8.7 and 9.x: Microsoft SQL Server 2008, 2008 R2, 2012, 2012 R2, 2014, or 2016. The Express, Developer, Standard, and Enterprise editions are all supported.

It is very important to mention that you cannot migrate the MemoQServer database from a newer MS SQL Server instance (i.e. 2012 R2 Express Edition) to an older MS SQL Server instance (2008 Standard Edition). There is no backwards compatibility.

Upgrading the SQL Server instance before migration

If you will simply upgrade the MS SQL Server instance (i.e. from MS SQL Server 2008 R2 Express Edition to MS SQL Server 2008 R2 Standard Edition), then follow these steps:

• Stop the memoQ server service
• Create a full backup of the MemoQServer database
• Upgrade the MS SQL Server instance to Standard Edition
• Start the memoQ server service

Migrating the database to a newly created SQL instance

If you wish to install a separate MS SQL Standard Edition instance, the steps are the following:

• Log into Windows with a Windows account which has a fixed system administrator server-level role (with this role you should be able to do everything related to the database migration) on both MS SQL Server instances (the account which was used during the instance installations must have sysadmin fixed server level role)
• Stop the memoQ server service
• Create a full backup of the MemoQServer database.
• Connect to the old MS SQL Server instance and detach the MemoQServer database. This can be done as follows in the Windows command line assuming that the old MS SQL Server instance name is MEMOQSERVER (and this is on the same computer as the memoQ server):

• Connecting to the MEMOQSERVER instance: sqlcmd -S .\MEMOQSERVER
• Detaching the MemoQServer database: sp_detach_db 'MemoQServer'
• Confirming the detach operation with the go command: go
• Connecting to the mssqlserver standard edition instance (default instance, not named instance): sqlcmd -S .\
• Attaching the MemoQServer database to the mssqlserver instance: sp_attach_db 'MemoQServer', 'absolute path of the MemoQServer.mdf file', 'absolute path of the MemoQServer_log.ldf file'
  For example: sp_attach_db 'MemoQServer', 'C:\ProgramData\MemoQ Server\MemoQServer.mdf', 'C:\ProgramData\MemoQ Server\MemoQServer_log.ldf'
• Confirming the attach operation with the go command: go
• After running the go command the database might be upgraded (i.e. if it is being migrated from older to newer version of MS SQL Server).
• When this is done type:
  exit
  to log out from sqlcmd.

• At this point you will need to modify the SQL server connection string in C:\ProgramData\MemoQ Server\Configuration.xml, SQLConnectionString node. Modify the Data Source section to Data Source=localhost.
• Save Configuration.xml and start the memoQ server service.

You might need to give db_owner database-level role to the account which is running the memoQ server service (if you receive login failed error for the account when connecting to the MemoQServer database)

This can be done as follows (we would suggest to do this with a sysadmin account because that should be do it without permission issues):

• Connecting to the mssqlserver standard edition instance (default instance, not named instance): sqlcmd -S .\
  Switching to the MemoQServer database: use MemoQServer
  Confirming the use command: go
• Giving db_owner database-level role to the account running the memoQ server service: sp_addrolemember 'db_owner', 'DOMAIN or COMPUTERNAME\ACCOUNTNAME'
  Confirming the command: go