December 1, 2016 at 7:12 am
Hello,
Just started a new DBA position. I am seeing things that seriously need some attention. I would rather not do things as the last DBA did.
One thing that I want to change is the running of adhoc queries, everyday and twice a day. Yes, here it is set up that the developers send in requests to update or delete rows in UAT and then, once approved, in Prod. They change the data so much that they have been limited to twice a day to make these changes.
In one week, I have missed the 10:00 a.m. time slot twice because I was busy doing other things. OK, time to automate.
What would be the best way to handle this daily submitting and executing of the developer's scripts?
1. Save the requests as .sql files and place them in a shared folder. Then have SSIS poll for that folder at the execution times and execute the scripts? I've started this, but putting the objects (Foreach loop container, figuring out how to read the sql file, etc. will take some time.
2. Create scheduled jobs on each server that execute at both of the scheduled run times. Then each time I get a request, add a step to the job with the current query request?
Or is there a better path to take to accomplish the above.
I appreciate the ideas that you may have.
Thanks.
Tony
Things will work out. Get back up, change some parameters and recode.
December 1, 2016 at 7:20 am
I'd go with the SSIS option because I know how to do it and it's pretty simple. This article might help: http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/99287/
Another option, which might be preferred by others is to use Powershell to run the scripts. I know it can be done, but I can hardly spell Powershell, don't make me use it.
December 1, 2016 at 7:20 am
Tony
If you're running so many ad hoc queries, that points to something missing in the application. Is there a pattern to the kind of modifications that you're being asked to do?
John
December 1, 2016 at 7:37 am
Hello,
I've only been here a week. However, no pattern yet. Update script. Insert rows. They come in from about 6 developers.
Thanks.
Tony
Things will work out. Get back up, change some parameters and recode.
December 1, 2016 at 7:42 am
One wrinkle I would add, however you build the deployment, SSIS or Powershell (I'd lean to Powershell personally), have that process create a script from the collection of ad hoc scripts and then run that script in UAT. Then, when the UAT passes, run the script, without rebuilding it. That way, no one "accidently" drops a script in to the folder that hasn't been run on UAT.
"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
December 1, 2016 at 7:46 am
WebTechie (12/1/2016)
Hello,I've only been here a week. However, no pattern yet. Update script. Insert rows. They come in from about 6 developers.
Thanks.
Tony
Tony
If developers are involved on a live system, perhaps it's to write data fixes in response to issues highlighted by users? If you're getting that many data fixes, you need to find out the root cause and fix that. If, on the other hand, it's routine addition of data to tables, there really ought to be a user interface to handle that. Surely your developers are far too well-paid to be spending their time doing stuff like this!
John
December 1, 2016 at 8:12 am
I would like to have an app built also.
However, as I mentioned, I am the new guy on the block. Senior guy, but still the new guy.
I don't want to upset everyone when management believes this is routine and ok.
I am leaning toward the SSIS. I just need to figure out how to read a sql file into SSIS and then execute the commands.
Thanks.
Things will work out. Get back up, change some parameters and recode.
December 1, 2016 at 8:33 am
WebTechie (12/1/2016)
I would like to have an app built also.However, as I mentioned, I am the new guy on the block. Senior guy, but still the new guy.
I don't want to upset everyone when management believes this is routine and ok.
I am leaning toward the SSIS. I just need to figure out how to read a sql file into SSIS and then execute the commands.
Thanks.
To read the sql files, you'll use a Foreach Loop. In the Collection tab, define the Enumerator as Foreach File Enumerator. Set the Folder path and Files as *.sql (or whatever filter you need). Be sure to use Fully qualified name. Use a Variable to store the file name in Variable Mappings.
Create a File connection using a dummy file. Then using the Expressions property, set the ConnectionString to the variable you used for the name.
Inside the loop, you can use a SQL Task. Choose SQLSourceType as File connection and use the File Connection as the source.
That should give you the basic way to do it. You'd still be missing the moving of the files, which is explained on the article I shared previously.
December 1, 2016 at 12:51 pm
Luis,
I followed all of that up to "use the File Connection as the source".
I'm not sure where you are setting the source to be the file connection?
Thanks.
Tony
Things will work out. Get back up, change some parameters and recode.
December 1, 2016 at 1:19 pm
WebTechie (12/1/2016)
Luis,I followed all of that up to "use the File Connection as the source".
I'm not sure where you are setting the source to be the file connection?
Thanks.
Tony
I'm sorry, I must have confused my ideas. In the SQL Statement area for the Execute SQL Task, the properties should be like this:
ConnectionType: This depends on the type of connection for the server that will run the script, probably OLE DB.
Connection: The name of the connection for the server that will run the script.
SQLSourceType: File connection (literally)
FileConnection: Name of the file connection you just created (the one with the variable as the connection string)
I hope this makes it clear.
December 1, 2016 at 3:29 pm
I wouldn't make another move without talking with the Developers and finding out what and why there is a need for so many data changes to prod and let them tell you what the logic behind it is instead of looking for patterns that may or may not exist. If you pose it all in a very helpful manner, it'll also make it easier to work with the Developers in the future because you'll be a member of their club especially if you make it so they don't have to do any manual work to do this every day. Remember, it's probably as big a PITA for them as it is for you. If you're lucky, they will already have some automated process to define the data that needs to be changed so often and you'll easily be able to automate the whole thing from womb-to-tomb.
Once you have it automated, share with them and teach them what and how you did it and why. They'll appreciate the mentoring, as well.
As a bit of a side bar, until they needed more room and had to move, I used to sit right in the middle of the Developers so I could listen for any problems they might have on the database side and I also used to help keep people from proverbial "drive by shootings". I still use the 100% code reviews that I do as a chance to mentor and they seem to appreciate it. When they have a problem, they voluntarily ask my opinion or for my help. They take great pride in writing nasty fast code and also understand that doing it right the first time really doesn't take any extra time at all and saves a huge amount of rework time. It's an amazing group effort between Devs'n'DBAs that I've not seen much of in other companies I've worked for.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 5, 2016 at 6:58 am
Have you maybe thought about creating a file watcher task that can be used as a trigger to automatically run a script when it is dropped into a folder?
December 5, 2016 at 9:26 am
kevaburg (12/5/2016)
Have you maybe thought about creating a file watcher task that can be used as a trigger to automatically run a script when it is dropped into a folder?
That would help the DBA. The real question is, why do the Developers need to make daily corrections to data? Something's wrong there. If the corrections are from customers, then the process needs to be automated in full instead of the current throws both the Developers and the DBA are going through.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 5, 2016 at 10:01 am
Jeff Moden (12/5/2016)
kevaburg (12/5/2016)
Have you maybe thought about creating a file watcher task that can be used as a trigger to automatically run a script when it is dropped into a folder?That would help the DBA. The real question is, why do the Developers need to make daily corrections to data? Something's wrong there. If the corrections are from customers, then the process needs to be automated in full instead of the current throws both the Developers and the DBA are going through.
I agree 100% with you but unfortunately I work in a Company that has an unsupported Version of Axapta as ist ERP solution running on unsupported Hardware, utilised by unsupported applications. Because everything around this (BI, EDI, PLM etc) is based on this database we find updates being rolled out in the frequency that the OP is experiencing. Oh yes, and the anticipated turnover for 2017 is €2bn......a Company that truly doesn't care about ist data.....
I offered the file watcher solution because I truly feel for him and the Situation and the very real possibility that in his Company he is the only one that cares.....
December 5, 2016 at 4:36 pm
kevaburg (12/5/2016)
Jeff Moden (12/5/2016)
kevaburg (12/5/2016)
Have you maybe thought about creating a file watcher task that can be used as a trigger to automatically run a script when it is dropped into a folder?That would help the DBA. The real question is, why do the Developers need to make daily corrections to data? Something's wrong there. If the corrections are from customers, then the process needs to be automated in full instead of the current throws both the Developers and the DBA are going through.
I agree 100% with you but unfortunately I work in a Company that has an unsupported Version of Axapta as ist ERP solution running on unsupported Hardware, utilised by unsupported applications. Because everything around this (BI, EDI, PLM etc) is based on this database we find updates being rolled out in the frequency that the OP is experiencing. Oh yes, and the anticipated turnover for 2017 is €2bn......a Company that truly doesn't care about ist data.....
I offered the file watcher solution because I truly feel for him and the Situation and the very real possibility that in his Company he is the only one that cares.....
Make no bones about it, the file watcher solution would definitely be a reasonable solution for the DBA. I wouldn't auto-magically make things run at the appearance of the file because someone might put an early file out there and then decide they did something wrong. I'd make it a scheduled job.
Still, I can help but think that Developers shouldn't be responsible for the creation/repair of data so often unless it's coming from a 3rd party as data updates, in which case, it should all be automated. Even ERP solutions typically have a method for exporting data on a scheduled basis. Even the unsupported versions.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply