Database Deployment with Terraform - Modules (2022-10-10)

SQLServerCentral Newsletter for October 10, 2022 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Database Deployment with Terraform - Modules]( - [Rethinking SQL Explicit Transactions]( - [Post-migration Validation and Optimization Guide]( - [From the SQL Server Central Blogs - Scary Scalar Functions - Part Four: Your Environment]( - [From the SQL Server Central Blogs - Viewing errors with extended events]( Question of the Day - [The Percentile Comparison]( The Voice of the DBA  Daily Coping Tip Write down three things you can look forward to in the next month 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. Things I've Learned About the Cloud I do a little cloud work, but mostly I end up working with customers that are trying to build and migrate their systems to the cloud. The transition to cloud first took place quicker than I expected, though certainly the pandemic of the last 3 years has continued to accelerate the moves by many organizations. While not all systems move, for better or worse, many organizations consider the cloud for everything and then decide to accept or reject the notion for individual cases. That's the cloud first world I see. At a recent event, I had numerous people telling me their were mandated to move to the cloud in a lift-and-shift fashion, and many of the technologists weren't happy. That's a conversation for another day, but listening to them and others has taught me a few things. Some of these are experiences I've learned, and some are things from others, nuggets gained from hard experiences. First, lift-and-shift appears to be a way to get started in the cloud. Some people find this to be an end-goal when they can get systems running without ever worrying about hardware or facilities. While costs can be higher, flexibility and tax considerations can make this worth the effort. Others see this as a first step to moving towards Platform-as-a-Service (PaaS) products and rewriting software, which is a good way to approach the process, but that leads me to the second nugget. Everything takes longer to get settled in the cloud than you expect. Not that the cloud is slower to build and use resources. The opposite is certainly true as provisioning anything is fast, and upgrades (or downgrades) are incredibly flexible and quick. However, making the move, getting systems running to your satisfaction, decommissioning old systems, and more take longer. It seems most life-and-shift projects end up taking much longer than expected, and not all of this is the massive data transfers needed to move data assets. Often it's humans that can slow the process, whether through debate on decisions or just getting comfortable with cloud resources through testing. Things take longer. Maybe the last big thing that I've seen affect many companies is that we have to put better software development techniques and infrastructure configuration in place. The connection to resources is more complex and tenuous than it is on a LAN. We need to better architect software, really architect it as we were told to do so early in our careers with better error handling and retry logic. The cloud is very secure when you set it up right, but you no longer have the castle with high walls that you grew used to in a data center. Instead, you have a series of people and systems that are disparately connected and you need to ensure you protect each one along with the connections between them. Plus there are new tools to learn and the habits of using command line interfaces to build. I love the cloud. In many ways, I prefer it over building and managing things on premise. Not for everything, and not for things where I might lose productivity with slow connections. I certainly don't want developer laptops or workstations in the cloud, but having other systems there can remove a lot of the hassles of managing assets that we purchase, track, upgrade, etc. I don't know I'd ever want to buy another physical server in a company I owned, but I also know that at some scales, it might still make sense. The other thing I've learned about the cloud is there are a lot of unknown unknowns, as well as plenty of known unknowns. Tackling the cloud require a staff that wants to grow and learn with the platform, as well as one that knows when to use the resources they know best and don't add complexity or novelty just because someone wants to try something new. The cloud is worth considering, especially as it seems physical offices and data centers are becoming rarer and rarer in many organizations. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Database Deployment with Terraform - Modules]( wagner crivelini from SQLServerCentral Deploy resources in Azure using reusable code with Terraform modules. [External Article]( [Rethinking SQL Explicit Transactions]( Additional Articles from MSSQLTips.com This article looks at a simple example of using explicit transactions when modifying data in SQL Server tables. [Technical Article]( [Post-migration Validation and Optimization Guide]( Steve Jones - SSC Editor from SQLServerCentral SQL Server post migration step is very crucial for reconciling any data accuracy and completeness, as well as uncover performance issues with the workload. [Blog Post]( From the SQL Server Central Blogs - [Scary Scalar Functions - Part Four: Your Environment]( Zikato from StraightforwardSQL Scary Scalar Functions series overview Part One: Parallelism Part Two: Performance Part Three: The Cure Part Four: Your Environment Foreword In the previous posts, we have learned why Scalar Functions (UDFs) are bad for parallelism... [Blog Post]( From the SQL Server Central Blogs - [Viewing errors with extended events]( Kenneth.Fisher from SQLStudies A little while back I came across this post: Identifying failed queries with extended events. It has a script to ... Continue reading   Question of the Day Today's question (by Steve Jones - SSC Editor):  The Percentile Comparison Both PERCENTILE_CONT and PERCENTILE_DISC produce a percentile value from a set of data. One interpolates the result and one picks a value from the dataset. If I have this table: CREATE TABLE PercentileCont ( PCID INT NOT NULL IDENTITY(1, 1) , Category VARCHAR(20) , SomeValue INT); go INSERT PercentileCont (SomeValue, Category) VALUES (10, 'Finance') , (20, 'Finance') , (30, 'Finance') , (40, 'Finance') , (50, 'Finance') , (60, 'Finance') , (70, 'Finance') , (80, 'Finance') , (90, 'Finance') , (100, 'Finance') GO What happens with this query? SELECT category , SomeValue , PERCENTILE_CONT (.5) within GROUP(ORDER BY somevalue) OVER (partition BY category) EightyPercent_Cont , PERCENTILE_DISC (.5) within GROUP(ORDER BY somevalue) OVER (partition BY category) EightyPercent_Disc FROM PercentileCont GO 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) What's the CEIP? What is the CEIP service in SQL Server? Answer: The configure usage and diagnostic data collection for SQL Server service Explanation: This is the configure usage and diagnostic data collection service that collects information about how you use SQL Server. None of this information is collected: - Any values from inside user tables - Any logon credentials or other authentication information - Personal information Ref: Configure usage and diagnostic data collection for SQL Server (CEIP) - [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 - Administration [SQL Server Windows NT - 64 bit" in Task Manager: CPU high]( -  Hello ,  The SQL Server Windows NT - 64 bit" in Task Manager: CPU high 56,961 MB , the overall server CPU is now 95 % kindly advise the solution. [subscriber part of ALWAYSON]( - Hi. Publisher stop sending transaction to AOAG primary replica whenever AG failover. It seems push publisher does not re-directed to subscription database while AG failover. Replication monitor found error messages as below. please suggest me how resolve this issues? Command attempted: if COLUMNPROPERTY( OBJECT_ID('MSreplication_subscriptions'),'distribution_agent','AllowsNull') <> 1 BEGIN alter table MSreplication_subscriptions alter column distribution_agent sysname null […] SQL Server 2016 - Administration [Shrink database issue]( - In my database, I have few temp tables that total up to 50GB in size. I already removed the data from the table. When I set the shrinkdatabase in my maintenance plan, it runs successfully but doesn't shrink the database. I tried dbcc shrinkfile(1,truncateonly) and got the same result. However, from sql management studio when […] SQL Server 2016 - Development and T-SQL [Grant user permissions to a view but the base tables live in another database]( - I have two databases on the same server. I have a service account I want to have access to SELECT off some view in the "Staging" database. The view calls on tables in our Finance database. If I run the SELECT as myself or other "normal" users it works fine because those accounts have access […] [The ORDER BY clause is invalid in views]( - Hi, I'm trying to run the below SQL code but I'm getting this error: An error occurred while checking the query syntax. Errors: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. I'm still learning SQL so any […] SQL 2012 - General [SQL Server Licensing Question]( - I'm in the process of replacing a sql server 2012 with 2019. How do CALs work if pieces of the database are publicly accessible? How do you purchase CALs for that? Or do you just only buy the core model in that case? We only have 2 actual employees who will be working with the […] SQL Server 2019 - Administration [Change tracking - Date a row changed]( - We have change tracking enabled on one of our databases and I am looking for a query where I could get the timestamp/date on when a row changed. Any help is greatly appreciated. Thanks a lot. [Data replication tools and review]( - Hello expertise, Could you please suggest best tool for database replication? data replication on transactional basis and using for filtering option like DELETE transaction not replicate to target database due to data should maintain long term in target database instance. Thanks.   SQL Server 2019 - Development [How to run a query multiple times with different values in date]( - I'm very new to the sql and am wondering what is the easiest way to run the same query multiple times with different dates (ideally is the last day of each month). Can I create a loop so that I don't need to manually change the value in Date? Values in date are 31Jan2022, 28Feb2022, […] [Migration of simple SPs from 120 to 150 COMPATIBILITY_LEVEL issue.]( - Migration of simple SPs from 120 to 150 COMPATIBILITY_LEVEL issue. Execution time for the first time run of any SP is close to 3 min. After the first execution, it takes ms to repeated execution. The execution plan is identical in 120 and 150. Recompile did not help. SPs are sitting on wait: PAGEIOLATCH_SH for […] [Referencing a SQLCMD variable in a Post Deployment script using SqlPackage]( - Ive got a Database project running in JetBrains Rider. The project publishes a DACPAC to create or update a target database. Now I have a set of reference data scripts which run (in Create mode) and these are reference through the Script.PostDeployment which looks like this: :r .\LoadDeviceConnectionType.sql :r .\LoadUser.sql :r .\LoadConversionType.sql :r .\LoadChannelType.sql :r […] SQL Azure - Administration [encrypt the database in azure]( - How can we encrypt the database in Azure. Research story.  Thank you Amazon AWS and other cloud vendors [Version Controlling Lambdas]( - I know there are lambda versions. But I want to know if this can be done using GitHub, can we integrate it with CodePipeline and create something like a centralised repo for all our Python lambdas and then update those functions with every push. Reporting Services [SSRS Reporting]( - Anyone can give me a sample program on how to pass parameters from SSRS RDL Report to ASP.net Core or C#? I have a project with multiple users in multiple groups. The idea is for users to generate reports only from their group and they cannot generate reports from other groups. Like when a user […] [How to show currency symbol in column header]( - In our reports we display the currency ($) symbol in the column header for example Amount ($) or Points Value ($) etc. If we were to "globalize" our reports, how can we pick up the currency symbol from the Culture UI or the registry (for example S-1-5-18 Local system)? I tried this but it did […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -