Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
datadrum
Starting Member
3 Posts |
 Posted - 2009-04-27 : 11:05:31
|
| Greetings everyone. I posted this question in the replication topic but I'm also posting it here as I'm not 100% where it should fall. What type of replication or SQL Server tool would best fit this situation/problem:I have Database A that we have a front end app writing too. This app wrote data up till a date, I'll use May 1st as an example. Somewhere down the line we setup another front end app that is now writing to Database B. So, I have data on Database A up till May 1st. After May 1st, the front end starts writing to Database B.What SQL Server 2005 tool/solution would be best so that I can bring the data from database A into database B? or merge the databases so that whatever is missing is replicated over?Many thanks! |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-04-27 : 13:34:17
|
Sounds like you have to do data migration fom A to B only one time.Webfred
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
Stamey
Starting Member
14 Posts |
Posted - 2009-04-27 : 13:41:32
|
| This depends on the compatibility level between the tables in DB A vs DB B. For instance, are the schemas exactly the same? What about identity fields on Primary keys? If the new DB was started with the initial identity value that the original DB started with you will duplicate values when copying to the new DB. If you have unique indexes on such fields you will get an error.Another way to go, possibly, is to just copy the tables into the new DB with an alternate name, such as TableX-Archive. What this swill afford you is as follows: 1. You will be able to have the data in the same DB without trampling on any field value issues that were mentioned above. If you want to query the "archive" tables AND the current tables, outside of the app, you can create a view that spans the old and new tables as if they were one, without breaking your app, if there are field value issues.All of the above can be done with TSQL relatively easily.Chris |
|
|
|
datadrum
Starting Member
3 Posts |
Posted - 2009-04-27 : 13:50:40
|
| Thank you for the reply webfred and Stamey. Yes the databases are identical in schema and structure. They both were setup at the same time but database A was being written to at one point and then the new front end was pointed to database B after May 1st. So there was a cut-off date. I'll look into those options and see what we can do. Thank you for you replies. I was hoping that there would be a SQL Server tool that would aid in this. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-04-27 : 16:57:29
|
| Just create SSIS package for insert(If not exists) or update (If exists) |
|
|
|
|
|
|
|
|
Merging of databases