A Fine Slice Of SQL Server
Gilbert Quevauvilliers puts a feature’s face on a milk carton:
I am sure everyone can agree that the new formatting pane is an awesome change.
But at the same time, I have found it a challenge to find settings with the new format pane.
In this blog post I will show you to find the conditional formatting which appears to have gone missing in the new format pane?
Click through to find out.
Comments closedAlex Aleksandrov shows off the PREDICT operator:
We can use Synapse for many activities. We can use it not only for ingesting, querying, storing and visualising data, but for developing machine learning models as well. Of course, one can say that doing data science is another functionality of this platform and this is definitely true. However, in this article, I would like to show you that instead of using Python, one can use T-SQL for doing predictions.
Click through to see how.
Comments closedMarco Russo and Alberto Ferrari are making a list and checking it twice:
The IN operator in DAX is useful in multiple scenarios to check whether an expression belongs to a list of values. It is oftentimes used along with the anonymous table constructors. IN is syntax sugar for the CONTAINSROW function. Just like CONTAINSROW, IN can be used with multiple columns at once although that syntax is not so common.
Click through to see how you can use IN in your work.
Tom Collins has a backup to restore:
I have a SQL Server with TDE enabled and the user databases are TDE configured. I need to take a backup and restore the TDE enabled database to another SQL Server Instance . Could you take me through the steps including prerequisites?
The answer is yes. And Tom is so kind as to show the answer rather than giving a flippant response, which is my modus operandi.
Comments closedGianluca Sartori continues a series on XESmartTarget. First up is a process to audit successful logins:
Sometimes you are not interested in the individual events captured by a session, but you want to extract some information from a series of events, by grouping and aggregating them. This is the case of events that happen very often, like, logon events, that can help you validate your story.
Imagine that you inherited a big, busy and chaotic SQL Server instance, with lots of databases and logins. One of the things that you probably want to do is track which logins are active and which ones are not and can be safely disabled.
Once you have that in place, how about unused objects?
The previous recipe showed you how to capture data from Extended Events sessions, summarize it in memory and then save it to a table in SQL Server, merging with any existing rows. This comes extremely handy when the events are not useful individually, but when the story is told by the aggregation of all the events.
Another possible problem that can be solved with the same technique is finding unused objects in the database. It looks like a trivial problem, but it’s not.
Read on to see how you can solve both of these issues.
Comments closedI have some thoughts on a recent announcement:
We could see the writing on the wall here ever since Cloudera and Hortonworks merged. Cloudera Distribution of Hadoop (CDH) and Hortonworks Data Platform (HDP) were both on-premises offerings that you could also get in the cloud. Post-merger, Cloudera Data Platform (CDP) was cloud-only and, to my knowledge, they have never released an on-premises version. Cloud versus on-premises isn’t itself the issue but it does tie in with the issue: in order for PolyBase to work, certain ports need to be exposed on your Hadoop cluster. Cloud offerings tend not to want to expose a bunch of ports to internal services and so PolyBase to CDP was a non-starter.
It’s about 30% bad news, 50% good news, and 20% meh news. Click through for the longer-form version of that.
Comments closedBrendan Tierney looks at the pycaret library:
In this post we will have a look at using the AutoML feature in the Pycaret Python library. AutoML is a popular topic and allows Data Scientists and Machine Learning people to develop potentially optimized models based on their data. All requiring the minimum of input from the Data Scientist. As with all AutoML solutions, care is needed on the eventual use of these models. With various ML and AI Legal requirements around the World, it might not be possible to use the output from AutoML in production. But instead, gives the Data Scientists guidance on creating an optimized model, which can then be deployed in production. This facilitates requirements around model explainability, transparency, human oversight, fairness, risk mitigation and human in the loop.
Read on for a tutorial as well as additional resources.
Comments closedThe Hadoop in Real World team moves data around in Elasticsearch:
In this post we will describe how to copy an index and its contents to a new index in Elasticsearch.
We currently have an index named account. We are going to copy the account index and its content to another index named account_v2 using the reindex API.
Click through to see how.
Comments closedGianluca Sartori continues a series on XESmartTarget:
For this post, the problem to solve is this: a session has an open transaction, is blocking other sessions, it’s been sleeping for a long time and it’s probably a good idea to kill it. This usually happens when there’s a problem in the application, that doesn’t handle transactions properly and leaves open transactions for a long time, maybe because it displays an error dialog, waiting for user input. There is very little that you can do in these cases: the blocked processes continue to pile up and the only thing left to do is kill the offending session.
Let’s see how to do that with XESmartTarget.
Let’s, shall we?
Comments closed