Dev Test / Live Set Up - SQL Back End

[mfosterbdp]

Hey All

Hoping somebody can help! 

I have developed a simple resource management app for our company, we link it into a Basic DTU Azure SQL DB which costs very little (£5 per month) We only need 3 tables and approx. 200 rows. 

We have a plan 2 licence and we have a dev/test environment and production environment. When we release updates I can promote to live using app packages, all good 🙂 

If we want to make changes to the back end (SQL tables) so far I have been doing this in live, which is a bit risky! 

I have asked out IT department to spin up another basic SQL DB for dev/testing and migrate the DB over, the idea is I can then use both front and back ends and keep them aligned. 

We may need to refresh the data from time to time and any SQL structure changes would need to be moved from dev/test back to live

This got me thinking though, what are other peoples experiences when having a dev/test live SQL back end? Is this the best approach for a small set up like ours ? 

Any help would greatly appreciated! 

Many thanks! 

[v-xida-msft]

Hi @mfosterbdp ,

Could you please share a bit more about your scenario?

Do you want to refresh the SQL Table data source within your app in Live version when you made any changes to your SQL Table?

Further, could you please share a bit more about the changes that you want to make in your SQL Table Back End?

If the changes that you want to make to your SQL Table Back End is data/records, I think it is not necessary to set up another basic SQL DB for dev/testing. If you want to refresh SQL Table records in real time within your app, I think the Timer control and Refresh function in PowerApps app could achieve your needs.

On your side, please consider add one Timer control within each screen of your app, then set the Duration property to following:

1000

set the AutoStart and Repeat property to following:

true

set the OnTimerEnd property to following:

Refresh('[dbo].[YourSQLTable]')

set the Visible property of the Timer control to following:

false

after that, re-publish your app, when you run your app, the SQL Table data source would be refreshed in real time.

If the changes you want to make to your SQL Table is structure, I think you need to re-edit your app. E.G. If you added a new column in your SQL Table, you need to re-edit your app, refresh SQL Table data source in your app, then you could reference the new added column value in your app.

From my perspective, it is not necessary to set up two back end SQL tables (One for Live, another for Test/Dev). The Live app and the Test/Dev app could connect to same SQL Table data source. In addition, when syncing data from one back end SQL table to another one, it may need to take a lot of time or cause issues.

Best regards,

[mfosterbdp]

Hi  @v-xida-msft 
 
Thank you for your reply, ok food for thought, maybe I'm over engineering this a little! The idea was we refresh the data from live back to dev, make our changes and if there are data and structure change sync them back from dev to live.

So one scenario would be we want to add test records to our DB after changes have been made and then ideally I want to 'clean' them up, so far I have been running queries against the live DB which feels a bit risky, sometimes I need to re seed keys etc..

One other option is I did think about creating a 2nd 'maintenance' app so we can clean records using that which maybe a bit safer?

Many thanks!

[v-xida-msft · Accepted solution]

Hi @mfosterbdp ,

I agree with your thought almost. If you want to "clear up" these test records from your DB without running queries against the live DB directly, I think setting up another app to maintain the records in your DB is a better choice.

On your side, please consider take a try to design a "maintenance" app using PowerApps, then use it to "clear up" test records in your DB.

Best regards,

[mfosterbdp]

Hi @v-xida-msft 

Thanks so much for your help

We actually now have the test db up and running and our app links in nicely. But yes I'm going to consider this as a simpler approach as well given the potential issues you have outlined 🙂 

Many thanks!