September 9, 2009 at 12:30 pm
This is a question for use of SSIS to transfer data among SQL database instances in an environment:
Does it make sense to have a single centralized, dedicated server for SSIS packages (and SQL Agent jobs running them) or to follow a decentralized approach and have SSIS packages running on each individual SQL Server instance?
Which option makes more sense?
Any thoughts?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 9, 2009 at 12:51 pm
Which option makes more sense?
I know you are going to hate this.. But it depends on a lot of factors..
First off, I like the idea of a centralized server, but you would need at least 2, one for prod and one for dev. I would also think you need strong change control procedures and some policies and procedures on how the SSIS packages are stored (I like directories in SSIS).
But in reality I tend to like to keep the SSIS packages close to the data they serve.
If you are moving a lot of data you have to pull it through the central server and push it out to the destination, this can lead to link saturation.
SSIS would only need a small SQL instance locally for package storage (I don't like file based solutions)..
SSIS would not be competing with SQL for memory.
So I guess for me, I have several with the SQL Servers they service and I would use MSDB as the storage location, as I said I don't like file based solutions since I now have to backup 2 locations to get everything..
CEWII
September 9, 2009 at 12:58 pm
Elliott W (9/9/2009)
Which option makes more sense?
I know you are going to hate this.. But it depends on a lot of factors..
First off, I like the idea of a centralized server, but you would need at least 2, one for prod and one for dev. I would also think you need strong change control procedures and some policies and procedures on how the SSIS packages are stored (I like directories in SSIS).
But in reality I tend to like to keep the SSIS packages close to the data they serve.
If you are moving a lot of data you have to pull it through the central server and push it out to the destination, this can lead to link saturation.
SSIS would only need a small SQL instance locally for package storage (I don't like file based solutions)..
SSIS would not be competing with SQL for memory.
So I guess for me, I have several with the SQL Servers they service and I would use MSDB as the storage location, as I said I don't like file based solutions since I now have to backup 2 locations to get everything..
CEWII
Thank you for the input!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply