Successfully Accelerated Deployment Process using Automation


Specific R & D division of one of the world's leading Hotels Search Engines in Sydney, approached us to develop an automated power shell script with various criteria so that they can deploy the database changes into their staging and then into the production environment, bypassing delays from the Administrator. This automation ensures no manual intervention required at all. Each Successful Result should be notified to the User and the Admin. For Errors, send user and admin different content for them to rectify and update next set of changes


  • tick Ensure only valid data is processed and at any fallbase case abort the operations
  • tick Handle Go Separators as it will have issues in running sqlcmd
  • tick All jobs should be processed only within allowed hours
  • tick Track the objects the user changed and also check for permission before processing
  • tick Ensure to Reserve certain keywords, so the automation will not use certain scenarios like drop type, alter view, alter procedure, alter table, create table, create procedure, drop table, drop view
  • tick Multiple files can be uploaded through the web form


  • tick After understanding the requirement , we created a Powershell job that uses sqlcmd command-line to deploy into a Staging database firstly, and then into the production database. A Web form with various fields and providing password to automate the process. Also, Reject any files over 2 MB. All the Web form details are stored in the dba.SQLDeployments
  • tick The user will login in IIS Basic Authentication populating the Server Environment variable. IIS does the authentication from Active Directory. The password field has no actual usage, but is included in the form as an alternate option.
  • tick The powershell job will be executed every 30 minutes by the windows scheduler and Check time of day is within allowed hours
  • tick Production implementation is our client’s technical team responsibility. Also clients ensure 100% code quality through various code reviews, feedback to our team being an esteemed engineering organization.
  • check-mark Executed the contents of the files as T-SQL.
  • check-mark Log to DB table the output of the SQL Server
  • check-mark Various Abort cases such as errors, MaxRows exceeded, StrictTimeout exceeded, Permissions controls exceeded
  • check-mark Verified the dba.DDLAudit table to see which objects the user touched. And Audit will have the information such as [DdlChange_ID],[InsertionDate],[CurrentUser],[LoginName],[Username],[EventType],[ObjectName],[ObjectType],[Tsql],[SchemaName]
  • check-mark Verified the dba.DeployPermissions to ensure the user has only touched the appropriate objects.
  • check-mark We ensured Dba.SQLDeploy Permissions table have the AuthUser Info, SchemaName and ObjectName they have access to
  • check-mark The dba.SQL Deployments table was designed with ID, CreatedOn, FormId which is hidden field in the form, Blob for file upload upto 6 files, FormData with JSON of all form Key/Pairs, AuthUser, Nullable AuthKey and Nullable AuthToken.
  • check-mark Included separate connection strings for the metadata, SQL server Staging, and SQL Server Production
  • check-mark As there can be change of Web Form over time, all form fields were stored as JSON objects.
  • check-mark Regex checking with reserved words was made flexible to remove whitespace and lack of brackets.
  • check-mark The powershell job will retrieve the single SQL column and convert the JSON object




Client Appreciated stating that, “Sivakumar Anirudhan & team worked with a high degree of professionalism despite the project being harder than first anticipated”

Let's develop your ideas into reality