SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

SQL Server Central Is Upgrading Our Servers
Using Pipeline Return Value in Azure Data Factory and Azure Synapse Analytics
Why Database Discussions Have Moved From the Backroom to the Boardroom
Normalize strings to optimize space and searches
From the SQL Server Central Blogs - Using the Pure Storage PowerShellSDK2 - Part 3 - Getting Performance Data from FlashArray
From the SQL Server Central Blogs - SQL Server Backups are Not a High-Availability Solution
The Definitive Guide to Azure Data Engineering: Modern ELT, DevOps, and Analytics on the Azure Cloud Platform

Question of the Day

Multiple Encryptions

The Voice of the DBA

Not Useless Features

Today's editorial was originally published on Dec 1, 2017. It is being republished as Steve is out of town on the Database DevOps Rocks Tour.

Some time ago I noticed Adam Machanic had written a post titled The SQL Hall of Shame. In it, Adam notes there are features that Microsoft will introduce in SQL Server and then let die. These are the useless features that aren't widely used, or even useful for many projects. You can read his list, and agree or disagree.

I think there certainly are some features that were silly experiments and I wish they had been introduced as experimental, or beta, features early, allowing users to give feedback on whether they're useful and where they need refinement. I certainly think too many resources were initially wasted on items like MDW and Query Notifications, and far too little investment later. These days I think we'll see more early releases of items in Azure where users can experiment and provide feedback to help Microsoft decide if more investment is needed. At least, that's my hope.

Today I'm wondering if you think there are features in SQL Server that do need more attention. Those features you use, but appear forgotten and you'd hope they are improved in the next version.

For me, I certainly wish replication would get more attention, tooling, and resilience. I'm not sure if this needs an overhaul, like SQL Server 2005 was, or there just needs to be some reworking of the code, but in today's distributed world, we need a more reliable way to hook up portions of databases and move data around. Not ETL, not Availabilty Groups, a better replication architecture and implementation.

There might be other features you use or want, but let me know today. What's a not useless feature that you want improved.

Steve Jones - SSC Editor

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

Featured Contents

SQL Server Central Is Upgrading Our Servers

Steve Jones - SSC Editor from SQLServerCentral

The SQL Server Central database servers are being upgraded on Thursday, Nov 2.

Using Pipeline Return Value in Azure Data Factory and Azure Synapse Analytics

arindamxs from SQLServerCentral

Learn how you can return data from one ADF pipeline for use in another.

Why Database Discussions Have Moved From the Backroom to the Boardroom

Additional Articles from SQLServerCentral

There are good reasons why business leaders should care about their databases—and hidden costs for neglecting them. Our CMO, Kate Duggan, wrote about the risks and potential costs in this article on Bloomberg.

Normalize strings to optimize space and searches

Additional Articles from SimpleTalk

While this article is specifically geared to SQL Server, the concepts apply to any relational database platform. The Stack Exchange network logs a lot of web traffic – even compressed, we average well over a terabyte per month.

From the SQL Server Central Blogs - Using the Pure Storage PowerShellSDK2 - Part 3 - Getting Performance Data from FlashArray

aen from Anthony Nocentino Blog

Welcome back to the second installment of our blog series on using the Pure Storage PowerShell SDK2. In this post, we’ll learn how to retrieve performance data from FlashArray...

From the SQL Server Central Blogs - SQL Server Backups are Not a High-Availability Solution

Kevin3NF from Dallas DBAs

Please continue doing your backups! Backups are Disaster Recovery, yes…but not HA. Some will argue with this (in the comments most likely), but I broadly define “High Availability” as...

The Definitive Guide to Azure Data Engineering: Modern ELT, DevOps, and Analytics on the Azure Cloud Platform

Site Owners from SQLServerCentral

Build efficient and scalable batch and real-time data ingestion pipelines, DevOps continuous integration and deployment pipelines, and advanced analytics solutions on the Azure Data Platform. This book teaches you to design and implement robust data engineering solutions using Data Factory, Databricks, Synapse Analytics, Snowflake, Azure SQL database, Stream Analytics, Cosmos database, and Data Lake Storage Gen2.

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Multiple Encryptions
I want to create a symmetric key with this command: CREATE SYMMETRIC KEY PIISymKey10 WITH ALGORITHM = AES_192 ENCRYPTION BY PASSWORD = 'MyS3cr#tP@ssword' , ASYMMETRIC KEY HRProtection , CERTIFICATE MySalaryCert , ASYMMETRIC KEY HRProtection2 , PASSWORD = 'AnotherS$trinSD'; What happens when I run this? Assume that the asymmetric key and certificate exist in the database.
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)

Parsing Strings

What is returned by this code?

SELECT PARSENAME('a.b.c.d.e', 2)

Answer: NULL

Explanation: This returns NULL as Parsename returns null if the string is not a valid SQL Server identifier. In this case, an identifier is a string with no more than 4 parts separated by periods. This one has 5 items and 4 periods. Ref: PARSENAME - https://learn.microsoft.com/en-us/sql/t-sql/functions/parsename-transact-sql?view=sql-server-ver16

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

Execute AS and Impersonate - I am trying to write up a stored procedure to allow users to execute jobs in MSDB that are owned by another user. The job-owning user is a service account (set up as non-interactive) has elevated permissions which I do not want the business users to have themselves. The problem is that I can't seem […]

Troubleshooting a SQL Server Wait Resource Issue" - I'm encountering some wait resource entries in sysprocesses without specific details. Has anyone faced a similar situation? Any insights on how to decode or address this issue would be greatly appreciated.

help decoding a wait resource from sysprocesses - Hi After some help . I've got a bunch of wait resources from sysprocesses that don't include specific resource type i.e its not prefixed with KEY, or TAB etc This type of format 66:1:6666666 So I presume the first numbers are the databaseid, maybe the second number is file id , but I cant decode […]

Administration - SQL Server 2014

How to include newly system generated tables to SQL replication? - Hi there, I have this situation that I have been trying to get around for some time but I haven't been able to despite some considerable investigation. I have transactional replication between 2 servers, the standard run of the mill set up. I noticed after some time, the replication goes out of sync (or breaks) […]

SQL 2012 - General

Can I still get a SQL 2012 eval for my home lab? - Just for testing upgrades. All the links are dead. I understand it's not in support but like to test the full upgrade properly.

SQL Server 2019 - Administration

SSIS - "Access to the path '\\XXX\data2\TXX\zz\Tracker - xx.xlsx' is denied.". - Hi All, SSIS error: Recently the following error "Access to the path '\\XXX\data2\TXX\zz\Tracker - xx.xlsx' is denied.". i occurring. The SSIS package is executed through SQL Agent using a Domain service account and running as a job for years No changes to SQL Server no changes to the fileserver permissions etc. 1st thing checked is […]

Replica stuck in resolving state during upgrade - Hi, Last week we tried to perform an upgrade on a 2 node sqlserver 2012 availability group. OS is windows server 2019. Cluster functional level is set to 9. We set secondary node to manual failover, assync commit, etc.. the secondary node was updated ok. On the primary, still sqlserver 2012, all was also good […]

SQL Service is not starting after upgrade... - Seems to be Windows is somehow decided to upgrade something in SQL Server and after that it can't start. SQL Service is starting correctly only with trace flag 902. How to fix the issue? Script level upgrade for database 'master' failed because upgrade step 'ISServer_upgrade.sql' encountered error 15151, state 1, severity 16. This is a […]

Can trigger execution be excluded only for a particular login? - Hi, I have an ERP application which connects to SQL 2019 database by an user - "vwuser". Whenever the users does transactions via ERP , data population happens. Very few people have backend access, but auditors are asking for tracking the data modifications that happen via Backend (say data corrections via Update / Delete). The […]

Reporting Services

Dynamic Subscription query with parameters???? - Hi, I'm trying to create a query within a dynamic subscription with a parameter for a Start and End Date. The query will not validate when I try to use @StartDate and @EndDate Something like: WILL VALIDATE --- Select ID from mytable where date between '10/1/23' and '10/31/23' Once I change the date to […]

Third Party Products

Is SQL Search 3 still supported? - I'm looking through my machine to see what I have installed and what I can remove. I found a product called SQL Search 3, which is by Red-Gate. Is it still supported?

Article Discussions by Author

Handling a Cancelled Online Index Rebuild in SQL Server - I was wondering what the expected behavior is when an online index rebuild (non-resumable) is cancelled in SQL Server? Are there any potential risks or considerations to keep in mind? Through Mcdvoice homepage, customer or participant can give his honest opinion (positive or negative feedback) to improve product quality, service, store quality & norms. https://mcdvoice.support/ […]

SQL Server 2022 - Administration

time for stats to get updated when auto update statistics async = true - hi, I'm using the following database version of MS SQL SERVER Microsoft SQL Server 2022 (RTM-GDR) (KB5029379) - 16.0.1105.1 (X64) I was experimenting with the Auto Update Statistics Async = TRUE in my test database and noticed that the time it took for my table column stats to get updated took anywhere from immediate to […]

stats get updated even when query is trivial - hi, I'm using the following version of SQL Server Microsoft SQL Server 2022 (RTM-GDR) (KB5029379) - 16.0.1105.1 (X64) In my database, I've got the following options set to TRUE Auto Update Statistics Auto Update Statistics Asynchronously I've got the following script create table testtab (col1 int) DECLARE @count INT; SET @count = 1; WHILE @count<= […]

SQL Server - Kerberos Configuration Manager - Unable to connect - am currently working through replacing a development and production SQL server, moving from server 2012 to server 2022. These two servers have the SQL Service running as an AD account. Thus, I have gone through and configured the proper SPNs in Active Directory and configured Kerberos Constrained Delegation. Adding the SPN for the FQDN and […]

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.