Stairway to SQL PowerShell Level 9: Objects For Everyone (2023-03-22)

SQLServerCentral Newsletter for March 22, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to SQL PowerShell Level 9: Objects For Everyone]( - [PostgreSQL Constraints: Learning PostgreSQL with Grant]( - [Improving the Quality of your Database Monitoring]( - [From the SQL Server Central Blogs - Call to Action for Female Speakers to Submit to EightKB]( - [From the SQL Server Central Blogs - Children at Technical Conferences Part II]( - [Pro Encryption in SQL Server 2022: Provide the Highest Level of Protection for Your Data]( Question of the Day - [Cloud Diagrams]( The Voice of the DBA  Daily Coping Tip Appreciate your hands and all the things they do for you 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. What's a Code Smell? This editorial was originally published on Oct 5, 2015. It is being re-run as Steve is out of town for VS Live. We all have a variety of code patterns and practices that we follow. Most of them were probably picked up along the path of our career. A suggestion from a colleague. A piece of sample code that solved a problem. A performance tuning trick that stopped our phone from ringing. These methods of learning are the way that most of us actually grow our skills over time. However just because we learned something, or because a technique solved a problem doesn't mean it was a good piece of code. In fact, often the code we may think works well might not be the most efficient way to structure the code. Many developers have learned this over the years, as they read about new techniques that are more efficent, elegant, or just simpler. Kent Beck and Massimo Arnoldi coined the term code smell years ago, as a way of noting the development patterns and practices that  lead to poorly written, or difficult to maintain code. There have been other attempts to document practices which are not recommended, though the success is probably limited as many developers continue to build on poorly written code rather than refactoring and cleaning their codebase over time. Simple Talk  and Phil Factor published [a SQL Code Smells ebook]( awhile back, trying to document the signs of poorly written T-SQL. The book is good, with guidance about particular patterns that can cause you problems over time. The items aren't meant to be rules, but rather guidelines that you adhere to unless you have a good, specific reason that you can justify to others. I ran into a code smell recently where a developer noted that their application depended on a specific database name in order to work.  That's not in the ebook, but I think it's easily one I'd avoid. My connection should determine the database, not the application itself. I know there may be exceptions here, but in general, application code shouldn't be dependent on a particular name. I'd urge you to pick up the ebook (it's free) and keep it handy. See if any of the items listed are habits you might have picked up over time and not realized that they are, in fact, poor practices. I would also recommend you peruse [Aaron Bertrand's Bad Habits to Kick]( series, as a way of improving your own code. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to SQL PowerShell]( [Stairway to SQL PowerShell Level 9: Objects For Everyone]( Ben Miller from SQLServerCentral.com This level will show how to create objects including a login, a database, a filegroup, a file a database user and then we’ll end with a table and an index. [External Article]( [PostgreSQL Constraints: Learning PostgreSQL with Grant]( Additional Articles from Redgate One of the most important aspects of data management is the ability to ensure that the data in your database is well defined and consistent. Some aspects of that are ensured through the relational data structures you design. Another piece of control is using the correct data type. Then, we get to constraints. A constraint is a way to validate data prior to adding it to your database. This is one more tool in the toolbox that helps you maintain good data. [External Article]( [Improving the Quality of your Database Monitoring]( Additional Articles from Redgate Database monitoring should be as simple as possible, and yet still allow the users to drill into sufficient detail to be able to recommend a fix to the problem. The trick is to adopt a layered or 'tiered' approach. [Blog Post]( From the SQL Server Central Blogs - [Call to Action for Female Speakers to Submit to EightKB]( Tracy Boggiano from Database Superhero’s Blog EightKB is a wonderfully ran conference by my friends Mark Wilkinson (T | B), Anthony Nocentino (T | B), Andrew Pruski (T | B), and The post Call to Action... [Blog Post]( From the SQL Server Central Blogs - [Children at Technical Conferences Part II]( Tim Radney from Tim Radney - Database Professional Nearly 10 years ago I wrote a blog post about having my son tag along at various conferences I was speaking at. So much has changed in the past... [Pro Encryption in SQL Server 2022]( [Pro Encryption in SQL Server 2022: Provide the Highest Level of Protection for Your Data]( Additional Articles from SQLServerCentral This in-depth look at the encryption tools available in SQL Server shows you how to protect data by encrypting it at rest with Transparent Data Encryption (TDE) and in transit with Transport Level Security (TLS). You will know how to add the highest levels of protection for sensitive data using Always Encrypted to encrypt data also in memory and be protected even from users with the highest levels of access to the database. The book demonstrates actions you can take today to start protecting your data without changing any code in your applications, and the steps you can subsequently take to modify your applications to support implementing a gold standard in data protection.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Cloud Diagrams Can I create database diagrams in an Azure SQL Database or an Azure SQL Managed Instance? 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) SQL Server 2022 Restore and DBCompat I have a new SQL Server 2022 instance. What is the earliest version of a SQL Server backup from which I can restore and what is the lowest db compat level? Answer: Earliest backup is SQL Server 2005 and db compat level 100 Explanation: The earliest backup you can restore from on SQL Server 2022 is SQL Server 2005. The lowest db compatibility level, however, is 100. This is the SQL Server 2008 level. Ref: - Copy Database With Backup and Restore - - Alter database compatibilit level - [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 distribution agent issues in Production]( - Hi all, We have publisher, distributor & 2 separate subscribers running on different servers. The same publication is in sync with one server but error for second subscriber. Having issues with distribution to subscriber. The distribution agent is not running. It’s not providing any error messages. Changed the distribution agent priories to skip errors but […] SQL Server 2017 - Development [Hexa 2 decimal 20 digits]( - Hi, Need to convert a hexadecimal to decimal/numeric with 20 digits, but due to bigint limitation i get a negative value that doesn't correspond to the exact value, is there any way to overcome this limitation? select CAST(CONVERT(VARBINARY, 'D002336AE90E2D93', 2) AS bigint) Result: -3458145029597549165  The convertion should give the value 14988599044112002451. Thanks in advance. SQL Server 2016 - Administration [system_versioning = on/off permissions]( - All, I would like to give a user permission to set system_versioning on or off on a table. I know that one way to do this is to give them control permission on the table. However I think that also gives them the ability to grant permissions and I don't think I can set a […] SQL Server 2019 - Administration [SQL Server Mirroring with TDE]( - I am trying to mirror a TDE Encrypted Database. (SQL 2019). When I try to start mirroring with the GUI it says "Database not configured for mirroring". Same if I try to use ALTER SET PARTNER code. I created a TestDB not encrypted and the mirror starts fine using the same steps. (restore with norecovery […] [After removing db from AG the secondary goes into Not Syncronizing]( - SQL Server 2019: So I have some databases that are no longer needed that were in an Availability group. I removed them from the AG and the database goes into Not Syncronizing instead of restoring. It was successfully removed from the primary and two of the secondaries but the third secondary it went into Not […] [SQL Server 2019 Standard Edition - Cannot Create Distributed Availability Group]( - According to Microsoft from KB5016394 - Cumulative Update 17 for SQL Server 2019, Distributed Availability Groups can be enabled on SQL Server Standard Edition. I'm trying to set up the distributed availability group and unfortunately I always get the error message "Msg 19513, Cannot create a distributed availability replica for availability group 'AGDIST2'. Distributed availability […] [Automation of t-sql executions requests.]( - We get a bunch of tasks/requests to run t-sql scripts to update views or stored procs or bad data. Does anyone know of a server product that can automate the execution of these t-sql and do full logging and error reporting of execution and email the requesting user of the error? Basically a server application […] [AG has disappeared in SSMS]( - I had a WSFC issue, resolved I think. I wasn't here and various tries have been made to resolve leaving things in a messy state. Now my AG only exists on 1 server. Not on the the other. Node 1 = AG in resolving state. dbs = not synchronising. Not available. Node 2 = AG […] SQL Server 2019 - Development [SSIS Error]( - Hi everyone I am getting an SSIS error. I want to import a CSV into SS table. I am using SSIS for this. I am using the Flat File Source -> OLE DB Destination. I am getting below error. Any ideas on how I can fix this? TITLE: Package Validation Error ------------------------------ Package Validation Error […] SQL Azure - Development [Can I connect Azure Synpase to an on-prem data source via Power BI Gateway]( - Hello, I heard you can connect from Azure Synapse to on-prem data sources by connecting through a PowerBI Gateway, however I have not found any documentation on this. I'm experimenting with a variety of methods to get data from On-prem sources to Synapse and wanted to check how this performed compared to other methods. Can […] Reporting Services [Cannot create a connection to data source (rsErrorOpeningConnection)]( - We have two servers with SSRS and SSAS. Both are running 2008R2. On SSRS server there are quite a few data sources using SSAS databases. All reports based on these data sources are working when I connect to Report Manager on SSRS server but when I try to open them on any workstation I am […] [How to prevent a series of symbols from splitting across lines]( - I have some text like this: "word word word 123(56) word word" The "123(56)" happens to be at the end of the line and is getting broken up. It is being displayed like this: "word word word 123 (56) word word" But I want it like this: "word word word 123(56) word word" Note that […] General [Triggers In Sql]( - I'm just getting started with SQL Server triggers and I'm having trouble with this problem: CREATE TRIGGER software_on_install_fill_in ON software FOR INSERT, UPDATE AS --declare the names and types of variables you will use DECLARE @software_pack char(4), @software_tagnum char(5), @software_datetime datetime, @software_softcost numeric(10,2) BEGIN --set the variables to values SET @software_pack = (SELECT software.PACK FROM […] Suggestions [Sort Order on Latest Topics Messed Up?]( - The Sort Order for Latest Topics seems to have become randomized. One would expect posts to be sorted in descending order by last post date. SQL Server 2022 - Development [Stop deployment of SQL DACPAC if there is DROP statements]( - Hi all, We have a SQL project to be deployed to an Azure SQL server. We are looking for s specific scenario where we can stop the ADO pipeline if we have DROP/ALTER statements in deployment scripts or DACPAC.? Basically I am looking for a way to validate the DACPAC before deploying to production for […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -