SQL Server 2012 AlwaysOn Groups and FCIs Part 3 (2023-04-12)

SQLServerCentral Newsletter for April 12, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [SQL Server 2012 AlwaysOn Groups and FCIs Part 3]( - [PASS Data Community Summit registrations are open!]( - [How to ask for programming help]( - [WIT Engagement Survey For Female Speakers]( - [From the SQL Server Central Blogs - Using SQL Alerts to Spot Suspicious Activity in SQL]( - [From the SQL Server Central Blogs - How to Change the Browser Used by SSMS for AAD Auth]( - [SQL Server Execution Plans, Third Edition, by Grant Fritchey]( Question of the Day - [Waitfor Threads]( The Voice of the DBA  Big Data Downsides Companies often want more data to help them make decisions on how they run their business. There has been this quest to gather and analyze as much data as possible to increase the efficiency of their operations to help reduce costs or increase profits. This has led to the importance of data as an asset, and the need for more data professionals in many organizations. That's good for many of us that work with data. However, using data to try and improve your efficiency has a downside. It can lead you to a very narrow focus in your approach. That can be good in narrow, well-defined areas, such as minimizing the distance driven or packing containers. For less focused tasks, such as telling a story or writing code, this can mean you get stuck in a rut and limit your opportunities to improve. There's [an interesting article about big data and Hollywood](, specifically looking at the types of products produced. Big data analysis leads companies to aim for the most effective types of movies that make money. Good for a company, not so good for society. Arguably, not even good for a company over time as people will tire of the same story, or type of story over time. Eventually, making simple decisions based on past data will start to fail. I can see the same thing in other industries as well. Using Big Data to drive decisions can help, but many of the areas where we use these techniques will evolve and change over time. The way we solve problems with code change over time as we develop new tools, techniques, platforms, languages, etc. There isn't a perfect way to design a database or write a CRUD app precisely because new capabilities or new possibilities emerge. You could say the same things about marketing, manufacturing, medicine, and many other endeavors. This isn't to imply big data and complex analysis isn't helpful or useful. It's just not everything. We need to balance human input, with some creativity, some instinct, some diverse thought, and some guessing. Most importantly, we ought to experiment and learn, not only from what machines might extrapolate, but from how humans change their thinking over time. Find a balance, accepting some imperfection in your process and in the world at large. Hopefully that will lead you to some success. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Technical Article]( [SQL Server 2012 AlwaysOn Groups and FCIs Part 3]( Perry Whittle from SQLServerCentral.com Part 3 of the AO and FCI integration series. This article gets SQL Server configured on the nodes and ready for work. [External Article]( [PASS Data Community Summit registrations are 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 to learn more. [External Article]( [How to ask for programming help]( Additional Articles from SimpleTalk Over the past 25 years, I have answered a lot of programming questions in online forums, from co-workers, and from friends. It has been a while since I had been around forums, but I recently decided it was time to get back to what started me in the SQL community: answering questions. Not only is it complementary to my current job as Simple-Talk editor, it is really a great joy to be able to help other people with their problems. It is also educational to see the kinds of things other people are going through before you also go through them. [Technical Article]( [WIT Engagement Survey For Female Speakers]( Additional Articles from SQLServerCentral This is a survey from us at dataplatformwit.com to understand why /how you, as a woman technologist are engaging with community. We ask that women in the data community please fill this out. From the SQL Server Central Blogs - [Using SQL Alerts to Spot Suspicious Activity in SQL]( david.fowler 42596 from SQL Undercover SQL doesn’t really give us too many tools out of the box to allow us to spot when someone may be up to no good. We can look at... [Blog Post]( From the SQL Server Central Blogs - [How to Change the Browser Used by SSMS for AAD Auth]( Meagan Longoria from Data Savvy Did you know that you can change the browser used by SQL Server Management Studio to authenticate using Azure Active Directory to a SQL database in Azure? I had... [SQL Server Execution Plans eBook, Third Edition, by Grant Fritchey]( [SQL Server Execution Plans, Third Edition, by Grant Fritchey]( Grant Fritchey from SQLServerCentral Every Database Administrator, developer, report writer, and anyone else who writes T-SQL to access SQL Server data, must understand how to read and interpret execution plans. This book leads you right from the basics of capturing plans, through how to interrupt them in their various forms, graphical or XML, and then how to use the information you find there to diagnose the most common causes of poor query performance.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Waitfor Threads I have some code that uses a WAITFOR in it. When a lot of people call this code, what happens with the threads associated with the calls? 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) OPENROWSET DNR This file in this code doesn't exist, but can I compile this code, with deferred name resolution working for OPENROWSET? CREATE PROCEDURE MyOpenRowSetTest4 AS BEGIN SELECT * FROM OPENROWSET(BULK 'd:\downloadedfiles\filethatdoesntexist.txt', SINGLE_CLOB) MyFile END Answer: This works fine Explanation: This works fine. The compilation process doesn't cause issues here. Ref: OPENROWSET - [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 - Administration [reads for DMVs]( - Hi All, I have created a test table with rowsize ~8K. I have inserted 2 rows and when I read data from the table, then it essentially has to show me 2 logical reads but it showing 6. why is it so? Am I missing anything? Second question is why sp_whoisactive is showing this spid […] SQL Server 2017 - Development [Tracking of Accounts data and Stored procedures]( - Please find my sample db here: I have a DB that I would like to track account movements(I created an "Accounts" table) I also want to track "AccountsCommisonHistory"(I also created this table). I created an "Accounts_daily_snapshot" and "CommisionGroups" table, but it contains no records as in the above tables. Where is the error in my […] SQL Server 2016 - Administration [Failover in Availability Group wipes data from sys.dm_db_index_usage_stats]( - Im using Brent's sp_blitzIndex results and storing them to prevent any loss during a restart. I found that when a failover occurs on a 2016 and 2019 AG that this table gets reset on the server that was Primary and is now a secondary. Is this expected? My assumption was that these tables only lost […] SQL Server 2016 - Development and T-SQL [How to add couple more conditions to the query]( - Good Morning,   Can you please advise how to add these 2 additional filters to the below query, i tried but didnt work.  1)All Colleges under municipal_ID 4269005 were excluded from the output (below query results). That MUNICIPALITY should still be included but we want to make sure the College ID 88 under […] [Excel style Contains filter]( - Hi, I am trying to do Excel style "contains" in SQL, but doesn't work. There are two forename fields in my two tables. I would like to know if first forename is "contained" in second forename field or vice-versa. The name can be anywhere in the other column. Basically identifying individuals who put in their […] Development - SQL Server 2014 [Bad data - updating date from new FK]( - Hi, Our DB vendor is now implementing a new FK. This new FK is not going in due to duplicates - The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.PractitionerInsurance' and the index name 'UX_PractitionerInsurance_PractitionerID_CarrierID_TypeID_Policy_EffectiveDate'. The duplicate key value is (43246, 96146, 13261, K1510, Aug 9 1988 […] SQL Server 2019 - Administration [Rebooting secondary replica of AG causes some databases to stop synchronizing]( - Rebooting secondary replicas should be non-impactful, but starting about 6 months ago we pretty consistently see a handful of databases that fail to synchronize post-reboot.  Background: Each of our SQL cluster nodes hosts 6 AGs, and each AG has around 50 DBs Definition of 'handful' - most recently, we rebooted 8 secondary nodes and had 15 […] [Database Backup Encryption & Recovery]( - What happens if a server "Dies" and a new server needs to be rebuilt, using encrypted backups ? If I create a master key CREATE MASTER KEY ENCRYPTION BY PASSWORD = '' Then create a certificate CREATE CERTIFICATE MyTestDBBackupEncryptCert WITH SUBJECT = 'MyTestDB Backup Encryption Certificate' Then backup my database WITH ENCRYPTION (ALGORITHM […] [SQL AlwaysOn configuration completely disappear]( - Hi, I've SQL 2019 STD with CU16 on Win 2019 STD. i had some ESXI host unresponsive for 5 sec and the SQL AlwaysOn become completely malfunction that both my SQL Server the databases that are part of the AlwaysOn are i the state of restoring. the "funny" thing is that the AlwaysOn configuration completely […] [I need to import v7r3 ibm i (as400) info to a sql 2019 database]( - I need to import as400 data into sql 2019 database. In Sql i can run a linked server to access the as400 data fine. When i look for the same data source to copy all the data to a database, i don't see the one i need, any help would be fantastic. SQL Server 2019 - Development [Inserting data into Physical Table on Azure Taking Longer Than Expected]( - We have an issue on Azure with inserting data into a physical table . We have done some benchmark testing and we are finding that inserting to a physical table on Azure, is taking 4 times longer compared to using a User Defined Table Type or Temp Table I wanted to know why this might […] SQL Azure - Administration [Need Suggestion: Does Blocking Has Affect On High DTU Utilization ?]( - Hi, Does blocking has affect on High DTU utilization ? (some people advocate that Spike in DTU might be caused by high number of blocking processes , or long term blocking) DTU definition select convert(decimal(18, 0),AVG([avg_DTU_percent])) from (SELECT (SELECT Max(v) FROM (VALUES (avg_cpu_percent), (avg_data_io_percent), (avg_log_write_percent)) AS value(v)) AS [avg_DTU_percent] FROM sys.dm_db_resource_stats WHERE end_time>=DATEADD(MINUTE, -10, GETUTCDATE())) […] General [SQL to set date/time period to 1600-1559 (dynamic)]( - Hi I have a View that shows orders entered into a system using GETDATE(). However, I need it to show all orders entered between 1600 and 1559 as the 24 hour period. This then displays in a spreadsheet  So at 1610 today it would just show anything entered since 1600 today And at 1559 […] SQL Server 2022 - Development [Domain Extension Splitting Based on Dot(.)]( - Hi All, I need to extract the domain extension from the column based on dot(.) like the below  [simple query question]( - hi all, I have a simple question: having a table where I have multiple orders for any number of vendors and a table with orders and amounts, how do I write the query that sums up the amounts by vendor. I tried: select vnd.VENDOR, sum(ord.Amount) as sumAmount from TBL_Vendor vnd inner join TBL_Orders ord on […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -