AzureDataStudio Blog posts contents page

Posted on March 15, 2020 by martincroft

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
24)
25)
26)
27)
28)
29)
30)
31)

Posted in Azure Data Studio, Azure Data Studio Extensions, Dataplatform, Docker, Markup, SQL Notebooks | Leave a comment

Azure Data Studio last daily post #25

Posted on March 25, 2020 by martincroft

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.

Posted in Azure Data Studio | Leave a comment

Azure Data studio – PowerShell Command Explorer – Day #24

Posted on March 24, 2020 by martincroft

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)

powershellbar

Which results in an extensive list of PowerShell commands that can be ran

powershell list

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)

filter 1

Once typing in click on the filter option it filters the results to ones with just diskspace in the title

filter 2

If you click on the pencil Icon it will paste the current command into the active window ( saves typos!)

diskspace click

Clicking the (?) icon you get the associated help

get help

This is piped to the terminal window to show the syntax of the command

powrshell help

Also a hyperlink to the forementioned dbatools page. Saving you time looking it up!

Posted in Azure Data Studio, PowerShell, Productivity | Leave a comment

Azure Data Studio – A week to go more short cut keys

Posted on March 23, 2020 by martincroft

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

  1. 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

copy line

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

clipboard compare

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

comments

(Just in case you wanted to know what block comments looked like !)

Posted in Azure Data Studio, Productivity | Leave a comment

Azure Data Studio data scripter extension

Posted on March 22, 2020 by martincroft

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

salesorder

 

3) You get a popup box as shown below

select from

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

select date

5) The scripter provides the create table statement commented out

create table
6) And also insert statements, as shown it’s just gives me the insert statements for that specific day
insert
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.
Posted in Azure Data Studio, extensions | Leave a comment

Azure Data Studio – The run book #21

Posted on March 21, 2020 by martincroft

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.

runbook

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

 

 

Posted in Azure Data Studio, Markup, Notebooks, Uncategorized | Leave a comment

Azure Data Studio and working from home day #20

Posted on March 20, 2020 by martincroft

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

C:\Users\yourusername\AppData\Roaming\azuredatastudio\User

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.

Nice 😉

Posted in Azure Data Studio, Configuration | Leave a comment

Azure Data Studio 16.1 Notebook Charts #19

Posted on March 19, 2020 by martincroft

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

notebook charts 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

notebook charts 2 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

Posted in Azure Data Studio, Notebooks, Visualisation | 1 Comment

Azure Data Studio – My SQL snippets freebie #18

Posted on March 18, 2020 by martincroft

I showed how easy it is to create a snippet in a previous blog and said I would share my snippets file. These are the current SQL snippets

sqlblocking – List blocked processes order by head blocking processes

sqlschema – List the schema in the current database

sqlGetIndexStats – Lists the current index statistics, read, writes, seeks, scans, etc.

sqlGetTableSizes – Show the table sizes and row sizes

sqlcolumnListSameLine -Generate a list of columns comma-separated on the same line, in column order

sqlcolumnListOrderbyName Generate list of columns comma separated by name

sqldatabasesizes – list of data and log sizes and combined free space

sqlfindcolumns List columns in all tables whose name is like ‘TableName’

sqljobsteps –  All the jobs enabled, ordered by step_id and also a blank line in for readability!

sqlstartstep – Code to start a job at a specific step

sqltablesizes – list current database sizes and rows

sqlWhoisactive – Adam Mechanic Whoisactive but with a twist ( its a temporary procedure so can use on servers that don’t have it)

If you want to try these yourself rather than link each file you can download my sql.json file.

Browse to your settings folder replacing the your login bit. backup your sql.json and copy in the one from above.

C:\Users\yourlogin\AppData\Roaming\azuredatastudio\User

Go into Azure Data Studio and type in sql…. and one of the selections above, feel free to use to your heart’s content.

I will maintain the settings.json from time to time but if you have any you think would be useful feel free to contact me and I’ll add them in.

Posted in Azure Data Studio, Productivity, Snippets, TSQL, Uncategorized | Leave a comment

Azure Data Studio Snippets #17

Posted on March 17, 2020 by martincroft

I know snippets have been around for years and I have utilised lots of SSMS tools like ssmstoolspack back in the day persuading a company I worked for to buy a copy for the developers.  Of late my SSMS tool of choice is ssmsboost, even buying it my self personally, well my company which is more or less the same, they also have a free community version for people involved in the community and they have helped out with Sponsorship several times for Manchester SQL Saturday and Manchester DataPlatform events.

I never really bought into snippets however till I started using Azure Data Studio and wow, so much productivity.

The first thing is to install the Query Editor Boost extension which has several features but the one this post is about is to easily add in snippets

09 Snippets Nothing better than showing by example in several easy steps. This came from trying to write a query without knowing the schema in the database, I know I’ll create a snippet.

1) Ctrl-N for a new SQL file

2) Paste in the code you want to make a snippet of

SELECT name
FROM sys.schemas
WHERE name NOT IN (
        ‘db_owner’
        , ‘db_accessadmin’
        , ‘db_securityadmin’
        , ‘db_ddladmin’
        , ‘db_backupoperator’
        , ‘db_datareader’
        , ‘db_datawriter’
        , ‘db_denydatareader’
        , ‘db_denydatawriter’
        )
ORDER BY 1
3) Open up the menu Ctrl-Shift+P and type in QE and choose Save New Query Template
13 New snippet
4) Enter a Name for the snippet
14 sqlSchema
5) Enter a prefix – I tend to keep the same name it’s easier
15 sqlSchema
6) Give the snippet a description
16 schema description
Now when you type sql you get a list of snippets and the description
17 snippets in action
hitting return you get the SQL script pasted in.
These are all stored in a text file in a JSON format in the following location, so you can build a library and take it with you.
C:\Users[userName]\AppData\Roaming\azuredatastudio\User\snippets
I am now going through a process of adding in my useful scripts into snippets. It takes literally seconds once you have the SQL.
I’ll add in a post with a link to my snippets file and the code snippets I have.
Posted in Azure Data Studio, Azure Data Studio Extensions, Management Studio, Productivity, Snippets, Uncategorized | Leave a comment

Azure Data Studio – Poor SQL Formatting extension #16

Posted on March 16, 2020 by martincroft

The stock formatting can be improved on with the Poor SQL formatting extension first it needs installing once installed open settings (Ctrl+,) type in poor, hit enter

11 poor formating

Configure as required to your specific configuration, at least change the keywords to UPPER case! You can assign a short cut to format the SQL or use the menu, (Ctrl-Shift+P) and type in poor, or if you have just used it will be at the top of the list.

12 Poor formating menu

Top tip don’t set as short cut key Shift +I as I did, and only realised the issue when capitalising the letter I and wondered why all the text went funny!

It’s so easy to just make your SQL look nice now 😉

Posted in Azure Data Studio, Azure Data Studio Extensions, Configuration | Leave a comment