Stairway to AlwaysOn Level 4: Analyse and Deploy a Windows Server Failover Cluster (2020-01-08)

 SQLServerCentral Newsletter for January 8, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to AlwaysOn Level 4: Analyse and Deploy a Windows Server Failover Cluster]( - [Portable Objects in PowerShell with CLIXML]( - [Free eBook: SQL Server Execution Plans, Third Edition]( - [From the SQL Server Central Blogs - [Cross post] Innovating Yourself as an IS Auditor]( - [From the SQL Server Central Blogs - Azure – BGP Community for Application Insights (Need Votes)]( Question of the Day - [Cleaning Up NA Values]( [Redgate Database Devops]( The Voice of the DBA  A Problem with Cowboy Coding As we implement more automation and complexity in our systems, we increase the ability of every worker to accomplish more during their day. In many cases, this is a good thing, as many of us have no shortage of tasks to complete. Being able to do more is good. Some technology workers, and likely some managers, view these changes as opportunities to use less people to do the same work. That can be worrisome for many people, especially those concerned with job security. That's a difficult situation to find one's self in. I'm not sure if this worker was concerned about losing employment or just wanted to get more work, but a contractor put logic bombs in his project that would cause issues at various times. He would then be called to fix issues, getting paid for his efforts. Recently [he was sentenced to prison time]( for his actions. Most workers make an effort to do good work and provide value for their time and knowledge. They may still make mistakes or cause problems, but it's often unintentional, not malicious. We also don't look to create future problems for others, though certainly we may accrue technical debt over time as we make trade-offs in our work. Whether intentional or accidental, we want to limit mistakes and issues in our code. This is one reason that modern development in a DevOps environment isn't a wild west, [cowboy style of work](. We use code reviews and continuous improvement to help other developers learn to write better code. We use automated processes with instrumentation and logging to ensure we know what code is deployed when and by whom. We may move faster with DevOps, but it's often safer, more secure, and more accountable than previous methods of building software. It doesn't prevent mistakes, but every part of the process should be more transparent and auditable, which is good for everyone involved. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums]( [Redgate University](   Featured Contents [Stairway to AlwaysOn]( [Stairway to AlwaysOn Level 4: Analyse and Deploy a Windows Server Failover Cluster]( Perry Whittle from SQLServerCentral.com In level 4 of the stairway to AlwaysOn we look at creating a Windows Server Failover Cluster. [Portable Objects in PowerShell with CLIXML]( Additional Articles from SimpleTalk PowerShell is a widely used tool for administrator... [Free eBook: SQL Server Execution Plans, Third Edition]( Press Release from Redgate If a query is performing poorly, and you can't understand why, then that query's execution plan will tell you not only what data set is coming back, but also what SQL Server did, and in what order, to get that data. It will reveal how the data was retrieved, and from which tables and indexes, what types of joins were used, at what point filtering, sorting and aggregation occurred, and a whole lot more. These details will often highlight the likely source of any problem. From the SQL Server Central Blogs - [[Cross post] Innovating Yourself as an IS Auditor]( K. Brian Kelley from Databases – Infrastructure – Security This is also posted to the ISACA Journal blog, Practically Speaking: As new technologies are developed, we have to stay up to date with them. More so than almost... From the SQL Server Central Blogs - [Azure – BGP Community for Application Insights (Need Votes)]( K. Brian Kelley from Databases – Infrastructure – Security Working with Microsoft, we determined that there is no BGP community for Azure’s Application Insights. As a result, I’ve created a feedback request for Microsoft to consider doing just...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Cleaning Up NA Values I have a data frame that has data from the first week of our fantasy football playoffs. > fantasy.playoffs Team Score Opp 1 Way0utwest Cowboys 135.40 2 SSC Ravens 66.26 Green Machine 3 Orange Engineers 85.20 4 Mexicanos 111.52 Mexicanos I want to extract out the rows with in them. What function can I use? 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) Changing Algorithms I create a symmetric key in a SQL Server 2016 database. I use this code: CREATE SYMMETRIC KEY SalaryKey WITH ALGORITHM = AES_256 , IDENTITY_VALUE = 'Salary Protection' , KEY_SOURCE = 'Protect this from hackers' ENCRYPTION BY PASSWORD = 'SomeReallyStr0ngP@ssword'; GO I then use this to encrypt some data in a table: OPEN SYMMETRIC KEY SalaryKey DECRYPTION BY PASSWORD = 'SomeReallyStr0ngP@ssword' UPDATE dbo.Employees SET EncryptedSalary = ENCRYPTBYKEY(KEY_GUID('SalaryKey'), CAST(Salary AS VARCHAR(50))) GO I now transfer this data to a SQL Server 2017 database, into the same structure table. Now I run this: CREATE SYMMETRIC KEY SalaryKey WITH ALGORITHM = AES_256 , IDENTITY_VALUE = 'Salary Protection' , KEY_SOURCE = 'Protect this from hackers' ENCRYPTION BY PASSWORD = 'SomeReallyStr0ngP@ssword'; GO OPEN SYMMETRIC KEY SalaryKey DECRYPTION BY PASSWORD = 'SomeReallyStr0ngP@ssword' SELECT top 10 e.EmpID , e.EmpSSN , e.Salary , CAST(DECRYPTBYKEY(e.EncryptedSalary) AS VARCHAR(50)) AS DecryptedSalary , e.EncryptedSalary FROM dbo.Employees AS e GO What is returned in the DecryptedSalary column? Answer: I get back NULL because the encryption process changed in SQL Server 2017 Explanation: The encryption process actually changed in SQL Server 2017. The encryption algorithm for the data is the same, but the way that the key values are hashed changed from SHA1 to SHA2. This means the data encrypted with symmetric keys need to be decrypted and re-encrypted with the new key when you change from a version prior to SQL Server 2017 to one later. Ref:  - FIX: SQL Server 2017 cannot decrypt data encrypted by earlier versions of SQL Server by using the same symmetric key - - [Migrating Column Level Encryption from 2008R2 to Azure SQL Database]( - [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 - Development [Query tweak]( - Is there any way to tweak the following query? Thanks ; WITH CTE AS ( SELECT DATEPART(QUARTER, GETDATE()) as 'Quarter', DATEPART(YEAR, GETDATE()) as 'Year' ) select Quarter,Year from CTE where Quarter IN (SELECT Quater from Quaters) AND Year IN (SELECT Year from Quaters) SQL Server 2016 - Administration [Kerberos / SQL Service Accounts - Cluster]( - Hi, In single server setups I have always used a single domain account for SQL services and set up the relevant SPNs, specifically for IIS "double-hop" issues. I'm now supporting an existing 2016 cluster which has used different domain accounts for Agent and DB Engine services. Interestingly SPNs have only been setup for the domain […] [Listener timeout]( - Hello folks and happy new year! . I have a lab here at work and i ran into this issue regarding the Listener. This is an Always On Availability group lab i have and everything is going fine but there's an issue when connecting through the Listener. These are 2 SQL server 2016 EE […] [always on issue]( - After rebooting the server we are unable to see the always on group in secondary and databases are in sync in primary AG, we did not find any errors in always on dashboard or in event viewr. how can we bring the always on group again on secondary.   SQL Server 2016 - Development and T-SQL [Error converting data type varchar to numeric.]( - , a.sku + p1.SHORT_DESC + ' ' + p1.LONG_DESC1 AS short_description sku is numeric the descriptions are nvarchar It works until I add the sku to the front then I get Error converting data type varchar to numeric. Any help is appreciated. [persisted computed column error]( - I'm trying to create a persisted computed column to make a distinct clause maybe a little faster here is my code ALTER TABLE xxx ADD groupset AS ( master_id+ company_code+FileLocation_FileBeginsWith +ISNULL(despatch_group,'') +CONVERT(varchar(100),ISNULL(despatch_group_start_date,'01/01/1900')) +CONVERT(varchar(100),ISNULL(despatch_group_end_date,'01/01/1900')) )PERSISTED but i'm getting an error that the column cannot be created as one or more of the columns is not deterministic […] Administration - SQL Server 2014 [Unable to attach database]( - I need to restore a sql database into a NEW database as I need just one table from it to update another. I have the backup of the mdf file. I've tried unsuccessfully to attach, point to the mdf file, change the Attach as to Test, then remove the log from the bottom but it […] Development - SQL Server 2014 [convert FOR XML as text]( - hi, I would like to convert the result of a SELECT FOR XML query into a text field to be able to manipulate the result in my development language. if there is only one level for my xml. No problem. I use convert or cast as text and it's ok. But if i have more […] SQL Server 2012 - T-SQL [Partition by and count]( - I'm trying to write a query that will count (N) all members within a specific city but without repeating the name of the city (N) times... any ideas to what is semi-working now?: SELECT city, COUNT(PeopleID) OVER(PARTITION BY city) AS CountOfCities FROM gcdf_DB..PeopleContactInfo SQL Server 2019 - Administration [Setting flexible security for reporting]( - I have an increasing number of reports distributed to users. Managing the security is providing challenge. Reporting code is stored in a repository database on the same server the production DB. A user needs to have EXECUTE on stored procedures in the repository DB, though stored procedures that call SP's in the production DB, and the […] [error while install sql server 2019]( - hi,  while install microsoft machine learning components, i have error: there is a problem with this windows installer package . a program run as part of the setup did not finish as expected.contact your support personnel or package vendor thanks for any help. SQL Server 2008 - General [Can't open Alert in SSMS - Cannot show requested dialog.]( - When I try to view the properties of an existing alert of try to use the ssms to add a new alert, I get the following error; Cannot show requested dialog. Additional information: Cannot show the requested dialog (SqlMgmt) --Failed to create/initialize Agent Alert dialog. (SqlManagerUI) ----Cannot create/initialize Response page. (SqlmanagerUI) --------Object reference not set […] [Code to extract data]( - Hi I'm trying to add some code into a query I have written which extracts the data and saves it in a certain file path on my network. Does anyone know a)if any code exists and b)what the structure of that code is?? Thanks in advance [Adding ranges to an existing partion function]( - I currently have the below code for partition scheme and function. Is the ALTER PARTITION SPLIT Range the best way to add additional ranges to the function? CREATE PARTITION SCHEME [ps_My_Rent] AS PARTITION [pfn_My_Rent] TO ([DB_DPARTITIONS_2012], [DB_DPARTITIONS_2013], [DB_DPARTITIONS_2014], [DB_DPARTITIONS_2015], [DB_DPARTITIONS_2016], [DB_DPARTITIONS_2017], [DB_DPARTITIONS_2018], [DB_DPARTITIONS_2019]) GO CREATE PARTITION FUNCTION [pfn_My_Rent](DATETIME) AS RANGE LEFT FOR VALUES (N'2012-01-01T00:00:00.000', N'2013-01-01T00:00:00.000', […] Discussions about Books [Hi, having problem getting require result for exam]( - Hi, having problem getting require result for exam raul.chavez28 at yahoo dot com   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -