Guide: Restoring an OutSystems SQL Server database backup into a another server

Guide: Restoring an OutSystems SQL Server database backup into a another server

  
Note: the steps on this forum post have been deprecated by the information on the technical note Migrating an Environment Using a Database Clone. Use that document instead.

Scenario:

Is usual, during hardware migration processes or recovery procedures, that a SQL Server database backup of the OutSystems catalog done in one SQL Server must be restored in a different SQL Server. So what kind of impact does it presents installing a fresh Hub Server platform over a restored database catalog? How about restoring the database catalog into an already installed and running Hub Server platform (also a very common scenario during replication of a production environment into a quality or development environment)?

In a nutshell, and also depending on the scenario in question, there are two major issues to be considered: the SQL Server security context (that may or not exist in the destination SQL Server) and the deployment Hub Nodes listing (that may be referring to different Hub Nodes other than the ones used by the destination platform).

The presented Follow Through Guide will address both issues with the purpose of restoring a fully functional platform over the an existent database catalog, while trying to be as general as possible, which makes it suitable to be applied in the following particular scenarios:
  • Installing a fresh Hub Server platform in a restored database catalog in a fresh SQL Server installation
  • Recovering a Hub Server platform in a restored database catalog in a fresh SQL Server installation
  • Replicating a production environment database catalog into a quality or development database used by a running OutSystems Hub Server platform
  • Migrating a SQL Server hardware and manually restoring the OutSystems database catalog

Follow Through Guide:

This guide assumes that you have an OutSystems Hub Server platform installation (either running or fresh installed and awaiting database configurations) and an OutSystems SQL Server database full backup file.
  1. Stop all Outsystems services and IIS on the Hub Server platform
  2. Restore the OutSystems database backup in the SQL Server database instance. If there's already an OutSystems database and is still in use, kill the SQL processes that connect to it.
  3. Delete the hubadmin, hubruntime and hublog users from the OutSystems catalog security users. When using SQL Server 2005, you'll need to delete the associated schemas first
    • If exists any objects ( views, tables, stored procedures, etc) in the OutSystems catalog dependent of any of these users change the object owner to dbo (evaluate the application level impact of this change)
  4. Delete the hubadmin, hubruntime and hublog users from the server security logins
  5. Confirm in the ossys_server table of the OutSystems catalog that there are no IP addresses pointing to different environment Hub Nodes servers. If they are, please change the enable column to false. There must be one that points to the Hub Server (or localhost if it is a single node installation) and has the enable column at true. This will ensure deployment in at least one Hub Node and prevent deployment to other Hub Nodes other then the one being installed, if configured.
  6. Run the OutSystems Hub Server configuration utility (make sure any SQL Management or Enterprise Manager applications are closed to avoid table locks), setup the SQL server and catalog to the just restored one and grant the permissions for the users HubAdmin, HubRuntime and Hublog. A login dialog box pops up requesting an SQL login. Use your sa administrative SQL user login (or any other SQL User with administrative priviledges) and press OK. Do this for every user and it will create the hubadmin, hubruntime and hublog users on the SQL Server and set the appropriate permissions onto the just restored database catalog. Afterwards, confirm the user's login by pressing the buttons Test Connection in all users.
  7. Then press the DB Schema Upgrade button before pressing the OK button to exit the Hub Server configuration utility.
  8. Start IIS, Deployment Controller Service, Deployment Service and Log Service. Do not start Scheduler Service nor SMS Connector Service at this point.
  9. We recommend to execute an updatedevicedb.exe and Scinstall.bat at this point to confirm the successful deployment
  10. Confirm the Hub Nodes list on the Server Configuration page. There should be only listed the Hub Nodes of the current installation and not any other IP address from another environment or installation. Delete any extra Hub Node from the list.
  11. In case of a environment replication, check every Web Reference effective value in the eSpace details page and the Site Properties for URLs or values pointing to another environments and installations services or servers. Change them accordingly.
    • If any Web Reference effective value was changed you will need to republish the eSpace in question.
  12. In case of a fresh Hub Server install, or the database brings a different factory, then you should publish all eSpaces to properly deploy them in the application server.
  13. Finally, if all the applications are running and there's no web references or other integrations linking to different environments, its safe to start the Scheduler Service and the SMS Connector Service

That's all to it.

Cheers

Miguel João
Don't forget to backup licence before the restore.
After the restore, and after OutSystems services are up, upload the licence.
Hi Miguel,

thanks for this useful post.

Would you care updating it for version 4.2, if necessary?
Hey

This information is already updated to 4.2 in the Technical Note Migrating an Environment Using a Database Clone.

Use the Technical Note instead, since it will be up to date with recent Agile Platform releases.

Cheers
Do you have a description how to do this in Oracle DB ?
Same for this document : http://www.outsystems.com/community/download.aspx?DocumentId=34

It's about a 5.0 installation !
OK for all you Oracle users...; here's how to do this for your database

This guide assumes that you have an OutSystems Hub Server platform installation (either running or fresh installed and awaiting database configurations) and an OutSystems Oracle database full backup file.
  1. Stop all Outsystems services
  2. Stop IIS
     
  3. Restore the OutSystems database backup in the Oracle database instance. If there's already an OutSystems database and is still in use, kill the SQL processes that connect to it.
  4. Make sure there are no other connections to the database (Select * from V$SESSION)
  5. Drop (cascaded) the HubAdmin, HubLog, HubRuntime and HubState users
  6. Create the HubAdmin, HubLog, HubRuntime and HubState again using the provied SQL scripts
     
  7. Run the OutSystems Configuration Tool (make sure any SQL Management applications are closed to avoid table locks), setup the Oracle server and catalog to the just restored one
  8. Then press the “DB Schema Upgrade” button
  9. Go to the session tab and click “Configure Session Database” button
  10. Press the OK button to exit the Configuration Tool
     
  11. Start IIS,
  12. Start OutSystems Deployment Controller Service,
  13. Start OutSystems Deployment Service
  14. Start OutSystems Log Service.
  15. Do not start Scheduler Service nor SMS Connector Service at this point.
     
  16. We recommend to execute an updatedevicedb.exe and Scinstall.bat at this point to confirm the successful deployment
  17. Start ServiceCenter (http://<servername>\servicecenter)
  18. Confirm the Hub Nodes list on the Server Configuration page. There should be only listed the Hub Nodes of the current installation and not any other IP address from another environment or installation. Delete any extra Hub Node from the list.
  19. In case of a environment replication, check every Web Reference effective value in the eSpace details page and the Site Properties for URLs or values pointing to another environments and installations services or servers. Change them accordingly.
    • If any Web Reference effective value was changed you will need to republish the eSpace in question.
       
  20. Publish the correct solutions.
     
  21. Finally, if all the applications are running and there's no web references or other integrations linking to different environments, its safe to start the Scheduler Service and the SMS Connector Service
 
Hello Joop

Yes, your overall procedure seems ok.

But there's a problem with the DRP CASCADE of the HubAdmin user. Since both the OutSystems data model and the applciations data model are created by the HubAdmin user, they will reside on the HubAdmin schema. Dropping the HubAdmin user with CASCADE will effectively drop the entire datamodel. This means that all data will be lost, and the database clone will be for nothing.

Instead of dropping the users, just change the OutSystems Configuration Tool to use them. This way you don't need to drop any users.

The reason why we recommend to drop them on SQL Server is because the way the users are created at the database and server level, and restoring a clone will only restore the user on the database, not the server, which will then break the Grant Permissions button on the OutSystems Configuration Tool.

In Oracle, since the users are manually created using the scripts provided by the Agile Platform, there's no need to drop them. The Grant Permissions button will not be available on the Configuration Tool.

Regarding the HubState users/schema, since it only has the session data, there's no need to clone, nor drop. Just recreated it from scratch based on the steps provided on the installation checklist for Oracle.

Hope this information helps

Cheers

Miguel Simões João

Miguel,

You're right. The reason why we could do the drop all was that we use webservices to gather our information.
We didn't have a datamodel in our application. :-)

Joop
Hello to all!

I will follow the guide with the objective: 
Replicating a production environment database catalog into a quality database used by a running OutSystems Hub Server platform

Concerning Cristiana Man considerations...

"Don't forget to backup licence before the restore." 
This is between steps 1 and 2, right?

"After the restore, and after OutSystems services are up, upload the licence."
After which step is this to be done?

Another issue... i want to replicate the production environment database catalog into a quality database, but i have to maintain the quality database environment with their actual references, so no harm can be done in the production environment... is this done in which step? 11 or 12?

Thanks in advance and cheers!

Paulo Cadete
I cannot find 
updatedevicedb.exe
Scinstall.bat

Where are those applications?
I'm using OS7
Antonio Xeira wrote:
I cannot find 
updatedevicedb.exe
Scinstall.bat

Where are those applications?
I'm using OS7
 
 Hi António

Those commands are in the Platform Server installation directory. However, the updatedevidedb.exe is no longer part of the Agile Platform 7.0, and it's not needed either, and the scinstall.bat is done by the Configuration Tool on the Agile Platform 7.0. If you're following the technical note Migrating an Environment Using a Database Clone, you can skip that step and proceed with the next ones, taking into consideration that the Service Center installation is done by the configuration tool. 

We're about to publish a new version of that Technote which will consider the changes on the Agile Platform 7.0, so keep in tune with the upcoming documents and downloads publications on the Agile Network.

Cheers

Miguel Simões João