Revolutionizing Real-Time Data Management with SQL Server, Kafka, and Informatica (2023-04-26)

SQLServerCentral Newsletter for April 26, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Revolutionizing Real-Time Data Management with SQL Server, Kafka, and Informatica.]( - [PASS Data Community Summit registration is open!]( - [Azure Machine Learning Introduction: Part 1 Overview and prep work]( - [From the SQL Server Central Blogs - I’ve Got 99 Problems But xp_cmdshell Ain’t One]( - [From the SQL Server Central Blogs - Video – Recovering data with crash consistent snapshots]( - [SQL Server 2022 Query Performance Tuning: Troubleshoot and Optimize Query Performance]( Question of the Day - [Stopping Managed Instance]( The Voice of the DBA  Creating Documentation Who should create documentation for software? In many companies, it's the developers. In fact, in Redgate, often our developers are tasked with updating articles for products on [our documentation site](). We do have a streamlined process that has developers can submitting changes in some format (markdown? ) and an automation process that automatically updates the site as part of a release. However, we also have marketing people who are in charge of external articles and communications. They ensure that our [product learning]( and [university](content is updated. They work with technical people to produce, edit, and publish content. These could be developers, advocates such as [Grant]( or [Ryan](, or even partners. Our [Friends of Redgate](also help with content. Who should write and update documentation? Developers might best know how software changes, especially for new features. I know that developers also hate this chore, and I think they often don't have the best perspective because the documentation should explain how I use things, not how I built them or just how I expect them to work. Developers sometimes just want to document how they expect the software to work. At the same time, marketing or some other area might not be aware of changes, necessitating more handoffs from the development teams. That can be a bottleneck in a DevOps flow. Of course, some of this process could be automated, like sending release notes to marketing. However, there is also the need for someone technical to review changes, which can require more resources. It could also mean marketing people ask developers to review changes, taking up their time anyway. If developers are like me, then they might just want to handle it themselves than communicate changes and still have to review someone else's writeup. There probably isn't a hard and fast rule. Not everyone communicate the same, or even that well. Writing is a skill, and while I might ensure all developers work on this skill, I might also try to lean more on those people that communicate well to ensure docs are updated for our clients. If you are building internal software, perhaps you forgo documentation entirely. I could see developers having a meeting with either QA staff or perhaps a business person to teach them what is changing. Then this person gets tasked with training others on the software rather than ensuring any formal documentation exists. Tribal knowledge might be the best solution here. If you're a developer, would you want to control documentation or avoid it like the plague? Let me know today in the comments. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Revolutionizing Real-Time Data Management with SQL Server, Kafka, and Informatica]( kpeddireddy from SQLServerCentral This article examines how one can structure a pipeline for processing real-time data using Kafka and Informatica. [External Article]( [PASS Data Community Summit registration is open!]( Additional Articles from Redgate In 2023, connect, share & learn with like-minded peers, speakers, and industry leaders during the full week of data celebrations. Summit happens in person, from November 14th to 17th in Seattle. Check out the blog post and learn more. [External Article]( [Azure Machine Learning Introduction: Part 1 Overview and prep work]( Additional Articles from SimpleTalk The five-part series is designed to jump-start any IT professional’s journey in the fascinating world of Data Science with Azure Machine Learning (Azure ML). Readers don’t need prior knowledge of Data Science, Machine Learning, Statistics, or Azure to begin this adventure. From the SQL Server Central Blogs - [I’ve Got 99 Problems But xp_cmdshell Ain’t One]( david.fowler 42596 from SQL Undercover xp_cmdshell is an extended SQL stored proc that allows users to run Windows command prompt commands from within SQL. Sound scary? It might, but is xp_cmdshell really a security... [Blog Post]( From the SQL Server Central Blogs - [Video – Recovering data with crash consistent snapshots]( dbafromthecold@gmail.com from The DBA Who Came In From The Cold A while back I posted a blog on how to recover data with crash consistent snapshots. Snapshots are pretty handy in certain situations so I thought I’d show you... [SQL Server 2022 Query Performance Tuning]( [SQL Server 2022 Query Performance Tuning: Troubleshoot and Optimize Query Performance]( Additional Articles from SQLServerCentral Troubleshoot slow-performing queries and make them run faster. Database administrators and SQL developers are constantly under pressure to provide more speed. This new edition has been redesigned and rewritten from scratch based on the last 15 years of learning, knowledge, and experience accumulated by the author. The book Includes expanded information on using extended events, automatic execution plan correction, and other advanced features now available in SQL Server.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Stopping Managed Instance If I stop an Azure SQL Managed Instance, what am I still charged for in my billing? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Tonie Huizer) Summing a BIT column with a numeric as the outcome I created a table like this: CREATE TABLE NewTable (record BIT) GO I have this query to run: SELECT record FROM dbo.NewTable AS nt GO The software using the outcome of the code would always expect a numeric value. What SELECT will meet this requirement? Answer: SELECT ISNULL(SUM(CAST(record AS INT)), 0) Explanation: The only SELECT that will work is: SELECT ISNULL(SUM(CAST(record AS INT)), 0) This will produce a numeric, even if there is an empty table or a NULL. Ref: - ISNULL - - CAST - [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 2016 - Administration [System versioned tables permissions]( - I have an issue with these permissions which I don't understand. When I add a user to a database containing some system versioned tables and look at their effective permissions, using SSMS, on tables I get the following: Ordinary tables - No permissions which is what I would expect System versioned history tables - An […] [Index]( - I have Index Maintenance Job I have stand alone secondary server when I run on secondary server it takes only 10 minutes to complete When I run in Production it takes 2 hours to finish I copied the same DB to secondary stand alone server and still it takes 2 hours to complete but server […] SQL Server 2016 - Development and T-SQL [Extracting just the text in parenthesis]( - Stand down. Found the solution Administration - SQL Server 2014 [Database Mail Using TLS 1.2]( - Hi, Has anyone had any experience in changing Database mail to use TLS 1.2....that would be enabling the Checkbox to use SSL in the mail configuration. I have added the registry settings as per a number of sources. To clarify do I need to restart Server, services or just agent. One source said agent would […] SQL Server 2019 - Administration [sql_variant to varchar is not allowed]( - Getting the following error: Implicit conversion from data type sql_variant to varchar is not allowed. Use the CONVERT function to run this query. ==== Table LogonInfo has the following columns: Spid int UserName nvarchar LogonDate datetime ClientNetAddress varchar Hostname nvarchar I have the following trigger to capture/insert into the table above: CREATE OR ALTER TRIGGER […] SQL Server 2019 - Development [partitioning a table]( - Hi Team , I want to partition a vey huge table . It has approximately 2109955648 rows . I am planning to partition the tables based on day_id as this column is used to filter the data by all the queries . Day_id is nothing but the month in which we had performed the calculation […] [Error during Restore: Msg 15247, Level 16, State 1, Procedure sys.sp_MSrepl_chec]( - My Backup/Restore job is failing - only when the DB is involved in REPL. Looking to see if anyone has a resolution: So this works fine: Create transactional PUB1 (all tables, views, procs) on Server1 DB1 Create SUB1 on Server 2 (populate DB1 on Server2) pulling from PUB1 on Server1 We now have req'mnt to […] SQL Server 2008 - General [Code Correction]( - The underneath code works on higher sql versions(2016 etc) but throws an error on 2008R2. Error:Msg 102, Level 15, State 1, Line 19 Incorrect syntax near '('. Thanks  SET NOCOUNT ON declare @tmp table(name nvarchar(500),Status int) insert into @tmp SELECT name,0 as completed FROM sys.databases WHERE (state_desc = 'ONLINE') DECLARE @dbname nvarchar(500) DECLARE @cmd […] Working with Oracle [Log shipping or Sychornization for Oracle vs SQL server]( - I would like to ask someone working on both Oracle and SQL server. For oracle, we have a script on a standby server that restores the archive log every 5 hrs from a production server ServerA to a report readonly server ServerB I see in the script that runs from synchronizing job which is scheduled […] [Log shipping or Sychornization for Oracle vs SQL server]( - I may click twice, delete this duplicated one Reporting Services [Open Report for editing using Report Builder 15 or below]( - Want to open SSRS report for edit using Report Builder 15 or below using JQuery function. Using this JQuery Function : tree.on('click', '.edit-report', function (event) { var dataPath = $(this).attr('data-Path'); window.location = dataPath; // Open the URL in a new window //window.open(url); event.preventDefault(); }); data-Path="@(string.Format("{0}/ReportBuilder/ReportBuilder_15_0_0_0.application?{1}", Settings.ReportServerUrl, item.Path.Replace(" ", "+")))" It is redirecting to report Manager. […] [Error configuring SSRS. The database engine instance is not valid.]( - Hi, This is a strange one. The error indicates that the database engine instance is not valid, but according to the documentation it should be valid. Does anyone have any ideas why this is occurring? Thanks in advance. Error: The database engine instance you selected is not valid for this edition of report server. Current […] General [SPN Help Needed]( - We have a .NET web application on a web server [MyWebSvr] and the database for the application is on another server [MySQLSvr]. The application is running fine and has been for years but the domain admin who setup the SPN's and delegation left the company years ago and did not document how the SPN's and […] [i need to automate some functions]( - hi all, i have to compare 9 times same scheme of table but each table keeps data for different device. my purpose is to list all the tables and then with cursor to build my queries.the need is to check if the table contains null values in all fields. i run one query to count […] SQL Server 2022 - Administration [Just joined but can't change sa or my new sql login]( - Trying to connect from sql express 2022 to the azure data studio and to a template in microsoft InfoPath. I'm pretty sure i have had it working once with azure but what  i can't get to happen is i can't get the default sa administrator password to update with a new one. It accepts new […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -