Press "Enter" to skip to content

Month: August 2022

DAX Studio 3 Released

Marco Russo has an announcement:

DAX Studio 3 was released today.

This new, major version offers a new user interface and user experience, providing a modern look and feel without losing any of the features available in previous versions. On the contrary, in DAX Studio 3, many features that were harder to reach in the past are now more visible and easier to use.

The cost of implementing a complete overhaul for a product’s user interface is typically high, but at the same time, this overhaul does not provide new features compared to previous versions. So why bother about the user interface while the product is free and open-source?

Read on to see what those changes look like.

Comments closed

Trying out Shiny Python

Jamie Owen kicks the tires on Py-shiny:

We would posit (see what we did there) that R-{shiny} has been a boon for data science practitioners using the R language over the last decade. We know that in our Python work, we have certainly been clamouring for something of the same ilk. And whilst there are other frameworks that we also like, streamlit and dash to name a couple, neither of them has filled us with the same excitement and confidence that shiny did in R to build both simple and complex bespoke web applications. With RStudio Posit conf in action the big news from July 27th was the alpha release of Py-{shiny} which was a source of great interest for us, so we couldn’t resist installing and starting to build.

If you are familiar with R-shiny already, then much of the py-shiny package will feel familiar to you (albeit with a couple of things having been renamed). However we will approach the rest of this post assuming that a reader does not have that prior experience and take you through building a simple shiny application to display plots on subsets of a dataset.

I’m curious how much take-up there will be for the library, given that there are several good competitors on Python.

Comments closed

NOLOCK and Inserts

William Assaf shows us the bad idea of the day:

Error 1065 states “The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE, DELETE or MERGE statements.”  However, NOLOCK can still dangerously be used as the source of the write. (NOLOCK can also fail with error 601 on even basic SELECT statements, but coders just aren’t always scared off by that.)

Here’s a very elementary example to share at parties in order to scare developers away from using NOLOCKs adventurously:

Read on for an example which shows the level of pain you can find yourself in with NOLOCK.

Comments closed

Installing SQL Server on CentOS

Nisarg Upadhyay has a walkthrough for us:

In this article, we will understand how we can manage a SQL Database in CentOS 8.0. This article is the first article on the topic Manage SQL Server on CentOS.

1. Step-by-step Installation of SQL Server and client tools in CentOS

2. Create and connect to the SQL Database

I have created a virtual machine on a workstation and installed CentOS 8.0. First, let us install SQL Server on CentOS.

One quick note is that CentOS is no longer officially supported as a platform for SQL Server on Linux. It’ll still work fine, though if you use it in production and run into a problem, there’s a risk that you wouldn’t be able to get timely support. Still, if you’re just trying it out on a personal device and you like the Red Hat-like experience versus Ubuntu-based distributions, getting Microsoft support isn’t all that relevant.

Comments closed

Overview of Arc-Enabled SQL Managed Instances

Warwick Rudd continues an overview of Azure Arc-Enabled Data Services:

In our previous post, we mentioned the 2 types of data services that are supported and able to be managed by our newly deployed Data Controller:

– Azure Arc-enabled SQL Managed Instance

– Azure Arc-enabled PostgreSQL Hyperscale

In this pose we are going to have a look at the differences between an installation of Azure SQL Managed Instance and Azure Arc-enabled SQL Managed Instance.

This post doesn’t cover the actual deployment; Warwick promises that in his next post.

Comments closed

Azure Data Studio August 2022 Updates

Timi Oshin has another month’s worth of updates for us:

The SQL Database Projects Publish dialog has been enhanced with a breadth of options including excluding object types, data definition language (DDL) trigger behavior, index rebuild behavior, and more. These options are available after clicking the Advanced button and a description for each option appears at the bottom of the panel. With the availability of these options, you can now publish a SQL project or generate the publish script with more precision for the requirements unique to your environment.

This is something I’ve missed from VSCode / Azure Data Studio that I could do 15 years ago in Visual Studio.

Comments closed

Undocumented Goodies in SQL Server 2022 RC0

Brent Ozar digs through some internals:

Microsoft has an official list of what’s new in 2022 overall, but here I’m specifically focusing on system objects that might be interesting to script developers like you and I, dear reader.

New stored procedure sp_get_table_card_est_and_avg_col_len – I assume the “card” refers to statistics and cardinality, not Hallmark. SQL Server has historically struggled with memory grants because it uses datatype size to budget memory grants, and bigger-than-necessary data types (like NVARCHAR(MAX)) have led to larger-than-necessary grants. It’d be cool if this proc was a down payment to mitigate that problem, but I have a sinking feeling it has to do with external data sources. I would tell you more about it, but when I run it, I get:

Read on for the full list.

Comments closed

SQL Server on Linux: No More GDR Repos

Amit Khandelwal makes an announcement:

We would like to announce the recent change made to the Announcing the Modern Servicing Model for SQL Server – Microsoft Tech Community   that applies for SQL Server on Linux/Container releases only. 

We are moving forward with a Linux apt mainstream servicing lifecycle for SQL Server 2017 and later:

1. For SQL Server 2017 and above we will only have Cumulative Update (CU) based General Distribution Releases (GDRs), thus simplifying it for users to download the required updates through one single branch which is the CU (Cumulative Update) branch.  Users need to subscribe and use only one single repository, that is the CU repository, to get all the critical fixes, product improvements or bug fixes.

2. The RTM (Release to Manufacturing) based GDR repository that contained base SQL Server release, critical fixes and security updates since that release, will be removed. As they worked only for older Linux distributions (RHEL 7, Ubuntu 16.04 & SLES 12). 

Read on for the consequences of this.

Comments closed

Securing Kafka Streams

Amani Newton gives us a primer on Apache Kafka security:

The largest companies in the world use Apache Kafka® for their real-time streaming data pipelines and applications. Kafka is the basis for the real-time fraud text alerts from your bank and the network-connected medical devices used in your local hospital. Securing customer or patient data as it flows through the Kafka system is crucial. However, out of the box, Kafka has relatively little security enabled. This blog post previews the free Confluent Developer course that teaches the basics of securing your Apache Kafka-based system.

Click through for the overview.

Comments closed

The Importance of Dev Environment Security

Kenneth Fisher shares an opinion:

I was asked a rather interesting question during an interview recently. It went something like this:

Your resume says you’ve implemented security standards. What do you do if a development team comes to you and says “We have to have a SQL Server Id in production (or other restricted permission) because that’s the way we tested it and we are going into production now.”?

And in general my response is “You give it to them. It’s too late to stop them at that point.”

Kenneth goes on to elaborate.

I prefer a compromise solution, which requires more than 2 environments. Dev is a sandbox. Developers don’t know what they need and generally need more space to work than the final product itself will need. A wide-open dev environment is something I don’t mind. In some organizations, a local dev environment may be sufficient for this task—that is, SQL Server (or whatever) installed on the developer’s personal machine or brought in via Docker container.

However, there must be at least one pre-production environment in which developers have prod-level rights. Ideally, it would not be the last step of integration prior to prod but instead something like a QA environment which is part of a CI/CD process.

I should also point out that module signing helps with this problem: stored procedures may contain rights that devs shouldn’t have but are necessary to the way an application functions. Access to execute those stored procedures is something the DBA team can control and put some limits around, so in Kenneth’s example, truncation could be handled via stored procedure. That procedure handles the truncation operation and opens the door to further checks, such as a list (or table) of tables which the app is allowed to truncate, logging of operations, etc.

Comments closed