Backing up SQL Server: VDI or SQLDMO?

I am in the process of writing a backup software package, and I posted a
question here a while ago about how to best backup SQL Server databases, and
the preferred approach for implementing backup from a program written in C++
.
Louis Davidson recommended SQLDMO, but since then I've found documentation
for the "Virtual Device Interface (VDI) for Backup".
So now I'm wondering what the pros and cons are of these two approaches. To
judge by the documentation, it seems that VDI would be the preferred approac
h
since it provides the "the highest online backup throughput with minimal
degradation to the transaction workload".
Thanks,
PeterVDI and DMO are two completely different and are targeting two different
things
VDI is a virtual device interface for ISV's to develop SQL Server Backup
software so you can backup you SQL Server database and tlogs to for example
compressed files, remote locations by backing up over a pipe etc.
DMO is a management interface, abstracting programmers to learn or issue
TSQL commands directly.
Are you writing an application that tells SQL to perform a BACKUP or are you
planning on implementing a backup device?
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"jpstewart" <jpstewart@.discussions.microsoft.com> wrote in message
news:44B5C821-78C9-4C25-8951-9E4892825397@.microsoft.com...
>I am in the process of writing a backup software package, and I posted a
> question here a while ago about how to best backup SQL Server databases,
> and
> the preferred approach for implementing backup from a program written in
> C++.
> Louis Davidson recommended SQLDMO, but since then I've found documentation
> for the "Virtual Device Interface (VDI) for Backup".
> So now I'm wondering what the pros and cons are of these two approaches.
> To
> judge by the documentation, it seems that VDI would be the preferred
> approach
> since it provides the "the highest online backup throughput with minimal
> degradation to the transaction workload".
> Thanks,
> Peter
>|||Thanks for your reply. I'm writing an application that should be able to bac
k
up SQL server in the most convenient, automatic and efficient way possible.
In so far as the backup device resides on the internet and uses our
proprietary compressed and encrypted file format, I guess you could also say
that it's an implementation of a backup device.
You wouldn't happen to know the answer to my other question about SQL-DMO
and ATL style smart pointers?
Thanks,
Peter
"Gert E.R. Drapers" wrote:

> VDI and DMO are two completely different and are targeting two different
> things
> VDI is a virtual device interface for ISV's to develop SQL Server Backup
> software so you can backup you SQL Server database and tlogs to for exampl
e
> compressed files, remote locations by backing up over a pipe etc.
> DMO is a management interface, abstracting programmers to learn or issue
> TSQL commands directly.
> Are you writing an application that tells SQL to perform a BACKUP or are y
ou
> planning on implementing a backup device?
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2005 All rights reserved.
> "jpstewart" <jpstewart@.discussions.microsoft.com> wrote in message
> news:44B5C821-78C9-4C25-8951-9E4892825397@.microsoft.com...
>
>|||Sounds like a VDI solution to me. I will dig up my ATL example, it is
possible, forgot how I did it though.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"jpstewart" <jpstewart@.discussions.microsoft.com> wrote in message
news:35978FC2-FB22-47DC-A3A7-0E90AC09741D@.microsoft.com...
> Thanks for your reply. I'm writing an application that should be able to
> back
> up SQL server in the most convenient, automatic and efficient way
> possible.
> In so far as the backup device resides on the internet and uses our
> proprietary compressed and encrypted file format, I guess you could also
> say
> that it's an implementation of a backup device.
> You wouldn't happen to know the answer to my other question about SQL-DMO
> and ATL style smart pointers?
> Thanks,
> Peter
> "Gert E.R. Drapers" wrote:
>|||Thanks, I would appreciate it!
-Peter
"Gert E.R. Drapers" wrote:

> Sounds like a VDI solution to me. I will dig up my ATL example, it is
> possible, forgot how I did it though.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2005 All rights reserved.
> "jpstewart" <jpstewart@.discussions.microsoft.com> wrote in message
> news:35978FC2-FB22-47DC-A3A7-0E90AC09741D@.microsoft.com...
>
>

Aysnchronously calling a package

Hello,

Is there a way to call SSIS package within another SSIS package, asynchronously. Any pointers would be much appreciated.

Thanks

Vishal

Say if you control flow looks like:

step 1
|
|
|_
| |
| |
step 2 step 3
|
|
step 4
And say step 3 is execute package task that you want to run asynchronously.
Can you describe your issues with this? Are you looking for event handlers?

Nitesh

|||

[Including Michael's answer for posterity]

You can use Execute Process task to run the package using DTEXEC.EXE utility.

To run DTEXEC without execute process task waiting for it, either specify a small timeout and set TerminateProcessAfterTimeOut to false, or start the DTEXEC using ‘cmd /c start dtexec.exe /F filename’ command.

Avoid Package Failure

Hello everyone

i'm using a Foreach Container to run certain tasks, these ones are inside a Sequence Container. I'd like to know if there is a way to have a task failure without a foreach failure, i don't need the hole process to fail, just the current iteration so i can log it.

Thanks a lot!

You may want to check FailParentOnFailure and FailPackageOnFailure properties of your inner tasks. I have not played with those but it seems like prove what you need.

Rafael Salas

|||Use an event handler.

Set the [System::Propagate] to false in an OnError handler in the inner tasks. That way, errors won't bubble up to the Foreach loop and cause it to fail.|||Thanks a lot

I used set maximum error count to a number fairly reachable, but i think it's not a good practice. I'll try your suggestions right now.

regards.|||Hello again,

I'm having the same problem, I did what you told me, on the OnError event handler I use a script where i change the Propagate variable to False. But there's a problem with the MaximumErrorCount property. Watching on the Execution Results tab, i figured out that the package fails because of the MaximumErrorCount property overpassed the foreach's number.

Well i have to say that inside the foreach Container there is a Sequence Container, and inside this one, there are 12 tasks, and i don't know if it could be stopping the package.

any suggestion?

regards.|||

I was referring to setting the OnError event handler on the failing inner tasks, however, I believe it would work just as well as on the sequence container, as long as its configured as depected.

└───ForEach
├───Sequence (<= OnError event handler here )
├───Task 1
├───Task N

On which tasks or containers is the OnError Event Handler defined?

Availability of SSIS Run-time support (Redistributable package)?

Can somebody please help answering this question?

I'm planning to consider writing a SSIS package for a new project that requires downloading large chunk of data and transform into the diverse databases such as MS SQL or Oracle depending on the Client's Datbase.

For the clients having SQL Server installed at their end, i had no issues in deploying this package on their server and run it in their licensed instance.

What should be the case for others having Oracle database? Wouldn't installing the SQL 2005 client tools install the necessary run-time services for running SSIS packages? What i understood from the MSDN library (http://msdn2.microsoft.com/en-us/library/ms403355.aspx) is that there's no run-time support available for running the SSIS packages (unlike DTS run-time support) in production environment!

Would that mean that it requires a SQL Standard edition, at minimum, (as Integration Services is OOTB from Standard Edition onwards) to be installed at the production site to run this package?

If so, the client wouldn't be ready (which is fair too) to buy the new license just to run this package. Is there any work-around/suggestions for this case?

If not, can somebody please point me to the right location where i can download the run-time support for running SSIS packages?

Any thoughts on this is highly appreciated.

Thanks for your time.

~Sagar

You're reading of the Help is correct, SSIS does not offer a redistributable like DTS did, and also the licensing has changed. SSIS is a server component, not client, so you need a Server license, not a CAL. Mind you for your Oracle shops, you would always have needed a server as you cannot have CALs without having purchased a server as well.

If you want to install/run SISS you need to but a "SQL Server" license. Choose the edition, Standard is normally fine for most people. I also think it is quite nice you get a relational engine, OLAP server and report server for free as well. Most ETL packages don't include that level of free software with them.

This is a big change from the DTS world, SSIS is a considered a Server now, and licensed as such.

|||

This is strange. Why would anybody (well, most of them at least) go for a full fledged database software with so many OOTB packages (database, SSIS, SSAS, OLAP Server, SSRS etc) for just running few ETL packages?

One hand we use SSIS package to transform the data to different databases and on the other hand we are asked to buy one MS SQL database license in order to execute the package. How justifiable it is to ask the Customer buy a SQL Server license (costing ~$3000 for a server setup) just to run this package? This not only a resource exhaustive but also a maintenace over head to tackle with the software which the customer might not want to..

Isn't it possible just to get a stand-alone install of SSIS (let alone the availability of run-time support for SSIS) if not now, may be in the future?

Regards,

Sagar

|||Take a look at the prices for other ETL software out there. $3000 a server isn't bad at all, even if the only thing you were getting is the ETL tool.|||

All happies If it's just done with 3000$.

But Who would like to share the Production server (on which Oracle database is installed) with MS Sql Server just to run the SSIS packages? It is a maintenance cost overrun to allocate a different server machine just to host the MS SQL license and hence a performance hit due to the cross-machine calls between SSIS Packages and the destination Server (i.e. Oracle database server).

With this background, does it not make sense having a redistributable package from MS (run-time support) for executing SSIS packages that can be installed on Oracle database server(on Production server)?

|||

VidyaSagarCh wrote:

All happies If it's just done with 3000$.

But Who would like to share the Production server (on which Oracle database is installed) with MS Sql Server just to run the SSIS packages? It is a maintenance cost overrun to allocate a different server machine just to host the MS SQL license and hence a performance hit due to the cross-machine calls between SSIS Packages and the destination Server (i.e. Oracle database server).

With this background, does it not make sense having a redistributable package from MS (run-time support) for executing SSIS packages that can be installed on Oracle database server(on Production server)?

IIRC, you do not need to install the SQL Server RDBMS to run SSIS. So the scenario you're describing should work just fine - if you only want to install SSIS on the Oracle box, you should be able to do that.

With that said, if your Oracle DBAs are anything like mine, you will have just as much trouble getting them to let you install SSIS as you would have getting them to let you install the full SQL Server stack.