Suggestion needed for sql server 2005 to 2008 replication

Hello experts,

I need to replicate few tables from Server A (sql server 2005 enterprise) to server B (sql server 2008 R2 enterprise) for an ACTIVE-ACTIVE environment

I need help deciding the best methodoly to achieve my goal. I was thinking transactional replication or mirroring. Which one do you think is best achieved? and can i get a step by step process to set up?
parpaaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hiran DesaiSolution ArchitectCommented:
Are you thinking of maintaining two databases in two different server 2005 & 2008 R2.
If so then replication strategy will work fine..depends on your requirement how you want to replicate.

If you are thinking for just data transfer and that's one time process then Linked server will be good option.
Carl TawnSystems and Integration DeveloperCommented:
You can't mirror between two different versions of SQL Server, so that option is out.

Transactional replication should work ok between versions. How up to date does your replicated version need to be? Log shipping could be an option too, but usually has a less frequent update cycle than replication.
parpaaAuthor Commented:
We need to replicate it close to realtime and that was the reason i didnt consider linked server or log shipping.
Carl TawnSystems and Integration DeveloperCommented:
In that case transactional replication is pretty much your only option.

You can find a simple guide to setting it up here: http://www.sql-server-performance.com/2010/transactional-replication-2008-r2/

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.