June 30, 2016 at 1:27 am
Technically this is about SQL Server, but it's not about a particular version of SQL Server, so I decided to place the question here.
As a DBA, for most of us, most of the time, the production servers are sufficiently locked down that no changes are made to the system unless we are the ones making them. However, sometimes, in some situations, changes that we haven't approved, or that we don't even know about, get on to our production servers.
I have two questions:
Where do these changes come from on your servers?
For example, I worked for an organization that gave every single individual 'sa' privileges. Changes came from everywhere, randomly.
What kind of changes do you see most often?
Is it just modifications to a view or a procedure, or do you find new databases?
Please help me out by answering these questions, and, by spreading the word on this research.
I will post back here with a link to the article after I get it done.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 30, 2016 at 4:18 am
Grant Fritchey (6/30/2016)
Technically this is about SQL Server, but it's not about a particular version of SQL Server, so I decided to place the question here.As a DBA, for most of us, most of the time, the production servers are sufficiently locked down that no changes are made to the system unless we are the ones making them. However, sometimes, in some situations, changes that we haven't approved, or that we don't even know about, get on to our production servers.
I have two questions:
Where do these changes come from on your servers?
If not from our internal DBA team (and we did it and forgot about it), they come from the corporate DBAs who also have sa access to the servers (they manage the POD farms). But that is a rare occurrence.
Grant Fritchey (6/30/2016)
What kind of changes do you see most often?
Modifications to jobs and DBA monitoring procs mainly. Rarely do we see changes to table schemas and application procs that aren't documented.
The most frequent change is to data, but that is usually done by a business user through the client or by a business user requesting an operational (data change) ticket with wrong information. In which case, according to them, it has to be the DBA team's fault because they had nothing to do with it. 😉 Of course, now we keep history in hidden tables so we can prove it wasn't us.
Grant Fritchey (6/30/2016)
Is it just modifications to a view or a procedure, or do you find new databases?
I've occasionally found new databases (maybe about 3 in the 11 years I've been here), but that was done by an internal DBA who can explain what happened and why it's there. Usually we send each other emails. Sometimes we forget. But mostly it's proc changes done to our monitoring procs which we document in a comments section of the proc.
Hope that helps.
June 30, 2016 at 5:03 am
I agree with Brandie that most of the changes are to data. This is a small shop so on occasion developers need to change data using SSMS.
We have a review process, but the occasional oops gets through.
Years ago, before review started, a IT staff person did an update without a where clause. (No begin tran/rollback) Lucky for us we had a hot copy to copy from.
I think I now have them trained to ALWAYS use begin tran/rollback (yea, right).:-)
If I do not get creative, things are mostly stable. (What do you mean everyone is locked out? 😀 )
June 30, 2016 at 5:28 am
In the last place I worked, the vendor's support team had full access to an application database. Not only that, but they also had access to remotely access our network whenever they wanted. To fix a support issue on once occasion, they created a trigger in the database that caused all sorts of problems.
John
June 30, 2016 at 6:04 am
Everyone,
Thanks for the feedback so far. Keep it coming. Any details on any of the horror stories would be very welcome.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 30, 2016 at 6:33 am
Similar to John Mitchell's story, in that external application vendor had full access to a database server that they were maintaining for us. Unfortunately, they had a very heavy-handed release process that would get rid of everything that didn't exactly match the schema they were expecting - including a couple of useful views that we put in and were heavily used in the reports against this system.
So I wrote a procedure to recreate the view - obviously, that got destroyed too.
Create the procedure in a system database - that went.
Ended up creating a scheduled task on a different server to connect and rebuild the views if required.
Details? C'mon, this was back when SQL 2000 was reasonably fresh.
Thomas Rushton
blog: https://thelonedba.wordpress.com
June 30, 2016 at 6:38 am
Then there was the place with the very stringent change control process - everything, no matter how minor, had to go through the full CAB.
Unless you were the Ops team. In which case everything could be run through by them as "operational changes", which bypassed CAB. But we DBAs weren't part of the Ops team... "So, about this database that's chewing up the disk..." "What database? Where did that come from?"
Thomas Rushton
blog: https://thelonedba.wordpress.com
June 30, 2016 at 6:44 am
djj (6/30/2016)
I agree with Brandie that most of the changes are to data.Years ago, before review started, a IT staff person did an update without a where clause. (No begin tran/rollback) Lucky for us we had a hot copy to copy from.
I think I now have them trained to ALWAYS use begin tran/rollback (yea, right).:-)
Oh, yes. Where "IT staff person" = "support chap who should have known better". On multiple occasions.
And then there was the time (no, wait. Several times) on a conveyancing system where house prices were all set to the same value. That was fun to unpick. Never did find out what happened there, but suspect it was a bug in our in-house software that was never uncovered. (The company in question doesn't exist any more, though not for this reason...)
Thomas Rushton
blog: https://thelonedba.wordpress.com
June 30, 2016 at 7:03 am
I'm wondering if I'm going to be the odd man out here...
My servers, both QA and production, are locked down to where I've not yet seen a "surprise" database. We've got two people with sysadmin privileges (me and my backup) and that's it.
Now, the developers (not the users,) do have db_owner on the databases they work on, both in QA and production, but that's more due to how we're set up here (think of me as the equivalent of an Azure admin, I keep the servers humming and updated, what you do in your DB is your problem {until you muck up and need a restore to recover something}) Some of the Devs also have sufficient privileges to create Agent jobs, but very rarely do they do this (and even then it goes through our CCB process.)
So, no surprise changes in what I'm responsible for.
June 30, 2016 at 8:18 am
jasona.work (6/30/2016)
I'm wondering if I'm going to be the odd man out here...
Not quite. True surprises are rare here. Our surprise databases show up in non-prod environments where the Devs aren't supposed to have access or as monitoring databases in Production from the other DBA team.
I think the biggest surprise was when we found out they (corporate DBAs) were letting the process auto-spawn new filegroups on drives we specifically requested for an AOR's database set (which was a disk space hog and needed to be separate from the transaction databases). Oh, did I get mad and put the kibosh on that. Opened up a help desk ticket which practically screamed "Stay on your own damn drive."
But it's been a few years since we've had real surprise changes. As I said, most of them are front-end data problems caused by users (or documented requests for back-end data updates where they put in the wrong information). We've got monitoring down so pat for SOX that every production security update, every login, and every schema change are monitored by a corporate SOX group who actively review all those details and compare then to change requests in our system. Then push back demanding explanations when something got through without a change record.
In those cases, it's mostly my team removing individual access permissions as we transition everyone over windows groups. But since we know about it, it's not a surprise change for us. Just for the SOX peeps.
June 30, 2016 at 8:24 am
jasona.work (6/30/2016)
I'm wondering if I'm going to be the odd man out here...My servers, both QA and production, are locked down to where I've not yet seen a "surprise" database. We've got two people with sysadmin privileges (me and my backup) and that's it.
Now, the developers (not the users,) do have db_owner on the databases they work on, both in QA and production, but that's more due to how we're set up here (think of me as the equivalent of an Azure admin, I keep the servers humming and updated, what you do in your DB is your problem {until you muck up and need a restore to recover something}) Some of the Devs also have sufficient privileges to create Agent jobs, but very rarely do they do this (and even then it goes through our CCB process.)
So, no surprise changes in what I'm responsible for.
But also no responsibility for what goes on inside a given database. So, crazy though it may be, they could be developing directly against the production server...
Don't mind saying, that gives me the heebee jeebees.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 30, 2016 at 8:45 am
Grant Fritchey (6/30/2016)
jasona.work (6/30/2016)
I'm wondering if I'm going to be the odd man out here...My servers, both QA and production, are locked down to where I've not yet seen a "surprise" database. We've got two people with sysadmin privileges (me and my backup) and that's it.
Now, the developers (not the users,) do have db_owner on the databases they work on, both in QA and production, but that's more due to how we're set up here (think of me as the equivalent of an Azure admin, I keep the servers humming and updated, what you do in your DB is your problem {until you muck up and need a restore to recover something}) Some of the Devs also have sufficient privileges to create Agent jobs, but very rarely do they do this (and even then it goes through our CCB process.)
So, no surprise changes in what I'm responsible for.
But also no responsibility for what goes on inside a given database. So, crazy though it may be, they could be developing directly against the production server...
Don't mind saying, that gives me the heebee jeebees.
No disagreement from me on that, and I know there's occasionally some dev work against production. One Dev was doing it (back when I started,) because his DBs were too large for the QA box. Did some work with him, compressed a few tables, he did some purging, and we got him over onto the QA box.
For the most part, in part due to the policies in place throughout the organization, I think the devs are fairly well-behaved. Doesn't mean it doesn't happen, but I can't control everything.
Downside is, yeah, if a dev blows up their production DB because they were being careless, I get pulled in to help clean it up. Ideally, if the guidelines we have to follow were followed more closely, every application would have a dedicated server and dedicated DBA / developer, in which case it would be easier to prevent work on production.
July 5, 2016 at 12:20 pm
Following are the standard changes we would normally consider;
1. Database backup - In case if we need to do outside of the regular schedule.
2. Online Index Rebuild - To improve performance of certain stored procedure or query as part of production performance issue. This is outside regular index maint job.
3. Manual statistics update - This is also outside the regular stats maint job.
4. Log File Shrink - To release unused space to file system in situation where we need urgent free space or to maintain no.of VLFs.
5. Create Index - Certain cases we might need to add additional indexes to improve performance.
6. Grow data file - This is to avoid auto growth where system is busy.
7. Partition maintenance - Usually there is a job to do this. However sometimes we may need to add/remove partitions or filegroups.
8. Add storage - This is a storage team task' but DBA need to extend the drive after allocating additional space.
9. Data File Shrink
Hope this helps.
Susantha
July 5, 2016 at 12:26 pm
Following are the standard changes we would normally consider;
1. Database backup - In case if we need to do outside of the regular schedule.
2. Online Index Rebuild - To improve performance of certain stored procedure or query as part of production performance issue. This is outside regular index maint job.
3. Manual statistics update - This is also outside the regular stats maint job.
4. Log File Shrink - To release unused space to file system in situation where we need urgent free space or to maintain no.of VLFs.
5. Create Index - Certain cases we might need to add additional indexes to improve performance.
6. Grow data file - This is to avoid auto growth where system is busy.
7. Partition maintenance - Usually there is a job to do this. However sometimes we may need to add/remove partitions or filegroups.
8. Add storage - This is a storage team task' but DBA need to extend the drive after allocating additional space.
9. Data File Shrink
Hope this helps.
Susantha
July 5, 2016 at 2:04 pm
If you use a continuous integration tool like TeamCity or an automated deployment tool like DbUp, then it's entirely possible for unexpected object or securoty related DDL operations to get mixed in with otherwise valid modications. For example, what should be a simple CREATE PROCEDURE script might contain CREATE INDEX, CREATE USER, or GRANT EXEC statements, either because the developer thought they were necessary or inadvertently generated them while in the process of leveraging a schema comparison tool. That's why continuous data integration unnerves me.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply