June 13, 2011 at 10:50 am
george sibbald (6/13/2011)
Ninja's_RGR'us (6/13/2011)
I like it too. Been around since dos 4.? so everything comes pretty naturally to me there... no point in learning clr and reinventing the wheel.I mainly use it to get file lists, delete old backups and regenerate restore commands for PIT... maybe free space as well.
Since many power users are also SA, I couldn't care less about the security risk. Both the vm and db are backed up so if someone really screws up with can recover and say "told you so" and maybe get this back under control ! 😉
Ah the challenges of only being a dev !!
there are sub cmdhell (sic) xps that do these sort of tasks. Not as easy to use I grant you but they exist (xp_delete_file, xp_fileexist etc)
Give this a whirl with those xps :
Get a list of all the files in the backup dir.
Figure out which one(s) are fulls and which ones are logs.
Then based on a retention requirement delete the uneeded full backup(s) and then all the child logs that become useless once the full is gone.
Full backups daily, trans every 15 minutes 24 / 7.
Can't wait to see that code! :w00t:
June 13, 2011 at 10:51 am
Jeff Moden (6/13/2011)
Shawn Melton (6/13/2011)
I tend to use SQLCMD (that is if PowerShell is not available to me :-D). We disable access and use of xp_cmdshell.Understood. Thanks for the feedback.
What do you use to execute the SQLCMD or PowerShell script and how do you handle the login security for the SQL Server they work with?
We use Windows Authentication wherever possible, most of the environment is AD with a handful of standalone servers (non-domain). I'm actually trying to play around with a tool that will push the PowerShell scripts out and return the information to me. I would love to use PowerShell remoting but restrictions do not allow me to enable that feature right now.
SQLCMD is used mostly locally on the server. I am trying to force myself into using PowerShell more now a days to make me learn it.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
June 13, 2011 at 11:19 am
Ninja's_RGR'us (6/13/2011)
george sibbald (6/13/2011)
Ninja's_RGR'us (6/13/2011)
I like it too. Been around since dos 4.? so everything comes pretty naturally to me there... no point in learning clr and reinventing the wheel.I mainly use it to get file lists, delete old backups and regenerate restore commands for PIT... maybe free space as well.
Since many power users are also SA, I couldn't care less about the security risk. Both the vm and db are backed up so if someone really screws up with can recover and say "told you so" and maybe get this back under control ! 😉
Ah the challenges of only being a dev !!
there are sub cmdhell (sic) xps that do these sort of tasks. Not as easy to use I grant you but they exist (xp_delete_file, xp_fileexist etc)
Give this a whirl with those xps :
Get a list of all the files in the backup dir.
Figure out which one(s) are fulls and which ones are logs.
Then based on a retention requirement delete the uneeded full backup(s) and then all the child logs that become useless once the full is gone.
Full backups daily, trans every 15 minutes 24 / 7.
Can't wait to see that code! :w00t:
That was a quick bite!
We keep fulls and tranlogs in different directories which simplifies it.
I'll check with the guy I tasked with rewriting our process to not be dependant on xp_cmdshell and PM it to you.
Our Deletion is based on age. Maintenance plans do all of the above and they are not dependant on xp_cmdshell so its gotta all be possible...........
---------------------------------------------------------------------
June 13, 2011 at 11:22 am
Ya but maint. plans have been more than buggy over the years so I've just created my own which I can control 100%.
June 13, 2011 at 1:24 pm
I must have read over the "legacy" excuse, so I'll use it myself :sick:
In our environments app devs need to modify their own sprocs or won't allow modification by the dba, if ever that dba would be in a good mood and stupid enough to offer that actual help.
To many instances, db, dev-teams, and no blocking power by the sysadmins.
However, we are working on no longer needing xp_cmdshell by offering alternatives, because in most cases, they really don't have the need to execute it in-process !
Now we move it to sqlagent jobs and have them fire a connected allert or SSB.
In most cases they just start an exe with provided parameter values.
We're not opening CLR because then they will no longer implement tsql sprocs, just because they can.
Guess who's the pita dba ?:blink:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 13, 2011 at 1:42 pm
Jeff Moden (6/13/2011)
GSquared (6/13/2011)
Of course, turning on CLR has its own security issues, but they can be mitigated more effectively.That's interesting. Thanks, Gus. You mention more effective mitigation of security issues when using CLR's... what does that mitigation involve?
A CLR procedure can only do the things it's programmed for. Doesn't have the flexibility of xp_cmdshell. If, for example, you need to move a file, the CLR procedure will have inputs only for the source and target, and ALL it can do is move files from source to target. Nothing else. If xp_cmdshell is enabled, and security gets elevated allowing for it to be run with arbitrary input, it can do anything the elevated account is allowed to do. With the CLR file-move object, if security gets elevated, all it can do is move files from source to target, still.
Where this gets tricky is if serious security issues exist, someone might be able to attach a DLL to your database, set the security options to allow arbitrary code to run, and then execute a malicious DLL through the CLR interface. Honestly, though, if someone can manage all that, they have sysadmin rights to your server already, and a rogue DLL is the least of your worries.
XP_CmdShell can be set up securely. I won't deny that. But misuse of it is easier than misuse of CLR, security-wise. Less hoops to jump through. So I "recommend against it", I don't "forbid it at all cost" or anything like that.
I'm still getting the hang of CLR programming, and am not all that good at it yet. But it's got some very powerful options in it. Just don't use it where T-SQL will be better, and you can get results like you found with your string parsing routine, Jeff. Another good one I have used checked for drive space available before running backups. Was specifically needed in a particular environment, but xp_CmdShell really can't easily check that kind of thing on a remote server, perform the calculations necessary to estimate the room needed vs room available, and return a set of values that tell the calling proc what to do next. CLR did so very easily, and it didn't take very many lines of code to do so.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 13, 2011 at 2:00 pm
Jeff Moden (6/13/2011)
BWAA-HAAA!!!! 48 reads and 2-1/2 days later and no one has a compelling reason to use xp_CmdShell? Considering the number of posts asking how to use it, I'm really surprised! 😀
Then again, you posted it on a Friday afternoon night, what'd you expect? 😀
My personal reasons for using it:
#1 reason to use xp_cmdShell: "I'm lazy and don't want to learn a more secure way to code it, I'm a bit busy, thanks..."
#2 reason to use xp_cmdShell: See most excuses that obfuscate #1 so that my manager doesn't feel like I just blew him off.
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
June 13, 2011 at 3:51 pm
Shawn Melton (6/13/2011)
SQLCMD is used mostly locally on the server
Thanks again, Shawn. The real question is "How is SQLCMD being called?" Are you calling it through a job on SQL Server, running a batch file, or ???
--Jeff Moden
Change is inevitable... Change for the better is not.
June 13, 2011 at 6:41 pm
george sibbald (6/13/2011)
there are sub cmdhell (sic) xps that do these sort of tasks. Not as easy to use I grant you but they exist (xp_delete_file, xp_fileexist etc)
It's true, there are ways to get directory listings and create directories (if they don't exist), move files around, and specific procs to work with backup files that will take retention into account automatically using built in xp_'s. But stringing all of these together to deal with multiple subdirectory trees (such as those that come from Ola Hallengren's backup routines), and ensure that you pick up all of the files that you need to get, is actually quite a lot of code. One call to robocopy with the mirror option takes care of everything, and makes it obvious what you're trying to accomplish.
Because of the difference in the degree of complexity in this specific situation, I feel it is one of those situations where the self-documenting nature of clear, concise code outweighs the potential benefits of more deeply engineered solutions. When I started building the copy/restore routines I actually first thought I'd go with CLR, made a file_system schema, and so on. In the end I realised it would be a real pain to maintain (and just getting something as simple as the default or bare output of DIR in .NET is a chore).
June 14, 2011 at 5:18 am
@allmhuran - I guess it depends exactly what you are trying to do.
We are just backing up, creating directories and deleting files, we leave copying files to software we have for the purpose.
Sure its harder but the point I was making is that it is possible which seemed pertinent to Jeffs question to me.
---------------------------------------------------------------------
June 14, 2011 at 6:44 am
I knew I shouldn't have posted this on a Friday night. 😛 You good folks sure did come alive on Monday. Thank all of you a huge amount.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 14, 2011 at 6:54 am
Oh, one other advantage of CLR vs xp_CmdShell:
Nobody is likely to ever going to be impressed by, "Really good at xp_CmdShell" on a resume. "Grand Master at command line use," isn't likely to land a job. They might be impressed by, "X years experience with C# in SQL CLR environment", or the VB equivalent (though C# has more market cache currently).
Take the time to learn the .NET way to get it done, and it's another skillset that can help get jobs/raises/whatever.
Not as important as the actual technical aspects of the two, but it is something to consider.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 14, 2011 at 7:39 am
Jeff Moden (6/13/2011)
Shawn Melton (6/13/2011)
SQLCMD is used mostly locally on the serverThanks again, Shawn. The real question is "How is SQLCMD being called?" Are you calling it through a job on SQL Server, running a batch file, or ???
Sorry, mostly batch files. We have an in house program that calls it as well for doing some things, I believe it is written in C# but I have never seen the code for it.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
June 14, 2011 at 8:06 am
3rd party backup software
Thomas
Thomas LeBlanc, MVP Data Platform Consultant
June 14, 2011 at 9:56 am
Shawn Melton (6/14/2011)
Jeff Moden (6/13/2011)
Shawn Melton (6/13/2011)
SQLCMD is used mostly locally on the serverThanks again, Shawn. The real question is "How is SQLCMD being called?" Are you calling it through a job on SQL Server, running a batch file, or ???
Sorry, mostly batch files. We have an in house program that calls it as well for doing some things, I believe it is written in C# but I have never seen the code for it.
You have no idea how happy that answer made me. 😀 Thanks, Shawn.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 107 total)
You must be logged in to reply to this topic. Login to reply