June 1, 2010 at 9:06 am
I became the DBA at a shop about 4 months ago. This place never had a DBA before me and the basic M.O. was do things to get them done. That meant wide open privileges for everyone, no planning, everything done in T-SQL. Aside from some of the more obvious things that need attention (like security), I'm trying to implement some standards for what we do within stored procedures. Because no one here took the time to learn anything about SSIS, all processing is done within stored procedures or, in many cases, straight T-SQL in a SQL Agent job.
At a high level, a typical proc/SQL Agent job here looks something like this:
- declare and initiate a bunch of variables
- run a bulk insert statement to populate a table
- drop and recreate a "temp" table
- use a cursor to process the data
- use xp_cmdshell to back up the files processed
Keep in mind we repeat this for every client we have, since paths to files are hard-coded. And since many are not stored procs, there is no way to control code, parameterize, etc.
To get back to the point, my thought process is a stored procedure should only be used to process the data. In the above example, I see an SSIS package with several steps - a data flow to import the data, an Execute SQL to run the procedure or T-SQL, maybe a couple of CmdTasks to back up the files. Throw in a configuration and some logging, and we're good to go with one copy for many clients.
Am I off base with this? I'm having a very hard time convincing the developers we need to do this. They are still in the mind set of "get things done", even though they are going further and further down a road we don't want to be on. And until I can completely lock down the servers, they can still implement this type of code.
Does anyone have any examples, links, policies they've implemented that cover which SQL tools should be used for which tasks? Is there any best practice, industry standard, etc. that helps guide folks in this sort of thing?
Thanks in advance.....
Pete
June 1, 2010 at 12:18 pm
Let me start by saying I totally agree with you - it will be nice to transform your free-range environment into a pro environment.
Having said that, let me ask the most important question... do you have a sponsor? is upper management going to back you up?
Forget about the developers, what you need is to convince upper management. Once you get a mandate and you have a plan... you just notify the developers.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 1, 2010 at 1:00 pm
Ah, upper management....we're in the process of a reorganization. The person I and my co-workers report to is not someone who is technical, nor someone who I think really understands why we need to take a little longer architecting a system or process. The people who do understand completely, I don't report to. So, for all intents and purposes, I'm in limbo.
That being said, I was told I am sort of the policeman when it comes to this stuff. I don't think I have carte blanche, but I'm in the process of locking everything down, so that we have a true development environment set up. I'll find out soon enough if I really am free to start saying no to the developers.
June 1, 2010 at 2:16 pm
Good! I"ll keep my fingers crossed 😀
If possible I would go with a three tier development model: Dev, Test, Prod.
Let developers play in Dev but only DBA has the power to promote code and apply ddl to both Test and Prod - Bottom line is, you can only be held responsible for an environment you control.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 1, 2010 at 2:33 pm
PaulB-TheOneAndOnly (6/1/2010)
If possible I would go with a three tier development model: Dev, Test, Prod.Let developers play in Dev but only DBA has the power to promote code and apply ddl to both Test and Prod - Bottom line is, you can only be held responsible for an environment you control.
That's the easy part. I already have most of that in place and am getting ready to lock down production.
What I am having trouble getting people to understand - and am looking to this community to make sure I'm not the one in left field - is they need to eliminate practices such as using cursors, constantly running drop/create table, backing up/moving/copying files using xp_cmdshell, using 'bulk insert' to load data within a proc, etc.
I'm trying to modularize these tasks in SSIS with config files. I thought our programmers would understand the concept of reusable code, but they continue to write custom code for every client when something generic can be used in many cases. I want to see them get away from using T-SQL for tasks that don't require T-SQL.
June 1, 2010 at 2:54 pm
Peter Veilleux-436764 (6/1/2010)
PaulB-TheOneAndOnly (6/1/2010)
If possible I would go with a three tier development model: Dev, Test, Prod.Let developers play in Dev but only DBA has the power to promote code and apply ddl to both Test and Prod - Bottom line is, you can only be held responsible for an environment you control.
That's the easy part. I already have most of that in place and am getting ready to lock down production.
What I am having trouble getting people to understand - and am looking to this community to make sure I'm not the one in left field - is they need to eliminate practices such as using cursors, constantly running drop/create table, backing up/moving/copying files using xp_cmdshell, using 'bulk insert' to load data within a proc, etc.
I'm trying to modularize these tasks in SSIS with config files. I thought our programmers would understand the concept of reusable code, but they continue to write custom code for every client when something generic can be used in many cases. I want to see them get away from using T-SQL for tasks that don't require T-SQL.
I agree with almost all that you're trying to do. However, there is always more than one way to get a job done. While I use SSIS, I also use T-SQL, and I think that frequently I can write T-SQL code to bulk-insert data into tables far faster than in SSIS. However, SSIS does have it's powers. So, for me it comes down to a decision - sometimes SSIS is a better choice, because of (take your pick: loop containers, yada yada), sometimes it doesn't matter which one to use and it's a personal preference as to which one to use.
This all comes down to this: don't force things "just because". If you are confident in your reasons and the developers disagree, then maybe the issue is really about training the developers - if they are ignorant of features, they can't be expected to utilize them. You're also asking them to change, and most people resist change - it's easier to do it the way they have always been doing it. (After all, they know it works.)
Ensure you let your developers develop - what they do is what makes money/value for your company.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 2, 2010 at 6:44 am
Thanks, Wayne. That's sound advice. You're right, there is always more than one way to get a job done, especially in the DBA world. Maybe I can put some more thought into this and see where I can make it more of a give-and-take. There are some things I won't bend on (eliminating cursors, drop/create table), but others that I could give in on, especially if it will make for a happier team without sacrificing the needs of the business.
Ultimately, I'd love to do a side-by-side comparison of the way things are currently done and the way I'd like to see them done. That would clearly point out where we could make improvements and where we could stay with the status quo. Being the only DBA here and having a pile of work as tall as I am, I don't think this will happen for a bit.
June 3, 2010 at 6:11 am
Phase in the changes that you want to implement. This way you should meet less resistance and the developers aren't looking at you like the hatchet man! You could perhaps start with having them convert the TSQL to sprocs. This will give them exposure to creating sprocs and you can call them in the jobs steps making it easier to edit. Once they realize the benefit of sprocs they'll realize what they could have achieved by using them earlier.
Later on as new functions, jobs, etc are being developed you can have them write them in SSIS, if it is best suited as an SSIS package.
Steve
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply