February 13, 2011 at 12:29 am
We have a bunch of old and new applications and the business wants to send data from one system to another system back through another system, etc, etc to cut down on the amount of admin data-entry that is being done.
What they want is becoming like a spider web of data going here, there and everywhere.
What kind of technology do you guys use to actually achieve something like this? Service Broker? SSIS? Just straight stored procedures/linked servers? Something else?
Would appreciate some comments.
February 13, 2011 at 8:21 am
Wow! Sounds like a bit of a mess. I guess it depends on what you're moving where and why. Any of the tools you mentioned will help, depending on the circumstances. Actually, the one thing I would say is, don't get one of these tools and use it to solve ALL problems. Look at what the tool does, replication for example, and ensure that it fits your needs. If it doesn't, don't try to force it through all kinds of crazy custom code to do what you want. Look at one of the other tools instead. But, rememeber, SSIS is all about doing some customization, so the rules change a bit there (but not if you find yourself writing lots of custom controls or custome VB scripts. that means you're stepping outside what SSIS should be doing).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 13, 2011 at 3:09 pm
Thanks for your thoughts Grant.
February 13, 2011 at 10:15 pm
I'm not sure what I can add to what Grant said about the tools, however, a few additional things to think of.
One of the things that will help to guide your choice of tools will be how much latency is allowed. Some of your data may be good to show up sometime in the next 48 hours. Some they may feel needs to be distributed inside of a few minutes.
Some instantaneous data you may have to live with linked servers. You can probably leverage Service Broker to keep this at near instantaneous levels but avoid a single server failure crashing the entire system. However, it's something to keep in mind.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 14, 2011 at 5:08 am
Craig Farrell (2/13/2011)
I'm not sure what I can add to what Grant said about the tools, however, a few additional things to think of.One of the things that will help to guide your choice of tools will be how much latency is allowed. Some of your data may be good to show up sometime in the next 48 hours. Some they may feel needs to be distributed inside of a few minutes.
Some instantaneous data you may have to live with linked servers. You can probably leverage Service Broker to keep this at near instantaneous levels but avoid a single server failure crashing the entire system. However, it's something to keep in mind.
That's an excellent point. I had it in mind when I was thinking about which tool to pick, but it's one of the driving factors. Thanks for pointing it out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 14, 2011 at 11:40 am
Linked servers can work for this type of thing if it's just specific data that needs to be distributed, such as changes to orders or customer information.
You want to look at which databases are the main ones holding the data you want to distribute. Which ones need to be updated from the main database or databases. This will determine if you want to 'push' the data or 'pull' the data.
If you have a database the needs to be on-line for user transactions and you don't want messy error messages coming up on it, then likely you will want to 'pull' the data you want from it. You don't want to push the data from a production server to a linked server in the middle of a transaction - if there's a problem on the other side then your transaction will likely rollback.
If you want very specific data changes from a database, and you want it available to distribute right away, then you may want to set up one or more staging databases whose sole purpose is to receive data that needs to be distributed to other databases and servers. The main transactional database can safely push what needs to be distributed into one of these staging databases (always on the same instance) and then forget about it. Jobs on the other servers wake up and pull what they want from the staging database(s). I would never push the data from a production database directly to another server while doing transactional processing.
I've done several of the staging type setups and they don't really impact the main OLTP databases very much.
Todd Fifield
February 14, 2011 at 2:29 pm
Thanks for the further thoughts guys, much appreciated. Plenty to think about.
Just a general question, having never used Service Broker before or even read much about it to date, is it something that is useful to consider for this type of work? In talking to friends and others, it seems to be a part of SQL Server that isn't used by many people. That true? Is it mature enough yet?
February 14, 2011 at 3:23 pm
I'm not that familiar with it. Sorry. It really isn't used much.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 14, 2011 at 3:26 pm
Service Broker isn't used much because its complexity is threefold.
1) If you've never worked with a messaging service you have to learn a bunch of new terminology.
2) You pretty much send everything around as XML, meaning most of us need to get better at using that.
3) There are other quicker to implement methods of doing similar things.
The key to Service Broker is that it it asyncronous. If one of the databases goes down and you have actions on one server that require actions on the other, triggers and/or linked server connections will fail the transactions. By utilizing Service Broker, the action on the foreign server is then queue'd up to eventually (eventual being a relative term, they communicate pretty quick) being able to be run when it comes online.
One of the other nice things about it is if you have a lot of heavy processor actions, you can put them in the queue and have them be run one at a time until complete avoiding your server grinding to a halt as you chew up the CPU.
Service Broker is nothing but an 'action queue', and how you manipulate that is up to your imagination. I've found it very useful for a number of scenarios.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 15, 2011 at 11:03 am
Craig Farrell (2/14/2011)
Service Broker isn't used much because its complexity is threefold.
Craig,
Do you know of a decent tutorial on it? I've been meaning to get into Service Broker for a while but I haven't had the time and it does look somewhat complex.
Todd Fifield
February 15, 2011 at 2:43 pm
tfifield (2/15/2011)
Craig Farrell (2/14/2011)
Service Broker isn't used much because its complexity is threefold.Craig,
Do you know of a decent tutorial on it? I've been meaning to get into Service Broker for a while but I haven't had the time and it does look somewhat complex.
Todd Fifield
I always use the excellent walkthrough I found by Adam Machanic, and still reference it if I get stuck. I've expanded on the topic with other items since then but this is so well done I can't say enough about it for a primer guide.
http://www.simple-talk.com/sql/learn-sql-server/service-broker-foundations-workbench/
http://www.simple-talk.com/sql/learn-sql-server/service-broker-advanced-basics-workbench/
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 16, 2011 at 11:27 am
Craig Farrell (2/15/2011)
tfifield (2/15/2011)
Craig Farrell (2/14/2011)
Service Broker isn't used much because its complexity is threefold.Craig,
Do you know of a decent tutorial on it? I've been meaning to get into Service Broker for a while but I haven't had the time and it does look somewhat complex.
Todd Fifield
I always use the excellent walkthrough I found by Adam Machanic, and still reference it if I get stuck. I've expanded on the topic with other items since then but this is so well done I can't say enough about it for a primer guide.
http://www.simple-talk.com/sql/learn-sql-server/service-broker-foundations-workbench/
http://www.simple-talk.com/sql/learn-sql-server/service-broker-advanced-basics-workbench/
Craig,
Thanks for the references. I'll look them up.
Todd Fifield
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply