Press "Enter" to skip to content

Category: Deployment

Building an SSMS Database Solution

Published 2022-01-04 by Kevin Feasel

Andy Leonard has a four-parter four us on database solutions in SQL Server Management Studio. Part one provides an introduction:

I like Microsoft Visual Studio a lot. I know some members of the team that developed Visual Studio, and they are scary-smart individuals who have forgotten more about developing software than I will ever know.

For some reason, I am not fond of SQL Server projects in Visual Studio. I believe the reason is that I am not familiar with the template. Please note I used the word fond intentionally. It’s an emotion. In this case, it’s all about me. I believe my emotion would change if I took the time to learn more about the Visual Studio SQL Server project template.

I continue to attempt to learn VS database projects. In the meantime, I prefer SQL Server Management Studio solutions.

Part two shows how to add a new query:

One solution is to add instrumentation to T-SQL scripts. I personally like to write T-SQL scripts that idempotent (a fancy way to describe “re-executable with the same results”). One way to write idempotent T-SQL is:

1. First check for the current state

2. Provide feedback (instrumentation) on the status

3. Provide more feedback on actions driven by the status (yep, more instrumentation)

Part three includes tables and views in the mix:

Click the “New Query” button in SSMS and add the following T-SQL:

Part four includes stored procedures:

Note the DDL to manage stored procedures is very similar to the DDL for managing views.

If all goes according to plan, the first execution of the s.i DDL T-SQL statement should generate the following messages:

Andy also shows how to use SQLCMD to create a proper deployment script.

Comments closed

Deploying SQL Server to Azure Container Instance via ARM

Published 2021-12-22 by Kevin Feasel

Rajendra Gupta builds an ARM template:

The Azure Resource Manager (ARM) template is a JavaScript Object Notation (JSON) file for deploying Azure resources automatically. You can use a declarative syntax to specify the resources, their configurations. Usually, if you need to deploy Azure resources, it might be a tiring experience of navigating through different services, their configurations. With the ARM templates, you no longer need to click and navigate around the portal. For example, you can use configure the template for Azure VM or Azure SQL Database deployment.

Click through for a step-by-step walkthrough. I will say, though, that I tend heavily to revise ARM templates the Azure Portal creates. They tend to make everything parameters, to the point where you get inundated with context-free decisions.

Comments closed

Building an MLOps Workflow with SageMaker and GitLab

Published 2021-11-26 by Kevin Feasel

Lauren Mullennex, et al, build out some pipelines:

Machine learning operations (MLOps) are key to effectively transition from an experimentation phase to production. The practice provides you the ability to create a repeatable mechanism to build, train, deploy, and manage machine learning models. To quickly adopt MLOps, you often require capabilities that use your existing toolsets and expertise. Projects in Amazon SageMaker give organizations the ability to easily set up and standardize developer environments for data scientists and CI/CD (continuous integration, continuous delivery) systems for MLOps engineers. With SageMaker projects, MLOps engineers or organization administrators can define templates that bootstrap the ML workflow with source version control, automated ML pipelines, and a set of code to quickly start iterating over ML use cases. With projects, dependency management, code repository management, build reproducibility, and artifact sharing and management become easy for organizations to set up. SageMaker projects are provisioned using AWS Service Catalog products. Your organization can use project templates to provision projects for each of your users.

In this post, you use a custom SageMaker project template to incorporate CI/CD practices with GitLab and GitLab pipelines. You automate building a model using Amazon SageMaker Pipelines for data preparation, model training, and model evaluation. SageMaker projects builds on Pipelines by implementing the model deployment steps and using SageMaker Model Registry, along with your existing CI/CD tooling, to automatically provision a CI/CD pipeline. In our use case, after the trained model is approved in the model registry, the model deployment pipeline is triggered via a GitLab pipeline.

Click through for the step-by-step guide on how to do this.

Comments closed

Serverless SQL Pool CI/CD via GitHub Actions

Published 2021-11-17 by Kevin Feasel

Kevin Chant reminds me I need to spend more time with GitHub Actions:

I want to cover one way you can do CI/CD for Azure Synapse Analytics serverless SQL pools using GitHub Actions in this post. For various reasons.

For a start, in a previous post I wrote about how you can CI/CD for serverless SQL pools using Azure DevOps. So, I thought I would balance things out and show how you can do the same thing within GitHub.

In addition to this, there have been a few discussions about using GitHub Actions instead of Azure Pipelines within the Microsoft Data Platform community recently. For example, the topic came up during the DataWeekender conference.

With this in mind, I want to show how easy it can be to migrate an Azure DevOps pipeline to GitHub Actions.

Click through for the example.

Comments closed

Serverless SQL Pool CI/CD

Published 2021-10-27 by Kevin Feasel

Kevin Chant doesn’t have time for manual deployments:

I want to cover one way you can do CI/CD for Azure Synapse Analytics serverless SQL pools using Azure DevOps in this post. Because I know it is a popular topic.

It’s related to my post about how you can create a dacpac for an Azure Synapse Analytics dedicated SQL pool using Azure DevOps. Since they are both based in the same service.

Plus, a while ago I wrote about the increase in demand for Data Platform automation. So, I really wanted to do a post about how you can do CI/CD for Azure Synapse Analytics serverless SQL pools.

Read on to learn how.

Comments closed

Deferred Name Resolution and Its Discontents

Published 2021-10-19 by Kevin Feasel

Kendra Little gives us a detailed rundown of deferred name resolution in SQL Server:

Have you ever tried to create an object in SQL Server, but it failed due to a missing table, column, or other dependency? If so, you’ve hit a case where SQL Server doesn’t offer ‘deferred name resolution’.

While these errors can be helpful when you’ve made a typo or accidentally used the wrong database, this can sometimes be a big hassle when you are…

– Deploying database code to set up a partial environment

– Deploying database code from version control to an empty database to ensure the code is valid

In this post, I walk through common scenarios and test whether deferred name resolution works or not.

Check it out for what is probably the most detailed look at the topic I’ve ever seen.

Comments closed

Architecting a Jenkins Replacement

Published 2021-10-18 by Kevin Feasel

Li Haoyi takes us through an internal Databricks tool for continuous integration:

Runbot is a bespoke continuous integration (CI) solution developed specifically for Databricks’ needs. Originally developed in 2019, Runbot incrementally replaces our aging Jenkins infrastructure with something more performant, scalable, and user friendly for both users and maintainers of the service. This blog post will explore the motivations behind developing Runbot, the core design decisions that went into it, and how we used it to greatly improve the experience of all the developers within the Databircks engineering organization.

It doesn’t look like the tool is available externally, but it’s an interesting read and helps understand some of the “why” behind the solution.

Comments closed

Index Maintenance and Pipeline Operation Scripts

Published 2021-10-14 by Kevin Feasel

Kevin Chant has a two-fer for us:

My first personal go-to script is one that has helped me out a lot over the years. Because I have used it a lot to identify missing indexes. I know there are a few different versions available online that you can use. However, I tend to use the one that comes with Glenn Berry’s Diagnostic Queries.

It is so easy to use. I’m not sharing the snippet of code on here because I want to encourage people to download the entire diagnostic script instead. Just download the script that is relevant for your version of SQL Server and search for ‘Missing indexes’.

Read the whole thing.

Comments closed