Successfully Accelerated Deployment Process using Automation

Overview

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

Challenges

Ensure only valid data is processed and at any fallbase case abort the operations
Handle Go Separators as it will have issues in running sqlcmd
All jobs should be processed only within allowed hours
Track the objects the user changed and also check for permission before processing
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
Multiple files can be uploaded through the web form

Solution

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
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.
The powershell job will be executed every 30 minutes by the windows scheduler and Check time of day is within allowed hours
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.

Executed the contents of the files as T-SQL.
Log to DB table the output of the SQL Server
Various Abort cases such as errors, MaxRows exceeded, StrictTimeout exceeded, Permissions controls exceeded
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]
Verified the dba.DeployPermissions to ensure the user has only touched the appropriate objects.
We ensured Dba.SQLDeploy Permissions table have the AuthUser Info, SchemaName and ObjectName they have access to
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.
Included separate connection strings for the metadata, SQL server Staging, and SQL Server Production
As there can be change of Web Form over time, all form fields were stored as JSON objects.
Regex checking with reserved words was made flexible to remove whitespace and lack of brackets.
The powershell job will retrieve the single SQL column and convert the JSON object

Technology

Status

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