 Search the WinSPC Knowledgebase |
 Browse by Category |
|
|
|
|
|
| How to move a WinSPC database from one SQL Server to another (manual migration process) |
|
Thank you for rating this answer.
|
WinSPC has a built-in utility to migrate a WinSPC database from one database server to another. The migration utility will copy the data to the new database, enter a notation into the old database indicating a migration has been done, and update the WinSPC.ini file in the remote directory so that each client station will know where the new database is at the next time the client is ran.
If you are moving your WinSPC database to a different database platform entirely (i.e., from SQL Server to Oracle), you will need to perform a standard WinSPC migration. A standard WinSPC migration performs a record by record copy from the original database to the new database. On larger databases, this operation can take some time to do. If, however, you are moving from one SQL Server database to another of an identical or newer version (i.e., SQL Server 2005 to SQL Server 2008), then you can perform an alternate migration procedure.
This procedure will only work if you are moving the database to an identical version or newer Microsoft Supported version of SQL Server. You can use this procedure to move the database from:
- SQL Server 2000* to SQL Server 2005
- SQL Server 2000* to SQL Server 2008
- SQL Server 2005 to SQL Server 2008
- SQL Server 2005 to SQL Server 2012
- SQL Server 2008 to SQL Server 2012
*NOTE: WinSPC 9 and above does not support Microsoft SQL Server 2000
The procedure below assumes that you have access to both the old and the new WinSPC databases, are familiar with the SQL Server Enterprise Manager tools, and are comfortable running SQL statements against a database. If you are not comfortable with any of the proceeding items, then you should use the standard migration process using the built-in WinSPC tools described above. This process will also require all users to out of the WinSPC application.
Steps to perform the manual migration:
1) On the new database server, configure the database for WinSPC, as per 'Phase 1' of Chapter 1 of the WinSPC "Installation and Configuration Guide". The "Installation and Configuration Guide" can be found on your product CD or in the files you downloaded to install or upgrade WinSPC. You can also download some of the recent installation guides here: https://knowledgebase.winspc.com/questions/98/. You can also contact our Technical Support to get a copy of the guide for your specific WinSPC version. This will create a new blank database and database user on the server. Create the database and database user with the same name as it is in the current environment. Also, set the password of the new database user to be the same as the old.
2) Run the following SQL command on the existing WinSPC database in order to set it to a 'migrating' state. This will prevent users from accessing WinSPC and making any changes while the database is being moved to the new location:
UPDATE SYSTEMSETTINGS SET DBSTATE = 2
3) Take a backup of the WinSPC database on the existing database server. You will need to use SQL Server Management Studio to do this and can find some more information on this step of the process here: https://knowledgebase.winspc.com/questions/20/
4) Move the backup file generated in step #3 to the new database server. Restore this backup to the database created in step #1. You can find more information on this step of the process here: https://knowledgebase.winspc.com/questions/215/
5) Execute the following SQL command to associate the new database user with the one contained within the restored database:
exec sp_change_users_login 'update_one', '{USERNAME}', '{USERNAME}'
Where {USERNAME} is the name of the database user. For example, if your database user name is 'johndoe', then the command would be:
exec sp_change_users_login 'update_one', 'johndoe', 'johndoe'
6) Update the 'WinSPC.ini' file in the WinSPC remote/network directory with the new database information. Specifically, you would need to change the following items:
- Under the '[ADO Params]' section, update the 'Data Source' to point to the new database server.
- Verify that the 'Initial Catalog' points to the new database name and that 'UID' points to the right database username.
7) On the current (old) database server, run the following SQL statement to mark the database as having been 'migrated':
UPDATE SYSTEMSETTINGS SET DBSTATE = 3
8) Mark the new database ready to use (since its a backup of the original, with a dbstate of 2):
UPDATE SYSTEMSETTINGS SET DBSTATE = 0
After completing the above steps, run your WinSPC client. It should:
- Attempt to connect to the old database, but see it has been 'migrated' (due to the change in the DBSTATE).
- It will then go out to the WinSPC remote directory, and pull down the updated WinSPC.ini file settings (including the location of the new server).
- It will then update its own 'WinSPC.ini' file with the new information and then attempt to connect to that WinSPC database instead. From this point forward, it will only connect to the new database.
9) Launch WinSPC on each client station and confirm it connects without any errors.
If you also need to move the location of the WinSPC remote directory (a move that will be mandatory if the remote directory was on the same hardware as the old database and that hardware is no longer going to be accessible to WinSPC clients), click here for instructions
|
| Attachments |
|
No attachments were found.
|
| Visitor Comments |
|
No visitor comments posted. Post a comment
|
The article has been updated successfully.
