Add a Second NIC for an Availability Group to Separate Network Traffic (2023-05-08)

SQLServerCentral Newsletter for May 8, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Add a Second NIC for an Availability Group to Separate Network Traffic]( - [Redgate opens the doors to cross-database DevOps Test Data Management]( - [Benefits and Limitations of SCHEMABINDING Views in SQL Server]( - [From the SQL Server Central Blogs - What Is Azure Data Factory?]( - [From the SQL Server Central Blogs - Code That Writes Code]( - [Practical Database Auditing for Microsoft SQL Server and Azure SQL: Troubleshooting, Regulatory Compliance, and Governance]( Question of the Day - [KILL permissions]( Featured Script - [Python 3 Script for Generating SQL Insert Statements from CSV Data]( The Voice of the DBA  Technical Debt We all experience technical debt when building software, especially software that has been around for years. Most of us have likely encountered code that we are loathe to touch and modify. At least, we don't touch it twice. We might change things once, but when it doesn't work as expected, most of us put it back in it's previous state and move on to something else. At the same time, it's hard to define what technical debt is or point it out to managers, who often can't grasp the concept of why technical debt matters to a development team. They can't understand the reasons why new code built on older code takes longer and longer to produce. It's a nebulous concept that can baffle both people new to software development and those with years of experience. I ran across a few articles that talk about technical debt. [This one]( notes it's commonly used as a phrase and also, it's inevitable. There's [a short video from Ward Cunningham]( who is said to be the inventor of the term. It's interesting because the explanation isn't bad code, it's more that we don't always understand the problem when we write code, especially at the beginning. As we have a disagreement between what's coded and what's needed, we accumulate debt. Ward talks about refactoring regularly as we gain understanding. There is [another piece that uses the Chernobyl disaster as a comparison]( with software development. This one looks at cost-cutting in both situations. The emphasis in software development is something [Jeff Moden]( will appreciate: developers not writing robust code. There is also a section on designing to meet only 95% (or less) of perceived use cases, which I think is just reality. We can't write software, in any practical sense, to cover 100% of use cases. The final section talks about incomplete testing. While software development has gotten better here, there is still plenty of work to be done, especially with database software. I find far too few database engineers wrap tests around their code and often have problems with new data or when code is refactored. A few more tests added early often prevent issues later, or at least, save time when debugging. This is one place I think AI might really help with labor savings by writing these tests for us. Technical debt is a reality of life. We have imperfect engineers, we have the pressures to get things done, the increasing complexity of software systems, and perhaps most of all, the pressure to keep moving forward with something new instead of refactoring something old. I don't have any great solutions, but I also do see software developers working better than they did in the past. That gives me hope for a future where software is embedded in more places and used more every day. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [Add a Second NIC for an Availability Group to Separate Network Traffic]( Vincent92 from SQLServerCentral Introduction Sometimes we face the scenario in an enterprise environment that the database in SQL Server Always On Availability Group (AOAG) has high concurrency read and write access from application servers. If we keep using the one network interface card for both network traffic of database connections from application servers and database mirroring between AOAG […] [External Article]( [Redgate opens the doors to cross-database DevOps Test Data Management]( Additional Articles from Redgate Redgate’s new DevOps test data management tool, Redgate Clone, provisions production-like data in seconds for SQL Server, PostgreSQL, MySQL and Oracle databases. [External Article]( [Benefits and Limitations of SCHEMABINDING Views in SQL Server]( Additional Articles from MSSQLTips.com The tip, Views in SQL Server, explored the purpose of views, creating views examples, and benefits of views. A view is a virtual table that references the actual database tables stored in the database. What if someone changes the underlying table structure, such as renaming the column, adding a new column, or dropping the table? What is the impact of changing schema on views? How can we stop any schema changes if the view references the schema? [Blog Post]( From the SQL Server Central Blogs - [What Is Azure Data Factory?]( dharmendra.keshari from SQL Geek What Is Azure Data Factory? Azure Data Factory (ADF) is a cloud-based PaaS data integration solution that provides a fully managed, serverless environment for ingesting, preparing, and transforming your... [Blog Post]( From the SQL Server Central Blogs - [Code That Writes Code]( Kevin3NF from Dallas DBAs “Work Smarter, not Harder” We’ve all heard it before, pretty much any job, anywhere. In our DBA slice of the IT world, this is very relevant to how we... [Practical Database Auditing for Microsoft SQL Server and Azure SQL]( [Practical Database Auditing for Microsoft SQL Server and Azure SQL: Troubleshooting, Regulatory Compliance, and Governance]( Site Owners from SQLServerCentral Know how to track changes and key events in your SQL Server databases in support of application troubleshooting, regulatory compliance, and governance. This book shows how to use key features in SQL Server ,such as SQL Server Audit and Extended Events, to track schema changes, permission changes, and changes to your data. You’ll even learn how to track queries run against specific tables in a database. ss   Question of the Day Today's question (by Steve Jones - SSC Editor):  KILL permissions What permissions are required to use KILL on a different connection? 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) SQL Server 2022 Log File IFI In SQL Server 2022, when can a log file use Instant File Initialization (IFI)? Answer: Only when the log file grows Explanation: IFI can be used with log file growth in SQL Server 2022. Only if the growth is 64MB or less (64MB is the default). Ref: What's new in SQL Server 2022 - Performance - [ [Discuss this question and answer on the forums](  Featured Script [Python 3 Script for Generating SQL Insert Statements from CSV Data]( Piyush Tripathi from SQLServerCentral This Python 3 script is designed to take CSV file data pasted into the csv_data variable and generate SQL insert statements that can be used to insert the data into a MySQL database. The script is easy to use and can save you a lot of time when working with large amounts of data. def add_quotes(row): values = row.split(",") output = ["'{}'".format(value) for value in values] return ",".join(output) def csv_to_mysql(table, csv_data): csv_array = csv_data.split("n") header_str = add_quotes(csv_array[1]) sql_statement = "" for row in csv_array[2:-1]: row_str = add_quotes(row) sql_statement += "INSERT INTO {}({}) VALUES({}); n".format(table, header_str, row_str) return sql_statement #---------- REPLACE THE BELOW TEXT WITH YOUR CSV DATA---------------- csv_data = """ CustomerID,CustomerName,ContactName,Address,City,PostalCode,Country 89,White Clover,Karl Jablonski,305 - 14th Ave. S. Suite 3B,Seattle,98128,USA 90,Kala Clover,Karl Jablonski,305 - 14th Ave. S. Suite 3B,Seattle,98128,USA 91,Karttunen Clover Markets,Karl Jablonski,305 ,Helsinki,21240,Finland """ sql = csv_to_mysql('Customers',csv_data) print(sql) print("Execution Finished!") [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 - Administration [Index Creation for large table failed.]( - I'm trying to create a clustered index on a very large heap table. Below is the script i'm using to perform the creation job. CREATE CLUSTERED INDEX [IX_Arc_tblFST_GSNID] ON [dbo].[tblFST] ( [GSN_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON, DATA_COMPRESSION=PAGE) ON [ps_tbl_arctbltnr]([TNR_Date]) However the creation […] [Always ON, HA, File Location Problem]( - Hello All, I have four VMs setup while I am learning SQL Server Admin tasks and configurations. SVR01 is the DC, the other three are the servers to be used. I am attempting to setup Failover clustering, however I run into this error every single time, no matter what folder I use, what name I […] SQL Server 2016 - Development and T-SQL [Extracting Data from XML file]( - Hi all, I have a XML file which consists this type of data: 12345 1 ABC XYZ TEST TYPE TEST REFNUM 2016-11-30 TESTCOMMENT TEST DESIGN TEST NAT TEST TYPE EXACT 1964-07-17 […] [How To Enumerate Hierarchical Info on SP calls]( - NOTE: Forgive me if I am using the wrong terminology with regards to this. \ I am trying to get a list of all The SP's (Stored Procedures) that are called by an SP. For example let's say I have the following SP's: LF5_Job1, LF5_JOb2, LF5_CalcResFees, F5_CalcCommFees & LF5_PopulatFees The SP LF5_Job contains one 'EXECUTE' […] SQL Server 2019 - Administration [Log File continue growing]( - Hello everyone, i read a lot about growing Log Files on this forum but can´t find a solution for my problem. I moved a database from one server to another, cause there were several performance problems on that old server. Since i moved the database the lfd file is continue growing. Database (mdf) file is […] [Generate 1 log file every 1 hour doing a backup every 1 hour?]( - EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = 'D:\DADOS-LOG', @BackupType = 'LOG', @LogSizeSinceLastLogBackup = 1, @TimeSinceLastLogBackup = 60, @DirectoryStructure = NULL, @FileName = '{DatabaseName}_{BackupType}_{Partial}_{CopyOnly}.{FileExtension}', @AvailabilityGroupDirectoryStructure = NULL -- How to modify this script to generate 1 log file every 1 hour doing a backup every 1 hour? [how to set to a variable]( - how can I make SELECT SERVERPROPERTY('MachineName') to set to a variable? Thanks, SQL Server 2019 - Development [Script to import BACPAC file?]( - Did some searching, and the closest I've seen is using the SqlPackage command-line tool, either directly from CMD or through a PowerShell script. I'm aware that in SSMS, you can do this by right-clicking and selecting "Import Data-tier Application". Is there any way to do this through a standard SQL script? Or is the only […] [select query]( - Hi, I would like to select the specific string from the field(Text_Loginame) value from a table. Below are the two records in that filed. Text_Loginame Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Could not find a login matching the name provided.[CLIENT: XXXXX] Login failed for user 'ABC\XXXX'. Reason: Failed to open the explicitly specified […] [Importing XML data into a Table]( - Hi, I am trying to write a script which will import fields from an XML file into a table. Below is the example XML Test company other Other 111111 […] [Grouping the words which has maximum count]( - Hi All, I have to highest two word in the columns. It should have the maximum two words combination. I have attached the excel for reference. General [SQL server loads slow troubleshoot common issues?]( - What are some best practices to identify and troubleshoot slow query performance issues on an SQL server, and what are some effective tools and techniques to resolve problems with server crashes or connectivity disruptions? How do you optimize SQL queries for performance, and what are some common pitfalls to avoid What are some advanced features […] Analysis Services [How can external customers access SSAS cubes to create their own analysis]( - My company has a number of SSAS tabular cubes which are deployed to a server and Power BI reports are built on top of that. We have lots of bespoke reporting requirements from external customers and are thinking it would better for them to do there own analysis and reporting as long as we can […] Strategies and Ideas [Injest user-provided Excel?]( - In today's episode of "Products that Should Exist (and maybe do)": Have you come across a solution (preferably a web-based portal) that allows an end user to upload an Excel file, digests the data, identifies tabular data, walks the user through column mapping to structured data, performs data validation, highlights invalid data, allows editing of […] Integration Services [SSIS package succeeding but Job failing (fail - converting date and/or time)]( - Hi, I successfully created an SSIS package and it executed perfectly in Visual Studio's development environment. However, when I scheduled this very package within a SQL Server Job Agent, it mysteriously fails: " Hresult: 0x80040E07 Description: "Conversion failed when converting date and/or time from character string.". End Error Error: 2023-05-04 13:33:28.19 Code: 0xC004701A Source: Load […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -