Getting Started With Azure Cosmos DB for PostgreSQL (2024-07-29)

SQLServerCentral Newsletter for July 29, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Getting Started With Azure Cosmos DB for PostgreSQL]( - [It’s a recovery strategy, not a backup strategy]( - [From the SQL Server Central Blogs - Calling a REST Endpoint from Azure SQL DB]( - [From the SQL Server Central Blogs - The Importance of Patch Management to Avoid Downtime]( - [Pro SQL Server 2022 Wait Statistics: A Practical Guide to Analyzing Performance in SQL Server and Azure SQL Database]( Question of the Day - [NOT IN Confusion]( Featured Script - [Comprehensive SQL Performance Monitoring and Optimisation Script]( The Voice of the DBA  A Quick Turnaround I visited a customer last week and attended [SQL Saturday Baton Rouge 2024](. Both were fun events, and an enjoyable week, though I was away from my wife for 4.5 days, which she didn't love. Today, I had a quick turnaround, heading to Wisconsin Dells for [THAT! Conference]( which I attended last year and enjoyed. I didn't submit this to the event, but got asked to go as part of my job. I accepted, and I'm gone from home for a week between these two trips. I do travel a lot, but these trips got me thinking about how many of us might handle the unexpected demands from our companies. In this case, I had planned on SQL Saturday Baton Rouge, but not a customer visit. I got asked a month ago to add this in, which was fine, but two weeks ago the trip got extended by another day. Just before I got the update from that call, I agreed to go to THAT! for a quick presentation. As a developer, I've been given last-minute work. It might have been a bug that was discovered or a new request that was high priority. In those cases, I've often had to work more hours for a short period, usually long days or even weekends. I've lost personal time because of the hours, but also the stress, as my mind is elsewhere. As an Operations person, I sometimes get stuck at work overnight or for long parts of many days. In most of my jobs, I've been a strong performer and had a good relationship with management. I've almost always been able to negotiate comp time for the extra hours spent. In some cases, I've been paid for them, but usually, there is the equivalent of shorter days for a period or even missing some days of work. Often this is off-the-books as most HR systems don't cope well with this. For those of you who get unexpected demands, how do you handle things? Do you get something back from the company? Time, days, compensation, maybe even a thank you and a gift? It's not something that I think is universal, but I'd like to think it's common. And if you're at THAT, say hello to me this week. I don't know when I'll get some shorter days, but likely they'll come in the next month as I try to catch up on ranch work. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Getting Started With Azure Cosmos DB for PostgreSQL]( Shivayan Mukherjee from SQLServerCentral This article shows how to get started with the PostgreSQL API in Azure Cosmos DB. [External Article]( [It’s a recovery strategy, not a backup strategy]( Additional Articles from SimpleTalk I’ve talked about it before; you shouldn’t have a backup strategy, you should have a recovery strategy. [Blog Post]( From the SQL Server Central Blogs - [Calling a REST Endpoint from Azure SQL DB]( Meagan Longoria from Data Savvy External REST endpoint invocation in Azure SQL DB went GA in August 2023. Whereas before, we might have needed an intermediate technology to make a REST call to an... [Blog Post]( From the SQL Server Central Blogs - [The Importance of Patch Management to Avoid Downtime]( david.bermingham from Clustering for Mere Mortals A recent Microsoft outage caused by a bad patch pushed out to Windows instances managed by CrowdStrike has thrown a spotlight on the critical importance of effective patch management.... [Pro SQL Server 2022 Wait Statistics: A Practical Guide to Analyzing Performance in SQL Server and Azure SQL Database]( Site Owners from SQLServerCentral Use this practical guide to analyze and troubleshoot SQL Server performance using wait statistics. You'll learn to identify precisely why your queries are running slowly. And you'll know how to measure the amount of time consumed by each bottleneck so you can focus attention on making the largest improvements first. This edition is updated to cover analysis of wait statistics current with SQL Server 2022. Whether you are new to wait statistics, or already familiar with them, this book provides a deeper understanding on how wait statistics are generated and what they mean for your SQL Server instance’s performance.   Question of the Day Today's question (by Steve Jones - SSC Editor):  NOT IN Confusion I have these two tables with a few rows of data: CREATE TABLE dbo.Person ( PersonID INT IDENTITY(0, 1) , PersonName VARCHAR(20)); GO CREATE TABLE dbo.Candidates (PersonName VARCHAR(20)); GO INSERT INTO dbo.Person (PersonName) VALUES ('Billy'), ('Joe'), (NULL); INSERT INTO dbo.Candidates (PersonName) VALUES ('Billy') , ('Mandy') , ('Me') GO I want to get the candidates that are not in the Person table. I run this: SELECT * FROM dbo.Candidates AS c WHERE PersonName NOT IN ( SELECT PersonName FROM dbo.Person ); What is returned on SQL Server 2019? 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) Internal Checkpoints Certain internal SQL Server actions cause internal checkpoints. Which of these actions does not cause an internal checkpoint? Answer: An Extended Event session is started for the ring buffer Explanation: These actions cause an internal checkpoint: - Database files have been added or removed by using ALTER DATABASE. - A database backup is taken. - A database snapshot is created, whether explicitly or internally for DBCC CHECKDB. - An activity requiring a database shutdown is performed. For example, AUTO_CLOSE is ON and the last user connection to the database is closed, or a database option change is made that requires a restart of the database. - An instance of SQL Server is stopped by stopping the SQL Server (MSSQLSERVER) service. This action causes a checkpoint in each database in the instance of SQL Server. - Bringing a SQL Server failover cluster instance (FCI) offline. Ref: Internal Checkpoints - [ [Discuss this question and answer on the forums](  Featured Script [Comprehensive SQL Performance Monitoring and Optimisation Script]( Jonathan AC Roberts from SQLServerCentral This script monitors and reports the execution statistics of SQL statements over a specified period, capturing metrics such as execution counts, CPU time, I/O operations, and elapsed time. It provides a comprehensive view of query performance, aiding in the diagnosis of performance issues and identifying problematic SQL queries in the database. -- ************************************************************************************************* -- Author j.roberts -- Date 11 July 2024 -- Reports costs/statistics of SQL statements for given time on given database -- ************************************************************************************************* GO DECLARE @Database sysname = 'MyDb', -- Set the database name @MonitorPeriod varchar(10) = '01:00:00' -- 1 hour -- set this to the time period you want to monitor for BEGIN SET NOCOUNT ON; SET ARITHABORT OFF; SET ANSI_WARNINGS OFF; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; DROP TABLE IF EXISTS #CombinedResults; CREATE TABLE #CombinedResults ( DBName sysname, QueryText varchar(MAX), Execution_count bigint, total_cpu_time bigint, total_IO bigint, total_physical_reads bigint, total_logical_reads bigint, total_logical_writes bigint, total_elapsed_time bigint, Identifier tinyint ,plan_handle varbinary(64) not null ,last_execution_time datetime ); DECLARE @Iteration tinyint = 1; WHILE @Iteration <= 2 BEGIN ;WITH PlanStats AS ( SELECT st.dbid, st.text AS QueryText, cp.plan_handle, MAX(cp.usecounts) AS Execution_count, SUM(qs.total_worker_time) AS total_cpu_time, SUM(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) AS total_IO, SUM(qs.total_physical_reads) AS total_physical_reads, SUM(qs.total_logical_reads) AS total_logical_reads, SUM(qs.total_logical_writes) AS total_logical_writes, SUM(qs.total_elapsed_time) AS total_elapsed_time, MAX(qs.last_execution_time) AS last_execution_time FROM sys.dm_exec_cached_plans cp INNER JOIN sys.dm_exec_query_stats qs ON cp.plan_handle = qs.plan_handle CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st WHERE DB_NAME(st.dbid) IS NOT NULL AND DB_NAME(st.dbid) LIKE @Database GROUP BY st.dbid, st.text, cp.plan_handle ) INSERT INTO #CombinedResults SELECT DB_NAME(ps.dbid) AS DBName, ps.QueryText, SUM(ps.Execution_count) AS Execution_count, SUM(ps.total_cpu_time) AS total_cpu_time, SUM(ps.total_IO) AS total_IO, SUM(ps.total_physical_reads) AS total_physical_reads, SUM(ps.total_logical_reads) AS total_logical_reads, SUM(ps.total_logical_writes) AS total_logical_writes, SUM(ps.total_elapsed_time) AS total_elapsed_time, @Iteration AS Identifier ,ps.plan_handle ,max(ps.last_execution_time) FROM PlanStats ps GROUP BY ps.dbid, ps.QueryText, plan_handle; -- Wait for the specified monitoring period during the first iteration IF @Iteration = 1 BEGIN WAITFOR DELAY @MonitorPeriod; END SET @Iteration = @Iteration + 1; END -- ************************************************************************************************ -- Results -- ************************************************************************************************ SELECT ISNULL(A.DBName, B.DBName) AS DBName, ISNULL(A.QueryText, B.QueryText) AS QueryText, ISNULL(A.Execution_count, 0) - ISNULL(B.Execution_count, 0) AS Execution_count, ISNULL(A.total_cpu_time, 0) - ISNULL(B.total_cpu_time, 0) AS total_cpu_time, (ISNULL(A.total_cpu_time,0) - ISNULL(B.total_cpu_time, 0)) / NULLIF(ISNULL(A.Execution_count,0) - ISNULL(B.Execution_count, 0), 0) AS avg_cpu_time, ISNULL(A.total_IO, 0) - ISNULL(B.total_IO, 0) AS total_IO, (ISNULL(A.total_IO,0) - ISNULL(B.total_IO, 0)) / NULLIF(ISNULL(A.execution_count,0) - ISNULL(B.execution_count, 0), 0) AS avg_total_IO, ISNULL(A.total_physical_reads, 0) - ISNULL(B.total_physical_reads, 0) AS total_physical_reads, (ISNULL(A.total_physical_reads,0) - ISNULL(B.total_physical_reads, 0)) / NULLIF(ISNULL(A.execution_count,0) - ISNULL(B.execution_count, 0), 0) AS avg_physical_read, ISNULL(A.total_logical_reads, 0) - ISNULL(B.total_logical_reads, 0) AS total_logical_reads, (ISNULL(A.total_logical_reads, 0) - ISNULL(B.total_logical_reads, 0)) / NULLIF(ISNULL(A.execution_count,0) - ISNULL(B.execution_count, 0), 0) AS avg_logical_read, ISNULL(A.total_logical_writes, 0) - ISNULL(B.total_logical_writes, 0) AS total_logical_writes, (ISNULL(A.total_logical_writes, 0) - ISNULL(B.total_logical_writes, 0)) / NULLIF(ISNULL(A.execution_count,0) - ISNULL(B.execution_count, 0), 0) AS avg_logical_writes, ISNULL(A.total_elapsed_time, 0) - ISNULL(B.total_elapsed_time, 0) AS total_elapsed_time, (ISNULL(A.total_elapsed_time, 0) - ISNULL(B.total_elapsed_time, 0)) / NULLIF(ISNULL(A.execution_count, 0) - ISNULL(B.execution_count, 0), 0) AS avg_elapsed_time, ISNULL(A.last_execution_time, A.last_execution_time) last_execution_time FROM (SELECT * FROM #CombinedResults WHERE Identifier = 2) A LEFT JOIN (SELECT * FROM #CombinedResults WHERE Identifier = 1) B ON A.DBName = B.DBName AND A.QueryText = B.QueryText AND A.plan_handle = B.plan_handle WHERE ISNULL(A.Execution_count, 0) - ISNULL(B.Execution_count, 0) <> 0 ORDER BY 4 DESC; DROP TABLE #CombinedResults; END GO [More »](  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 - Development and T-SQL [Deadlocks with UPDATE statements using serializable transaction isolation level]( - We are seeing frequent deadlocks occurring due to a particular stored procedure that is using the SERIALIZABLE transaction isolation level. The stored procedure is essentially trying to ensure that the same reference number (concatenated from multiple fields) is never returned more than once. CREATE PROCEDURE dbo.sp_GenerateNextNumber ( @SequenceKey nvarchar(10), @ReferenceNumber nvarchar(25) = NULL OUTPUT ) […] [Error while Simultaneous Switching and BCP in same table]( - Hi, Our application is created in VC++. We have House keeping process in which the data from main table A is transferred to another table B using Switching method(yes both table has partition). the data in table A is inserted using BCP. The records get successfully inserted. Now when the insertion process is going on […] SQL 2012 - General [Behaviour since moving to Azure]( - Hello, all, Recently we have migrated a few AGs up to Azure. These are fairly old SQL 2012 servers, running in a 2-node cluster. We've been getting sporadic occurrences of WSFC errors, 41000 and 41005. Seems to a resource issue according the those error messages. The nodes are well-resourced and basically replicate what they were […] SQL Server 2019 - Administration [Unable to add the database back into AG]( - I took the full backup of the DB Backup database MyDB to disk = 'D:\backup\MyDB_Full.bak' with compression, copy_only, stats = 5 Restored on the secondary with replace. Restore was successful. When I tried to add the DB into AG, it threw an error. the mirror database has insufficient transaction log data to preserve. I then […] [AG-Group primary during reboot]( - New to ag groups. I have a 2 node aggroup. We are doing maintenance tonight and plan to reboot the secondary and have it come up and then do the primary. I am wondering what happens when the primary is rebooted with out triggering a failover to the secondary?  Does the listener move to […] [Sessions and CPU Threads]( - Hello everyone, I've got a question when it comes to best practices about application sessions in SQL Server. When your company developers write code and create applications to connect to SQL Server, and these sessions despite being connected to the SQL Instance for a few days ("connect time" from sys.dm_exec_sessions) is (2024-07-01 01:00:00) but the […] SQL Server 2019 - Development [error in both ssis and ssms - something about losing connections - new error]( - hi for about 4 or 5 days now, i've been seeing various connections (to our dw server) issues in ssis (excel to sql) under vs 2022 AND SSMS. the ssis error is shown below. in ssms it looks like this ...  The connection is broken and recovery is not possible. The connection is marked […] [How do I get a certificate for a SQL Server database?]( - I'm working on a new application at home, using SQL Server 2019 Developer Edition. I'm trying to scaffold a DBContext to my local database, but am getting this error: A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain […] [Summarize the proportions stored in the table]( - CREATE TABLE dbo.tab1 ( idrow INT NOT null IDENTITY, group1 VARCHAR(10) NOT NULL, c1 NUMERIC(10,2) NOT NULL, c2 NUMERIC(10,2) NOT NULL ) GO INSERT INTO tab1 ( group1, --group c1, --numerator c2 --denominator ) VALUES ('1', 1, 2), ('1', 2, 1.4), ('1', 3, 5.2), ('2', 1, 0.6), ('2', 4, 0.5), ('2', 3, 0.9) to store […] [Sum Nested Join Help]( - Hello all, I have a query where I am trying to do a left join to gather a sum and this one seems not simple. Here is what I have: SELECT C.CustomerId, C.TargetQty, C.GroupId, C.AcctCd FROM Customer C This is what I am needing to do and need the help. I have another table called […] SQL Azure - Administration [Copy Logins from on prem to Managed Instance]( - Hello, I try to migrate SQL on Prem Instances to SQL Managed Instances. I'm using the DBA Tools Start-dbaMigration. Everything is working, expect the migration of the Logins. I get the error: "Domain\User is a Windows Login, not spported on a SQL Managed Instance" We Sync the Users to the Azure with AD Connect so […] SQL Server 2022 - Administration [Use Polybase with ODBC to create external table]( - I'm trying to use the installed Polybase service on an SQL 2019 server to create an external table by using and ODBC DSN. The connection of the DSN is to a fairly exotic BBj server that hosts 3 databases. Somehow I just do not seem to get the proper syntax for creating the external table. […] SQL Server 2022 - Development [Issues adding and updating a column]( - Hi all  I'm hoping someone will able to say "you're an idiot because....." on this one.  We download a database but we have to add a column to a table and then update it. The code to add/update is as follows: IF NOT EXISTS ( SELECT * FROM UK_Health_Dimensions_New.INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_SCHEMA= 'ODS' […] [how can vs see SSIS under my regular user id but not my admin?]( - Hi, we run vs 2022.  I'm stumped how when i run VS as admin i cant see ssis after hitting create new project unless i want to import ssis or tabular.  but under my regular id i can see new ssis, import ssis, import tabular and new ssrs after hitting crate new project.  its been […] [SSIS execute powershell fails with exit code 1]( - I'm trying to execute a simple, test powershell script (which works fine when I right-click it and run with powershell, .ps1 file) from SSIS. My ExecuteProcess task has these parameters: Executable: PowerShell.exe Arguments: -F "D:\Users\Folder\Working Files\Teton Information\TestPowershellScript.ps1" I get back exit code 1, failure. What am I doing wrong ?? I've tried it with and […]   [RSS Feed]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -