August 14, 2014 at 12:18 pm
I recently changed from one department to another within my company and found some weird policies for DDL changes from which I would like your opinion. It happens for Oracle, but I'm sure the same could be applied to SQL Server.
To create a table, I have to fill an excel file defining the schema, name, initial rows, growth among other things. This file must be emailed to the dbas to ask for a tablespace (filegroup).
After receiving the tablespace, I have to generate the CREATE TABLE script that includes GRANTs to different users. This will have to be emailed for validation to the dba group including the previous response (if it's not included, they will ask for it even if it's the same dba who sent it).
Once receiving the response, I need to create a ticket to run the validated script.
In addition to that, a teammate must create another ticket on a different sytem.
Finally, after obtaining all authorizations, the same dbas will run the script to create the tables.
This seems over complicated and I'm sure that a single ticket that includes table definition, initial size and expected growth should be enough and DBAs must define the script by themselves.
I'd like to know what do you think about this and how do you do it in your workplaces.
August 14, 2014 at 1:27 pm
We don't typically pre-estimate growth and the like in a justification document, but as a DB Dev we do write ALL scripts for the DBAs to run. There's a very extreme line where I'm at for auditing compliance. Our DBAs don't write architecture, design, or development pieces. We do not ever deploy them other than in the dev sandbox.
Now, new schema shouldn't be coming up that often, so these sound like evolved self-defense policies that have come up over the years to give the DBAs a chance to defend themselves for runaway BLOB storage or equivalent items that the devs 'forgot to mention' and they have something that they can point at. It also gives them paperwork for working with the SAN team to be able to request space and the like.
While the ticketing system may seem a bit much, I've also got an equivalent at my location. One's used for inter-department ticketing for the QA/UAT systems, another much more robust, and manager only, system is used for actual production deployment approvals and the like.
So... no, not really. That seems pretty status quo for me.
Edit: Whoops, didn't mean to leave the quote in, just wanted it for reference.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 14, 2014 at 1:43 pm
I think the multiple requests and subsequent multiple tickets just to have one table created is a bit much. Do the Oracle guys really create a new tablespace for each table? Seriously? When I worked in Oracle, we never had such a thing, unless you had (like Kraig pointed out) lots of LOB storage or something and they wanted to be able to defragment it by exporting and importing it again to rebuild the tablespace. For normal tables, however, I can't see the reason behind it. BTW, initial rows is meaningless with the table definition from the DDL to create the table. Row estimation is usually (albeit not always) just a wild guess.
Like Kraig said, this is probably a bureaucracy that built up over the years to defend against some bad behavior. Nonetheless, I think it's a bit too much and probably gets in the way of getting things done. I don't envy you having to deal with it.
August 14, 2014 at 2:54 pm
Sounds like a bit of a waste of time and somebody who decided to put his power to the test by creating a bunch of time wasting policy.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 14, 2014 at 3:28 pm
I don't understand the need for the double loop. It should be enough to either provide the table description (including columns) or the CREATE TABLE statement (including permissions and growth expectations).
If it's just schema, name, initial rows, growth, how will the DBA team evaluate if the table is needed the way it is or if there are alternatives?
Who's in charge to evaluate normalizaion/logical structure of the table or even redundancy to already existing information? (sounds like another department is needed here 😉 ).
I do understand the need to separate table definition and responsibility to run the query. But both should understand what the table is needed for...
August 15, 2014 at 5:53 am
Multiple requests seems nuts. In fact, I prefer to let the developers just develop. You may think you need a table that looks like X, get part way into your code and then realize you need it to look like X+1, X-3 or even Y. Until you have it solidified, why would I make you bother me. Further, in terms of setting up automated deployments and automated testing, this is about the worst possible way to go. You should be working out of a source control system where I, as a DBA, can go at any time to review the structures and code.
Nope, this isn't how I'd set it up.
"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
August 25, 2014 at 9:38 am
Looks like yours is a very controlled, monitored and process oriented system. This kind of functioning happens, when the DBA , SAN/Infrastructure et al is represented by different vendors or contracting companies. Yes, they can combine first two steps into one, but probably that would involve too many meetings, changes to tracking et al. You might have to bring up to a Manager and show the hours wasted in this process and how changes can provide cost cutting 😀
August 25, 2014 at 10:03 am
The sad reality is that "very controlled, monitored and process oriented system" is just a nice wish as there are many things that just get into production and haven't been fully tested. As a matter of fact, I could send one script to validate and change it after validation and it will be deployed into production without problems.
So even with all the bureaucracy, the process is far from being perfect.
I wonder if we'll ever get to a point where changes are really controlled and validated without having to waste all that time to everyone involved (developers, dbas, bosses, etc).
October 1, 2014 at 8:41 am
Luis Cazares (8/25/2014)
The sad reality is that "very controlled, monitored and process oriented system" is just a nice wish as there are many things that just get into production and haven't been fully tested. As a matter of fact, I could send one script to validate and change it after validation and it will be deployed into production without problems.So even with all the bureaucracy, the process is far from being perfect.
I wonder if we'll ever get to a point where changes are really controlled and validated without having to waste all that time to everyone involved (developers, dbas, bosses, etc).
I can't know why such things happen but that whole process sounds more like some silly BSOFH junk.
I have a process at work where I've given the Dev's SA privs on the Dev box to do just about anything they need to in return for the understanding that they shall not grant privs, create users, linked servers, new databases, or any of the other things that you'd expect a DBA to do. While that sounds a bit care free on my part, it allows them great freedom to try new things, encourages experimentation/innovation, and keeps me from being a roadblock in Dev. I also do Point-in-Time backups on the Dev Box because mistakes will (and have) happen and I'd rather spend a couple of minutes to setup a restore than to have the Devs down because of a mistake.
The Devs have Read and View Definition privs on the staging and prod boxes so that they can troubleshoot problems. I also sit right in the middle of the Devs so that I can hear can show them "how to" if they have a question. It also helps them understand that we're all part of the same team to get things done and encourages them to approach me with a problem before they've wasted a lot of time doing things the "wrong" way (and yes, we have Company SQL Specs that I wrote that they have to follow, as well). It also allows me to overhear their mumblings when they might be having a problem that I can help with and it also allows me to help protect the devs when a demanding user shows up with an unreasonable request.
I also do 100% peer reviews (and the auditors love that little nuance) where I check for form, function, and scalability. The Devs cannot promote their own code to Staging or Prod. The peer reviews also give me a chance to do some serious mentoring for the types of things they have to do. There are times that we'll make a change together right there in the peer review. There are also times where I'll send them back to make a change (I don't have the time to fix everything) but with new knowledge (I have lots of demo code I use) on how to better solve a problem.
Does it take a fair bit of my time? Yeah, but I'm a hybrid DBA (combination of Designer, System, Apps, and a couple of other hats) and, in the long run, I've found that it's much easier and more effective to enable them to do things the right way than it is for me to be just a road block for the wrong way. More shops should figure that out and managers need to get the idea, as well.
It's all been worthwhile because when I first reported to the company, the 8 CPU box was averaging 40% with several long winded 100% plateaus (causing multiple "outage" reports each day) and Logical Reads where measured in hundreds of Tera-bytes during normal working hours. We now average 5% cpu, Logical Reads are 4 orders of magnitude less, and we haven't had an "outage" report in over a year. And, get this... the devs "get it" and have actually started to brag about improvements and performance problems they've solved during development. It's become a "do it right" culture instead of a "get it off my plate" culture and they're actually turning out code faster than they used to because 1) of the formatting/documentation requirements I put on them (troubleshooting/researching code has become much easier) and 2) they've developed a real sense of pride in their code because I brag about their successes, as well!
--Jeff Moden
Change is inevitable... Change for the better is not.
October 2, 2014 at 10:14 am
Jeff Moden (10/1/2014)
Luis Cazares (8/25/2014)
The sad reality is that "very controlled, monitored and process oriented system" is just a nice wish as there are many things that just get into production and haven't been fully tested. As a matter of fact, I could send one script to validate and change it after validation and it will be deployed into production without problems.So even with all the bureaucracy, the process is far from being perfect.
I wonder if we'll ever get to a point where changes are really controlled and validated without having to waste all that time to everyone involved (developers, dbas, bosses, etc).
I can't know why such things happen but that whole process sounds more like some silly BSOFH junk.
No, the BSOFH would just have murdered anyone who disturbed his peacefil life. But I agree it's utter junk.
I have a process at work where I've given the Dev's SA privs on the Dev box to do just about anything they need to in return for the understanding that they shall not grant privs, create users, linked servers, new databases, or any of the other things that you'd expect a DBA to do. While that sounds a bit care free on my part, it allows them great freedom to try new things, encourages experimentation/innovation, and keeps me from being a roadblock in Dev. I also do Point-in-Time backups on the Dev Box because mistakes will (and have) happen and I'd rather spend a couple of minutes to setup a restore than to have the Devs down because of a mistake.
The Devs have Read and View Definition privs on the staging and prod boxes so that they can troubleshoot problems. I also sit right in the middle of the Devs so that I can hear can show them "how to" if they have a question. It also helps them understand that we're all part of the same team to get things done and encourages them to approach me with a problem before they've wasted a lot of time doing things the "wrong" way (and yes, we have Company SQL Specs that I wrote that they have to follow, as well). It also allows me to overhear their mumblings when they might be having a problem that I can help with and it also allows me to help protect the devs when a demanding user shows up with an unreasonable request.
I also do 100% peer reviews (and the auditors love that little nuance) where I check for form, function, and scalability. The Devs cannot promote their own code to Staging or Prod. The peer reviews also give me a chance to do some serious mentoring for the types of things they have to do. There are times that we'll make a change together right there in the peer review. There are also times where I'll send them back to make a change (I don't have the time to fix everything) but with new knowledge (I have lots of demo code I use) on how to better solve a problem.
Does it take a fair bit of my time? Yeah, but I'm a hybrid DBA (combination of Designer, System, Apps, and a couple of other hats) and, in the long run, I've found that it's much easier and more effective to enable them to do things the right way than it is for me to be just a road block for the wrong way. More shops should figure that out and managers need to get the idea, as well.
It's all been worthwhile because when I first reported to the company, the 8 CPU box was averaging 40% with several long winded 100% plateaus (causing multiple "outage" reports each day) and Logical Reads where measured in hundreds of Tera-bytes during normal working hours. We now average 5% cpu, Logical Reads are 4 orders of magnitude less, and we haven't had an "outage" report in over a year. And, get this... the devs "get it" and have actually started to brag about improvements and performance problems they've solved during development. It's become a "do it right" culture instead of a "get it off my plate" culture and they're actually turning out code faster than they used to because 1) of the formatting/documentation requirements I put on them (troubleshooting/researching code has become much easier) and 2) they've developed a real sense of pride in their code because I brag about their successes, as well!
Looks to melike the right way to do things. I haven't a clue why typical IT managers have never discovered this, unless it's because they are not very bright (actually, maybe that applies to most managers).
Tom
October 2, 2014 at 10:28 pm
Thanks for the feedback, Tom. And, yeah... I wish more managers "got it".
--Jeff Moden
Change is inevitable... Change for the better is not.
October 3, 2014 at 7:34 am
The double-loop is a little odd, but otherwise I don't see anything that makes me really question the overall idea. Including the table def + the script in one ticket should be enough to me, but it might bear investigating how this process evolved. There could be a semi-logical reason for it. It might just be they wanted lots of controls in place. Maybe different people did stuff along the way in the past and those positions no longer exist. I can see a use for the spreadsheet + the script if they use the spreadsheet to feed some documentation system. The script would eliminate errors from generating something out of the spreadsheet.
Jeff, love the streamlining you've done and that makes a lot of sense. We have something similar in place, though dev work is typically done on local machines before being pushed up to the shared dev servers and then further up the chain. We also had a DBA who monitored our production system for really bad SQL usage and brought those to our attention. We worked with him and he worked with us so it wasn't a case of "us vs them". We also had a small DevOps team going at one point to help get our systems in line. Of course, not all shops will do that and we took some time to get there, but it helped quite a bit.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply