AzureDataStudio Blog series
1) Azure Data Studio and SQL Saturdays
2) ADS Keyboard Short cuts – part 1
3) ADS Keyboard Shortcut and Name drop
4) ADS Server and Switching Profiles – Setting up ability to switch in different dashboards, user profiles
5) Azure Data Studio – Database Status Dashboards – Steps to create a dashboard and clever trick to run a script across different SQL versions with different syntax
6) ADS Extensions – Brief intro to extensions
7) SQL Saturday Belgium – My 1st SQL European SQL Saturday experience
8) ADS Error switching in PowerShell– with switching to PowerShell Kernel in Notebooks and fix
9) Azure Data Studio Notebooks Markup– Annotating notebooks, headers, lists, and formatting, with a clever hack to get around text alignment issues
10) Azure Data Studio – wow more time saving – Searching for servers or adding in new servers
11) Leeds and Manchester Data Platform Meetup AzureDataStudio – Trip over the Pennines and another great feature to compare clipboard and the active window
12) Azure Data Studio -The gift that keeps on giving – Even easier filtering for files
13) Less AzureDataStudio more Yorkshire– Trip to Haworth
14) Getting the git repository- AzureDataStudio. And an offer that I might regret this one! Maybe not – link to the repository
15) This contents page.
16) Azure Studio formatting TSQL – easy formatting of SQL text
17) Azure Data Studio Snippets
18) Azure Data Studio my snippets – list of TSQL files and link to the snippets sql.json file
19) Azure Data Studio Notebook Charts – charting in Notebooks
20) Azure Data Studio – Working remotely, setting up ADS to run without installation
21) Azure Data Studio – Run book– How I utilised Notebooks for a SQL upgrade/migration
22) Azure Data Studio Data Scripter– Ability to script tables to insert statements and specify where clauses
23) Azure Data Studio– More short cut keys – duplicating lines, compare clipboard to the active window
I have decided to call it a day posting daily, technically I didn’t post every day in March, I made 25 continuous days blogging in some weird and wonderful locations (Belgium trains for one) also hotel room after nights out. The last week had been a challenge and I could have continued but not with content I’d have liked. I had some time to looks at the Juypter Notebook feature and thought this could be really useful but felt I needed to learn some new skills to do it justice.
So I am not abandoning the blog but looking to change it was daily to weekly to add some rich content ( without making the previous sound weak 😉 )
More of a deep dive and an understanding of some of the deep dive areas that can be explored.
And all respect to all those great SQL bloggers out there who somehow manage to do this daily. I have learned a lot and hopefully, I shared something useful in the process. Now for a different challenge.
It’s half 8 again and I am starting the blog. I am not accustoming well to continued home working, I am usually an early riser but the current situation I didn’t sleep and slept into 8.30, by that time I usually have been in work 30 minutes and had an hour on the train reading. Things need to change.
Anyhow, The PowerShell Command Explorer is a useful tool. I am a big fan of DBAtools and use it a lot. I usually have the webpage open on the doc section for syntax, memories going at my age and it’s always easier.
The PowerShell Command Explorer sits on the left-hand bar (which you can move around to where you like)
Which results in an extensive list of PowerShell commands that can be ran
If you click on the left-hand pane and start typing diskspace (in my example I am trying to find a SQL drive with enough space to restore a large database on a test server)
Once typing in click on the filter option it filters the results to ones with just diskspace in the title
If you click on the pencil Icon it will paste the current command into the active window ( saves typos!)
Clicking the (?) icon you get the associated help
This is piped to the terminal window to show the syntax of the command
Also a hyperlink to the forementioned dbatools page. Saving you time looking it up!
I said I would attempt to blog every day in March around Azure Data Studio and it has been a challenge especially the last few days. I’m now at the point of looking for topics to blog around, so I started looking through the short cut keys and he’s some that I could utilise if I can remember them
- Copying the current line to the next line. Doesn’t sound all that but so often I will highlight a single line copy it to the next line, comment it out and then make some changes to the 1st line. That way I have the original to fall back to when I mess something up or it didn’t work as planned. SHIFT + ALT + Down Arrow
to add line comment its CTRL+K CTRL+C or +U to remove, these commands can also be amended to whatever you want.
2. This one is really cool ( and I am now second-guessing myself if I haven’t already mentioned this already so I had to review the contents here http://tiny.cc/ADSContents.
You can compare what is on the clipboard with the active window. So scenario you paste some code into a new window, change a few things, you can see exactly what has changed, using CTRL+K C. I also used this recently to compare two complex string, copy one then the other to clipboard and hit the key combination
3. There are so many short-cut keys some obvious what they do and a few need a bit of tweaking, the 3rd one I’d add is Toggle block comment, anything that makes me more productive CTRL+SHIFT +A to add and remove which is a novelty
(Just in case you wanted to know what block comments looked like !)
I thought that I would cover some of the great extensions that make ADS so much more, one that is quite nifty and I can see a use for is the data scripter extension.
This extension was written by Sean Price and also the db snapshot tool ( which is very useful as well) the github link is here
1) So to use, install the data scripter extension
2) Navigate to a table you want to script, right-click and select Script Table Data
3) You get a popup box as shown below
4) You can amend the TSQL statement above to just select columns you want or even add a WHERE clause. I have filtered the data for one specific date. Hit return
5) The scripter provides the create table statement commented out
6) And also insert statements, as shown it’s just gives me the insert statements for that specific day
This is another tool in the arsenal of the productive data professional. I can use to create test data for various scenarios from one server to another.
I have just used SQL notebooks today for a SQL migration from SQL Server 2000 database to SQL Server 2016 with many, many updates to code, procedures, configuration changes, linked servers and thought I would share some thoughts on using SQL notebooks for production change.
I had 30 + code sections with multiple steps, including scheme change, data fixes, and 50 odd stored procedure updates. I had for each one a text section with what the change actually was, using markup sections.
There were several steps that I couldn’t run from the main SQL notebook but I annotated the code that needed to run, as this was a SQL 2000 migration so required backup/restore to SQL 2008 before the leap to the future and SQL 2016.
It was all contained in one document and I could still see the steps not available to run in SQL notebooks I just had to just copy and paste, either Enterprise Manager or PowerShell and execute that element.
This was a complex upgrade and things might need tweaking as running through each section some steps couldn’t run easily in test environments, so it was expected that some tweaks would be needed this was where SQL notebooks are so much better than running stand-alone scripts.
So anything that changed from what was planned I added a new text section and added comment using the emphasize option, so I can add notes when things don’t work as planned for the record.
SQL Notebooks advantage over running 30+ separate SQL scripts
- it’s obvious where you are up to
- you can step through and run each section easily
- the result set is retained for each section
- you can save all the result with the scripts
- you can annotate anything that needs tweaking if it doesn’t work as planned as you go along
any gotchas/changes? Yes
- make sure you specify the database context your running in, so USE [Database]
- there were a lot of commands completed successfully. I’d consider adding in print statements to keep a better record, everything succeeded but that might not have been the case.
- don’t be caught by trying to highlight a section of code in a notebook, it won’t run the section you have highlighted but the whole section
Its difficult times we live in at the moment with things going on, like many, I am working from home and sometimes the connection might not be the best it could be. I remote in using the companies I am working at’s chosen VPN and can execute things locally on my machine against the network, like I am in the office, however somewhat slower at times.
I can RDP to one of the servers that has management studio installed on it if it should be is another question for another day. Run my queries without risk of connection issues as when I connect back in the query is still potentially running.
But I wanted to use Azure Data Studio !!.
I don’t have access to just install Applications on any old server.
but that’s not an issue. If you copy the install folder that Azure Data Studio is installed to in my case c:\azuredatastudio-windows-1.15.1 to the remote server in question, if you are allowed and you have download ADS as Zip version you can just run the exe and bingo
It also on startup creates the user-specific files into the following folder
so if you copy your setting.json and snippets files, you automatically get the servers and snippets and configuration from your local machine to the remote one.
I love this product, it keeps getting better and better and I keep finding new things out daily, the latest version came out today (and I only just noticed) I was coming to write the daily blog when I noticed, so it only seems right I should mention one of the new features.
Azure Data Studio Notebook charts
So I created a new notebook and created some dummy data (one thing I noticed charting normal queries isn’t that great, but I will endeavor to create a routine that puts the output into the format that charts can use easier)
which returns a result like this
Just above the results, you can see the options to export to CSV, EXCEL, JSON and also at the end create a CHART click on it and you get visualizations
Unfortunately it doesn’t seem to allow saving in the chart format but still its early days a bit more playing to see what can be achieved, the options to export to Excel will be really helpful for runbooks