Видео

Postman (www.postman.com/) is not part of any Azure or Microsoft product. It is a tool you can use to test REST APIs. I use it in this video (and in quite a few other places) because it provides me an easy way of making an API call, seeing the response, and even building tests around how I expect the response to look.

@@KevinFeasel Thank you so much for your prompt reply, your videos are amazing!!!

I think I can do that for future videos. I know I typically have the gain turned way down to minimize outside noises but I should be able to do something for the videos.

As of today, the answer is kind of complicated. You can use Azure OpenAI within Power Query to enrich data: techcommunity.microsoft.com/t5/educator-developer-blog/how-to-use-azure-open-ai-to-enhance-your-data-analysis-in-power/ba-p/4041036 And there is Copilot for Power BI within Microsoft Fabric but it requires Microsoft Fabric and at least an F64 SKU: learn.microsoft.com/en-us/power-bi/create-reports/copilot-introduction There is at least one third-party custom component that might do what you want in AI Lens for Power BI, though there will likely be additional costs for licensing it: www.lensvisual.io/

@@KevinFeasel thanks kevin for detail answer, but instead of openai or copilot i want to use other open source model from huggingface. Is it feasible?

@@muhammadtalmeez3276 Probably not without a significant amount of development work on your end. Just spitballing an answer, you'd probably need to host your model via API and then create a custom component to send the user prompt plus data to that API and get the response back. It's the "plus data" that would make this a real challenge, especially if you intended to use it with sliders and to view data on charts. It's not something I've done before, though I suppose it is technically possible, just a major endeavor.

@@KevinFeasel thank you so much kevin. I am struggling this from last 3 days. Your answer is very helpful to conclude my research.

I did not come back to that GitHub Action execution or the workflow log, but I did show the results of the scan: that's the vulnerability alert that we see in the "Code scanning" menu item around 11:53. The workflow log itself has quite a few diagnostic entries, but isn't really that interesting on its own, unless you're troubleshooting a problem with CodeQL scans.

Thanks for the kind words. I do have some of the Azure certifications (data scientist, AI engineer, data engineer, admin, Power BI data analyst, database administrator). I do have on my backlog the idea of doing a series on data engineering. It's a huge topic, so I haven't quite figured out yet how I want to tackle it. But it is in the plan.

You should have the az cli installed beforehand, yes. I have the pre-requisites in the readme file for the demo code: github.com/feaselkl/Beyond-the-Basics-with-AzureML When it does run, it will use your local Python installation, yes. But that will then make a call to the Azure ML API and request executing code on a compute instance there, so you're not doing actual model training locally, just orchestrating it locally.

You're exactly correct. If we stop right after adding the Web Service Input, the model we deploy will work fine, but our web service will expect exactly the same input columns as our initial CSV file. That includes the PaymentIsOutstanding label column, which would be silly to include in production. For that reason, we remove the ChicagoParkingTickets file input and substitute it with a manual version that excludes PaymentIsOutstanding from our expected inputs.

Good luck on the exam, Rick.

I passed! These videos helped. Thank you for putting so much effort into these, Kevin.

If you're following along with the videos in this series, we don't have a Label column at all. For that reason, I might not be able to tell you for sure why you're getting that error. My best guess, given current information, is that perhaps you have something in the transformations on the training side, renaming the PaymentIsOutstanding column as Label. If you did that, then your inference would expect the PaymentIsOutstanding column because it would need something to rename as Label.

Thanks yours responding, Label is a column in my data which used to make a pipeline. First i used label is my target for training, after that i want to deploy a endpoint model, which can inference with new data (without label/target), but I fail. Do you have a suggestion?

@@113because Given current information, as I noted above, you might be using the Label column in one of your transformations (column renames, data type changes, cleaning missing data, etc.--basically, everything other than loading data, training the model, scoring the model, and evaluating the model). If so, then you'd have to remove Label from those transformations in the training section; otherwise, scoring will require Label in the Apply Transformation step.

That happens when you choose the Azure ML compute instance, which is at 6:35 in the video. That works because I've already set up the compute instance in a prior video in the series. To set one up, go to ml.azure.com and connect to your Azure ML workspace. Then, navigate to Compute and make sure the Compute instances tab is selected. Select the New button and you get a menu of available CPU and GPU options. As a quick note, trial accounts and free credits (like from Visual Studio subscriptions) won't have access to any GPU options, but you can at least see the GPUs that are available and if you are using a paid account, you can request quota for the setup you want.

