How Can you Provide Feedback for Azure Data Studio

Azure Data Studio has a number of great features.  When including the extensions, it has even more functionality.  Azure Data Studio allows for the creation of extensions to add or build on to the native functionality of ADS.  The question my come up in your mind, “How do I provide Feedback?”.

Feedback is very important to the Azure Data Studio team.  While I personally do not know any member of the team, if have attended sessions presented by them and they are very open to community members providing feedback.  All in an effort to continuously improvethe tool!  Just another reason why the SQL Server community is a great one to be a part of.

If we look at ADS, there is the application itself and the extensions.  The extensions come from a number of different places.  Companies like Microsoft and RedGate are just a few.  A large number of the extensions are provided by the community. Providing feedback for the extensions can be done a number of different ways.  Most will let you report issues on their GitHub site.  Microsoft has built into ADS a place to provide feedback.  This can be found in the lower right corner of Azure Data Studio.  The smile face!!

If you have the latest version of Azure Data Studio, as of March 10, 2020, the smiley face is now a different icon. See below.

If you do not see the Smiley face, right click on the Status bar and you will see this menu.  Click the “Tweet Feedback” item.

 

Simply click it Smiley Face and you will see the form below.

As you can see there are a few pretty obvious items.  Starting with the faces, smiley and frown.  These can not only be used to help express your feelings about something, it can also dictate the number of charaters you can use.  A smiley face allows for 237 characters, while a frown face allows for 259 characters.  This will allow you to Tweet your thoughts.

However, if you don’t want to use Twitter, you have a few other options. The image below shows the two options, “Submit a bug” or “Request a missing feature”.

These will take you to different places.  Starting with “Request a missing feature”.  You will be taken to the GitHub location for Azure Data Studio.  You can then complete the following form.

If this is your first time submitting an item, you will see a few links to the right of the above form.  This is something you should read.  This gives you the guidelines for submitting an issue as well the Code of Conduct.

It is important to read both of these so you know what the rules and expectations are.

When you click “Submit a Bug” a new form will open up.  It will look like the image below.

With the first drop down box you will see three options.

      • Bug Report
      • Feature Request
      • Performance Issue

While the next drop down box, Select Source has a few options as well.  These options include the following:

      • Azure Data Studio
      • An Extension
      • I dont know

Sometimes you simply may not know what the source is.  This is where the “I don’t know” option comes into play.  When you chose ‘An Extension” an additional drop down box will appear.  This new box will list out all the extensions you have installed.

Then give the issue a title and any steps needed to reproduce the issue.  It is important to provide as much details as possible so the development team will have enough information to address the issue at hand.

Now that you have entered all the important information you are ready to click: Preview in GitHub.  When you do this, ADS gives you a pretty good description of what the issue is and how we need to proceed.

 

When you click OK, you will be asked to login to GitHub.  It is here you will need to paste what is on your clipboard or enter new comments.

Now you are ready to submit.  Hopefully your suggestion will now be used to improve the Azure Data Studio end user experience.

Thanks for visiting my blog!!

 

 

ADS: TSQL Checker

Azure Data Studio has quite a few extensions that are very useful.  Over the past six to eight months I have completed a number of posts on quite a few of these extensions.  So far one of my favorite extension is the PoorSQL Formatter.  It provides great assistance in the formatting of Transaction SQL code. What it doesn’t do, is help identify code that is not in line with best practices.  This is where the TSQL Checker comes into play.

This extension was developed by Daniel Janick. Daniel is a Microsoft MVP from the Austin, TX area.  He can be found at @SQLSME on Twitter.

You might be wondering exactly what does this extension check.  According to the extension documentation this is what it checks:

“TSQL checks for hints, select * from options”

Of course “SELECT *” is a very well known problem in query design.  Hints and options can also create significant performance issues as well.   What exactly is a hint?  According to Microsoft’s documentation a hint is something that will over ride the execution plan that the query optimizer might select for a query. Here is a link to Microsoft’s documentation on hints.  Hints will act on the four DML statements, SELECT, INSERT, UPDATE and DELETE.  Hints should only be used as a last resort and only by experienced developers or DBAs.

While this extension is still in preview mode, it can still be very useful.  To install it you will click the “Install” button, green button below.  This will appear when you are looking at the documentation of the extension.  When you do, you will be asked to open the GitHub sight for this extension.

When you click open, you will be taken to the GitHub site for this extension.  You will want to download the tsqlchecker-0.0.1.vsix file.  It is highlighted below.  When saving it, save it in a location where it will not be overwritten.

To install this extension, simply go to the File menu and then go to “Install extension from Vsix file”.  While some extensions require a restart of Azure Data Studio, this extension does not.

The purpose of this extension is to help you identify code that is not inline with best practices.  In order to do so, the extension needs to mark the offending code in some manner.  The extension will do this one of three ways.

    1.  Highlighting the code with a box:
    2.  Highlighting the code in yellow:
    3. Highlighting the code in red:

Let’s take a look at some of the code that the TSQL Checker will flag.

Note: This code was take from the Microsoft documentation.  This really isn’t a post on what these things do, just how TSQL Checker flags them.  Refer to the Microsoft documentation for more information on these.

SELECT *

MAXDOP

FORCESEEK

FORCESCAN

LOOP JOIN

MERGE JOIN

OPTIMIZE FOR

HASH JOIN

For all of these warnings, the developer has also added a popup that will provide additional information.  When you float the cursor over the warning you will see somethings similar as you is in the image below.

I really like this extension.  It reminds us of code that is less than ideal and should be reviewed.  Remember, few if any of these hints are recommended for production use.  If you run into a situation that you think these are part of the solution, just do your homework first.  Research and see if there is a better way to address the problem.

Thanks for visiting my blog!!!

 

ADS: DB Snapshot Creator

Like many Azure Data Studio extensions, DB Snapshot Creator is designed to bring functionality into ADS that is not present by default.  This extension was developed by Sean Price. As the name suggests, this extension can be used to easily create database snapshots.  Before going too deep into this extension, let’s take a quick moment to go over what a snapshot is.

First of all don’t think of it as a backup.  Think of it as a read-only copy of the database.  As pages are modified for the first time in the source database, SQL Server will move the original unmodified pages to the snapshot.  This process will allow users to see that snapshot that is a mirror of the database when the snapshot was created. You might be asking, why should I use a snapshot?  There are a number of reasons, but the biggest in my mind is the ability to use the snapshot for reporting purposes.

What if an 8-K page is modified twice?  SQL Server will only copy the page to the snapshot the first time it is modified.  After that, the page will not be copied again.

In Microsoft’s documentation, found here, right at the beginning of the document MS makes one very important point.

“Database snapshots are unrelated to snapshot backups, snapshot isolation of transactions, or snapshot replication.”

To find if there are any snapshots you can use the code below.  This code will not only tell you if snapshots exist, it will also return what database it is associated with and when it was created.

SELECT    a.name AS ‘Snapshot Name’
                , a.create_date AS ‘Snapshot Create Date’
                , b.name AS ‘Source Database’
                , b.create_date AS ‘Database Create Date’
FROM sys.databases a
     INNER JOIN sys.databases b
ON a.source_database_id = b.database_id

This is what the results will look like.

Microsoft has some good documentation on Snapshots.  Go here to read it.

How to Create the Snapshot in SSMS

This is pretty easy to do.  Many times if you want to do something with a database you can right click on the database and easily perform the desired task. This could be completing a backup, shrink the database and restore the database.  One task that is not present is Create Snapshot.  Given this, you will need to use TSQL to create the snapshot.  Note the file extension, it is not MDF or NDF.

CREATE DATABASE [AdventureWorks2014_Snapshot_02_27_2020_01] ON
(NAME = [AdventureWorks2014_Data],
FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014_Snapshot_02_27_2020_01.ss’)
AS SNAPSHOT OF [AdventureWorks2014];
Below is a screenshot of the files in the file system.  As you can see, the file for the snapshot has a different extension and there is not a corresponding .ldf file.

How to Create the Snapshot in Azure Data Studio

The option to use TSQL is always present, even in Azure Data Studio.  And just as in SSMS, when you right click on the database, Create Snapshot is not an option.  This is where the DB Snapshot Creator extension can help.  Something to keep in mind as we go over this extension, it is still in preview mode.  Neither the documentation in Azure Data Studio nor the GitHub site have a great deal of documentation on this.  However, there is an FAQ that answers three questions.  These questions are below and were taken directly from the extension documentation.

To install the extension you will first need to download the .vsix file.  When viewing the extension the in the Extension Marketplace, you can click the green Install button. This will take you to the Github where you can download the file.

Make sure the place this file in a location where it will not be deleted.  Once downloaded, you can go to the File menu and to “Install Extension from VSIX package”.

Then simply find the file and it will install.  Now when you right click on a database you will see a new menu item to create the snapshot.

By default, when you click the above menu item Azure Data Studio will write the code to create the extension, but not actually create it.  The CREATE DATABASE statement earlier in this post was created by this extension.  This code is also below.

CREATE DATABASE [AdventureWorks2014_Snapshot_02_27_2020_01] ON
(NAME = [AdventureWorks2014_Data],
FILENAME ‘C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014_Snapshot_02_27_2020_01.ss’)
AS SNAPSHOT OF [AdventureWorks2014];

However, if you would like to skip the step of having the code written, you can go to preferences and uncheck the box below.

While in SSMS, the snapshots are located in a folder as seen below.

In Azure Data Studio, the snapshots are located at the same level as the rest of the databases.

 

This extension makes the creation of a database snapshot very easy!  And like all other extensions, it has a great price. FREE!!

Thanks for visiting my blog!!

 

ADS: New Database

Regardless of what tool we are using, SQL Server Management Studio or Azure Data Studio, the need to create new databases is always present. Using Transact SQL is an option in both tools.  What is not an option in both tools is to right click and to go “New Database”.  This has been in SSMS for many years, however it is NOT present in Azure Data Studio.

Below you can see what the menu options are when you right click on databases in SSMS.  The New Database option is right at the top.

 

Now lets move on to Azure Data Studio.  When you right click on a server you will have a number of options, including new query, refresh and disconnect.  What you do not see is “Delete Database”.

As with a great deal of functionality in Azure Data Studio, the ability to right click on a server and create a new database comes with the installation of an extension.  In this case the extension was created by Keven Cunnane, It is pretty simple to install and cost the same as ADS, nothing.  This extension is currently not in “Preview” mode.  It is currently at version 1.0.0.

To install you will need to click the install button when looking at the extension documentation in Azure Data Studio.

Once you do, you will be taken to the location to download the .VSIX file.  You will need to download it and save to a location where it won’t be deleted or overwritten. To install, simply click on “Install Extension from .VSIX package” on the File menu.

Once you do you will see in the lower right corner that the extension has been installed.  A restart of Azure Data Studio is not required.  Now when you right click server, what do you see?  Well..you will see that nothing changed.  You will need to right click on Databases under the connection.

Once you click “New Database” you will not see the same dialog box you will see in SSMS.  What you will see is something like what is in the image below.  In the box you will enter the name of the new database.

Then hit enter and assuming you have the proper rights, ADS will create the database.  You will not be able to enter any of the settings we often change to accommodate the specific needs of the database or system.  This includes things like file location, recovery model, file growth rate and many more. With this, you will either need to use TSQL to adjust some of the settings or use SSMS.  This method of creating a database will depend on the defaults for all the settings.

Overall this is a good extension although it depends heavily on default database settings.  I still prefer to use TSQL when creating a database, however this is a nice simple method to use as well.

Here is a list of a number of available extensions, click here.  This list does not include all the extensions that are available but is still a nice list.

Thanks for visiting my blog!!

ADS: Demo Mode Extension

Through out the year I present a lot of sessions on SQL Server and related technologies. These take place at user groups(9 in 2019), SQL Saturdays(14 in 2019), training sessions at work and teaching the SQL Server certification courses from Microsoft.  With these many different presentations there are differences in the screen sizes and room sizes.

When presenting, setting the demos up in a way that will allow all attendees to easily see the code can be a bit of a challenge sometimes. To accomplish this presenters have several tools available to them.

Tools commonly used

      • ZoomIt – This is a free third party tool that I have been using for a few years
      • Windows 10 Zoom feature
      • Increase the font of the code window in SQL Server Management Studio or Azure Data Studio
      • Use the Demo Mode extension in Azure Data Studio
      • A number of other tools

While I would love to list the pros and cons of each, that really isn’t the focus of this blog post.  Recently an extension for Azure Data Studio was released by Drew Skwiers-Koballa that I think is a great addition to the list of available tools.

The first thing to do is to install the extension.  To do this, you will need to download the .vsix file.  In order to find the extension, you can type the word “Demo” in the extension search box.  Taking a small step back, if you are not sure how to get to the extensions, you can open the extensions by using the menu item under the View menu.

To download the file you will need to click the install button in Azure Data Studio when looking at the documentation of the extension.

When you download the file, place it in a location that will be secure and the file will not be overwritten.  Once saved, to install it, go to the “Install Extension .VSIX package” menu item under the file menu.  You will need to restart Azure Data Studio when the install is complete.  Please remember, this is extension is still marked as being a Preview.  Given that, things could change with the next release.

Now that it is installed, let’s go over what it does.  This part is pretty simple, when enabled, the font in the query window will be come larger and when disabled the font will become the original size.

The two images below show the font with Demo Mode enabled and disabled.  The first image is disabled, while the second has Demo Mode enabled. Notice that the only thing that changed is the font size of the query.

When Demo Mode is enabled the only thing on the screen that will change is the font size of the query.  Everything else will remain the same size.

To enable\disable the extension you will need to do one of two things.  First, you can use the Command Pallett.  Type “Demo Mode” into the search box.  Two options will be returned, Enable and Disable Demo Mode.  This can be seen in the image below.

The second option is to click the Enable\Disable Demo Mode button that is located in the lower left corner of Azure Data Studio.  I personally like this option because it is very easy to use.  By default neither of these options has a keyboard short cut assigned to it.  However, under File–>Preferences–>Keyboard Shortcuts, you can add the short cut key or key combination that will work for you.

You may be asking yourself where does the extension get the font sizes to use.  When the Demo Mode extension is installed, three settings are added.  Two of these settings identify the font size to use when the extension is enabled or disabled.  See the image below.

The Demomode: Demo Font Size setting is what font size ADS will use when the Demo Mode extension is enabled.  As expected, you would want this to be bigger than the Original Font Size, which is just below it.  This represents the font size with the extension is disabled.

The third option will allow Azure Data Studio to send confidential about the usage of the extension.  As you can see with the presence of a checkbox, this is something you can option out of if you like.  The documentation can be found here. This is what the documentation actually states:

“enables anonymous usage data to be sent to an online service”

The documentation also goes on to state the following:

“The extension telemetry sends usage data anonymously to an Application Insights instance. Machine information, such as operating system or general location, may be shared with the general public in aggregate. Your information will not be sold to any third parties. For more on the collection of telemetry in this extension, read more here or ask questions here”

Please read through these very carefully to determine of this is something you would like to opt out of.

Under the hood, when this extension is enabled, it changes the Editor: Font Size to be the same as the Demo: Font Size setting for the extension.  If you close Azure Data Studio while the extension is enabled, the font setting will persist.

What happens if there are more than one query window open?  Well, the extension will apply the font setting to all query windows that are open.  I also noticed that if the extension is enabled and you close settings, the current font size will also persist.

Another question that comes to mind is will this setting only apply to queries?  The answer is no. This extension will also have an impact on the font in Notebooks.  If you look at the image below you will see what the text will look like with the extension enabled and disabled.  Notice the difference in size of the box the text will be entered into.  Of course this assumes that the two font settings for the extension are different sizes.  In a notebook, this font change only applies to when text is being entered, it will not have an impact on the look of the text that will be displayed.  I checked the Terminal that will be used for PowerShell and the use of the extension does not have any effect on the size of the font.

If you open Settings and you only get json, earlier I completed a blog post on how to open the Settings in json rather than the UI.  Here is the link to that blog post.  In the settings.json file, the Demo Mode extension will look like below.

I like this new extension and plan on utilizing it for all my sessions that use Azure Data Studio.  For additional information you can go to the documentation. I would like to point out that while this extension has a target audience of people who do presentations, it also has another audience.  Sometimes, I just need the font to be slightly bigger for a temporary reason, like I am tired and the eyes just can’t focus as well as normal.  I can use this extension to make that much easier.

For more information about  Drew, the developer of this extension, you can go to his blog, https://www.drewsk.tech/.  Also he has a nice presentation on developing extensions.  If you get a chance to hear this at your local user group or SQL Saturday, make sure you go, it is a very nice session!

Thanks for visiting my blog!!!

 

ADS: Settings Editor

When working with the Settings in Azure Data Studio, you have a few options on how you will see them.  The two options available at this point are the “user interface” and “JSON”.  While it is usually easier to modify the settings using the UI, you might prefer to use the json instead.  I feel that anyone that uses Azure Data Studio on a regular basis should learn the basics of JSON.

These are my reasons why I think we should learn the basics of JSON.

      • Some settings must be changed in JSON
      • One of the export options for a result set is JSON

The first reason is where the focus of this post is.  Many settings can easily be modified using the user interface, however not all can.

The settings can be found under the File menu or you can use the keyboard shortcut of “Ctrl + ,”.  Under the File menu is you will find Preferences.  It is in the pop out menu that the Setting menu item can be found.

Once in settings you will quickly see that there are many settings.  Most of these you will not have a need to change, but it is good to know what can be changed. Once in the Settings, the user interface is quite easy to navigate around. You can even search for specific key words on the top.

As you scroll through the various settings you might notice that not all the options can be changed in the UI.  Some must be changed using the JSON file.

The settings.json file can be found in this location.

C:\Users\<<UserProfileName>>\AppData\Roaming\azuredatastudio\User

Notice that “azuredatastudio” does not have any spaces.  When you navigate to this location “C:\Users\<<UserProfileName>>\AppData\Roaming” you may see two folders, both named Azure Data Studio.  One will have spaces, the other will not.  The settings.json file is found in the folder without spaces in the name.

 

Now that we know the location of the settings.json file, let’s return to the editor.  As mentioned earlier, the settings can be changed in two places, the editor UI and the JSON file.

This is what you will see if the setting can be modified in the UI.  Depending on the setting, you may see a dropdown box, a text box or a check box.

If you see something like what is in the green box below, the setting must be modified in using the settings.json file.

When using the UI, you have both options.  However, there is an setting that will allow you to use only the settings.json file.  This setting can be found in the Workbench group.

The dropdown box will have two options, ui and json.  When set to json, the settings.json file will open when you attempt to look at the settings.  Keep in mind, when you change this to json, you will not get a warning and it will be saved automatically by default.

With it set to json, as expected the settings.json file will open instead of the user interface.

You might be asking, how can I change it back?  This is quite simple.  Look for the “workbench.settings.editor” setting and change it back to “ui”.  Remember there are only two option, “ui” and “json”.  Make sure to keep the quotes.  As you can see in the above image, it is on line 209 on my instance, although will probably be different for your instance of ADS.

When in the json file, if you float your mouse pointer over one of the settings a pop up will appear that may provide a bit of information about the setting.  Something like what is in the red box below.

While I prefer to use the ui, it is nice to have this option if I wanted to change.

If you are not using Azure Data Studio on a regular basis, my suggestion is to take a look at it.  The price is right, it is free and has many great features.

Thanks for visiting my blog!!

 

 

ADS: Number on the Explorer Icon

When you open Azure Data Studio you will see a tool bar on the left side of the screen.  On this bar you will see a number of icons.  The image below is what you might see.  From time to time you might see a number on the lower right of the icon.

In the example, there are two icons that have number over them.  The bottom one is related to extensions and it is telling you there are updates to extensions you have installed.  In this case there are two.

The top number is the focus of this post.  The icon is for the Explorer. When you click on the icon you will see a screen pop up. At the top of this screen you will see “Open Editors”. Similar to below.

These are files that you have opened, made a change to and have not saved.  As you open files and make changes, this number will increment.  It will also increment if you open a new query window and make a change to it.

Although Azure Data Studio can persist your unsaved documents, it is still a best practice to save your work as you go.

Thanks for visiting my blog!!!

 

ADS: Simple Data Scripter

Moving data in a table from a production server to a lower environment such as a development server can be done a number of different ways.  These include backup\restore, using SSIS to move the data, import\export wizard and scripting the data in SQL Server Management Studio.  Azure Data Studio by itself doesn’t have a way to script the data in a table and it does not have the import\export wizard.  This is where the Table Data Scripter extension comes in handy.

Like most extensions for Azure Data Studio, this extension is also in a preview state.  As of the date of this post, it is currently at version 0.1.1 and is published by Sean Price. The documentation for this extension can be found here, Simple Data Scripter. In the documentation there are a few limitations identified

      • Not all data types are supported, specifically the binary datatypes like text, binary and varbinary
      • Has only been tested with SQL Server
      • Does not work on a view

Some extensions will add options under Preferences.  This extension does not add any options.  Given that, there is limited ability to configure this, which is OK, not really much of a need to do any configuration.

Just as a review, let’s take a look at how we can script the data in SQL Server Management Studio.  After right clicking on the database and going to Tasks…Generate Scripts…  When you do, a wizard will pop up and then after a few clicks on next, you will arrive at the screen below.  The box highlights the “Types of data to script” option.  With this there are three options, Data Only, Schema and Data and finally Schema Only.

When right clicking on a database in Azure Data Studio you will see limited options none of which are related to scripting data.  This is also true when right clicking on a table in the database.  Here are the options we see by right clicking on a table, as before, none of which are related to scripting data.

In order to script the data in Azure Data Studio we will need to install the Simple Data Scripter extension.  This is a pretty straight forward and simple install.  You will need to download the .vsix file.  You can get to the GitHub to download the file from the documentation for the extension in ADS.  You can install it under the File menu.  While you will not be prompted to restart ADS when you install it, I usually like to restart ADS anyway.  In this case, the extension doesn’t move to the installed extensions until after a restart.

Now that the extension is installed, you will see a new option when you right click on a table.  This is another potential limitation of the extension, there isn’t an option to script all the data in the database.  Well, at least I haven’t found it yet.  But then again, I am not sure having a simple place to script all the data in one place if the best way to go.

When clicked the following screen will appear at the top Azure Data Studio.  Initially when we look at it our first thoughts might be, what a bad query.  But it does make sense, we are creating a script of all the data in the table.

If you click anywhere, ADS will cancel the query and you will see this pop up in the lower right corner and the above screen will disappear.

If you press enter Azure Data Studio will execute the query and generate the script. You will not see a result set but you will see the script.  It will look similar to below.  Notice the Create Table statement at the top, it is commented out.  The table that is used for the example is named RentalCar.  The script is working with a table named #tempRentalCar.

When the script is finished, the Task window will open up at the bottom of the screen.  The top two are successful executions while the third failed, reason for the failure will be covered shortly.

You may be wondering if the query can be modified.  The answer is yes, however not every type of statement will work.  If you want to add a WHERE clause that works just fine.  However, as the query become more complex it failed more frequently.  I attempted to run a query with a join in it and it failed.  That is the failure in the image above.  The error stated “Query has no results to return”.  However, the query works just fine when running in ADS or SQL Server Management Studio.

Here are some of the statements I have tried.

      • Query with a join – failed
      • Query with a sub-query – failed
      • Query with a WHERE clause and just one table – succeeded

When compared to the expectations, this extension works very well and I can definitely see a use for this.

Thanks for visiting my blog!!

 

ADS: Notebook Text Formatting

Azure Data Studio has a great feature in the form of Notebooks.  A notebook is a document that allows for executable code such as TSQL or Python as well as notes for documentation.  Notebooks can be utilized to help organize things such as DR runbooks and troubleshooting documents.  What I like best about notebooks is that everything needed can be located in a single file, including data.

When working in a Notebook you have two types of cells, text and code.  The focus of this post is how to format the text cell.  Of course text goes into this cell so that part is easy and of course the text can say anything you would like to say.  When we work with text in Word, there is a format tool bar that we can use to make it look like we want it.  The text cells do not have this toolbar.

You might be asking, without the format toolbar, does that mean we can’t format the text?  That answer is no….we can still format the text, we just need to do it slightly different.  Rather than use a toolbar, we need to use characters.

While not all formatting options are available in Notebooks, there are a few commonly used options that are.  These are below as well as a few others we won’t cover in this post.

        • Font Size
        • Font Weight
        • Bullet lists
        • Italics

Here is an example of what your text might look like with formatting.  Notice that there are different size fonts, bold fonts, email address, URL and a small section highlight(blue vertical bar).  We will go over each of these in this post.

First of all, in order to get to the location that allows you to format the text, you simply need to find the text you want to format and double click on it.  When you do, the box to format the data will appear just above it.  Similar to below.

The chart below has an example of each of the font options. The sample code is in the parenthesis.

 

Font Changes in a Notebook

Formatting Applied and Sample code Example
No Formatting
One #

(# Font Changes)

Two #

(## Font Changes)

Three #

(### Font Changes)

Four #, adds bold

(#### Font Changes)

Five #, adds bold

(##### Font Changes)

Six # – Makes font tiny and bold

(###### Font Changes)

Seven # – no impact at all, prints the # signs

(####### Font Changes)

Italics

The above chart shows how to change the size and weight of the text.  Well, what if I want the text to be italics.  To do this we will use the *, well actually a pair of them. One will be placed at the start of the text you want to be italics and the other will be placed at the end.

Like this:   *Font Changes*

This is what it will look like in a Notebook

If you want a larger font you can combine characters.

Using both the # and *, we can get a larger font as well as italics.

This is what it would look like:  # *Font Changes*

The image above shows a much larger font and italics.

URL

If you want to include a URL to a web site you can easily do this with out the use of any special characters, such as a # or *.  When you do, the text to the URL appear just as you type it.  For example, if I want to include a link to my blog I could type, www.davebland.com.  When I do, that is exactly how it will appear in the text, www.davebland.com.

However, what if I want different text to appear, let’s say I want “Dave Bland’s blog” to appear in the text rather than www.davebland.com.  This is also pretty easy.  You will need to utilize the square brackets, [] and a pair of parenthesis, ().

The square brackets will hold the text I want to appear, while the URL will be placed between the ().

Like this:

[Dave Bland’s Blog](http://www.davebland.com)
Here is an example of both.

 

Bold

We already talked about how you can use the # to get a bold font.  But what if I want a word in the middle of the text to be bold and nothing around it.  This is where the * comes in.  We just put two * before and two after the text we want bold.

Like this:

# I what these words to be bold: **Font Changes**
This is what it will look like.  Notice the # at the start, remember this makes the font larger.

 

Block of Text

Azure Data Studio also offers the ability to block off sections of text, similar to the text in the green box  below.  Notice the blue vertical bar and the tinted background.

This is done by placing a > at the start of the line.

This is what it will look like:

You can add more > at the front if you like, this will create blocks in a block.

This is what it will look like:

>>### “Those responsible for sacking the people who have just been sacked, have been sacked.” – What movie is this from?

 

And this is the result:

Bullet Lists

The final format item I would like to cover it a bullet list.  Like all the other format options, this is completed by the use of a special character.  In this case it is the plus sign, +.

This is what it will look like:

+ First Bullet
+ Second Bullet
This is the result:

Put it all together

If you use a number of these in one text box, this is what it might look like.

And this is the result:

There you have it….formatting text in Azure Data Studio notebook without the use of a format toolbar.

Thanks for visiting my blog!!!

 

 

 

 

Azure Data Studio – Schema Compare Extension

From time to time we are asked to help identify the differences between two databases.  For example, the differences between a database in QA and the same database in production.  Over the years I have become accustomed to using a very nice tool by Red-Gate, SQL Compare.  With the release of Azure Data Studio and the SQL Server Schema Compare extension, I now have another option.  As with many of the extensions, this one is also in preview.

In order to install the extension, after opening the extensions in Azure Data Studio, you will want search for Schema Compare, similar to the image below.  Notice the white star in the upper left corner, this means that this extension is recommended by Azure Data Studio.

Installation is very easy, once you have found the Schema Compare extension, click the Install button.  This is a pretty quick install and does not require a restart of Azure Data Studio.

Once installed you can launch the Schema Compare extension in one of two ways.  The first way is to right click on one of the databases you wish to compare and go to Schema Compare in the context menu.

The second method is to use the Command Palette.  You can reach this from the View menu.

The difference between the two is that if you use the Command Palette, both the source and target locations will be empty.  If you right click on a database to compare, that database will be the source and the target will be empty.

Using the Command Palette – notice both the source and destination are blank.

Right clicking on a database – the source is already populated with the database you right clicked on.

Setting up the Source and Destination

When setting up the source and destination, you will have a choice to make.  Will you compare a database or will you use a DACPAC to complete the compare?  You don’t have to just compare two databases, you can compare a database and a DACPAC if you have a need to do so. When you click the ellipsis to the right of either the source or destination, you will be taken to the location that will let you set up the comparison.

What I like is that I don’t have to do a database to database compare or a DACPAC to DACPAC compare.  Schema Compare allows for a database to DACPAC compare as well.  Once set up your screen will something like what you see below.

Since I am comparing two DACPACs, there is a path, however if either the source or target is a database, it will look similar to below.  The format is ServerName.databasename.  In this case since I am using a named instance it is slightly different, ServerName\InstanceName.DatabaseName.

Menu Options

Once you have set up the source and target, the menu bar will appear across the top.

Let’s take a moment to go over each of these.

Compare and Stop – these are pretty simple and are used to start the compare or stop it.

Generate Script – Once the compare is done, we can use this to generate the script we can use to make the changes.  Notice in the image above this is not active. It will become active after you have completed the compare AND you are comparing two databases.  You will not be able to use this option when comparing two DACPACs.

Apply – Will apply the discovered changes to the target

Options – There are a number of options that include rules to follow, like “Block on Possible Data Loss” and what to include in the compare.  “Ignore Permissions” is an example.

Switch Direction – This will take the source and make it the target…and of course take the target and make it the source.  When you click this, the arrow between the two will stay pointing to the right, the items to compare will move.

Open .scmp\Save .scmp – Schema Compare allows you to save the configuration to run the compare again at a later date if you like.  If you save it after the compare is completed, this will NOT save the results of that compare.  These files are XML based and will look similar to below.  The format is pretty simple, the XML will contain the version, source, target and the current state of all the options.

 

Changing Target or Source

Once the target and source are set up, you can easily change one or both of them.  Simply click the ellipsis again, make the changes you want and click “OK”.

When you do make a change and click “OK” the following will appear in the lower right corner of Azure Data Studio.   It is just asking if you want to complete the compare now.  If you click no, you will still be able to complete the compare by clicking “Compare” when you are ready.

Generate Script

This does exactly as you would expect it to. It generates the script.  Once the compare is complete, as in the image below, the Generate Script option becomes active.  Just a reminder, if both the source and the target are DACPACs, generating the script is not an option.

Prior to generating the script, you will want to review what was found, specifically the Include and Action columns to make sure those are indeed the changes you would like to make.  If there is a difference you do not want to be part of the script make sure to clear the check box in the include column.  Once Azure Data Studio is completed with the generation of the script a new table will open up.  This tab will not be connected to a data source.  If you want to run it, you will need to connect to the desired data source.

Notice in the bottom left corner of the above image you see “Problems” with the number 7 to the right of it.  Initially you might think that Azure Data Studio created bad code, that is not the case.  When you click on “Problems” you will see a list of the problems as well as the line number and the location of the first character of the problem.  If you click on one of the problems, you will be taken to that location.

You still might be thinking ADS created bad code.  Now I will show that the code is just fine.  If you look at the code and scroll down just a bit you will see this.

“Detect SQLCMD mode and disable script execution if SQLCMD mode is 
not supported.

 

The code that is generated requires that SQLCMD be active. Once SQLCMD is active, the problems disappear.

Now you are able to run the script when you are ready.  Of course, the script can be saved as a .sql file if you want to run it later.

I think this is a very useful warning that can be found near the top of the generated script.

“This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.”
Just something to think about before making any changes.

Options

The final item I would like to cover is the Options button.  Of course this is exactly what you might think it is, a location to set options.

When you do click this, a panel will open up on the right side of Azure Data Studio.  At the top of this panel you will see two options.

The first options is “General Options”.  This is where you can identify what you actually want to capture in the compare. While the second option will identify the object types you want to be looked at, for example tables, views or stored procedures.

Under the General Options there is one in particular I would like to point out, Verify Deployment. Having this checked, the deployment will not be allowed to proceed if the validation detects a problem.  The image below is the actual description you will see.

As time goes on we will use this extension more and more, we will then have a better understanding of some of the additional options we might want to include.  You should really check this extension out!

Thanks for visiting my blog!!