A Fine Slice Of SQL Server
Bill Anton walks through some of the basics of managing an Azure Analysis Services cube:
The quickest win – from an ROI perspective – for Azure AS is the ability to pause the instance during extended periods of inactivity – for example, at night, when there aren’t any users running reports.
This can be achieved via the Suspend-AzureRmAnalysisServicesServer cmdlet we saw in the previous post.
Read on for a few tips of this ilk, including resizing the server.
Comments closedKenneth Fisher hits a frequent cause of login failure:
No go. Next thing to check is the password. The error I’m getting does indicate the incorrect password so it’s a distinct possibility. Now when I moved the server principal I made sure to copy the password hash from the old server so the password should be the same. However, I’ve made mistakes before, and odd things happen, so I decided to change the password just in case. Before I did I backed up the original password just in case.
The solution is something that I’ve seen a lot of sysadmins forget to do in their setup processes.
Comments closedWe can check whether or not you’re going to get an execution plan for this in two easy ways. You can capture the estimated plan, or run the query and capture the actual plan. Since most of the time, it’s preferable to work with the added runtime information that an actual plan provides, let’s start there. However, if you execute the query above and try to capture an actual plan, you will not get one. So, let’s capture the estimated plan instead. Here’s what we see:
Not much of a plan really.
Read on for that, and start holding your breath for a new execution plans book.
Comments closedHere’s the Connect Item involved – written by Michael Swart.
Trace flag 2390 can cause large compile time when dealing with filtered indexes. (Active)
https://connect.microsoft.com/SQLServer/feedback/details/2528743/Huh. “What does that have to do with clustered columnstore indexes?” you might ask. No, really, it’d be a personal favor if you *did* ask…
Ask and then read on. This is the kind of post I’d send to someone wanting to learn how to troubleshoot issues.
Comments closedAndrew Pruski notes that you can delete active databases in SQL Server on Linux:
What we’re going to do is delete the database files whilst the instance is up and running. Something you can’t do to a database running in an instance of SQL on windows as the files are locked.
Click through for the results, which are counter-intuitive for Windows admins, as well as the reason for this behavior.
Comments closedMonica Rathbun begins a new challenge by describing the importance of one tiny form control:
When I worked at the Port of Virginia, I was a little less experienced in SQL and didn’t notice this lovely little option. I of course failed to set it. Can anyone guess what happened? YEP, we got low on resources in the wee hours of the morning and SQL kicked off an Error 017-Insufficient Resources. Thousands of emails were generated and caused the Exchange server to go down as well as some other issues that arose because of this. The worst part is that all the emails had to finish processing before we could delete them from the system. I think when all was said and done there was well over 250k messages it created.
She also includes a list of important alerts; just make sure to set the delay.
Comments closedKenneth Fisher discusses the Azure Resource Explorer:
Now this is just default tab. The GET, PUT tab. Which basically shows you the get command of the resource manager API that calls this information, and if you hit the edit button you can actually change information in the JSON output and issue a PUT command to send it back. I’ll admit up front that this is a bit beyond me as I don’t do API calls and I’m new enough to Azure that I don’t know what I can and can’t change (everything I’ve tried so far hasn’t worked). There are several other tabs, though, including a Powershell one and I’m a bit more familiar with Powershell. In it, you can see some of the Powershell commands associated with the resource manager and this particular object.
Read on for more information.
Comments closedVincent-Philippe Lauzon looks at how you can size databases with an Azure Elastic Pool:
We can’t change a database maximum size in the portal (as of December 2016).
Using ARM template, it is easy to change the parameter. Here, let’s simply show how we would change it for an existing database.
Building on the example we gave in a previous article, we can easily grab the Pool-A-Db0 database in resource group DBs and server pooldemoserver:
Click through for all the details. I highlighted this snippet as another point that the most important language for a Windows administrator to learn nowadays is Powershell.
Comments closedFortunately, the
.NET SqlClient(and other ODBC drivers as well) has a built-in solution. Your application’sconnection stringhas quite a few parameters available to provide configuration and information, and one that seems to get overlooked isApplication Name. This one does exactly what it says on the tin – it lets you specify a name that will be displayed to anyone looking for it in SQL Server, includingsp_whoisactive. Anyplace you have the ability to write a connection string, you can use this. It costs you nothing!
You can also start getting fancy with resource governor as well, segmenting pools based on application name.
Comments closedKenneth Fisher diagnoses an error when calling sp_refreshview:
Msg 297, Level 16, State 3, Procedure sp_refreshsqlmodule_internal, Line 74 [Batch Start Line 2]
The user does not have permission to perform this action.
Click through for the solution to this problem.
Comments closed