Stairway to ScriptDOM Level 3 - Finding Patterns in the Abstract Syntax Tree (2022-06-08)

SQLServerCentral Newsletter for June 8, 2022 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to ScriptDOM Level 3 - Finding Patterns in the Abstract Syntax Tree]( - [Why Database Monitoring Tools are Important for Senior Leaders]( - [Optimizing my.cnf for MySQL performance]( - [From the SQL Server Central Blogs - Power BI connectors to Azure Synapse]( - [From the SQL Server Central Blogs - Installing the mssql-cli on Ubuntu 22.04]( Question of the Day - [EXCEPT]( The Voice of the DBA  Daily Coping Tip Send a friend a photo from a time you enjoyed together. 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. One or Many Databases These days I see more and more customers adopting a sharding or federation approach to their database, often putting each client in a separate database. This is [one of two common ways to design a multi-client database system](, and both of these have their pros and cons. If you read the link above, you will see some of the advantages of using one over the other, which might help you decide how to architect your own system. There are lots of disadvantages to both as well. I find that customers who put all clients in one database often have issues with noisy neighbors, where one customer might cause problems for other customers with the load they place on the system. They also struggle with scaling up when they find the workload is too large for a single server. There is also the need to manage security better as each query needs to ensure it checks for authorized access to the appropriate set of data, something which is even more complex when you have clients or users that need access to multiple sets of data. On the other hand, in the age of DevOps, I find customers who struggle with deploying code to hundreds, or sometimes thousands, of databases. Yes, I have customers who literally have the same schema across low thousands of databases and then wonder why a deployment takes hours. Many of these clients also struggle with [schema drift](, which can complicate their ability to even successfully deploy across their estate. There are certainly other challenges with these two architectures as well. I've worked with both paradigms, and I find that no matter which you choose, there are going to be situations that make you wish you had chosen the other design. Ultimately, I think learning more about both architectures and experimenting a bit with each is the best way to learn what might work best in your situation. I also have a client that has a hybrid of these two. Their schema is built as all clients in one database, but they have separated out certain clients into another database. Actually, I think they have 3 databases, each with a mix of clients. Their DBAs have written lots of routines to ensure they can "move" a client from one database to another. This is an interesting strategy, and it's good in that their deployments are fairly simple and quick, they can manage drift, and they can move noisy neighbors to a new database (or new instance). On the other hand, they still have worries about security and ensuring users only see the data they have the authorization to view. Is there an approach you prefer? Stick all clients in one database or shard your systems out into multiple databases. I'm curious today if one architecture has worked better for you and why. Let us know in the comments. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Technical Article]( [Stairway to ScriptDOM Level 3 - Finding Patterns in the Abstract Syntax Tree]( Diligentdba 46159 from SQLServerCentral Learn how you can query for patterns in the Abstract Syntax Tree to analyze your code. [External Article]( [Why Database Monitoring Tools are Important for Senior Leaders]( Additional Articles from Redgate Matt Gordon is a Microsoft Data Platform MVP and the Director of Data and Infrastructure at Rev.io. In this short video, he talks us through the 3 key reasons database monitoring tools are essential for him as a Director, and how they help him to lead a team successfully and productively. [External Article]( [Optimizing my.cnf for MySQL performance]( Additional Articles from SimpleTalk Database systems often need tuning for best performance. Lukas Vileikis explains to how to optimize my.cnf for MySQL performance. [Blog Post]( From the SQL Server Central Blogs - [Power BI connectors to Azure Synapse]( James Serra from James Serra's Blog When using Power BI and pulling data from Azure Synapse, you will use the “Get Data” feature in Power BI. There are now three connections that you can use... [Blog Post]( From the SQL Server Central Blogs - [Installing the mssql-cli on Ubuntu 22.04]( dbafromthecold@gmail.com from The DBA Who Came In From The Cold I really like the mssql-cli tool. I use it pretty much everyday however it seems like it’s not being maintained anymore and as such, there are issues when trying...   Question of the Day Today's question (by Carlo Romagnano):  EXCEPT What does this query return? SELECT * FROM Sorders WITH(NOLOCK) WHERE order_dat >= '20220503' except SELECT * FROM Sorders WHERE order_dat >= '20220503' 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) Columnstore Index Data Types Which of these data types cannot be included in a nonclustered columnstore index? (select all that apply) Answer: ntext, xml, nvarchar(max), rwoversion Explanation: None of these data types can be in a columnstore index. The complete list is: - ntext, text, and image - nvarchar(max), varchar(max), and varbinary(max) (Applies to SQL Server 2016 (13.x) and prior versions, and nonclustered columnstore indexes) - rowversion (and timestamp) - sql_variant - CLR types (hierarchyid and spatial types) - xml - uniqueidentifier (Applies to SQL Server 2012 (11.x)) Ref: CREATE COLUMNSTORE INDEX - [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 - Administration [BUILDING AN INVENTORY OF SQL SERVERS in Active Directory]( - We need to scan our entire network to build the list of all machines where SQL Servers are found. Once we have the list we will be building the report with certain server settings for each SQL Server. I tried SQLCMD -L but it list only some servers but not all, so it is probably […] [Print sql server substring from a table column and put into a table]( - Hello, Looking for a guidance to form a script to extract a specific substring (change tables) from a sql server table column and put them to another table to know the distinct tables. Here is the task which I'm performing: So captured through extended events all the strings which has the extended events. Now have […] SQL Server 2016 - Development and T-SQL [Fill missing rows in dataset]( - I have data in table like this. I want to fill/insert the rows with missing quarters from 2019-Q1 to 2022-Q4 with Sales as 0 for missing quarters and rest of the dimensions as it is. For example: West has only two rows of quarter 2019-Q1 AND 2020-Q3. I also need other 14 rows of remaining […] [Charindex values of a substring inside a long sentence]( - I need to gather the starting position of the keyword "sql" from a sql statement from a table so that I can run through all the occurrences to print it. This is just an example. declare @expression varchar(30) = 'Practice sql server daily. SQL Server is Important, SQL Server is a great db platform'; select […] Administration - SQL Server 2014 [OLE DB provider \"SQLNCLI11\" for linked server]( - We have a DTS package running on one of the production server. This is very critical package and not suppose to fail. Package running every night and completed most of the days and failed occasionally (1-2 a month) with the following error message. Need urgent help. TIA Job process - scheduler => job sent to […] SQL Server 2012 - T-SQL [Selecting distinct values from dupes based on latest date]( - I have a table of patients that has often multiple occurrences of a patient number but with differing dates of which I need to return only the latest date but I'm unsure if I need a subquery, in short I'm stuck as to what might work. Here is my table and some sample data. The […] SQL Server 2019 - Administration [Sign all stored procedures with a Certificate.]( - Hello, My company is going about ever increasing our security on all our Servers/databases. The question proposed to me was "can we sign all our SP with a certificate?" I figured it is doable but my question is, is it advisable? We would most likely use the azure key vault connector to store secrets that […] [why I cannot see memory related waits while server is under pressure?]( - Hi, It may be trivial question but I am really confused at this moment. At one of my servers I noticed that cached plans are deleted pretty much every 4 hours. Data files on that server consume a little bit more than 700 GB and SQL's memory is limited to 25 GB. I found one […] [Account logging - pwd change/db access change]( - Is there a way to continuously log account changes? For SQL accounts log for who and when the pwd changes and mappings to db's change as well as role changes?  For domain groups or accounts, log for who and when the mappings to db's change as well as role changes?  Thanks [Backup by specified date interval]( - Good afternoon, Consulting the SQL Server documentation I saw that there are some types of backup, but none of them answer my question. I would like to know if it is possible to backup database in a certain interval. Ex.: I want a backup of data from year x to year y. It's possible? SQL Server 2008 - General [Upgrading SQL Server 2008 R2 to 2017]( - Do you guys recommend upgrading SQL Server 2008 R2 to 2017 by just running the disc and uprading? Or would it be better to create a whole new server, install SQL Server 2017, back up the database from the old server, and restore it onto the new server? I don't work with SQL much, so […] SQL Server Newbies [Need to provide access to database without giving access to production]( - Hoping I could find some best practice answers here: We have analysts who need to query and manipulate SQL data out of one of our prod databases, and obviously we do not want to give them access directly to the production DB. I was wondering the best way to handle this? Was thinking about spinning […] SQL Azure - Development [Beginner Question about Azure SQL Database and Source Control (Azure Dev Ops)]( - Hi guys, I hope someone of you has the time to help me. I am relatively new to SQL and SQL Server administration. I am looking for an elegant solution for version control of SQL.  I'm using a Azure SQL DB and Azure Dev Ops Services and we are a very small team accessing […] General Cloud Computing Questions [Load balancing IPv6 UDP]( - It seems that none of the cloud providers have internal load balancers that can handle IPv6 UDP. For reasons beyond sanity, I need to run several resilient UDP services and I want to do this from inside a VPC. All inbound traffic will come via an interconnect. Is there a cloud provider with this capability? […] SQLServerCentral.com Website Issues [SSC "Points" ?]( - What generates the "Points" we have in our profile ? I just reported a span account that had 200 points.   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -