Level 2 of the Stairway to Synapse Analytics: Analyze Data in Storage Account using the Serverless SQL pool (2024-08-12)

SQLServerCentral Newsletter for August 14, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Level 2 of the Stairway to Synapse Analytics: Analyze Data in Storage Account using the Serverless SQL pool]( - [SSIS in One Hour: Hands-On Tutorial]( - [Introducing PASS Summit Charitable Organization Scholarship]( - [From the SQL Server Central Blogs - My Toolbox - SSMS]( - [From the SQL Server Central Blogs - Finding Where xp_cmdshell is Used]( - [Pro T-SQL 2022: Toward Speed, Scalability, and Standardization for SQL Server Developers]( Question of the Day - [Database Options and Numeric Roundabort]( The Voice of the DBA  Prevent SQL Injection I would hope most of you reading this know what SQL Injection (SQi) is and how you can prevent it. Or at least what patterns cause problems. If not, here's [a short explanation]( that is worth reading. If you have more questions, [ask in our forums](. SQL Injection has been, and continues to be, a problem in many systems. In fact, I chatted with Mike Walsh recently after he'd published [this post]( on an attack for one of his clients. He has some notes that explain how your database server might be vulnerable, as well as a description of a recent attack example. He also notes that many of you are responsible for protecting data, which is separate from other security mechanisms. You need to be sure you are protecting your data, even in vendor applications. I've seen similar issues in the past, both in homegrown and purchased applications, where text fields aren't checked and SQL is built by concatenating user input with code. I've complained to vendors, though often a short repro helps them see the problem and I've found many companies will patch systems, albeit sometimes slowly. There are application firewalls that can help, and certainly limiting access to those users who need access is always good, but that's not helpful when the application is something that many clients use. The best protection is education. If you don't know what to do, or your developers don't listen to you, perhaps engaging a consultant like Mike will help. I'm amazed at how often people listen to an outsider when they ignore the same advice from someone they work with. That might be especially true for managers who are more concerned with doing more new work rather than fixing something that's not quite working well. Security is becoming a bigger issue in many organizations. Not because we might get fined, but often because our customers might decide to choose another service if we can't protect their data. There are other choices these days for most of the services we provide, and many organizations are finding customers increasingly fickle and quick to leave. This might not be the case in business-to-business work, but it does happen. We often won't be perfect in our security and even if we are, our systems will change and new vulnerabilities or attack vectors will appear. We can work on the problems we know and improve security over time. SQL Injection is fairly simple to prevent, but it takes some education, some practice, and some code review. All things good database professionals should be doing. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Technical Article]( [Level 2 of the Stairway to Synapse Analytics: Analyze Data in Storage Account using the Serverless SQL pool]( Sucharita Das from SQLServerCentral In this second level of the Stairway to Synapse, we learn how to create external tables and query data that is stored in our data storage using the serverless pool. [External Article]( [SSIS in One Hour: Hands-On Tutorial]( Additional Articles from MSSQLTips.com The goal of this tutorial is to enable ETL developers to obtain practical knowledge to exploit SSIS in transforming and combining data through hands-on exploration to be ready for real-world challenges in managing information. [Technical Article]( [Introducing PASS Summit Charitable Organization Scholarship]( Additional Articles from PASS To help mark Redgate’s 25th Birthday, the company has launched a new initiative to foster knowledge and skills in the data industry. [Blog Post]( From the SQL Server Central Blogs - [My Toolbox - SSMS]( Zikato from StraightforwardSQL No matter how hard Azure Data Studio (ADS) is pushed by Microsoft, most DBAs still use SQL Server Management Studio (SSMS). In this blog post, I’ll go through my... [Blog Post]( From the SQL Server Central Blogs - [Finding Where xp_cmdshell is Used]( Steve Jones - SSC Editor from The Voice of the DBA I saw a post recently where someone was concerned about where xp_cmdshell was in use inside their system. They felt it was a security risk, and decided to get... [Pro T-SQL 2022: Toward Speed, Scalability, and Standardization for SQL Server Developers]( Site Owners from SQLServerCentral Learn how to write and design simple and efficient T-SQL code. This is a hands-on book that teaches you how to write better T-SQL with examples and straightforward explanations.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Database Options and Numeric Roundabort I run this code: ALTER DATABASE sandbox SET NUMERIC_ROUNDABORT Off Then, in the sandbox database, I run this: DECLARE @a NUMERIC(5,3) = 1.24 DECLARE @b NUMERIC(5,3) = 1.465 DECLARE @c NUMERIC(5,1) SELECT @c = @a + @b SELECT @c What is the result in @c? 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) Upgrading Old Instance to SQL Server 2022 I am trying to update my database servers to SQL Server 2022. I have a very mixed estate, some of which are older instances. What is the earliest version of SQL Server that I can perform an in-place upgrade from to SQL Server 2022? Answer: SQL Server 2012 SP4 Explanation: The oldest supported upgrade is SQL Server 2012 SP3. 2012 RTM is not supported. Ref: Supported version and edition upgrades (SQL Server 2022) - [ [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 [Searching for the Assumed Full Backup]( - I have this maintenance plan with differential backup and maintenance clean-up task in it. The plan runs once daily, and the clean-up task is deleting backup files older than 1 week. There is no other full backup plan or subplan there in SSMS. I ran the following script day before yesterday, yesterday and today to […] SQL Server 2016 - Development and T-SQL [Can you pass an undeclared variable to a Stored Procedure]( - If I have a SP that calls another/different SP can I pass to that second SP a variable that has not been declared within the 1st SP and which was not passed to the 1st SP like the below? I am walking through some code in a SP and found that it had a variable […] SQL Server 2019 - Administration [Windows 11 & sudden SSMS sorting nuisance]( - When I was still in Windows 10, I'd open up SSMS (version 18) and go to Object Explorer Details, where I would see the folders System Databases and Database Snapshots up at top and all the individual database names listed in alphabetically order (unless I sorted otherwise). Now that I've upgraded to Windows 11, something […] [HADR SYNC COMMIT / deadlock rebuild index]( - Hi, I have some interesting issue. Server1 2024-08-09 02:59:25- start session 234 with UPDATE STATISTICS on Table1 WA_sys_xxxx stat 2024-08-09 03:00:08 - start session 800 - with ALTER INDEX on Table1 with Index1 2024-08-09 03:00:25 - deadlock session 800 like victim and session 527, which is application with some MERGE 2024-08-09 03:04:22 - first record […] [Ping a sqlinstance]( - How do I (ping/check if up and running) a sqlinstance from t-sql ?  SQL Server 2019 - Development [can my phone run ssms inside our firewall?]( - Hi my phone is now upgraded to allow teams , outlook etc using my work account.  Its a portal more or less. Is there a way to leverage my new portal to run ssms from my phone so i can check on the status of a couple of jobs rather than carrying my pc around […] [what have people done for ssrs params needing very precise start and end times]( - Hi we run 2019 standard. one of our mfg locations requires somewhat precise start and end param times along with the start and end dates controlling how an ssrs report is filtered. our main user doesnt like the idea of them typing hh.mm.ss.nnn next to the date showing when they pick a date from the […] Integration Services [Destination Table - Condition Amount 0]( - I need your assistance with an ETL process that runs every six months. Currently, we are in Calendar Year/FY 2025, which started in July 2024. The issue is with the "Condition Amount" for FY 2025. Data comes through correctly until the "Insert Data Into Task" step (please see the attached screenshot). However, it appears that […] [Destination Table - Condition Amount 0]( - I need your assistance with an ETL process that runs every six months. Currently, we are in Calendar Year/FY 2025, which started in July 2024. The issue is with the "Condition Amount" for FY 2025. Data comes through correctly until the "Insert Data Into Task" step (please see the attached screenshot). However, it appears that […] [Destination Table - Condition Amount 0]( - I need your assistance with an ETL process that runs every six months. Currently, we are in Calendar Year/FY 2025, which started in July 2024. The issue is with the "Condition Amount" for FY 2025. Data comes through correctly until the "Insert Data Into Task" step (please see the attached screenshot). However, it appears that […] Design Ideas and Questions [Why is it a good idea to not restore a production database to test?]( - (I looked over all the forums here on SSC and this one seemed the best for my question. If you think I'm wrong, I'm sorry I posted my question here.) For years I've read here and elsewhere that it is not a good idea to restore a production database to its equivalent test database. I've […] SQL Server 2022 - Administration [Collation Change]( - Any recommendations on changing the collation if DB ? There is dependent objects so simple alter won't work. Also DB to big to generate script with schema and dats [In primary alwayson replica index maintenance plan need tobe prepare fordowntime]( - Dear all, I have a 3.5 tb primary always on database with 2 secondary replica one is sync and dr is async In primary always on replica index maintenance plan need to be prepare for downtime can any one mention the order for the plan and its steps to follow Do we need to suspend […] [OLA's Maintenance scripts - should I be reorganizing?]( - Hi These are my current params for nightly maintenance: USE MASTER EXECUTE dbo.IndexOptimize @Databases = 'MY USER DB', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REBUILD_ONLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE', @FragmentationLevel1 = 25, @FragmentationLevel2 = 50, @SortInTempdb = 'Y', @MaxDOP = 2 I have removed 'REORGANIZE' as per Jeff's thread here: Am I correct in removing the […] SQL Server 2022 - Development [Query timeout question]( - I am debugging (sort of) a problematic query, that gives a timeout error (30 seconds) in our company application. I've captured the query + parameters using Extended Events and when I run the exact same query with the same parameters in SSMS, it takes about 1 second. Anyone got any ideas what's happening here?   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -