SQLServerCentral.com Community Update for November 1, 2021

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Synapse Distributions

The Voice of the DBA

Daily Coping Tip

You can’t do everything. What are the top three priorities this week?

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.

Moving to DataOps

I've worked in a number of organizations and every one of them was constantly searching for a way to better ensure data is up to date and consolidated across systems. There has been no shortage of projects trying to assemble a "single view of the truth." Using ETL, data warehouses, external master data systems, and more, companies somehow want to ensure that they get the most value from their data.

That sounds a lot like DataOps, which is discussed in this piece. It's trying to more efficiently ensure that we manage data data better, getting it to the right system, updated, and safely. It's not quite the same as normal data operations, such as running jobs and fixing data a user broke. This is more like the overreaching we in which we move data through a pipeline.

In some sense, as a build pipeline is to the software, DataOps is to the DBA's job.

In the past, most organizations were not very coordinated in how they managed data. Instead, each application or each team was responsible for their own data, often without much communication with others. I think a lot of master data management projects in the past haven't gotten much penetration inside a company for this reason.

These days, with more governance and concern about security, I see a renewed interest in better managing data, as well as ensuring metadata is up to date and used. Companies are realizing that they can get more value from data when they know more about it, especially when the metadata can be queried programmatically. This also helps organizations ensure they are aware of their data assets, understand their risk in how the data is handled and used, and even be aware of when data might cross a security boundary.

Much of the XOps world is concerned with automating the process. CI automates the build and test of software, reducing the load on developers. In the DataOps world, I'd expect we could automate a lot, but I wouldn't think we could do it all. The structures of data assets, the ETL processes that move them, and the ways in which people consume data will constantly change. Often at a pace that isn't easily automated.

Steve Jones - SSC Editor

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

Featured Contents

Configure the CData Cloud Add-On for Google Sheets

JerodJ from SQLServerCentral

With Google Sheets you can create branded reports with data visualizations to share with your clients. Combined with CData Cloud, Google Sheets give you cloud-to-cloud access to data from the supported SaaS, Big Data and NoSQL sources for visualizations, dashboards, and more. In this article we explain how to use the CData Cloud Add-On to […]

dbatools - new functions for Database Mail

Mikey Bronowski from SQLServerCentral

Background The dbatools module is broadly used and is an ever-growing set of useful commands. While looking for some inspiration I checked the old issues logged on the GitHub repository. Finally, I found this one Remove-DbaDbMail Profile and Account · Issue #4990 · sqlcollaborative/dbatools (github.com). The issue was more than 2.5 years old (32 months). […]

Secrets of Flyway Teams Undo

Additional Articles from Redgate

Flyway Teams has an Undo facility that enables you to roll back one or more migrations. This article demonstrates how it works and explains how Undo migrations make testing migration scripts easier and branch-based development simpler.

SQL Server Network Related Performance Monitor Counters

Additional Articles from MSSQLTips.com

In this article we cover which performance monitor counters to use to baseline and troubleshoot SQL Server network issues that may be causing performance issues.

From the SQL Server Central Blogs - A Live Conference Experience at Data Minds Connect 2021

Steve Jones - SSC Editor from The Voice of the DBA

Last week I was privileged to attend a live conference in Belgium. dataMinds Connect 2021 took place in Mechelen, just outside of Brussels. I had submitted months ago, was...

From the SQL Server Central Blogs - #PowershellBasics: Get a list of files from a directory.

Kenneth.Fisher from SQLStudies

I’ve got a project I’m working on at the moment and it includes a number of elements from my recent ... Continue reading

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Synapse Distributions
If I create a table in Azure Synapse, how many distributions (locations) are used to store my table by default?
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)

SSIS Catalog Object Names

I want to rename a project in my SSIS Catalog. Which of these is a valid name? (ignore the quotes)

Answer: "My@Project"

Explanation: There are a few rules for project (as well folder or environment) names. They are:

Invalid characters include ASCII/Unicode characters 1 through 31, quote ("), less than (<), greater than (>), pipe (|), backspace (\b), null (\0), and tab (\t).
The name might not contain leading or trailing spaces.
@ is not allowed as the first character, but subsequent characters might use @.
The length of the name must be greater than 0 and less than or equal to 128.

The My@Project is valid. Ref: SSIS Catalog - https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?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 2017 - Administration

cannot generate SSPI context - We have one user who face this issue while connecting to SQL server ''Cannot generate SSPI context" (win authentication) When i checked the user account the account looks good and there is no password issue. Same user is able to connect SQL using command prompt. what could be the reason

Database Corruption - LOB - Hello, I have recently inherited a SQL 2012 database (no backups available), running in SQL 2005 compatibility mode with Filestream enabled. The first error, I received was: Error: 3043, Severity: 16, State: 1. BACKUP 'mcs_mia' detected an error on page (3:1484827) in file 'e:\BLOB\mcs_mia.ndf'. When I ran DBCC check, I received several of these errors: […]

SQL Server 2017 - Development

arabic digits stored in varchar column with Arabic_* collation - Hello, FYI Database collation is Arabic_100_CS_AS Why do Arabic characters in a varchar column (collated as Arabic_100_CS_AS) get handled correctly (inserted and displayed) while digits get transformed into their Latin equivalent? We know that NVARCHAR is able to handle this correctly but we still want to know what goes wrong with our varchar column in […]

SQL Server 2016 - Administration

Partitioning and Indexes needed on huge table - I have a table with 24,000,000 records and 140 fields. The records can be divided into 3 years for a partitioning. I'm still quite new to doing indexes and partitioning and any tips would help. I'm thinking of course I need to do this at night when no one is on. Also I'm using SQL […]

Databases status are recovery pending - Hi All , After SQL restart most of databases turned to be Recovery pending . The restart happened at primary server and automatic fail over happened. moreover the listeners are gone. i couldn’t remove db from AG either . Couldnt bring database online.. When i right click , no option to add listener . Appreciate […]

Remove database from Distributed Availability Group replica AG ONLY! - Hi, all! I have an existing always on availability group on-prem and last week created a second one in AWS. After that, I created a distributed availability group with on-prem as primary and AWS as the replica AG. Being new to SQL Server, I configured automatic seeding without thinking for a second that it would […]

Finding all logins that can alter DDL - Hello experts, Is there a ready-made query out there that I could run on a server to list all the logins that have the ability to alter DDL in any of the databases? I know some of this can be retrieved by finding members of db_ddladmin, but there could also be individually granted permissions for […]

SQL Server 2016 - Development and T-SQL

Replace Numbers - My goal is to replace the numbers 1-9 in a column with a different number. For this example the numbers would change to 1=7 2=3 3=9 4=1 5=4 6=2 7=5 8=6 9=8 I tried using the replace function shown below but my problem is a value keeps getting changed as it progresses through each replace. […]

Development - SQL Server 2014

How to redesign a query - Hi Result is determined by the calculated Usage, With the below query, the result for the last output row is 0 because usage is based on the previous date and value (using LAG). How can I rewrite this so that the calculated usage is based on the previous date and value where the result is […]

SQL 2012 - General

SQL Server SP updates - Hello, I have a SQL server I'm managing that needs to be updated from SQL server 2012 to SQL server 2012 SP3. I inherited this box so no updates have been applied to it in regards to CUs and SPs. Is installing SP3 right off the bat a bad idea, should I install SP1/SP2 first? […]

SQL Server 2019 - Administration

Cannot connect to Existing Replica - Dear Friends, I setup a test run case on 2-Node AlwaysON , on developer edition.The issue is I want to add another replica to this but the connection doesn't happen in the first page of the Add Replica wizard (please refer attached file for the error). I can though connect to both the instances well […]

SQL Server 2019 - Development

How do I join two temporary tables using MySQL? - Hi all, I'm still pretty new to SQL and could use some help getting this query to work. The ultimate goal is to join two temporary tables. In the first temporary table, I pulled all of the distinct rows from a table called 'Table A' and used a JOIN clause to combine it with a […]

SQL Server 2008 - General

Insert records with a where - I want to insert record job allocations only for selected jobs I know the following code won't work form the WHERE part but this gives you an idea of what I want achieve. TIA. INSERT INTO dbo.WipJobAllLab(W.Job, Operation, WorkCentre, IExpUnitRunTim, Milestone, IWcRateInd) VALUES(W.Job, 1, '001', 0.01, 'N', 1) ... "WHERE SELECT W.Job FROM dbo.WipMaster W […]

SQL Azure - Administration

Row Level Security - Dear Experts, How can we implement row-level security on Azure when the application is hitting the database using a managed identity. How the same can be implemented on On-prem when an application uses SQL credentials or a windows account? Currently, the application hits the DB using a managed identity and application authentication is based on […]

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.