The most likely reason is that you're actually pointing to the Ubuntu 20.04 packages list rather than Ubuntu 22.04. The Focal release (Ubuntu 20.04) had as part of its requirements for SQL Server 2022 libldap-2.4-2 specifically, and Ubuntu 22.04 removed version 2.4 in favor of version 2.5. You can check to see if this is the case by navigating to /etc/apt/sources.list.d/ and then running the command: "less mssql-server-2022.list" Inside there, it'll include a link to Microsoft's package server. If that includes /ubuntu/20.04/ in the domain, that's your culprit. First, uninstall mssql-server: "sudo apt remove mssql-server -y" Next, remove the mssql-server-2022.list file: "sudo rm mssql-server-2022.list" Then, add the Ubuntu 22.04 package link: "curl -fsSL packages.microsoft.com/config/ubuntu/22.04/mssql-server-2022.list | sudo tee /etc/apt/sources.list.d/mssql-server-2022.list" Now it should install the Ubuntu 22.04 version of SQL Server and not 20.04.

Merge replication is not supported on Linux, no. Merge replication tends to be something of a laggard for most features. You'll see transactional and snapshot replication support in a variety of features but merge and peer to peer are much less likely to be supported when new things come out, including SQL Server on Linux. learn.microsoft.com/en-us/sql/linux/sql-server-linux-replication?view=sql-server-ver16#supported-features

@@KevinFeasel so can i set Bidirectional Transactional Replication on Linux , I want the subscriber pull the data from the publisher and in some times subscriber can make some changes , i want to move out of windows to use linux and docker container

@@HusseinSaeed-ex7pm This is where the documentation gets a little tricky. There's nothing saying you *cannot* use bidirectional transactional replication, but there's also nothing explicitly saying you *can*. I haven't tried the scenario, to be honest, so I don't know whether that's possible. I'd recommend trying it out to see. If you made me give my prior probability on success, I'd say about 30% chance of success, as it's a more complicated replication scenario than normal transactional replication and unless there were Microsoft customers clamoring for support of that particular feature, it'd probably be low on their implementation list. But there is a chance bidirectional replication came "for free" with the normal transactional replication code--the only way to know for sure is to try it and see if it works.

Thanks for the idea. I'll add it to my backlog.

If you're talking about the best model generated from my prior AutoML video, we're training a new model from scratch to see how to do it. If you're asking in general, you can do this in a couple of ways. One is to train separate models as different experiment runs, saving each in the Azure ML model registry and comparing model results--for classification, you might check measures like accuracy and F1 score. A second option would be to train separate models as runs in an experiment but tagged under the same model type, saving different versions of a model in the registry. Then, after comparing, you could delete the versions that don't perform as well. A third option would be to perform comparative model analysis as part of your initial training: you can incorporate hyperparameter sweeping and even use of different algorithms in the training steps and then save the best model of the bunch to the registry. I don't have an example of doing this in a video but Microsoft does have a code example of using a hyperparameter sweep: github.com/Azure/azureml-examples/tree/main/sdk/python/jobs/pipelines/1c_pipeline_with_hyperparameter_sweep

You upload the dataset. To generate the file dataset, grab the file from the video description and save it locally. Then, upload it into Azure ML as a v1 Tabular type, not a v2 MLTable. In the Data menu, make sure you are on the Data assets tab. Then, select +Create and name the data asset something like ChicagoParkingTickets. Select in the Type menu "Tabular" from the v1 section. On the second page, create your data asset From local files and that will give you a few more pages around where to store the data, the file you want to upload, and additional settings. Those steps should be pretty straightforward, as I tried to ensure that there would be no complications with this dataset. The Python code is something you submit via API call and we do that in the next video in the series.

Two things come to mind. 1. Make sure that you are using Ubuntu 22.04 instead of 24.04. 24.04 just came out, so SQL Server won't support it yet. 2. Make sure to add the mssql-server-2202.list file that I show in the video. Command line instructions are available at 36chambers.wordpress.com/2023/12/26/video-installing-sql-server-on-ubuntu-22-04/ if you want to follow along that way.

I do dig into the last agenda item in a separate video on the channel: ruclips.net/video/JL6OCwtz3Cg/видео.html Admittedly, I don't get into monitoring or rollback, so I will add that to my backlog because it's a really good idea for a future video.

@@KevinFeasel Thanks so much Kevin; greatly appreciated :-)

Hmm, that's interesting. I do see azureml-inference-server-http available in pip, so the package hasn't been removed. If there's a typo, that could explain things, though I didn't see any typos based on the error message you have. One thing you could try is running the container locally to see if you get any additional information. I have some instructions at github.com/feaselkl/AzureML-Local-Deployment/tree/master and a video at ruclips.net/video/bue85m7lbjQ/видео.html. If you can successfully run the container locally, that would indicate an issue somewhere in Azure ML.

To generate the file dataset, grab the file from the video description and save it locally. Then, upload it into Azure ML as a v1 Tabular type, not a v2 MLTable. In the Data menu, make sure you are on the Data assets tab. Then, select +Create and name the data asset something like ChicagoParkingTickets. Select in the Type menu "Tabular" from the v1 section. On the second page, create your data asset From local files and that will give you a few more pages around where to store the data, the file you want to upload, and additional settings. Those steps should be pretty straightforward, as I tried to ensure that there would be no complications with this dataset. Uploading an MLTable asset is quite a bit more difficult than the v1 Tabular: learn.microsoft.com/en-us/azure/machine-learning/how-to-mltable?view=azureml-api-2&tabs=cli There's some work behind the scenes to add the MLtable metadata , so that, when I look at the ChicagoParkingTickets dataset in the Azure ML UI, I see the Dataset type = Tabular and Type = Table (mltable). That's why the output node for the Azure ML Designer says MLTable even though I never explicitly generated any MLTable metadata. Azure ML did the work for me after I uploaded the text file as a v1 Tabular dataset.

Is there a particular question you have around configuration? If not, I suggest checking out some of the links in the description, as they'll go into more detail on items.

If you mean you get an error that reads something like "the certificate chain was issued by an authority that is not trusted," that's simply saying that you are using a self-signed certificate, which is the default for SQL Server. If you are connecting from SQL Server Management Studio, you can tick the "Trust server certificate" box before connecting. If you're using Azure Data Studio, there's a Trust server certificate drop-down that you can set to True. I get into this in a little more detail during the video on firewalls and TLS in SQL Server on Linux: ruclips.net/video/MunQ_TA6JBQ/видео.html

@@KevinFeasel Hi thanks I solved this problem but, now I have another error that said "Login failed for user sa " Error 18456

@@patriciocampoverdeg.9171 Make sure you're using the correct password and have selected "SQL Authentication" as the authentication mechanism.

The Pipeline folder is separate from the Notebook folder. All of the code is available at github.com/feaselkl/Beyond-the-Basics-with-AzureML. Inside the Pipeline folder, we have three sub-folders: - config/ -- This stores the YAML files that we use for each step in the process. - scripts/ -- This stores the Python scripts that we use for each process. Because I am a simple man who tries not to complicate things too overly much, we have one Python script per config file and the names are very similar. - data/ -- This contains a couple data files we use for testing purposes. We also have two scripts, deploy-train.py and deploy-score.py, which handle training and scoring, respectively. There's no strict requirement to use this folder structure with Azure ML, though it does follow some reasonable practices around separating YAML and code.

@@KevinFeasel Hello Kevin, Thanks for the detailed response, I appreciate it. I apologize for not being a bit more clear because you explained most of this directory structure in your video already. What I actually want to know is where your repository, 'beyond-the-basics-with-azureml' lies within the AzureML filesystem? Is it a folder within Authoring > Notebooks -> Users/username? I'm not super familiar with Azure ML yet, so I'm not sure if there is another file system somewhere. In the previous video, I took your advice and used VScode so that I could commit my notebook to a Github repo. What ended up happening is it published the entire folder as a repo, so everything within my compute instance (which is Logs, and Users/username/ChicagoParking.ipynb). Since the video only shows us the directory structure inside the repo, I want to know what is the parent folder of 'beyond-the-basics-with-azureml', so I can organize my repos in a more professional way. Thanks again, I love these videos so far!

@@BusinessDuck69 Ah, I understand the question now. The short answer is, you don't need to upload the files at all. We run the code locally, rather than on our compute instance. We ran the notebook on the compute instance because that infrastructure is already there. Locally, I just have them in a \SourceCode\ folder that holds all of my Git repos. That said, if you want to run the code from your compute instance rather than local machine, you could upload the files into /Users/{username}/beyond-the-basics-with-azureml/, like you have it. Ultimately, think of the compute instance as a data science VM, and the storage associated with it is accessible across the team, but more as a set of scratch files. So admittedly, my AML folder structures sometimes look like a bit of a mess. The "real" outcome should be whatever you put into source control.

Hmm, that is a good question. It could very well be that you're using a trial subscription, but I'm not positive about that because I've not used the free trial subscription for any Azure ML testing. There's a GitHub post that does walk you through how you can see which VM classes you can use with the free trial: github.com/MicrosoftDocs/azure-docs/issues/56032. These commands use the Azure cloud shell and PowerShell. The idea would be that you could see which VM classes are enabled and what the quotas look like. Then, change the cpu-cluster to use one of the allowed classes and try again. The work I show in this video isn't particularly compute-heavy, so it should still work okay on a single instance of a smaller VM class.

Unfortunately, I have bad news and slightly less bad news. The bad news is that the 502 Bad Gateway response you're getting is (most likely) because the response to the Power BI gateway is too large, above 8MB. Here's a similar issue regarding Application Insights data: stackoverflow.com/questions/41869170/query-from-powerbi-to-ai-suddenly-fails-with-502-bad-gateway To fix this, there are three options I can see. One is, like you mentioned, reducing the amount of data you send (and receive) as part of the Power BI to Azure ML connection. Because that's not a desirable option for you, a second alternative would be to feed the data to Azure ML using a separate process, store the results, and then load that into Power BI. You lose the benefit of the AML-PBI direct integration and may have some level of delay in scoring data, but would not run into this issue. The third alternative would be something like a hybrid approach. Have a back-end process call your scoring endpoint for data, storing that someplace you can access it later. Then, in Power BI, have two Power Query tables: one with the scored results and one without (for records that you ingested after the last time your back-end scoring process ran). The one without, you'd perform just-in-time scoring. Then, union the two tables together and the output looks normal again. Some enterprising mind might know of a good fourth way, but I'm not aware of one.

Thank you for the note. This is where I readily admit that I am by no means a storage expert, so it's good to learn more about it. For people trying at home, there's an older thread (Ubuntu 16.04) at www.linux.org/threads/solved-how-to-use-mssql-with-drbd-and-pacemaker.27297/#post-84034 that covers the topic. I haven't tried it or validated that it works with current versions of Ubuntu + SQL Server, but it may serve as a good starting point.

Thanks for the note. Yes, the v2 Table type is a bit different from v1 Tabular and it makes use of the MLTable Python SDK to generate a file in a special format. I haven't spent much time working with that format, as I tend to focus on v1 Tabular or v2 Folder types. As for deleting datasets, you typically don't do that because then, you'd lose the data associated with prior experiments and job runs. Archival is useful for hiding it away, so you don't have old versions of datasets cluttering the Data assets page. But if the concern is that you don't want to spend the money on file storage (especially if the files are large), you can delete a data asset's contents by navigating to the Data menu under Assets in Azure ML. Then, select your archived data asset--you may need to switch the "Include archived" toggle on the right-hand side of the menu bar that contains Create, Refresh, Archive, and Reset view. Select your data asset, and in the bottom-right box, labeled Data sources, there's a gray box that contains a link: "View in Azure Portal." That link will take you to where the actual file is located in Azure Blob Storage, and you can delete the file from there. Then, toggle off the "Include archived" toggle in Azure ML Studio and it'll be out of sight.

Sure, I can dig into this as a future topic, automating the model movement process. There is a way of doing it by hand: you can download the model assets from the dev server's Models page. That gives you a zip file of assets. Unzip the files and upload a new folder in the Models page on the test server using the "+ Register" drop-down and selecting From local files. That wouldn't re-create any deployments or endpoints, so they'd need to be added separately. Later in this series, I do have a few videos on MLOps. In those videos, I re-train new models in each environment, so it's not quite the same thing.

@@KevinFeasel Hi Kev. I have done that and moved the model to the test environment. Thank you. Only problem is when I create the end new endpoint, I get the status that it’s unhealthy. That’s whats keeping me awake now. Thank you for the help. Really appreciate it.

Hi Kev. Turns out I don’t have gpu compute instance on my test env. I have requested for one. Could that be the reason why my end status always says unhealthy? I was deploying the endpoint with a cpu.

@@germplus If you were deploying with CPU and if your operations only require CPU support, then not having a GPU-based compute instance should be fine. Regarding the unhealthy state, you might find some information in the logs for that deployment. If the logs don't show anything useful, I've found the reason is often due to a failure in the container and Azure logging isn't great at those particular issues. I have another video on troubleshooting deployments locally: ruclips.net/video/bue85m7lbjQ/видео.html That shows you how to build the container image locally. Use the same asset files you pushed to the test environment and, after starting the container, check docker logs for any error messages. Also, just to make sure it wasn't one of the most common errors: when you get the zip file of assets, it'll be named something like File_.zip. Unzip the file and you'll have a folder called File_ and inside there is another folder. That inner folder is the one you want to upload, *not* the File_ folder itself. In my case, for example, the inner folder was named trained_models/. Basically, make sure the Artifacts tab of the test model matches what you have in the development model's Artifacts tab exactly.

@@KevinFeasel Thank you for the help Kev.

Azure Data Studio and SQL Server Management Studio have a lot of overlap but ultimately fit into different niches. SSMS is more of a traditional SQL Server DBA tool, so there's a lot of emphasis on administrative functionality, so it's easier to set permissions, change server settings, and review server logs. It also has a variety of built-in tools around things like monitoring replication, managing availability groups, etc. Azure Data Studio is more of a development tool, so the emphasis is on writing and running code. Its result set viewer is much more practical, allowing you to export results in various formats. Its extensions focus more on the developer path: support for Postgres and Cosmos DB, working with notebooks, and working with SQL Server ML Services. You're right that SSMS is Windows-only. There was never a good SSMS experience under WINE, so we couldn't even go that route for cross-platform support.

That's one route: install SSIS and run the package from cron. If you have the option, though, my recommendation is to avoid using SSIS on Linux altogether. Deploy the packages to Windows, where you get the SSIS integration catalog (SSISDB) and run SQL Agent jobs from it. Those SSIS packages can connect to SQL Server on Linux just fine via ODBC or OLEDB and you won't run into the issues I had during the video.

@@KevinFeasel oh thx for the advantage i'll gonna try that

I am not particularly familiar with the Common Data Model, so the best I can give you is a semi-educated guess. There's almost no information out there on how to *use* Azure ML with CDM-style data, but I could see the process working by registering the ADLS gen2 storage account as a Datastore, and then the Entity folders (using the example at learn.microsoft.com/en-us/common-data-model/data-lake) as uri_folder asset types. I'm not sure if there is an easier way to use the CDM metadata itself with Azure ML, unfortunately.

@@KevinFeasel thanks for the reply I was thinking the same thing, registering the cdm folder as a folder_uri asset. Thanks again

This version was just for Ubuntu 20.04 and earlier. I have a follow-on video for Ubuntu 22.04 at ruclips.net/video/T3sXphWAPOs/видео.html. Ubuntu 23.10 *might* work but would not be officially supported. Microsoft only supports the LTS releases of Ubuntu, so there's a risk some library versions may differ between Ubuntu releases. Still, I'd recommend starting with that latter video and see if you can get it working. I haven't tried with Ubuntu 23.10 so I couldn't tell you for sure either way.

There is no way to replicate the complete instance, no. It's just databases, either individually or in a set, if you have Enterprise Edition. As for matching logins, dbatools has a cmdlet to do that: docs.dbatools.io/Sync-DbaAvailabilityGroup.html That's the way I'd recommend keeping those databases in sync. SQL Server also has the notion of contained availability groups: learn.microsoft.com/sql/database-engine/availability-groups/windows/contained-availability-groups-overview The idea with those is that you can also replicate relevant parts of master and msdb, but there are some things they don't support, so check the "Not supported" section first.

Kevin, how can i manually failover to the other node in this case where cluster type is set to none? The standby databases are read only. So if primary goes down, how can i manually failover to the other node? Also, here listener ip is set to primary ip, so if primary fails, do i need to recreate listener with secondary ip (which is going to be the new primary) ??

@@FaizanFastian23 The way to fail over when there's no cluster type is available on Microsoft Learn: learn.microsoft.com/sql/database-engine/availability-groups/windows/perform-a-planned-manual-failover-of-an-availability-group-sql-server?view=sql-server-ver16#fail-over-the-primary-replica-on-a-read-scale-availability-group Specifically, the "Manual failover without data loss" section. Assuming nodes 1 and 2 both are in synchronous commit (like in my example), you'd start on step 4. ALTER AVAILABILITY GROUP [BusDataAG] OFFLINE ALTER AVAILABILITY GROUP [BusDataAG] FORCE_FAILOVER_ALLOW_DATA_LOSS Then, on the primary: ALTER AVAILABILITY GROUP [BusDataAG] SET (ROLE = SECONDARY); Then restart the AG: ALTER DATABASE [BusData] SET HADR RESUME To your note, yes, you would need to re-create the listener because there is no mechanism (Windows Server Failover Clustering or Pacemaker) that knows about the failover. This is the big downside to using NONE as the clustering type, but I wasn't able to show Pacemaker here because I didn't have RHEL or SLES to demonstrate full failover. With WSFC or Pacemaker, your listener would actually be a separate IP address associated with the clustering service, and the clustering service would handle routing that IP address.