What is the BEST way to automate tasks on your server?

  • I have many servers I have to back up.

    I am using Maintenance Plans to create files, but then I use batch files and Scheduled tasks to drive 7-Zip and Robocopy to do the work of compressing, encrypting and moving those files off the server.

    But batch files are ridiculously limited -- there is no real error checking or handling. They work just fine when they work, in the best-case scenarios, but when things go wrong you might not even know about it, and then you don't have any backups. You most especially need backups to work in the worst-case scenarios!

    So I was wondering: What tools other than batch files/command-line do you use to automate your admin tasks?

    For example, how about VBscript? My concern with that is what allowed the ILOVEYOU virus, and may be a security risk and disabled on some production servers. I want my backup system to work on all servers.

    How about PowerShell? I believe it is totally free, but it requires the .NET framework, and besides, at the enterprise where I work getting anything new approved to be installed onto a production server is a nightmare that takes months (at best).

    And there are commercial tools like WinAutomation that seems to be exactly what would work for automating admin tasks, but again not only would I need to justify installing it, I'd have to make the case for buying it.

    Once you create backups to files sitting on a hard drive, then what do you do??

    Just FYI, here is a sampling of what I am currently doing for my backups:

    I use Maintenance Plans to just do regular backups to files right on the same server.

    Then I compress and encrypt those files with 7-zip (using a batch file and Scheduled Tasks). Then I copy all those small/encrypted files over my network to a central file server (using a batch file and Robocopy and Scheduled Tasks). I do hourly Log backups, so my loss-exposure -- even if the server blows up -- is 1 hour. (Some servers are even tighter.)

    Then I also copy all those files across the network again to a big hard drive attached to my workstation (also with a batch file and Robocopy and Scheduled Tasks).

    And finally I pull all those files to tape. If you don't have any backups, you can be -- and should be -- fired. No one ever gets fired for making too many backups!! 😀

    The net result is I have a boatload of backups, copied all over the place, they are secure since they are encrypted, and I have a copy on my workstation so I can easily do restores and actually test the backups and validate they are working (and document the recovery process, etc, etc).

    Its a pretty good system, when it works, which it usually does, but what happens if a server loses power during a really long Full backup?? A corrupt .BAK files is created, gets compressed, mirrored all over the place, and I think I have a backup but I don't!! Many similar ugly scenarios when relying on Batch Files..........

  • The best way to automate tasks on the server is whatever works the best for your situation. There is no one size fits all automation tool.

    As for Powershell - if you are running SQL Server 2008 (you posted in the 2008 forum, so I am assuming that is what you are running), then Powershell is already installed. SQL Server 2008 comes with it's own version of Powershell that you can automate through SQL Server Agent - or Task scheduler.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I wish I was running SQL 2008!

    Actually -- believe it or not -- they have SQL 7, 2000, 2005 and 2008 running here... ugh! Yes, even NT 4 Server. Double-Ugh!

    So I could use that newfangled PowerShell thingey on the modern servers, but -- the life of a DBA -- I have to support whatever they throw at me.

    So I am having success developing a system using good old batch files, but as you can maybe see, it takes a LOT of time to develop and test Test TEST and make sure everything works, even in bad/rare situations -- so here I am geeking away on Saturday.

    I was just hoping maybe someone had some magic insight that would save me a bazillion hours. But no, I shall script away on Saturday.

    I need to get out of here and go talk to girls.

    :w00t:

  • We use custom stored procedures and jobs for all our maintenance tasks, the backups hold a single copy locally and mirror/copy it to a remote dedicated backup server where we hold backups for about a week. This is cleared out by a simple vb.net program.

    We are looking logging this in more detail and then reading it into a central server for reporting purposes so that we can get an idea of when and how the backups fails.

    All routines are currently deplyed from a central server using a vb.net program, this is due to change when we upgrade the server to SQL Server 2008 R2 when we are going to use the deployment options in 2008.

    Our estate is currently mainly 2005 with 2008 catching up fast with a few 2000 servers that we hope to upgrade by the end of the year.

  • I use SSIS for everything. There is just about nothing that I cannot do. Especially with SQL 2005. I can move files around, create directories. You can also do the same thing with DTS, just using SSIS is now easier.

    And the best part, it is all free. Everything comes with SQL Server, of all versions. use one version, I would use SQL 2005, to do all the work. It has no idea what version of a SQL file it will be dealing with when moving files

    Andrew SQLDBA

  • AndrewSQLDBA (2/22/2010)


    I use SSIS for everything. There is just about nothing that I cannot do. Especially with SQL 2005. I can move files around, create directories. You can also do the same thing with DTS........

    Andrew SQLDBA

    Andrew -- thanks for the response. I so wish I was only using 2005 and later, would make life MUCH easier!

    I am looking in 2000 at DTS, and I don't see any way to move files around or create directories or anything. Am I missing something? Can you point me in the right direction??

    Thanx!

  • jpSQLDude (2/20/2010)


    I have many servers I have to back up.

    I am using Maintenance Plans to create files, but then I use batch files and Scheduled tasks to drive 7-Zip and Robocopy to do the work of compressing, encrypting and moving those files off the server.

    But batch files are ridiculously limited -- there is no real error checking or handling. They work just fine when they work, in the best-case scenarios, but when things go wrong you might not even know about it, and then you don't have any backups. You most especially need backups to work in the worst-case scenarios!

    So I was wondering: What tools other than batch files/command-line do you use to automate your admin tasks?

    For example, how about VBscript? My concern with that is what allowed the ILOVEYOU virus, and may be a security risk and disabled on some production servers. I want my backup system to work on all servers.

    How about PowerShell? I believe it is totally free, but it requires the .NET framework, and besides, at the enterprise where I work getting anything new approved to be installed onto a production server is a nightmare that takes months (at best).

    And there are commercial tools like WinAutomation that seems to be exactly what would work for automating admin tasks, but again not only would I need to justify installing it, I'd have to make the case for buying it.

    Once you create backups to files sitting on a hard drive, then what do you do??

    Just FYI, here is a sampling of what I am currently doing for my backups:

    I use Maintenance Plans to just do regular backups to files right on the same server.

    Then I compress and encrypt those files with 7-zip (using a batch file and Scheduled Tasks). Then I copy all those small/encrypted files over my network to a central file server (using a batch file and Robocopy and Scheduled Tasks). I do hourly Log backups, so my loss-exposure -- even if the server blows up -- is 1 hour. (Some servers are even tighter.)

    Then I also copy all those files across the network again to a big hard drive attached to my workstation (also with a batch file and Robocopy and Scheduled Tasks).

    And finally I pull all those files to tape. If you don't have any backups, you can be -- and should be -- fired. No one ever gets fired for making too many backups!! 😀

    The net result is I have a boatload of backups, copied all over the place, they are secure since they are encrypted, and I have a copy on my workstation so I can easily do restores and actually test the backups and validate they are working (and document the recovery process, etc, etc).

    Its a pretty good system, when it works, which it usually does, but what happens if a server loses power during a really long Full backup?? A corrupt .BAK files is created, gets compressed, mirrored all over the place, and I think I have a backup but I don't!! Many similar ugly scenarios when relying on Batch Files..........

    I assume it is SQL 2008 is that correct ? What is the compression ratio of backup files with Robocopy? Once you answer these questions i can recommend you better way to achieve this.

    EnjoY!
  • SQL DTS is very simple, and you use VBSCript as your coding language. All you have to do to create a function to move a file or delete a file, or create a directory, is to write it. So you are only limited to your own imagination, well, almost. using an OOP language will give you many more options. But still, with VBScript you can do many, many things. All of the things that you are talking, and more. And everything is in the DTS Pacakge, so there is no need in having to keep up with a bunch of little files stuck every where, for everyone to that has access to see, and even worse, modify or delete. Thinking that no one is using them. Even better, when you move the DTS package, the code goes with it.

    Check out www.SQLDTS.com, there are plenty of examples. You cna modify them and you have something different for a different task.

    I used DTS for many years before SSIS. I created and performed all kinds of tasks and things using DTS. I never used an old fashioned command file. Download the VBScript reference from microsoft site, so that you have all the syntax, functions, methods, etc.... that VBScript has

    Andrew SQLDBA

  • GT-897544 (2/22/2010)


    I assume it is SQL 2008 is that correct ? What is the compression ratio of backup files with Robocopy? Once you answer these questions i can recommend you better way to achieve this.

    The newer versions -- 2005 and 2008 -- are easy. The hard ones are 2000 and 7.

    That's why I'm trying to develop one system -- that works on any OS, any version of SQL.

  • AndrewSQLDBA (2/22/2010)


    SQL DTS is very simple, and you use VBSCript as your coding language. All you have to do to create a function to move a file or delete a file, or create a directory, is to write it. So you are only limited to your own imagination, well, almost. using an OOP language will give you many more options. But still, with VBScript you can do many, many things. All of the things that you are talking, and more. And everything is in the DTS Pacakge, so there is no need in having to keep up with a bunch of little files stuck every where, for everyone to that has access to see, and even worse, modify or delete. Thinking that no one is using them. Even better, when you move the DTS package, the code goes with it.

    Check out www.SQLDTS.com, there are plenty of examples. You cna modify them and you have something different for a different task.

    I used DTS for many years before SSIS. I created and performed all kinds of tasks and things using DTS. I never used an old fashioned command file. Download the VBScript reference from microsoft site, so that you have all the syntax, functions, methods, etc.... that VBScript has

    Andrew SQLDBA

    Thanks Andrew -- I could actually code a mean ASP page back in the day, so getting back up to speed on VBScript should be pretty quick.

    No concern about security? The ILOVEYOU virus came via .vbs -- no one ever turns that off on your production servers? Do you ever have to worry about STIGs or any other server-hardening guidelines??

    http://en.wikipedia.org/wiki/Security_Technical_Implementation_Guide

  • jpSQLDude (2/22/2010)


    GT-897544 (2/22/2010)


    I assume it is SQL 2008 is that correct ? What is the compression ratio of backup files with Robocopy? Once you answer these questions i can recommend you better way to achieve this.

    The newer versions -- 2005 and 2008 -- are easy. The hard ones are 2000 and 7.

    That's why I'm trying to develop one system -- that works on any OS, any version of SQL.

    Did you use SQL lite speed or HyperBac before? I don't think it is good idea to backup to local system and then copy to network drive and then tape. We did use Robocopy before it is not consistent you don't want to take chances against production backups.

    EnjoY!

    EnjoY!
  • I use caution when coding, but it is really just a matter of using your best judgment. Once you look at the SQLDTS site, you will see that it is VBScript based, but slightly different, so no one knows how to write anything against DTS. An dit only fires off when the package fires, and you need better than normal permissions to even get to that are to code against the DTS Package. I just use an account that has no permissions, or very low permissions to create or move data and directories. And that is done thru the file system, not in DTS.

    Andrew SQLDBA

  • I never use a third party tool when working with SQL Server, it can just about anything, and it can sure handle copying a file across the network to another box. I also, do not store anything locally, I always copy the backup files on to a network share. But I code the DTS or SSIS Package to do that. I just do not trust mixing apps when the original one can do the same thing.

    Andrew SQLDBA

  • AndrewSQLDBA (2/22/2010)


    I never use a third party tool when working with SQL Server, it can just about anything, and it can sure handle copying a file across the network to another box. I also, do not store anything locally, I always copy the backup files on to a network share. But I code the DTS or SSIS Package to do that. I just do not trust mixing apps when the original one can do the same thing.

    Andrew SQLDBA

    Andrew -

    Thanks again -- but another question for you. I have some truly massive databases, like 150 GB in size. A weekly FULL backup takes a really long time to do, and then I want to compress that .BAK file before I transfer it over my network, and that compression takes hours.

    Would you script all 3 of these steps to occur sequentially, in on big VBScript, within DTS, within SQL 2000?

    My concern would be what happens if one of these operations errors out for some reason, or the server crashes during this time, or whatever?

    Thanks again!

  • jpSQLDude (2/22/2010)


    AndrewSQLDBA (2/22/2010)


    I use SSIS for everything. There is just about nothing that I cannot do. Especially with SQL 2005. I can move files around, create directories. You can also do the same thing with DTS........

    Andrew SQLDBA

    Andrew -- thanks for the response. I so wish I was only using 2005 and later, would make life MUCH easier!

    I am looking in 2000 at DTS, and I don't see any way to move files around or create directories or anything. Am I missing something? Can you point me in the right direction??

    This is a SQL 2008 forum, NOT SQL 2000. Please clarify what version of SQL Server you have.

    Also, DTS is pretty awful, the only reason to use it is if you have nothing else available.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply