2018 was an great year for blogging for myself. There was a decent amount of people reading articles that I’ve posted this year. My numbers are a bit off, as there were some issues I didn’t notice when I did an upgrade of my WordPress plugin that counts all the views. My plugin recorded about 100k views of my posts. Based on the stats that were recorded, my estimated page views are about 150k views for this year. Given the recovery this year, the lower number of page views make sense.
The most popular post that people were looking at was Difference between an Index and a Primary Key. With the next most popular post being my post on what MSDTC is, titled What exactly is MSDTC, any when do I need it?.
All of this is a pretty huge spread of topics, but it gives me some idea what people are interested in reading about.
Here’s to an great 2019.
Today we start the new year (yes I know, yesterday was Jan 1, but it was a holiday) with a new team member at Denny Cherry & Associates Consulting. Peter Shire (T) is joining our team as our new Director of Sales.
Peter is a successful sales and marketing professional with vast experience and connections throughout the SQL Server community. He began his career with a eight year stint at Microsoft. Peter enjoys sharing that he “knew Bill when he wasn’t even a Billionaire. Here we are when he was a meager 470-Millionaire!”
Of course, many of you probably recognize Peter from his eleven years at SentryOne and or his long tenure as the President of the Charlotte SQL Server Users Group (CSSUG). While at SentryOne serving as the Director of Partner Relationships, he designed, built and managed their successful Partner program as well as their relationship with Microsoft and also SQL Server MVPs.
He helped resurrect CSSUG and grow it into one of the nation’s leading users group. The success of Charlotte’s SQLSaturday #33 – the first of the large multi-tiered events featuring 23 SQL Server MVPs, was pivotal to bringing the 2013 Summit to Charlotte. Peter’s influence on the community extends beyond Charlotte as he created unique events that brought renowned SQL Server experts Grant Fritchey and Kevin Kline throughout multiple PASS chapters in the South. Kevin Kline interviewed Peter in July 2017.
Peter is a husband, father to three daughters and wrangler of two golden retrievers. He loves college basketball but his favorite player of all time is still in high school. You can follow Peter via Twitter and Instagram.
We are extremely happy to be able to welcome Peter to our team.
Today (December 24th, 2018) is the last day that you can click through to our DCAC’s Happy Holiday’s page, which just my visiting the page will trigger us to donate $1 to the Elizabeth Glaser Pediatric AIDS Foundation. If you come to the page after December 24th, 2018 you can still make a donation by clicking through on the page to make your own donation.
If you aren’t familer with the Elizabeth Glaser Pediatric AIDS Foundation; they have assisted 27 million pregnant women at more than 5,000 clinics in 19 African countries, The Elizabeth Glaser Pediatric AIDS Foundation continues to seek an end to global pediatric HIV/AIDS through prevention and treatment programs, research, and advocacy.
Needless to say, EGPAF is a great founation for Denny Cherry & Associates Consulting to be team up with. So click through, and we’ll make a donation.
Tis the season and Denny Cherry & Associates Consulting has a quick way for you to make a difference. All you have to do, is click through to our donation webpage, and that’s it. For clicking through, DCAC will donate $1 to Elizabeth Glaser Pediatric AIDS Foundation.
Every dollar accessed by your participation matters. Having assisted 27 million pregnant women at more than 5,000 clinics in 19 African countries, The Elizabeth Glaser Pediatric AIDS Foundation continues to seek an end to global pediatric HIV/AIDS through prevention and treatment programs, research, and advocacy.
Want to DONATE MORE? EXCELLENT! We’re matching funds! Click through here to donate to EGPAF!
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.
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).
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.
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.
IO performance for your Managed Instance may not be what you were expecting. Typically when we create databases, especially when working in the cloud, we create the databases pretty small. However, this can be a problem with Azure SQL Database Managed Instance. The performance that’s assigned to your databases which are hosed in Managed Instance will depend on the size of the database when you first create the database. This means that you’ll want to create the database at the largest size possible (1TB currently) to get the best possible performance from your managed instance environment.
From an attendee perspective, there isn’t a lot of crossover between the two conferences. Some people are able to attend both events, but for the most part, there is going to be minimal crossover between the two events.
From a speaker perspective, there is some overlap between the PASS Summit and Microsoft Ignite, because only a few speakers are selected to present at the Microsoft Ignite conference.
From a sponsor and exhibitor perspective, things are going to get a little more interesting. There is a decent amount of crossover between the vendors when they have a booth at the conferences. Vendors will probably end up having to pick an event to sponsor and have their teams attend.
This choice is where things will get interesting. While the PASS Summit is about DBAs and Developers, the Microsoft Ignite conference is about everyone in IT, including senior management. This most significant part of IT that Microsoft Ignite focuses on is the fact that IT management has the budget to buy tools. While DBAs and Developers are the ones that have to use tools, management has to decide if they should buy the tool, higher the consultant, etc.
The fact that senior management attends Microsoft Ignite and typically doesn’t attend the PASS Summit is going to be a challenge for the PASS Summit when it comes to attracting vendors and sponsors. One of the things that differentiate members of management from DBAs and developers is that management can spend money on tools, consulting, etc. This means that vendors that are deciding between going to the PASS Summit to talk to DBAs and Developers and going to Microsoft Ignite to speak to senior management are going to have a decision to make.
Given that DBAs and Developers typically can’t spend money (or they can’t spend much money) where members of management have much more money to spend. From a pure business perspective, going to the PASS Summit may end up being a hard decision for companies and people that typically go to both events.