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 elipsis 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 procedue 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!!

ADS – Did I Save My Changes?

After working on a query for a long time, we want to make sure that we save the changes we have made.  I have lost hours of work over the years because I didn’t save the changes.  Azure Data Studio has a few features that can help prevent this from happening.

The first feature is that ADS can “save” your changes if ADS is restarted.  This can be turned off in Preferences.  I completed a blog post on this feature.  Here is the link.  This feature allows Azure Data Studio to remember what changes you made and will open the file when you restart ADS.

In SQL Server Management Studio an asterisk is placed on the tab the code is on.  Once you save the file, the asterisk will disappear.

Azure Data Studio is similar but instead of an asterisk, ADS uses a dot.

Once the file has been saved, the dot changes to an X.

What is interesting is that if the file has not been saved when ADS is closed, the unsaved state will be retained.  The next time you open ADS, the file will be opened, the changes will still be there and the file will be marked as not saved.

This is a short and simple blog post.  Thanks for visiting my blog!!!

ADS – Outdated Extensions

As you add more and more extensions to Azure Data Studio,  keeping them up to date could present a challenge.  Of course we will want to install all the extensions that we think might be helpful to us.  Luckily Azure Data Studio has a nice way to let us know if there is an extension that is out of date.

If you look at the extensions in the left vertical bar you will see a number over the extensions, the blue circle. That number represents the number of extensions that have updates available. In the image above, you can see that there is one extension that is out of date.

Now we need to find that particular extension.  You can do this by scrolling through you active extensions and look for the green box as in the above image.  This will take you to the proper location for the update.  In the case above, I needed to update the Redgate SQL Search extension.

This is an update to the VISX file.  Once you download it, you will then need to click on the “Install extension VISX package” item under the File menu.  Prior to installing the new version, I would suggest that you review exactly what the new version brings.

As with the initial install of the extension, it is possible that you may need to restart Azure Data Studio to complete the installation.  In this case, there was a nice reminder to do so.

Thanks for visiting my blog!!!

ADS – Query History Extension

I just downloaded the latest release of Azure Data Studio.  A new extension came with it that I really like, Query History.  According to the documentation, the Query History extension does this: “Adds a Query History panel for viewing and running past executed queries”.

This installation of this extension is pretty simple and does not require a restart of Azure Data Studio.  However, if you disable or uninstall the extension, you will need to restart ADS.

If you look at the View menu, you will notice that there is not a menu item for “Query History”.

This will change once the extension is installed.  You will find the Query History item just under Output.

Once you install the extension, you will now begin to see query history.  If you run a query, you will see the history as seen below.  If you to not see this at the bottom of the screen, go to the menu item mentioned above.

When you run a query, it takes a very short period of time for it to appear in the history.  In the above image,  I ran three queries, two of which were the same query.  This window is not part of the query window.  Since it is a separate window, it will collect queries from all tabs that are open.  Each row in the history will return three things, the query, the server and database uses as well as the times stamp of when the query was ran.  The server can be seen in the green box below.  Sometimes a forth item is included in the list of items returned, the icon that is at the far left of each row.

Now that we have history, let’s take a moment to see what we can do.  First of all, if you float your cursor over one of the queries, you will see a pop up.  This pop up will display the query.

You will see a lot more options if you right click on one of the queries.

All the options are pretty straight forward and really don’t need a great deal of explanation.  The bottom option, Pause Query History Capture I can see being useful if you are running query that you don’t want to be part of the history or just running too many queries at the moment.

Since these options are pretty straight forward, let’s take a look at a few questions.

What happens to the history when Azure Data Studio is closed?

The history is no longer available

What if I execute a batch, will each query in the batch be a separate line?

All the queries for the batch will be on the same line. The query in the green box is actually two select statements.

To the left of the query, I see a green check mark.  What are other icons I might see? 

There is currently only one other icon you might see. The red X when there is an error.

When there is a error, you will see a small number next to the “Problem” tab.  When you clock to this tab, you can now see details on what the error was.

What’s the difference between “Run Query” and “Open Query”?

Open query will do just that. Open the query so you can review the TSQL.  Run query will simply run the query and you will see any results if there are any.

Over all I really like this extension and can really see the practical use of it.  Just remember, this extension does not stored the queries beyond the Azure Data Studio session.

Thanks for visiting my blog!!!

 

 

 

 

Azure Data Studio – Process Explorer

Looking at processes in Windows Task Manager is something that most DBAs have done many times.  The need to do this hasn’t changed, however what has changed is how we can get information on the processes.

Prior to Azure Data Studio we would go to Task Manager in Windows.  Task Manager will look something like this.  You can see the name of the process, user name, PID number as well as CPU and memory utilization.

Azure Data Studio offers the ability to view these same processes, however the processes returned focus on Azure Data Studio related processes.

To open Process Explorer, go to the Help menu item and it is in that menu.  Just as in the image below.

Once open you will see familiar columns.  These columns include, CPU%, Memory(MB), pid and Name.  These are all Azure Data Studio related processes.

When you right click on one of the processes, you will see a somewhat familiar pop up menu.  The menu item will allow us to terminate the process, just as we have done many times in Task Manager.

This pop up menu is significantly smaller.  The task manager pop up has a ton more options including change priority and affinity. Below is what you will see in the pop up menu in Task Manager.

Looking at the image below, the left side is Azure Data Studio and the right is Windows Task Manager.  The important thing to note here is that in order to map the process to a process in Task Manager, the match will happen on the Pid column, as seen below.

Notice that the pids all point to the azuredatestudio.exe processes.  Azure Data Studio provides just a bit more information than Task Manager.  Please be careful changing the state of a service.  In other words, be careful stopping a process unless it is a last restore approach to fixing an issue.

In Task Manager the sort order for a column can easily be change.  You will need to put in just a bit of effort into determining what the proper sort order is.  When looking at the data in Process Explorer, the data cannot be sorted in any way.  It looks like it is sorted by CPU in descending order.

This is a pretty short blog, but I think it is a good idea to know thqt Process Explorer exists.  I really like that I can stay in the same tool and review related processes.

Thanks for visiting my blog!!!

 

ADS: Why doesn’t the app ask to save anything when I close it?

Azure Data Studio has many great features and even more if you add all the extensions that are available.  Many extensions are very useful now, even though they are still in preview.  These features are naturally compared to SQL Server Management Studio.  One feature I like that sort of exists I SSMS is the auto save feature.  This feature will automatically save your files when you close Azure Data Studio and will be there the next time you use ADS.  SSMS has the auto recovery option, but is works a bit differently so it isn’t quite the same. ADS has a setting named “Files: Hot Exit”.

If you open Settings and then do a search on “save”, you will see a few settings returned.  The one we are looking for is under “Text Editor” then “Files”.  When you click files you will see the setting at the bottom.

When you click the drop down box you will see three options, Off, onExit and onExitandWindowClose.  What I like about it is that as you float your cursor over each option, a description appears below it. As you can see below, onExit is the default option.

According to the documentation from Microsoft, these are the descriptions of the three options, these are directly from Microsoft here.

      • "off": Disable hot exit.
      • "onExit": Hot exit will be triggered when the application is closed, that is when the last window is closed on Windows/Linux or when the workbench.action.quit command is triggered (from the Command Palette, keyboard shortcut or menu). All windows with backups will be restored upon next launch.
      • "onExitAndWindowClose": Hot exit will be triggered when the application is closed, that is when the last window is closed on Windows/Linux or when the workbench.action.quit command is triggered (from the Command Palette, keyboard shortcut or menu), and also for any window with a folder opened regardless of whether it is the last window. All windows without folders opened will be restored upon next launch. To restore folder windows as they were before shutdown, set window.restoreWindows to all.

While this feature may give the perception that the changes were saved, in reality the file on disk has not changed at all.  The image below shows a change to the word “Total(s)”.  The top Totals is the word in Azure Data Studio after a change and after a close of the ADS.  The bottom one is from the file that was opened in SQL Server Management Studio, notice there is an “s”.  This shows that despite ADS persisting the change when ADS is restarted, but actual file was not changed.

I really like this option.  Not only will it “save” the files, but it will also save any text you have selected.  If you have a block a text selected and Azure Data Studio is closed, your text will also be selected when you restart Azure Data Studio.

This will not only save your files, but it will also save a number of other items that you may have open.

If you have the Settings open when ADS is closed, it will also be open when reopen ADS.  Although you may not be in the same place in Settings when you return.  If you have the connections window open, this will also be open when reopening ADS.  Like Settings, you may not be in the same place in Connections when I reopens.

Although there might be some Settings you might want to change, for me personally I don’t think this one should be on the list of things to change.

Thanks for visiting my blog!!!

 

ADS – AutoClose Database Setting

Recently when working with Azure Data Studio, I noticed something I hadn’t seen before. I noticed to the right of the database name the words “Auto Closed”, as in the image below.

Let’s take a quick second to go over exactly what Auto Close is and what it means.  Auto Close is a database level setting that can be set to either On or Off.  When this is set to On, SQL Server behaves slightly different.  SQL Server will open and close the database for each connection.  While this creates additional, usually unneeded overhead, this isn’t the only concern.  When Auto Close is set to ON, it will free up all the resources it is currently consuming, including the plans in the plan cache.

To turn this on or off, you will need to go to the settings of the database.

Having this is to ON, can cause a performance issues and more entries in the log.  Below is an example of what entries you may see in your log.

In the below image you can see the database that has been flagged as having been closed automatically.

This is something new in Azure Data Studio.  SQL Server Management studio does not display this, as shown in the image below.

Once you query a table, the “Auto Closed” message will go away.  The reason it will go away is because the database is no longer closed.

Now that we have covered what Auto Close is and how it will be displayed in Azure Data Studio, you might be wondering whether or not it should be turned ON.  The answer is NO…you should not turn this on.  I think Pinal Dave summed it up the best in the screenshot of something from one of his blog posts, the screenshot is below.  This is the link to this page.

In addition to what Pinal says, there many others that also recommend that this setting be turned OFF.  This post really wasn’t about the inner workings of the Auto Close setting, but I felt it was important to give a brief overview of what it is.  Read some of the other posts and make up your own mind, but keep in mind it is considered a best practice to leave this off.

Thanks for visiting my blog!!

 

 

 

ADS – Server Groups

Azure Data Studio has a ton of great features. Of course some I like more than others, but Server Groups is something I really like.  The team I am currently part of manages almost 200 production SQL Servers.  However we do find ourselves working on Dev and QA servers as well, greatly increasing the numbers of servers where could potentially work with.  This greatly increases the likelihood of making a mistake and running the code on the wrong server.   However, even with just a few servers, it would be nice to be able to group the connections to make it a bit easier.  Server Groups are not new, it has been in SQL Server Management Studio for quite some time.

Create a New Server Group

Creating a new server group is pretty easy.  When in the servers window, float your cursor in the upper right corner and three buttons will appear.  These buttons are, from left to right, New Connection, New Server Group and Show Active Connections.  The middle one is the one we want.

Once you do, a very simple window will open up.  This window may appear on the right side of your screen, which is by default where it will appear.  To create a group you only need three bits of information, the group name, group description and color.

Once you click “OK”, you will now see your server group.  You will see the description when you float your cursor over the group.

Now that we have our server group, it is time to add a connection to that group.  This is pretty easy as well.  When creating a new connection, one of the properties you will be asked to fill out, although not required, is the Server Group.

What if it is an existing connection I want to move to the new group?  This is even easier.  When in the connections window, simply drag the connection to the group header that you want to move it to.

Add a Server Connection to a Group

Now that we have the group, we can begin to add server connections to the group.  This can be done simply by right clicking on the group and then clicking, New Connection.  This will open the New Connection window with the server group already filled in.

If the connection already exists and would like to add it to the new group.  All you need to do is click and drag the connection to the header of the group.

Changing settings of an Existing Group

If you attempt to right click on a group, you will get three options.

      • New Connection
      • Edit Server group
      • Delete group

As mentioned earlier, the New connection will take you to the new connection window with the server group already filled in, although you can still change it if you like.

Edit Server group will take you to the same window that you used to create the group.  This allows you to change the Name, Description and color.

However, what if the setting I want to change isn’t in the list.  For this you will need to go to Settings.  This can be found under the File menu.

File —> Preferences —> Settings

When you are there, type group into the search box and a number of settings will appear. The ones we are looking for are under Data.  As highlighted below.

Notice there are four settings.  Of which, only two can be modified here.

Server Group: Auto Expand – will expand the server groups automatically

SQL: Tab Color Mode – This will change the color of the tab to the color of the server group it is in.  I complete another blog post on the details of how this works and how to change it.  The post can be found here.

That leaves Server Group: Colors and Data Source: Connection Groups.  These will need to be modified in a much different way.  These will involve editing the settings.json file.

The settings.json file can be found here:

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

According to Microsoft’s web site the file can be found in this location:

%APPDATA%\azuredatastudio\User\settings.json

Although knowing the location is good, we really don’t need it.  Azure Data Studio provides a link that will open the file in ADS so we can modify it.

Reading the Json File

Let’s take a few minutes to go over how to read the json file.  While this is not a json post, knowing how to read what you see is important.  We will discuss only the two settings that are relevant to this post.  While reading this, please keep in mind I am not a json expert. Jim Dorame has a really nice SQL Saturday presentation titled “Json for the Data Mortals” that is very good.  He presented it at the SQL Saturday in Madison, WI this past year.  The presentation can be found here.  The image below is a screenshot of what you might see if you clicked either one of the links in the settings window.

If you look above, you will see these []. These define an array.  In this case the array is named: dataource.connectiongroups.  Under that you will begin to see these:{}.  These will define each object within the array. In this case there are four items.  Finally within each object you can find the properties.

Notice that each object has an ID as well as the ParentID.  The ParentID can be used to create a hierarchy of the server group.  This could be useful in many ways.  For example, you have a ton of SQL Servers, some in North America and some in Europe.  You could create a top level group named Produciton and then under that of two subgroups, North America and Europe.  Similar to what you see below.

Now let’s take a quick look at the Json.  In the image below, you will notice that the parentID for the North America object is the same as the ID for the Production object.  If you wanted to move the North America group to another top level group, you will need to find the ID of that new group and paste it into the ParentID setting of the North America group.  When you exit out of the settings.json file, you will be asked to save the file.  Of course you will want to save it.  If you do change it, keep in mind that the setting won’t take effect until Azure Data Studio is restarted.

Another property you may want to change is the group color.  When creating the server group, you only have a few options.

Well, what do I do if either I don’t like the colors or I have more groups that colors.  Again, this is where the settings.json file will come into play.

For each group you will see a color setting.  All you will need to do is find the hexadecimal number for the color you are wanting to use and place it into this property.  Here is a few good links, HTMLColors and Color-Hex.

Let’s say I wanted a group to be purple.  I could find the hex number, which is #800080 and paste it in the settings.json file as you can see below.

After restarting Azure Data Studio, this is what you will see, North America is now purple.

Now you have all the information to create groups to help you manage you servers.

Thanks for visiting my blog!!!

 

 

 

 

ADS – How to Mark a Tab as Production or QA

Have you ever been in an environment where you worked with both DEV\QA servers and Production servers?  Of course you have, we all have.  However, being in such an environment brings a certain level of risk.  We all try to be very careful to run any code we are asked to run on the correct server and in the correct environment.  Despite all of our checks and our Due diligence, we are still human and do make a mistake from time to time.  Azure Data Studio has a nice feature that will allows us to change the color of tabs based on the environment the server is in.  The image below is an example of what it would look like.

Setting this up can easily be done in two steps, creating Server Groups with different colors and changing the tab color property in Settings.

Create a New Server Group

Creating a new server group is pretty easy.  When in the servers window, float your cursor in the upper right corner and three buttons will appear.  These buttons are, from left to right, New Connection, New Server Group and Show Active Connections.  The middle one is the one we want.

Once you do, a very simple window will open up.  This window may appear on the right side of your screen, which is by default where it will appear.  To create a group you only need three bits of information, the group name, group description and color.

Once you click “OK”, you will now see your server group.  You will see the description when you float your cursor over the group.

Now that we have our server group, it is time to add a connection to that group.  This is pretty easy as well.  When creating a new connection, one of the properties you will be asked to fill out, although not required, is the Server Group.

What about if it is an existing connection?  This is even easier.  When in the connections window, simply drag the connection to the group header that you want to move it to.

Tab Settings

Now that we have our groups, it is time to set Azure Data Studio up so the query tabs will match the group color, similar to the image below.

To do this we need to change a property.  The property can easily be found by first going to Settings.  This can be found under the File menu.

File —> Preferences —> Settings

Once there, do a search on “Tab Color” and the proper setting will be returned.  The name of the setting is SQL: Tab Color Mode.

In the drop down box there are three options.

      •  Off
      • Border
      • Fill

Off is what it is…Off.  Border will place a colored line that matches the group color.  Fill turns the entire tab the color of the group.  If you change the setting, you should restart Azure Data Studio.

This is what the Border setting will look like.  Notice that there is a line across the top of the tab.

Now we have an additional check we can make to help us assure we are in the proper environment.

Thanks for visiting by blog!!!