July 29, 2009 at 2:08 pm
We purchased new server hardware and sql 2005 software, we also have two other sql 2000 servers one has reporting db and a few other db's the other sql server is for development.
My question is what is the best way to pass data between the 2 production servers of different versions Reporting database will be on the 2000 server. The main crm database will be in 2005.
Thanks for any suggestions.
July 30, 2009 at 6:56 am
It depends on what you want to do. You can use sqlcmd to run command line scripts to move data from 2008 to 2000 & vice versa. SSIS will work in both directions. You can connect from 2000 to 2008 Query Analyzer (although I've seen some quirky behavior). If you look it up in Books Online, there's a whole section on using different versions of SQL Server within a replication topology.
You've got tons of choices. It really depends on what you need. For a straight copy of the data, I'd suggest replication. If you need to do ETL, SSIS is your friend. For the odd one-off, use TSQL.
"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
July 30, 2009 at 9:48 am
Thanks for the reply
We will most likely be doing its a reports database that needs to be a copy of the production database at some intervial behind. 1 hour 4 hours 8 hours and so on. Also developement of our internal crm will have elements created in 2000 (sp's/tables/etc.) Im not a DBA just the server admin trying to prevent installing sql 2000 on a server with 16GB's of RAM quad 2.8's the ram would be wasted. So it seems replication seems like the best route for our reports database. What about system load? will it be too heavy? SSIS seems like it have take care of the development elements moving to production.
Thoughts?
July 30, 2009 at 11:16 am
Ronnie.Patton (7/30/2009)
Thanks for the replyWe will most likely be doing its a reports database that needs to be a copy of the production database at some intervial behind. 1 hour 4 hours 8 hours and so on. Also developement of our internal crm will have elements created in 2000 (sp's/tables/etc.) Im not a DBA just the server admin trying to prevent installing sql 2000 on a server with 16GB's of RAM quad 2.8's the ram would be wasted. So it seems replication seems like the best route for our reports database. What about system load? will it be too heavy? SSIS seems like it have take care of the development elements moving to production.
Thoughts?
Without tons more details it's hard to say whether or not your system will handle the load.
If I understand the question, SSIS is not a good choice for moving structures from development to production. If you mean moving data from development to production, yeah, SSIS is a good choice.
"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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply