A Fine Slice Of SQL Server
Gilbert Quevauvilliers walks us through the groups which can set permissions in Power BI:
As you can see from above it is good to know which groups can be used to assign permissions in the Power BI Service.
If there is anything I have missed, is wrong or needs updating please let me know via the comments section below.
Thanks for reading!
Gilbert has a nice matrix as well as lots of screenshots establishing the matrix’s veracity.
Comments closedGrant Fritchey shows us how to see the waits associated with a specific query:
And that my friends is only the waits associated with the one query. TA-DA indeed!
Now, we could get into filtering this stuff too. Toss the ones that have little to no duration, ensure that I only capture for a specific query or procedure, all would be helpful. However, this is how you can easily identify just the waits associated with a single query, and only that query.
Click through to see how. The one thing I’d caution here is that the query which received waits isn’t necessarily the query in the wrong—it might be the fourth or fifth session in a blocking chain. But this is a great technique for getting additional per-query info when you can control the experiment.
Comments closedGianluca Sartori takes us through workload replay ability in WorkloadTools:
One of the features I was asked to implement for WorkloadTools is the ability to replay commands to a database name different from the one recorded in the source workload.
This is something that I had been planning to implement for a while and it totally makes sense. Usually, you have two identical environments for the workload capture and replay, both with the same databases. Sometimes it makes sense to have two different databases as the source and target for the workload, for some particular reasons: resources constraints, ease of testing and so on.
WorkloadTools now supports replaying commands to a different database, using the
DatabaseMapproperty of theReplayConsumer.
Setting this up is pretty simple, though Gianluca does lay out a caveat.
Comments closedAchilleus walks us through a process to install Apache Airflow on a machine:
Airflow is an amazing tool by Airbnb and is a kinda defacto standard of ETL deployments in the Data Engineering domain nowadays. But at the same time, you can also use Airflow to schedule to ML pipeline and automate the whole ML pipeline(almost).
This is my attempt to install and set up a fairly robust Apache Airflow deployment for my needs. I am pretty sure there might be some better ways of doing it or add any enhancements to it. Any comments or suggestions are highly appreciated!
This is an easy-to-follow set of steps, so check it out.
Comments closedGoncalo Cruz has a plan to run SQL Agent jobs on the primary node in an availability group:
In SQL Availability Groups the SQL jobs have to be created in all replicas and you need to add logic at the beginning of each relevant job to make it execute on the primary database. (this only applies when the local replica is the primary for the database)
If you do not add the logic they will execute with success in the primary replica but they will fail in the secondary replica.
Read on for a process which keeps jobs from running except on the primary.
Comments closedErin Stellato shows us how to use relog:
Today I had to remember the steps for using relog. If you’ve never heard of relog, stay with me. It’s a tool for merging PerfMon files, and also for extracting smaller data sets from Perfmon. I still love PerfMon friends, I admit it. It’s been a staple for understanding performance on a Windows Server since I’ve started in IT. But I haven’t used relog in forever so I completely forgot the commands I needed.
Click through for usage instructions.
Comments closedHadi Fadlallah looks at one of my favorite stored procedures:
For this reason, Adam Machanic (a Microsoft MVP since 2004) developed a more powerful stored procedure called “sp_whoisactive” to fill in the gap between the actual needs of DBAs and the currently provided procedures (sp_who and sp_who2).
In the following sections, we will talk briefly about sp_who and sp_who2 stored procedure, then we will illustrate how to download and use sp_whoisactive stored procedure.
Though there is a nicer way to insert into a table based on WhoIsActive outputs.
Comments closedJamie Wick takes us through the less-trodden path:
SQL Server containers are gaining popularity as a way of enhancing and standardizing development environments for Windows & Linux based SQL databases. SQL containers allow developers to have their ‘own’ dedicated copy of a database, usually without the need for extensive server infrastructures. Additionally, a single computer can host multiple containers, each with a different edition/version of SQL Server. This allows the user to quickly switch between environments, without the need to reinstall. Currently, a popular option for implementing containers on Windows-based computers uses Docker.
For those not familiar with containerization, here is a Microsoft article on Windows containers.
I’d definitely prefer to use Linux containers, even on Windows machines. But if Windows-based containers is your thing (or you need to use them for some reason), Jamie’s got you covered.
Comments closedAaron Bertrand has a quick-and-easy method of restoring databases in a Docker container:
I have been using Docker containers for local development and testing for some time now; I first blogged about my steps into this brave new world back in late 2016. Most of the time, I just need to create some throwaway database, with a throwaway table, to prove a point or to validate an answer I’ve supplied.
Sometimes, though, I need to work with a real database. It’s a little trickier to do this in a container, because it’s isolated — I can’t just attach or restore from my Downloads folder. I could fire up a VM and attach there, but I actually don’t even use Parallels on my work laptop, and I find that using VMs leads to response times that are a lot more sluggish across the board.
It’s not too difficult to get files into your containers, provided your instance will have a total size less than the size of the container, and this quick tutorial proved to be very helpful.
Read on for the walkthrough. Aaron notes that this is a Mac-specific walkthrough, though the Windows and Linux versions are pretty similar as well, as we still create persistent volumes.
Comments closedKellyn Pot’vin-Gorman explains to us what the Automatic Workload Repository is:
The Automatic Workload Repository, (AWR) had been around since Oracle 10g and requires the diagnostic and tuning management pack licensing to use all of its features in Oracle’s Enterprise Edition database. Versions before 10.2.0.4 had limited collections vs. the modern reporting schema and every subsequent release of Oracle has added to it’s content, which explains the size increase stored in the objects/number of objects in the SYSAUX tablespace.
By default and since version 11.2.0.4, the AWR retention is 8 days and takes an automatic snapshot once per hour. It’s common for DBAs to up this retention to at least 31 days to capture a month of workload information and these snapshot identifiers can then be used to identify workload intervals for querying and reporting. Oracle can be also be configured to lessen the intervals between snapshots to change the granularity of the AWR reports, or my preference, the DBA or privileged user can take manual snapshots to identify an important beginning or ending of a period.
Kellyn goes into a good amount of detail in this post and, based on the title, promises at least a part 2. Though this could be a History of the World: Part I trick Kellyn is playing on us.
Comments closed