Best Practices sFTPing files between servers

Hi-

I have a sql 2005 database that stores weekly time and attendance data for 500 users. I need to send the file on a scheduled basis.

I'd alo like to have programmatic control over sending the file via my asp,net application's admin pages. (I have roles and security set up on the site)

My question is two fold:

Can asp.net be scripted to manage a data extraction to a file, (I gotta HOPE so) THEN is there any sFTP functions built into asp.net?

I know I can do this in SQL2005, then have a scheduled event fire off the file via sFTP. but like I said, I'd like to have programmatic control over the file sending. (Of course they have to be sent on SUNDAY!)

Thanks in advance for any advise

You can have a button that when its cliked, you check on the day. If it is Sunday (for example) then call some stored procedures which basically will run a DTS/SSIS package which you can confugure it to take the needed data from the database and dump it in Excel file. Then you send this Excel file (e.g. Weekly Report) to the management though email (as attached file).

How to run the DTS/SSIS package from ASP.NET, please have a look on these links:

http://www.sqlteam.com/article/how-to-asynchronously-execute-a-dts-package-from-asp-or-aspnet

http://www.sqldev.net/dts/ExecutePackage.htm#Visual%20Csharp

Good luck.

|||

4Ever-

First, thank you for helping out so much in these forums.

Second, great idea, but I have a specific file format constraints as well as an sFTP directory my flat file needs to show up in.

So let me repackage my question.

Is there a way to (stored procedures?) script sql server 2005 to export and import data to and from a sFTP directory on another server? I gotta think I am not the only one that ever had this requirement!

Thanks again for your help.

Dan

|||

Harperator:

4Ever-

First, thank you for helping out so much in these forums.

Thanks Dan.

I think this is a good time to use BizTalk, but there might be a less expensive solutoions..

What about createing a C# windows services to do this or if you have a complex logic use VB script (not sure about it)?

So, to make things clear .. You have a sFTP which reside in diffrent machine that your SQL Server , right?

If this is right, so what is preventing from using the suggest solution above (my previous post)? If it is the file strcture .. can you have it in diffrent format (e.g. CSV) then organzing it in your special strcture?

|||

Hum...

here's the deal:

I have asp.net program running on iis on a win server running sql 2005.

I need to SFTP a file weekly, to and from a remote server.

I need to automate the process.

I can do this by creating a ssis job in sql, have it output the file, then write a batch file to send it via sftp using securefx by Vandike software.

I am just wondering if there is not a simple microsoft solution already built into sql, or iis, of asp.net or windows server.

|||

I do not know if this will help but let us see ..

In your "Transformation" you have a source which is SQL and the destination (e.g. Excel file .. select that path which indicate to your file which is already in another server).

Do your transformation -> Data will be in Excel.

Let your client get the file (e.g. Copy & Pase).

Have a batch which will basically copy data from an empty Excel file to your REAL file -> to have a new Excel file ready to receive the data from SQL Server.

I do not know if this will help .. I hope it is.

Good luck.

|||

Thanks!

Dan

(I broke down and bought sql 2005 unleashed)

Best practice for shrinking Databases

Is there a "best practice" for shrinking databases?
Daily, Weekly, Monthly? Daily do a normal 25% free and weekly Move pages to
the beginning before shrinking?
I already have Maintenance Plans to backup logs and the database etc. but
wondered about automating the shrink process as well. There seems to be
alot of free space 67%+ in the database and logfile.
Dan
In general you want to avoide shrinking databases on a regular basis.
If the database grows back every week, or month, then it obviously
needs the space, so leave the space there! It is a terrible
performance hit to wait while SQL Server allocates more space, you
really don't want it happening more than it must.
So shrinking should be on an as-needed basis.
As for how much elbow room to leave, one rule of thumb I have used is
at least 25% more free space than the largest table. That allows room
for rebuilding a clustered index.
Another rule of thumb is that DBA time costs a lot more than disk
space; managing a database server without plenty of extra disk space
will cost far more dollars in administration than it saves on
hardware.
Roy
On Wed, 22 Feb 2006 17:25:11 -0500, "Dan" <someone@.yahoo.com> wrote:

>Is there a "best practice" for shrinking databases?
>Daily, Weekly, Monthly? Daily do a normal 25% free and weekly Move pages to
>the beginning before shrinking?
>I already have Maintenance Plans to backup logs and the database etc. but
>wondered about automating the shrink process as well. There seems to be
>alot of free space 67%+ in the database and logfile.
>Dan
|||That's good advice Roy.
When I do shrink it, should I use the option to move the pages to the
beginning before shrinking?
Dan
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:234qv1h601176jknofkr8furr4lr7gc1o1@.4ax.com... [vbcol=seagreen]
> In general you want to avoide shrinking databases on a regular basis.
> If the database grows back every week, or month, then it obviously
> needs the space, so leave the space there! It is a terrible
> performance hit to wait while SQL Server allocates more space, you
> really don't want it happening more than it must.
> So shrinking should be on an as-needed basis.
> As for how much elbow room to leave, one rule of thumb I have used is
> at least 25% more free space than the largest table. That allows room
> for rebuilding a clustered index.
> Another rule of thumb is that DBA time costs a lot more than disk
> space; managing a database server without plenty of extra disk space
> will cost far more dollars in administration than it saves on
> hardware.
> Roy
>
> On Wed, 22 Feb 2006 17:25:11 -0500, "Dan" <someone@.yahoo.com> wrote:
|||Moving the pages is the only way you will be able to shrink to the
leve you want.
Roy
On Wed, 22 Feb 2006 20:48:23 -0500, "Dan" <someone@.yahoo.com> wrote:

>That's good advice Roy.
>When I do shrink it, should I use the option to move the pages to the
>beginning before shrinking?
>Dan

Best practice for shrinking Databases

Is there a "best practice" for shrinking databases?
Daily, Weekly, Monthly? Daily do a normal 25% free and weekly Move pages to
the beginning before shrinking?
I already have Maintenance Plans to backup logs and the database etc. but
wondered about automating the shrink process as well. There seems to be
alot of free space 67%+ in the database and logfile.
DanIn general you want to avoide shrinking databases on a regular basis.
If the database grows back every week, or month, then it obviously
needs the space, so leave the space there! It is a terrible
performance hit to wait while SQL Server allocates more space, you
really don't want it happening more than it must.
So shrinking should be on an as-needed basis.
As for how much elbow room to leave, one rule of thumb I have used is
at least 25% more free space than the largest table. That allows room
for rebuilding a clustered index.
Another rule of thumb is that DBA time costs a lot more than disk
space; managing a database server without plenty of extra disk space
will cost far more dollars in administration than it saves on
hardware.
Roy
On Wed, 22 Feb 2006 17:25:11 -0500, "Dan" <someone@.yahoo.com> wrote:

>Is there a "best practice" for shrinking databases?
>Daily, Weekly, Monthly? Daily do a normal 25% free and weekly Move pages t
o
>the beginning before shrinking?
>I already have Maintenance Plans to backup logs and the database etc. but
>wondered about automating the shrink process as well. There seems to be
>alot of free space 67%+ in the database and logfile.
>Dan|||That's good advice Roy.
When I do shrink it, should I use the option to move the pages to the
beginning before shrinking?
Dan
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:234qv1h601176jknofkr8furr4lr7gc1o1@.
4ax.com...[vbcol=seagreen]
> In general you want to avoide shrinking databases on a regular basis.
> If the database grows back every week, or month, then it obviously
> needs the space, so leave the space there! It is a terrible
> performance hit to wait while SQL Server allocates more space, you
> really don't want it happening more than it must.
> So shrinking should be on an as-needed basis.
> As for how much elbow room to leave, one rule of thumb I have used is
> at least 25% more free space than the largest table. That allows room
> for rebuilding a clustered index.
> Another rule of thumb is that DBA time costs a lot more than disk
> space; managing a database server without plenty of extra disk space
> will cost far more dollars in administration than it saves on
> hardware.
> Roy
>
> On Wed, 22 Feb 2006 17:25:11 -0500, "Dan" <someone@.yahoo.com> wrote:
>|||Moving the pages is the only way you will be able to shrink to the
leve you want.
Roy
On Wed, 22 Feb 2006 20:48:23 -0500, "Dan" <someone@.yahoo.com> wrote:

>That's good advice Roy.
>When I do shrink it, should I use the option to move the pages to the
>beginning before shrinking?
>Dan

Best practice for shrinking Databases

Is there a "best practice" for shrinking databases?
Daily, Weekly, Monthly? Daily do a normal 25% free and weekly Move pages to
the beginning before shrinking?
I already have Maintenance Plans to backup logs and the database etc. but
wondered about automating the shrink process as well. There seems to be
alot of free space 67%+ in the database and logfile.
DanIn general you want to avoide shrinking databases on a regular basis.
If the database grows back every week, or month, then it obviously
needs the space, so leave the space there! It is a terrible
performance hit to wait while SQL Server allocates more space, you
really don't want it happening more than it must.
So shrinking should be on an as-needed basis.
As for how much elbow room to leave, one rule of thumb I have used is
at least 25% more free space than the largest table. That allows room
for rebuilding a clustered index.
Another rule of thumb is that DBA time costs a lot more than disk
space; managing a database server without plenty of extra disk space
will cost far more dollars in administration than it saves on
hardware.
Roy
On Wed, 22 Feb 2006 17:25:11 -0500, "Dan" <someone@.yahoo.com> wrote:
>Is there a "best practice" for shrinking databases?
>Daily, Weekly, Monthly? Daily do a normal 25% free and weekly Move pages to
>the beginning before shrinking?
>I already have Maintenance Plans to backup logs and the database etc. but
>wondered about automating the shrink process as well. There seems to be
>alot of free space 67%+ in the database and logfile.
>Dan|||That's good advice Roy.
When I do shrink it, should I use the option to move the pages to the
beginning before shrinking?
Dan
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:234qv1h601176jknofkr8furr4lr7gc1o1@.4ax.com...
> In general you want to avoide shrinking databases on a regular basis.
> If the database grows back every week, or month, then it obviously
> needs the space, so leave the space there! It is a terrible
> performance hit to wait while SQL Server allocates more space, you
> really don't want it happening more than it must.
> So shrinking should be on an as-needed basis.
> As for how much elbow room to leave, one rule of thumb I have used is
> at least 25% more free space than the largest table. That allows room
> for rebuilding a clustered index.
> Another rule of thumb is that DBA time costs a lot more than disk
> space; managing a database server without plenty of extra disk space
> will cost far more dollars in administration than it saves on
> hardware.
> Roy
>
> On Wed, 22 Feb 2006 17:25:11 -0500, "Dan" <someone@.yahoo.com> wrote:
>>Is there a "best practice" for shrinking databases?
>>Daily, Weekly, Monthly? Daily do a normal 25% free and weekly Move pages
>>to
>>the beginning before shrinking?
>>I already have Maintenance Plans to backup logs and the database etc. but
>>wondered about automating the shrink process as well. There seems to be
>>alot of free space 67%+ in the database and logfile.
>>Dan|||Moving the pages is the only way you will be able to shrink to the
leve you want.
Roy
On Wed, 22 Feb 2006 20:48:23 -0500, "Dan" <someone@.yahoo.com> wrote:
>That's good advice Roy.
>When I do shrink it, should I use the option to move the pages to the
>beginning before shrinking?
>Dan