SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Using Recommendations for MAXDOP and Max Memory During Installation
Getting Started with Delta Lake Using Azure Data Factory
From the SQL Server Central Blogs - Creating Azure SQL Database AAD Contained Database Users with an SPN using PowerShell, Secrets Management, Azure Key Vault, and dbatools
From the SQL Server Central Blogs - Fun with Power BI and Color Math

Question of the Day

Bypassing Dynamic Data Masking

The Voice of the DBA

Daily Coping Tip

Plan a future project or gathering in your local community

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.

The Evolution of Windows

I like Windows 10. I've used every Windows version since 3.1, abandoning Windows 98, Windows ME, and Windows 8. That last versions I used very briefly, didn't like it, and quickly returned to the previous version. I thought Windows 7 was amazing, using less hardware resources for the first time ever, and I've enjoyed Windows 10 since I upgraded. In fact, I think at this point I've built 4 or 5 different Windows 10 machines from scratch.

I don't change hardware, but Windows 10 has evolved, essentially growing without changing versions. It had its 5th anniversary recently, and I was surprised to see a few articles like this one, that all talked about Windows 10 not being a success. News to me, since I've been happy with it.

It's interesting that the article talked about some of the promises of Windows 10 not being fulfilled. I don't know about the "free" aspect of it, though I do think Cortana was a bit of a failure. I didn't use it much, but I think the always listening and the issues with Siri/Alexa/etc. for speech platforms is an issue. I don't know many people that aren't geeks using Cortana, but perhaps this just isn't a useful thing in a non-phone OS? There are a few other things, but you'll need to read the article for yourself.

What I've noticed is that the evolution of the OS has faded a bit into the background. I do worry about updates, but I worry about lots of software updates. While quality has improved, the chance of being down is always disconcerting. While I haven't had any Windows 10 update issues across my 3 machines, or even any Android updates across the last 3 phones in 4-5 years, I still worry.

I also think the evolution of adding the Windows Subsystem for Linux is cool. I don't run a lot of native Linux stuff, but I do run Linux containers for SQL Server and I SSH into systems once in awhile, so I appreciate the native tools rather than some Windows port.

I do think we may get to the point where SQL Server versions fade a bit, with patches adding features, but I don't know we completely get away from versions. I think there's too much revenue from major upgrades, and since customers rarely want to make that investment, both for financial and time resources, means that I think we'll continue to get new major versions of SQL Server periodically, unlike Windows. The one thing I'd like to see if SQL Server stop evolving the database structure, and allowing the restore of a SQL Server 2021 database on a SQL Server 2019 instance, assuming no new keywords are used.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

Featured Contents

Using Recommendations for MAXDOP and Max Memory During Installation

iLearnSQL from SQLServerCentral

Finally, two important SQL Server recommended settings have been introduced during the installation process. These are the settings that every DBA (should) change after every single installation. The settings are MaxDOP and Max Memory. This article looks at each of these settings and how they are presented during installation. MaxDOP When an instance of SQL […]

Getting Started with Delta Lake Using Azure Data Factory

Additional Articles from MSSQLTips.com

This article will demonstrate how to get started with Delta Lake using Azure Data Factory's new Delta Lake connector through examples of how to create, insert, update, and delete in a Delta Lake.

From the SQL Server Central Blogs - Creating Azure SQL Database AAD Contained Database Users with an SPN using PowerShell, Secrets Management, Azure Key Vault, and dbatools

mrrobsewell from SQL DBA With A Beard

Following on from my posts about using Secret Management Good bye Import-CliXml and running programmes as a different user, I have another use case. After creating Azure SQL Databases...

From the SQL Server Central Blogs - Fun with Power BI and Color Math

Meagan Longoria from Data Savvy

I recently published my color contrast report in the Power BI Data Stories Gallery. It allows you to enter two hex color values and then see the color contrast...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Bypassing Dynamic Data Masking
I am using Dynamic Data Masking to limit access to the last four digits of an ID number for customer service personnel. I want a manager to be able to see the entire field for a table. What permission to do I assign and at what scope?
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)

Setting Server Memory

I can set the instance memory with an sp_configure command:

EXEC sys.sp_configure
    @configname = 'max server memory'
  , @configvalue =

I enter a value for the configvalue in what format?

Answer: as an integer representing MB

Explanation: We enter max server memory in MB. Ref: Server memory configuration options - https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-ver15

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

Replication gets dropped automatically - I am not sure who or what is doing it but as soon as I setup a snapshot replication and leave it for a day then the other day I find the replication gets dropped completely and I have no clue what process is doing it. I checked agent jobs as well and I couldn't […]

Changing values in Partitioned Key - Hi All One interesting scenario here. We have a partitioned table which is partitioned based on a date column. The clustered Primary key is a composite key with ID+ that Date column. The table also has some 25 non clustered indexes. Now the value of partitioned key (i.e. the date column) might change. So that […]

How to create a role and add data_reader and execute permission on 2 databases - Hi Can someone tell me the easiest way to create a new role (report_user_role) on 2 databases which gives it the data_reader permissions and execute permissions on 2 databases. I then want to create a new user report_user and assign that role to it so it can read all tables in the 2 databases and […]

SQL Server 2016 - Development and T-SQL

Sum of column from multiplied columns - I would like to get the sum of "Transaction Cost" as created from the multiplication of TxQty and AvgCostOld. Any help would be appreciated. SELECT Item, TxQty, AvgCostOld, TxQty*AvgCostOld AS TransactionCost, TxNumber, TxDate FROM tblimInvTxHistory WHERE TxDate > '2020-08-25' AND TxCode = 'ADJ'

LEGACY_CARDINALITY_ESTIMATION = ON and COMPATIBILITY_LEVEL - Hi everyone, we migrated from sql 2012 to sql 2016 and since then we have been facing some performance issue. We decided to change the compatibility level to 110 (sql 2012) and set the LEGACY_CARDINALITY_ESTIMATION= ON. It helped but then we were still having performance issues and we switched back to the original configuration. Now, […]

Administration - SQL Server 2014

What is XE_SERVICES_RWLOCK and why am I seeing it so much? - So I've got a new Windows 2016 / SQL 2014 SP3 server that's giving me trouble. When the issue occurs, the response from the server is extremely slow, up to and including login timeouts when trying to connect to it with SSMS from a workstation. When the problems occur, applications that use the various databases […]

FCI - I have two standalone SQL Server instances. Planning to configure SQL Server FCI between those two instances. Is that possible or I need two nodes without SQL Server on it? Please advise? Thanks in Advance!

SQL 2012 - General

NT authority/system - In our environment someone is using nt authority/system account with sysadmin permission I have checked the logs, event viewer but not able to find how/who assigned this and when Is there a way to find this Thanks Atulyan

SQL Server 2019 - Development

How to CAST() the value column in a dynamic unpivot - I have a set of data that has multiple different data types including one field that is a nvarchar(max) field. I need to unpivot the data but of course I am getting the pesky error "The type of column "myColumn" conflicts with the type of other columns specified in the UNPIVOT list. I am wondering […]

find group of time - hi all, i have the follwing table : 10 lines i want to show only row after 30 second (should be paramter :)) between them. ( as summery of previous records) on this record set: 2020-08-26 00:00:00.000 --first line bring 2020-08-26 00:00:20.000 -- 30 second not over avoid this record 2020-08-26 00:00:25.000 -- 30 second […]

SQL Server Newbies

SSMS connections to Availability Groups - Hello, I am new to Sql server and have a question on SSMS connecting to AGs. When I connect to the primary node the database has a white circle on it and on the secondary node it has a green arrow. I would think it should be the other way around. Primary should show a […]

Reporting Services

Two Tablix in a report - I have a report that renders two different outputs depending on a parameter(Summary or Detail). I have to add a new column to both reports. When I add a new column, its only adding to the detail report. I can't figure out how to change both. I'm using report manager to do this but could […]

General

GMSA for use on Windows Task Scheduler Job - Hi team, I created a new windows task scheduler job using GMSA. Command was success: $Action = New-ScheduledTaskAction "E:\TEST\DeleteFiles.bat" $Trigger = New-ScheduledTaskTrigger -At 06:00 -Daily $Principal = New-ScheduledTaskPrincipal -UserID DOMAIN\SVCSQL$ -LogonType Password Register-ScheduledTask TestTask –Action $Action –Trigger $Trigger –Principal $Principal The task is just clear any files in a folder, example D:\TEST\ - any files […]

SSDT

How to dynamically create a table in SQL from a Excel file using SSDT - Hi, I am fairly new to SSDT and am looking for some help with loading excel files that I have been provided for a project. The files will all have the consistent file names and consistent tab names but that is where the consistency ends. The tabs can have from 1 to 150 columns with […]

Integration Services

SSIS Package Slow As Agent Job vs Manual DTexec execution - Hello Folks, I have an SSIS package on a machine(SQL Server 2017, Windows Server 2016) that has user configured variables for table names and exports 3-4 tables from SQL Server database to a flat file and later zip it so essentially it is doing a select * from view (the view in question here is […]

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.