Azure Data Factory Pipeline Variables (2021-11-17)

SQLServerCentral Newsletter for November 17, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Azure Data Factory Pipeline Variables]( - [Getting In A State Using Flyway]( - [Introduction to SQL Server sequence objects]( - [From the SQL Server Central Blogs - Notes on SQLSaturday Orlando 2021]( - [From the SQL Server Central Blogs - Row-Level Security Basics–#SQLNewBlogger]( Question of the Day - [The Priority Protocol]( The Voice of the DBA  Daily Coping Tip Do something playful outdoors – walk, run explore, or relax. I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. The API Bottleneck While talking with a client recently about their performance challenges, I was relieved to find that the database wasn't the problem. Instead, their API server was overloaded by the number of calls taking place in their application. While the database did provide the backing for the API calls, there was a fair amount of caching. However, as they'd moved to microservices, more and more of the interaction between modules was taking place as a network call to a single server, which became overloaded. Certainly, they could add more API servers, or break out different types of API servers, but each of these changes potentially has downsides. Data can become out of sync across two systems, which is why we often want a single database storing all data. Distributed systems are hard to architect and implement, and I suspect as more developers try to build them, we'll find more issues like this. Not that developers are doing anything wrong, but they often don't realize the challenges of building a distributed system when the workload scales. They're relearning the knowledge that others have gained through their own experiments and failures. One of the things we try to do here at SQL Server Central is share our knowledge with others. We hope to help each of you learn from others, and hopefully not need to make the same mistakes in your applications. As I listened to the client talk, I was reminded of [this post from Troy Hunt](. In it he talks about the HaveIBeenPwned site, which he built a scaled to meet demands. The site doesn't cost a lot, but there is a great quote about the API server he set up. He wrote: "I’ve tested it with [380k queries a minute](! Infrastructure will scale out and magic will happen, so long as you’re not maxing it out for perpetuity, just go for it." However, in your company, you might be scaling in perpetuity in an app, especially as a workload grows and more parts of your application need to make queries against an API. Good design, good coding, and adhering to good distributed system design, will help ensure you can handle the load. This is especially true in the cloud, where scaling up is just provisioning another resource, but there is a cost. You have to know what to do, so I urge anyone looking to build a distributed system to read about the experiences of others. Learn from their knowledge, rather than make your own mistakes and try to correct them. There is a lot of great information out there, but you need to be looking for resources that provide technical details, like [Troy's posts on how he built his site]( or Jeff Moden's posts on [writing high performing queries](. That's the type of knowledge that is invaluable if you want to be an efficient and effective developer. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Azure Data Factory Pipeline Variables]( arindamxs from SQLServerCentral Learn about variables in your ADF pipelines. [External Article]( [Getting In A State Using Flyway]( Additional Articles from Redgate Flyway Teams baseline migration scripts are a simple and fast way to deploy new copies of a database, at a specific version, for testing work, or to create a new branch during development. [External Article]( [Introduction to SQL Server sequence objects]( Additional Articles from SimpleTalk SQL Server sequence objects can be used in place of identity columns. In this article, Greg Larsen explains how to set up and use sequence objects. [Blog Post]( From the SQL Server Central Blogs - [Notes on SQLSaturday Orlando 2021]( Andy Warren from SQLAndy We held an in-person SQLSaturday here in Orlando last weekend (Oct 30th). We didn’t organize one last year, there was just too much risk and too much uncertainty, so... [Blog Post]( From the SQL Server Central Blogs - [Row-Level Security Basics–#SQLNewBlogger]( Steve Jones - SSC Editor from The Voice of the DBA Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. I realized recently that I hadn’t really blogged...   Question of the Day Today's question (by Steve Jones - SSC Editor):  The Priority Protocol Which protocol is always tried first from a client, if enabled? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Row-Level Security Predicates What types of security predicates are supported by row-level security in SQL Server/Azure SQL? Answer: filter and block Explanation: The two types of security predicates are filter and block. Filter predicates are for read operations and block predicates are for write operations. Ref: Row-Level Security - [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [optimize cost sort]( - hello , do you have any idea for optimize the sort object in my table I have only one clustered index in column id I created a nonclustered index on the dateInsertion column but I still have an index browse GO CREATE TABLE [dbo].[BLMachines]( [id] [int] IDENTITY(1,1) NOT NULL, [distinguishedName] [varchar](max) NULL, [samAccountName] [varchar](max) NULL, […] SQL Server 2016 - Administration [DBCC Checkdb cannot finish - Msg 8921, Level 16, State 1, Line 8 Check terminate]( - Hello We have this issue on one of our Prod databases. The database is fully operational but our DBCC checkdb job is failing with the error below: Msg 8921, Level 16, State 1, Line 8 Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. […] [alter partition function]( - Hello, I have a DB with partitions create partition function PF_IdEmpresas (smallint) AS range left for values (1,2,3,4,5,6,7,8,9,10,11,12,13) create partition scheme PS_BASE AS PARTITION PF_IdEmpresas to (BASE01,BASE02,BASE03,BASE04,BASE05,BASE06,BASE07, BASE08,BASE09,BASE10,BASE11,BASE12,BASE13,BASE14) In the filegroup BASE14 there are data with the values 14, 15 and 16 and I want to add a new filegroup, BASE17, for the data with […] Administration - SQL Server 2014 [How to distinguish human principals from all the rest in sys.server_principals ?]( - Hi all, If I run this query: select * from sys.server_principals where type = 'U' and name not like 'NT%' I get a listing of logins of both types: human which are our team-members, as well as like "...reports", "...developers", etc. I am tasked to collect a listing of only human members, but there is […] SQL Server 2019 - Administration [Missing 'sys.sp_MSIsContainedAGSession' causing failure in CU13 update script]( - After upgrading installing Cumulative Update 13 for Microsoft SQL Server 2019 (KB5005679), the server failed to start. Error logs showed that this was because the upgrade script msdb110_upgrade.sql attempts to run stored procedure 'sys.sp_MSIsContainedAGSession'. However, the server has no such stored procedure, nor do our other, non-upgraded instances have it. Error: 2812, Severity: 16, State: […] SQL Server 2019 - Development [Percent Rank]( - Hi Everyone I have an Access query that computes the percentile for a given set of values. The key is that the calculation is based on a rolling window of the past X days. For example, if calculated today, it will look at the last 30 periods (including today's date) and then determine the percentile […] [Output results to a table]( - Hi everyone One of my queries is running too slow so I would like to output the results of the subquery into a table and then use that table in the main query. Not sure if this will improve performance or not but it is worth a shot. I know it made a difference in […] [Reference Query within a Query]( - Hi everyone This is definitely a rookie question. In Access, I can reference another query by just using its name. I tried to do the same thing in SQL Server and I got an error. Here is my query: SELECT * FROM QRY_DATES The message I got was Msg 208, Level 16, State 1, Line […] [View Index]( - Hi everyone I am in the process of moving over Access queries to SQL Server. One Access query took close to 2 hours to run. Once moved over to SQL Server and using Columnstore Index, the same query took about 6 seconds. Wow! I have created a view to so I can reference it again. […] [Show Queries]( - Hi everyone I am moving from Access to SQL Server so I am learning more about SQL Server. In Access I can view the objects for my project (such as tables and queries). Is there a way to show the queries I have written in SQL Server so it shows up in the Object Explorer […] SQL Azure - Administration [Setting up Replication on a SQL Managed Instance]( - Hello, I have searched and found some articles on setting up replication in SQL MI between SQL Instances and going to on-premise but is there any article on how to setup just to replicate to a database on the same Managed Instance? I think the issue I am running into is with the setup\permission of […] Azure Data Factory [Removing specific rows in an Excel file using Azure Data Factory]( - I have a set of excel files inside ADLS. The format looks similar to the one below: The first 4 rows would always be the document header information and the last 3 will be 2 empty rows and the end of the document indicator. The number of rows for the employee information is indefinite. I […] Reporting Services [Reports upgraded to SSRS 2019 on WIN 2019 server is slower compared to SSRS 2008]( - We are upgrading SSRS at QBE from SSSR 2008 on Windows 2008 R2, to SSRS 2019 on Windows 2019 for the Report Server and Catalog DB . We’ve done the upgrade and have hit a re response time issues, where we have noticed degradation. Question: why is running the same reports (and fetching the parameters) […] Strategies and Ideas [Multiple sources for dimensions in Data Warehouse]( - I am currently working on a financial Risk data warehouse. For my collateral dimension, I am souring the data from one source system. However, after further research by the business analyst, we found a legacy application that also holds collateral information which the bank also needs in the data warehouse. Bar a few common attributes […] Integration Services [Excel file changing hourly]( - I have some excel files that got imported to a certain folder through ftp. this files are going to that folder hourly. every time an excel file goes into this certain folder it has all the accumulations of the excel files before, so only the most recent one matters. this basically means the moment i […]   [RSS Feed]([Twitter]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -