Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
The Voice of the DBA
 

The Need for DevSecOps

One of the things that happens with many companies that start adopting DevOps is that they release new features constantly. They publish their lists of changes, and they try to attract customers and grow their businesses. They may make some mistakes, but they fix those quickly and keep pushing forward. That's the idea, and it works well.

However, many of the developers (and most managers), don't think about the security side of their changes. This piece looks at the way hackers and criminals view DevOps, often using release notes and feature changes as a target to focus their efforts. In this way, they exploit holes and vulnerabilities in software to attack data storage. The examples include S3 buckets of storage and Elasticsearch, which is notoriously poorly secured by many people.

I'm sure there are hacks that also expose relational data stores and NoSQL stores, but those are often more secure and harder to directly attack. Certainly, hackers do get credentials and can query data, it's more often I hear about data breaches from other sources than direct relational database access. SQL Injection is definitely still an issue, and I hope that more and more developers are learning patterns that avoid these vulnerabilities.

DevOps works. I think it's great. However, it's not enough to trust developers to build features without including static code analysis, pen testing, and other security evaluations before you release code. Often developers can build features just as fast with good patterns as bad. Use automation to catch bad patterns and force developers to learn new ones.

Also, avoid letting developers implement data stores out of convenience and speed. Ensure that strong security practices, long passwords, service accounts, secrets, and more are implemented from the start. It's easy to shortcut these, but it's also harder to explain to customers and investors why we didn't do better.

If you're a developer, the main thing to keep in mind is that you will often be the scapegoat for these issues. Upper management might not support you and pressure you to move faster, but when there are issues, they'll also be quick to blame you and let you go first. Push back and ensure that you have the tools and the process to evaluate if you are building problematic code. Always use long passwords, and document what you do for others to follow.

And if your boss insists on cutting corners, get that in writing. It might not save your job, but that documentation has served me well in the past when issues come to light.

Steve Jones - SSC Editor

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

 
 Featured Contents
SQLServerCentral Article

Sentiment Analysis with IA

Daniel Calbimonte from SQLServerCentral

Learn how to use Python and AI to perform a sentiment analysis on text.

SQLServerCentral Article

PostgreSQL 101: A guide to PostgreSQL documentation & useful resources

Additional Articles from SimpleTalk

In the latest webinar of our PostgreSQL 101 series, join Ryan Booz, PostgreSQL Advocate at Redgate, and Grant Fritchey, Microsoft Data Platform MVP, and discover:

How to navigate PostgreSQL documentation
The essential things you need to know about PostgreSQL documentation
Other useful learning resources to help make your journey to using PostgreSQL easier

Please register to watch the recording.

External Article

Getting Started with Power Query M Language in Excel

Additional Articles from MSSQLTips.com

This article introduces M language concepts and examples of using M to manipulate data in Excel.

Blog Post

From the SQL Server Central Blogs - Decimal Display in Power Query

Joyful Craftsmen from Joyful Craftsmen Blog

Introduction
Power Query is a versatile tool found in both Power BI and Excel, facilitating data transformation and manipulation. Despite its efficiency in numerous data tasks, managing decimal values can...

Blog Post

From the SQL Server Central Blogs - How to curb aggressive parallelism in Microsoft SQL Server

sqlrunner from SQLRunner

Microsoft SQL Server, like most modern database systems, can convert a query into a set of parallel instructions to improve efficiency. This is map-reduce before map-reduce was a popular...

Azure SQL Revealed

Azure SQL Revealed: A Guide to the Cloud for SQL Server

Site Owners from SQLServerCentral

Access detailed content and examples on Azure SQL, a set of cloud services that allows for SQL Server to be deployed in the cloud. This book teaches the fundamentals of deployment, configuration, security, performance, and availability of Azure SQL from the perspective of these same tasks and capabilities in SQL Server. This distinct approach makes this book an ideal learning platform for readers familiar with SQL Server on-premises who want to migrate their skills toward providing cloud solutions to an enterprise market that is increasingly cloud-focused.

 

 Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

A Reuseable Window

I have this sample code from the MS Docs:
SELECT
  ROW_NUMBER () OVER win AS "Row Number"
, SUM (s.TerritoryID) OVER win AS TerritoryCount
, p.LastName
, s.SalesYTD
, a.PostalCode
FROM
  Sales.SalesPerson AS s
  INNER JOIN Person.Person AS p
    ON s.BusinessEntityID = p.BusinessEntityID
  INNER JOIN Person.Address AS a
    ON a.AddressID        = p.BusinessEntityID
WHERE
  TerritoryID IS NOT NULL
  AND SalesYTD <> 0 WINDOW win AS(PARTITION BY PostalCode
ORDER BY SalesYTD DESC)
ORDER BY PostalCode;
GO
When I run this on a SQL Server 2022 database, what happens?  

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)

Backing Up a Restoring Database

I have a database involved in log shipping. I have this database restored with the NORECOVERY option as we continue to add log restores to this.

Can I back up this database with a BACKUP DATABASE command while it's in the restoring state?

Answer: No

