I have migrated the on premise database to Azure SQL Database using SSMS. For any upgrades required to the database objects like table, stored procedure, I am connecting back to the SQL Azure using SSMS and executing the change script manually.
For on premise database, I used DbDeploy.net 2 for automating the execution of sql changes.
DbDeploy.net 2 also supports SQL Azure database also.
For SQL Azure database, what is the best way to manage the automation of the deployment of sql changes.
Is there anyway to leverage VSTS here.
Can anyone help me to resolve this issue?
If you are wanting to automate deploying the schema changes, I would recommend using SSDT/DacFx. There are several ways to achieve this depending if you use TFS to track your database changes or if you want to pull the changes from a local database instance. If you track your changes in TFS and use SSDT, you can publish the DACPAC from your build drops using SQLPackage.exe. If you track your master copy of the database schema in a local database, you can run an extract operation using SQLPackage.exe and deploy the extracted DACPAC to Azure using the publish operation.
You can also see my talk from TechEd Europe Application Lifecycle Management for SQL Server Database Development: