Update SQL Server database schema using MVC .net with versioning

user_4746457 0
Viewed 130

We are using a SQL Server database and web apps hosted on Azure. We maintain a database version in a table with version column.

We have added code to Application_Start() to check the version of database and execute the commands with higher version than current from our ASP.NET MVC app to upgrade the schema.

It was working fine until recently we noticed that in span of few seconds the Application_Start event got triggered again and upgrade schema functionality ran twice.

How can we prevent upgrading schema by adding some check if there is another instance upgrading schema or Is there a way to not trigger the Application_Start event multiple times?

We deploy the code to web app using Azure Dev Ops pipeline.

Thank you!

azure-web-app-service azure-sql-database asp.net-mvc sql-server
1 Answers

Your web app can be recycled for multiple reasons that are beyond your control. So placing code within Application_Start to run scripts that bring your database up to date is not optimal for the reasons you just mentioned.

Since you're already using a Dev Ops pipeline, there's a built in way to handle this as part of your deployment. Check out the Database Project type in Visual Studio. This will allow you to script all of your tables, stored procedures, views, etc in text form and keep it under source control. Then, as part of your Dev Ops pipeline, you can use the Visual Studio Build task to build your Database project and create a DACPAC as output. Then, use the Azure SQL Database Deployment Task to pick up the DACPAC output and deploy it to your Azure SQL instance.

This way, you KNOW that your database is already upgraded before you finish your production deployment.

user_75091680
Related