Hamish Watson explains that Windows authentication is not available with Azure Managed Instances:
Azure SQL Managed Instances do not utilise windows authentication – so your two methods of authenticating applications and users are:
– SQL Authentication:This authentication method uses a username and password.
– Azure Active Directory Authentication:This authentication method uses identities managed by Azure Active Directory and is supported for managed and integrated domains. Use Active Directory authentication (integrated security) whenever possible.
Hamish also elaborates on some of the trickier bits about Azure Active Directory for someone used to on-prem AD solutions.
Bert Wagner looks at the optimize for ad hoc workloads option in SQL Server:
Instead of filling the execution plan cache with plans that will never get reused, the optimize for ad hoc workloads option will cache a plan stub instead of the full plan. The plan stub is significantly smaller in size and is only replaced with the full execution plan when SQL Server recognizes that the same query has executed multiple times.
This reduces the amount of size one-time queries take up in t he cache, allowing more reusable plans to remain in the cache for longer periods of time.
I’ve run into several cases where this has helped SQL Server and don’t think I’ve found a scenario where it actively hurts.
It’s important to try and use an install set that is the same level of Service pack as your current install. Otherwise, you could end up installing multiple patches to get the SQL Launchpad service to work. Which is something discussed in a previous post here.
I know some companies have a central installer for SQL Server and then have all the updates in another location. Hence, if you are in such an environment be prepared to run multiple updates from that location after the install.
This is definitely one of the features which is easier to install from the beginning than to install after the fact.
Azure SQL Database – Managed Instance is fully-managed PaaS service that provides advanced disaster-recovery capabilities. Even if you accidentally drop the database or someone drops your database as part of security attack, Managed Instance will enable you to easily recover the dropped database.
Azure SQL Managed Instance performs automatic backups of you database every 5-10 minutes. If anything happens with your database and even if someone drops it, your data is not lost. Managed Instance enables you to easily re-create the dropped database from the automatic backups.
Click through for the Powershell script.
John Morehouse walks us through the Get-ClusterLog cmdlet in Powershell:
Sometimes you know that a problem occurred, but the tools are not giving you the right information. If you ever look at the Cluster Failover Manager for a Windows Cluster, sometimes that can happen. The user interface won’t show you any errors, but you KNOW there was an issue. This is when knowing how to use other tools to extract information from the cluster log becomes useful.
You can choose to use either Powershell or a command at the command prompt. I tend to lean towards Powershell. I find it easier to utilize and gives me the most flexibility.
Click through for an example, including of a method which filters out everything but error messages.
Adrian Buckman announces a new version of the SQL Undercover team’s Inspector:
We know some of you really hate linked servers so we have been working on a powershell collection which will allow you to install the inspector without using linked servers to centrally log the information and instead the powershell function Invoke-SQLUndercoverInspector will do the rest for you (We will be writing a blog post about how you can use this soon) – this is currently a pre-release version so it’s a work in progress – I must say a massive thank you to Shane O’Neill (b | t) without his powershell skills this wouldn’t turned out as well as it has, thanks Shane!
If you’ve already downloaded this version, be aware that there is a hotfix.
Andy Levy has a problem. Well, about 8000 of them. In part 1, he describes the plan:
How do you move eight thousand databases in a reasonable amount of time? I spent about an hour and a half one morning hashing ideas out w/ folks in the dbatools Slack channel, plus several conversations in the office and with our hosting provider.
Then, in part 2, he describes the execution:
We missed the estimated time for our go/no-go decision by five minutes. With the number of moving parts, databases in play, unexpected delays, and amount of testing we had to do, that’s pretty good! My colleague and I had some additional work we needed to take care of after the team declared the migration a success. Agent jobs needed to be enabled, overnight job startups monitored, things like that. We called it a day after about 14 hours in the office.
It was a nice success story, so check it out.
Jess Pomfret takes us on a journey to a desired state:
PowerShell DSC is a platform to support the concept of Infrastructure as Code (IaC). It uses declarative syntax instead of the usual imperative syntax of PowerShell. This means that you describe your desired state rather than the specific steps needed to get there. There are two modes for DSC, push and pull, although pull mode offers more features and scalability, we’ll look at writing our configuration and using push mode for this blog post to keep it simple.
This post covers initial installation and some of the initial configuration, so check it out if you’re new to DSC.
Andre Kamman walks through a particularly useful cmdlet in the dbatools package:
My answer to that is simple, I’m a major contributor to the awesome Powershell library dbatools. What I’ve contributed to that library are commands that will help automate the running and processing of queries from the DMV library of Glenn Berry
At some point in the life of a DBA we’ve all come accross his scripts. For the longest time I would advise people to google “Glenn Berry DMV”, and it will be the top result.
The scripts however, come in a single file per SQL Server version and you can’t run them all in one go. You would have to select a script, run it, and paste the result from Management Studio into an Excel sheet. Glenn provides an empty sheet with tabs ready to paste the various result sets in. I’ve automated this part, hope you like it!
Click through for a demonstration of this cmdlet and the useful output it generates.
Rich Brenner walks us through installing SQL Server 2019 and enabling Python support:
First things first, you’ll want to choose your version of SQL Server. Python is available on 2017 and greater. For this demo I’ll be using SQL Server 2019 Developer Edition (CTP 2.2).
With 2019 CTP2.2 they’ve increased the requirement of your OS too, in my example I had a spare VM with Windows Server 2012 laying around but I needed to update this to Server 2016. Check the relevant documentation for the version you’re using.
Click through for a step by step guide with plenty of screenshots.
Kevin Feasel
2019-01-29
Administration, Cloud, Security