Restoring Master on a Windows 2019 / SQL Server 2019 Cluster (2021-04-08)

 SQLServerCentral Newsletter for April 8, 2021 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Restoring Master on a Windows 2019 / SQL Server 2019 Cluster]( - [Moving from application automation to true DevOps by including the database]( - [Five facts about the SQL Server Model Database]( - [From the SQL Server Central Blogs - VMware Improves In-Guest Time Synchronization with 7.0 U1]( - [From the SQL Server Central Blogs - Extended Events: Avoid the XML]( Question of the Day - [Killing Stats Updates]( The Voice of the DBA  Daily Coping Tip Appreciate something that went well in life 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. Poor Software Testing Due to a email sending issue, this editorial from last week is being resent in the newsletter. I am a big advocate of testing your code, including your database code. I like repeatable testing, especially unit testing. I think this has contributed to the increase in quality over the last ten years as more developers have incorporated unit testing into their work. That, along with the increase in standard frameworks, seems to have resulted in less crashes and instability in much of the software I used today. That being said, the client ultimately decides if software works as expected. Certainly there can be logic errors, but there also could be errors in how specifications and requests are interpreted. This is one reason we need humans to do some QA testing and clients to ensure there is some user acceptance review. Apparently [that didn't happen with one election machine](. There is a hash that is supposed to be used to verify that the correct version of software is installed. However, if that reference hash isn't there, the machine still reports things are fine. Certainly an issue that is a problem, though not necessarily one that users would detect. We would expect someone that purchases, updates, or administers these machines to check for the correct version. That isn't an issue here because the acceptance testing was done by the vendor. While I am a big advocate of developers checking their work, there needs to be independent evaluation by a CI process, and there ought to be some QA review by another group. Certainly a client ought to be able to double check the software as well, and some client should have done acceptance testing here. As the world moves more towards DevOps type software development, we need better testing, and that likely needs to include some independent testing outside of the developers and testers. I certainly could see the need for clients to submit some tests and get some verification that those tests passed. Automated CI/CD systems can do this, and provide detailed logs of what happened. Ultimately we may still have bugs, either because we don't have enough testing, or we don't quite write the code that does what client expects. We may also have performance issues, but that's another testing issue. We can get better, but we have to work to do so, learning from our mistakes, ensuring we are always improving testing, and listening to feedback. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Restoring Master on a Windows 2019 / SQL Server 2019 Cluster]( Johan Bijnens from SQLServerCentral Howto restore master database on a Clustered SQL Server 2019 Instance [Moving from application automation to true DevOps by including the database]( Additional Articles from Redgate This article explains the challenges of DevOps automation for databases, starting with how to manage the database, as a set of SQL scripts, in the version control system, and then how to start building an integrated and automated script pipeline for continuously testing and deploying database schema changes, alongside the application code. [Five facts about the SQL Server Model Database]( Additional Articles from MSSQLTips.com In this article we cover interesting facts about the SQL Server model database and how new databases inherit properties from the model database. From the SQL Server Central Blogs - [VMware Improves In-Guest Time Synchronization with 7.0 U1]( kleegeek from Technobabble by Klee from @kleegeek I just found out that VMware has updated their in-guest time synchronization with the 7.0 Update 1 release. Previously, we had to manually disable some of the advanced time... From the SQL Server Central Blogs - [Extended Events: Avoid the XML]( Grant Fritchey from The Scary DBA One story I hear over and over goes like this: I tried setting up Extended Events, but then I saw the output was XML so I stopped. Look, I...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Killing Stats Updates An asynchronous statistics update job is running in SQL Server. How can I kill this? 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) A New Array of Zeros I want to create a new numpy array that is 2x2, and initialized to zeros. How do I do this? import numpy a = Answer: numpy.zeros((2, 2)) Explanation: The zeros() function in the numpy module will do this. Ref: numpy.zeros - [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 [View using Cross Join and LIKE operator please suggest alternate syntax.]( - Hi, The below view is using cross join and as far as I know, Cross join is not good for performance. Can you please help me rewrite these views? Any suggestions are much appreciated on the below issues 1 & 2. FROM dbo.tbEmp er CROSS JOIN dbo.tbEmpType ect LEFT OUTER JOIN t ON t.Ece_ID = […] [Non clustered Index on #temp table]( - Hi, In order to avoid a SORT operation in my execution plan, I want to create a Non clustered Index on a Temp table. However I am getting the below error which makes sense since it is a temp table that is created on the fly, however, is there any other way? please advise. ERROR […] [how to properly uninstall SQL server 2008 ?]( - Hello , I will proceed to install SQL server 2016 after deleting SQL server 2008 properly I saw several programs that depend on SQL server all these propragrams must be uninstalled (Microsoft visual c++ 2008 redistrubutable , Microsoft SQL server CLR Type,SQL server Native client ...)  SQL Server 2016 - Development and T-SQL [Rounding to 0]( - How do I preserve the value (and not result to 0). In the below calculation, how do I preserve the value? I want atleast 0.01 to come out instead of 0. declare @q int declare @weight decimal(33,13) set @q = 6 set @weight = 0.0000000060240 select Sum(@q *COALESCE(NULLIF(COALESCE(@Weight, 0), 0), 0.01)) Result = 0.0000000  [How do I JOIN these records?]( - Hi, possible to give me a tip on how do I join these 2 tables? TABLE_A record must have the first nearest DateTime on TABLE_B Datetime record on B must be greater than or equal to A (top 1) Record on B can be matched with a record on A multiple times as long as […] SQL Server 2019 - Development [Convert teradata REGEXP_INSTR into SQL SERVER]( - Hi, I need some guidelines to convert Teradata REGEXP_INSTR logic into SQL server code. Please find the attached file in which I had provided a sample script. Appreciate your reply in advance. Thanks   [If Statement in Computed Column]( - Greetings, Thank You for taking the time to read and hopefully answer my question. You will soon see how new I am to SQL Server. I am rebuilding in SQL Server a DB that I built in MS Access. Several of the columns in one of the MS Access tables are calculated using IIF statements. […] [What to expect on first day of PL SQL at new firm?]( - Hi All, Looking for answer on what do I expect to happen on very first day for PL SQL job role, given 5 years of experience in resume. Because it will be completely new firm for me, will I receive some time to learn their systems?   Thank you The Future of Community [Forum Api Project: Setup #7 View Models]( - This script creates 2 procedures which return view models in JSON format. The view models return the posts for a thread in either recent view (plain old normal view) or threaded view (similar to old Drupal with nesting expanded). Both implement offset paging. set nocount off; /* determine if schema 'frm' already exits. If it […] [Forum Api Project: Setup #6 Example Posts (reply to Posts)]( - This script creates 7 posts in the 'frm.posts' table. These are "indirect responses" to thread(s) or replies to (other) posts. The variable declarations are repeated for each procedure execution (to step through 1 reply at a time). For the purpose of creating example posts the reply p_id's are located by the post title which is […] [Forum Api Project: Setup #5 Example Posts (reply to Threads)]( - This script creates 5 posts in the 'frm.posts' table. These are direct responses to threads. /* delete frm.posts; dbcc checkident ('frm.posts', reseed, 0) with no_infomsgs go */ set nocount on; set xact_abort on; begin transaction /*create proc frm.api_posts_post @t_id int, @u_id int, @title nvarchar(256), @body nvarchar(4000), @test_p_id bigint output, @test_msg varchar(2048) output*/ declare @t_id int=(select […] [Forum Api Project: Setup #4 Example Threads]( - This script creates 5 threads in the 'frm.threads' table. /* delete frm.threads; dbcc checkident ('frm.threads', reseed, 0) with no_infomsgs go */ set nocount on; set xact_abort on; begin transaction /*create proc frm.api_threads_post @f_id int, @u_id int, @title nvarchar(256), @body nvarchar(4000), @test_t_id int output, @test_msg varchar(2048) output*/ declare @f_id int=(select f_id from frm.forums where title='Title 4'), […] [Forum Api Project: Setup #3 Procedures]( - This script creates 3 stored procedures which create rows in the remaining 2 tables, 'frm.threads' and 'frm.posts' tables. The proc to create new threads is a plain insert. The proc to create new posts in response to threads is a plain insert. As far as I can tell there's 1 key query in the whole […] [Forum Api Project: Setup #2 Seed Data]( - This script inserts seed data into 5 of the 7 tables created by setup1. 7 users were created: id email username 1 admin@abc123.com SysAdmin 2 stevej@abc123.com SteveJ 3 stevec@abc123.com Steve Collins 4 moderator1@abc123.com Jose SQL 5 moderator2@abc123.com Jane5687 6 member1@abc123.com AdamF 7 member2@abc123.com Bean When 8 claims were created: cl_id securitystamp claim hierarchy 1 74FEAEBE-B13D-4759-9B4F-F63636F48E3F […] [Forum Api Project: Setup #1 DDL]( - This script creates 7 tables. The only requirement is there needs to be a schema available called 'frm'. If one exists already the script fails. If 'frm' is available for this purpose then the schema and tables are created.     [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -