SQLServerCentral.com Community Update for February 22, 2023

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Types of Always Encrypted

The Voice of the DBA

Daily Coping Tip

Share something you find inspiring, helpful, or amusing

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 Danger of Sharing Data

In the past, many businesses hired employees whose role was deciding which prices to charge for their goods or services. At one point, organizations largely set prices based on their costs, though over time they tend to look at their competitors and set similar prices. If, however, management from multiple companies went into a room and determined prices, this would be price-fixing.

Price fixing is illegal. In many countries, we would not allow different companies to work together in a way that might reduce competition or take advantage of consumers. However, that might be a struggle in the future as we find companies using various services to help them manage their systems.

In this case, a few different Las Vegas hotels used the same company to help them decide how to price their rooms. Rainmaker is a revenue management platform, which uses lots of data to help hotels price their rooms in a way that maximizes their revenue. That sounds great, but if this company is successful and many of their clients are in the same location, this is really a way of sharing data by proxy. The hotels are being sued because of their use of this platform.

This one of the problems (or advantages) of lots of data. It allows information to be drawn out of data that wouldn't otherwise be obvious. Certainly, lots of companies look at their competitors and make decisions based on what they see. I'm certain there are lots of people inside airlines constantly checking the prices of their competition. However, they are gathering this data independently and making their own decisions. If Rainmaker were used by American, United, and Delta to set prices for flights, I imagine many would see this as an anti-trust violation.

Big data is powerful. It can help give an organization an advantage over its competition. This is one reason lots of companies hire data professionals like us; they see data as a very valuable asset. However, in this case, I feel that one company selling this data, or rather the conclusions, to competitors is a problem.

I expect more problems like this in the future as smart people look to harness the power of data and sell their services to competitive companies in many industries.

Steve Jones - SSC Editor

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

Featured Contents

Azure Automation With Visual Studio Code

Arun Yadav from SQLServerCentral

Microsoft combined the power of two powerful tools by announcing the public preview of Azure Automation Visual Studio Code Extension. This article will show you how to get started.

PostgreSQL Basics: Roles and Privileges

Additional Articles from SimpleTalk

Information security, whether in databases, applications, or larger IT systems, is based on the principles of Authentication and Authorization, often referred to as AuthN and AuthZ respectively. Likewise, PostgreSQL security involves creating roles that allow access to server resources (AuthN) and then granting privileges to database objects appropriately (AuthZ). Understanding how each of these components work together is essential to effectively manage security in a PostgreSQL cluster.

Undoing Actions on Groups of Database Tables

Additional Articles from Redgate

During development, you'll occasionally need an undo script that drops a group of tables, or you might need to truncate a group of tables and then insert fresh data in order to run some tests. Unless you perform the required actions in the correct dependency order, you'll be tripped up by foreign key constraint violations. This article provides a SQL function that returns the list of tables in the correct dependency order.

From the SQL Server Central Blogs - New Microsoft Purview features

James Serra from James Serra's Blog

Microsoft Purview, formally called Azure Purview (see Azure Purview is generally available) has recently released a number of new cool features. I wanted to call out a few of...

From the SQL Server Central Blogs - Query Store Data Cleanup

Grant Fritchey from The Scary DBA

Microsoft supplies quite a few knobs to control how Query Store performs data cleanup. You can set your cleanup various ways, and, they interact. Let’s talk about how and...

Transact-SQL: The Building Blocks to SQL Server Programming eBook by Gregory A. Larsen

Transact-SQL: The Building Blocks to SQL Server Programming by Gregory A. Larsen

Greg Larsen from SQLServerCentral

Transact SQL (TSQL) is the languaged used to query and update data stored in a SQL Server. This book, written by SQL Server Central and Simple Talk author Greg Larsen, will give developers an understanding of the basics of the TSQL language. Programmers will have the building blocks necessary to quickly and easily build applications that use SQL Server.

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Types of Always Encrypted
What are the types of encryption available with Always Encrypted?
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)

The Query Store Capture Mode

I am using Query Store for an application my company has written. We pre-load the database with some data and have a specific set of stored procedures and queries we run to test the database. Once we've completed this, we don't want to capture any ad hoc or DBA queries that are run, but we want Query Store to continue to monitor these queries.

What capture mode should we set?

Answer: None

Explanation: None would be the mode that continues to allow Query Store to monitor the queries in its cache, but not to capture new ones. Read -only isn't a mode. Ref: Setting the Optimal Query Store Capture Mode - https://learn.microsoft.com/en-us/sql/relational-databases/performance/manage-the-query-store?view=sql-server-ver16&tabs=ssms#set-the-optimal-query-store-capture-mode

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

Looking for guidance on perf issue - Hi All, Need some guidance here. We have multiple integrations with our centralized data warehouse database. Recently, a new application got integrated and management has defined an SLA of 2secs for every query coming from that application. Its kind of an API call. Now, that particular team is complaining that sometimes the api calls are […]

misleading blocking - Hi All, I came across a blocking scenario where everyone thinks its SELECT is blocking the UPDATE operation but it's not true. The DMVs are showing up only current batch or current sql text which was executed. My question: How to prove its not the SELECT but its the DML update which took the 'X' […]

logon failed for user sa - Hello Immediately before running the daily backup created in SQL Server Configuration Manager, login errors appear in the SA account, but the system informs that the backups ended successfully. The sql agent service account, is a domain administration account, which is sysadmin. Must I configure this account owner of the databases? This situation is related […]

Move database amd log to another disk - Sql 2017 Std installed on a windows 2012 R2 server which is a domain controller. I do not use replicas. The databases aren't participating in an Availability Group. Goodnight First of all, please be patient with me. I'm new to this subject of sql I need to move 5 databases from one disk to a […]

SQL Server 2017 - Development

begin tran vs WITH(ROWLOCK) - Hi , when begin transaction and update holds lock on row and no other can update if unless committed, why and what is the purpose of "WITH(ROWLOCK) " ?as per internet both looks same UPDATE transak WITH(ROWLOCK) SET Col1 = 1 ,col2=2 where col3=1; BEGIN TRANSACTION UPDATE transak SET Col1 = 1 ,col2=2 where […]

Administration - SQL Server 2014

DBCC CHECKDB and Backup Database - Hello. Are there any references to running DBCC CHECKDB and/or Backup Database concurrently against different databses in the same SQL Instance? My searches are turning up discussions against concurrent activities against the same DB. DBCC CHECKDB is resource intensive (in terms of Memory) but Backup Database seemingly is not - other than IOPS. It does […]

Development - SQL Server 2014

Need to do a mass update - taking days - Hi I need to anonymize some of our members for an outward facing demo web site. Long ugly story, but basically it has to be a mass update of our existing (restored copy) data. Problem is I don't have much disk space so I am trying to batch them in. If I do an update […]

SQL Server 2019 - Administration

Find all server level permissions assigned to user defined SERVER roles - Please help me with a T-SQL for finding all server level permissions assigned to user defined SERVER roles such as VIEW ANY DATABASE, VIEW SERVER STATE, SHUTDOWN, ETC. For example, in the screenshot attached, for the test role, it has VIEW ANY DATABASE. what's the T-SQL to find the permissions for test server role. Thanks

SQL Server 2019 - Development

options for querying external data sources from SQL Server - is there a good introductory article somewhere that covers the various options for querying data outside of SQL Server, like OPENROWSET(), BULK IMPORT, etc and when to use each one? (Ideally a tutorial, but I'll take what I can get!). I did search, but it's harder to find stuff if you don't know exactly what […]

Trying to get results back from 12 month ago thru 18 months ago from todays date - So today's date is 2/11/2022 so my results should be from 2/11/2022 and 8/11/2021. SELECT oi.Name as ItemAssignedBranch, col.Name as CollectionName, cir.MaterialTypeID, cir.Barcode as ItemBarcode, ird.CallNumber, br.BrowseAuthor, br.BrowseTitle, cir.FirstAvailableDate, cir.LifetimeCircCount as ItemLifetimeCircCount FROM Polaris.CircItemRecords cir inner join Polaris.ItemRecordDetails ird on (cir.ItemRecordID = ird.ItemRecordID) left join Polaris.Collections col on (cir.AssignedCollectionID = col.CollectionID) inner join Polaris.BibliographicRecords br […]

Operating System Error on Bulk Insert - Hello everyone, it's my first post here, and I'm no expert on SQL, so I hope you're able to help, and will forgive my ignorance, I'm keen to learn! I'm trying to import around 100 txt files into a SQL table, for processing ahead of bringing into PowerBI. I've built a query, and a layout […]

SQL Server 2008 - General

Is there a way to have SQL Server be aware of a new file? - Every weekday we receive a file which under normal circumstances gets processed by a SQL Job that runs on a schedule at 7 AM. It's worked this way for years. This file is produced by a partner, who uploads it to a secure FTP site. However, for the last several weeks the file has either […]

Amazon AWS and other cloud vendors

How can I access EC2 instances in a private subnet without using SSM? - Hi, I would like to access my EC2 instances over SSH, which are currently in a private subnet. I was considering a NAT GW, but then I would have to create an IGW too, and that would defeat the purpose of my efforts (to keep the instances private and locked down). Is there any other […]

General Cloud Computing Questions

Request for suggestion on data transfer from on-premises to cloud - I've been working for quite some time with AWS but only on limited services there (up to 10-15 services). Currently, I've got a request from a friend of mine who owns a small clinic (around 90 employees) that wants to move the patient data from on-premises to the cloud. I have some knowledge of AWS […]

SQLServerCentral.com Website Issues

Warning prompt when writing a reply - When I wrote this reply https://www.sqlservercentral.com/forums/topic/sql-server-database-redunduncy#post-4148195 and pressed submit I got an error "Are you sure you want to do that?" I could be missing the obvious but I'm not sure why the website queried my post? Possibly it's looking at language and it thought my post had a negative tone to it? Unless I'm […]

This email has been sent to newsletter@newslettercollector.com. 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.