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

Shift Right

Earlier this year I was watching someone present on DevOps and IaaC (Infrastructure as Code). The speaker was showing how they had worked with clients to implement tests and checks that evaluated whether their systems were deployed and the code was working as expected. They did this as the CI/QA environments weren't maintained, but rather spun up as needed when code was committed or tested.

What I found fascinating was the part in the talk where the speaker said they were "shifting right."

That had me sitting up and paying closer attention. After all, for ten years I've been working in DevOps where we try to shift everything left. Shift testing to the developers, shift infrastructure into the repository, shift pen testing into some automated CI stage. We want to know about issues sooner. What is this shift right stuff?

It turns out that the speaker was using this as a way of smoke testing. They didn't completely trust that CI/QA were configured the same as production. They were performing many of the same tests in production after a deployment. In a few instances, they'd spin up a copy of production, deploy changes, and then run tests against that. They wanted to get a real-life view of how the system worked.

For some parts of the infrastructure, this seemed like overkill. After all, if I'm updating an app and perhaps adding a web server to check, verifying this was the same size as a previously deployed web server seems silly. However, they included other checks like security. Was the new, or even old, server configured with the same security that was required by the org? They had policies around various authentication and authorization items and these were constantly rechecked. Someone alter a file share or enable anonymous access? If this was against policy, someone was notified when the next deployment caught this.

What was interesting about this methodology was that deployments weren't often failed. Instead, when something failed, there was a report sent to a group and the incident was logged, but the deployment still occurred. That's an approach that I actually like. Don't stop things, but make sure someone follows up. Of course, like many nagging problems, if this happens constantly or it can't get fixed, then this isn't helpful information.

I haven't thought much about the idea of shifting things right, but I have advocated for the same ideas. Create smoke tests that evaluate if things still work after deployment. Get feedback to people right away. Much like a unit test, I'd want to know if simple things are broken. I'd also approach these tests the same way. Don't write a lot to start, but every time there is an issue, add a unit test and a smoke test to prevent regressions.

And be careful of which frameworks you use. Some database unit testing frameworks alter tables, which can't happen in production.

Steve Jones - SSC Editor

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

 
 Featured Contents
Stairway to DAX and Power BI

Level 24: Time Intelligence – Dates Functions: The DAX OPENINGBALANCE*() Functions

Bill Pearson from SQLServerCentral

Business Intelligence Architect, Analysis Services Maestro, and author Bill Pearson introduces three similar DAX Time Intelligence functions related to Date: OPENINGBALANCEMONTH(), OPENINGBALANCEQUARTER(). and OPENINGBALANCEYEAR(). He discusses the syntax, uses and operation of each function, and then provides hands-on exposure to it in Power BI.

Technical Article

Sessions for Summit 2023 are now LIVE!

Additional Articles from PASS

The wait is finally over. This year’s PASS Data Community Summit sessions are now live!

External Article

How to secure legacy ASP.NET MVC against Cross-Site (CSRF) Attacks

Additional Articles from SimpleTalk

CSRF is when an attacker submits unauthorized commands to a website user already logged in. In layman’s terms, When you click on a malicious hyperlink, it triggers scripts that perform actions on your behalf to your logged-in bank website. Viola, the attackers, have your money.

Blog Post

From the SQL Server Central Blogs - What’s in a SQL Server Managed Instance?

Kenneth.Fisher from SQLStudies

My current position involves a lot of work with SQL Server Managed Instances so you’ll probably be hearing a lot ... Continue reading

Blog Post

From the SQL Server Central Blogs - Using Secrets in Docker Swarm for SQL Server

dbafromthecold@gmail.com from The DBA Who Came In From The Cold

In a previous post I went through how to run SQL Server in Docker Swarm and in this post I want to continue delving into the Docker Swarm platform...

Microsoft Power BI Quickstart Guide cover

Microsoft Power BI Quick Start Guide: The ultimate beginner's guide to data modeling, visualization, digital storytelling, and more, 3rd Edition

Steve Jones - SSC Editor from SQLServerCentral

Bring your data to life with this accessible yet fast-paced introduction to Power BI, now in color.

 

 Question of the Day

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

 

An Interrupted Restore

I was restoring a database to my development server from a backup on a network share. This was a large, 500GB database and the process was taking a long time. There was a network interruption to the process and I got a failure message in SSMS. However, the database was shown as "restoring" in SSMS. The network is back now. What should I do to most easily complete the restore?

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 PowerShell Copy

I have this PowerShell code:

$source="c:\fileloading" #location of starting directory
$destination="c:\fileloaded"; #location where files will be copied to
$files="*dys_ihhist*" #files matching this pattern
$a = get-childitem $source -filter $files
$a | foreach {copy-item $_.fullname $destination\$($_.basename)-$(get-date -f yyyyMMdd)$($_.extension)}

What happens when I run this?

Answer: The files matching the filter are copied from c:\fileloading to c:\fileloaded with the date appended to the file name

Explanation: This PowerShell script matches files in c:\fileloading that have a "dys_ihhist" in the filename only, not the extension. These files are then copied, not moved, to c:\fileloaded with the date appended to the filename. Ref:

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
Select blocking a Truncate stamemet - Hi All, I am seeing a SELECT blocking a TRUNCATE statement. THE SELECT is running over 4 hours and it is waitype is AYSN_NETWORK_IO. Isolation level is default read committed isolation level. Why or when does a SELECT takes "Sch-S” lock? I see this lock being acquired using sp_whoisactive. Truncate is waiting for="Sch-M" lock. How […]
What's the right way of capturing expensive queries? - Hi All, While capturing expensive queries by CPU or IO, why it is important to group queries by query_hash and then tune them instead of directly pulling top 10 queries from sys.dm_exec_query_stats DMV?. What am I missing ? is it wrong way to capture expensive queries> if so, I want to understand why? please share […]
High CPU & blocking - Hello SQL Experts, We have SQL2017 enterprise edition with about 60 databases. Recently we are seeing very high cpu spikes. We have DB maintenance job running twice/wk (Hallenger Script). Manually updated the statistics but it didn't helpful. I don't have baseline about the no of transactions or queries that ran previously.  Followed the guidance provided […]
SQL Server 2017 - Development
Join on Column A when Column B is Null - hi, I need to join on a table T1 AND Table T2. Something like this : Select * from Table T1 JOIN TableT2 on T1.A =T2.A but here , condition is if  (T1.a or t2.a is null ) then join on (T1.b =t2.b ) where t1.b and t2.b is not null.   How to achieve […]
Administration - SQL Server 2014
SQL 2014 Standard - All SQL agent jobs disabled after patch - We have a SQL server 2014 (with SSRS) and over the weekend the list of patches below got installed around 1AM. KB5029312 KB5028970 KB5028957 KB5029368 The following morning it was noticed that no jobs ran and when we checked, EVERY single SQL agent job had a red x and were all disabled. I have never […]
SQL 2012 - General
The target princial name is incorrect. Cannot generate SSPI context. - I've just created new certs, new SPNS and applied via SSCM. Within AD I've ticked trust this user for delegation, use kerberos only to all of my new SPNS. SQL starts fine & I can logon locally but get the above error logging on from a different machine (therefore attempting kerberos). I'm sure I've done […]
The target princial name is incorrect. Cannot generate SSPI context. - I've just created new certs, new SPNS and applied via SSCM. Within AD I've ticked trust this user for delegation, use kerberos only to all of my new SPNS. SQL starts fine & I can logon locally but get the above error logging on from a different machine (therefore attempting kerberos). I'm sure I've done […]
SQL Server 2019 - Administration
msdb job history permission error - SELECT * FROM msdb.dbo.sysjobhistory error: The SELECT permission was denied on the object 'sysjobhistory', database 'msdb', schema 'dbo'.   the user is a member of the following msdb role: SQLAgentReaderRole SQLAgentUserRole SQLAgentOperatorRole   What other permissions do i need?
How to Backup SQL Active/Passive cluster Database to File Share - Hi We have 2 node Active/Passive SQL serer cluster with 2 databases, due to the backup disk capacity limitations we are planned to backup databases to the File Share location (\\192.168.0.x\Backup). When we trying to connect File Share to the WSFC there is no option to add file share it is showing only the availble […]
Query joins and SQL Server mirroring (2019) - Have a bit of a situation with database mirroring and table joins, hopefully someone can shed some light on possible solutions. Scenario: DB01 is on Server01 and is mirrored to Server02 DB02 is on Server01 and is mirrored to Server02 DB03 is on Server 03 and is mirrored to Server04 Linked servers are setup between […]
Color Particular Column - Hi I want to color particular column . If value is -ve then color should be red Thanks
SQL Server 2019 - Development
Powershell script run through Task Scheduler - Cannot validate argument on param - I have a Powershell script which is being called through Windows Task Scheduler My script is set up as follows:   param( $FileDirectory, $VerboseDirectory, $ServerInstance, $Database ) Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -Query "EXEC spImportReturn @FileDirectory = '$FileDirectory'" -verbose 4> $VerboseDirectory Now I then call this Powershell script from Windows Task Scheduler as follows: Executable: […]
Powershell
change delimeter - This script converts from xlsx to csv, but how can I change delimiter from comma to semicolon, and pickup the same file name from multiple folders to process? Thanks. # To execute the script without agreeing with the execution policy Set-ExecutionPolicy Bypass -Scope Process # Defines the directory where the file is located $dir = […]
Integration Services
Job Step Properties - Configuration - Parameter Issue - Hi, I am fairly certain that both myself and my colleagues could ALL change the configuration parameter on a Job Step, but I am now informed that only a Sys Admin or the job owner can change this value. ( currently on  SSMS 18.12.1 ) This is a bit annoying as it makes being able […]
SQL Server 2022 - Development
Proc and Table permissions (Grantor column) - I have several tables and stored procedures that have an user ID of a person that is no longer with the company that is listed in the Grantor Column under schema properties permissions window. We ran into an issue with a stored procedure executing until we change the Grantor (the user Id) Column to a […]
 

 

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

 

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