Tech News - Databases

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.  Today’s tip is to congratulate someone for an achievement that may go unnoticed. This is a double congrats, for someone that I know and don’t get to see often enough. Some of you may also know Glenn Berry, hardware expert and the author of the DMV Diagnostic queries that lots of people use. Glenn left his job and struck out on his own this past year and he’s been busy working on a number of things where he’s had some success that I think is worth noting. I have probably left some positive notes on posts, but I’m going to use today as a way to congratulate him for a few things. First, his YouTube channel has over 500 subscribers, which is a nice accomplishment in relatively short time. Second, he’s been winning awards for his beer. Congrats to him, and hopefully I’ll get the chance to try some of these. I was over at his house last summer, watching him brew beer one day and got to try a few of his creations. Looking forward to doing it again. The post Daily Coping 29 Dec 2020 appeared first on SQLServerCentral.

“It was the best of times, it was the worst of times, it was the age of wisdom, it was the age of foolishness, it was the epoch of belief, it was the epoch of incredulity, it was the season of light, it was the season of darkness, it was the spring of hope, it-> Continue reading Goodbye PASS The post Goodbye PASS appeared first on Born SQL. The post Goodbye PASS appeared first on SQLServerCentral.

Christmas is this week so not a technical post for this week.  Just a simple post wishing you and your family as many blessing as possible (especially in the year 2020) and good tidings during this holiday time.  I hope that 2020 wasn’t too harsh on you or anybody close to you.  May the holidays bring you peach and joy! Take care and wear a mask! © 2020, John Morehouse. All rights reserved. The post Merry Christmas first appeared on John Morehouse. The post Merry Christmas appeared first on SQLServerCentral.

A recurring theme in the world of SQL Server seems to be the battle with Kerberos, SPNs, and SSPI Context errors. It is common enough that many DBAs have gone bald along with their domain admin counterparts trying to fix the problems. Once upon a time, I contributed an article showing a decent tool that can help figure out some of the problems related to SPNs, SSPI errors, and Kerberos in general – with regards to SQL Server. The tool I mentioned in that article is called “Kerberos Configuration Manager” (KCM). Recently, I ran into an error with this tool that is a bit absurd and not very helpful at all. Given the usefulness of the error and absurdity of it, I thought to myself – why not share the problem and resolution in this, the first article in this year’s 12 Days of Christmas Series. What A Wretched Little Error On the surface this seems like a pretty chill error. It seems that there is useful information in the error screen that could help one go and figure out what to do next. Looking at the error text, it appears that I have an issue with permissions because I can’t access UserAccount information and the error text gives me a log to go check. Let’s break this down a little bit. This error pops up with a user that happens to be a member of the local admins group on the server in question. The user also happens to be a domain administrator. And <grimace>, this user is also a sysadmin in SQL Server. So, seemingly permissions should not be an issue, right? I know, I know. This is not an ideal security setup. It just so happens to be a point of interest currently being discussed and worked on with this client. The security setup will get better. That said, I would eliminate permissions as a variable, and therefor permissions would not be a cause in this error. Let’s take a look at the next given (sorry mathematical proof concept shining through there), aka “bit of information from the error text”. The error text tells me there is a log available and gives me the directory where it should exist, so it is time to look at that log. If I proceed to open that file and look at the contents, I frequently see something like this ( 4 for 4 with this particular client). Note here that the file is entirely empty. This is a problem! The place I am supposed to look to resolve this problem has nothing logged to the file. How can I possibly use that to troubleshoot? Well, keep reading. The Cause and How to Fix this Kerberos Config Mgr Error The error message is certainly misleading. Then again, maybe it isn’t. As it turns out, the cause of the message is due to the existence of a ghosted AD account in the local admins group. Here is an example of what I am talking about in the image to the right. The local admins group on each of the affected systems had at least one of these detached SIDs. These are accounts that basically don’t exist any longer in Active Directory. These accounts should be cleaned out on a regular basis and it is a fairly risk free process. Given this bit of insight, if you re-examine the error text, it now makes sense. There is an account for which the tool cannot gain access because the account does not truly exist – just some shards of it. To fix the error, just delete these SIDs from the Local Admins group and then run the KCM tool again. After the ghost SIDs are removed, then an interesting thing happens (besides the KCM working properly). When you open the log file again, you will see something different. Here is an example. Looking closer at the text of the log, this is the line of the greatest interest: Error: Access of system information failed System.DirectoryServices.AccountManagement.PrincipalOperationException: An error (1332) occurred while enumerating the group membership. The member’s SID could not be resolved. Clearly, if this message had populated before the problem was fixed, then I would have been able to fix the problem in a more direct path. This clearly states that there is a problem with an SID and that the SID could not be resolved. Why the tool needs to be able to resolve all SIDs escapes me, but it is what it is and we just roll with it for now. Put a bow on it This article showed a problem with one of my favorite tools – Kerberos Configuration Manager. This tool does provide a great deal of power in helping to resolve various SPN related problems with your SQL Server instances. Sadly, the error in this case is a bit of a pain to figure out because the log doesn’t populate properly when the error is thrown. Rather the log seems to populate after the error is resolved. The solution provided in this article is an easy fix and is consistent across multiple versions of Windows and SQL Server. Save yourself some headache up front, just delete those phantom SIDs from the local admin group on a routine basis. They shouldn’t be there anyway. Interested in more back to basics articles? Check these out! Want to learn more about your security? Try this article on some of the many problems with lazy security in your environment (yes akin to a user being a Domain Admin, Sysadmin, and local admin). Here is another fantastic article discussing some of the persistent issues I have seen across the board at numerous clients for years and years. And as a prelude to an upcoming article in the 12 Days of Christmas series, here is a refresher on a topic I wrote about just last month. This is the first article in the 2020 “12 Days of Christmas” series. For the full list of articles, please visit this page. The post How To Resolve User Error in Kerberos Configuration Manager first appeared on SQL RNNR. Related Posts: The Gift of the SPN December 10, 2019 CRM Data Source Connection Error January 23, 2020 Collation Conflict with Extended Events March 12, 2020 Configuration Manager is Corrupt December 17, 2019 Ad Hoc Queries Disabled December 20, 2019 The post How To Resolve User Error in Kerberos Configuration Manager appeared first on SQLServerCentral.

The long awaited general release of MySQL 8.0 was finally announced last week.  MySQL, as we all know, is the world’s most popular open source database. Despite the growing adoption of NoSQL databases, MySQL continues to be widely used across the industry. The new features introduced in version 8.0 bring significant upgrades in performance, security as well as database development and administration. What’s new in MySQL 8.0 server? Let’s take a quick overview of all the new features and additions introduced in MySQL 8.0, and how they may affect the developers and DBAs: SQL Window Functions, including major enhancements to table expressions, indexes, regular expressions, and more New JSON functions and enhancements to performance, for working with JSON values GIS Support added, which means MySQL 8.0 is now capable of handling and working with geographic data with efficiency. Spatial data types, indexes and functions have been introduced. Better reliability, with DDL statements becoming atomic and crash-safe. New enhancements to InnoDB means the metadata is now stored more securely and can be worked with in a better manner. Significant enhancements to performance schema, configuration variables, and error logging. New security enhancements, with improvements to OpenSSL, SQL roles, changes to authentication and privileges and more Performance improvements, with InnoDB now able to perform better read/write workloads and better resource optimization There are lot more enhancements to the MySQL database such as replication, MySQL shell, and the different DevAPI-based connectors.To know more about the newly added features in MySQL 8.0 in detail, you can check out their official blog page. Download the in-demand 8.0 release to try the new features of MySQL! Additionally, to upgrade your existing MySQL installation from the previous version, you can also check out official MySQL 8.0 documentation. Read More Top 10 MySQL 8 performance bench-marking aspects to know 12 most common MySQL errors you should be aware of  

PASS was a big influence on a lot of us and did a lot of good, if never quite as much good as many of us wished. I wish PASS had survived, but it didn’t, and now we’re at a crossroads for what comes next. We’ve got short term challenges as far as supporting events that are coming up in the next few months and getting groups moved to an alternative of some sort, but beyond that, we have to decide if we want a successor to PASS or not. I think to answer that, it depends on what we want the new org to do. What would that new mission statement be and can all (most) of us agree on it? Even before we get into funding and a governance model, what can/could a new org do that we care about? My short answer is that a new org should do all the stuff that doesn’t make much money, if any. I think it would exist to facilitate career growth in areas not served (or served well) by for profit businesses. I think it could be an org we see as the glue without being in control. I think it probably doesn’t include a Summit class event because it just over shadows everything else. I think it could help facilitate regional events via grants and experienced volunteers. I think it can’t be all things to all people, but it could be some thing to many people. Back in 1998 defining it the focus as SQL Server was an obvious move. Today, there’s still plenty of people that use SQL, but there is lots of other stuff going on and figuring out where to draw the line is important, because that mission statement helps you evaluate everything you do or don’t do. Microsoft Data Platform excludes a lot of cool stuff. Focusing on Azure tends to ignore the world of on premise, AWS, and Google. But…it depends on what you want to accomplish, doesn’t it? Is it to be a professional association? To make money? To do good at a larger scale than a single product or profession? Or to narrow the focus, perhaps on day long events or SQL DBA’s or growing speakers or whatever. I made a small wish list (and surely I could add another 100 lines to this!): A real non-profit, with a sturdy and clear charter that includes a commitment to transparency, and one that owns all the intellectual property we choose to put into it (for example, SQLSaturday.com if we can get it) A plan for raising the minimal amount of funds needed for things like owning a domain, hosting event sites, etc, and building a multi year reserve No full time staff and limited outsourcing on a project basis, with all the day to day stuff automated or handled by volunteers Vendor agnostic, vendor independent, but one that recognizes the importance of vendors in our work and our community. A solid way of deciding who can be a voting member (one person=one vote) and who can join the Board An org that we’ll be proud of and hold up as a best in class example of how to build a technical professional association. As few rules as possible To answer the question I posed in the title, I haven’t decided yet (though I started out two weeks ago thinking “yes”). I don’t know if its possible or practical to have a single successor org to PASS. I’m still thinking about it, and waiting to see what ideas bubble up over the next couple of months. The post Should There Be A Successor to PASS? appeared first on SQLServerCentral.

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.  Today’s tip is to Give away something you have been holding on to. I have made more donations this year and in the past,. Partially I think this is because life slowed down and I had time to clean out some spaces. However, I have more to do, and when I saw this item, I decided to do something new. I’m a big supporter of Habitat for Humanity. During my first sabbatical, I volunteered there quite a bit, and I’ve continued to do that periodically since. I believe shelter is an important resource most people need. site:I’ve had some tools at the house that I’ve held onto, thinking they would be good spares. I have a few cordless items, but I have an older miter saw and a table saw that work fine. Habitat doesn’t take these, but I donated them to another local charity that can make use of them. I’m hoping someone will use them to improve their lives, either building something or maybe using them in their work. The post Daily Coping 24 Dec 2020 appeared first on SQLServerCentral.

I was just working on a new SQL job, and part of creating the job was adding a few new tables to our DBA maintenance database to hold data for the job.  I created my monitoring queries, and then created new tables to hold that data  One tip - use SELECT...INTO as an easy way to create these types of tables - create your query and then add a one-time INTO clause to create the needed object with all of the appropriate column names, etc. https://i.redd.it/1wk7ki3wtet21.jpg SELECT DISTINCT SERVERPROPERTY('ServerName') as Instance_Name , volume_mount_point as Mount_Point , cast(available_bytes/1024.0/1024.0/1024.0 as decimal(10,2)) as Available_GB , cast(total_bytes/1024.0/1024.0/1024.0 as decimal(10,2)) as Total_GB , cast((total_bytes-available_bytes)/1024.0/1024.0/1024.0 as decimal(10,2)) as Used_GB , cast(100.0*available_bytes/total_bytes as decimal(5,2)) as Percent_Free , GETDATE() as Date_Stamp INTO Volume_Disk_Space_Info FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs       INNER JOIN sys.master_files AS mf WITH (NOLOCK)       ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id   CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID)    order by volume_mount_point I thought at this point that everything was set, until I tried to write my next statement... The dreaded Red Squiggle of doom! I tried to use an alias to see if Intellisense would detect that - no luck. Some Google-Fu brought me to the answer on StackOverflow - there is an Intellisense cache that sometimes needs to be refreshed. The easiest way to refresh the cache is simply a CTRL-SHIFT-R, but there is also a menu selection in SSMS to perform the refresh: Edit>>Intellisense>>Refresh Local Cache In my case, once I performed the CTRL-SHIFT-R, the red squiggles disappeared! https://memegenerator.net/img/instances/61065657/you-see-its-magic.jpg Hope this helps! The post Toolbox - When Intellisense Doesn't See Your New Object appeared first on SQLServerCentral.

Some time ago I started gathering the SQL Saturday XML files. I started to parse and work with these, but the data is a mess and it never was that important. I regret that now. In any case, with the announcement of insolvency from PASS recently, I asked people to save some data. One of those that responded was Ben Weissman (b|t), and he actually rendered out all the available schedules as PDFs (using VB, of course). He sent them to me and I decided to load them up. Tl;dr; You can see my page here: https://dataplatformdaysweb.azurewebsites.net/ I had started an initial project for replacing SQL Saturday, but hadn’t planned on anything more than a static site. Actually, I wanted a sortable grid, but that was beyond the time and web skills I had at this time. That’s still a goal. This is a quick look at how I built things. I am not a real software developer, at least not on the current, interactive web. This stuff likely makes sense to any junior web dev, but it was learning for me. Azure DevOps and Dot Net Core I wanted a simple site, but I wanted this built in Azure DevOps, so it has some control and tracking. I thought about a simple HTML site, but producing a build with that didn’t seem intuitive to me, so I fired up Visual Studio. I chose a Dot Net Core ASP.NET REACT application, as I may move this to Linux. It’s cheaper In any  case, I took the defaults. No real reason other than I’ve tried MVC and that was hard, and lots of people seem to like react. I also have people I can bug at Redgate. I got the default project to build locally. Then I changed the names of the pages and loaded this into an Azure DevOp repo. Once up there, I took a default build process. I pointed this at my repo and then clicked Save and Queue… and it failed. Changes to the Build I got a message that the nuget restore wouldn’t work with dotnet core 3.1. I could fall back to 2.2, but when I did that, the project wouldn’t build locally. I realized I’d initially selected a Windows VS-2016 hosted agent, but I had built the project on VS2019. I changed that to the Windows 2019 agent and it worked. Deployment to Azure I’d set up an Azure App Service already, and I created a very simple release step. I linked my artifact and selected a release to an Azure App Service Plan. I had to authorize my plan, but once I did that, I was able to select the App Service I’d set up. No configuration needed. I clicked save, built a release, and ran. I found the default React Site at my URL. Changes to the Project I made a few changes to the project as well, to remove some of the defaults. First, I needed to load my PDFs into the project. I had originally created an Assets folder in the root of the project, but that did not get included in the artifact that was built. Looking at the project, and searching around Google a bit, led me to see that the main page, index.html, was in the ClientApp/public folder. I moved my Assets folder below this, and then saw all my files included in the build artifact and deployed. I also wanted to remove some of the default sample menu items. I found these in the ClientApp/src/components folder in the NavMenu.js. I deleted the two entries, leaving just a “home” there for now. I may do some other grouping later. Building the Archive This was the more interesting item for me. Ben had sent me a ZIP file with all the PDF files in it. I unzipped these and I saw this view: Originally I thought a simple list of numbers and files would get me started, but there are hundreds of files. How can I do this? My first thought as PowerShell can help. I popped this open and use Get-ChildItem to get a list of files and compile this into a variable. I have been wanting to use Azure Data Studio more for PoSh, and that’s where I did this. This got me a basic HTML list of my files. I had trouble with the pathing, so rather than futz around and try to build production code here, I just used this and then a “search and replace” of the [a href=”] to add a [a href=”/Assets/PDF/”] got me the correct paths. I need to learn how to properly get paths working here in PoSh, but this string manipulation wasn’t important for a one off task. Once I had this, I had something. Of course, at this point, Ben sent me his index list of the event names, which was what I really wanted. I could have taken the source of his page and used search and replace to get the pathing, but I did something stupider. In a hurry, I copied and pasted his list of events into SSMS in a new Query Window. One of the reasons I do this is that the cross line editing is superior (IMHO) to VS and VSCode. I’ll repeat the process with just a few lines here, but keep in mind I had like 800. This is a useful text trick as well for some data changing. I had this list: I wanted to make this a table, so I use the Select+Alt+Arrows to select the entire first column. I then added my table HTML. I could do this in VSCode, but the reason I like SSMS is that I can space over to the right and then get a vertical edit link, rather than a set of end-of-line cursors. I then can create another edit point and add other code, like this: I wrapped this in the table beginning and ending and had my table. What about the URLS? Well, I could easily add the paths, but then getting the individual file names was hard. Or was it? I used the same trick. I pasted my list code into SSMS and selected all the file names: I copied and pasted this set of vertical text into my table, and viola, I had a working table that looked, well, about as good as I could make it quickly. More to come, as I try to archive and preserve the SQL Saturday data and history as best I can. The post Building a SQL Saturday Archive appeared first on SQLServerCentral.

