I have been working on a project where we use a SQL database to develop on, while we have a database and service running in production on azure. This has been working very well and with the azure staging deployments, we are able to update our service without any downtime. Updating the Vidyano data is also easy and you can do this without any downtime too. You just have to follow these easy steps:
Generate all the scripts you need. To update the Vidyano data without downtime, you have to exclude the CacheUpdate table from the scripts! also, if you haven’t synchronized your users, be sure not to drop/create the following table either: Users, Groups, User_Group. These are the two scripts you need to generate:
- The script that creates all the Vidyano tables and their data. You can generate this script with the SQL Server Management Studio on your development database.
- The script to drop the existing Vidyano tables. You can generate this script with the SQL Server Management Studio on your production database.
Make sure your production service is running. As you know, IIS recycles your application when it isn’t used for some time. You can be sure the service is running by logging in into the application and executing a query. Depending on how many instances you have running, you may need to execute multiple requests to be sure.
Clear the data inside the CacheUpdate table if there is any. This makes sure the service won’t try to update any Vidyano data while you are recreating the schema and data.
Execute the 2 scripts created in step 1. Off course you have to use the drop script first.
Now we have to tell the service to reload the data stored in the Vidyano tables. We use the CacheUpdate table to do this. Execute the following script on the production database:
insert into [Vidyano].[CacheUpdates] ([Id], [Timestamp], [Data]) values (newid(), sysdatetimeoffset(), cast('' as varbinary))
You can now test the service to see if all changes where made correctly