how to run sql scripts on sql azure db when deploying to azure website

Generally speaking, you can leverage node.js scripts and defined into the scripts section in the package.json, and config the custom npm script into the deploy.cmd file, then the deployment task will call the custom script.

Please try the following steps:

1. Create a file .deployment and deploy.cmd by azure-cli command azure site deploymentscript --node --sitePath nodejs
2. create a node.js script in your application which will can handle sql operations, e.g. var sql = require('mssql'); var config = { user: '<user>', password: '<pwd>', server: '<database_server>.database.windows.net', // You can use 'localhost\\instance' to connect to named instance database: '<databasename>', options: { encrypt: true // Use this if you're on Windows Azure } } var connection = new sql.Connection(config, function(err) { var request = new sql.Request(connection); // or: var request = connection.request(); request.query("insert into dbo.todoitem (text) values ('test string')", function(err, recordset) { // ... error checks console.log(err); console.dir(recordset); process.exit(); }); }); connection.on('error', function(err) { // ... error handler });
3. Modify the package.json, add the mssql module and config the custom scripts: { "engines": { "node": "5.9.1" }, "dependencies": { "mssql": "^3.3.0" }, "devDependencies": {}, "scripts": { "customscript": "node connection.js" }, }
4. Modify the deploy.cmd to add the step to call the npm run customscript command during the Azure deployment task, in the original deploy.cmd file, you can find the sentence to install node.js modules: :: 3. Install npm packages IF EXIST "%DEPLOYMENT_TARGET%\package.json" ( pushd "%DEPLOYMENT_TARGET%" call :ExecuteCmd !NPM_CMD! install --production IF !ERRORLEVEL! NEQ 0 goto error popd )
   You can directly add the script under it: echo npm customscript call :ExecuteCmd !NPM_CMD! run customscript
5. Then deploy your application to Azure via Git, it will run the custom npm script during the deployment task, and will query your SQL.

Any further concern, please feel free to let me know.

Ok so turns out you can just use SqlCmd directly in deploy.cmd to do this. Following are the steps (source):

1. install npm on your machine
2. run the following command npm install azure-cli -g
3. for mvc4 web application run : azure site deploymentscript --aspWAP mvc4\Mvc4WebApplication\Mvc4WebApplication.csproj -s Mvc4\Mvc4WebApplication.sln
4. This will create .deployment and deploy.cmd files. Add these to the root of your repository.

I couldn't really find a good source for below, and I just got lucky when testing this out on Kudu cmd tool:

Modify deploy.cmd section under Deployment section add something like the following:

set dbName=mydb
set sqlRunner=sqlcmd
set dbServer=tcp:myorg.database.windows.net,1433
set currentdir=%cd%
set username=myusername
set password=mypassword

for %%F IN ("%currentdir%\DBScripts\05.Changes\*.sql") do (
    @echo %%F
    %sqlRunner% -S "%dbServer%"  -U %username% -P %password% -d %dbName% -i "%%F" -b
    if ERRORLEVEL 1 GOTO ERROR
)

The Key here is availability of sqlcmd. This is what I was looking for. You can actually test all of this out from within Kudu Cmd Tool.