Provisioning Azure SQL Database with Failover groups using Terraform (2020-06-18)

 SQLServerCentral Newsletter for June 18, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Provisioning Azure SQL Database with Failover groups using Terraform]( - [Unlocking the value of frequent database deployments]( - [Hands-On with Columnstore Indexes: Part 1 Architecture]( - [From the SQL Server Central Blogs - SQL Homework – June 2020 – DML Triggers]( - [From the SQL Server Central Blogs - Data Visualization, Context, and Domain Expertise]( Question of the Day - [CTE in Tempdb]( The Voice of the DBA  Daily Coping Tip Say positive things in your conversation with others today I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. Why Billing Will Be Part of Our Job I am old enough to remember when many large corporations implemented chargebacks. Essentially, each internal department was charged for their usage of IT systems, similar to how we are charged in the cloud. It was a mess, and individual departments had to answer for excessive charges. I don't know that any department ever lost service, but their shared of the IT budget was certainly discussed in management meetings. These days more and more of us are going to move systems to the cloud, and some of those systems moved will be databases. We may not move all our systems, but we will move some, and the ongoing cost of those systems is something that we will periodically be asked to justify. In the past, we rarely dealt with billing, usually just when specifying and procuring new hardware or requesting a VM. Moving forward, I can see us spending time every quarter, or likely every year, analyzing the resources we are paying for and then determining if we are overspending. Or, if you're proactive, perhaps you'll start looking at costs and performance, [as Brent did](, and try to write better code to reduce your outlay every month by more efficiently using resources. Have you built in-efficient designs? Are you pulling back too much data with SELECT * from a table or view? Are you building code that recreates [the N+1 problem](? We've known for years that writing good code to interact with databases can prevent scale problems and overloaded hardware, even with large workloads. Many case studies over the years show SQL Server backing extremely large databases and busy workloads. Multi-TB databases with thousands of concurrent clients are common these days, and often we find that workloads at clients would easily be run on existing hardware with some better code. For some of us, the refocus on the cost of systems may give us some help in pushing developers to write better code from the beginning, learn to use efficient patterns and avoid anti-patterns, and perhaps even get more time to tune systems. However, those are skills that many of us need to improve, learning how to find code that can be improved, and what techniques to use to do so. We have [lots of articles]( that help, as to [Brent Ozar](, [Erik Darling](, [sqlSkills]( and others. There are lots of ways to improve your skills, and they can help improve your bottom line. If you work on them. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Provisioning Azure SQL Database with Failover groups using Terraform]( ramakant from SQLServerCentral Get a basic introduction to Terraform by deploying and removing SQL Servers and a failover group to Azure. [Unlocking the value of frequent database deployments]( Additional Articles from Redgate Database deployments are on the rise, with more organizations releasing weekly or daily. But this does not always result in more value being delivered. Steve Jones advises the steps you can take to unlock the value of frequent releases in this blog. [Hands-On with Columnstore Indexes: Part 1 Architecture]( Additional Articles from SimpleTalk Microsoft introduced Columnstore with SQL Server 2012 and have improved the feature in every version since then. You may be wondering why they are different than traditional indexes and how they work. In this series, Edward Pollack explains the architecture of Columnstore indexes. In future articles in the series, he will describe best practices for Columnstore. From the SQL Server Central Blogs - [SQL Homework – June 2020 – DML Triggers]( Kenneth.Fisher from SQLStudies Triggers. Blech. Triggers are a really really cool feature of SQL Server, that are continuously misused and cause all kinds ... Continue reading From the SQL Server Central Blogs - [Data Visualization, Context, and Domain Expertise]( Meagan Longoria from Data Savvy I recently posted a graph to twitter and asked people to explain it. Let’s look at the graph. The graph is from Fitbit. It shows the number of steps...   Question of the Day Today's question (by Steve Jones - SSC Editor):  CTE in Tempdb When does a CTE use tempdb? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Getting the Group Counts I have this dataframe in Python: sales.head() Date Day Month Year Customer_Age Age_Group Customer_Gender Country State Product_Category Sub_Category Product Order_Quantity Unit_Cost Unit_Price Profit Cost Revenue 0 2013-11-26 26 November 2013 19 Youth (<25) M Canada British Columbia Accessories Bike Racks Hitch Rack - 4-Bike 8 45 120 590 360 950 1 2015-11-26 26 November 2015 19 Youth (<25) M Canada British Columbia Accessories Bike Racks Hitch Rack - 4-Bike 8 45 120 590 360 950 2 2014-03-23 23 March 2014 49 Adults (35-64) M Australia New South Wales Accessories Bike Racks Hitch Rack - 4-Bike 23 45 120 1366 1035 2401 3 2016-03-23 23 March 2016 49 Adults (35-64) M Australia New South Wales Accessories Bike Racks Hitch Rack - 4-Bike 20 45 120 1188 900 2088 4 2014-05-15 15 May 2014 47 Adults (35-64) F Australia New South Wales Accessories Bike Racks Hitch Rack - 4-Bike 4 45 120 238 180 418 Which of these will give me count of distributions for each of the values in Age_Group? Answer: sales["Age_Group"].value_counts() Explanation: The value_counts() method will help here. Ref: pandas.Series.value_counts() - [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Development [PIVOT query]( - Hi I have this query using PIVOT SELECT * FROM ( SELECT a.Mun, l.Name, a.NumberOfRooms FROM Acco as a INNER JOIN look as l ON a.AccoTypeId = l.id INNER JOIN reg as r ON r.AccoId = a.Id WHERE r.RegDate IS NOT NULL AND r.RegDate <> '' and a.Mun like 'CB%' ) as t PIVOT( SUM(NumberOfRooms) […] SQL Server 2016 - Administration [SQL server 2016 SP2 error]( - Hi All,  am trying to update my 2016 SQL server instance to the SP2. the current version is Microsoft SQL Server 2016 (SP1-GDR) (KB4505219) - 13.0.4259.0 (X64) Jun 15 2019 19:20:12 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows 10 Enterprise 6.3 (Build 18363: ) SP2 setup file : SQLServer2016-KB4549825-x64.exe for some […] [SQL Trace Not Capturing Database Events....EXEC, INSERT, UPDATE...]( - I want to see what Stored Procedures are run when users enter data in a 3rd party product we use. I would also like to see INSERTS & UPDATES to tables. I set up a trace that I have used elsewhere with success, but in this case, it's not capturing any of the events I […] [SSRS "My Subscriptions" Fails to Load if TLSv1.0 Is Disabled on the Server]( - We have noticed in the past that if we disable TLSv1.0 in HKey_Local_Machine\System\CurrentControlSet\Control\SecurityProviders \SCHANNEL\Protocols\TLS 1.0\Server\Enabled (set to 0), most functionality still works in SSRS, but "My Subscriptions" fails to load, giving the not-very-descriptive error: "An error has occured. Something went wrong. Please try again later." Upon changing the setting to 1, "My Subsriptions" works as […] [SQL Server authentication]( - I see my sql server is NTLM authentication enabled. I am looking to connect using my mac pro laptop now and I am not able to connect to sql servers as they use windows authentication without kerberos so I can't connect from non windows machines. I am thinking to use sql account instead of enabling […] [adding a Select statement to an existing table]( - I have this table abc. when I script it out it contains Create table and Alter procedure commands. Now I want to add a Select statement to this table, so when I script out the next time it should consist of Create table, Alter procedure and the new Select statement Is there a way to […] SQL Server 2016 - Development and T-SQL [In general how do you know which fields to join on between 2 tables?]( - I know this is a broad question. I have 2 tables (no primary/foreign keys). In general, with 2 tables, how do you know which fields to join on and who many fields do you use? From what I understand, the more fields you join on the more narrow you are making your data. But do […] [Comparing 2 large tables row by row and date comparision]( - We have a functionality where in we need to process a large file containing around 1.5 million rows. We insert the data from the file in a secondary table and once this insert is complete, we compare it with our parent table and only insert rows which have changed. The parent table contains around 2.5 […] [Project EVERYTHING is assigned to me. Can I measure Everything via T-SQL? How?]( - Is it reasonable to roll-up ALL metrics from Query Store to measure 'everything'? and then compare 1 sp (input param, SP name) metrics with 'Everything else' on server (or should it be by db?) one stored proc watch is easy via Query Store. But aggregating ALL THE REST ('Everything') for even just one day is […] SQL Server 2019 - Development [Extracting a Date Properly from a String.]( - If i have a string of the following format master_20200613220000.bak Then How do i display the Date as 2020-06-13 22:00:00.000 Please Help.  [Hi all, just registered with SQLServerCentral.]( - Hi all, I am looking for some one to join me in learning and developing a small project. I have some background on MS Sql Server 2012. If there is some one there with a project or we could find one and learn together, please let. I believe the best way to learn quickly is […] [Two Strings compare using t-sql]( - Declare @Intstr1 varchar(100) Declare @Intstr2 varchar(100) Set @Intstr1 = '1,2,3,4,5,6,7,8,9'  -- This will NOT contain duplicates Set @Intstr2 = '4,6,6,7,7' -- This string may have duplicates but surely exists in str1 Need a t-sql for finding numbers from Str1 which are NOT in Str2 Please help. Expected output.. @output = '1,2,3,5,8,9'   General [ISNULL clause with a condition]( - Hi, I've got the following code to select the period where I have active data: { FIXED : MAX(IF NOT (ISNULL([Qty in tn])) THEN [Period] END)} The problem I have is that the Qty in tn is only null when another column [Data Type] = ACT. How do I modify the above code to take […] Anything that is NOT about SQL! [SSC Traffic Declining ?]( - Are there fewer posts & replies on SSC these days ? It seems like it. Or are they just spread over more sub forums of the various SQL versions ? COVID-19 Pandemic [Daily Coping 15 Jun 2020]( - Today’s tip is to find a joyful way of being physically active (indoors or out).   [RSS Feed]([Twitter]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -