An Introduction to Azure Databricks (2021-11-08)

SQLServerCentral Newsletter for November 8, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Perform API Operations Using Stored Procedures in CData SSIS Components]( - [An Introduction to Azure Databricks]( - [Text Mining and Sentiment Analysis: Data Visualization in Tableau]( - [State of Database Monitoring Report 3]( - [From the SQL Server Central Blogs - Quick CSV Import with dbaTools – #SQLNewBlogger]( - [From the SQL Server Central Blogs - Log Analytics with Azure Synapse Analytics]( Question of the Day - [Dropping the Aggregate]( The Voice of the DBA  Daily Coping Tip Make a list of new things you want to do before the year ends 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. SQL Server Next We now know when the next version of SQL Server is coming. At Ignite last week, [Microsoft announced SQL Server 2022](, which comes nearly 3 years after the last version, SQL Server 2019. Apparently, the pandemic's effects include delays in product development as it's been nearly three years since SQL Server 2019. The new version is in preview, with a few new features announced. There's also [a video version from the Ignite site]( but if you want a quick summary of things, read on. There were a few items that get me a bit excited. The first is that we get some query improvements with regard to parameter sniffing in stored procedures. This has been a constant problem for many databases and workloads for years. This is because only one plan has been kept in the cache. That changes in SQL Server 2022, as the instance can not keep multiple plans around. It will be interesting to see if this helps many customers. The Query Store was an interesting edition to SQL Server and plenty of people have found it valuable. However, it's use was optional and you had to enable it. In SQL Server 2022, Query Store is now on by default and there is support for read replicas as well. This change is there to enhance the intelligent query processing (IQP) work that has been growing in each new version. This new version gets MAXDOP and Cardinality Estimator feedback being incorporated into the feedback loop. IQP isn't perfect, and it might cause you some issues with certain workloads, but many of the changes made in the last few versions do seem to be helping customers. There was a quick note in the Ignite video about multi-write replication is not available. Not true peer to peer, but this version should automate the last write wins rule, based on UTC timing. I've been skeptical of using [peer-to-peer replication]( in the past, mostly because of the need to code conflict scenarios, but a last-writer wins scenario might work well for some applications. If you can accept this rule, and you have good resources for propagating data, this might be something you can use. Blockchain is coming to SQL Server. There is [a Ledger feature]( that provides an immutable ledger of data changes. This ensures that data integrity is maintained with full auditability. Trusted storage is needed to ensure this meets all audit and regulatory requirements, though if the data has been tampered with, you might only know something happened. It's not perfect, but it does provide the ability to prove there is trust in the data you are watching. I suspect this will be a feature like TDE that auditors want enabled, even if it isn't perfect. I do wonder what this will do to storage costs and complexity. [Azure Purview]( is being integrated into SQL Server 2022. The big takeaway here is that you can scan on-premises data for free. Now I don't know if this means you can use your data for free in any way, but I do think that this does start to make governance easier. I think that's a big deal for all of us in the future if companies were to better know what data is risky and work on protecting it. Normalizing the way we look at data governance is a big step forward. This certainly is a long way from most applications using the "sa" account. One of the challenges I've seen in many companies is dealing with reports. Often companies try to run complex queries on their OLTP system, or they spend a lot of time and effort to build ETL structures. Synapse Link provides automatic change feeds from a SQL Server to a SQL pool in Synapse. This is like an automatic replication of the table(s) into your data warehouse. [It's been available for CosmosDB](, but now it comes to SQL Server. Given the cost of development and managing ETL jobs, I could see some organizations upgrading to SQL Server 2022 just for this feature. If it works well and quickly. Disaster Recovery (DR) is something I've always been concerned about. I've had too many issues over time to treat this lightly. In this new version, you can set Azure SQL Managed Instance as a target for a DR recovery, with failover to the cloud through a distributed availability group. That's has been possible before, though now this seems easier than ever (as it should be). The failover is a good feature, but a lot of us wouldn't necessarily want to live in the cloud, so the big announcement here is that you can restore the backup from MI back to an on-premises SQL Server 2022 instance! We can restore back from the cloud to our own systems, which has been a hassle for a long time. Now, this isn't failback. I have no idea if logs can be backed up and restored, or if this means a lot of downtime to leave the cloud if you have a big database. My guess is this is really a first cut, but the ability to move from MI back on-premises is huge. If for no other reason than you can backup things for dev work inside your organization. That's a big win for a lot of customers I know, at least it is if Azure SQL DB and Azure SQL MI are close in functionality from the inside-the-database perspective. There are other enhancements as well. The cloud offerings from Microsoft gain some scale, which is good. Most of us don't have huge systems, but more scale usually trickles down with better pricing for the mid levels. Azure Arc grows, allowing you to run Azure SQL MI and Hyperscale PostgreSQL on your own hardware. You'll pay something to Microsoft, but having these systems stand up, keeping data local, and having them managed easily is a big deal. I expect that more companies will consider things like Azure SQL DB on their own hardware over time, especially as standing up and managing a Kubernetes cluster becomes easier. This feels like the early days before vSphere when we struggled to manage lots of virtual machines. I suspect we will find better ways to deploy and manage container orchestrators on-premises over time. It feels like a long time since we talked about SQL Server. Certainly the last 18 months have felt like the world is frozen and not much has happened. I know I've done some things, but it's been a strange feeling of suspension for much of life. For me, the announcement of a new version of SQL Server brings some excitement. It feels as though the world is moving forward, and I'm looking forward to learning and writing about the future. I'm also hoping to get the chance to deliver some presentations on something new in SQL Server in 2022.  Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Perform API Operations Using Stored Procedures in CData SSIS Components]( JerodJ from SQLServerCentral APIs require up-to-date and strong documentation, increased security levels, comprehensive testing, routine versioning, and high-reliability to be appropriately utilized. With the CData SSIS Data Flow components you can import and export data from various data sources. However, in some cases the type of operation you need does not fit the SSIS Source or the SSIS Destination model. The […] [SQLServerCentral Article]( [An Introduction to Azure Databricks]( Sucharita Das from SQLServerCentral Learn a bit about Azure Databricks in this article. [External Article]( [Text Mining and Sentiment Analysis: Data Visualization in Tableau]( Additional Articles from SimpleTalk Tableau is a popular visualization tool used by organizations around the world. In this article Sanil Mhatre demonstrates sentiment analysis with Tableau and Oracle data. [External Article]( [State of Database Monitoring Report 3]( Additional Articles from Redgate Database professionals are spending 1/3 of their time on security tasks each day and say it’s their biggest professional challenge this year. Learn how 2500+ database professionals are reacting to these unprecedented demands in our third database monitoring insights report. [Blog Post]( From the SQL Server Central Blogs - [Quick CSV Import with dbaTools – #SQLNewBlogger]( Steve Jones - SSC Editor from The Voice of the DBA Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. Recently I was searching around for some sample... [Blog Post]( From the SQL Server Central Blogs - [Log Analytics with Azure Synapse Analytics]( Bradley Schacht from Bradley Schacht There are a lot of services in Azure. Way more than a few. What is something you want to do with all your services and applications? You want to...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Dropping the Aggregate What are the minimum permissions I need to drop an aggregate from a SQL Server 2019 database? (choose 2) 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) The Tail-Log Backup What is contained in a tail-log backup? Answer: Transaction log records that have not been backed up yet Explanation: The tail-log backup captures log records that have not yet been backed up. Ref: Tail-Log Backups - [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 [Shrink File on database to release the free space back to a drive]( - I work in an IAAS and we have a situation where we are trying to by time before we have to have our client upgrade to another tier of IAAS. I have very little experience shrinking either a database or a file for the obvious reasons(i.e the database will just autogrow again which is costly, […] SQL Server 2016 - Administration [HADR_SYNC_COMMIT wait when running Async with very large Log Send Que]( - Hello all, Edit: Posted here because I last saw this issue on a SQL 2016 server. We use AOAG extensively for our production DBs across 2014, 2016, and 2019 SQL Instances. All our AOAG setups use Async commit without exception. We occasionally see an issue where the Log Send Rate drops very low (500KB\sec) causing […] [Sharing an SSRS ReportServer database]( - Hello experts, I have a request to install SQL Server Reporting Services. I plan to use SSRS 2017. My question is - We have another SSRS 2017 instance that uses a remote ReportServer db on one of our shared SQL Servers. Can I use that same remote ReportServer db as the remote SSRS db for […] [End user needs to execute just ONE sproc]( - Hi all, As the title implies, Mr One-Sproc just needs to run that - and we don't want him having access to anything else. I'm thinking the least effort way which is reasonably secure to abstract this is to set up a CMD/PS file that accepts his params and lets loose - no client SSMS, […] SQL Server 2016 - Development and T-SQL [bring back single line]( - How can I turn this query to report back single line for different 'TEST" values. each one of the .test will have a differnt .value, but instead of writing 2 lines of data is it possible to just produce single record? SELECT UpCastProduction.ItemID, UpCastProduction.ContainerID, UpCastProduction.GrossWeight, UpCastProduction.EquipmentID, UpCastItems.PackageCode, UpCastProduction.OnHold, UpCastProduction.Scrapped, UpCastProduction.Uploaded, UpCastProductionQuality.Value FROM UpCastProduction INNER JOIN […] [Question about Management Studio and XX-bit versions]( - Hello, I would like to ask, where can I check which versions if SQL Management Studio is installed on a client? My user cannot run the import tool, getting the error : 'Microsoft.ACE.OLEDB.16.0' provider is not registared on the local machine This tool which should be 64-bit is part of the SQL Server, which the […] Development - SQL Server 2014 [Identify INT,BIGINT,character in stored proc]( - Hi, I have a stored proc that takes comma separated list , split it and use it to match against table columns. e.g. execute spValidationGet '821780031254,821780031250,1473983,1473903,65644' I need to implement some input validation in it 1- I should discard character string e.g. 821780031254,821780031250,1473983,abc,1122' , my proc should remove abc before comparing it to INT column […] SQL 2012 - General [when insert rows it take too much time for 0 rows inserted so why and How to sol]( - I work on SQL server 2012 I face issue when insert rows it take too much time and on final no rows added EXECUTION PLAN AS BELOW CREATE TABLE [Parts].[TradeCodes]( [TradeCodesID] [int] IDENTITY(1,1) NOT NULL, [PartID] [int] NOT NULL, [Code] [varchar](20) NOT NULL, [CodeTypeID] [int] NOT NULL, [SourceTypeID] [bigint] NULL, [RevisionID] [bigint] NULL, [ModifiedDate] […] SQL Server 2019 - Administration [Error connecting to the Read-Only Replica from Application]( - Dear Friends, I have a Read-Scale setup with 2 nodes , and one of them being 'Read-Only' replica. The connections work well at the Database Level but throws an error at the Application Level ,from DevOps ,(as in the attachment) when I go and test the Read-Only replica .I got the same error from SSMS, […] SQL Server 2019 - Development [Connection String for Access 2016 ADODB to SQL Server 2019]( - Upgraded from an Access 2010 ADP database, linked my tables, but I can't connect to the server when the main module is loaded. Can't figure out the change in the code. [Update Rows in one table based on another table]( - Posted this in a BI Forum but am curious as to how to do this in SQL. I would prefer to do it in SQL if it's not too complex. [Pivot Query]( - Hello: I am trying to get a count of columns for values within a given date range. I am using this example, but unable to get results.  There are three transaction types, Receipt (value of 1), Issue (Value of 2) and Adjustment (Value of 3). How many are valued at 1, 2 and […] SQL Azure - Administration [SQL 2016 Linked Server to Azure DB Not Working]( - I am trying to create a linked server to and Azure DB from SQL 2016, SP3. I have used instructions found on SQLShack and SQLTips and successfully did the initial creation. I used a SQL login/user on the Azure DB. However, when I try to connect to the linked server I get an authentication error, […] Powershell [remove header and blank line from output file querying a DB]( - The following script retrieves data from a MS Access database correctly. I'd like the output file to NOT have the header and blank line at the beginning of the file. Is there a way to suppress these lines?  A second question while I'm asking.  I'm hardcoding the pipe delimiter as part of the query. […] Integration Services [SSIS Script component - namespace not found]( - I am trying to upgrade a SSIS project from 2016 to 2019 and having issues with some script components in data flow tasks. If I change the TargetServerVersion of the project to SQL Server 2019 then these types of errors (this is when I rebuild the script from within the script editor): The type or […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -