Advanced SQL Server installation using PowerShell DSC (2023-11-22)

SQLServerCentral Newsletter for November 22, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Advanced SQL Server installation using PowerShell DSC]( - [T-SQL Fundamentals: Controlling Duplicates]( - [Azure DocumentDB: Working with Microsoft's NoSQL Database in the Cloud]( - [From the SQL Server Central Blogs - Get Certificate Details for SQL Encryption In-Transit Using PowerShell – UPDATED]( - [From the SQL Server Central Blogs - Crosstream - Efficient Cross-Server Joins on Slow Networks in Python]( - [Practical Graph Structures in SQL Server and Azure SQL: Enabling Deeper Insights Using Highly Connected Data]( Question of the Day - [Further Defining the Window Clause]( The Voice of the DBA  Having Data Modeling Standards While working with a customer recently, they mentioned that they have certain standards for their objects. They require a PK, and it's the name of the table with _PK added. They also have some standards, like CustomerName vs. CustomerNames for various data items. In fact, they have enough that they built a tool to scan their database code to ensure that changes to the QA and UAT environments adhere to these modeling standards. I wonder how many organizations have formal standards. While I've often tried to set some naming guidelines, I often haven't seen anything (or created anything) formal enough to build a tool around. I would like to, and I think it's a good idea, but it's often something that isn't handled in advance. One thing I do think is important is to use singular naming. It's strange to me, as I've often thought of a table as a collection of Customers or Orders, but I get why singular makes more sense. I really started to embrace this more after a great design talk at one of the [Denver Dev Days](. In the talk, the speaker talked about naming a table as an entity, but then including the entity name in columns. For something like a Customer table, we might have CustomerID, CustomerFirstName, CustomerLastName, CustomerStatus, CustomerPreferredContact, etc. There could be FKs in here, such as ContactID or AddressID, that linked to the entries in those tables, but in general, using a name like this was fairly unique for each column, and it helped anyone know where the data resided. It was a lot of typing, and if you didn't have a tool like SQL Prompt, that might be really annoying, but I found myself seeing this as a very logical and easy-to-understand structure that even inexperienced developers might be able to follow and mimic. That doesn't mean there isn't a need to understand how we want to model structures like names, addresses, and more, but it did seem better than having FirstName in some fields and fname in others. CustomerFirstName or EmployeeFirstName makes a lot of sense. It also prevents ambiguous columns in queries. I do think that good data modeling standards are helpful in organizations, especially as we allow more and more people to make database changes and our teams grow larger. I would like to know how many of you reading this feel. Do you think strong data modeling standards are a good idea? Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Advanced SQL Server installation using PowerShell DSC]( VishnuGupthanSQLPowershellDBA from SQLServerCentral Introduction In the previous article, we learned how to install a SQL Server instance with basic parameters like InstanceName, Features, SourcePath, SQLSysAdminAccounts, etc using Powershell DSC. In this article, we'll discover how to enhance our SQL Server installation with additional configurations. We'll cover things like setting up service account credentials, custom installation directories, configuring TempDB […] [External Article]( [T-SQL Fundamentals: Controlling Duplicates]( Additional Articles from SimpleTalk When people start learning a new field, for example T-SQL, it’s tempting to spend very little time trying to understand the fundamentals of the field so that you can quickly get to the advanced parts. [Technical Article]( [Azure DocumentDB: Working with Microsoft's NoSQL Database in the Cloud]( Additional Articles from Dr. Dobbs Coding for the new NoSQL JSON database service that supports SQL queries. [Blog Post]( From the SQL Server Central Blogs - [Get Certificate Details for SQL Encryption In-Transit Using PowerShell – UPDATED]( Patrick Keisler from Everyday SQL In a previous blog post, I showed you how to use PowerShell to retrieve the details of the certificate being used for encryption-in-transit, aka TLS encryption. I have received... [Blog Post]( From the SQL Server Central Blogs - [Crosstream - Efficient Cross-Server Joins on Slow Networks in Python]( Bert Wagner from Bert Wagner Earlier this year I presented at PyCon 2023 in Salt Lake City, UT on the topic of "Efficient Cross-Server Data Joins on Slow Networks with Python". You can watch... [Practical Graph Structures in SQL Server and Azure SQL: Enabling Deeper Insights Using Highly Connected Data]( Site Owners from SQLServerCentral Use the graph table features in Azure SQL that were introduced in SQL Server 2017 and further refined in SQL Server 2019. This book shows you how to create data structures to capture complex connections between items in your data. These connections will help you analyze and draw insights from connections in your data that go beyond classic relationships.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Further Defining the Window Clause I have defined a window in SQL Server 2022 as follows: SELECT SUM(sod.OrderQty) OVER() AS Total FROM Sales.SalesOrderDetail AS sod WHERE sod.ProductID IN (710, 711, 712) WINDOW ob_OrderID_ProductID AS (ORDER BY sod.SalesOrderID, sod.ProductID) In the SUM() aggregate, I want to partition the data. Which of these statements is the way to make a more detailed window over() clause? A: SELECT SUM(sod.OrderQty) OVER(ob_OrderID_ProductID partition BY sod.SalesOrderID) AS Total FROM Sales.SalesOrderDetail AS sod WHERE sod.ProductID IN (710, 711, 712) WINDOW ob_OrderID_ProductID AS (ORDER BY sod.SalesOrderID, sod.ProductID) B: SELECT SUM(sod.OrderQty) OVER(ob_OrderID_ProductID partition BY sod.SalesOrderID) AS Total FROM Sales.SalesOrderDetail AS sod WHERE sod.ProductID IN (710, 711, 712) WINDOW ob_OrderID_ProductID AS (ORDER BY sod.SalesOrderID, sod.ProductID), pb_SalesOrderID_ob_OrderID_ProductID AS (PARTITION BY sod.SalesOrderID ORDER BY sod.SalesOrderID, sod.ProductID) C: SELECT SUM(sod.OrderQty) OVER(partition BY sod.SalesOrderID ORDER BY ob_OrderID_ProductID) AS Total FROM Sales.SalesOrderDetail AS sod WHERE sod.ProductID IN (710, 711, 712) WINDOW ob_OrderID_ProductID AS (ORDER BY sod.SalesOrderID, sod.ProductID) D: SELECT SUM(sod.OrderQty) OVER(partition BY pb_SalesOrderID ORDER BY ob_OrderID_ProductID) AS Total FROM Sales.SalesOrderDetail AS sod WHERE sod.ProductID IN (710, 711, 712) WINDOW ob_OrderID_ProductID AS (sod.SalesOrderID, sod.ProductID), pb_SalesOrderID AS (sod.SalesOrderID) 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) Minimal Stats Creation If I want to create statistics in a SQL Server 2022 database, what do I need to include in the CREATE STATISTICS command? Answer: The name for the statistics, the table on which to create them, and the column(s) on which to create them Explanation: The minimal items in the CREATE STATISTICS statement are: - statistics name - table - column(s) The CREATE STATISTICS will not generate a name. Auto-created statistics will get auto names. Full scan or sample are not needed and the statement will sample at a rate that is determines. Ref: CREATE STATISTICS - [ [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 [UPDATE query is running slow and is causing blocking]( - Hi All, We have an UPDATE query which is causing a lot of blocking in QA env. Its taking more than 4 hours and keep running and we see a waittype as "(151ms)PAGEIOLATCH_SH:QA4_ORS:3(*)". Worst part is, the app team has scheduled this stmt at multiple times of the day. Even before the 1st UPDATE completes, […] SQL Server 2016 - Administration [SQLPS (and SQL Agent PowerShell steps) don't work after Windows upgrade to 2019]( - We were running SQL2016 on Windows 2012R2 without issues. We've just had an OS upgrade to Windows 2019 and now we can't run SQLPS.exe or any SQL Agent PowerShell steps. SQLPS.exe returns: import-module : File E:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1 cannot be loaded because running scripts is disabled on this system. For more information, see […] [How to solve "The operating system returned error 21"]( - Starting point was a power break with open files etc. in windows. I really do not know where to start.  TITLE: Microsoft SQL Server Management Studio ------------------------------ Backup failed for Server 'DESKTOP-MB6NMGV\EDVARD'. (Microsoft.SqlServer.SmoExtended) For help, click: ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The operating […] SQL Server 2016 - Development and T-SQL [The DATETIME data type question]( - I know that the DATETIME data type is no longer an ANSI/ISO Standard and I have heard the Microsoft plans to eventually stop supporting it but I cannot find any mention of this out on the internet, besides the few comments I came across which I cannot find now. So does anyone know if Microsoft […] SQL Server 2012 - T-SQL [AttachDbFileName in Connection String in Vb.Net code gives errors.]( - While using AttachDbFileName in the connection string to attach database to localdb,The following error occurs Error: The logical database file 'Common WM_log' cannot be found. Specify the full path for the file. Could not open new database 'C:\USERS\SOURCE\REPOS\WINDOWSAPP20\WINDOWSAPP20\BIN\DEBUG\COMMON.MDF'. CREATE DATABASE is aborted. An attempt to attach an auto-named database for file C:\Users\source\repos\WindowsApp20\WindowsApp20\bin\Debug\common.mdf failed. A database […] SQL Server 2019 - Administration [Concerns about database access after migrating to new server - CNAME record]( - Hi all, I'm planning to migrate a database from SQL Server 2008 (Windows Server 2008 R2) to SQL Server 2019 (Windows Server 2019), I've ran the Data Migration Assistant to assess the move and it came back without any issues, I'm also planning on using this tool for the actual migration. There are currently multiple […] [Recovery Point Objective (RPO)]( - Does the following backup strategy satisfy the RPO of 24 hours. these are SIMPLE recovery. no transaction FULL every sunday at 12 am. DIFF daily at 2am  [SPID 0 is blocking some SPID.]( - This is the 1st time I have seen some sessions blocked by SPID 0. Does anyone know what this zero session is? SQL Server 2019 - Development [How to copy from one to another almost identical db]( - I have inherited a demo db called demo_1 and a live db with +300 tables. They are almost identical, but there has been done some work on the live db, which has not been done on the demo. Its missing primary fields, some keys, a few identity fields.  Its pretty easy to copy the […] [Split json column]( - I have a column which has the following data in it for example {"changedAttributes":[{"logicalName":"line1","oldValue":"street1","newValue":"street2"},{"logicalName":"city","oldValue":"City1","newValue":"City2"},{"logicalName":"phone","oldValue":"123","newValue":"345"}]} There maybe 100 logicaname values not just the three here How do I write in sql a query to extract the logicalname, old value and new value for each logical name that exists in the column Lets presume the table is […] Integration Services [How to add a variable to Data Flow task?]( - We have a Data Flow task to load a table from one server to another. But the destination has one extra column which I have to pass from a package variable. How I can add it? In a Mapping tab it has an drop-down option against this column, but how I can insert my […] SQLServerCentral.com Website Issues [HTML on the published article is broken]( - Hey team, I need help with fixing the HTML on my published article. SQL Window Functions Series: RANK() and DENSE_RANK() It is all broken and is providing no value to the reader. Please fix it ASAP SQL Server 2022 - Administration [Table with high unused space]( - I have a table where over 50% has unused space  How can I reclaim this space back? Index maintenance? SQL Server 2022 - Development [Cannot able to login to mysql as localhost]( - I have a query in mysql I have created a account with test@localhost and i want to access the particular database where I have given access to the db like all necessary permissions and all these my sql accounts are hosted in a server and now when i try to login in local mysql workbench […] [Update CSV string with lookup table]( - I need to replace word(s) in CSV string in table tblA, in the [original] column, by [word] from lookup table tblB. The new CSV string will be stored in tblA table, in the column [modified]. If the [tblB].[synonyms] column doesn't contain the word from CSV in tblA, then the original word will be kept. For […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -