Install SSMS using Powershell DSC (2024-01-31)

SQLServerCentral Newsletter for January 31, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Install SSMS using Powershell DSC]( - [Gracefully Handle Errors using TRY CATCH Logic in SQL Server Stored Procedures]( - [From the SQL Server Central Blogs - Using Spark Connect from .NET]( - [From the SQL Server Central Blogs - A Primer on Attaching Databases with PowerShell Method AttachDatabase]( - [SQL Server 2022 Administration Inside Out]( Question of the Day - [Dacpac Data]( The Voice of the DBA  Mind Over Milkshake (Thoughts Matter) Last year I went to [THAT Conference]( in Wisconsin. It was a fun event, very community and family-friendly, and I enjoyed it. So much so that I recently went back to the Texas event in January. It's more developer-focused, but it does have some data related sessions. I recommend this conference if you're looking for some fun training and want to combine that with a family vacation. Your kids will love it. In any case, I watched a keynote talk that referenced an NPR article, called [Mind over Milkshake](. It's an interesting look at how the food labels affected people's bodies. It's not definitive and I wouldn't make any drastic changes based on this, but it is an interesting read on the idea your mind and thoughts can influence your body. I've heard about the effect of placebos in the past, we well as attitude on healing, so this makes some sense. I don't know to what extent this would change how I manage my health and medical care, but I do think a similar idea is important in my work with databases. I see lots of people who have a negative attitude towards learning, change, or even adopting new/better ways of doing things. So many people, whether workers or management, get stuck in a rut and want to stay there. Or they don't feel empowered to change. It's why I find the cultural part of DevOps way more challenging in organizations than the technology part. In some of my management positions, I've often challenged workers not to bring problems, but to find solutions. I want them to view the issues we face, the things that go wrong, as opportunities to improve, not set-in-stone problems that we gripe about. I know many chronic issues recur regularly. I also know that in any organization, there can be resistance to change, and a "we've always done it that way" attitude. However, adopting that for yourself is how things continue to linger on (or get worse). Your mindset can make a huge difference in how you approach situations, including how much stress you feel from the environment. I don't advocate change for change's sake, but I do look to critically evaluate if something works well or can be improved. I've also learned to ask for change and that being turned down doesn't mean that nothing changes. Or nothing ever well, or even this thing won't change. It often means that this particular thing can't change, or that it can't change now. I have also learned to separate this request from another request for a different change. The key is often to analyze the solution, prepare a good reason why something should change, and present this un-emotionally. I know when I approach things as an opportunity life is better. Even if I don't make a difference, or I don't like the outcome, I feel better about it. Try it, and you might feel the same way. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Install SSMS using Powershell DSC]( VishnuGupthanSQLPowershellDBA from SQLServerCentral Learn how you can install SMSS automatically using PowerShell DSC as this series continues. [External Article]( [Gracefully Handle Errors using TRY CATCH Logic in SQL Server Stored Procedures]( Additional Articles from MSSQLTips.com Learn how to capture errors in SQL Server stored procedures and gracefully handle errors using Try Catch coding in your stored procedures. [Blog Post]( From the SQL Server Central Blogs - [Using Spark Connect from .NET]( Ed Elliott from Ed Elliott's Sql Developer Blog Introductory Ramble Spark Connect In July 2022, at the Data and AI summit, Apache Spark announced “Spark Connect,” which was a way of connecting to Apache Spark using the... [Blog Post]( From the SQL Server Central Blogs - [A Primer on Attaching Databases with PowerShell Method AttachDatabase]( SQLPals from Mission: SQL Homeostasis A Primer on Attaching Databases with PowerShell Method AttachDatabase Attaching a SQL Server database using PowerShell can be straightforward. Here's an effective and easy approach using the... [SQL Server 2022 Administration Inside Out]( Site Owners from SQLServerCentral Dive into SQL Server 2022 administration and grow your Microsoft SQL Server data platform skillset. This well-organized reference packs in timesaving solutions, tips, and workarounds, all you need to plan, implement, deploy, provision, manage, and secure SQL Server 2022 in any environment: on-premises, cloud, or hybrid, including detailed, dedicated chapters on Azure SQL Database and Azure SQL Managed Instance.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Dacpac Data Can a .dacpac file created with sqlpackage.exe /extract include data? 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) High Execution Plan Performance Variance I want to see the top plans that have a lot of performance variation in their execution plans in SQL Server 2022 for a new database I created and deployed. What's the easiest way to do this? Answer: Click the report under the Query Store in SSMS for the database Explanation: There are a number of reports in SSMS for Query Store. One of these is the Top 25 Queries with high performance variation. You can click this as Query Store is enabled by default for databases in SQL Server 2022. Ref: Best practices for monitoring workloads with Query Store - [ [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 [recovery model for model database]( - Hi All, Is it good to leave "model" database recovery model as "FULL:" or change it to simple? is it just to ensure the newly created user databases to have FULL recovery model or is there any other good reason ? I see log backup pending for model database that's why asking this question.  […] SQL Server 2016 - Administration [List Procedure That Creates A Table]( - I've been trying to develop a process that will take the input of a table name and trace that back to a stored procedure that creates, updates, or inserts into it. I've been piecing together queries using the sys.sql_dependencies, sys.sql_expression_dependencies, dbo.sysobjects, and dbo.syscomments tables but I'm not able to get the results that I'm looking […] [Local admin cannot view/launch failover cluster manager]( - hi all. I am new to this and hope someone can help me. We have created a failover cluster for our SQL. When I log in using a Domain Admin user (Domain\user), I can view the cluster manager. But when I log in using a local admin (local administrator group), I cannot/unable to view the […] SQL Server 2016 - Development and T-SQL [SQL Query requirements]( - Hi All  create table #temptable (idValue int) insert into #temptable values(1) insert into #temptable values(2) insert into #temptable values(3) insert into #temptable values(4) insert into #temptable values(5) insert into #temptable values(6) insert into #temptable values(7) insert into #temptable values(8) insert into #temptable values(9) insert into #temptable values(10) In this above Query I need two […] [what parts of an ssas tabular cube should i version in tfs/dev ops]( - Hi, i want to start versioning the critical portions of my ssas tabular cube in tfs. What should i archive the bim file? SQL 2012 - General [SQL Server 2012 - not accepting new connections on occasion ...]( - SQL Server 2012 running on Windows Server 2012 R2. Microsoft SQL Server 2012 (SP4-GDR) (KB4583465) - 11.0.7507.2 (Intel X86) This occurs occasionally, maybe once every 2 weeks or so, the DB server does not accept new connections. Existing connections are OK, but it will not accept new ones. It requires a restart of the SQL […] [Database mail quit working]( - About a week ago, database mail stopped working. The error message from the log is "Cannot send mails to mail server (Failure sending mail)." the rather generic error message. This is happening both on test emails as well as other production emails I restarted the server, tried temporarily disabling the firewall, recreated the db mail […] SQL Server 2019 - Administration [Size of DB Log File]( - hi I was looking at the size of my db. The log looks really big. It is supposed to be that big? here is the query i used to get this information: SELECT mdf.database_id, mdf.name, mdf.physical_name as data_file, ldf.physical_name as log_file, db_size_MB = CAST((mdf.size * 8.0)/1024 AS DECIMAL(8,2)), log_size_MB = CAST((ldf.size * 8.0 / 1024) […] [SQL Server 2012 - not accepting new connections on occasion ...]( - SQL Server 2012 running on Windows Server 2012 R2. Microsoft SQL Server 2012 (SP4-GDR) (KB4583465) - 11.0.7507.2 (Intel X86) This occurs occasionally, maybe once every 2 weeks or so, the DB server does not accept new connections. Existing connections are OK, but it will not accept new ones. It requires a restart of the SQL […] [Error delete old audit File]( - Hello , I recently set up a SQL database audit to record a few sessions. The audit configuration is well done with retention of 2 files Everything works correctly except that in the error log I have this message which indicates that SQL server can no longer delete the old audit file because it seems […] SQL Server 2019 - Development [Need help with Pivot query or other mechanism to report below data]( - Hi, I've got below dataset (this is sample dataset) I would like to show dataset following way[…]  srv01 srv02 srv03 srv04 srv05 2024-01-22 10:46:00.000 10 20 30 40 50 2024-01-22 10:47:00.000 10 20 30 40 50 Integration Services [Could not find file]( - Please assist. I don't know if the actual file does not exist( it does not exist in mentioned folder) or if the file path in the variables are the problem. Am i jumping the gun here? [Excel Source - Master Data Reference [14]] Error: System.IO.FileNotFoundException: Could not find file '\\ZADURFS01\Departments\Finance\Business Intelligence, Reporting and Analytics (BIRA)\Source […] Suggestions [What is Data Cleansing and what is the importance of Data cleansing for B2B]( - Hello Recently when I was talking to my friends I came to know about data cleansing, they told me that data cleansing will be helpful for B2B database, but I did not know about data cleansing and it is not useful for B2B business. How important it is for you. Please guide me regarding data […] SQL Server 2022 - Administration [Directory CleanupDir is filling up our drive]( - We recently upgraded to SQL Server Analysis Services 2022 and noticed the CleanupDir has almost 3 million files. The location is H:\MSSQL\MSAS15.SSRSPRO\OLAP\Data\CleanupDir. We have tried to manually delete the files, but no progress. There are files older than 30 days. Two questions. What is this directory used for in the SSAS instance? Is there a […] SQL Server 2022 - Development [SQL to insert row with RefNum to be the next numeric number]( - Hi all, I need some help with SQL. I want to create test data by inserting new rows into an existing table using TSQL. Each row has a RefNum column which is in integer and isn't the primary key. RefNum of the new row should be max. value of RefNum in the table + 1 […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -