Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

What is the BEST way to automate tasks on your server? Expand / Collapse
Author
Message
Posted Saturday, February 20, 2010 3:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 08, 2014 1:07 PM
Points: 115, Visits: 902
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..........
Post #869718
Posted Saturday, February 20, 2010 3:48 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:34 PM
Points: 4,379, Visits: 9,471
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
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #869720
Posted Saturday, February 20, 2010 4:59 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 08, 2014 1:07 PM
Points: 115, Visits: 902
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.



Post #869728
Posted Monday, February 22, 2010 9:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 10, Visits: 250
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.
Post #870446
Posted Monday, February 22, 2010 11:22 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, April 21, 2014 7:08 PM
Points: 958, Visits: 3,267
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
Post #870582
Posted Monday, February 22, 2010 12:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 08, 2014 1:07 PM
Points: 115, Visits: 902
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!
Post #870630
Posted Monday, February 22, 2010 12:44 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 20, 2013 9:25 AM
Points: 389, Visits: 357
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!
Post #870644
Posted Monday, February 22, 2010 12:51 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, April 21, 2014 7:08 PM
Points: 958, Visits: 3,267
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
Post #870651
Posted Monday, February 22, 2010 1:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 08, 2014 1:07 PM
Points: 115, Visits: 902
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.
Post #870664
Posted Monday, February 22, 2010 1:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 08, 2014 1:07 PM
Points: 115, Visits: 902
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

Post #870675
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse