Build a pipeline in AzureDevOps for Sql Server on prem to run sql scripts

ONLINECAST

3 min read

·

04-10-2024

8

0

Share

Streamlining SQL Server On-Prem Script Execution with Azure DevOps Pipelines

Managing and executing SQL scripts for your on-premises SQL Server database can be tedious and prone to errors. Manually running scripts can be time-consuming and lead to inconsistencies. Azure DevOps pipelines offer a robust solution for automating this process, making it efficient, reliable, and easily reproducible.

The Problem:

Many organizations struggle with the following challenges when managing SQL scripts for their on-premises SQL Server databases:

• Manual execution: Repetitive and error-prone manual script execution.
• Lack of traceability: Difficult to track script changes and execution history.
• Version control issues: No central repository for script management.
• Inconsistent environments: Different development, testing, and production environments leading to deployment issues.

The Solution: Azure DevOps Pipelines

Azure DevOps pipelines provide a powerful platform to automate the entire process of building, testing, and deploying your SQL scripts. This eliminates manual tasks, ensures consistent execution, and improves overall database management.

Let's Build a Pipeline:

Here's a step-by-step guide to build an Azure DevOps pipeline for executing SQL scripts against your on-premises SQL Server:

1. Create a New Azure DevOps Project:

• Log in to your Azure DevOps organization and create a new project.
• Navigate to Pipelines and choose Create Pipeline.

2. Select the Repository:

• Choose the repository containing your SQL scripts. This could be a Git repository on Azure DevOps or another source control provider.

3. Configure a YAML Pipeline:

• Select the YAML template "Azure Pipelines" and paste the following code into the YAML editor:

trigger:
- main

pool:
  vmImage: 'ubuntu-latest'

steps:
- task: DownloadSecureFile@1
  inputs:
    secureFile: 'your-secure-file-name' # Name of the secure file containing SQL Server credentials
    targetFolder: '$(Build.ArtifactStagingDirectory)'

- task: SqlServerDeploy@1
  inputs:
    serverName: 'your-sql-server-name'
    databaseName: 'your-database-name'
    sqlFilePath: '$(Build.ArtifactStagingDirectory)/your-sql-script.sql'
    authenticationType: 'SqlAuth'
    userName: '$(your-sql-server-username)'
    password: '$(your-sql-server-password)'
    command: 'Execute'

- task: PublishPipelineArtifact@1
  inputs:
    targetPath: '$(Build.ArtifactStagingDirectory)'
    artifactName: 'sql-script-output'

4. Create a Secure File:

• Create a secure file within your Azure DevOps project. This file will contain sensitive information like your SQL Server credentials.
• Add the following content to the secure file:

sqlServerName=your-sql-server-name
sqlDatabaseName=your-database-name
sqlUsername=your-sql-server-username
sqlPassword=your-sql-server-password

5. Replace Placeholders:

• Replace the placeholders in the YAML pipeline with your actual values.
• Make sure to name your secure file correctly in the DownloadSecureFile task.

6. Run the Pipeline:

• Click "Save and Run" to execute your pipeline.

7. Monitor the Execution:

• Observe the pipeline execution log for any errors or warnings.
• The pipeline will download the secure file, connect to your SQL Server, execute the script, and publish any output artifacts.

Insights and Enhancements:

• Environment Variables: You can use environment variables instead of storing credentials in a secure file.
• Script Management: Maintain multiple SQL scripts in your repository and easily manage their version control.
• Conditional Execution: Use conditions to execute specific scripts based on the environment (development, testing, production).
• Deployment Tasks: Expand your pipeline to include other database deployment tasks, such as data migration and schema updates.

Additional Considerations:

• Ensure your on-premises SQL Server is accessible from Azure DevOps.
• Implement appropriate security measures for your SQL Server credentials.
• Use the SqlServerDeploy task for other SQL Server operations like database creation, schema changes, and data backups.

Conclusion:

By utilizing Azure DevOps pipelines, you can streamline your SQL Server script execution process, improve reliability, and enhance your overall database management practices. This automation not only saves time and effort but also ensures consistency and traceability, making your database operations more efficient and less prone to errors.