Stairway to SQL Server Replication: Level 3 - Transactional Replication – The Publisher (2023-01-04)

SQLServerCentral Newsletter for January 4, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to SQL Server Replication: Level 3 - Transactional Replication – The Publisher]( - [Setting up for Team-based Database Development using Flyway Desktop]( - [Express.js or Next.js for your Backend Project]( - [From the SQL Server Central Blogs - Understanding Keys and Certificates with TDE]( - [From the SQL Server Central Blogs - Monitoring with the Pure Storage FlashArray OpenMetrics Exporter]( Question of the Day - [Power BI REST Data]( The Voice of the DBA  Daily Coping Tip Share something helpful with a friend that you learned recently 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 Tech That is Gone The world of technology has undergone an amazing revolution in my lifetime. Things that I thought were incredible in my youth have come and gone in the world. I'm sure that there were dramatic changes in technology across lots of other generations, but I wonder if they were as dramatic for others as they feel for me. As I watch older movies, I'm amazed at the evolutions. I was watching an older sitcom recently and saw someone using a cordless phone. While we have these cordless mobile devices, there was a time when having a phone in my house that wasn't tethered to a spot on the wall was incredible. And I haven't seen one of these in years. My Mother worked in real estate and getting an answering machine was critical technology for growing her business. These were essential in every home for a long time (along with call waiting). First tape-based, then SSD, but now these devices are really gone and exist in the cloud. Now you get voice mail and call waiting on a mobile device. Does anyone have an answering machine anymore? For that matter, are there still public phones? I've seen a few booths in my travels, but they seem to be mostly standing as tourist attractions for pictures or repurposed as small libraries or wi-fi hotspots. One of my big career steps was working with an import company that lived off faxes. When I was there, we went from a single computer-based fax service to upgraded multi-line fax add-in boards with not only digital fax reception but the OCR of the actual recipient to email the images to the appropriate department. It was an amazing job where I learned a lot about imaging and large-scale storage of data. I know there are companies that still have fax machines, but emailing images around seems to have overtaken this in much of the world. We still have mainframes and lots of older applications, but one piece of software I expected to see around was Lotus Notes. I know some people might still use it, but [IBM doesn't support it](. They've outsourced. I guess a lot of software I used early in my career is gone: Ami Pro, WordPerfect, Lotus 1-2-3, and more. Maybe one interesting device my wife loved was a Palm Pilot. There were lots of PDAs for a while, but the advances in mobile phones completely did away with that category of devices. I'm actually still amazed that Blackberry couldn't survive the transition to screen-based devices. For a time it felt like everyone in business carried one around. Hard disks and tape changers seem to be fading, though I'm sure someone still uses them. Anyone [bought a tape drive]( for their personal or business use anytime in the last few years? Or for that matter, do you still use CDs or floppy disks anymore? VHS tapes? Dot-matrix printers? What about a modem? The [sound of a modem]( connecting was both amazing for me as a young man and an annoying sound early in my career. Same for pagers. I loved getting one but hated carrying it. While there might be these devices in use somewhere, for the most part this technology has been removed from our lives. Perhaps one of my favorite devices, which came late and died quickly, was the iPod. I had a few digital music players, but they were hard to manage. The iPod was revolutionary, and I had quite a few over the years. I still have a waterproof Shuffle I use when I swim, but for the most part, [these devices are just memories](. What tech do you miss, or remember fondly? Or perhaps, what old tech do you still work with that you enjoy? Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to SQL Server Replication]( [Stairway to SQL Server Replication: Level 3 - Transactional Replication – The Publisher]( Sebastian Meine from SQLServerCentral.com The Publisher is the database where all replicated data originates. Each Publisher can have multiple publications defined within it. Each publication contains a set of articles that all need to reside in a single database. Each article corresponds to all or part of a single database object. A single database object can map to an article in more than one publication. [External Article]( [Setting up for Team-based Database Development using Flyway Desktop]( Additional Articles from Redgate This article provides a simple demonstration of how a small team of developers might set up a Flyway Desktop project to manage, automate, and control database development. [External Article]( [Express.js or Next.js for your Backend Project]( Additional Articles from SimpleTalk In this article, I will introduce two JavaScript frameworks that can be used to build backend projects. [Blog Post]( From the SQL Server Central Blogs - [Understanding Keys and Certificates with TDE]( Matthew McGiffen from Matthew McGiffen DBA When you first look at the encryption hierarchy for TDE in SQL Server it can be a bit daunting. There seem to be a lot of objects involved and... [Blog Post]( From the SQL Server Central Blogs - [Monitoring with the Pure Storage FlashArray OpenMetrics Exporter]( aen from Anthony Nocentino Blog This post introduces you to the Pure Storage FlashArray OpenMetrics Exporter. It shows you how to get started quickly using Docker Compose so you can monitor your Pure Storage...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Power BI REST Data How can I consume data from a REST API (no credentials required) in Power BI? 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) Kicking off 2023 Which one kicks off the new year? Answer: SELECT CAST(0x07E7 AS INT) Explanation: This is easy to calculate: SELECT (16 * 16 * 7) + (16 * 14) + 7 Happy New Year! [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 - Development [A question about stored procedures and Transactions]( - I have been trying go get an answer to this question for sometime now. It has to do with transaction handling and stored procedures. I can best explain with an example. Suppose I have two stored procedures: Insert_TableA and Insert_TableB. One inserts to TableA and the other to TableB. There are times when I need […] SQL Server 2016 - Administration [Any way to store successful logins in a different event log?]( - Hello experts, I am running into an issue where log information in the Windows Application event log gets rotated out too quickly. Our Systems team tells me this is because the SQL Server in question is set to log both successful and failed logins. And it is true that I see tons and tons of […] SQL Server 2016 - Development and T-SQL [Group by producing different results]( - So, I have two sets of code which should ideally produce the same results. The first set of code works with just Group by on 'ts_originationopportunity' SELECT ts_originationopportunity, STRING_AGG(distinctreferrals.referralcompanycontact,'; ') AS referralcompanycontacts FROM (SELECT DISTINCT ts_originationopportunity, ISNULL(ts_referralcontactname + ' at ','') + ts_referralcompanyname AS referralcompanycontact FROM ts_referralsource) distinctreferrals GROUP BY ts_originationopportunity However, the second set […] [Code Modification - with Sub Query]( - Hi Community, Can someone show me how to modify this code without the use of Sub-Queries. SELECT activityid, concat_ws(ts_inititals, ', ') AS attendees FROM (SELECT activityparty.activityid, systemuser.systemuserid, systemuser.ts_inititals FROM baseorigination.activityparty LEFT JOIN baseorigination.systemuser ON activityparty.partyid = systemuser.systemuserid GROUP BY activityparty.activityid, systemuser.systemuserid, systemuser.ts_inititals ) attendees GROUP BY activityid Pleaes let me know if you need sample […] [Remove Issuance and Expiry dates]( - Hi everyone, I have a sql table in which data looks like this: I have to modify two columns (natid and passport) data so that it would only show the actualy natid and passport numbers. For e.g natid and passport of ABC1 would show ID-123 and PASS-456 when using select query. issuance and expiry dates […] [Does a Partition Switch to an empty table appear as tranlog delete?]( - i have a table that has been partition by month and year. the table has CDC tracking enabled on it, and a program called HVR is being used to read the transaction logs for tables using CDC for changes. it sends the changes to parquet files, where we can consume that data elsewhere. if we […] Development - SQL Server 2014 [Trouble with date values]( - SQL just goes and goes and returns no records if I un-comment this date line: AND cp.from_service_date BETWEEN d.eff_from AND ISNULL(d.eff_thru,'12/31/9999') FROM --#baseFilter jBaseClaim --INNER JOIN dbo.claim_procedure cp ON cp.claim_procedure_id = jBaseClaim.claim_procedure_id dbo.claim_procedure cp INNER JOIN dbo.claim c ON c.claim_id = cp.claim_id INNER JOIN dbo.claim_procedure_2 cp2 ON cp2.claim_procedure_id = cp.claim_procedure_id INNER JOIN dbo.type_of_service t ON […] SQL Server 2019 - Administration [DB Comp. Level 110 and Cardinality Version 150 in [sys].[query_store_plan]]( - I am using Query Store in SQL Server 2019 to evaluate the performance impact of upgrading the compatibility level of a database from 110 (SQL 2012) to 150 (SQL 2019). As part of this exercise I frequently alternate between the two comp. levels, from 110 to 150 and vice versa, and looking at query performance […] [Can we configure read scale between 2 fcis?]( - Suppose I have 2 WSFC clusters, each having 1 FCI (2-node + 1 FileShare). Fci1 (node1 & node 2) Fci2 (node 3 &node 4) There is a database on fci1 which I want to place on fci2 in near real time sync. It looks like read scale ag is the solution for me ( Does […] SQL Server 2019 - Development [Update query generation]( - Hi All, I am trying to generate MYSQL query by using this , But not getting correct result. Can you help. CREATE TABLE tbl1 (col1 int,col2 int,col3 int,col4 int,col5 int) go CREATE TABLE t_column_details ( [TABLE_SCHEMA] [varchar](500) NULL, [TABLE_NAME] [varchar](500) NULL, [COLUMN_NAME] [varchar](500) NULL, [length] [bigint] NULL ) go insert into t_column_details values ('dbo','tbl1','col1',2) […] [string_agg truncation issue (EDIT: no issue, just a brain not working)]( - I'm using string_agg to develop a long string to be used in some dynamic sql.  I'm using nvarchar(max), but it continues to truncate. This the code I'm using. It gets me a result with a length of 1920 characters. (Note: I have noticed I'm adding an extra "or" on the end of my code here, […] Amazon AWS and other cloud vendors [I accumulated $60 on MSK serverless over a few days]( - Just a rant really. I'm used to lambda, DynamoDB etc, but got access to sandbox account at work and wanted to learn Apache Kafka. Turns out serverless version is not scaling from $0 like most, but it's actually more expensive than a single small instance. I left it running over the weekend and lo and […] Analysis Services [Visual Studio and SSAS]( - I have a question regarding Visual Studio and SSAS. I created a "Analysis Services Tabular Project" in VSS. Connected to the SQL database and got the required tables and was able to publish to SSAS server. It created the required Database and tables on SSAS. My question is, I am going to have multiple people […] SQL Server 2022 - Development [Is there another way to solve this "group in 5 day spans" problem?]( - I ran into an interesting problem over on StackOverflow at the following link... To summarize, here's the data the op posted with 1 row removed because removing that row broke the "Accepted" solution. DROP TABLE IF EXISTS #MyHead; GO SELECT * INTO #MyHead FROM (VALUES (1 ,'2017-11-24 09:45:00.000') --These should be in Grp #1. […] [Best table structure for a tall table that needs to be pivoted]( - Hi Everyone, I have a table with about 2 million distinct IDs and another table with about 7000 distinct codes plus a varchar(02) flag. I did a simple cross join to create a table with just over 14 billion records. To be clearer each distinct ID will have the same codes. I will update the […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -