A Fine Slice Of SQL Server
Chad Callihan doesn’t want to catch you foolin’ around with some other log file:
We’ve had some discussions about multiple data files for a database. Do we get much benefit from setting up databases with multiple log files? Not likely. Let’s discuss why that is.
Read the whole thing.
Comments closedHiram Fleitas builds a process:
I use an Azure Automation account to host my PowerShell script because it provides reusability and management of required modules. Schedules and logging are included but most importantly, it provides a REST API webhook that I can call from pipelines if necessary.
Click through to see how this all works.
Comments closedAndrea Allred spams the “burn it down” button:
Replication is not my favorite, it is kind of far from my favorite. No further than that. Little further.
When it breaks, it can cause havoc and it always seems to break at the worst time. Recently we noticed that our logfile was massive (like 3 times the size of the database) and that was making many of the other processes painful. We didn’t know how long the log hadn’t been clearing so we got to burn it all (kind of).
The first thing I did was tell replication that we were done with all the transactions that had been committed.
I’d say about 40-50% of the pain of replication is in how difficult it is to troubleshoot. Transactional replication is an order of magnitude easier than merge replication, too, especially on systems of non-trivial size and scale. The single most common question I get is “When will this row be replicated to the other side?” I can’t answer that with merge replication. The second-most common question is, “Why are things slower right now than before?” Can’t answer that either…
Comments closedMichael Mayer has a festive post:
This one picks up a topic that was intensively discussed a couple of years ago on Wolfram’s page: Namely that the damped sine wave
f(t) = t sin(t)
can be used to draw a Christmas tree. Throw in some 3D animation using the R package rgl and the tree begins to become virtual reality…
Click through to see how you can create this in R using 10 lines of code.
Comments closedAdam Aspin takes us through an important topic for paginated report developers:
In the previous article of this short series, you learned the fundamentals of creating datasets using DAX to populate paginated reports delivered using the Power BI Premium service. The next step is to appreciate the practicalities – and subtleties – of how data can be filtered using DAX for paginated report output.
As most, if not all, report developers come from an SQL background, it may seem overkill to devote an entire article to filtering data. However, DAX is very unlike SQL as far as filtering output data is concerned. Something as simple as classic OR logic needs to be handled differently from the techniques you may be used to – either as a SQL or as a Power BI developer. To ensure that you can deliver the report data that you need to populate paginated reports, take a detailed look at how to filter data in DAX datasets using the core
SUMMARIZECOLUMNS()function.
Read the whole thing.
Comments closedJared Poche explains why the TOP clause is superior to using SET ROWCOUNT:
I was presenting on how to use the TOP clause to break down large operations into short, fast, bite-sized operations. The mechanics are things I learned from writing processes that do garbage collection, backfill new columns, and anonymizing PII data on existing tables. I’ve just posted the slides and example scripts here if you are interested.
ARE THEY THE SAME?
The question was whether the SET ROWCOUNT command would work just the same, and the answer is sometimes yes but largely no.
Read on to see what Jared means.
Comments closedGauri Mahajan explains how continuous backup works in Cosmos DB:
To follow the exercise in this article, we would need an Azure Account with administrative privileges to operate the Azure Cosmos DB service. It is assumed that such an Azure Account and setup is already available and ready for use. The Continuous backup option is not available for all the editions of Cosmos DB and works only under certain configurations. The focus of this exercise is to understand the use-cases and options under which continuous backup would work in Cosmos DB.
There are four restrictions (as of the draft of this article), which should be kept in view while considering using continuous backup.
Read on for those restrictions and then how you can configure continuous backup to run.
Comments closedSaveen Reddy catalogs what’s new in Azure Synapse Analytics:
Quick Reuse of Spark clusters
By default, every data flow activity spins up a new Spark cluster based upon the Azure Integration Runtime (IR) configuration. Cold cluster start-up time takes a few minutes. If your pipelines contain multiple sequential data flows, you can enable a time-to-live (TTL) value, which keeps a cluster alive for a certain period of time after its execution completes. If a new job starts using the IR during the TTL duration, it will reuse the existing cluster and start up time will be greatly reduced.
Read on for the full list of updates.
Comments closedRayis Imayev troubleshoots an Azure Data Factory deployment:
To run these scripts, you will need to have Azure PowerShell installed on your DevOps agents (Azure Pipeline Agents). If your pipeline agents are Microsoft-hosted, then you’re good and all maintenance and software updates are taken care of for you. However, when you implement and install self-hosted agents, then additional software and component installation is solely your responsibility to maintain.
Recently, while I was configuring those pre- and post-deployment scripts for my Azure Data Factory deployment, I received the following error message, “Could not find the modules: ‘Az.Accounts’ with Version: ””.
Rayis does have a working solution, but I do recommend against installing modules in System32 because that directory is supposed to be reserved for Windows. Instead, for multi-user Powerhsell modules, I’d drop them in %ProgramFiles%\WindowsPowerShell\Modules instead, following the general Powershell guidance.
Andy Leonard gives us a bit of troubleshooting advice:
There are a handful of reasons for an “Ended Unexpectedly” package execution status in the SSIS Catalog. One reason is: the SSIS package was reporting a “Running” status when the SQL Server instance started.
Read on to see what Andy means.
Comments closed