A Fine Slice Of SQL Server
Microsoft has just released SQL Server 2019 CU3:
This article describes Cumulative Update package 3 (CU3) for SQL Server 2019. This update contains fixes that were released after the initial release of SQL Server 2019 and updates the SQL Server and Analysis services components to the following builds.
It does fix the SQL Agent problem from CU2 and it looks like the ML Services team had several patches thrown in.
Comments closedDavid Smith takes a look at R 3.6.3:
On February 29, R 3.6.3 was released and is now available for Windows, Linux and Mac systems. This update, codenamed “Holding the Windsock“, fixes a few minor bugs, and as a minor update maintains compatibility with scripts and packages written for prior versions of R 3.6.
February 29 is an auspicious date, because that was the day that R 1.0.0 was released to the world: February 29, 2000. In the video below from the CelebRation2020 conference marking the 20th anniversary of R, core member Peter Dalgaard reflects on the origins of R, and releases R 3.6.3 live on stage (at the 33-minute mark).
I’m holding out for R 4, though then I’ll have to wait to see when SQL Server will officially support it.
Comments closedFinally is here, PowerShell 7 GA (Generally Available) is available for download for Windows, Linux, and macOS. Go and get it!
And Max is here to show us exactly how to get it.
Comments closedRandolph West takes a look at my favorite activity: deleting code:
The following statements are true:
– SQL Server 2019 does not have any deprecated features, but does have discontinued features
– SQL Server 2017 does not have any discontinued features, but does have deprecated features
– SQL Server 2016 has both deprecated and discontinued featuresLet’s discuss the difference between deprecated and discontinued features, and explain how this affects database administrators looking to move to SQL Server 2016 or newer. My current advice is to target SQL Server 2019 with at least Cumulative Update 2 (CU2).
Read on for more details.
2 CommentsTaryn Pratt goes into a change in the sys.syslogins system view in SQL Server 2019:
Sigh ok, something is really broken because this was working before we failed over.
The code for the login replication basically does the following via a cursor (yeah, I know, but it works…normally):
1. Select from the primary via
OPENQUERYto query the logins and passwords
2. Usingsp_hexadecimalconvert thevarbinarypassword to astringvalue
3. Create a string to be executed, i.e. dynamic SQL that runs aCREATE LOGIN
Read on for the whole story and how you can protect yourself as you upgrade to SQL Server 2019.
Comments closedAlan Yu announces the February 2020 release of Azure Data Studio:
Azure Data Studio is a multi-database, cross-platform desktop environment for data professionals using the family of on-premises and cloud data platforms on Windows, MacOS, and Linux. To learn more, visit our Github.
The key highlights to cover this month include:
– Improved Azure sign in support.
– Find in Notebook support.
– Visual Studio Code merge to 1.42.
– Bug fixes.
Read on for more details on each topic.
Comments closedMilos Radivojevic takes us through improvements with interleaved execution in SQL Server:
As you might know, the Interleaved Execution is the member of the Intelligent Query Processing family of features. It has been introduced with SQL Server 2017 (as a part of the Adaptive Query Processing). It is designed to improve the performance of queries referencing multi-statement table-valued functions (MSTVF). Actually, it addresses currently only queries using MSTVF, but is hopefully designed for much more. The query optimizer usually has two issues with queries using MSTVF:
– MSTVF is a black-box for the optimizer; it does not know what’s inside, it cannot perform cross-statement optimization (as it is a case with inline TVFs) and it assumes it is a cheap and fast operation
– MSTVF has a fixed cardinality of 100 (prior to SQL Server 2014, it was 1)Interleaved execution does not improve the first issue (MSTVF is still a black-box for the optimizer), but solves the cardinality issue.
Read on to understand how this second aspect has changed for the better.
Comments closedJohn Mount has a public service announcement:
In a lot of our R writing we casually say “install from CRAN using
install.packages('PKGNAME')” or “update your packages by usingupdate.packages(ask = FALSE, checkBuilt = TRUE)(and answering ‘no’ to all questions about compiling).”We recently became aware that for some users this isn’t complete advice.
The above depends on your R install pointing to a repository that is in fact up to date. To check what repositories you are using please use the command
options('repos').
The specific example here is around the Microsoft R Archive Network (MRAN), which stays at fixed dates. This is for a good reason: because it helps companies standardize on a known set of versions of R packages by default. That way you don’t have version 1.8 of a package in dev and then get 1.9 in production and find out that something broke between the two versions.
Comments closedErin Stellato gives us a moment of zen:
A question I’ve gotten a few times when teaching relates to database compatibility level and Query Store. I was talking to a client yesterday about post-upgrade plans and implementing Query Store, and the topic came again. They wanted to know what compatibility level the database needed in order to use Query Store.
The quick answer: it doesn’t matter.
Read on for a demonstration.
Comments closedThe Hadoop in Real World group takes us through schema changes in Apache Kafka:
Meetup.com went live with this new way of distributing RSVPs – that is through Kafka. Both the producer and consumer agrees on the Schema and everything is great. It is silly to think that the schema would stay like that forever. Let’s say meetup.com didn’t feel the value in providing member_id field and removes it. What do you think will happen – will it affect consumers?
member_id field doesn’t have a default value and it is considered a required column so this change will affect the consumers. When a producer removes a required field, the consumer will see an error something like below –
Caused by: org.apache.kafka.common.errors.SerializationException: Error deserializing Avro message for id 63
Caused by: org.apache.avro.AvroTypeException: found com.hirw.kafkaschemaregistry.producer.Rsvp,
expecting com.hirw.kafkaschemaregistry.producer.Rsvp, missing required field member_id
This is an interesting review of the schema registry in Kafka and what the different modes allow for.
Comments closed