Upgrade database schema when deploying to windows azure instance

Question

I have web application hosted on Windows azure instance. We have a test and live cloud service, the test service have 1 instance and live service have 3 instances.

The application for test is deployed automatically every nightly via TFS Builds and deployed to Windows Azure using power shell scripts.

The problem is we have old school sql scripts for database schema and data changes (if required). Currently we have to login to azure sql database manually (SMSS) and run the script.

I would like to automate the sql script on every build that run nightly via TFS Build Machine using powershell script. How can I do this ?

The SQL Script is re-runnable so there is no worries, if it runs the same script every day as this will not create any problem on the test application.

Answer 1

Perhaps I'm wrong, but I see this very simple. Why not you call the "sqlcmd.exe" utility from your PowerShell scripts passing the SQL scripts files as parameters?

Be sure to use the sqlcmd.exe that comes with SQL Server 2008 R2 or later, in order to work properly with SQL Azure.

Answer 2

I would like to understand how the SQL Package is been deployed.

Looks like you have a script that has to be run each time after deployment of the Database. This can be done with powershell (ExecuteNonQquery), just as it can be done for a on-premise SQL Server.

For a single Query:

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlQuery = "Your Query here"
$SqlConnection.ConnectionString = "Database Conncetion String"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $sqlQuery
$SqlCmd.CommandTimeout = 300
$SqlCmd.Connection = $SqlConnection
$sqlConnection.Open()
$result = $sqlCmd.ExecuteNonQuery()
$sqlConnection.Close()

Have your query and connection string in place

For a SQL Script: You can refer to the question here

Hope this helps