SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Subqueries II

The Voice of the DBA

Distributed Monoliths

I was watching a video called Microservices are Technical Debt. In it, the person being interviewed said that a lot of people really have a distributed monolith. That caught my eye since I've worked with a number of customers who are trying to adopt microservice architectures for their applications. I think this is less a performance/scaling choice than a reworking of their software development teams, and I'm not sure they will end up with a better system.

What is a distributed monolith? I am not an expert, but this appears to be a place where all the services still depend on each other. For example, I might have a service getting user profile info that an app calls, with another service getting previous orders, and a third service that returns inventory. In a monolith, if any of these are down, the others don't work. In a distributed monolith, these might be built independently, but perhaps the core app/web page still requires all these to be working to show the user something.

That's how my gym app seems to work sometimes. It can't get my profile and the list of classes and the things I've registered for, so it hangs. This is very annoying when all I really need is my ID QR code, which never changes, and is validated by another system.

In a microservices architecture, if any of these were down, the others would still be working, and the app would continue functioning. If inventory is down, I can still update my profile. If previous orders are working, I can view them even if the current inventory doesn't work.

Things are loosely coupled for the end user and for the development teams. Not for just one of those groups.

I'm not sold on microservices, especially when they include databases. There is a lot of power, and efficiency, from being able to pull together different data sets in a database efficiently rahter than doing so in an application, which might require different API calls, separate db connections, and the delays that come with returning more data than needed to let the client join it together. At the same time, I can see how separate services could potentially scale much higher over time.

The thing that I tend to see is that most of us never need to really scale as high as the Google/Facebook/Spotify/Netflix/etc. organizations that espouse these microservice architectures. They have different problem domains and different needs than most of us. Even Uber has realized that one size doesn't fit all and is moving some things from microservices to macro ones. I'm not against microservices, but they need to be used in appropriate situations, not all of them.

What most of us need is a little more help from Brent or Erik or Paul or someone else in the form of a little consulting that tunes our code to run more efficiently. And if we use them, we should learn a few things from them so that we write better code the next time.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

Featured Contents

JSON in PostgreSQL

Shivayan Mukherjee from SQLServerCentral

Learn how to work with JSON in PostgreSQL with this article.

Calculate a Moving Average with T-SQL Windowing Functions

Additional Articles from MSSQLTips.com

Learn how to create a moving average in SQL Server using older and less performant T-SQL and a modern way using windowing functions.

From the SQL Server Central Blogs - Microsoft Ignite Announcements Nov 2024

James Serra from James Serra's Blog

Announced at Microsoft Ignite last week were some new product features related to the data platform and AI. Check out the Major announcements and Book of News. Below are...

From the SQL Server Central Blogs - Episode 11 of Simple Talks: Oracle

Steve Jones - SSC Editor from The Voice of the DBA

The 11th episode is now live, recorded a few weeks ago at the PASS Data Community Summit. This was a lot of fun, us grabbing a space in the...

The Unicorn Project

Site Owners from SQLServerCentral

In The Unicorn Project, we follow Maxine, a senior lead developer and architect, as she is exiled to the Phoenix Project, to the horror of her friends and colleagues, as punishment for contributing to a payroll outage. She tries to survive in what feels like a heartless and uncaring bureaucracy and to work within a system where no one can get anything done without endless committees, paperwork, and approvals.

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Subqueries II
What is wrong (if anything) with this code? SELECT * FROM Sales.SalesOrderHeader AS soh WHERE customerid IN (SELECT soh.CustomerID FROM Sales.Customer AS c WHERE soh.CurrencyRateID = 1 ORDER BY c.ModifiedDate)
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)

More Funny SELECTs

What does this code return?

SELECT
  ( SELECT COUNT (*), MAX(soh.OrderDate) AS latestorder
    FROM Sales.SalesOrderHeader AS soh
    WHERE
      soh.OrderDate     > '01/01/2011'
      AND soh.OrderDate < '01/01/2012') AS OrdersIn2000
, ( SELECT COUNT (*), MAX(soh.OrderDate) AS latestorder
    FROM Sales.SalesOrderHeader AS soh
    WHERE
      soh.OrderDate     > '01/01/2012'
      AND soh.OrderDate < '01/01/2013') AS OrdersIn2001
, ( SELECT COUNT (*), MAX(soh.OrderDate) AS latestorder
    FROM Sales.SalesOrderHeader AS soh
    WHERE
      soh.OrderDate     > '01/01/2013'
      AND soh.OrderDate < '01/01/2014') AS OrdersIn2002;
GO

Answer: An error

Explanation: This returns an error. The error is: Msg 116, Level 16, State 1, Line 6 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. This is actually returned 3 times, one for each subquery. A subquery cannot return multiple results unless this is used with an EXISTS or IN. Ref: Subqueries - https://learn.microsoft.com/en-us/sql/relational-databases/performance/subqueries?view=sql-server-ver16

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

Active and Active Cluster - Hi Team, I am planning to apply security updates for SQL Server 2016 on Active/Active Cluster servers. Could you please advise on the proper procedures to follow? Specifically, which server should I apply the updates to first, and when should I reboot the servers? Your guidance would be greatly appreciated. Thanks

Tracing a performance issue - Edit: This is a duplicate of another post.

Tracing a performance issue - Hello, We have a SQL agent job that runs daily. It runs a procedure, which for the purposes of this post, I will refer to as 'Procedure A'. That procedure runs other procedures. Every few months we get an issue where it appears to have stalled. Task manager is showing the SQL Server process at […]

SQL Server 2016 - Development and T-SQL

identify consecutive records greater than 1 - Hello I need help identifying all records that have consecutive hours (time in order) of greater than 1 for the value field. The output I am looking for would have Number,Start Time,End Time, Average of val over that time range that is identified. create table LunchTable ( Number integer, Value decimal(15,2), StartTime datetime ); insert […]

Import data from json file with special characters - I am using the following script to import data from a json file. This file contains special characters for some names. Once imported, I noticed that sql server is replacing the special characters with some other ones. Is there away to import special characters without changing them? Any help is greatly appreciated. CREATE TABLE test3 […]

Administration - SQL Server 2014

SSIS package works in VS but failed in SSMS - Please help and thanks. Package:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.".

SQL Server 2019 - Administration

SQL Server migration using replication - I've set up replication in my SQL 2019 environment in attempt to migrate SQL databases individually from one datacenter to another. in my testing of one database several issues were found and any advice would be appreciated. 1. There several missing indexes 2. very large tables cannot be filtered on for transaction replication(post snapshot migration) […]

Automating SQL Server 2019 Setup and Configuration Across Multiple Servers - Hi everyone, I’m looking to automate the installation and configuration of SQL Server 2019 on multiple servers in our environment. So far, I’ve been using tools like PowerShell scripting using scriptrunner, attune, pdqdeploy and dbatools for some tasks, but I’m curious if anyone has a full end-to-end solution they recommend. My main goals are: Automating […]

SQL Server 2019 - Development

Need a list of stored procedures that execute multiple stored procedures - Hello SSC! I hope you all had a happy and safe holiday! Apologies if this post is confusing... I have a bunch of procs that call an index rebuild proc multiple times based on column name. Basically, this is all hardcoded (I did not write this :)). I tried a bunch of scripts to identify […]

SQL Azure - Administration

Creating LS from IaaS to Paas using MFA - Guys - I need your help on this.... I like to create an linked server from an IaaS (VM) to a PaaS managed instance but when I heve to loging to the MI using MFA I have to respond using mij GSM ( entering a code to confirm it is me ). How can I […]

Integration Services

Call dynamic sql storedprocedure from SSIS execute sql task - hi, I have a table called Rules Create table Rules ( Id int , Rules Statement Nvarcahr(max) ) values Id RulesStatement 1 Sp_execute_rules @job_id , @run_id,@createid So , i will be passing the parameters from my ssis package and calling the storedprocedure. this is how i am calling in execute sql task […]

SQL Server 2022 - Administration

Migrating database with many orphan users. - I am currently upgrading a very old database running SQL Server 2008 to SQL Server 2022 using export & import method to a new server. I have migrated the necessary objects e.g. logins, linked servers & configuration to the new server successfully. For logins, I use revlogin procedure to script out all the logins from […]

Primary and Secondary Filegroup - Hello, I am having database primary and secondary filegroup. Few tables/indexes are partitioned on secondary filegroup for better performance. But when I check activity monitor, I always see MDF & LDF files having response times under the Data Fil I/O but there is always 0 response time for NDF. Is this normal behaviour of MSSQL? […]

SQL Server 2022 - Development

Cannot install development version. - A while into install I get a Microsoft OLE DB Driver for SQL Server. The feature you are trying to use is on a network resource that is unavailable. It is looking for msoledbsql.msi. I have one locally but than I get a message: The file 'D:\msoledbsql.msi' is not a valid installation package for the […]

sql query to check status change of an item - I have a table dbo.tblPresentationStatus (sql script attached - table script.txt) I have to select rows where the status change is wrong. (not as per the correct flow as shown in attached image status_Flow.jpg) From 'Review' the status for a specific presentation ID can change to either 'Approve' or 'Presentation' or 'Close' From 'Approve' the […]

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.