Using Registered Servers in SSMS (2024-07-31)

SQLServerCentral Newsletter for July 31, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Using Registered Servers in SSMS]( - [Getting Started with Azure CLI to Manage Azure Resources]( - [PASS Summit Futures Scholarship]( - [From the SQL Server Central Blogs - Terraform Elastic Jobs]( - [From the SQL Server Central Blogs - Are Your Secondary Availability Group Read Only Replicas Falling Behind? Are You Seeing PARALLEL_REDO_TRAN_TURN Waits?]( - [The Unicorn Project]( Question of the Day - [Managing Backup History Options in MSDB]( The Voice of the DBA  A Different View of Technical Debt Today we have a guest editorial from Grant as Steve is out of town. I’ve recently got my General license as a Ham radio operator. I’m working on getting ready to pass the exam for the Extra. Upon passing I’ll have access to all the frequencies open to amateur radio. The hobby is a lot of fun, but it’s also enlightening as a technologist. There are amazing new radios, especially around the SDR, or software-defined radio, space. There are also a ton of digital transmission mechanisms with more coming out all the time. However, there are also a number of operators working on old, vacuum tube radios (boat anchors is the affectionate term), some of them using Morse code (CW being the appropriate term). That’s right, hundred year old tech is still alive and well (OK, mostly well), side-by-side with the most advanced tech possible. All of which makes me think about technical debt. The concept is simple to understand. Choices you made when you designed and built a system may not have been perfect at the time. Over the years those choices begin to inflict more and more pain on you, hence, a debt you’ve built up and will have to pay off at some point. Frequently, refusing to upgrade to newer versions of software is lumped in to the technical debt discussion. However, I think we should be very wary of that. Like an old CW radio, SQL Server 2000 may be working perfectly well for some people. It’s not seen as technical debt, or even a problem, because it does everything they need and it does it well enough for them. Yeah, there can be all sorts of issues tied to this (and as a consultant, I don’t know that I’d ever advocate for staying on twenty year old technology), but it’s working. So, while I would personally still advocate to upgrade to newer versions of software (security, compliance, performance, the list of reasons why is long), I want to try to be understanding of why an organization may dig in their heels at moving to a newer version of SQL Server. Listening to people who’ve forgotten more about how radio waves operate than I’ll ever know, and their resistance to move to newer radios or digital communications mechanisms, I feel I’ve got a better understanding for exactly why not everyone is going to upgrade on the same schedule and some, may never upgrade. Grant Fritchey [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Using Registered Servers in SSMS]( Frank Dolan from SQLServerCentral One of the features in SQL Server Management Studio (SSMS) that I find very useful is to have a list of registered servers and databases in SSMS. This lets me quickly pick from a number of servers rather than flipping the drop down in the connection dialog. This post looks at this feature and how […] [External Article]( [Getting Started with Azure CLI to Manage Azure Resources]( Additional Articles from MSSQLTips.com Learn about Azure CLI and how to manage your Azure resources using commands instead of using the Azure portal. [Technical Article]( [PASS Summit Futures Scholarship]( Additional Articles from PASS In celebration of their 25th anniversary in 2024, Redgate, as the host of PASS Summit, is thrilled to introduce the PASS Summit Futures Scholarship. This initiative aims to empower the next generation of data professionals from diverse backgrounds. Applications are open internationally to students and early-career professionals. Each of the 10 lucky winners will be awarded: [Blog Post]( From the SQL Server Central Blogs - [Terraform Elastic Jobs]( hellosqlkitty from SQLKitty I did a couple of other posts on Elastic Jobs. But now I want to set them up with Terraform. This aligns with how we create infrastructure where I... From the SQL Server Central Blogs - [Are Your Secondary Availability Group Read Only Replicas Falling Behind? Are You Seeing PARALLEL_REDO_TRAN_TURN Waits?]( david.fowler 42596 from SQL Undercover Are you struggling with a laggy redo and a build up in the redo queue on your readonly secondaries? Are you suffering with high PARALLEL_REDO_TRAN_TURN waits? Then this magic... [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):  Managing Backup History Options in MSDB What are my options for managing the amount of data in the backup and restore tables in MSDB? 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) 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? Answer: Nothing Explanation: This returns nothing, as the NOT IN returns a NULL value. Since the value of NULL is unknown, the WHERE clause cannot determine if there is a match or not. Ref: IN - [  [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 - 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -