Hello everyone, this is John your Austin SQL Server Consultant here and today I am going to answer a question that comes up often so I wanted to blog about it for everyone. The question of the day is where can I download the previous SQL Server Updates?
The History towards Updates
Back in the day when we were young but not a kid anymore there were service packs and cumulative updates. We could download these separately and all of the updates were easy to find. Now today, if you click on a KB article to download an update you get pointed to the latest update as shown below.
How far is My SQL Server on Updates?
This is also another great question. My favorite place to find all the history of updates toward SQL Server is the SQL Server Build List Blog. You can cross-reference this towards your version by running the following query below.
You can use SELECT @@VERSION to get your current version number.
I fully get exactly why Microsoft is trying to point everyone to the latest update. Normally, it makes perfect sense but let’s take a look at today Jan 9th, 2020. I am planning to update SQL Server 2017 to CU17. Its been out for two months. Today CU18 is released and if I wasn’t careful I would have downloaded a different update than expected.
SQL Server Blog List is a great resource for finding a list of all SQL Server Updates
Getting a previous SQL Server Update
So, on to the solution. It’s actually an easy one but also one that is easy to overlook as well. Let’s go back to the new standard update page for SQL Server updates.
That is right, the Microsoft Update Catalog is your best friend to find all your updates for Microsoft products including SQL Server. You can search for the product you want. For example, in this case, I am looking for SQL Server 2017 and can see all the previous updates for SQL Server.
All the SQL Server Updates can be found in the Microsoft Update Catalog
If you enjoyed this tip and found it helped make sure to join our newsletter so you can enjoy more free tips, tricks, and video demos.
Hello everyone! This is your SQL Server Consultant in Austin, TX and due to some posts on twitter about SQL PASS recordings costing $999 I wanted to share some of my favorite places to find free SQL Server training videos. I hope this helps make your data fast, secure and highly available in 2020 and beyond!
Attending conferences is nice but free recorded sessions are priceless!
Those who know me know I love music. I especially love the underground non-mainstream content. Therefore, my first recommendation is UserGroup.TV. As of December 27th, there are 127 videos tagged as SQL Saturday alone. Shawn goes around to almost every Tech conference he can find and brings his rig and records sessions for the community.
Are you in love with the new pop singles? Wish you could hear them before they hit the radio? If you like your tech like your music than Microsoft Ignite is for you. Every year Microsoft puts on a conference called, Ignite. This conference is usually where Microsoft will break its cutting edge tech. My favorite thing about the conference is that the content is available online for free. Midway through the page, you can search through the massive collection of free recorded sessions.
Next up, is the consistent greatest hits. Almost every session is a banger! This reminds me of my favorite Microsoft Data Platform conference. This is SQLBits and yes, their video content is also available for FREE.
Microsoft made SQL Server 2019 Generally Available this week we want to share some videos and code examples of our favorite new features. Most of these will make your code go faster without any code changes!
We have been testing SQL Server 2019 for months and hope you enjoy these features as much as we do!
I am not sure why but sometimes I am glutting for punishment. Maybe its why I try every backup and restore solution I can get my hands on? While Microsoft has done an amazing job at building the best relational database engine Azure Backup for SQL Server Virtual Machines has some architecture problems. In this post, I will showcase things you need to focus on, problems, and workarounds for your initial run with an Azure Backup for SQL Server VMs.
What’s Azure Backup for SQL Server Virtual Machines (VMs)?
If you take a look at Azure Backup they added functionality for backing up SQL Server databases inside an Azure VM. This seems like a really cool feature. Let’s use the same technology we use to backup our VM’s to also backup our databases. You know the whole one-stop-shop for your disaster recovery needs. Comes with built-in monitoring and it also eliminates the struggle some people have with setting up certificates, encryptions, purging old backups in blob storage, backups and restores from blob storage. It is really nice to also have a similar experience as restoring Azure SQL Databases as well.
Unfortunately, the product doesn’t work as expected at this point in time. I would expect any database backup tool to be able and backup the system databases by default without any customization. Therefore, Last night I setup my first Azure Backup for SQL Server Virtual Machines in the Backup Vault and this morning you can see my results below.
Azure Backup for SQL Server VM’s gets 0/3 system databases backed up by default
Now we will dig into concerns and initial problems with Azure Backup for SQL Server Virtual Machines (VMs).
Automatically Backup New Databases
Having the ability to backup new databases automatically is taken for granted. So much, that I noticed that Azure Backup for SQL Server VM’s will not automatically backup new databases for you. That’s right. Make sure you remember to go in and detect and select your new database every time you add a database or you will not be able to recover.
Azure Backup for SQL Server VM’s has an interesting feature called Autoprotect. This should automatically backup all your databases for you. Unfortunately, this does not work. Yes, I double-checked by enabling autoprotect for a VM and I added a new database. The database didn’t get backed up so I had to manually add the database.
Simple Recovery Problems
Looking into the failures for my system database backups I noticed something interesting in the log for the master database. It looks like you will get errors with the only SQL Server backup policy created by default. The reason is the policy includes transactional log backups and as you know its impossible to take a transactional log backup if your database utilizes the simple recovery model. Now, most backup tools know how to roll with databases in simple and full recovery.
Looks like Azure Backup for SQL Server VM’s is not one of these tools that easily allow you to mix databases utilizing both simple and full recovery models.
Yup, simple recovery model is no bueno..
So, how do we get around this? It is not too hard. Just create a new backup policy that does not include transactional log backups and assign it to your databases that utilize the simple recovery model.
Transactional Log Backup Problems
So, what happens when you try to take a transactional log backup of a database that doesn’t have a full backup? It fails. This is by design. If you try to take a log backup in this scenario with T-SQL it will fail as well. That said, several 3rd Party open source backup solutions like my recommended one can gracefully handle this for you. It can take a full backup instead of the log backup. I have grown to expect this behavior.
Here is what you will see in the logs of Azure Backup for SQL Server VM’s.
You have to force a full backup or wait until the scheduled full backup occurs. Yuck!
So, the workaround here is simple. You can force a backup. This will start the process of allowing your schedule log backups to work as designed. You could also wait until the scheduled full backup runs but know this means you will not have point in time recovery until that full backup runs. There should be an option to perform a full backup instead of a transactional log backup if a full backup does not exist. This would prevent the transactional log scheduled backups from failing.
Things to Know!
Azure Backup for SQL Server VM’s pricing goes off of storage as well as instances of SQL. By default, compression is not used for the SQL Server Backups. You will most likely want to make sure you enable this to save some money.
There are many documented limitations that we didn’t cover in this blog post. Some shocking ones to me are SQL Server Failover Cluster Instances and don’t configure backup for more than 50 databases in one go
Once again it is PASS Summit week in Seattle. This is the biggest event in the world for SQL Server and Microsoft Data Professionals to gather to connect,
Frye First Time Speaker
share and learn.
I will never forget being anxious and scared the first time I gave a presentation at PASS in front of hundreds of people. Therefore, one of my favorite traditions during PASS Summit is to find a first-time speaker at PASS and try to make them at ease by having their friends and peers wear something special during their first presentation.
This year, I couldn’t think of a better person than Jeremy Frye. I have known Jeremy for years. I have been blessed to work with him at RDX. While everyone in the community knows him as the speaker who wears a Pittsburgh Pirates hat at SQL Saturday’s he is an inspiration to me. He is proof that good guys can be successful in this community. He is one of the most humble, kind and helpful people I know. I have been
Dress like Frye Day!
blessed to see him share his knowledge for years and am excited for everyone to do so this week as well. Therefore, to help Jeremy for his first session we will be giving out Pittsburgh Pirates hats to support Jeremy. If you can make Jeremy’s session on “Speed Up Your SSAS Data Refresh with Dynamic Partition Processing” at 11:00 am in room 604 on Friday (I like to call it FryeDay) come on by the RDX booth and ask for a Pirates hat. When you see Jeremy around this week tell him you got this and you cannot wait for his session!
With Microsoft’s Ignite conference this week a lot of new features are being advertised all over the internet. Most likely if you are following along you have heard of Big Data Clusters, Spark, Performance Tuning, and security features. I am really excited about this new feature and hope you are as excited about it too.
In SQL Server 2016 we saw Query Store. Query Store was a game changer to help database administrators identify troublesome queries. Query Store helps DBAs make those queries run faster. Microsoft’s marketing team even jumped on to help coin the phrase, “SQL Server It Just Runs Faster.” With SQL Server 2017, this started to get even better with automatic tuning. Don’t worry database administrators. Automatic Tuning will just enhance your career and not replace it.
SQL Server 2017 Automatic Tuning looks for queries where execution plans change and performance regresses. This feature depends on Query Store being enabled. Note, even if you don’t turn on Automatic Tuning you still get the benefits of having access to the data. That is right. Automatic Tuning would tell you what it would do if it was enabled. Think of this as free performance tuning training. Go look at the DMVs and try to understand why the optimizer would want to lock in an execution plan. We will actually go through a real-world example:
Automatic Tuning with SQL Server 2017
First, let’s take a quick look at the output of the data. You can find the query and results we will focus on below.
SELECT reason, score,
script = JSON_VALUE(details, '$.implementationDetails.script'),
planForceDetails.*,
estimated_gain = (regressedPlanExecutionCount+recommendedPlanExecutionCount)
*(regressedPlanCpuTimeAverage-recommendedPlanCpuTimeAverage)/1000000,
error_prone = IIF(regressedPlanErrorCount>recommendedPlanErrorCount, 'YES','NO')
--INTO DBA.Compare.Tunning_Recommendations
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON (Details, '$.planForceDetails')
WITH ( [query_id] int '$.queryId',
[current plan_id] int '$.regressedPlanId',
[recommended plan_id] int '$.recommendedPlanId',
regressedPlanErrorCount int,
recommendedPlanErrorCount int,
regressedPlanExecutionCount int,
regressedPlanCpuTimeAverage float,
recommendedPlanExecutionCount int,
recommendedPlanCpuTimeAverage float
) as planForceDetails;
I will break the results down into two photos to make them fit well in this blog post.
Free Tuning Recommendations with SQL Server 2017 (1/2)
Free Tuning Recommendations with SQL Server 2017 (2/2)
Now we know in the query store query_id 2271 has CPU time changing from 7,235ms to 26ms. That’s a big difference. Let’s take that query and look at its findings by using the tracked query report inside SSMS.
Find my Changed Query. Did the plans change?
Here we can see the major difference between the two execution plans. One is averaging over 14 seconds in duration while the other is under a second.
Query Store showing the performance difference between the two plans
Now we can select both plans on the screen above and look at the execution plans side by side inside of SSMS. When doing so, we see the common example of the optimizer determining if it is better to scan an index vs a seek with a key lookup.
Using SSMS to compare auto tuning recommended query.
To complete the example I want to point out that automatic tuning would lock in the index seek plan (Plan 2392). In SQL Server 2016 you can do this as well manually inside Query Store. With SQL Server 2017 it can be done automatically for you with Automatic Tuning. If you have ever woken up to slow performance due to an execution plan changing and performance going through the drain this might be a life saver.
Some of my friends know I am a huge fan of the song “Havana” by Camila Cabello. They also know I like to remix songs and if I was to remix the song I would just change the word “Havana” to “Wheeling, WV” because half of my heart is in the Ohio Valley.
Why?
Wheeling is where I grew up as an adult and to this day it is one of the special happy places I like to visit. On April 28th Wheeling will host their the third SQL Saturday. Anyone can attend for FREE! l look forward to sharing my favorite city with the SQL Community and my SQL Family.
Free SQL Server Training on April 29th in Wheeling, WV
Procure SQL will be teaming up with the Wheeling Chapter of AITP (only IT group in Ohio Valley) to bring some expert SQL Server training from MVPs, MCTs, and community experts to the Ohio Valley. I hope Data Platform professionals in nearby cities like Columbus, Pittsburgh, Harrisburg, Cleveland and Washington DC join is un the fun as well.
Things to Do?
Check out this quick five-minute video to find out some of the great things you can do in Wheeling, WV and why I fell in love with Wheeling!
Food
Colemans Fish Market – It is #1 on TripAdvisor for a reason. Best-fried fish sandwiches. Ye Old Alpha – The Bridgeport brownie is legendary good. DeCarlos Pizza – Wheeling’s special version of Pizza. If you get it make sure to eat it quick. Locals typically will eat it on the hood of their cars. Undos – My personal favorite Italian food restaurant.
Places to See:
Good Mansion Wine – If you like wine, the selection here is fantastic. They will also have an open wine tasting event April 27th at 6 pm. If you are looking for something fun to do the night before the event I would recommend this. Suspension Bridge – If you like history. You have to check out one of the oldest suspension bridges in the USA. You can still walk and drive across it. Wheeling Artisan Center – Great small tour of the history of Wheeling, WV. Center Market – Historic part of town with a lot of shops and places to eat. Its an easy walk from the SQL Saturday venue. Oglebay Resort – Depending on the weather the driving range or ski lift will be open. Seriously, a great five-star resort with epic holiday events including Christmas lights, ogalbayfest, and 4th of July. Wheeling Island Casino – If you like to play cards and win money its a great location. Used to do it a lot on lunch breaks.
Last weekend I had a blast speaking at the SQL Saturday in Chicago. It was awesome to share my knowledge and also catch up with some good friends. My talk was on Automating the Pain Away with Query Store and Automated Tuning. I hope this session helped people leverage Query Store and Automated Tuning to resolve their parameter sniffing problems.
John Sterrett teaching Performance Tuning at SQL Saturday Chicago
This week at MVP Summit I got to talk with a friend who loves profiler. We were talking about capturing workloads and doing analysis on them. T-SQL or other 3rd party tools like ClearTrace, ReadTrace would be used to aggregate the data to get insight into the top offenders. I mentioned that workload analysis could be done with extended events without writing a single line of T-SQL. This was a lightbulb moment for him. Quickly, I learned that he is not alone and that there are a lot of people in the community who didn’t know this either.
I am including a quick video below to show you why Extended Events is a great solution for finding top offenders in a workload.
This site uses functional cookies and external scripts to improve your experience. Which cookies and scripts are used and how they impact your visit is specified on the left. You may change your settings at any time. Your choices will not impact your visit.
NOTE: These settings will only apply to the browser and device you are currently using.
