Offline/remote backups

Hi

What is the best way to perform daily offline/remote/off-premise backups of our websistes Sql Server 2005 database?

I've looked into using SSIS but for one reason or another am having serious problems with this. Ideally we'd like to be able connect over the internet and/or lan and pull the entire database down in one go. Our db is ~600mb so isn't major.

We used to use Sql Server 2000's DTS and this worked well, but I'm having a real hard time using SSIS to do the same so was wondering what the other options are

Many thanks

Ben

I'm curious as to why you're going the DTS/SSIS route rather than simply using a standard SQL backup and copying the backup file to a remote location. Understanding that would help us come up with a scenrio that would meet your needs.

If you need a remote system that is a hot or cold standby, you might consider something like log-shipping.

|||

Hi Kevin

The reason we used DTS originally was that it allowed us to backup over the internet. Obviously, upgrading to Sql Server 2005 made SSIS the logical choice.

My customer has two servers running inside the same building and he wants the live database periodically backed-up to the other server so in the event of hard-drive failure, they can still retrieve their data. Also, possibly just once a day, they'd like to download the entire database to another office via the internet.

I suggested to my customer about using the normal backup and possibly FTP'ing the backup file to a remote location. However, log-shipping certianly seems like a good idea. I'd never heard of it until just you mentioned it, so thankyou very much.

Do both instances of Sql Server have to be the same type? Ie, can an Enterprise or Standard version log-ship to a Sql Express instance? Can more than one other instance be used for log-shipping?

Thanks for your help

Ben

|||Log shipping is not supported in SQL Server 2005 Express. If you want, you can emulate log shipping by doing a job that backs up the transaction log on the primary server, FTP the backup to the standby server, and restore the backup on the standby server in read-only mode. This can be done using scripts. A good understanding of how log shipping works will give you an idea on how to create the custom log shipping using a combination of jobs and scripts. The problem with this is that you do not have the luxury of a monitor server nso you need to make sure that you implement your alerts properly. You can check http://www.sql-server-performance.com/sql_server_log_shipping.asp for a reference article on how to do a custom log shipping with scripts, jobs and all.

Office Interop Assemblies and SSIS

Hi guys,

I'm trying to write an SSIS script that will create an excel file and append data to it.

I downloaded the Office 2007 PIAs (Primary Interop Assemblies) and installed them on my desktop PC where i'm developing from.

However, and even though the assemblies are in the GAC, they don't appear when you look in "Add Reference" for your script task.

If i create a standard .NET web or windows app and try to "Add Reference" I can find them there.

Am I missing anything obvious?

Thanks for your help in advance

Mike

Do you have the Visual Studio Tools for Office 2007 installed?

Just curious, why create the excel file in the script task?

|||

Mike_Gaziotis wrote:

Hi guys,

I'm trying to write an SSIS script that will create an excel file and append data to it.

I downloaded the Office 2007 PIAs (Primary Interop Assemblies) and installed them on my desktop PC where i'm developing from.

However, and even though the assemblies are in the GAC, they don't appear when you look in "Add Reference" for your script task.

If i create a standard .NET web or windows app and try to "Add Reference" I can find them there.

Am I missing anything obvious?

Thanks for your help in advance

Mike

This should help.

VSA requires DLLs to be in the Microsoft.Net folder (but not all the time)

(http://blogs.conchango.com/jamiethomson/archive/2005/11/02/SSIS_3A00_-VSA-requires-DLLs-to-be-in-the-Microsoft.Net-folder-_2800_but-not-all-the-time_2900_.aspx)

-Jamie

|||

The VSA designer's "Add Reference" dialog does not look for assemblies in the GAC but only in the %ProgramFiles%\Microsoft SQL Server\90\SDK\Assemblies and %windir%\Microsoft.NET\Framework\v2.0.50727

We recomend that you copy them to the SQL server location pointed above for the "Add Reference" to work.

|||Thanks very much Jamie and Silviu!
Putting the DLLs in the .NET Folder seems to have done the trick.

In response to jwelch's question: I would have used the standard Data Flow task but it didn't seem to support some of the functionality i needed (ie dynamically building the file path and file name based on content etc)

Thanks again guys

Mike
|||

Actually, that sounds like a great opportunity to use variables or expressions. If you describe your scenario a little more, perhaps there might be some alternative approaches.

Not trying to change your mind on direction, just want to make sure you know what options are available.

|||Hi Jwelch,

Always keen to find out if there are more approaches that i haven't considered. Even if they're not used in this scenario it's good to know of the possibilites

Here are my SSIS job requirements:
- Execute (several) SQL queries which retrieve report data
- Insert the data in worksheets of several excel files
- Save the files by:
a: Putting a timestapt in their name (ie reportA_04072007)
b: storing them in different locations on the server based on the report
(ie %Reports%/ReportA/reportA_04072007.xls)
- Send mails with excel files attached

This is to be a scheduled job managed by the SQL server agent.

It's not a job of enormous complexity i must admit, but i somehow i found myself (perhaps because of me being a developer rather than a DBA by nature) more keen to write code than having to use loops, data flow tasks and all the rest. In fact when i first had the problem with the assemblies i half considered writing a windows app that would do that job and then run that from the sql server agent. And really, is there such a big difference between writing an app or a script-only SSIS job?

Thanks and sorry for the babble
Mike

Odd timing problem MSAccess to SS2005

I have an SSIS package that takes data from a table in Access and puts it into a fact table in SS2005. Very little data manipulation is done. It processes approximately 1.5 million rows when it runs weekly. The process is run in an SSIS package that is called by a parent package, and all of that (including the use of the config files and accessing the parent variables) is working fine.

The issue is there is one field in the Access table that must be put into a different SS2005 fact table.

When I run the data flow task that loads the first fact table, it completes in less than two minutes. However, if I either (a) put a multicast step in the dataflow task to redirect a copy of the key data and remaining field to the second fact table, or (b) copy that step in the package to have it perform the same tasks with the different target (and using just the key and the remaining field), the execution time suddenly jumps to 30 minutes. In the case of (b), it remains true whether the copied step remains in the package or is executed in its own package, and also remains true if the package is loading against a table that starts out empty or with data already in it.

Has anyone ever bumped into a situation like this?

This could be because of a number of things, but I think the most likely are probably that the slow table is heavily indexed or has foreign keys being enforced. Also, make sure you're using fastload.|||

[banging head against desk]

Fast load ... that was it. Doggone it. Thanks for indulging a goof.

ODBC password not saved in configuration file

Hi,

I have an SSIS package that pipes data from an iSeries data source to a SQL Server table. The connection manager I am using is the .Net Providers\Odbc Data Provider

When I run this SSIS package on my developer PC (from inside BIDS or using the Management Studio Execute Package Utility) it works. However when I run the package from within a SQL Server Agent Job it fails.

I have enabled logging and the error message given is:

System.Data.Odbc.OdbcException: ERROR [28000] [IBM][iSeries Access ODBC Driver]Communication link failure. comm rc=8002 - CWBSY0002 - Password for user MALLPRESS on server SMICHC is not correct, Password length = 0, Prompt Mode = Never, System IP Address = 172.16.0.4 ERROR [28000] [IBM][iSeries Access ODBC Driver]Communication link failure. comm rc=8002 - CWBSY0002 - Password for user MALLPRESS on server SMICHC is not correct, Password length = 0, Prompt Mode = Never, System IP Address = 172.16.0.4

It gives the message “Password length = 0” despite the fact that I have specified a password in the package.

To try to get around this I have added an XML configuration file to the SSIS package. In this configuration file I’ve set values for the ConnectionString, UserName, and Password.

I still get the above error about a password length of 0.

I have also tried to execute the package using dtexec, but this gives a similar error.

The SQL used to run this is:

xp_cmdshell 'dtexec /sq "\SCSRV-06\DTS Packages\Staging\StagingBranchImport"'

This has got me stumped, and I’m not sure where to go from here as these packages need to be programmatically run each morning. At the moment I am manually running them from within BIDS.

All packages that don’t use an iSeries connection manager run successfully in SQL Agent jobs.

I have also tried using the OLEDB connection manager (ibm db2 udb for iseries ibmda400 ole db provider) without success.

Any help would be very appreciated.

Thanks, Matt

SSIS will NOT store the password in the file for you. You have to edit the file manually to put the password in there. Have you done that?

-Jamie

|||

Hi Jamie,

Yes I have manually edited the config file and entered the password.

Matt

|||

Just to let anyone who's interested know....

I now have it working. The problem was that I did not have administrator access to the server, and so was using a network share to store the configuration file. I now how admin access to the windows server, and have moved the config file to the server's C:\, which has fixed the problem.

Matt

|||

Thanks for posting the solution, I am just ran into that problem today - ODBC works fine on local machine but when I move to production I get the password length error, even though I set the password in my package. I will try the package configuration file tomorrow.

I'm having the same problem with the iSeries Access OLEDB drivers - I can't seem to get them to work. Did you ever get them to work? I'm using 5v3 iSeries Access.

ODBC password not saved in configuration file

Hi,

I have an SSIS package that pipes data from an iSeries data source to a SQL Server table. The connection manager I am using is the .Net Providers\Odbc Data Provider

When I run this SSIS package on my developer PC (from inside BIDS or using the Management Studio Execute Package Utility) it works. However when I run the package from within a SQL Server Agent Job it fails.

I have enabled logging and the error message given is:

System.Data.Odbc.OdbcException: ERROR [28000] [IBM][iSeries Access ODBC Driver]Communication link failure. comm rc=8002 - CWBSY0002 - Password for user MALLPRESS on server SMICHC is not correct, Password length = 0, Prompt Mode = Never, System IP Address = 172.16.0.4 ERROR [28000] [IBM][iSeries Access ODBC Driver]Communication link failure. comm rc=8002 - CWBSY0002 - Password for user MALLPRESS on server SMICHC is not correct, Password length = 0, Prompt Mode = Never, System IP Address = 172.16.0.4

It gives the message “Password length = 0” despite the fact that I have specified a password in the package.

To try to get around this I have added an XML configuration file to the SSIS package. In this configuration file I’ve set values for the ConnectionString, UserName, and Password.

I still get the above error about a password length of 0.

I have also tried to execute the package using dtexec, but this gives a similar error.

The SQL used to run this is:

xp_cmdshell 'dtexec /sq "\SCSRV-06\DTS Packages\Staging\StagingBranchImport"'

This has got me stumped, and I’m not sure where to go from here as these packages need to be programmatically run each morning. At the moment I am manually running them from within BIDS.

All packages that don’t use an iSeries connection manager run successfully in SQL Agent jobs.

I have also tried using the OLEDB connection manager (ibm db2 udb for iseries ibmda400 ole db provider) without success.

Any help would be very appreciated.

Thanks, Matt

SSIS will NOT store the password in the file for you. You have to edit the file manually to put the password in there. Have you done that?

-Jamie

|||

Hi Jamie,

Yes I have manually edited the config file and entered the password.

Matt

|||

Just to let anyone who's interested know....

I now have it working. The problem was that I did not have administrator access to the server, and so was using a network share to store the configuration file. I now how admin access to the windows server, and have moved the config file to the server's C:\, which has fixed the problem.

Matt

|||

Thanks for posting the solution, I am just ran into that problem today - ODBC works fine on local machine but when I move to production I get the password length error, even though I set the password in my package. I will try the package configuration file tomorrow.

I'm having the same problem with the iSeries Access OLEDB drivers - I can't seem to get them to work. Did you ever get them to work? I'm using 5v3 iSeries Access.