I’ve been working on a project where I use Azure Data Factory to retrieve data from the Azure Log Analytics API. The query language used by Log Analytics is Kusto Query Language (KQL). If you know T-SQL, a lot of the concepts translate to KQL. Here’s an example T-SQL query and what it might look like in KQL. --T-SQL: SELECT * FROM dbo.AzureDiagnostics  WHERE TimeGenerated BETWEEN '2020-12-15 AND '2020-12-16' AND database_name_s = 'mydatabasename' //KQL: AzureDiagnostics  | where TimeGenerated between(datetime('2020-12-15') .. datetime('2020-12-16'))  | where database_name_s == 'mydatabasename' For this project, we have several Azure SQL Databases configured to send logs and metrics to a Log Analytics workspace. You can execute KQL queries against the workspace in the Log Analytics user interface in the Azure Portal, a notebook in Azure Data Studio, or directly through the API. The resulting format of the data downloaded from the API leaves something to be desired (it’s like someone shoved a CSV inside a JSON document), but it’s usable after a bit of parsing based upon column position. Just be sure your KQL query actually states the columns and their order (this can be done using the Project operator). You can use an Azure Data Factory copy activity to retrieve the results of a KQL query and land them in an Azure Storage account. You must first execute a web activity to get a bearer token, which gives you the authorization to execute the query. Data Factory pipeline that retrieves data from the Log Analytics API. I had to create an app registration in Azure Active Directory for the web activity to get the bearer token. The web activity should perform a POST to the following url (with your domain populated and without the quotes): "https://login.microsoftonline.com/[your domain]/oauth2/token" Make sure you have added the appropriate header of Content-Type: application/x-www-form-urlencoded. The body should contain your service principal information and identify the resource as "resource=https://api.loganalytics.io". For more information about this step, see the API documentation. Data Factory Copy Activity The source of the copy activity uses the REST connector. The base url is set to "https://api.loganalytics.io/v1/workspaces/[workspace ID]/" (with your workspace ID populated and without the quotes). Authentication is set to Anonymous. Below is my source dataset for the copy activity. Notice that the relative url is set to “query”. ADF Dataset referencing a REST linked service pointing to the Log Analytics API The Source properties of the copy activity should reference this REST dataset. The request method should be POST, and the KQL query should be placed in the request body (more on this below). Two additional headers need to be added in the Source properties. Additional headers in the Source properties of the ADF copy activity The Authorization header should pass a string formatted as “Bearer [Auth Token]” (with a space between the string “Bearer” and the token). The example above retrieves the token from the web activity that executes before the copy activity in the pipeline. Make sure you are securing your inputs and outputs so your secrets and tokens are not being logged in Data Factory. This option is currently found on the General properties of each activity. Embedding a KQL Query in the Copy Activity You must pass the KQL query to the API as a JSON string. But this string is already inside the JSON created by Data Factory. Data Factory is a bit picky in how you enter the query. Here is an example of how to populate the request body in the copy activity. { "query": "AzureDiagnostics | where TimeGenerated between(datetime('2020-12-15') .. datetime('2020-12-16')) | where database_name_s == 'mydatabasename'" } Note that the curly braces are on separate lines, but the query must be on one line. So where I had my query spread across 3 lines in the Log Analytics user interface as shown at the beginning of this post, I have to delete the line breaks for the query to work in Data Factory. The other thing to note is that I am using single quotes to contain string literals. KQL supports either single or double quotes to encode string literals. But using double quotes in your KQL and then putting that inside the double quotes in the request body in ADF leads to errors and frustration (ask me how I know). So make it easy on yourself and use single quotes for any string literals in your KQL query. In my project, we were looping through multiple databases for customized time frames, so my request body is dynamically populated. Below is a request body similar to what I use for my copy activity that retrieves Azure Metrics such as CPU percent and data storage percent. The values come from a lookup activity. In this case, the SQL stored procedure that is executed by the lookup puts the single quotes around the database name so it is returned as ‘mydatabasename’. { "query": "AzureMetrics | where TimeGenerated between (datetime(@{item().TimeStart}) .. datetime(@{item().TimeEnd})) | where Resource == @{item().DatabaseName} | project SourceSystem , TimeGenerated , Resource, ResourceGroup , ResourceProvider , SubscriptionId , MetricName , Total , Count , Maximum , Minimum , TimeGrain , UnitName , Type, ResourceId" } With dynamically populated queries like the above, string interpolation is your friend. Paul Andrew’s post on variable string interpolation in a REST API body helped me understand this and get my API request to produce the required results. You can do similar things with Data Factory to query the Application Insights API. In fact, this blog post on the subject helped me figure out how to get the Log Analytics data I needed. Be Aware of API Limits There are limits to the frequency and amount of data you can pull from the Log Analytics API. As noted in the API documentation: Queries cannot return more than 500,000 rows Queries cannot return more than 64,000,000 bytes (~61 MiB total data) Queries cannot run longer than 10 minutes (3 minutes by default) If there is a risk that you may hit the limit on rows or bytes, you need to be aware that the Log Analytics API does not return an error in this case. It will return the results up to the limit and then note the “partial query failure” in the result set. As far as I can tell, there is no option for pagination, so you will need to adjust your query to keep it under the limits. My current process uses a Get Metadata activity after the copy activity to check file sizes for anything close to the limit and then breaks that query into smaller chunks and re-executes it. It’s All in the Details I had a lot of trial and error as I worked my way through populating the request body in the API call and dealing with API limits. I hope this helps you avoid some of the pitfalls. The post Retrieving Log Analytics Data with Data Factory appeared first on SQLServerCentral.

(2020-Dec-21) While working with Azure Functions that provide a serverless environment to run my computer program code, I’m still struggling to understand how it actually works. Yes, I admit, there is no bravado in my conversation about Function Apps, I really don’t understand what happens behind a scene, when a front-end application submits a request to execute my function code in a cloud environment, and how this request is processed via a durable function framework (starter => orchestrator => activity).  Azure Data Factory provides an interface to execute your Azure Function, and if you wish, then the output result of your function code can be further processed in your Data Factory workflow. The more I work with this couple, the more I trust how a function app can work differently under various Azure Service Plans available for me. The more parallel Azure Function requests I submit from my Data Factory, the more trust I put into my Azure Function App that it will properly and gracefully scale out from “Always Ready instances”, to “Pre-warmed instances”, and to “Maximum instances” available for my Function App. Supported runtime version for PowerShell durable functions, along with data exchange possibilities between orchestrator function and activity function requires a lot of trust too because the latter is still not well documented. My current journey of using Azure Functions in Data Factory has been marked with two milestones so far: Initial overview of what is possible - http://datanrg.blogspot.com/2020/04/using-azure-functions-in-azure-data.html Further advancement to enable long-running function processes and keep data factory from failing - http://datanrg.blogspot.com/2020/10/using-durable-functions-in-azure-data.html Photo by Jesse Dodds on Unsplash Recently I realized that the initially proposed HTTP Polling of long-running function process in a data factory can be simplified even further. An early version (please check the 2nd blog post listed above) suggested that I can execute a durable function orchestrator, which eventually will execute a function activity. Then I would check the status of my function app execution by polling the statusQueryGetUri URI from my data factory pipeline, if its status is not Completed, then I would poll it again.  In reality, the combination of Until loop container along with Wait and Web call activities can just be replaced by a single Web call activity. The reason for this is that simple: when you initially execute your durable Azure Function (even if it will take minutes, hours, or days to finish), it will almost instantly provide you with an execution HTTP status code 202 (Accepted). Then Azure Data Factory Web activity will poll the statusQueryGetUri URI of your Azure Function on its own until the HTTP status code becomes 200 (OK). Web activity will run this step as long as necessary or unless the Azure Function timeout is reached; this can vary for different pricing tiers - https://docs.microsoft.com/en-us/azure/azure-functions/functions-scale#timeout The structure of statusQueryGetUri URI is simple: it has a reference to your azure function app along with the execution instance GUID. And how Azure Data Factory polls this URI, is unknown to me, it's all about trust, please see the beginning of this blog post https://<your-function-app>.azurewebsites.net/runtime/webhooks/durabletask/instances/<GUID>?taskHub=DurableFunctionsHub&connection=Storage&code=<code-value> This has been an introduction, now the real blog post begins. Naturally, you can execute multiple instances of your Azure Function at the same time (event-driven processes or front-end parallel execution steps) and the Azure Function App will handle them. My recent work project requirement indicated that when a parallel execution happens, a certain operation still needed to be throttled and artificially sequenced, again, it was a special use case, and it may not happen in your projects. I tried to put such throttling logic inside of my durable azure function activity, however, with many concurrent requests to execute this one particular operation, my function had app used all of the available instances, while the instances were active and running, my function became not available to the existing data factory workflows. There is a good wiki page about Writing Tasks Orchestrators that states, “Code should be non-blocking i.e. no thread sleep or Task.WaitXXX() methods.” So, that was my aha moment to remove the throttling logic from my azure function activity to the data factory. Now, when an instance of my Azure Function finds itself that it can’t proceed further due to other operation running, it completes with HTTP status code 200 (OK), releases the azure function instance, and also provides an additional execution output status that it’s not really “OK” and needs to re-executed. The Until loop container now will handle two types of scenario: HTTP Status Code 200 (OK) and custom output Status "OK", then it exits the loop container and proceeds further with the "Get Function App Output" activity. HTTP Status Code 200 (OK) and custom output Status is not "OK" (you can provide more descriptive info of what your not OK scenario might be), then execution continues with another round of "Call Durable Azure Function" & "Get Current Function Status". This new approach for gracefully handling conflicts in functions required some changes in Azure Function Activity: (1) to run regular operation and completes with the custom "OK" status or identify another running instance, completes the current function instance and proved "Conflict" custom status, (2) Data Factory adjustments to check the custom Status output and decides what to do next. Azure Function HTTP long polling mission was accomplished, however, now it has two layers of HTTP polling: natural webhook status collection and data factory custom logic to check if my webhook received OK status was really OK. The post Using Azure Durable Functions with Azure Data Factory - HTTP Long Polling appeared first on SQLServerCentral.

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag. Today’s tip is to share a happy memory or inspiring thought with a loved one. Not sure I need to explain, but I did show my kids this one from a celebration.. The post Daily Coping 22 Dec 2020 appeared first on SQLServerCentral.

One of the most significant design considerations in ETL process development is the volume of data to be processed. Most ETL processes have time constraints that require them to complete their load operations within a given window, and the time required to process data will often dictate the design of the load. One of the more common mistakes I’ve seen... The post ETL Antipattern: Load Processes that Don’t Scale appeared first on Tim Mitchell. The post ETL Antipattern: Load Processes that Don’t Scale appeared first on SQLServerCentral.

Announced last week is a major new feature for Power BI: you can now use DirectQuery to connect to Azure Analysis Services or Power BI Datasets and combine it with other DirectQuery datasets and/or imported datasets. This is a HUGE improvement that has the Power BI community buzzing! Think of it as the next generation of composite models. Note this requires the December version of Power BI Desktop, and you must go to Options -> Preview features and select “DirectQuery for Power BI datasets and Analysis Services”. You begin with a Power BI dataset that has been published to the Power BI service. In Power BI Desktop you connect to the dataset, where you can build a new model over the original one.  You can extend the original model by adding tables, columns, and measures, and you can also connect to other datasets and combine them into a single semantic model.  While doing this you do not lose any elements from the original model – measures and relationships continue to work. You do not have to worry about anything but the additional data you want to integrate. When the original model is refreshed, your local model also sees any updated information. You work as if you have a local copy of the model and full rights to modify and expand it, even though you are not duplicating any data already stored on the server. This feature is ideal for report authors who want to combine the data from their enterprise semantic model with other data they may own like an Excel spreadsheet, or who want to personalize or enrich the metadata from their enterprise semantic model. This seals the marriage between self-service and corporate BI. The main technology that makes this work is DirectQuery storage mode. DirectQuery will allow composite models to work with live connected sources and other data sources like Excel or SQL Server. Using DirectQuery for Power BI datasets and Azure Analysis Services requires that your report has a local model.  You can start from a live connection to an existing dataset and upgrade to a local model, or start with a DirectQuery connection or imported data, which will automatically create a local model in your report. Live connection is basically a connection to the remote model (the model is not inside of Power BI Desktop).  Converting the remote model to DirectQuery gives you a local model in Power BI Desktop.  So if you want to make changes to your live connection, you will first convert to a DirectQuery connection. If you don’t need to make changes to the remote model and just combine it with other models, you can keep the remote model as a live connection. Keep in mind that when you publish a report with a local model to the service, a dataset for that local model will be published a well.  This is the same behavior as when you publish a report with imported data to the service. When connecting to a remote model, the data for that model is kept in the cloud, and you can join it with another local model with it’s data to create new columns, new measures and new tables without ever moving the data from the remote model to your local PC. It’s an easy way to extend that remote model, which could be managed by IT and refreshed all the time. You are just responsible for managing and refreshing your local model and data. This is how you are combining the efforts of enterprise IT and end-users. With this new feature I can see many companies creating enterprise semantic models in Azure Analysis Services or in a premium instance of Power BI. These semantic models can have the entire business logic of your company using the huge amount of data from your data warehouse. Then users can use DirectQuery against those models and extend those models locally with their own calculations, without having to download any data from the semantic models to Power BI Desktop. This is definitely a game changer. This new feature allows you to do dataset chaining. Chaining allows you to publish a report and a dataset that is based on other Power BI datasets, which was previously not possible. Together, datasets and the datasets and models they are based on form a chain. For example, imagine your colleague publishes a Power BI dataset called Sales and Budget that is based on an Azure Analysis Services model called Sales, and combines it with an Excel sheet called Budget. If you create and publish a new report and dataset called Sales and Budget Europe that is based on the Sales and Budget Power BI dataset published by your colleague, making some further modifications or extensions, you are effectively adding a report and dataset to a chain of length three (the max supported), which started with the Sales Azure Analysis Services model and ends with your Sales and Budget Europe Power BI dataset: This opens a whole new world of possibilities. Before this feature was available, to modify a dataset you would have to get a copy of the pbix file with the dataset and make your own pbix copy, which would also include the data. Also, you were not able to chain models together or to combine datasets (i.e. making models of models). This is quite an improvement! Share your feedback on this new feature at this Power BI Community forum post. More info: New composite models in Power BI: A milestone in Business Intelligence Power BI Direct Query Composite Models = Amazing Composite Models Gen 2 and DirectQuery over Power BI Datasets Power BI Composite Models using Analysis Services -Direct Query Mode Composite models over Power BI datasets and Azure Analysis Services “Composite” arrived – CAUTION! Prologika Newsletter Winter 2020 The post DirectQuery for Power BI datasets and Azure Analysis Services (preview) first appeared on James Serra's Blog. The post DirectQuery for Power BI datasets and Azure Analysis Services (preview) appeared first on SQLServerCentral.

At first glance, the question of how many rows are estimated from a table variable is easy. But, is it really that simple? Well, not really. To dig into the why, first we need to identify why table variables estimate 1 row. The obvious answer is because they don’t have statistics. However… ALTER DATABASE CURRENT SET AUTO_CREATE_STATISTICS OFF GO CREATE TABLE Test (SomeCol INT); INSERT INTO Test (SomeCol) VALUES (1),(22),(37),(45),(55),(67),(72),(86),(91) SELECT SomeCol FROM Test SELECT * FROM sys.stats WHERE object_id = OBJECT_ID('Test') DROP TABLE dbo.Test That table has no statistics, but it still estimates rows correctly. So it’s not just the absence of statistics. Hmmm… Where else is there a difference with a table variable? It has to do with when the plans are generated. The XE event results are from an event tracking statement start and end and the post-compilation event for the plan. For the query using the table variable, the entire batch is compiled before the execution starts. For the permanent table, there are multiple compilation events. And this is because of something called ‘deferred compile’. For the table variable, the entire batch is compiled at the start, at a time where the table variable does not exist, and because there are no statistics, no recompile is triggered after the insert. Hence, there cannot be any row estimation other than 1 row, because the table did not exist when the estimate was made. For the permanent table, the compilation of the query that uses the table is deferred until the query starts, not when the batch starts. Hence the plan for the query is generated after the table exists, after it’s been populated. That’s the difference here. Now, there’s still no statistics, and so there’s no way to get data distribution, but that’s not the only way to get information on the rows in the table. The Storage Engine knows how many rows are in the table, though data distribution isn’t known. Hence, with a table variable we can expect to see an estimated row count other than 1 any time the table variable exists before the query that uses it is compiled. That will happen when the table variable is a table-type parameter, when the query using it has the RECOMPILE option, and when SQL 2019’s deferred compile for table variables is in play. CREATE OR ALTER PROCEDURE TestRowEstimations @Input TestTableType READONLY AS SELECT SomeCol FROM @Input; DECLARE @Test TABLE (SomeCol INT); INSERT INTO @Test (SomeCol) VALUES (1),(22),(37),(45),(55),(67),(72),(86),(91); SELECT SomeCol FROM @Test; SELECT SomeCol FROM @Test OPTION (RECOMPILE); GO Table-valued parameter Normal select on compatibility mode 140 Normal select on compatibility mode 150 Select with OPTION(RECOMPILE) The post On table variable row estimations appeared first on SQLServerCentral.