I want to cover my experience of testing SQL Server Assessment in Azure Data Studio in this post. Due to the fact I tested it slightly different than most will the first time around.
Because I initially tested it against a Windows based container running SQL Server 2017. In fact, I used the same container image that I used in a previous post which you can read in detail here.
SQL Server Database
Before I started testing SQL Server Assessment in Azure Data Studio, I thought it would be a good idea to restore an older database on here. Because I’m a keen cyclist and used it for demo’s in the past I thought I would use the AdventureWorks database.
To make it more interesting I also decided to use the AdventureWorks2008R2 database. Now, this is where I first started having interesting issues.
Once I had copied the backup onto the container I could not restore it using the GUI in Azure Data Studio. To check all was well I connected to the container using SQL Server Management Studio.
My connection to the container appeared to be a bit better there, and I was able to do the restore with use.
SQL Assessment in Azure Data Studio
After I done the restore I then installed the newly released SQL Assessment extension which is currently in preview. Once I had installed it I went back to my instance which was running on a Windows based container.
From there, I selected the option to manage my server and selected ‘SQL Server Assessment’. Just to show this was running in a Windows based container I have highlighted the OS version below.
From there, I clicked the ‘Invoke Assessment’ option to get things started.
After around 20 minutes I stopped this from running because I was not getting anything back. Which I admit did surprise me a bit. So, I closed Azure Data Studio, opened it back up and then started it again.
I started getting results back after doing this. In fact, the whole process took around six minutes. Because I was curious I looked at some of the results it returned.
My first impressions are that it looks quite promising. Of course, because it’s still in preview there are a few things that need attention like the links to further information.
Because this extension was open source, I thought I would look at the GitHub page. One thing that caught my eye straight away was that the GitHub repo for it contained two notebooks. So, I decided to download them and have a look.
When I opened these, I realized they were alternative ways to run SQL Assessment in the notebooks. Which may prove useful if you want further insight as well.
You can read the GitHub page in detail here.
SQL Server Assessment Performance
One thing that was bugging me though was the performance against the Windows based container. With this in mind I thought it was only fair if I tested this extension against a Linux based container.
I can confirm that the navigation experience appears to be so much better using a Linux based container in Azure Data Studio. Which I am not surprised about since the current recommendation is to use Linux based containers for SQL Server at the moment.
However, I did have the same experience with the extension again after adding a Linux based container using the wizard in Azure Data Studio. So, I closed down Azure Data Studio and opened it up again.
After doing this it all worked OK. In fact, it ran in only two minutes, which is four minutes faster than the Windows based container.
Post testing advice
After testing SQL Server Assessment in Azure Data Studio I have a couple of pieces of advice.
Firstly, try and stick to Linux based containers with SQL Server on for now. Because the experience seems a lot better to me.
Secondly, if you deploy a new container using the wizard within Azure Data Studio I recommend restarting it before doing anything else.
Final word about testing
I hope you enjoyed reading about my experience of testing SQL Server Assessment in Azure Data Studio. Especially since it was originally done against a Windows based container.
It does make me wonder if we will see extensions for other offerings like dbachecks appear in the future.
If you have any views about anything in this post, such as other extensions you would like to see, feel free to leave a comment.
[…] There were a couple on interesting announcements relating to SQL Server on Azure Virtual Machines at Microsoft Ignite as well. Including the fact that the SQL assessment feature is now in preview. It’s hard to believe it’s been sixteen months since I posted about using SQL assessment in Azure Data Studio. […]