If you’re using Windows 2016 in, its default config you may not get getting the IO performance that you were expecting. If you try a Windows 2012 server, the problem magically goes away. The question is why.
The answer is shockingly straightforward, Windows 2016 ships with Windows Defender installed by default, where Windows 2012 R2 didn’t. Windows Defender, if not disabled by GPO can have a significant impact on your server. On a client machine in Azure that was having the issue, DiskSpd on a disk which should have 7500 IOPs available was getting just 1100. The same applied to a stripe of 1TB disks which should have given us 20,000 IOPs was also seeing only 1100 IOPs. As soon as we disabled Windows Defender, we got the speed we were expecting.
Now I’m not going to recommend that you disable Windows Defender automatically, but you’ll want to set exclusions for it to ignore MDF and LDF files as well as BAK files (and any folders that you’re using for FILESTREAM data) so that Windows Defender it’s making your SQL Servers extremely slow.
The post IO Performance in Windows 2016 appeared first on SQL Server with Mr. Denny.
Azure is a great platform to use, and the new Managed Disks are fantastic as you just need to set it and forget it. However, if you run into the problem that I can recently with moving the VM from one resource group to another, you’ll run into a stumbling block pretty fast. I hit this because I put a VM in the wrong resource group, then the customer installed a bunch of software of the VM, then I went to move it to another resource group. That’s when I got the lovely error that said “Operation ‘move’ is not supported on Resource ‘MyVM’ with managed disks. (Code: BadRequest)”.
This error was a bit of a problem as I didn’t want to blow away the VM. I had to turn on a couple of Azure features in PowerShell (eventually you won’t have to do this, maybe you don’t have to do this already) and I was able to move the VM without issue. After running the following commands (and using Get-AzureRmProviderFeature to wait for the feature to be registered), the move worked.
Register-AzureRmProviderFeature -FeatureName ManagedResourcesMove -ProviderNamespace Microsoft.Compute
Register-AzureRmResourceProvider -ProviderNamespace Microsoft.Compute
At this point, I was able to move the VM and all it’s resources to the correct resource group using either PowerShell or the Azure Portal (I assume CLI worked as well, but I didn’t need to do more testing).
The post Moving VMs in Azure with Managed Disks Between Resource Groups appeared first on SQL Server with Mr. Denny.
There’s been a lot of discussions recently about SQL Agent jobs, proxy accounts, and job ownership. I wanted to try and clarify some of the myths out there, including job ownership and permissions.
The owner of a job is the context of the account, that the SQL Agent Job runs as. This account by default will be the user that creates this job. Normal application level jobs can have this be changed to an application level account, or another non-privileged login which has permissions inside SQ: Server to do the work that it needs to do. ]
The account that SQL Server Agents runs as must have sysadmin rights within the SQL Server Instance otherwise the SQL Server Agent will not be able to run. Also maintenance jobs that rebuild indexes or update statistics are going to need to have at least Database Owner rights within the databases, if not sysadmin rights within the instance.
Jobs for things like Replication and CDC should generally be left alone with sysadmin rights as that’s what they are going to need to function.
Jobs that are created by your application should run with whatever permissions that they need to function.
If you want (or need) to run job steps, where each job step executes as it’s own account, then proxy agents are going to be the way to you. You configure Proxy Accounts per job step in SQL Agent, so you can create different proxies for each job step if you want/have to. These proxy accounts can have whatever permissions the job needs to complete the task at hand. If a job step needs sysadmin rights to complete then you can create a SQL Server proxy for that step, if a job step needs minimal rights for the job step to run, then you can safely grant the proxy just the permissions that it needs.
If you want to give more into the security of SQL Server than I’d highly recommned that you look over a copy of my book, Securing SQL Server and check out my precon at the PASS Summit in 2018.
The post Who should own your SQL Agent jobs? appeared first on SQL Server with Mr. Denny.
Some of the complaints that I hear about building a new Kubernetes (K8) cluster is that a Cluster for testing out K8 is too expensive for companies to spin up. In the modern days, that just isn’t true. DCAC was able to build our brand new K8 customer lab for about $2,000 all in. Granted our cluster is a smaller two-node cluster, and there are only 128 Gigs of RAM per server (we’ll increase this if we need to). But this is a big enough cluster to show clients how K8 can be helpful to them and work on scripts for clients (and to publish online).
Is K8 running SQL Server that all DBAs should be able to test out to see if this is where they’re company should be going in the future? Yes for sure.
Is this something that’s too expensive for companies to try out? No, not at all. If your employer has a couple of thousand dollars for hardware, you can make a test lab happen. Even if the equipment gets thrown away six months later, it’s worth the small expense.
Needless to say, our lab for us to test out customer ideas is growing again.
The post Think a Kubernetes Cluster is to Expensive to Build? appeared first on SQL Server with Mr. Denny.