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

When Work Isn't Done

Software development can be a challenge for each of us with lots of demands and the need to ensure your code solves a problem correctly, efficiently, and completely. Juggling the workload by yourself is one thing, but add in a team of developers, and the complexity quickly grows.

The real world is chaotic and despite the best efforts of project managers and scrum masters, our software development life cycle doesn't always proceed smoothly. I wonder how many of you run into this situation and how you deal with it.

Developer 1 gets a piece of work, let's call this A. They complete this and send it to the QA team. Somewhere during this process, Developer 2 get's a different piece of work (B) and writes code. They send this to QA before A is completely tested.

Now, Developer 1 finds a mistake. Something doesn't work or they realize their solution is incomplete. QA is A + B, but A doesn't work and needs revision. B passes testing and needs to be deployed. If your codebase and QA have both A and B in them, how do you strip out A or B and ensure B is deployed to production but A isn't?

If this is C# or Java, you might have one solution, even if both changes are in the same class. If this is database code, you might have a different set of issues to deal with.

Really, the question is can you reorder work in your deployment process? I find many customers don't consider this when they are evaluating their software pipeline. They somehow assume if code gets to QA that it's good, which is nicely optimistic but not realistic. At some point, we'll deploy code to QA that doesn't work. The more developers we have, the more likely this is, and the more demands on our time, the more likely we need to reorder work and release one thing but not another.

As a DB developer and DBA in a company 20 years ago, I built a process and forced us to reset the QA environment and redeploy B only (using a branch of code that stripped out A) for re-testing. This ensured that we tested what was going to be deployed. However, I find a lot of organizations can't do this or don't want to. They want to hope that a human and either extract all of B or strip out all of A and release partially tested code without issues.

I find that to be a poor idea. In this era of regular staff changes, staff of varying quality, and the high complexity of software, this is asking for mistakes. With cheap hardware, virtualization, and the ability to provision copies of environments, we ought to do better.

How do you handle this today? Depend on humans to not make mistakes? Hope for the best? Or follow a repeatable, reliable process that accounts for inexperience and human error?

Steve Jones - SSC Editor

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

 
 Featured Contents
SQLServerCentral Article

Efficient Table Migration to a New Schema in T-SQL

Parthprajapati from SQLServerCentral

This article will explain why and how you can easily move tables to new schemas if the need arisees.

SQLServerCentral Article

Microsoft at the PASS Data Community Summit 2023

Steve Jones - SSC Editor from SQLServerCentral

Microsoft will be presenting a number of sessions at the PASS Data Community Summit 2023. Read about their plans for the event and register to come if you can.

External Article

SQL Server Fragmentation Impact with Modern Hardware

Additional Articles from MSSQLTips.com

Read this article to find out if you still need to worry about SQL Server index fragmentation with modern hardware.

Blog Post

From the SQL Server Central Blogs - Deleting a Git Branch–#SQLNewBlogger

Steve Jones - SSC Editor from The Voice of the DBA

I had someone ask me recently about deleting branches. While I had known how to delete a local branch, I had to look up how to delete a remote...

Blog Post

From the SQL Server Central Blogs - SQL Server query runtime is not everything

kleegeek from Technobabble by Klee from @kleegeek

SQL Server query developers, listen up! Query execution time is not everything you should be worried about. You need to examine the parse and compilation time for each of...

Pro T-SQL 2022: Toward Speed, Scalability, and Standardization for SQL Server Developers

Site Owners from SQLServerCentral

Learn how to write and design simple and efficient T-SQL code. This is a hands-on book that teaches you how to write better T-SQL with examples and straightforward explanations.

 

 Question of the Day

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

 

The EXCEPT Query

I have a couple tables that are similar, but the data is different.
CREATE TABLE Original
( productID   INT
, Productname VARCHAR(20));
CREATE TABLE NewVersion
( productID   INT
, Productname VARCHAR(20));
GO

INSERT dbo.Original
  (productID, Productname)
VALUES
  (1, 'PC Case')
, (2, 'Motherboard')
, (3, 'CPU')
, (4, 'RAM')
, (5, 'SSD');
INSERT dbo.NewVersion
  (productID, Productname)
VALUES
  (1, 'PC Case')
, (2, 'Motherboard')
, (3, 'CPU')
, (4, 'DRAM')
, (5, 'SD');
How many rows are returned from this query?
SELECT * FROM dbo.Original AS o
EXCEPT
SELECT * FROM dbo.NewVersion AS nv;
 

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)

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.

Answer: This works fine

Explanation: This works fine. You can encrypt the key with multiple methods of encryption. Ref: CREATE SYMMETRIC KEY - https://learn.microsoft.com/en-us/sql/t-sql/statements/create-symmetric-key-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
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 […]
What happens when an online index rebuild (not resumable) is cancelled? - TLDR:  What is the expected behavior for an index rebuild in online mode (not resumable), which is cancelled on SQL Server 2017?  Is it rolled back completely?  left to be resumed? continues to cause overhead on writes? Details: Hello community!  It has been a while since I posted.  I have been away from SQL Server […]
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 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 […]
CMS server sync - I have an old and a new CMS server. I am trying to keep the CMS groups and registrations in sync. I am trying this with dbatools. Here is what I have, error at bottom.... Remove-DbaRegServer -sqlinstance (my new cms server) -Confirm:$false Remove-DbaRegServerGroup -sqlinstance (my new cms server) -Confirm:$false Export-DbaRegServer -SqlInstance (old cms server) -FilePath […]
can a DB user access that DB objects without a SQL server login? - assume this DB user (UserA) has no sql server login, not in any domain group that has a login. UserA is a windows user (user type) for Database A. UserA has only CONNECT permission to Database A. Can UserA without a login access any objects in Database A?  if so, how?
Query related issue - I have a small query that runs in 1 sec. See an example below. Use DB1 GO select EMPLD, FirstName, Lastname from DB1..Employee where dept = 'IT'   But when I run the same query on the same server but in a different database, it takes 10x + more. Use DB2 GO select EMPLD, FirstName, […]
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
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 […]
 

 

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

 

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