A Fine Slice Of SQL Server
“But wait, Andy. That’s not a best practice!” you say?
The pseudo best practice of “don’t install SSMS” is a misguided one–advice that I even fell into repeating in the past. However, that’s actually proposed solution to a best practice, rather than being itself a best practice.
I agree with Andy wholeheartedly on this.
Comments closedDave Bland talks about a few useful tables in msdb:
How long a database takes to backup is something that over the years I have been asked to get. These requests come for different reasons, sometimes it could be to find out how much it has increased over time, sometimes it could be to see if the backup job is interfering with other jobs and sometime it isn’t about duration at all, it is more about showing the backups were completed. Over the years I have had a number of auditors ask of backup history.
In order to get this information we need to pull data from two tables in the MSDB database, backupset and backupmediafamily.
Read on to learn about these two tables and to get a sample query. On systems with a large number of databases and a DBA who loves frequent transaction log backups (like I do), this table can get pretty big, so don’t forget to prune that data over time.
Comments closedThe cases of migrating our database in Azure become more and more every day. Azure SQL Database is the flagship SaaS service Microsoft Provides for hosting a relational database. But no matter it is the same engine there are still many features not supported or with limited functionalities in Azure SQL DB comparing to on premises SQL Server versions. For example, all cross-database references are possible in on premises SQL Server databases but is not supported in Azure SQL Database.
If we could check in advance and plan our migration based on those checks it would be time and effort saving. This is what Migrate to Azure new SSMS features are built for.
Click through for the options, some of which are simply informational and some of which actually do the work.
Comments closedDavid Klee shares some thoughts on hyperthreading in virtual environments:
I recommend leaving the hyper-threaded logical cores enabled in the host BIOS, but not depending on them for performance gains. Hyperthreaded CPU cores, or logical cores, should not be factored into CPU overcommitment rations as if they are full processor cores.
Every task that is triggered inside a virtual machine must be scheduled to run on a physical compute resource. These scheduled tasks must be placed into a scheduling queue inside the hypervisor layer before it gets its time on the physical compute resource. If the hypervisor is overloaded, or if the vCPU scheduling queues are imbalanced from an incorrect vCPU configuration, these queues can grow, and the performance impact on the vCPU performance can suffer.
Click through for an explanation of hyperthreading and David’s guidance on the topic.
Comments closedGokul Kamaraj and Liliana Kadar walk through tools for the DBA in Cloudera Data Platform:
You can use Cloudera Manager to automate the process of upgrading the operational database in your Cloudera Data Platform-Data Center (CDP-DC). Upgrades are provided through releases or maintenance patches. Cloudera Manager installs the releases and/or patches and manages the configuration as well as the restart process.
If you are using CDP on a public cloud such as Amazon AWS, you have to create a new Data hub cluster to upgrade to the new versions of various components. For more information about creating a new operational database Data hub cluster, see Getting Started with Operational Database on CDP.
Cloudera’s offering is a cluster-based offering; upgrades and patches all span multiple nodes (servers) and installation, configuration, reboot are all automated, including rolling reboots where applicable.
Click through for a walkthrough of other tools for Hadoop DBAs.
Comments closedDenny Cherry walks us through the steps for updating a SQL Server 2019 Big Data Cluster:
If you’ve been running your SQL Server 2019 Big Data Cluster (BDC) for a while now, you probably want to patch it to the current (or a specific) build version.
Upgrading a BDC is actually fairly easy. A couple of commands will do the trick.
Read on for the steps.
Comments closedMala Mahadevan has started learning a bit about PostgreSQL:
I had the opportunity of working on a project converting a postgres database to a sql server based one at work. I used this opportunity to learn more of this platform and decided to write some blog posts on it. I will be focusing quite a bit on how this compares with SQL Server as I go along and hope it will be useful.
PostGreSQL is an open source based relational database – it is easy to download and install from here. I chose the version available for Windows since mine involves a comparison with SQL Server on Windows and this is easier.
Click through for the installation steps.
Comments closedGilbert 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 closed