Tuesday, August 22, 2023

Introduction to IaC: Deploying Data infrastructure to Azure using Terraform (Part 1 - Intro and prerequisites)

Introduction

One integral part of modern Continuous Integration and Continuous Development (CI/CD) pipelines is the ability to create the underlying infrastructure as part of the pipeline in a consistent and repeatable manner.
Infrastructure as Code (IaC) offers this capability, allowing us to automate and deploy multi-cloud infrastructure as definition scripts.


This approach helps us to reduce time invested in provisioning the infrastructure, as well as reducing human errors or misconfigurations.  The infrastructure can also be redeployed to other environments knowing that the configuration will be the same each time.
Although Azure offers ARM templates and AWS offers CloudFormation, these are just for its respective cloud provider.


Which option do we have if we need an enterprise-grade solution that supports multi-cloud environments and can integrate with our CI/CD pipelines?


Terraform is a declarative, open source IaC tool that can deploy to multiple cloud providers and offers a great documentation and community support. Is one of the most used IaC tools in the world because of its reliability and ease of use.


It can be used locally in multiple operating systems and using Terraform Cloud. It can be integrated into our CI/CD pipelines , as for example Azure DevOps.


Terraform scripts are created in Hashicorp Configuration Language (HCL) or JSON. For this article we will be using HCL.
Basic terraform files have the .tf extension, usually we need at least one main.tf file to work.
The infrastructure status at any given point is stored in the Terraform State, it helps to keep control of what should be created, updated or destroyed.


The most common objects of Terraform are:

Backend: where the terraform state is stored, can be standard or enhanced. Each type can be stored locally or remotely.
Providers: Target where we want to deploy our infrastructure, it uses each cloud provider’s API and RPC calls to communicate with it.
Modules: Offer simplified ways to deploy some common solutions that enclose multiple resources, it helps reduce coding efforts and offers code reusability, can be developed by official cloud providers, Terraform, or by any individual.
Resource: Object to be deployed, it can have multiple parameters and configuration options. If no optional parameter is provided, the defaults configured in the provider or module are used.

There are a lot more parts of Terraform files and configuration, but that is the basic information we need for now.

Once you have your scripts ready, the usual Terraform lifecycle looks like this (in very simplified way):
 

We will see how to use each one in our example, but for now, let me briefly explain each one:

Terraform init: Allow us to initialize our project, it downloads the required providers and modules, or updates them to the latest required version.
Terraform validate: Parses the script file and verifies the integrity of the code, it highlights syntax errors or undefined properties or values.
Terraform plan: It compares the current Terraform state, and determines which changes, if any, should be performed. It displays the proposed information for review and can be saved to a plan file.
Terraform apply: It deploys the required changes to the cloud provider and updates the state file with the latest changes. It also allows us to refresh the Terraform state with the latest changes from the cloud provider using the refresh-only option.
Terraform destroy: It allows us to destroy the infrastructure partially or totally. If we run the apply command after a destroy, all resources will be created again.


Terraform concepts and lifecycle are a more complex topic so is not possible to cover everything on this article. I provided just enough information so we can proceed with a simple Azure SQL deployment.

On this article we will use Terraform on our local machine to deploy common Azure data resources. We will start with a very basic script so you can understand the fundamentals on how IaC works with Terraform.

On next articles, we can use VCS like GitHub and use Terraform Cloud to implement versioning and more secure workflow.

Prerequisites

For the most basic deployments we will need the following:
    • Note: if you are using windows, just add the terraform.exe path to PATH environment variable.
  • Install the CLI for the provider you want to use, for this example, we will use Azure CLI.
  • Authenticate to Azure CLI, so you can store your credentials locally: az login
  • If you do not have it already, install VSCode, we will use it as our IDE.
  • Install these 2 extensions as it will make your life easier:
    • Hashicorp Terraform
    • Hashicorp HCL
 
Once you have the prerequisites, we are ready to work on our first deployment.
 
 
---------------
 
To make things clear, we will split this into multiple posts, so stay tuned for the rest of the parts.
I will update this post as well as soon as the other parts are released.
 
 

Wednesday, January 13, 2021

Install Python on Windows


Python is the most famous development language today.

This is because is open-source, scalable and robust. It can run in almost any device with a processor (or microprocessor, we will see that in coming posts) and is very simple to use and learn, even if you don't have any programming backgroud or experience.

Start using it is very simple and you can learn the basics in the official site.

 

Installing Python

For windows systems, you can download the latest version here.

Once downloaded, open the executable file as administrator.

Then, for the uses we will give on the articles on this site, please install it this way:

  • Make sure you have selected the add Pyton to PATH option - This will allow us to run the Python engine from multiple sources (As is intended for use on multiple applications).
 
  • Make sure you install Python for all users - This will allow usto run the script commands.
 
  • Select PIP feature - This is used to install adittional libraries and functionalities, one of the main reasons for using Python and benefit from it.

 

Run the Installer as administrator and select the Customize installation option:

 
 
In my case since I am just upgrading, the install for all users option cannot be changed, but make sure to select it the first time you install it.

 
 
If everything is ok, you will see this message after the setup completes.


Test it!

Ok, but how do we actually use it? 

There are plenty of integrated development environments (IDE) for Python, depending on what are you planning to do with it.

But just to make sure is working ok, just open a CMD window and type python, then you can start using it right away.



Ok, once you have checked everything is working as expected, a good IDE to start working with Python right away is to use VSCode, you can download it here.

Then, add the Python extension.


 

Python files ends with the .py extension, so for VScode, just create any new file, and then save it as .py extension, the IDE automatically will load the Python extension and you can start working with it right away!

 


Happy coding! Stay tuned for the next things we will buid using Python.

 



Monday, July 20, 2020

Checking Azure SQL DB service tier via T-SQL

If you have to manage or work with an Azure SQL database and want to know what service tier the database is currently operating, but you don't have access to the Azure subscription or CLI.

Or you want to know the status of the service tier after a scale up or scale down, directly from the database; you can do it via T-SQL

Just query the sys.database_service_objectives DMO to obtain this information, this will give information about the service tier, and also will tell you if the database is part of an elastic pool or not.

Basic usage (in the context of the database you need the information):

SELECT * FROM sys.database_service_objectives;

This will return the following information for the current database:

I am running on a General Purpose tier with 1 VCore


What if you want the information for all the databases created?

Just change the context to the master database and execute the following query:

SELECT D.name AS DB
    , D.create_date
    , SO.edition
    , SO.service_objective
    , SO.elastic_pool_name
FROM sys.databases D
INNER JOIN sys.database_service_objectives SO
    ON D.database_id = SO.database_id;

Then you will be able to see each database tier and related elastic pools (if configured).


You can always change your Service objective via T-SQL issuing the ALTER DATABASE command.
More information here.


Monday, March 30, 2020

Administration Basics: Point in time recovery

Nowadays, data is a precious asset for companies today. If you are a database administrator (by decision or by mistake) or simply you are the "IT guy," you have the mission of guarantee all the data is backed up and accessible for recovery.

Trust me, even when you could think you have the more reliable hardware on the planet, or you have multiple database replicas around the globe, anything can happen (a user deleting an entire schema by mistake, an application updating the wrong records, some process crashing, a lot of things can happen).
So trust me and don't question me, just backup all your databases regularly.

The Backup/restore strategy is a broad topic, so for this post, we will focus on the restore activity, so if you don't know how to back up a database yet, you can start here.


What do we need to know?

I like to think of the SQL backups like a pyramid, like this:

Pyramid of backups

For restoring your database to a particular time, you must perform a series of restores:
  • Full backup BEFORE the date you want. 
  • If available, the closest differential backup (this can be omitted if your diff backup is after the date you want or you don't have it).
  • Each T-log backup in order until the required date (we will see an example later).
It is essential to know that only the FULL database recovery model supports point-in-time recovery; with Bulk-logged, you are only allowed to restore the entire transaction log backup.

Set up our example

For this example, I have created a database named DB_RestoreDemo in SQL 2019. On it, I have a table called dates_demo with no relevant information, just a date and a varchar field for this example.

USE [DB_RestoreDemo]
GO

CREATE TABLE [dbo].[dates_demo](
 [id] [bigint] IDENTITY(1,1) NOT NULL,
 [date] [datetime2](7) NOT NULL,
 [text] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_dates_demo] PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY]

GO

I populate the table with some records (save it as we will use the same insert later):

USE DB_RestoreDemo
GO

INSERT INTO dbo.dates_demo
VALUES(GETDATE(),replicate('|',rand()*100))

GO 50

Then take a full backup:

BACKUP DATABASE [DB_RestoreDemo] TO  
DISK = N'C:\SQLServer\DB_RestoreDemo_full.bak' 
WITH COMPRESSION
GO

After that, execute the INSERT statement above, but replace the number after the GO with a significant number, so it takes a lot of time to complete, in my case I will execute it 10 million times:



While it is running, open a new query window and perform some Differential and Transaction log backups as you prefer (in any order and as many times you want), just remember to change the filenames each time to have them quickly identified:


-- Differential backup
-- Remember to change the filename each time

BACKUP DATABASE [DB_RestoreDemo] 
TO  DISK = N'C:\SQLServer\DB_RestoreDemo_diff_1.bak' 
WITH  DIFFERENTIAL, COMPRESSION
GO

-- Transaction Log backup
-- Remember to change the filename each time

BACKUP LOG [DB_RestoreDemo] 
TO  DISK = N'C:\SQLServer\DB_RestoreDemo_1.trn' 
WITH COMPRESSION
GO

Once you are happy with the backups taken, you can stop the INSERT query or let it run until it finishes... depends on you.

We have what we need for our example now.


Let's do it!


Open the table and select a random record, we will try to restore the database as close as possible to the date of that record.

SELECT * 
FROM dbo.dates_demo
ORDER BY [date];

I have selected this random record; we must guarantee that the last record of the table is this one (or very close in case the millisecond resolution include other records):
Id: 613834, date: 2020-03-30 01:49:50.5366667



Then, go to the folder where your backups are located, and check the modified date of the files, you have three options depending on which record you have selected:


  • Option 1: The time you want to restore is before the full backup taken: Congratulations! You know what data loss is now, good thing this is a demo. You cannot go to that point in time if you don't have the required backups.
  • Option 2: The date you want to restore is after any backup was taken: You can make another transaction log backup.
  • Option 3: The date you want to restore is in range, just locate the file with the closest creation date after the date you want.

In my case, is this file:

 

Based on the pyramid we saw before, this must be my restore strategy:


  1. Restore 1 FULL backup - DB_RestoreDemo_full.bak
  2. Restore 1 DIFF backup - DB_RestoreDemo_diff_1.bak
  3. Restore 1 Transaction Log backup - DB_RestoreDemo_2.trn

We will restore these backups to a new database DB_RestoreDemo_NEW, so we don't overwrite existing.

Please note that except for the last one, all restore operations must be done with the NORECOVERY option.

For the last transaction log file restored, we will use the STOPAT option; this option allows us to restore a database to a specific date (our example), a particular LSN, or a transaction mark (we will see that on another post).

NOTE: for this example, we used only INSERT statements without explicit transactions, but in a real-life scenario, the STOPAT option with a date parameter will restore the database to the latest committed transaction to that point in time.

 Once we have our restore strategy in place, we create the restore scripts:

USE [master]
GO

RESTORE DATABASE [DB_RestoreDemo_new] 
FROM  DISK = N'C:\SQLServer\DB_RestoreDemo_full.bak' 
WITH  FILE = 1,  
MOVE N'DB_RestoreDemo' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\DB_RestoreDemo_new.mdf',  
MOVE N'DB_RestoreDemo_log' 
TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\DB_RestoreDemo_new_log.ldf',  
NORECOVERY;

GO

RESTORE DATABASE [DB_RestoreDemo_new] 
FILE = N'DB_RestoreDemo' 
FROM  DISK = N'C:\SQLServer\DB_RestoreDemo_diff_1.bak' 
WITH  FILE = 1, 
NORECOVERY;

GO

-- Because of the resolution on the decimals in datetime type
-- the increments are .003, .007 and .000, so the closest one
-- to the date we want .537 is .540
RESTORE LOG [DB_RestoreDemo_new] 
FROM  DISK = N'C:\SQLServer\DB_RestoreDemo_2.trn' 
WITH  FILE = 1, STOPAT = N'2020-03-30T01:49:50.540';

GO

 We navigate to the restored database, and if everything is ok, our selected record should be the last one (or very close to):



We have successfully restored a new database to the required point in time. A few things to have in mind:

  • You can specify the STOPAT option with a LSN or a transaction mark; this is useful if you have a critical process you can write your application to "mark" it for easy restore.
  • You just need the latest FULL and DIFF backup to the required date, but need all the TLog backups after that DIFF (or full if you don't have it).
  • In case the date you want to restore is included on a DIFF backup, you will need to ignore that DIFF backup and restore all the TLOG backups since the last FULL, including the first one after the DIFF backup.
  • For Bulk-logged recovery mode, you cannot perform point-in-time recovery, but you can perfectly restore log backups, but the entire content is restored.

And remember to always test your backups by restoring them regularly, with this you can achieve three things:
Confidence in the restore process, this will help you to remain calm under a stressful situation.
You can prepare the scripts you need to do it before anything bad happens.
You can estimate the time you need to do it, and check if that aligns with your RPO and RTO.

Friday, January 31, 2020

New Youtube Channel

Last year I noticed I was blogging way less than I should, in part because of new projects: work, personal stuff, local community activities, university projects... you name it.

As for 2020's goals, I want to be more active on my blog and try new stuff as well. One of the new things I am doing is starting to record videos (my first experience doing this, even when I have presented a couple of webinars, this is a whole new skill to learn).

According to some studies (don't ask me the accuracy of those), young people prefer videos over text, so I am giving it a try!

What am I using?


For now, my setup is very simple (and cheap):

  • Blue Yeti microphone - Best mic to create videos or do webcasts (check reviews), connect and start using it.
  • Logitech webcam C615 - not sure if I want to record myself!👽
  • Logitech G231 headset - Cheap but excellent sound and response, very comfortable also.
  • Camtasia Studio - To screen record and video editing, I think everybody uses it anyway.
  • My Dell laptop - That works just fine for what I need, no super fancy or powerful Mac required at this point.
  • Samsung Monitor - To be able to record screen and see my notes
  • Microsoft  Wireless desktop 3050 - Keyboard and Mouse, nothing special here, just cheap and do their job. One cool thing is it has keys at the top where you can program custom actions or key combinations (I have key combinations and custom apps mapped to them)
  • Logitech Spotlight- Not needed, but useful if you want to stand up and want to change slides. I feel more comfortable recording while standing up, it feels more natural for me, maybe when I have more experience I can sit down.

A picture of my setup:

Need a better desk! hahaha



What is my plan?


For now, learn how to create & edit videos, Camtasia has some tutorials to get started where you can learn the basics on how the product works.
They offer some free courses as well, to learn how to create quality content.

After that, creating short videos with particular topics related to data platform and cloud, and see how people respond to them. Maybe create a series of tutorials in the future.

I plan to do the videos in Spanish for now since it is my mother language, and I want to reach our local user group. Will consider to add English subs or mix English/Spanish.

Ok, but once you have your videos created, you have to share them somewhere, I have chosen Youtube because of its simplicity and popularity, also you can upload to the platform directly from Camtasia.

My youtube channel


I choose to create a new channel with my name, I had a previous one I created some years ago, but the name was not so professional (if you know what I mean! 😂)

I plan to do one series of short videos (around 1 minute) called "SQL en un minute" (SQL in a minute), and another set for longer videos dedicated to tutorials or product reviews.

You can visit it here (you are encouraged to subscribe! 🤓)

And this is my first video, let me know what you think!




Friday, January 3, 2020

T-SQL Basics: using OUTPUT

You are working on a database development project, and you need to obtain the records before/after an INSERT, DELETE, UPGRADE or MERGE Statement to present it to the user or do any work with that data.


A common approach that I see a lot and involves a lot of code to obtain the before/after information can be the following (maybe some IF and TRY...CATCH also) :


DECLARE @id INT = 1;

SELECT PostalCode
    , ModifiedDate
FROM Person.Address a
WHERE a.AddressID = @id;

UPDATE Person.Address
SET PostalCode = 95012, 
    ModifiedDate = 'Jan 02 2020'
WHERE AddressID = @id;

SELECT PostalCode
    , ModifiedDate
FROM Person.Address a
WHERE a.AddressID = @id;

The results will be something like this (before and after):


And the execution plan will look like this, each extra SELECT statement adds more workload to our server (independently of the resources used) :



Even when the code is easy to read, but if you use this pattern over all your codebase, maintain it can become difficult if you have to change object names or implement it on another system.

T-SQL language provides the OUTPUT clause, that allows you to retrieve information from a DML statement in the same batch.

NOTE: OUTPUT clause will return rows even when the statement presents an error and is rolled back, so you should not use the results if any error is encountered.

Sample usage is as follows:

-- Values After INSERT INTO --
INSERT INTO <MyTable>
OUTPUT INSERTED.*
VALUES (@a, @b);

-- Values After INSERT FROM --
INSERT INTO <MyTable>
OUTPUT INSERTED.*
SELECT * FROM <MyOtherTable>;

-- Values Before/After UPDATE --
UPDATE <MyTable>
SET <myField> = @value
OUTPUT 
 DELETED.<myField> as [ValueBefore],
 INSERTED.<myField> as [ValueAfter]
WHERE id = @key;

-- Deleted records After a DELETE --
DELETE <MyTable>
OUTPUT DELETED.*   
WHERE id = @key;


If we refactor the first example to use OUTPUT we obtain this code:

DECLARE @id INT = 1

UPDATE Person.Address
SET PostalCode = 95012, 
    ModifiedDate = 'Jan 02 2020'
OUTPUT 
 DELETED.PostalCode, 
 DELETED.ModifiedDate, 
 INSERTED.PostalCode, 
 INSERTED.ModifiedDate
WHERE AddressID = @id

Less code and also easy to follow and maintain, and the results will be something like this:


What if we take a look at the execution plan? we can see that this time just one sentence is executed (as expected):


Of course, performance can vary depending on how many rows are you retrieving back to the user, current workload, index and table design that is very own to your environment, but the more you know, the better you can adapt each situation to achieve the optimal performance.

Speaking of which, what if we have a lot of fields or records to work with, it is possible to store the output results on another table? of course, you can, as we can see in this other example:

-- To review the correct data has been deleted
-- we can output to a temp table
DELETE
FROM Person1
OUTPUT DELETED.*
INTO PersonBackupInfo
WHERE EmailPromotion = 0;

-- we can review/recover data if needed
SELECT * 
FROM PersonBackupInfo;

We can implement a deleted data review/rollback logic to our application to protect it from user errors (the typical Ooops queries) and protect our databases from an unnecessary point in time restores.

Like everything in life, nothing is perfect, and there are some remarks you should consider to implement it on your applications, so be sure to read it before using it.

Thursday, December 26, 2019

Xmas tree in T-SQL

This is a very simple version of a Xmas tree using T-SQL, also is a good example to learn how recursive CTE works.

So, if you are in front of the computer with little work to do, take a look at the code, maybe you can improve it further!


/* Draw a simple xmas tree using recursion 
   🎄 Happy holidays!              */
WITH XmasTree
AS (
 SELECT 
  CAST(REPLICATE(' ', 16) + '^' AS VARCHAR(50)) AS t,
  0 AS lvl
 UNION ALL
 SELECT 
  CAST(REPLICATE(' ', 15 - lvl) + '/' + REPLICATE('*', 2 * lvl + 1) + '\' AS VARCHAR(50))
  ,n.lvl + 1
 FROM XmasTree n
 WHERE lvl < 16
 )
SELECT t as [Happy Holidays]
FROM XmasTree;

And this is the final result:



HAPPY HOLIDAYS!

How I Built RAG for My Accounting App on Bedrock and pgvector

The first version of my accounting assistant could not find a single purchase of "comida" in a database full of lunches, misse...