A Fine Slice Of SQL Server
Arun Sirpal wants to shuffle deck chairs in Azure:
As you can see I have a standard elastic pool which has 7 databases within it. I also have CloudDB and CRMDB that are single databases but not yet part of my elastic pool. How do I move them into it?
Click through to learn how to do this.
Comments closedPamela Mooney shows us how we can find broken code on our SQL Server instances:
Before we approached our last major SQL Server upgrade, I was curious about what might break. Yes, I had used the DEA to check our code against deprecated or discontinued code. But I am talking about code that might not have been used in some time and would break because objects no longer existed, or other things like that. So I wrote these scripts to refresh the sprocs, views and functions in our (non-production) environment.
Pamela’s motivation was to handle code which breaks during an update. You can also use this to see what you can probably deprecate—if the view doesn’t work, it can’t be in use. That means either someone should fix it or drop it.
Comments closedNiko Neugebauer shows us how to use the SHORTEST_PATH() function with graph tables in SQL Server 2019:
SHORTEST_PATH() function will allow you to traverse the given graph looking for the shortest path between different Nodes. It will use the Arbitrary Length Pattern to define the traversal path. This function will not return any results any results in SELECT clause because it must be used within MATCH clause only!
To my understanding because one of the mechanisms being used is depth-first search, in situation where multiple shortest path do exist, the function will return the first one only.
Click through for a detailed article on the topic. There are some nice parts to this but also a couple not-so-nice limitations in the current CTP.
Comments closedThe tool that we are going to use to make a classifier is called a convolutional neural network, or CNN. You can find a great explanation of what these are right here on wikipedia.
But we are not going to fully train one ourselves: that would take way more time than I would be willing to spend. Instead, we are going to do transfer learning, where we take a pre-trained CNN and replace only the last layer by a layer of our own. Then we only need to train that single layer, as all the other layers already have weights that are quite sensible. Here we exploit the fact that the images we are interested in have a lot of the same properties as those images that the original network was trained on. You can find a great explanation of transfer learning here.
Read on for a detailed example.
Comments closedJohn Mount covers an interesting issue you can run into when using xgboost:
While reading Dr. Nina Zumel’s excellent note on bias in common ensemble methods, I ran the examples to see the effects she described (and I think it is very important that she is establishing the issue, prior to discussing mitigation).
In doing that I ran into one more avoidable but strange issue in using xgboost: when run for a small number of rounds it at first appears that xgboost doesn’t get the unconditional average or grand average right (let alone the conditional averages Nina was working with)!
It’s not something you’ll hit very often, but if you’re trying xgboost against a small enough data set with few enough rounds, it is something to keep in mind.
John McCormack shows how you can use AZCopy to move SQL Server backups into Azure Storage:
AZCopy is a useful command line utility for automating the copying of files and folders to Azure Storage Account containers. Specifically, I use AZCopy for SQL Backups but you can use AZCopy for copying most types of files to and from Azure.
In this blog post example (which mirrors a real world requirement I had), the situation is that whilst I need to write my SQL backups over a network share, I also want to push them up to Azure Storage (in a different region) to allow developers quicker downloads/restores. This is why I need to use AZCopy. If I only needed my backups to be written to Azure, I could have used BACKUP TO URL instead.
Read on to see how John did it.
Comments closedArun Sirpal notes a change to the Azure SQL Database Service Level Agreement:
I am sure many missed the updates to Azure SQL Database SLA (Service Level Agreement). It used to be 99.99% across all tiers but split between two different high-availability architectural models. Basic, Standard and General Purpose tiers had its own model and the Premium / Business Critical tiers had a different one.
Read on to see the change.
Comments closedSteve Stedman has a new version of the Database Health Monitor:
Version 2.8.2 is the July 2019 Release of Database Health Monitor. There was a 2.8 release on July 17, 2019, followed shortly by a 2.8.1 and 2.8.2 release after a report of a bug in the blocking queries report that was fixed.
Read on to see what’s new.
Comments closedMax Vernon has a script which shows more than what xp_fixeddrives has to offer:
However, the command output doesn’t include the total size of each drive, making it impossible to determine the percent free space. If you’re in an environment where a separate team monitors disk space, and has alerts set when free space falls below a certain percentage, you may want to ensure you don’t breach those levels. The following script provides “the big picture” for your servers, since it provides total size, free space, available space, and the percent free. It does require the use of the documented and supported
sys.xp_cmdshellsystem extended stored procedure. The code uses the drive letters returned bysys.xp_fixeddrivesinside a cursor. Inside the cursor, we call the dos commandfsutil volume diskfree C:to get total capacity and free space, etc:
Click through for the script.
Comments closedAndy Kirk gives us five techniques for gussying up bar charts:
“Bar charts are boring”, say many people. “How can we make them more attractive”, say many desperate clients. Bar charts are ubiquitous because they are the reliable and trusted lieutenants often relied upon to show the always-common quantitative comparisons across different categories. Their frequent use can induce ‘boredom’ through the familiar but, in particular, accusations of inelegance can be raised with the default tool styles many creators lean on.
The five charts below just offer some different ways you might style them, through variations in the use of functional colour properties, chart apparatus and layout decisions, in particular. The reasons why you would choose to use any of these methods are varied and especially contextually dependent, based on matters like space to work in, range of quantitative values, size of category labels, number of bars, importance of precise readability. The charts are all showing the all-time top 10 most streamed songs on Spotify, as of July 2019, with data from wikipedia.
Read on for the five options.
Comments closed