Press "Enter" to skip to content

Category: Versions

Merge Replication: Subscriber and Publisher Versions

Published 2020-06-03 by Kevin Feasel

Steve Stedman gives us the proper order for upgrading SQL Server when you’re using merge replication:

Working on a recent SQL Server merge replication project we needed to update some of the servers in a merge replication scenario without upgrading all of them. Consider a merge replication setup with a publisher, a distributor and 2 or more subscribers all on the same version of SQL Server, and you need to upgrade the SQL Server version on the subscriber to a newer version like SQL Server 2019.

Before doing any type of upgrade, I wanted to confirm that things would or would not work. First checking some Microsoft documentation it appears that replication from a SQL 2012, SQL 2014, SQL 2016, or any older version of a publisher is not supported to a subscriber running on SQL Server 2019. Or more specifically the subscriber needs to be on the same ore older version than the publisher.

Read on for a demo, as well as an interesting caveat.

Comments closed

May 2020 Release of Azure Data Studio

Published 2020-05-21 by Kevin Feasel

Alan Yu has some goodies for us:

The key highlights to cover this month include:

– Announcing Redgate SQL Prompt extension
– Announcing the new machine learning extension
– Added new Python dependencies wizard
– Added support for parameterization for Always Encrypted
– Improvements to the notebook markdown toolbar
– Bug fixes

For a list of complete updates, refer to the Azure Data Studio release notes.

I’ll have to check out the ML extension.

Comments closed

Don’t Install Hadoop on Windows

Published 2020-05-11 by Kevin Feasel

Hadi speaks truth:

A few days ago, I published the installation guides for Hadoop, Hive, and Pig on Windows 10. And yesterday, I finished installing and configuring the ecosystem. The only consequence I have is that “Think 1000 times before installing Hadoop and related technologies on Windows!”.

The biggest problem is that Microsoft got flaky about this. Back in 2012-2013, they backed running Hadoop on Windows as part of getting HDInsight up and running. I even remember the HDInsight emulator which could run on a local desktop. By 2014 or so, they shifted directions and decided it wasn’t worth the effort. Because Apache Spark (which does have pretty decent Windows support, at least for development) really wants Hive, you can fake it with winutils.

Comments closed

Availability Group Offerings in Standard Edition

Published 2020-05-01 by Kevin Feasel

Guy Glantser notes an issue with Availability Group documentation:

In SQL Server 2017 Microsoft added a new flavor called Read-Scale Availability Groups. This is different, because the goal here is not high availability or disaster recovery, but rather read-scalability. As opposed to the other flavors, in RSAG there is no cluster, and there is also no automatic failover mechanism. But you can set up multiple secondary replicas with read-only access and load balancing, and offload read workloads from the primary replica. This is a great scalability feature, and you can read more about it here.

Now, if you check Microsoft documentation regarding the editions and supported features of SQL Server, you will be happy to see that RSAG is supported in Standard Edition. I was happy to see it too. Unfortunately, if you try to set up a Read-Scale Availability Group on Standard Edition, it will not work. You will only be able to create a Basic Availability Group, as discussed earlier.

Click through for the answer, as well as what you can do in Standard Edition.

Comments closed

Azure Data Studio April 2020 Release

Published 2020-04-29 by Kevin Feasel

Alan Yu announces the April 2020 release of Azure Data Studio:

KQL magic extension support is now available in Azure Data Studio Notebooks. It allows you to connect, query and explore Azure Data Explorer (Kusto), ApplicationInsights and LogAnalytics data using kql (Kusto Query Language). If you are using Log Analytics today for your Azure SQL DB as described here, you can now do log metric analysis using KQL magic in Azure Data Studio Notebooks. 

KQL magic package can be downloaded from Manage Packages in Python Notebook or using pip install. In a Python Notebook in Azure Data Studio, load KQL magic using (%reload_ext Kqlmagic). Start connecting, querying, and exploring using %kql or %%kql for multi-lines.   

KQL magic allows you to see tabular results similar to SQL Notebook, where you can also have the benefits of exporting outputs to other formats (csv, Excel, JSON, XML) and using the Charting functionality. You can also take advantage of rendering charts directly with plotly for richer interactivity. 

There are several fairly big changes in here, so check them all out.

Comments closed

R 4.0 Released

Published 2020-04-28 by Kevin Feasel

David Smith walks us through what’s new in R 4.0:

R 4.0.0 was released in source form on Friday, and binaries for Windows, Mac and Linux are available for download now.

As the version number bump suggests, this is a major update to R that makes some significant changes. Some of these changes — particularly the first one listed below — are likely to affect the results of R’s calculations, so I would not recommend running scripts written for prior versions of R without validating them first. In any case, you’ll need to reinstall any packages you were using for R 4.0.0. (You might find this R script useful for checking what packages you have installed for R 3.x.)

And I just got 3.6 into production yesterday. Them’s the breaks…

Comments closed

Azure SQL Database Tiers

Published 2020-04-28 by Kevin Feasel

Tim Radney enumerates the tiers available to us with Azure SQL Database:

When Azure SQL Database first launched, there was a single pricing option known as “DTUs” or Database Transaction Units. (Andy Mallon, @AMtwo, explains DTUs in “What the heck is a DTU?“) The DTU model provides three tiers of service, basic, standard, and premium. The basic tier provides up to 5 DTUs with standard storage. The standard tier supports from 10 up to 3000 DTUs with standard storage and the premium tier supports 125 up to 4000 DTUs with premium storage, which is orders of magnitude faster than standard storage.

But there have been several additions since then and Tim lays it out for us.

Comments closed

SQL Server 2019 and Columnstore Cleanup Issues

Published 2020-04-27 by Kevin Feasel

Taryn Pratt shares an issue with columnstore cleanup in SQL Server 2019:

The output of sp_who2 was repeatedly showing GHOST CLEANUP and CREATE INDEX. Over and over and over again. To be clear, I’m not a clustered columnstore expert, I know enough to be able to maintain them as needed. I went to Twitter and mentioned what I was seeing. I was advised by @sqL_handLe to try trace flag 661 which disables the ghost record removal process, and by Joe Obbish via Erik Darling to enable trace flag 634 to disable the tuple mover background task.

Initially, we enabled trace flag 634, but the logs continued to grow. We disabled trace flag 634. Then we enabled trace flag 661, and the logs continued to grow, so we disabled it. Finally, we tried enabling both of the trace flags. The big jumps stopped, but we now had about 400GB of logs that needed to be written to the reporting cluster before we could perform the failover.

While the logs were exploding we wondered if whatever was happening might have been caused by the deletions we did in early February. But why would they be triggered by the upgrade to SQL Server 2019?

Read the whole thing if you’re looking at a migration to 2019.

Comments closed

Getting SQL Server with Current Linux Distributions

Published 2020-03-26 by Kevin Feasel

Tejas Shah announces the availability of SQL Server 2019 on the latest long-term releases of Ubuntu, Red Hat, and SuSE:

SQL Server team has been working diligently in adding support for current Linux distributions. To this end, the team announced support for SQL Server 2019 on RHEL 8.0, Ubuntu 18.04 and SLES 12 SP5 within last quarter.

The team is glad to announce that the Azure marketplace PAYG (Pay As You Go) images for SQL Server 2019 on RHEL 8.0, Ubuntu 18.04 and SLES 12 SP5 have been made generally available. You can deploy these images to get the latest of both SQL Server 2019 functionality and operating system improvements.

With Ubuntu 20.04 coming out soon, it’ll be interesting to see when that officially becomes supported.

Comments closed

Upgrading SQL Server Reporting Services to 2017

Published 2020-03-25 by Kevin Feasel

Alexandre Hamel walks us through the new process for upgrading SQL Server Reporting Services:

In the past, we could run the SQL installer to do an in-place upgrade of SQL server including the SSRS instance to a newer version. As of 2017, SSRS is a separate install from SQL server, so this is no longer possible. In fact, if you do an in-place upgrade of SQL 2014 to 2017 for example, you will see a warning that SSRS will be uninstalled. Before proceeding with the SQL upgrade, follow these steps to upgrade the SSRS instance.

It’s not as easy as it was before, but Alexandre takes us through the step-by-step process and even includes some notes on how to roll back your upgrade attempt if necessary.

Comments closed