A Fine Slice Of SQL Server
David Fowler announces the release of Undercover Catalogue 0.2:
dbaTools have announced that a number of functions have been deprecated and will be removed before the release of version 1.
To future proof the Catalogue against these breaking changes, all deprecated functions have been removed from the Interrogation Powershell script.
Click through for the full change log.
Comments closedIf you manage a lot of SQL Server instances, you likely run into failed login attempts quite often. Perhaps you’re even wondering what client machine is causing all those failures. Since most environments run over TCP/IP; SQL Server helpfully logs the IP address of the client machine that made these failed login attempts to the SQL Server Error Log.
This solution is in T-SQL but shells out to cmd. It might be better suited for Powershell, but it does the trick.
Comments closedAndy Levy shows us how to use default parameters in Powershell:
By the 4th
Invoke-DbaQuery, I found myself thinking “this repetitive typing kind of sucks.” Then I remembered Chrissy LeMaire’s segment in the first PSPowerHour where she talked about default values, and her accompanying dbatools blog post. Most of the blog posts and demos of this feature focus on using it from the command line, so I had overlooked the fact that I could use it from within a script as well, and even change the values when looping.
As it turns out – it works inside scripts and functions as well, and can make them a lot easier to read. And you’re not limited to the default parameters every time you call a given function; you can override the defaults by specifying the parameters when you call it.
Andy gives us an example with default values for a SQL Server instance and database, but also shows us how to override that.
Comments closedMatt Usher announces Query Store is now available to all in Azure SQL Data Warehouse:
Since our preview announcement, hundreds of customers have been enabling Query Store to provide insight on query performance. We’re excited to share the general availability of Query Store worldwide for Azure SQL Data Warehouse.
Query Store automatically captures a history of queries, plans, and runtime statistics and retains them for your review when monitoring your data warehouse. Query Store separates data by time windows so you can see database usage patterns and understand when plan changes happen.
Given its power in the on-prem product, I’m glad that Azure SQL Data Warehouse is getting Query Store as well.
Comments closedAllison Koenecke takes us through the arulesSequences package in R:
In the following tutorial, we answer both questions using the R package arulesSequences [4], which implements the SPADE algorithm [5]. Concretely, given data in an Excel spreadsheet containing historical customer service purchase data, we produce two separate Excel sheet deliverables: a list of service bundles, and a set of temporal rules showing how service bundles evolve over time. We will focus on interpreting the latter result by showing how to use temporal rules in making predictive sales recommendations.
Our running example below is inspired by the need for Microsoft’s Azure Services salespeople to suggest which additional products to recommend to customers, given the customers’ current cloud product consumption services mix. We’d like to know, for instance, if customers who have implemented web services also purchase web analytics within the next month. Actual Azure Service names have been removed for confidentiality reasons.
Market basket analysis is an interesting topic, though in my limited experience, it really falls apart when you have a large number of products to compare, so it tends to work better with toy examples or limited product selections because when you have a 50,000+ SKU inventory, the lift of any individual combination of products rarely gets above the level of noise.
Comments closedHugo Kornelis explains SQL injection and how to fix it:
Translating this back to technical terms, the root cause of actual SQL injection vulnerabilities is a front-end program (often, but not always, a web application) that allows the user to enter data, forms a query by concatenating that data into pre-made query strings, and sends the result to the backend database server.
Once you know the root cause, the fix becomes rather obvious.
When I explain SQL injection, I like to explain it using two concepts: code versus data and technology boundaries. Ideally, your code is separate from your data, and within a single technology (e.g., in that .NET web app), that’s typically the case. But when you start to traverse boundaries, it’s convenient (and wrong!) to combine code and data together to pass everything across as a single stream of information. Parameterization is the way of keeping code and data separate as you cross those boundaries. Your data isn’t code and your code isn’t data and conflating the two is how attackers can inject arbitrary code into your system.
Incidentally, technology boundaries can happen within a single product, too: dynamic SQL is an example of this.
Comments closedEric Cobb has updates to the SQL Server Permissions Manager project:
I recently made some updates to my SQL Server Permissions Manager project on GitHub. These updates mainly consisted of bug fixes and documentation updates, although I did also add the ability to snapshot server level permissions such as “sysadmin” or “securityadmin”.
Given how much we want to audit permissions and how difficult it is to do a really good job of that in SQL Server, there’s space for a good auditing tool.
Comments closedKevin Chant has a list of tools you can use to manage SQL Server on Azure VMs:
From experience I know it’s important to know what applications you can use locally with Azure to manage SQL Server solutions. So you have the right tools for the job.
For instance, I was talking with some people at a client’s site the other day about deciding what application to use to future proof themselves.
In this post I will cover applications for use with Windows, MacOS and Linux distributions.
I don’t think I’m spoiling too much in saying that about 80% of these are the same tools you would use for on-prem work.
Comments closedIf using managed disks, the recommendation is to attach several premium SSDs to a VM. From Sizes for Windows virtual machines in Azure you can see for the size DS5_v2 you can add 64 of 4TB data disks (P50) yielding the potential total volume size of up to 256TB per VM, and if you use the preview sizes (P80) your application can have up to around 2PB of storage per VM (64 of 32TB disks). With premium storage disks, your applications can achieve 80,000 I/O operations per second (IOPS) per VM, and a disk throughput of up to 2,000 megabytes per second (MB/s) per VM. Note the more disks you add the more storage you get and the more IOPS you get, but only up to a certain point due to VM limits, meaning at some point more disks get you more storage but not more IOPS.
But there are a few other options too, so check them out.
Comments closedErik Darling explains that the IF block in a stored procedure won’t help you with performance:
Making plan choices with IF branches like this plain doesn’t work.
The optimizer compiles a plan for both branches based on the initial compile value.
What you end up with is a stored proc that doesn’t do what it’s supposed to do, and parameter sniffing times two.
Read on to see an example of this. If you really, really want to use an IF block, you could separate the components out into individual stored procedures and call those stored procedures independently.
Comments closed