Showing posts from 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 AS DB , D.create_date , SO.edition , SO.service_o

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 mu

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

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 code