A Fine Slice Of SQL Server
Andrew Pruski notes an improvement with the April 2018 Windows update:
The April 2018 update for Windows brought a few cool things but the best one (imho) is that now we can now connect to Windows containers locally using ‘localhost’ and the port specified upon container runtime.
Let’s have a look at how this works.
Click through for a walkthrough.
Comments closedLori Brown shows how to use a SQL Agent alert to warn you if tempdb grows beyond a certain size:
Lastly, create a SQL Alert to notify you as soon as tempdb grows past the threshold you stipulate. Using the GUI to create the alert, you need to fill out every field on the General page and make sure the Enabled checkbox is marked. Create a Name for the alerts, then specify the Type as SQL Server performance condition alert. The Object should be Databases, the Counter is Data File(s) Size (KB), and the Instance will be tempdb. The alert will trigger if counter rises above the value. The Value will depend upon the cumulative size of your tempdb files. In this case each tempdb file is 12GB (or 12,288,000 KB), so the total size is 98,304,000 KB.
I liked the approach of only firing the SQL Agent job after a trigger was met, rather than running a job which queries and then creates an e-mail afterward.
Comments closedFirst, let me say that this person knows that
Select-Objectcan be used to select the properties we want, so he tried to guess the property name using a trial/error approach.The person tried:
Get-Service WinRM | Select-Object Startup, Status, Name, DisplayNameand also:
Get-Service WinRM | Select-Object StartupType, Status, Name, DisplayNameBut all of them were just empty.
There is a better way.
Comments closedIf you are using Docker for Windows and want to switch between Linux or Windows containers you can do this by right clicking the Docker “Whale” in the systray and selecting “Switch to Windows containers”:
….but no one likes clicking around do they!
There is an alternative way to do this which I use in my docker session demo’s which makes things so much easier and the switch is a lot quicker!
Click through for the Powershell call, which has the added benefit of being scriptable.
Comments closedGarry Bargsley gives us two ways to update dbatools on a schedule:
I have been using dbatools heavily since I was introduced to it. I have automated processes and created new processes with it. There are new commands that come out almost daily that fill in certain gaps or enhance current commands. One way to stay current with these updates is to update your dbatools install frequently.
How better to do this than to have an auto update process that will run daily and get the latest dbatools version for you…
I have put together two ways of doing this based on your preferred method. One is via a SQL Agent Job and the other is using a Windows Task Scheduler job.
Read on for examples of both techniques.
Comments closedAndrew Pruski answers an attendee question:
A few weeks ago I was presenting at SQL Saturday Raleigh and was asked a question that I didn’t know the answer to.
The question was, “can you change the location of named volumes in docker?”
This is one of the things that I love about presenting, being asked questions that I don’t know the answer to. They give me something to go away and investigate (many thanks to Dave Walden (b|t) for his help!)
Read on for Andrew’s answer.
Comments closedDavid Smith announces Microsoft R Open 3.4.4:
An update to Microsoft R Open (MRO) is now available for download on Windows, Mac and Linux. This release upgrades the R language engine to version 3.4.4, which addresses some minor issues with timezone detection and some edge cases in some statistics functions. As a maintenance release, it’s backwards-compatible with scripts and packages from the prior release of MRO.
MRO 3.4.4 points to a fixed CRAN snapshot taken on April 1 2018, and you can see some highlights of new packages released since the prior version of MRO on the Spotlights page. As always, you can use the built-in checkpoint package to access packages from an earlier date (for reproducibility) or a later date (to access new and updated packages).
David also spills the beans on when we’ll see MRO 3.5.0.
Comments closedRandolph West hits us with a misnamed SQL Server data type:
It occurred to me that we haven’t covered the
TIMESTAMPdata type in this series about dates and times.
TIMESTAMPis the Windows Millennium Edition of data types. It has nothing to do with date and time. It’s a row version. Microsoft asks that we stop calling itTIMESTAMPand useROWVERSIONinstead.Much like
DECIMALis a synonym ofNUMERIC, so too isTIMESTAMPa synonym ofROWVERSION. Please call it aROWVERSIONand pretend thatTIMESTAMPdoesn’t exist. Microsoft is deeply sorry for the confusion.
As I say, dates and times are hard. But at least this is easy: if you don’t use it, you won’t have problems with it.
Comments closedA second option is to use statistics profiling. This was introduced in SQL Server 2014 and is easily set by using SET STATISTICS PROFILE ON orenable query profiling globally using DBCC TRACEON (7412, -1). This trace flag is only available in SQL Server 2016 SP1 and above. Selecting from the dynamic management view (DMV) Sys.dm_exec_query_profiles you can do real time query execution progress monitoring while the query is running. This option will return estimated and actual rows by operator.
Click through for the full set of methods.
Comments closedOne way that we can do this is by signing the procedure (you can also use this with functions and triggers) with a certificate or an asymmetric key.
In this post I’m only going to look into signing a stored procedure with a certificate but the same ideas can be applied for the other objects and with an asymmetric key.
So…
We’re going to create a certificate and sign our stored proc using that certificate. We’ll then create a user based on the certificate and grant the new certificate user the appropriate permissions to run the stored procedure.
Every SQL Server DBA should know how to do this, but in my experience, it’s a small percentage who do.
Comments closed