March 17, 2010 at 3:37 am
Hi,
I need to rollup data from multiple SQL Server 2005 DBs to a central server across a slow WAN (56kb/s). What would be the best method to do this? My initial thought was linked servers but I am worried about the WAN. Would SSIS be a more reliable fit here? If so where would the SSIS live - on the central server or on the remote clients?
I have never used SSIS before so any advice would be appreciated!!
March 17, 2010 at 2:49 pm
How much data? How often?
CEWII
March 18, 2010 at 1:01 am
There should be about 512kB every 30 minutes (per site)... (writing that down it actually doesn't seem so much 🙂 )
The system needs to be able to take care of itself and the (assumably) unreliable WAN cannot be a factor.
March 18, 2010 at 1:36 am
SSIS could be just fine for that.
I would house SSIS at the central site. You could build it such that it rolls the data up from each site, if one is down - report it as down and continue on from there.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 18, 2010 at 7:51 am
I agree with Jason. And you are right, 512K isn't much..
CEWII
March 19, 2010 at 1:44 am
Thanks guys, going to pursue the central idea as suggested by Jason 🙂
March 25, 2010 at 4:04 pm
In addition to agreeing with the others, I thought that I'd offer some suggestions for your SSIS design since you've stated that you have never used it before.
I would recommend creating a table that houses the connection info for your remote DBS. Something like InstanceName, DatabaseName, IsActive, etc.
1. Create 2 OLE DB Connection managers. One to your central DB and one to your Remote DB (just create a connection to one of them for now, we'll make that connection dynamic to connect to all of them).
2. Create an ExecuteSQL task in the Control Flow that queryies this table for all active Hosts. Output those values into an Object type variable. To do this, set the Result Set property to 'Full result set'. Then, on the Result Set tab, set the result name to 0 (zero) and ceate a new variable name of type object. This tells the task to place the entire contents of the source query into the object variable.
3. Set up a For Each Loop Container to loop through the ADO recoredset inside your Object variable from #2. On the Collection tab, set the Enumerator to 'Foreach ADO Enumerator' and set the 'ADO object source variable' to the variable from #2. On the Variable Mappings tab, create variables to hold your columns from #2. So you'll need variables for InstanceName, DatabaseName. The Index value should start at 0 (zero) and count up 1 for each column. This represents the ordinal value for the column order from your source query. So if you are only working with 2 columns InstanceName, DatabaseName (in that order), your values would be 0, 1.
What this does is sets up the loop so that each iteration will take 1 row from the object variable and place the column values for that row into the variables specified. Those variables can then be used elsewhere in the loop to make the loop contents dynamic. In this application, this will allow you to define the loop tasks one time and execute them against your multiple databases dynamically.
4. Now, highlight your Remote host connection manager and press F4. This will bring up the properties window. Find the Expressions property and open up the Property expressions editor by pressing on the elipses (... button). Now, select the ServerName property and open the expression editor. Once open, drag your InstanceName variable into the Expression window and press OK. Repeat this for DatabaseName and any other connection properties that you choose to store.
This will dynamically change the connection for all tasks that you use the conenction manager in. Place your data flows inside the loop container and use this connection manager inside your data flows for the source queries.
That should be enough to get you started....post back w/ questions.
March 26, 2010 at 3:07 am
John Rowan (3/25/2010)
In addition to agreeing with the others, I thought that I'd offer some suggestions for your SSIS design since you've stated that you have never used it before.I would recommend creating a table that houses the connection info for your remote DBS. Something like InstanceName, DatabaseName, IsActive, etc.
1. Create 2 OLE DB Connection managers. One to your central DB and one to your Remote DB (just create a connection to one of them for now, we'll make that connection dynamic to connect to all of them).
2. Create an ExecuteSQL task in the Control Flow that queryies this table for all active Hosts. Output those values into an Object type variable. To do this, set the Result Set property to 'Full result set'. Then, on the Result Set tab, set the result name to 0 (zero) and ceate a new variable name of type object. This tells the task to place the entire contents of the source query into the object variable.
3. Set up a For Each Loop Container to loop through the ADO recoredset inside your Object variable from #2. On the Collection tab, set the Enumerator to 'Foreach ADO Enumerator' and set the 'ADO object source variable' to the variable from #2. On the Variable Mappings tab, create variables to hold your columns from #2. So you'll need variables for InstanceName, DatabaseName. The Index value should start at 0 (zero) and count up 1 for each column. This represents the ordinal value for the column order from your source query. So if you are only working with 2 columns InstanceName, DatabaseName (in that order), your values would be 0, 1.
What this does is sets up the loop so that each iteration will take 1 row from the object variable and place the column values for that row into the variables specified. Those variables can then be used elsewhere in the loop to make the loop contents dynamic. In this application, this will allow you to define the loop tasks one time and execute them against your multiple databases dynamically.
4. Now, highlight your Remote host connection manager and press F4. This will bring up the properties window. Find the Expressions property and open up the Property expressions editor by pressing on the elipses (... button). Now, select the ServerName property and open the expression editor. Once open, drag your InstanceName variable into the Expression window and press OK. Repeat this for DatabaseName and any other connection properties that you choose to store.
This will dynamically change the connection for all tasks that you use the conenction manager in. Place your data flows inside the loop container and use this connection manager inside your data flows for the source queries.
That should be enough to get you started....post back w/ questions.
Thanks John, that is a great idea, I have followed your instructions and developed a small prototype and it is working well. Just a hint for all the other N00bs out there who follow Mr Rowan's post: After setting your OLE-DB connection parameters to variables (step 4) go back and assign them a default value else your Data Flow Task won't compile - took me about 2 hours to get that right!! 😉
Also I found this post quite useful :
http://www.sqlservercentral.com/Forums/Topic885782-148-1.aspx#bm886814"> http://www.sqlservercentral.com/Forums/Topic885782-148-1.aspx#bm886814
kudos again to John Rowan
March 29, 2010 at 2:10 pm
Ah, yes. I should have mentioned it! I assign default values any time I am working with dynamic expressions so I don't have to set everything to delayed validation.
Glad to help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply