Install SQL Server using Powershell Desired State Configuration (DSC) (2023-05-29)

SQLServerCentral Newsletter for May 29, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Install SQL Server using Powershell Desired State Configuration (DSC)]( - [How Snowflake Creates and Detects Deadlocks]( - [Flyway and SSDT: Extracting a DACPAC from a Flyway-managed Database]( - [From the SQL Server Central Blogs - Query Store – Useful Tool for DBAs]( - [From the SQL Server Central Blogs - Pulling Azure Analysis Services logs from Azure Log Analytics into PowerBI using Kusto Query Language (KQL)]( - [Transact-SQL: The Building Blocks to SQL Server Programming by Gregory A. Larsen]( Question of the Day - [The Secure Enclave]( The Voice of the DBA  Happy Memorial Day 2023 Today is Memorial Day in the US, and I hope those of you in the states remember, celebrate, and enjoy the day. I'm camping with my family, cooking food while they ride horses. Maybe I'll get on one. Who know? For those of you outside the US, I hope you have a pleasant day and spend a moment or two thinking of a pleasant memory from your past. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Install SQL Server using Powershell Desired State Configuration (DSC)]( VishnuGupthanSQLPowershellDBA from SQLServerCentral This article shows the basics of using PowerShell Desired State Configuration to install a SQL Server. [External Article]( [How Snowflake Creates and Detects Deadlocks]( Additional Articles from MSSQLTips.com In this article, we look at how Snowflake handles deadlocks and the process of creating a deadlock and detecting a deadlock. [External Article]( [Flyway and SSDT: Extracting a DACPAC from a Flyway-managed Database]( Additional Articles from Redgate When you are integrating Flyway into an existing SQL Server SSDT development, you don't necessarily have to change everything at once. The development team might continue to use the SSDT tools, but Flyway will soon take over the deployments. This means that any automated processes will need to be able to handle both DACPACs and Flyway migration scripts with equal grace. In this article, I'll demonstrate how to automatically extract a versioned DACPAC from each new Flyway version of a database. [Blog Post]( From the SQL Server Central Blogs - [Query Store – Useful Tool for DBAs]( Rohit Garg from MSSQLFUN SQL Server is a robust and flexible database management system that offers a variety of features and capabilities to help organizations effectively manage their data. One of the key... [Blog Post]( From the SQL Server Central Blogs - [Pulling Azure Analysis Services logs from Azure Log Analytics into PowerBI using Kusto Query Language (KQL)]( sqlrunner from SQLRunner We have very large Analysis Services(SSAS) cubes with billions of records and hundreds of users so we need to be able to monitor the performance of queries. 95% of... [Transact-SQL: The Building Blocks to SQL Server Programming eBook by Gregory A. Larsen]( [Transact-SQL: The Building Blocks to SQL Server Programming by Gregory A. Larsen]( Greg Larsen from SQLServerCentral Transact SQL (TSQL) is the languaged used to query and update data stored in a SQL Server. This book, written by SQL Server Central and Simple Talk author Greg Larsen, will give developers an understanding of the basics of the TSQL language. Programmers will have the building blocks necessary to quickly and easily build applications that use SQL Server.   Question of the Day Today's question (by Steve Jones - SSC Editor):  The Secure Enclave When using Always Encrypted in SQL Server 2019, where is the secure enclave located? 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 Current Context in go-sqlcmd The new open-source sqlcmd, written in go-lang, uses contexts to run queries. You can run this to get the contexts that are set up on your system: sqlcmd config get-contexts However, this doesn't tell you which context is current, as you can see here: [sqlcmd context listing] How can I determine which context is active? Answer: use "sqlcmd config current-context" Explanation: Current-context is the sub command that returns the active context. You could have multiple contexts connected to the same server, or you could have multiple servers that are similar. Ref: go-sqlcmd - [ [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 2016 - Development and T-SQL [import from CSV - special chars]( - My data csv file sometimes comes in with special characters and French accents too (è ë à ç ) And then this special apostrophe in surnames e.g. O’Brien. They go in the database as this: O’brien I am using SSIS for import. Where do I need to make changes to allow these special chars? The tables have […] SQL Server 2019 - Administration [Upgrade to SQL2019 from 2012 and everything is slow - what to look for?]( - We have recently started a process of planning the upgrade of our four-node SQL2012 availability group to SQL2019. The main database is pretty large (a few TB) and we've spent years tuning the SQL to get the performance to where it needs to be such that huge complex procedures absolutely fly on SQL2012. We've had […] [DBCC CHECKDB error 2576 allocation errors]( - Getting the following after the execution of the DBCC CHECKDB:  Msg 2576, Level 16, State 1, Line 1 The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (3:17) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057617208115200 (type Unknown), but it […] [AD permissions updates CVE-2021-42291 and AO Clusters/GL]( - I have an SQLSERVER Always on Environment. In AD it's logging: The directory service detected an LDAP modify request for the folling object that normally would have been blocked for the following security reasons. The client included an nTSecurityDescriptor attribute in the add request but did not have explicit permissions to write one or more […] [Database snapshot, TDE, and sys.databases]( - Something I was looking at on a dev server where I was taking some snapshots and where we have TDE enabled and operational on the underlying database. Querying sys.databases shows the is_encrypted field as zero? Reading through the TDE documentation there is no exception that snapshots are NOT encrypted that I could find, but now […] SQL Server 2019 - Development [Query to Insert Data Based on CustomerID from one table to another]( - Hello All, Below is the my existing table structure from which I need to insert records to a new table I need a query to insert records into a new table for each customer for same date.  For example considering above data structure query should insert records into a new table as below based on […] [Encryption insert driving me mad]( - Hi all, I'm wondering if someone can help me with this, I have a table (very simple) CREATE TABLE [api].[Employee]( [EmployeeID] [int] NULL, [CreatedDate] [datetime] NULL, [Title] [varchar](10) NULL ) ON [PRIMARY] GO I've then gone and encrypted the Title column in SSMS using Randomized encryption type with a key in our Azure Key vault, […] [Most performant way to join a DATE to an INT]( - Hello and thanks in advance if you can help. I guess its somewhat acceptable in dimensional modeling to use the INT data type when creating a date dimension surrogate key. I can't say I agree with this but here we go. I need to join tables on DATE and INT columns. What is the most […] [Cumulative Sum by Groups]( - HEY, I HAVE TWO TABLES: CUSTOMERS AND USERS PER CUSTOMER I NEED TO CREATE TWO CUSTOMERS GROUPS: DVIR & RON - 'CRNTER GROUP', AND DANNY & DAVID 'OTHER GROUP'. AFTER THAT I NEED TO SUM THE NUMBER OF USERS FOR EACH GROUP. I STARED BY USING UNION ALL AND PARTITION BY: WITH "CUST-CTE" AS ( […] [Trigger Email After the record is inserted/deleted/updates from a table]( - Hi, I have following requirement. There is Sql server DB table OrderDetails. It has date, UserName , Details, OrderID columns. Case 1: I want to send email alert whenever any new record is inserted then email should be sent with below details. The new records date and time of insertion, UserName who inserted the record, […] [Triggers applied to primary and secondary tables causing rollback on primary]( - Apologies for the title, I'll explain more clearly. I have two tables and each has a trigger applied. The first table trigger is a simple audit style (AFTER INSERT/UPDATE) trigger that takes a copy of the modified record and inserts it into a Log Table. On the Log Table, I'm using another (AFTER INSERT/UPDATE) trigger […] [Calculate a sum based on time periods in CASE statement]( - Hello everyone, I have a situation where I am trying to calculate SUM(History Quantity) based on MIN Posted Date + specific number of weeks. For example, how many quantities were sold within the start date of '2022-05-22' + 8 weeks The code that I have now, but need to understand how to make CASE statement work because […] SSRS 2016 [Unable to connect SSRS]( - Hi, We are using SSRS 2016 version, it's running good and suddenly it's throwing exception as below when we are trying to generate reports, after restarting the SSRS again it's working good.  2023-05-18 22:07:42.9021|INFO|1|library|Dump on: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException Microsoft.ReportingServices.Modeling.InternalModelingException Microsoft.ReportingServices.ReportProcessing.UnhandledReportRenderingException 2023-05-18 22:07:42.9021|INFO|1|library|Do not dump on: Microsoft.PowerBI.ReportServer.WebApi.Catalog.CatalogAccessExceptionSystem.Threading.ThreadAbortException System.Web.UI.ViewStateExceptionSystem.OutOfMemoryException System.Web.HttpExceptionSystem.IO.IOExceptionSystem.IO.FileLoadException Microsoft.SharePoint.SPException Microsoft.ReportingServices.WmiProvider.WMIProviderException System.AppDomainUnloadedException 2023-05-18 22:07:42.9021|INFO|1|library|Minidump location: C:\windows\TEMP\  […] Design Ideas and Questions [Database Design Help]( - Hello, I am looking for some input on a database I am attempting to design for a video game. I have most of the design basically done, but I am having trouble finishing this last part. The basis of the database is around the entities "item", "fluid", "node", and "building". The schema related to these […] SQL Server 2022 - Development [Determining Time Worked Between Time Pairs During Certain Hours of Higher Pay]( - We have nurses pulling night shifts. They get bonus pay (more $$$ per hour) during the hours of 10pm and 5am. They check in and we capture their check-in time and check out time. How can I calculate the amount of time the worked during the overtime pay hours? I know SQL pretty OK - […]   [RSS Feed]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -