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 |
|
mfahner
Starting Member
11 Posts |
 Posted - 2007-02-15 : 16:33:18
|
| Hello,We are in the process of creating a stored procedure that will upgrade Database A to Database B and move all the tables with Data in DB A to DB B. My question to everyone is will we have a performance issue when upgrading a wide table or a table that has millions of rows in it. If so what are our options as far as increasing the speed of transferring data from DB A to DB B.thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-15 : 16:44:24
|
| Why not just use BACKUP/RESTORE?Tara Kizer |
 |
|
|
mfahner
Starting Member
11 Posts |
Posted - 2007-02-15 : 16:49:39
|
| We cannot use Backup/REstore since we are updating the schema as we go. So for example we have a client that is on version A of the database and know we are on version Z of our database schema. So the stored procs need to map the data from version A to Version Z but the client might have data in Version A of the Database that we need to transfer over to Version Z. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-15 : 16:54:05
|
| Just exactly how are you updating the schema? Why do you need to move data between databases? Why not just update the current database with the schema changes? We make schema changes all of the time and never need move data between databases to do it.Tara Kizer |
|
|
|
mfahner
Starting Member
11 Posts |
Posted - 2007-02-15 : 17:05:52
|
| Well I guess we are not really updating the schema but we create a new Database and then move the data from Version A to Version B and B being the new database that we just built. Basically our customers have data in almost every column that needs to either get mapped to a new column in Version B or mapped to the same column in Version B. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-15 : 17:18:41
|
| I think you need to revisit your procedures as what you are doing is a ton of extra work. Can you give us an example of one of the changes in Version A to Version B?Tara Kizer |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-16 : 08:23:59
|
| I think you need to weight up whether you change the Schema in-situ, or whether you copy the data to a new database.We do the first, sometimes I wish we did the second!I'm very happy with in-situ schema changes. We have procedures that work well.Trouble is when we move from TEST to PRODUCTION sometimes things get overlooked (I know, they shouldn't, but its my real-world experience that they do ... some last minute fix that didn't get checked in, for example)So I'm tempted to 1. Create a New Database2. Restore TEST into the New Database3. Copy data from Production into New Database4. Drop Production5. Rename New Database to Productionbut I reckon that "Copy the data" will increase the downtime considerably over just running the schema change scripts.Horses-for-courses I suppose ...Kristen |
|
|
|
hrishi_des
Yak Posting Veteran
76 Posts |
Posted - 2007-02-16 : 08:29:32
|
| HI,I agree with Tara, apply changes to old db, dnt "move" data & proc to another dbWhen solution is simple, God is answering…. |
|
|
|
|
|
|
Performance Concerns