November 3, 2019 at 12:24 pm
I think rollback commit is a good standard approach especially if you have triggers / lots of related tables. peer review prior to committing the changes and also CTE's if the delete is particularly complex or using the temporary tables e.g. select * into, to breakdown the deletions where possible.
For our back office suite of software, which incorporates accounting ledgers, we also have a selection standard sp's for regularly requested data removal e.g. where customers have made errors in processing and are not expecially keen on using e.g. opposing postings to correct. We make a charge for these amendments to discourage the requests and to ensure we get the correct details in writing but maintain the sp's which are often used in the software as for standard data movement/amendment and to standardise our usage and minimise wrong deletions
November 3, 2019 at 3:01 pm
Table triggers will kill your performance and are generally frowned upon. If your database is small and your users are few it won't matter, but this isn't a scalable solution.
I've not had such a problem with table triggers even with column level audit triggers. We did at first because someone prior to the current team tried to make the code portable between tables. It was taking 4 minutes to update just 4 columns of 10,000 row update on a 137 column table (yeah... that's a legacy problem, as well) because of the audit trigger. But it wasn't our code.
After I fixed it, it was within milliseconds of being as fast as the naked insert.
I agree that triggers are generally frowned upon but that most likely got its start because of things like the legacy triggers I talked about above. Triggers aren't the problem... it's the way some (most?) people write them that's the problem... especially for things like column level auditing. They also do "worst practice" things like auditing inserts, saving both the inserted and deleted table entries for audited updates, and other really bad things that they just don't know about.
And, yes... absolutely... I'll also agree that, like anything else, triggers are not a panacea. They have a place (other than the trash bin 😉 ).
--Jeff Moden
Change is inevitable... Change for the better is not.
November 3, 2019 at 3:37 pm
The best thing we ever invested in is Our DR test server - regular automated restore tests and also re-usable as a data playpen - i'm hoping to get our restore tests to an interval of 3 days (so that our playpen is a little more usable)
Totally agreed here. We actually have 2 of these. One is smaller and does nightly restores of two critical databases from the backups that were just taken. Good place to do DBCC CHECKDB on super critical stuff. The largest of those databases aren't huge compared to what some people have but it does weigh in at almost 1TB.
We also have a "DevReview" system. Because I'm the only DBA, it's most "MY" system. The CPU and Memory isn't as beefy as prod but I have slightly more disk space on it than prod. We can restore the entire prod box using a "Nimble" snapshot in less than 5 minutes. We deploy our new code to it before deployments to production so we can do a RedGate compare with production to check for regressive code. We use it to test massive data changes or changes to massive imports both for accuracy and performance. And, in the area of being "MY" server, I use it develop POP code (Proof of Principle Code) that would bring the Dev box to its knees and also because the Dev box doesn't have nearly the data on it that prod does.
It also makes a great place to do DBCC CHECKDB. It's got close to 5TB of space and it's understood that nothing permanent should live there except a little code to help with the "Nimble" flash restores.
We also go one step further. I work really close with the folks on the infrastructure team. We were practicing "DevOps" for about 2 decades (the director of that group and I worked together at a previous company, as well) before anyone ever thought of the buzz word. We know what we can do when we get together to solve a problem and we know what each other can do separately.
With that, not only do we have the 2 DR/Test servers and databases, the infrastructure team also gave me a 1TB scratch drive on Prod and a 700GB scratch drive each on Dev and Staging. They're all reserved for DBA stuff. I made it clear that I'd never store any permanent data or program-ability on those drives in return. I use it for things like large index rebuilds (to keep the MDF files from blowing up when rebuilding a nearly 200GB Clustered Index, for example) and other DBA things. If you don't have one of these DBA-only scratch-drives at least on your prod box... you need to ask for one with the same conditions... exclusive use and never save anything permanent on it. I really lucked out... the Infrastructure Team knows what I have done in the past when I had the room and so they double-downed on the prod box by putting the DBA-Only drive on SSDs like the rest of prod.
It also makes a great place to store seriously temporary (keep for less than 24 hours, normally) copies of tables when doing modifications and updates. Of course, you should also continue to follow proper backup protocols even when this is available.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 3, 2019 at 3:39 pm
p.s. Also, remember that the answer is always "No" unless you ask. If you don't have an "IN" with the infrastructure team or the people holding the purse, also remember that you might only get to ask once... so do your homework, write it down as a formal request, have people review it for concept, etc, and then submit it.
One great selling point is that of "over-provisioning SSDs" and "over-provisioning freespace" to handle sudden success and the immediate growth of data that goes with it. On the Dev and Staging boxes, it can be justified not only as having a temporary space to do important things without having to stand up another box, but in a horrible pinch, it could be used as a "Get back in business" server if your on-premise dedicated prod box ever failed and something went wrong with your DR plans (which will happen to you someday because people don't practice DR well enough or often enough).
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2019 at 9:24 am
I've started using MERGE for updating records because UPDATE just freaks me out. It takes more thinking out but will get faster with practice and it's good to get familiar with merge.
Something like below. I can highlight the inner SELECT to verify the number of records. Maybe wrap in a BEGIN TRANS - COMMIT as belt and braces approach. Our tables all have triggers for insert, update delete to copy rows to corresponding history tables but performance impact on large databases might be unpalatable (our tables are less than a million rows).
MERGE account TARGET
USING (SELECT id,
COUNT(*) OVER() item_count,
0 active_yn,
account_code
FROM account
WHERE account_code = '4c') SOURCE(id,item_count,active_yn,account_code)
ON TARGET.id = SOURCE.id
AND TARGET.account_code = SOURCE.account_code
WHEN MATCHED
THEN UPDATE SET TARGET.active_yn = SOURCE.active_yn;
ok - unless i'm being really stupid - i think you overthought the update/merge issue - feel free to mock me if i got this wrong
update account SET active_yn=0 WHERE account_code = '4c'
MVDBA
November 4, 2019 at 10:35 am
Developers could certainly have read access to production data. If a production change needs to occur to data for whatever reason, the production change should be done by an ops DBA, who would document the request, the change approval, the testing of the change script, the outcome of the testing, the user approval of the test results, the production execution, and the outcome of the production execution. This is the *most basic* form of segregation of duties. Even if your "ops DBA" is just a technician who isn't a developer. If your company is publicly held or owned by a publicly held corporation in the US, this isn't even an option, it's a requirement.
If your developer has write access to your production databases, you're operating on a development database, and operating far outside best practices.
We don't ever allow our Devs access to Production. What we do for them, however, is create a "production copy" database on a daily basis down to a non-prod server for troubleshooting purposes. The data is a day behind at most, this tests our backup restores on a daily basis without interfering with SDLC for releases and gives them access to production-not-production as needed. 3 wins.
Of course, they're not allowed to write to Prod Copy databases, but they can read them with the appropriate PII scramble of stuff they shouldn't need.
November 4, 2019 at 11:35 am
We don't ever allow our Devs access to Production. What we do for them, however, is create a "production copy" database on a daily basis down to a non-prod server for troubleshooting purposes. The data is a day behind at most, this tests our backup restores on a daily basis without interfering with SDLC for releases and gives them access to production-not-production as needed. 3 wins.
.
Carefull with that one - if you are in the UK then you might fall foul of a few GDPR complexities about moving customer data to development servers - to be honest Steve Jones is your man on this one. he knows way more than me. - but certainly data masking is a key point.
I have a similar setup to yourself, and i can see why you have it - It's quite rare that people do automated DR testing (mine failed this morning because someone had added a new filegroup to a database)
My DR databases are locked down and if they need to be used then I grant temporary access and try to make sure that anything sensitive is not exposed (unless the dev really needs that data for the task)
we are far from perfect, but maybe with the input from other people on this site we can progress day by day ..... maybe 🙂
MVDBA
November 4, 2019 at 11:42 am
Brandie Tarvin wrote:We don't ever allow our Devs access to Production. What we do for them, however, is create a "production copy" database on a daily basis down to a non-prod server for troubleshooting purposes. The data is a day behind at most, this tests our backup restores on a daily basis without interfering with SDLC for releases and gives them access to production-not-production as needed. 3 wins.
.
Carefull with that one - if you are in the UK then you might fall foul of a few GDPR complexities about moving customer data to development servers - to be honest Steve Jones is your man on this one. he knows way more than me. - but certainly data masking is a key point.
Did you not see my second paragraph?
November 4, 2019 at 1:47 pm
I use CTEs to do updates and deletes against a table.
{
with cte as (
select id, field2change from table
where id = '1234'
)
update cte
set field2change = '4321'
}
This way with the cte you first verify you only query what you want to update.
And of course I agree with defining the transaction with a transaction name, just in case there's a need to rollback.
November 8, 2019 at 7:10 pm
stelios21 wrote:Not deploying your own code forces you to think about all the things you have to take care will not go wrong when someone else must deploy your code. That person may not have any idea of what your code does and how it should be deployed and does not need to. Not only it forces you to think, it makes you take steps to ensure bad things will not happen.
That's some awesome feedback. Thank you.
I'm right there with the DBA you talked about. I won't deploy by hand. It has to be in the script and the script has to follow all the rules.
I also like your second paragraph... it's exactly the way I've always felt. It's also a great teaching tool because the same types of things also work very well inside stored procedures. The folks I work with have gotten extremely fast and good about doing this for both deployment scripts and within stored procedures.
Thank you for your kind comments. I have always found your comments and articles very instructive and insightful. They are an invaluable aid to all developers. Please keep them coming.
Sometimes, when surrounded by good developers we tend to rely on knowing what they can do and on them knowing what we can do and also knowing we know what they can do. It's easy to slip into a semi-lax attitude that way. There are no excuses.
I have learnt a lot from having to write code for a variety of people with all sorts of expertise levels who I didn't know, never met or would never meet. We learn a lot from this sort of experience, when forced to think that there's no way of going back: once the code is out of your hands, there's no recourse available. This is almost as valuable as being in the company of good developers as it helps you focus and sharpen your game.
November 24, 2019 at 5:34 pm
I prefer "implicit transaction", they are more safe than "begin tran" in case you use "GO" in the script.
SET IMPLICIT_TRANSACTIONS ON
;with a
as
(
SELECT * FROM dbo.account WHERE account_code='4c'
)update a set
active_yn =0
output inserted.*,deleted.*
rollback
--commit tran
November 25, 2019 at 2:30 am
I prefer "implicit transaction", they are more safe than "begin tran" in case you use "GO" in the script.
SET IMPLICIT_TRANSACTIONS ON
;with a
as
(
SELECT * FROM dbo.account WHERE account_code='4c'
)update a set
active_yn =0
output inserted.*,deleted.*
rollback
--commit tran
Hoo boy... This has the propensity to rival the robust rhetoric between bombastic blatherskites on bankers rounding or nulls.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 25, 2019 at 5:45 pm
Hoo boy... This has the propensity to rival the robust rhetoric between bombastic blatherskites on bankers rounding or nulls.
Well, you placed this poetic perfection like a placard to polarize the people. XD
Now that you've mentioned "the can", will you begin opening it by giving us the Cliff's Notes version? I personally don't know what you're referring too.
November 26, 2019 at 8:40 am
OK let me in on the alliteration joke
MVDBA
Viewing 15 posts - 61 through 75 (of 77 total)
You must be logged in to reply to this topic. Login to reply