Erin Stellato is excited about SQL Server 2019 CTP 3.0:
Friends, CTP 3.0 dropped today, and it includes some changes for Query Store in SQL Server 2019! I am so excited!! I’ve downloaded it and have WideWorldImporters installed and have a lot of testing planned, but if you’re impatient, guess what? The documentation is already updated! If you check out the ALTER DATABASE SET page you will see that Query Store now has a new option for QUERY_CAPTURE_MODE: CUSTOM. For those of you with ad hoc workloads, this will help.
Read on to see how it can help.
The SQL Server team has announced the latest CTP for SQL Server 2019:
Big data clusters
– Scale out by supporting deployment configurations with an increased number of SQL Server instances in the compute pool. You can now specify up to 4 instances in the compute pool for optimal performance of your queries against data pool, storage pool, or other external data sources.
– The mssqlctl utility includes updates to ease the big data cluster management experience with enhancements to the login experience. There is also a new command to list the cluster endpoints.
– Persistent volumes abstract the details of how the storage is provided and how it’s consumed. In this release, we’re enhancing the supported storage configurations by enabling you to customize storage classes independently for logs and data. With these changes, we also consolidated the storage configurations for big data components, so that the number of persistent volume claims for a big data cluster has been reduced for a default minimum configuration cluster.
There are a few other changes announced in this CTP. Now that we’re at 3.0, the light is at the end of the tunnel.
Adrian Buckman takes us through recent changes in Undercover Inspector:
#119 When the backups check module reports backup issues for a database but the issue is with a FULL or DIFF and the LOG is ok, we now show just the primary server in the Preferred replicas column as a FULL and DIFF only applies to the Primary – this reduces the number of warnings raised within the report as it will no longer report for all replica nodes if the AG backup preference is set to Prefer secondary or Secondary Only. See Git issue for more details.
Click through for the full change set.
David Smith lays out the major changes in R 3.6.0:
A major update to the open-source R language, R 3.6.0, was released on April 26 and is now available for download for Windows, Mac and Linux. As a major update, it has many new features, user-visible changes and bug fixes. You can read the details in the release announcement, and in this blog post I’ll highlight the most significant ones.
There are some good changes in here.
Randolph West gives us some quick info on Azure SQL Database Edge:
I was first made aware of this edition at the MVP Summit earlier this year, and I need to clear some things up for folks who might be confused about the name, and who it’s for.
Firstly, recall that Azure means “hybrid” now, so while we might expect that it refers to cloud computing, it also takes on-premises infrastructure into account.
Secondly, this is the full SQL Server database engine running on a 64-bit ARM CPU. It could run on a Raspberry Pi, or — provided there was support for the other hardware — Android or iOS devices, however it is geared towards edge devices that gather data from IoT sensors and other data points. Think of this as one step up from the IoT devices capturing data in the field, whether it be wine-making, oil and gas, manufacturing, you name it.
Read the whole thing. I’m definitely interested in how they handle time series. With luck, it’s done well and brought over to the main product.
Randolph West tells a tale about checking compatibility levels:
In that demo, the AdventureWorks sample database was initially set to compatibility level of 140 (SQL Server 2017 default compatibility) to execute a scalar UDF. At this point, the estimated execution plan showed that the UDF was given a cost of 0%, and performance was terrible (the expected behaviour). Then the database compatibility level was switched to 150 (which is all that’s required to enable this new optimization feature), the query was executed again, the UDF was inlined, and performance improved dramatically.
This is where it got interesting. As a test, the compatibility level of the database was set back to 140, but the query plan continued to inline the UDF. Curious. Flushing the plan cache didn’t change the outcome (even though we knew it wasn’t necessary). Had we discovered a bug in a preview version of SQL Server 2019? It was CTP 2.2 after all, and since then (at the time of this writing) CTP 2.5 is already available.
Read on for the answer.
The SQL Server team has a new CTP out:
We’re excited to announce the monthly release of SQL Server 2019 community technology preview (CTP) 2.5. SQL Server 2019 is the first release of SQL Server to closely integrate Apache Spark™ and the Hadoop Distributed File System (HDFS) with SQL Server in a unified data platform.
This is a big one for me: lots of changes in Big Data Clusters, PolyBase on Linux, and a Java SDK. Looks like I am going to be pretty busy.
Dave Mason goes against the flow:
One of the recurring questions I see on Stack Overflow is “How do I restore a SQL Server backup to a previous version of SQL Server?” The answer, of course, is you don’t. Upgrading a database to a newer (major) version is a one-way ticket–at least as far as the database files and subsequent backups go. I recently found myself in a similar position as all those hapless Stack Overflow questioners. I had a customer that had migrated to a newer version of SQL and they wanted to roll back to the previous version. What to do?
A couple of thoughts immediately came to mind. There’s the SQL Server Import and Export Wizard and the Generate and Publish Scripts Wizard. Neither of these sounded convenient. In particular, generating a script with both schema and 500 GB of data sounded like a fruitless endeavor. Two other options sounded much more appealing. So I focused on those.
Dave has a couple of creative methods effectively to downgrade a database.
Alan Yu announces the April release of Azure Data Studio:
The key highlights to cover this month include:
– March release recap
– Azure Explorer improvements
– Visual Studio code merge process
– Insiders build process
– Viewlet revamp
– Notebook improvements
– Announcing SandDance extension
– Bug fixes
There’s a lot going on with the product, so grab the latest version and give it a try.
Dave Mason shares some thoughts on the Data Migration Assistant:
I recently took advantage of an opportunity to try Mirosoft’s Data Migration Assistant. It was a good experience and I found the tool quite useful. As the documentation tells us, the DMA “helps you upgrade to a modern data platform by detecting compatibility issues that can impact database functionality in your new version of SQL Server or Azure SQL Database. DMA recommends performance and reliability improvements for your target environment and allows you to move your schema, data, and uncontained objects from your source server to your target server.” For my use case, I wanted to assess a SQL 2008 R2 environment with more than a hundred user databases for an on-premises upgrade to SQL 2017.
Dave takes us through an upgrade on three sample databases and then gives us some more messages from actual production databases.
Kevin Feasel
2019-05-24
Query Store, Versions
No Comment