Explanation: You cannot back up a database in the restoring state. Ref: BACKUP - https://learn.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver16#general-remarks

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
cost threshold for parallelism value to be set - Hi Everyone, Why and when should be set 'cost threshold for parallelism'? We are on SQL 2017 Enterprise Edition. What should be the value set for cost threshold for parallelism instead of default value 5 and why ? what benefit do we get if we set a high value and what should be the starting […]
Identifying the process that is backing the databases to virtual device - We have about 20 SQL Servers. We use Net backup or Native backup for SQL Server database backups. When I query the msdb to find the backups, 2 servers have backup method different. In addition to the native backup some other process is backing up the databases to virtual device. I am struggling to identify […]
SQL Migration - Identifying Connections - Best approach - I have one SQL Database server which is old version and planning to migrate to SQL2017. We have about 10 databases on this server. I am seeing connections to only one database from sp_who2.  Looks the connections to other databases are closing immediately. Question 1: Other than running SQL Profiler, what are the options available […]
SQL Server 2016 - Administration
Does multi-subnet with standardeditionofsqlserver supports DB Mirror Or AG group - Does multi subnet failover with standard edition of sqlserver supports DB Mirror or AG groups 1. can db mirror configured in different multi subnet with standard edition 2. can db always on configured in different multi subnet with standard edition
SQL Server 2016 - Development and T-SQL
get week number of month - I want to query a table that has a datetime column ([CreateDateTime])  and get the week number for that specific month. 2023-03-01 09:30:00 2023-03-12 11:15:22 2023-03-22 14:22:18 2023-04-02 15:12:30 I want to get these results from the above dates: 1 3 4 1  
CXConsumer wait type issue - hello, my query is select * into #TmpA from tblA (nolock). tblA contains 116000000 records. This query sometime takes 6 minutes and some time takes less 2 minutes. I observed CXConsumer wait types mostly and sometimes CXPacket. On observing the execution plan, parallel execution is taking place. kindly guide what can be done in this […]
SQL Server 2019 - Administration
High tempdb usage alternatives - Hi We have an stored procedure which runs frequently and  it contain logic in which data are first dumped in temproray table and then moved to physical table .due to large data and frequent run of sp .tempdb database get filled up quickly .We cannot take server restart everytime so whats alternative to temp variables […]
high latency in sql server transactional replication - Hi I am new to replication and we have database of size around 2TB and its has been set up for replication .there are 4 subscriber to it. Tables(articles) of database has been divided into 14 publishers. Issue sometime we face high latency on server with lakhs of undistributed command so what should i check. […]
upgrade evalution editon reporting services to Stanadard - Hi All, I installed reporting services evaluation edition for testing purpose, but now 180 day trial period is expired and I am unable to generate reports. Is it possible to upgrade it with SQL server 2019 standard edition, without disturbing the existing configuration. I Installed SQL server 2019 database engine on same machine, but while […]
Delegated rights to Enable/Disable TSQL Jobs (Specific Jobs) - All, In attempts thinking that I am smarter than your average bear, I attempted to do the right thing and write a wrapper for users to be able to enable/disable certain jobs.  Trying to avoid the pitfalls of allowing users access to modify everything in a job and avoid code elevation, I wrote this test […]
SQL Server 2019 - Development
Convert columns result into rows with comma and single quotes - Hi All, Hi All, I want to convert columns result into rows with comma. Ex: Result should be - 'SQL1','ABCD100','XYZ123' #code: create table #tbl_db (name varchar(100)) insert into #tbl_db values ('SQL1') insert into #tbl_db values ('ABCD100') insert into #tbl_db values ('XYZ123') select * from #tbl_db Result needed: 'SQL1','ABCD100','XYZ123'  
SQL Azure - Administration
post steps for Table PARTIONING - Any one did Table partitioning in Prod, please list steps here. we have a transaction DB with 4  Existing Large 60 M row Tables, WITHOUT partition storing Transaction Data from 2019 to 2023. They asked me to come up with solution to partition this data , on azure MI ( assume only one FG is […]
Strategies and Ideas
SSIS vs TSQL sp - Hey All, I'm working on a new DW design and I'm learning DW on the fly right now, so I don't have much experience here. Anyway, we are currently in the planning stages. We are considering not using SSIS, and just writing our own SP to handle all DML logic for the EDW. I'm just […]
Employers and Employees
Master Data - Relational or Dimensional modeling? - Dears, In my company, we have certain master data domains like customer; Vendor; Product; Materials; Bill of Materials. So far we have been doing our modeling using relational modeling in the cloud. It consists of entire tables that we push from SAP ECC and SAP MDG like MARA; MVKT, etc... then we have views on […]
SQL Server 2022 - Development
Hello i am new to Sql and idk why i get this error and also not getting results. - CREATE DATABASE dbsubjects; CREATE TABLE tblstudent( ID INTEGER PRIMARY KEY, TheName Varchar (100), TheAddress VarChar (100), Marks Real, Comments Varchar (100)); insert into tblstudent values (1001, 'Imon', 'FRI', 90,'Great'); Msg 213, Level 16, State 1, Line 9 Column name or number of supplied values does not match table definition.
 

 

RSS FeedTwitter

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

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -