April 19, 2018 at 7:06 am
k.schouten - Thursday, April 19, 2018 5:09 AMMaybe there is an easy way to use something like "where environment <> 'PROD' " in update and delete statements?
You can do this!!
WHERE @@SERVERNAME = 'MyProductionServer'
April 19, 2018 at 7:08 am
Ken Mercadante - Thursday, April 19, 2018 6:35 AMI use the colored connections in Management Studio, but they're inconsistent and don't always change when changing connections.It would be nice if Microsoft included an option that allowed you to specify a particular database as "Production", and then any Inserts, Updates, Deletes (or any of the other commands that can ruin a career) generate a modal confirmation dialog before executing.
SQL Prompt offers this and so does the SSMS ToolPak I believe.
April 19, 2018 at 7:13 am
ken.trock - Thursday, April 19, 2018 7:02 AMallinadazework - Thursday, April 19, 2018 3:53 AMIt would probably help a great deal if servers had better names... they always seem to be algebraic. How would the average developer know if it was production or not?
Our servers are named like an automobile VIN where each character in the name means something. So one position will either be d for development or p for production. Not always easy to spot!
Invariably they are named for the network admins ease of use not for the humble developer. Although I've heard that Lord of the rings and star wars names are quite popular among network admins. With server names such as Gandalf (production presumably) and Boromir (dev perhaps)? Mordor would be appropriate for some of our database servers.
April 19, 2018 at 7:27 am
jonathan 33820 - Thursday, April 19, 2018 5:19 AMGood article. Great to know about colors!
I always start a new instance of SSMS for each database this way all tabs point to the same dm.
I resize the window that I am not working on making it tall and skinny or or short and wide so it is clear that this is the "other" DB.
When I write an ad hoc insert or delete I write it like this:
Select *
--DELETE
From TableName WHERE
A=1 AND ...
If I execute the whole page then this is just a select. I can test the where clause with the select like the other poster said. The WHERE hanging at the end of the line will break the query if I don't also include the conditions (that often will not fit on the same line).
I drink lot's of coffee.
I really like your A=1 at the end of your where clause but there is the microscopic possibility that you might have a crazy table schema that has "A" as a column name and even more entertainingly it might be an integer with some rows that contain a value of 1!! So how about WHERE 1 = 0 to be on the safe side?
April 19, 2018 at 7:28 am
allinadazework - Thursday, April 19, 2018 7:06 AMk.schouten - Thursday, April 19, 2018 5:09 AMMaybe there is an easy way to use something like "where environment <> 'PROD' " in update and delete statements?You can do this!!
WHERE @@SERVERNAME = 'MyProductionServer'
So the delete statement would only work in production and not in test? 🙂
April 19, 2018 at 8:09 am
It would be helpful if the concept of assertions were introduced formally into the T-SQL programming language, not just for development purposes but also as a sanity check in production. The problem is that the QA environment doesn't always mimic production. Maybe it does in terms of schema, but rarely will it in terms of data. Something like differences in meta data configuration between environments can cause a DML script to behave in unexpected ways.
I sometimes program assertions into my production deployment scripts. If something like the server name or number of rows affected are not what is expected, then I'd rather the script rollback and abort without continuing. This can be done by raising an error with severity level 20 - 25. The RETURN and ROLLBACK statements are included just for completeness, but are really not necessary since SQL Server should rollback and disconnect immediately after a fatal error is raised. Also, fatal error messages are written to the error log.
IF @@SERVERNAME != 'ABC'
BEGIN;
RAISERROR ('Aborting deployment script.
Server ABC was expected.', 20, 1) WITH LOG;
RETURN;
END;
BEGIN TRAN;
DELETE FROM TableABC WHERE CustomerID = 123;
IF @@ROWCOUNT > 1
BEGIN;
RAISERROR ('Aborting deployment script. An unexpected number
of rows were affected by DELETE FROM TableABC.', 20, 1) WITH LOG;
ROLLBACK;
RETURN;
END;
COMMIT TRAN;
GO
Msg 2745, Level 16, State 2, Line 5
Process ID 60 has raised user error 50000, severity 20. SQL Server is terminating this process.
Msg 50000, Level 20, State 1, Line 5
Aborting deployment script. Server ABC was expected.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 19, 2018 at 8:15 am
Phil Factor - Thursday, April 19, 2018 2:53 AMMaybe we should train our subconscious mind into good habits by treating all servers as though they were production servers.
Not the answer for all destructive changes I know, by a long way, but I have trained myself to always wrap any data modification statements in a transaction, regardless of system.
Sanity check row count and commit/rollback for simple ones, confirm with a select statement with rollback for leisurely verification for more complex updates.
Even have a SQL Prompt Snippet to BEGIN TRANSACTION... ROLLBACK TRANSACTION COMMIT TRANSACTION in case I execute the whole script accidentally so it rolls back first
Not perfect I know, I'm lucky that I don't look after any highly transactional, public facing databases, but it has saved my bacon at least a couple of times - so far I haven't left an open transaction and gone to lunch!
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
April 25, 2018 at 5:39 pm
This is a perfect example of why multi-tasking can cause more problems than it solves.
Just like some people think if they just work longer then a 10 hour day they will have an Epiphany.
We need down time, in order to focus, and our bosses need to understand.
Many studies have been done that show performance is directly related to ones ability to shutdown, remove themselves from work.
Nothing better than a Good Nights Sleep to solve a problem.
September 7, 2022 at 8:48 pm
jonathan 33820 - Thursday, April 19, 2018 5:19 AMGood article. Great to know about colors!I always start a new instance of SSMS for each database this way all tabs point to the same dm.
I resize the window that I am not working on making it tall and skinny or or short and wide so it is clear that this is the "other" DB.
When I write an ad hoc insert or delete I write it like this:
Select *
--DELETE
From TableName WHERE
A=1 AND ...
If I execute the whole page then this is just a select. I can test the where clause with the select like the other poster said. The WHERE hanging at the end of the line will break the query if I don't also include the conditions (that often will not fit on the same line).
I drink lot's of coffee.
I really like your A=1 at the end of your where clause but there is the microscopic possibility that you might have a crazy table schema that has "A" as a column name and even more entertainingly it might be an integer with some rows that contain a value of 1!! So how about WHERE 1 = 0 to be on the safe side?
Or even 1=0=2 to be doubly safe. 🙂
----------------------------------------------------
September 7, 2022 at 8:51 pm
On the idea of using RDP to connect to a production system, I can offer an idea. Typically organizations have three environments. Qa, Integration, and Prod. For each of these remote desktops I use a different background color. Green , yellow and red respectively.
Its hard to miss where you are here , provides more protection from a mistake from not knowing where you are logged in.
----------------------------------------------------
September 7, 2022 at 9:41 pm
ken.trock - Thursday, April 19, 2018 7:02 AMallinadazework - Thursday, April 19, 2018 3:53 AMIt would probably help a great deal if servers had better names... they always seem to be algebraic. How would the average developer know if it was production or not?
Our servers are named like an automobile VIN where each character in the name means something. So one position will either be d for development or p for production. Not always easy to spot!
Invariably they are named for the network admins ease of use not for the humble developer. Although I've heard that Lord of the rings and star wars names are quite popular among network admins. With server names such as Gandalf (production presumably) and Boromir (dev perhaps)? Mordor would be appropriate for some of our database servers.
SSMS actually provides a simple method to manage the naming of servers, and I find a lot of people just don't utilize it. Registered Servers allow you to setup local server groups or use a central management server. You can create different folders to organize the servers, provide meaningful names and descriptions and even use a custom color for each connection.
A better solution to server names is to create DNS aliases. This way - you are not connecting to server DC1PSQL1 and hoping it is the right server and instead are connecting to PROD, DEV, UAT or QA. An added benefit is when you decide to upgrade your hardware - you can easily 'migrate' the application servers just by updating the alias.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 28, 2022 at 2:00 pm
When creating DML scripts for deployment, I sometimes implement assertion based logic like in the two examples below.
The use of RAISERROR with severity level 20 causes SQL Server to terminate the connection and thus prevents any following batch processing.
PRINT 'Here we assert script is running on correct server.';
IF @@SERVERNAME != 'DEVSERVER'
RAISERROR ('Script not intended for %s', 20, 1, @@SERVERNAME) WITH LOG;
GO
PRINT 'This should not get executed.'
GO
DECLARE @CustomerID INT = 123;
BEGIN TRAN;
-- Here we assert that no more than 1 row is deleted.
DELETE FROM Customer
WHERE @CustomerID = @CustomerID; -- Oops!
IF @@ROWCOUNT <= 1
COMMIT TRAN;
ELSE
BEGIN
ROLLBACK TRAN;
RAISERROR ('DELETE FROM Customer > 1', 20, 1) WITH LOG;
END;
I wish ANSI SQL / T-SQL would adopt a formal declaration for assertions similar to this:
DELETE FROM CustomerCallLog
WHERE CustomerID = @CustomerID
WITH (ASSERT_ROWCOUNT <= 100);
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 28, 2022 at 3:50 pm
I don't remember ever causing a serious database failure, but once in the old days of flat files I accidentally deleted the Open Order file for a food wholesaler and lost several hours of large institutional food orders that had to be manually re-entered by data entry operators from paper documents. Loading trucks by union employees was delayed by a couple hours.
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply